Contract
No input parameters.

Water Contract
<class ice />

Database Installation

If you've already got a database and know how to connect it, great. If not, we suggest setting up a MySql on your development computer, since its free and good. You can download a free MySql server from www.mysql.com

Warning

MySql, like most programs can be easy or quite difficult to install, depending on the idiosyncracies of the current state of your operating system, exactly what choices you make and what order you perform the many steps needed to install it, etc. If it doesn't work the first time, good luck finding documentation to help you. With persistance and skill, you will eventually be able to get it to work. Below we document one path but there are at least several that could work.

Java driver

Also, in order to have water connect to a database server running on your local machine, you need to install a Java 'driver'. This is a set of Java class files contained in a 'jar' file. Go to www.mysql.com and look in their download area for "Drivers and Connectors" for Java. Download the appropriate jar file and place it in your lib/ext folder under your Java folder. Alternatively, you can place the file in the ext_jars folder within your water_user_folder. The file will be named something like: mysql-connector-java-3.1.7-bin.jar Use the Steam IDE menu bar menu "Help/About" to see the exact location of the folder to place Java extensions.

Launching the Database

After installing the database and any extensions, you must reboot Water. Some ways to install MySQL will set it up to launch each time your computer launches. This isn't so great unless you expect to be using MySQL all the time because having more processes running than necessary on your machine slows it down and invites trouble. You can launch the database server either from your operating system user interface or programmatically from Water. On Windows by default there will be files: "C:/Program Files/MySQL/MySQL Server 5.0/bin/mysql.exe" and a "mysql.exe" file. We've had a hard time using these to launch MySql. Here's how you can lanuch it from Water on recent versions of Windows XP.

os.<execute source="NET START MySQL"/>.<trim/>

"The MySQL service was started successfully."

You can stop the database from water with the below:

os.<execute source="NET STOP MySQL"/>.<trim/>

"The MySQL service is stopping..
The MySQL service was stopped successfully."

Get the Database Object

In order to persist to a database, you first need to make a database resource:

temp.<set MYSQLROOT = <resource "jdbc:mysql://localhost/" 
                         username="root" password=""/> />

temp

To get a vector of the databases at the resource do the following. Note that it is common for this operation to error, perhaps because the database wasn't installed correctly, launched correctly, or given the wrong username and password when you were creating the database resource. You may see a "couldn't connect to the database" error message with some vague clues from MySQL about how their software is broken. This is the crucial step. If it works, its likely that MySQL is in good shape, if not, debugging is likely to be difficult.
Be sure to use the correct username (usually root) and password (chosen at time of installation).

temp.MYSQLROOT.<get_databases/>.<is_a vector/>

true

The list of databases can be inspected to find the names of the existing database at the resource. MySQL has a "test" database installed at the beginning to help you play.

temp.<set MYSQL=temp.MYSQLROOT.<get_database "test"/>/>

temp

Here's how to get a vector of the names of the tables in the database:

temp.MYSQL.<get_table_names/>.<is_a vector/>

true

Creating a Persistent Class

Once we have a database, we need a class (or several) to persist, for example:

<class person 
  id=req=integer name=opt=string
  age=38=integer children=opt=integer works_for=opt
  friends=opt=multiple enemies=opt=multiple
  <!-- _parent_f_persist=true -->
  _table="PEOPLE" _unique_key="id">
 <method htm_inst>
   <span> Person <do .name/> (<do .id/>) </span>
 </method>
 <!-- Declare types here, since we can't have circular references -->
 .<set works_for_f_type=_subject/>
 .<set friends_f_type=<multiple_of _subject/>/>
 .<set enemies_f_type=<multiple_of _subject/>/>
</class>
person.<get_type "works_for"/>

person

This will create the 'person' class. The default table for it to be stored in will be the same name as the class, ie 'person'. Above we override that default by _table="PEOPLE".
To persist the class to the database, use the dbify method on the database connection, calling it on the class (or classes) to persist. This will create the 'people' table or overwrite any existing 'people' table.

