mSQL: a simple but efficient SQL server

Example: an Image database A.Empain June'98

Introduction

Historically, databases were first handled by monolithic application (following the mainframe/terminals paradigm, often fused as stand-alone machines for small applications).

Later on, the duty to handle the problem was divided into the server side (e.g. SQL server) and a proprietary client side.

Things evolve towards a more open approach, owing to the 'standardized' SQL and ODBC or JDBC isolationg layers, for example.

mSQL

mSQL is a low-cost (free for research and schools) lightweigh SQL server , available for many platforms (the sources are provided).

It is written to allow in many cases the replacement of heavy and expensive SQL servers, when the application does not require exotic SQL functions and when low price and efficiency are the main requirements (typical for a WEB server use).

Structure of the example

Database side :

The mSQL server runs on a Linux machine (msql2d = 150 kB);

The database is called 'addr', the image table is 'image' (contains some description text fields and the image file name)

[root@taraxacum bin]# ./relshow addr image
Database = addr
Table    = image
 +-----------------+----------+--------+----------+--------------+
 |     Field       |   Type   | Length | Not Null | Unique Index |
 +-----------------+----------+--------+----------+--------------+
 | imageid         | char     | 15     | Y        | N/A          |
 | owner           | char     | 2      | N        | N/A          |
 | title           | char     | 30     | N        | N/A          |
 | imgdate         | char     | 9      | N        | N/A          |
 | insdate         | char     | 8      | N        | N/A          |
 | isocode         | char     | 2      | N        | N/A          |
 | location        | char     | 30     | N        | N/A          |
 | source          | char     | 6      | N        | N/A          |
 | category        | char     | 15     | N        | N/A          |
 | span            | char     | 1      | N        | N/A          |
 | comment         | char     | 78     | N        | N/A          |
 +-----------------+----------+--------+----------+--------------+

HTTP server side

mSQL implementation :The mSQL http interface is copied into the '/cgi-bin' directory on the HTTP server (w3-msql = 120 kB)

HTML front-end : classical HTML pages with classical <FORM ...> <INPUT> </FORM> layout; the trick lies in the CGI call :

w3-msql is the html/SQL interface binary (cited above),

the actual 'CGI' is image-qux.html (placed by convention in '/usr/local/Hughes/www'

<FORM METHOD=GET ACTION=/cgi-bin/w3-msql/image-qux.html>
<input type=submit> 
<br>WHERE imageid is : (%=any string, _=any character)
  <input name="imageid" size=20> 
 <br>OR Location contains :
  <input name="location" size=30> 
<br>OR Comment contains :
  <input name="comment" size=30> 
 </FORM>

Application implementation : The specific 'embedded mSQL' html pages (see also above) are placed in '/usr/local/Hughes/www' (standard place for security = not seen by the client browser)

The trick is to use <! ......... > to embed mSQL front-end statements (PERL like language)

<H1>Usage example of the mSQL 2.x interface ([email protected])</H1>
<hr>
<!-- Connect to the ADDR - IMAGE database ---------------------->
<! echo("<H2>Welcome to the IMAGE database</H2>\n"); $IP="...";>
<!    $sock=msqlConnect($IP); >    (Connect a socket to the mSQL server IP=$IP)
<! if ( $sock < 0 )  {
       error("ERROR when connecting to msqld : $ERRMSG\n");
       exit(1);
   }
>   
<! if ( msqlSelectDB($sock,"addr")<0)  {  (select the database)
      echo("ERROR when selecting : $ERRMSG\n"); 
      exit(1);
   }         
>
<!-- Test the query parameters and create the resulting table ---------------->
<!  if ( $imageid != "" ) {   (SQL statement)
      if (msqlQuery($sock,
           "SELECT imageid,owner,title,imgdate,isocode,location,
                 source,category,span,comment 
                FROM image 
                WHERE imageid LIKE '$imageid' ") < 0)  
      {
        echo("ERROR select addr-image $ERRMSG\n");
        exit(1);
      }
    }  
  if ( $location != "" ) { (... similar SQL statement, based on location...)  }
  if ( $comment != "" )  { (... similar SQL statement, based on comment...)   }  
>    
<!-- Here w3-msql displays all the data retrieved from the query -->
<!  $res=msqlStoreResult(); (store the resulting query into an array of records)
    $row=msqlFetchRow($res);   (fetch the first row into a field array)
    printf("<TABLE>\n");
    (---------------------------------------- LOOP for each found record -----)
    while (#$row > 0 )       { 
      $id=strseg($row[0],4,12);
      $owner=$row[1];
      ....
        ....
      $comment=$row[10];
      printf("<TR><TD><A HREF=/img/$owner/$year/$row[0]>");  (the image link)
      printf("<IMG SRC=/img/$owner/$year/$tumb></A>\n");  (a displayed tumbnail)
      printf("<TD>Owner=$owner <B>$title</B> ($cdate)<br>$row[0]");
      printf("<TD>$isocode: $location");
      printf("<TD>$source, $category, $span");
      printf("<TD>$comment\n");
      $row = msqlFetchRow($res);
    }   (----------------------------------------- end of loop --------------)
    printf("</TABLE>");
    msqlFreeResult($res);    ( FREE THE ALLOCATED MEMORY )
>
</BODY>
</HTML>