It slices! It dices! It delivers 5000 tps!
   
 

10 Interacting With Databases

Like most web programming languages, the moto language was designed with database interaction in mind. Extensions are included with moto in order to facilitate interaction with Postgres and MySQL databases. To connect to one of these databases you must use the appropriate codex.db library

$use("codex.db.mysql")
$use("codex.db.pgsql")
 

10.1 Connecting to the Database

Prior to using a database you must connect to it, this is done in moto by constructing a connection object

$declare(MySQLConnection myconn = new MySQLConnection(server address,database name,user,password))
$declare(PGConnection pgconn = new PGConnection(server address,database name,user,password))
 

10.2 Querying the Database

Once you have a connection to the database you can issue SQL queries against it. Queries results are returned in a ResultSet.

$declare(String query =
  "SELECT cardsets.id,cardsets.name,cardsets.shortname,count(print.id) AS cardcount ,sum(price.buy) AS setprice,sum(price.diff) FROM cardsets,print,price WHEREprice.print=print.id AND print.edition=cardsets.id GROUP BY print.edition")

$switch(getValue("_orderby","Set"))
  $case("Set")
     $do(query = query + " ORDER BY cardsets.name")
  $case("Cards")
     $do(query = query + " ORDER BY cardcount DESC")
  $case("Price*")
     $do(query = query + " ORDER BY setprice DESC")
$endswitch

$declare(MySQLResultSet rset = conn.query(query))
 

Those results are then iterated over and either displayed or used in other computations. The result set object keeps track of what row you are currently looking at. Methods are called on the result set object to retrieve individual columns of the current row.

     $while( rset.next())
        <tr>
           <td align=center><img src="images/exp_sym_$(rset.getString(2)).gif"></td> <td >
              <a href="cardlist.moto?sid=$(
                 getState().getSID()
              )&searchby=set&set=$(
                 rset.getString(0)
              )">$(rset.getString(1))</a>
           </td> <td align=center >
              $(rset.getInt(3))
           </td> <td align=center ><nobr>
              $("$ ")$(rset.getString(4))
              $if(rset.getFloat(5) > 0)
                 <img src="images/uparrow1.gif">
              $elseif(rset.getFloat(5) < 0)
                 <img src="images/downarrow2.gif">
              $endif
           </nobr></td>
        </tr>
     $endwhile
 

The result set method next() must be called prior to retrieving values for fields in the result set. This method returns true if there are more rows in the result set then scrolls to the next one.

Values for specific columns in the result set are returned either by specifying the column number (as in the above example) or by referencing the column name. Column names are not case sensitive.

$(rset.getString("column name"))  

To find out how many rows were returned in the result set, call the method size() on the result set object

$if(rset.size() > 0)  

10.3 Modifying Data

You can also execute SQL inserts, updates, or deletes against the database. This occurs via the update method of the Connection object. This method returns the number of rows inserted, updated, or deleted by the method.

           $if(rset.next())
              $do(deckid=rset.getInt(0))
              $do(conn.update("DELETE FROM deckcards WHERE deck="+str(deckid)))
           $else
              $do(conn.update(
                 "INSERT INTO deck VALUES (0, '"+
                 conn.escape(getValue("_name"))+"','"+
                 conn.escape(getValue("_creator"))+"',"+
                 str(uid)+",0)"
              ))
              $do(deckid = conn.insertID())
           $endif

           $do(tok= new Tokenizer(ids,'|'))

           $while((curID=tok.next()) != null)
              $declare(String curQTY =getValue("_q"+curID))

              $if(curQTY != null && curQTY ne "0")
                 $do(conn.update(
                    "INSERT INTO deckcards VALUES ("+str(deckid)+","+curID+","+curQTY+",0)"))
              $endif

              $do(curQTY =getValue("_s"+curID))

              $if(curQTY != null && curQTY ne "0")
                 $do(conn.update(
                    "INSERT INTO deckcards VALUES ("+str(deckid)+","+curID+","+curQTY+",1)"))
              $endif
           $endwhile
 