temp.MYSQL.<dbify person/>

person

Each instance of a person will be stored in a row in the "PEOPLE" table. We can see the columns that will be used for storing each field in an instance like so:

person._cols

<v 'id' 'name' 'age' 'children' 'works_for'/>

dbify will also create auxilliary tabels for holding complex values for fields. In this case, the fields having values of type 'multiple' (enemies and friends) will each have a table created for them with names 'people_enemies' and 'people_friends'.

Using The Database

Here we create an instance of person [in RAM], and 'put him on ice', [DISK] (store the new instance in the database.) by freezing him like so:

temp.<set js=<person id=1 name="John Sheridan"/>/> <!-- make the instance -->
temp.js.<freeze/>  <!-- store it in the database -->
temp.js.name

"John Sheridan"

Note that we might want to create an instance and set a whole bunch of fields in it before storing it in the DB. This is much faster than saving on each modification. Way below we show how to save all the instances of a class in one fell swoop.
The database now contains a new row representing the new instance. Now we can use SQL to query our database:

temp.MYSQL.<execute "SELECT * FROM PEOPLE where id=1"/>.<length/>

Just one instance matches the query criteria so there's only one object in the returned vector of results. Note that each object is of type 'thing', not of type 'person'.
We can change the age, freeze the instance, and get the age back via SQL.

temp.js.<set age=42/>
temp.js.<freeze/>
temp.MYSQL.<execute "SELECT age FROM PEOPLE where id=1"/>.0.age

We can use find_instances to use SQL's search capability on our objects (whether we've loaded them or not). Here the returned objects are instances of person. If only one object is found, it is returned. If more than one is found, a multiple of the instances is returned.

person.<find_instances age=42/>

<m temp.js/>

We can define more instances as we wish and "freeze" the class to commit all the instances we've changed/created to the database. Then we can use SQL to check that they're really there:

<person id=2 name="Stephen Franklin" 
        works_for=person.<find_instances name="John Sheridan"/>/>
<person id=3 name="Susan Ivanova"/>
<!-- Freeze all modified instances of a class -->
person.<freeze/>
temp.MYSQL.<execute "SELECT * FROM PEOPLE where id=3"/>.<length/>

Or we can just create an individual instance and freeze it:

<person id=4 name="Michael Garibaldi" 
        works_for=<person id=1/>/>.<freeze/>
temp.MYSQL.<execute "SELECT * FROM PEOPLE where id=4"/>.<length/>

For SQL-backed classes, find_instances with no arguments returns all of the instances stored in the database.

person.<find_instances/>.<length/>

SQL can be used to store the one-to-many relationships implemented in Water by multiples. To demonstrate this, we first bind some convenience variables to the instances created above. This should work even if we have restarted Water, since it will load them from the SQL database.
ICE does some tricks with Water default values, so it's worth while checking that they still work.

temp.<set sf=<person id=2/>
          si=<person id=3/>
          js=<person id=1/> 
          mg=<person id=4/>/>
<!-- Check that defaults still work (a little tricky) -->
temp.si.age

Another test, because we're about to change it.

temp.mg.age

38

Now we'll change a value and see it reflected in a SQL query:

<!-- Modify one instance and freeze all the changes -->
temp.mg.<set age=48/>
person.<freeze/>
temp.MYSQL.<execute "SELECT age FROM PEOPLE where id=4"/>.0.age

Finally, we test multiples, adding values to the "friends" field:

temp.sf.<add friends=temp.si/>
temp.sf.<add friends=temp.mg/>
temp.si.<add friends=temp.js/>
person.<freeze/>
temp.sf.friends

<m temp.si temp.mg/>

temp.MYSQL.<execute "SELECT friends from PEOPLE_friends where id=2"/>.<length/>

temp.sf.<drop friends=temp.mg/>
temp.sf.<freeze/>
temp.sf.friends

<m temp.si/>

temp.MYSQL.<execute "SELECT friends from PEOPLE_friends where id=2"/>.<length/>