Databases

Databases supported

Any REBOL driver implementing the port API for databases (as used in REBOL Technologies' drivers) or in Softinnov's drivers will work. Non-standard drivers can be used too but won't benefit from Cheyenne's databases abstraction layer (you will need to open/close connections in your own code).

• Softinnov's free drivers: MySQL and PostgresQL are supported.
• REBOL Technologies drivers: they require the SDK if you run Cheyenne from sources or a Cheyenne Command binary version.

Databases declaration

RSP and webapps provide a thin abstraction layer to access databases. This means, you don't have to care about opening, closing or re-opening databases connections. It is managed automatically by Cheyenne as long as you provide a definition in the configuration file using the databases? directive, in one of the sections (Globals, Hosts or webapp):

 globals [
     databases [
         books    mysql://user:pass/localhost/books
     ]
     ...
 ]
 domain.com [
     ...
     databases [
         books2   mysql://user:pass/localhost/books
     ]
     ...
     webapp [
         ...
         databases [
             books3   mysql://user:pass/localhost/books
         ]
     ]
 ]

Note: avoid using the same database definition name in different scopes as the inherited definitions can collide (needs a more accurate heritage support in the RSP engine).

You can add as much definitions as you need in the same databases block:

 databases [
      books    mysql://user:pass/localhost/books
      admin    mysql://root:pass/localhost/books
      sales    oracle://user:pass/1.2.3.4/sales
      ...       
 ]

Once defined, databases connections will be automatically opened on first SQL request. As Cheyenne relies on a pre-forked model for RSP scripts evaluation, the number of connections will scale up with the worker processes number, so you'll get a natural databases connection pooling for free!

Databases access

The SQL queries are sent using a single function: DO-SQL that will take two arguments:

• the database connection name (as declared in configuration file)
• the SQL query (or query and parameters in a block)

Example:

 <%
     list: do-sql 'books "SELECT * FROM titles WHERE author='Kubrick'"
 %>
 <table>
     <tr><th>Title</th><th>Author</th><th>Year</th>
 <% foreach book list [ %>
     <tr>
        <td><%= book/1 %></td>
        <td><%= book/2 %></td>
        <td><%= book/3 %></td>
     </tr>
 <% ] %>
 </table>

Another example using parameters from the request:

 <%
     unless invalid?: validate/full [author string! *][
         list: do-sql 'books [
             "SELECT * FROM titles WHERE author=?"
             request/content/author
         ]
     ]
 %>

This second form is what you should be using for securing query's arguments and avoiding SQL injection attacks.