When inserting String values into the database you should first escape the string value being inserted. This is accomplished by calling the connection method escape() on the value to be inserted. Binary data may also be escaped in this way for insertion into BLOB fields. The escape function effectively backslashes single quotes and other restricted characters.

conn.escape(String value to be escaped)
conn.escape(byte[] to be escaped)
 

10.4 Accessing Database Meta-Data

Once connected to a database you can retrieve a list of tables in the database as a result set by calling the listTables() method on your connection.

$do(rset=conn.listTables())
$while(rset.next())
   $(rset.getString(0))
$endwhile
 

Once you have a result set in hand you can retrieve the names and types of the columns returned

$declare(int cols=rset.getColumns())
$declare(int i)
$for(i=0;i<cols;i++)
   $(rset.getColumnName(i))
   $(rset.getColumnType(i))
$endfor
 

10.5 Persisting Queries Across Multiple Pages

ResultSets cannot be stored in the session or the context since the results are not stored in shared memory. Yet caching queries often provides a very good performance benefit. To do this, TabularData objects have been provided to store result sets in shared memory. The TabularData object is provided in the codex.util extension.

$use("codex.util")
$declare(TabularData ocs)
$if((ocs=<TabularData>getContext().get(query)) == null)
   $do(ocs = conn.query(query).store())
   $do(getContext().promote(ocs))
   $do(getContext().put(query,ocs))
$endif
 

TabularData objects are similar to result sets except the entire result is stored in memory. Thus rows my be accessed in an arbitrary order.

$for(i=0 ;i < ocs.getRows() ;i++)
   $do(curSet = ocs.getInt(i,0))
$endfor
 

The standard field retrieval functions getInt,getString,getFloat etc... take both a row and a column ( or a row and a column name)

TabularData objects are also modifyable and may be used as a convenient data structure with which to store arbitrary table data

  $declare(TabularData cardData)

  $if((cardData =<TabularData>getContext().get("CardList")) == null)

     $declare(MySQLResultSet cacherset = conn.query("SELECT max(id) from print"))
     $do(cacherset.next())

     $do(cardData = new TabularData(
        cacherset.getInt(0)+1,
        10,
        "shortname,name,card,print,color,type,cost,rarity,price,diff",
        "String,String,int,int,char,String,String,char,float,float")
     )

     $do(cacherset = conn.query(
        "SELECT cardsets.shortname,card.name,card.id,print.id" +
        ",card.color,card.type,card.cost,card.rarity,price.buy,price.diff "+
        "FROM cardsets,card,print LEFT OUTER JOIN price "+
        "ON price.print=print.id "+
        "WHERE card.id = print.card "+
        "AND print.edition = cardsets.id"
     ))

     $while(cacherset.next())
        $do(cardData.setString(cacherset.getInt(3),0,cacherset.getString(0)))
        $do(cardData.setString(cacherset.getInt(3),1,cacherset.getString(1)))
        $do(cardData.setInt(cacherset.getInt(3),2,cacherset.getInt(2)))
        $do(cardData.setInt(cacherset.getInt(3),3,cacherset.getInt(3)))
        $do(cardData.setChar(cacherset.getInt(3),4,cacherset.getChar(4)))
        $do(cardData.setString(cacherset.getInt(3),5,cacherset.getString(5)))
        $do(cardData.setString(cacherset.getInt(3),6,cacherset.getString(6)))
        $do(cardData.setChar(cacherset.getInt(3),7,cacherset.getChar(7)))
        $do(cardData.setFloat(cacherset.getInt(3),8,cacherset.getFloat(8)))
        $do(cardData.setFloat(cacherset.getInt(3),9,cacherset.getFloat(9)))
     $endwhile
     $do(getContext().promote(cardData))
     $do(getContext().put("CardList",cardData))
  $endif