Unofficial Roxen & ChiliMoon FAQs

Roxen and ChiliMoon stores some stuff in its local database:

Argument cache, cimg cache, precompiled security patterns (eg htaccess), gbutton cache, gtext cache, roxen module list, precompiled pike files, session cache, update information, etc...

Most of which is information that is easily reconstructed, or doesn't matter much if it gets lost. Therefore this data does not have to be backuped.

One important point here is that the <accessed/> tag may store information in the database. If the database gets zapped, your accesscounters may get zapped as well. This may be bad, so to make sure that you don't suffer any data loss, please back up the internal mysql-files as well.

No you can't. Developers have chosen MySQL for many reasons, not the least of them MySQL being the fastest in the way it is used (for caching purposes eg.) It is not recommended to use the internal database for your own project, because there are no backups made of the database.

You are not forced to use MySQL for your own data, it just uses it for its own purposes. You are free to use whatever database server you want for your own data.

Yes. "Create new database" in the administration interface's DBs tab. Choose type "external" and insert an Url in the form of

 Unix: mysql://user:password@:/path/to/mysql/socket/db-name 
 Win: mysql://user:password@:C_/path/to/configurations/_mysql/pipe 

Roxen uses socket when connection is made to mysql://localhost/db and if the operating system permit it.

You can access the the internal databases from the command-line MySQLl program if you have read/write access to the mysql socket file (which is located in the "_mysql/" sub-directory of the configuration directory usally accessed as "../configurations" relative to the server directory).

Start the MySQL command-line client like this:

 mysql --socket=/usr/local/roxen/configurations/_mysql/socket -u rw 

An alternative is to connect via TCP-IP. Connect to the MySQL server via socket file. Create an user with the rights for external access (see MySQL manual) and change the bind addresse in /usr/local/roxen/configurations/_mysql/my.cfg to

 bind-address = 192.168.0.13               # (or whatever your IP is).  
 port=3307                                 # optionally, if you want to change from the standard port 3306 

Another option to change the bind address and port is to set the environmental variables in roxen's startup-script

 export MYSQL_HOST=192.168.0.13            # (optionally, default 127.0.0.1) 
 export ROXEN_MYSQL_TCP_PORT=3306 

The cleanest way is to set up your database links inside the administration interface, so you don't have to embed any database names or passwords in the script itself:

 <?pike 
 object sq=DBManager.get("mydb"); 
 array result=sq->query("select id, name, address from mytable"); 
 write("%d records:<br>\n", sizeof(result)); 
 foreach (result, mapping row)  
 { 
    write("ID: %s<br>Name:%s<br>Address:%s<br><br>\n",  
        row->id, row->name, row->address); 
 } 
 ?> 

Roxen older than 4.0 shipped with MySQL 3.23 but should also work with MySQL 4.0 (but not later). You have to be careful to compile Pike earlier than 7.4 using MySQL 3.23 client libraries - they connect just fine to a MySQL >=4.0 server. Roxen 4.0 ships with MySQL 4.0. Pike 7.4 and later compiles with MySQL 4.0 / 4.1 client libraries.

ChiliMoon has the same requirements for MySQL version as Pike CVS does. Which means MySQL >=4.0 server for now.

• insert <sqlquery host="your_host" query="alter session set nls_date_format='YYYY-MM-DD'" /> somewhere at the top of every page, or
• if you have access to the startup scripts of the Roxen server, simply set the environment variable NLS_DATE_FORMAT there, or
• or even better, just add a line "export NLS_DATE_FORMAT=..." to local/environment.

• Check if you have access to the database from the DB manager (DB tab in the admin interface)
• Check if your site has according rights (R=Read and/or W=Write) in the administration interface's DB manager
• You might also check if you have access to DB from your host in general and from Pike in special

Internally the column variables are "UNDEFINED" if the value in the row is NULL. Undefined variables in RXML tested with <if variable="sql.col">/ will return false for a NULL value in the row's column "col", and true for an empty string in this column. Inserting an undefined variable in RXML will give an empty string as result.

In your main page write

 <img src="getimage?id=12332" /> 

The getimage.html page that looks like

 <header name="Content-Type" value="image/jpeg" /><emit source="sql" query="select * from binfiles where id=&form.id"><img src="&sql.binary;"/></emit> 

Important note: write this in one line so you don't add any extra linefeeds to the output image.

Alternative option:

 <emit source='sql' query='select imagedata from images where id=37'> 
   <cimg data='&sql.imagedata:none;'/> 
 </emit> 

This solution is worse from a scalability viewpoint, since using the cimg tag produces a copy of the image in the image cache

If you install Roxen / ChiliMoon from source you have to ensure to have your native database client libraries installed (and Pike can find them) before you compile Pike.

To verify if your database drivers are available in your Pike, use the Pike module list task in the Roxen / ChiliMoon Administration interface: tasks -> debug_information -> features show the available Pike modules. To get new modules included, you have to install your database client libraries first and either reinstall Roxen or recompile Pike.

On linux you can directly test if your database libraries are available (example checks for Oracle):

  ldd /<roxen-home>/pike/lib/pike/modules/___Oracle.so 

If you are using other OS then you have to use truss(1) or some equivalent command and try to connect to the oracle from hilfe:

 # cd /<roxen-home>/server/bin 
 # truss ./roxen 
 Pike v7.0 release xyz running Hilfe a.b (Incremental Pike Frontend) 
 object db=Sql.sql("oracle:///<sid>"); 

Native database drivers are supported for Oracle, Sybase, Msql, Postgres, Informix. Others can be used with an ODBC driver.

It is possible, however it is not recommended in a production server. Always keep in mind that there might be no backups made of the internal database.

if you have access to the startup scripts of the Roxen server, simply set and export the following environment variables

export ORA_NLS33=/usr/local/oracle/app/oracle/product/8.1.7/ocommon/nls/admin/data 
export LNG=american_america.WE8ISO8859P1 

or add them to the local/environment.

You can use <else>/ directly behind <emit>/ and <sqlquery>/. With <sqlquery>/ the statements between the <else>/ tags are executed when the query fails. Furthermore you can use <then>/ when the query succeeds.

<sqlquery query="foo" /> 
<then> 
  Success handling code.  
</then> 
<else> 
  Error handling code. 
</else> 

With <emit>/ the statements between the <else> tags are executed when the query returns no result whereas the contents of the <then>/ container are executed after the last returned result.

<emit source="sql" query="foo" /> 
 Code for the queryresult. 
</emit> 
<then> 
  Success handling code.  
</then> 
<else> 
  Sorry, no results. 
</else> 

Note that the <else>/ tag will not suppress SQL error messages.