metaSQL.anubis 145 KB

Edit Raw Blame History


                                  The Anubis project

                          Interfacing a database with Anubis


   Author: Alain Prouté (May-July 2013)


   This file contains a 'metaprogram', that is to say a program which generates an
   Anubis source file to be part of your own program. It also contains directly
   usable functions.

   This system is independant of the particular database software you are using.
   Nevertheless, it needs some database dependant stuff, but all this stuff is gathered
   into a single datum (object) of type:

public type MetaSQL_Specific:...   (an essentially opaque type)

   This type is independant of the database software. Only the datum of this type
   produced by the function 'metaSQL_make_specific' is dependant on the database software.
   Such a function can be found for example in 'metaSQL_Postgres.anubis'.

   If you use this metaprogram, you can use a database with only an approximate knowledge
   of the SQL language. Nevertheless, you need to have basic notions about databases, and
   in particular about how to design well behaved databases.

   In an Anubis program, a row of data comming from a SELECT request should be a single datum whose
   type has only one alternative within which each component corresponds to a datum of this
   row. This is of course not difficult to transform a row got from a network connection
   with a database into such a single datum. However, this transformation may encounter
   many problems. For example, we may have a transmission problem (network error), a
   particular datum cannot be converted into the required type (for example, a datum
   which must be an integer cannot be converted using 'decimal_scan'), etc. As a consequence,
   writing down safe and complete translation functions in Anubis, taking into account
   all possible errors (even if they are not likely to occur) is a huge and boring work.
   One of the purposes of this metaprogram is to generate these functions automatically from a
   formal description of the database and of the queries you want to execute.

   This metaprogram also provides tools for initializing the database or automatically
   modifying it in case you change its description. It also provides a web interface
   for administering your database.

   This metaprogram also provides other avantages. It is able to check that queries
   satisfy some required conditions (for example, that 'INSERT' gets an actual value for
   each NOT NULL column), so that the corresponding error will not happen at execution time.
   It also checks properties of the database description itself. The list of verifications
   provided by this program is given below.

   Using this metaprogram puts to some extent the use of the database under the control
   of the Anubis compiler. For example, if you change the attributes of a column by adding
   the condition UNIQUE, it may be the case that the return type of some queries is changed.
   This will of course be detected by the Anubis compiler when compiling your own program if
   it has not been modified accordingly. Hence, you get more safety than if your Anubis program
   does not know the precise structure of your database. You will discover other features of
   metaSQL by using it.

   Enjoy !


   ---------------- Table of Contents ------------------------------------------------

   *** (0) Before you read the sequel, make an experiment.

   *** (1) How it works.

   *** (2) Classifying SQL commands.

   *** (3) Formal description of the database.
      *** (3.1) 'MetaSQL_Type' (data types of table columns).
      *** (3.2) 'MaybeNull'.
      *** (3.3) 'MetaSQL_Prepared' (values prepared for being sent to the database)
      *** (3.4) 'MetaSQL_Attr' (attributes of columns).
      *** (3.5) 'MetaSQL_PrimKey' (chosing to have a primary key column in a table).
      *** (3.6) 'MetaSQL_ColStruct' (describing a column in a table).
      *** (3.7) 'MetaSQL_TabStruct' (describing a table).
      *** (3.8) 'MetaSQL_DBStruct' (describing a whole database).

   *** (4) Formal description of queries (metaqueries).
      *** (4.1) 'MetaSQL_Column' (naming columns in WHERE clauses).
      *** (4.2) 'MetaSQL_Var' (introducing variables in WHERE clauses).
      *** (4.3) 'MetaSQL_Where' (describing a WHERE clause).
      *** (4.4) 'MetaSQL_Range' (getting only a range of rows in a SELECT query).
      *** (4.5) 'MetaSQL_DML' (describing queries for the DML part of SQL).
      *** (4.6) Convenience functions for SELECT queries.

   *** (5) The function 'make_queries'.

   *** (6) Cursors.

   *** (7) Constructing the 'specific datum'.

   *** (8) Some useful types and tools.
      *** (8.1) 'MetaSQL_Id'.
      *** (8.2) 'MetaSQL_Answer'.
      *** (8.3) Conversion functions.
         *** (8.3.1) Converting 'anubis(...)' to and fro.
         *** (8.3.2) Converting 'binary' and 'binary_or_null' to and fro.
         *** (8.3.3) Converting 'boolean' and 'boolean_or_null' to and fro.
         *** (8.3.4) Converting 'date' and 'date_or_null' to and fro.
         *** (8.3.5) Converting 'foreign' and 'foreign_or_null' to and fro.
         *** (8.3.6) Converting 'integer?' and 'integer?_or_null' to and fro.
         *** (8.3.7) Converting 'char/text' and 'char_or_null/text_or_null' to and fro.

   *** (9) Transactions.

   *** (10) Verifications performed by this metaprogram.
      *** (10.1) Cycles of references in the database.
      *** (10.2) Values provided to _INSERT.
      *** (10.3) Coherence of WHERE clauses.

   *** (11) Creating the database and modifying its structure.

   *** (12) Database administration through the web.

   -----------------------------------------------------------------------------------


   *** (0) Before you read the sequel, make an experiment.

   Copy the file 'library/data_base/metaSQL_example_queries.anubis' into a working directory, and
   compile it. This will generate the file 'metaSQL_example_output.anubis' (in the same
   working directory). Compare both files and you will understand in advance most
   of what is explained in details below.

   You are encouraged to make modifications to your copy of 'metaSQL_example.anubis' and
   see what happens in the generated file.


   *** (1) How it works.

   In order to use this 'metaprogram', create a file named for example 'meta_queries.anubis',
   within which you write what is shown below. It is recommanded to make a directory 'generated'
   within your source directory, where automatically generated files will be put. This helps
   to distinguish manually written Anubis files from automatically generated Anubis files
   (which are not true source files, and which should never be modified by hand).

   --- The file 'meta_queries.anubis' ----------------------------------------------

                                  My Project
               Interfacing my Anubis program with my database.

      Blah blah blah ... (good practice is to comment)

   read data_base/metaSQL.anubis

      --- Description of my database:
   define MetaSQL_DBStruct
      my_database
        =
         ... describe your database here (see below) ...

     --- Description of all my queries:
   define List(MetaSQL_DML)   // DML stands for 'Data Manipulation Language' (a part of SQL)
      my_queries
        =
      [
         ... here is the description of all your queries (see below) ...
      ].

      --- The module that will generate the new Anubis file:
   global define One
      make_my_queries     // you can choose another name for this module
        (
          List(String) args    // actually not used
        ) =
      make_queries // this function is defined in this metaprogram
        (
          "generated/queries.anubis",     // the path of the Anubis file to be generated
          ...                             // other arguments (see below)
        ).

      --- Generate the file 'generated/queries.anubis':
   execute anbexec make_my_queries

      --- Check it:
      This is just for testing that the generated file compiles ok. In any case, this does not
      waste compilation time since during a compilation each file is read only once regardless
      of the number of 'read' of this file.

   read generated/queries.anubis

   --- End of 'meta_queries.anubis' ------------------------------------------------

   After the file 'meta_queries.anubis' is compiled, the file 'generated/queries.anubis'
   exists and may be 'read' by other files in your source directory. You should probably
   put a 'read meta_queries.anubis' close to the beginning of the main file of your program,
   in order to generate 'generated/queries.anubis' before needing to 'read' it.


   *** (2) Classifying SQL commands.

   The SQL language is naturally divided into several sections:

     (1) The structure of the database is setup by SQL commands like CREATE,
         ALTER, DROP, ... applied to tables, indexes, ...

     (2) The access to the database is controled by commands like GRANT, REVOKE, ...

     (3) The everyday manipulation of data is performed by the 4 commands INSERT,
         UPDATE, SELECT and DELETE, with the help of essentially two mecanisms:
         'cursors' (for retrieving large amounts of data in several steps), and
         'transactions' for ensuring the integrity of data manipulation. Cursors
         use the commands DECLARE, MOVE, FETCH and CLOSE, while transactions use
         the commands BEGIN, COMMIT and ROLLBACK.

   The program in this file deals only with points (1) and (3), that is the so-called
   'Data Definition Language', aka 'DDL' and the 'Data Manipulation Language', aka 'DML'.
   Point (2), the 'Data Control Language', aka 'DCL' may be handled in the future.

   In a program using a database (for example a web site), the same SQL command is
   issued many times a day but with different values of its parameters. For example,
   retrieving the row whose 'id' is 5 in the table 'product' may be performed by:

      SELECT * FROM product WHERE id = '5';

   This query will be issued many times a day, but the value of the 'id' will just
   be different almost each time. Hence, such a query can be divided into two parts:
   the 'constant part' and the 'variable part'. Ideally, in Anubis, we should have
   a function of type:

                   (Int id) -> Result(String /* error message*/,Product)

   for executing this query. Of course, this function contains several things
   within its microcontext, in particular, another function (the 'query function'),
   itself containing in its own microcontext a network connection to the database.
   Tools for easily constructing this kind of function are automatically generated
   by this metaprogram.

   In order to generate such tools, metaSQL needs two things: a formal
   description of the database and a formal description of the queries.


   *** (3) Formal description of the database.

      *** (3.1) 'MetaSQL_Type' (data types of table columns).

   Sorts of data we can find in the database are listed below. We consider that the
   fact of being 'nullable' is a matter of type, hence the following definition:


public type MetaSQL_Type:
   anubis(String _T),                   // the datum of type _T is serialized and base64 encoded
   binary,                              // contains a byte array
   binary_or_null,                      // contains a byte array or NULL
   boolean,                             // true or false
   boolean_or_null,                     // true or false or NULL
   date,                                // date with the precision of the day
   date_or_null,                        // date with the precision of the day or NULL
   time,                                // time with the precision of the second
   time_or_null,                        // time with the precision of the second or NULL
   datetime,                            // datetime with the precision of the second
   datetime_or_null,                    // datetime with the precision of the second or NULL
   foreign(String table_name),          // foreign key to 'id' in another (or same) table.
   foreign_or_null(String table_name),  // foreign key to 'id' in another (or same) table or NULL
   integer16,                           // 16 bits integer
   integer16_or_null,                   // 16 bits integer or NULL
   integer32,                           // 32 bits integer
   integer32_or_null,                   // 32 bits integer or NULL
   integer,                             // integer of arbitrary size
   integer_or_null,                     // integer of arbitrary size or NULL
   char(Int size),                      // text of maximal size 'size' (number of characters)
   char_or_null(Int size),              // text of maximal size 'size' (number of characters) or NULL
   text,                                // text of variable size
   text_or_null.                        // text of variable size or NULL

   From the point of view of your Anubis program, these data will be of types:

   Name      | Anubis type       | Type within the database
   ----------+-------------------+--------------------------------------------------
   anubis    | T (serializable)  | text (base64 encoded serialization)
   binary    | ByteArray         | text (the byte array base64 encoded)
   boolean   | Bool              | boolean
   date      | String            | text (date in ISO-8601 format: yyyy-mm-dd)
   time      | String            | text (date in ISO-8601 format: hh:mm:ss)
   datetime  | String            | text (date in ISO-8601 format: yyyy-mm-dd hh:mm:ss)
   foreign   | MetaSQL_Id        | 64 bits integer
   integer16 | Word16            | 16 bits integer
   integer32 | Word32            | 32 bits integer
   integer   | Int               | arbitrary size integer (numeric)
   char      | String            | max size text
   text      | String            | arbitrary size text

   and similarly for nullable types, except that the Anubis type is wrapped into a 'MaybeNull(...)'.

      *** (3.2) 'MaybeNull'.

   This type scheme is isomorphic to 'Maybe', and is used for representing data which
   can have the value 'NULL'. We don't use 'Maybe' for this purpose because
   we want to avoid the misleading 'Maybe(Maybe(...))'. Instead, we will have
   sometimes 'Maybe(MaybeNull(...))', so that 'null' means 'NULL', and 'failure'
   means an error.

public type MaybeNull($T):
   null,
   not_null($T value).

   Conversion tools between date, time and datetime in ISO-8601 format and 'Int'
   (number of seconds since the epoch) are provided in 'library/tools/ISO-8601.anubis'.


      *** (3.3) 'MetaSQL_Prepared' (values prepared for being sent to the database)

   Data must be formated (prepared) as appropriate strings in order to be inserted into queries.
   Such prepared strings are not of type String but of type:

public type MetaSQL_Prepared:...     (an opaque type)

   This is for avoiding any confusion between a prepared string and any other kind of string.


      *** (3.4) 'MetaSQL_Attr' (attributes of columns).

   Possible attributes of columns: (each column has a list of such attributes)

public type MetaSQL_Attr:
   unique,                       // by default, the values in a column are not required to be all different
   indexed,                      // by default, a column is not indexed
   default(MetaSQL_Prepared).    // default value (used in case of creation of a NON NULL column
                                 //   in a table which already contains some rows)

   Of course, 'NOT NULL' is not an alternative of 'MetaSQL_Attr' because it is already coded into the
   database type itself (type 'MetaSQL_Type' above).


      *** (3.5) 'MetaSQL_PrimKey' (chosing to have a primary key column in a table).

   'MetaSQL_PrimKey': does the table have a primary key or not ?

public type MetaSQL_PrimKey:
   with_pk,            // with 64 bits primary key
   no_pk.              // without primary key

   Note: A primary key column is always named 'id', it is of type MetaSQL_Id as far as Anubis
         is concerned, and has attributes 'UNIQUE', 'NOT NULL' and 'INDEXED'. Futhermore, it
         is a serializing field, i.e. each new row inserted in the table receives a new 'id'
         automatically (by the database itself). In particular, 'id' must not be given an
         explicit value in an INSERT command.

         'id' should be present in the case of a table of 'entities', even if there is another
         column with attribute 'unique'. For example, in a web site, where users may be
         registered, they can be uniquely identified by their email address. Nevertheless, despite
         the fact that the column 'email' has attribute 'unique', you should still have an 'id'
         column. Of course, this allows users to change their email address, but there are more
         general reasons. The two columns 'id' and 'email' do not have equivalent behaviors as
         demonstrated below. Indeed, if you get an email address via the network (someone filling
         up a registration form for example) you have to check if this address is already in the
         database. To that end, you use a SELECT command, with condition email = ..., and the
         number of rows in the answer can be:

              0    the email address is not yet registered
              1    the email address is already registered

         Notice that none of these is an error. They are both normal behaviors.
         Now, if you have the 'id' of a user at hand, this 'id' comes necessarily from the database.
         This means that the corresponding row always exists, and the number of rows returned by
         a SELECT command, with condition id = ... must always return one and only one row. The case
         it returns 0 row is now an error. (In case you delete a row, you must of course abandon
         the corresponding id which becomes non significant at that moment.)

         Assuming that the Anubis type of a row is ROW (you choose this name yourself, but not the
         definition of the type; see below), any description of a SELECT command with condition of
         the form id = ... will generate a function returning (if no error occurs) a datum of type
         ROW. On the contrary, any description of a SELECT command with a condition of a different
         form will return a datum of type Maybe(Row) if the system knows the answer is unique (but
         may not exist), and of type List(ROW) otherwise. Have a look at 'metaSQL_example.anubis'
         and at the corresponding generated file to check this affirmation.


      *** (3.6) 'MetaSQL_ColStruct' (describing a column in a table).

   Description of a column:

public type MetaSQL_ColStruct:
   col (String               name,               // name of ordinary column (i.e. all but 'id')
        MetaSQL_Type         type,
        List(MetaSQL_Attr)   attributes).


      *** (3.7) 'MetaSQL_TabStruct' (describing a table).

   Description of a table:

public type MetaSQL_TabStruct:
   table (String                     name,       // name of the table
          MetaSQL_PrimKey            pk,         // with or without primary key column
          List(MetaSQL_ColStruct)    columns).   // columns other than the primary key column (if any)


      *** (3.8) 'MetaSQL_DBStruct' (describing a whole database).

   Description of a whole database:

public type MetaSQL_DBStruct:
   database  (String                      name,       // name of the database
              List(MetaSQL_TabStruct)     tables).    // list of all tables in the database

   Note: indexes are described via the attributes and will be generated accordingly.


   *** (4) Formal description of queries (metaqueries).

   SQL commands from the DML are represented by the following type:

public type MetaSQL_DML:...         (defined below)

   This type has alternatives corresponding to INSERT, SELECT, UPDATE and DELETE, but the
   components need auxiliary types, that we now define.


      *** (4.1) 'MetaSQL_Column' (naming columns in WHERE clauses).

   A 'SELECT' query may refer to several columns not all in the same table ('joins').
   Hence, we must define the notion of 'selection of columns'. This is a list of data of type:

public type MetaSQL_Column:
   String table_name.String column_name.

   For example,  "product"."price"  is (the name of) a column (the column 'price' in the table 'product')
   Note: be careful to avoid any space around the dot, because in Anubis, a dot followed by a space
   (used as end of paragraph or in the list notation '[_ . _]') is not the same operator as a dot not
   followed by a space (used here).

   However, you may also just want to 'count' the number of rows in the result. Hence, the
   following type:

public type MetaSQL_Selection:
   columns  (List(MetaSQL_Column)     selected_columns),     // get the rows
   count    (List(MetaSQL_Column)     selected_columns).     // get only the  number of rows

   The same one in case there is only one table involved in the query:

public type MetaSQL_Selection1:
   columns  (List(String)             selected_columns),     // get the rows
   count    (List(String)             selected_columns).     // get only the  number of rows

      *** (4.2) 'MetaSQL_Var' (introducing variables in WHERE clauses).

   Most queries include a condition, also called a 'WHERE clause', which contains the 'variables'
   taking different values at each call.

public type MetaSQL_Var:       // variables for queries
   var(String name).


      *** (4.3) 'MetaSQL_Where' (describing a WHERE clause).

public type MetaSQL_Where:     // formal description of a 'WHERE' clause
   MetaSQL_Column      = MetaSQL_Var,              // equality of values
   MetaSQL_Column      = MetaSQL_Column,           // useful if in two distinct tables
   MetaSQL_Column      < MetaSQL_Var,              // comparison for numbers, dates, ...
   MetaSQL_Var         < MetaSQL_Column,
   MetaSQL_Column     =< MetaSQL_Var,
   MetaSQL_Var        =< MetaSQL_Column,
   is_null         (MetaSQL_Column),               // 'is null' test
   is_not_null     (MetaSQL_Column),               // 'is not null' test
   contains        (MetaSQL_Column, MetaSQL_Var),  // check if the datum contains a given substring
   true,
   false,
   MetaSQL_Where       & MetaSQL_Where,            // 'AND'
   MetaSQL_Where       | MetaSQL_Where,            // 'OR'
                       ~ MetaSQL_Where.            // 'NOT'

   The same one, but with column names without table name (used when there is only one table
   involved in a SELECT). Actually, this is just to put some syntactic sugar.

public type MetaSQL_Where1:
   String              = MetaSQL_Var,              // equality of values
   String              < MetaSQL_Var,              // comparison for numbers, dates, ...
   MetaSQL_Var         < String,
   String             =< MetaSQL_Var,
   MetaSQL_Var        =< String,
   is_null         (String),                       // 'is null' test
   is_not_null     (String),                       // 'is not null' test
   contains        (String,MetaSQL_Var),           // check if the datum contains a given substring
   true,
   false,
   MetaSQL_Where1      & MetaSQL_Where1,           // 'AND'
   MetaSQL_Where1      | MetaSQL_Where1,           // 'OR'
                       ~ MetaSQL_Where1.           // 'NOT'


      *** (4.4) 'MetaSQL_Range' (getting only a range of rows in a SELECT query).

   In a SELECT statement, you may not want to get all rows matching the condition but only
   a number of rows starting at some row, i.e. a 'range' of rows. Notice that using this
   is less efficient than using cursors, since the rows will be tested from the beginning
   at each call. Also, it is not warranted that the result of the two methods is the same
   one, if rows are updated or inserted between two calls.

public type MetaSQL_Range:
   all,                                 // get all matching rows
   range(Int start, Int how_many).      // get only a range of rows

   The resulting table can be ordered. This is acheived by transimitting a list of 'MetaSQL_Order_by'.

public type MetaSQL_Order:
   asc  (MetaSQL_Column   column),
   desc (MetaSQL_Column   column).

public type MetaSQL_Order1:
   asc  (String           column),
   desc (String           column).

   'asc' is for 'ascending' and 'desc' for descending. The resulting order is the lexicographic order
   corresponding to this list.


      *** (4.5) 'MetaSQL_DML' (describing queries for the DML part of SQL).

   Below is the definition of the type 'MetaSQL_DML'. Each alternative has a 'query_name' component
   which becomes the name of the function to be called for executing the query. Things which seem
   to be missing in this definition (like form example the values to be inserted in the case of
   _INSERT) become arguments to the generated function (typed according to the description
   of the database).

public type MetaSQL_DML:
   _INSERT
     (
       String               query_name,
       String               table_name,       // the name of the table into which a row must be inserted
       List(String)         columns_names     // all 'NOT NULL' columns (except 'id') must be present
                                              //   and 'id' must not be present
       // values are provided as arguments to the generated function
     ),
   _SELECT
     (
       String                    query_name,
       String                    output_type_name, // name for the output type (for rows) of the generated function
       MetaSQL_Selection         selection,        // a selection of columns from several tables
       MetaSQL_Where             condition,
       MetaSQL_Range             range,
       List(MetaSQL_Order)       order
       // some convenience variants of this are provided (see below)
     ),
   _UPDATE
     (
       String               query_name,
       String               table_name,
       List(String)         column_names,
       MetaSQL_Where1       condition
       // values are provided as arguments to the generated function
     ),
   _DELETE
     (
       String               query_name,
       String               table_name,
       MetaSQL_Where1       condition
     ).


   Note: the names of the queries need not be unique provided the generated functions may be
   distinguished by their types.

   Note: the name of the output type for rows in _SELECT queries need not be unique provided that
   any two usages of this name correspond to types with the same components. In other words,
   if you use the same name for the output type in two _SELECT queries, ensure that the
   selections of columns (3rd argument) are identical. Anyway, if it is not the case, this
   will be detected by the Anubis compiler at the compilation of the generated file.


      *** (4.6) Convenience functions for SELECT queries.

   In case there is only one table used in a SELECT, you can use this more handy function,
   so that, for example, you can write "price" instead of "product"."price".

public define MetaSQL_DML
   _SELECT
     (
       String               query_name,
       String               output_type_name,
       String               table_name,
       MetaSQL_Selection1   selection,
       MetaSQL_Where1       condition,
       MetaSQL_Range        range
     ).

public define MetaSQL_DML
   _SELECT
     (
       String                   query_name,
       String                   output_type_name,
       String                   table_name,
       MetaSQL_Selection1       selection,
       MetaSQL_Where1           condition,
       List(MetaSQL_Order1)     order
     ).

   The same one without the 'range' argument (assumed to be 'all'):

public define MetaSQL_DML
   _SELECT
     (
       String               query_name,
       String               output_type_name,
       String               table_name,
       MetaSQL_Selection1   selection,
       MetaSQL_Where1       condition
     ).


   *** (5) The function 'make_queries'.

   The function 'make_queries' (the main function for generating the target file)
   is declared as follows:

public define One
   make_queries
     (
       String                  target_file_path,     // for example "generated/queries.anubis"
       List(String)            files_to_be_read,
       List(MetaSQL_DML)       metaqueries,
       MetaSQL_DBStruct        database_description
     ).

   The arguments are:
      (1) the path of the file to be generated,
      (2) a list of file paths, each of which will generate a 'read ...' at the beginning
          of the generated file (just after the standard 'reads'),
      (3) the description of your queries,
      (4) the description of your database.


   *** (6) Cursors.

   Not yet implemented in metaSQL.


   *** (7) Constructing the 'specific datum'.

   If your database software is Postgresql, just write:

   read data_base/metaSQL_Postgres.anubis

      ...
      with specific = metaSQL_make_specific(...),

   in order to construct the specific datum. At the time I'm writing this, only Postgresql is
   available. I hope people will soon provide interfaces to other database softwares.

   If you want to provide one, have a look at metaSQL_Postgres.anubis, and write the almost
   same program for the other database software. More explanations to be found there.


   *** (8) Some useful types and tools.


      *** (8.1) 'MetaSQL_Id'.

   A datum of this type is the identifier of a row in a table of the database.

public type MetaSQL_Id:...      (an opaque type)


      *** (8.2) 'MetaSQL_Answer'.

   The answer to a query as returned by a database software can be:

      - an error message,
      - an aknowledgment (for example, the result of an INSERT),
      - a table (for example, the result of a SELECT),
      - an information message.

    Hence, we define the following type for handling the answers returned by your database software:

public type MetaSQL_Answer:
   error            (String                             message),
   command_complete,
   table            (List(List(MaybeNull(ByteArray)))   rows),
   informations     (String                             infos).

   When the answer is a table, this is just a list of rows, and each row a list of data. Each
   datum can be NULL (null) or NOT NULL (not_null(...)). Hence this definition.

   The functions generated by the metaprogram transform such an answer into a datum easier to
   use in your Anubis program.


      *** (8.3) Conversion functions.

   The system needs two kinds of conversion functions.

   The first kind 'prepares' data for insertion into queries. Their name begins by
   'prepare_' and they all have 'MetaSQL_Prepared'as their return type. You need to
   use one of them if you have to put the attribute 'default(...)' to a column, because
   the operand of default is of type 'MetaSQL_Prepared'.

   The second kind is used for transforming byte arrays received from the database
   into data of various Anubis types. Normally you don't have to use them directly.
   Their names are prefixed by 'convert_'.


         *** (8.3.1) Converting 'anubis(...)' to and fro.

   The type $T must be serializable.

public define MetaSQL_Prepared        prepare_anubis                ($T datum).
public define Maybe($T)               convert_to_anubis             (MaybeNull(ByteArray) mbnb).


         *** (8.3.2) Converting 'binary' and 'binary_or_null' to and fro.

   The communication with the database is assumed to be of type 'text' not of type
   'binary'. Hence, byte arrays are converted into strings via base64 before being
   inserted into a SQL query (hence, they are stored as character strings in the
   database).

public define MetaSQL_Prepared         prepare_binary                (ByteArray b).
public define MetaSQL_Prepared         prepare_binary_or_null        (MaybeNull(ByteArray) b).

   Conversely, 'binary' data arrive in the form of 'MaybeNull(ByteArray)'. In the case
   of 'null' the result is an error for 'binary' and NULL for 'binary_or_null'. Otherwise,
   the result is concverted to a string, and base64 decoded, which yields a byte array
   again.

public define Maybe(ByteArray)         convert_to_binary             (MaybeNull(ByteArray) b).
public define MaybeNull(ByteArray)     convert_to_binary_or_null     (MaybeNull(ByteArray) b).


         *** (8.3.3) Converting 'boolean' and 'boolean_or_null' to and fro.

   Booleans are transformed into the keywords TRUE and FALSE (official SQL).

public define MetaSQL_Prepared         prepare_boolean               (Bool b).
public define MetaSQL_Prepared         prepare_boolean_or_null       (MaybeNull(Bool) b).

   Conversely, more forms of booleans are recognized, such as 't', '1', 'y' for TRUE.
   The output conversion functions are case insensitive.

public define Maybe(Bool)              convert_to_boolean            (MaybeNull(ByteArray) b).
public define MaybeNull(Bool)          convert_to_boolean_or_null    (MaybeNull(ByteArray) b).


         *** (8.3.4) Converting 'date' and 'date_or_null' to and fro.

   Dates (of type 'String' in Anubis) are stored into the database as ISO-8601 strings,
   for example:  2013-05-24 09:34:23

   We provide functions for conversion into Int (number of seconds since the 'epoch') and conversely
   in 'library/tools/ISO-8601.anubis'.

public define MetaSQL_Prepared         prepare_date                  (String b).
public define MetaSQL_Prepared         prepare_date_or_null          (MaybeNull(String) b).

public define Maybe(String)            convert_to_date               (MaybeNull(ByteArray) b).
public define MaybeNull(String)        convert_to_date_or_null       (MaybeNull(ByteArray) b).


         *** (8.3.5) Converting 'foreign' and 'foreign_or_null' to and fro.

public define MetaSQL_Prepared         prepare_foreign               (MetaSQL_Id  i).
public define MetaSQL_Prepared         prepare_foreign_or_null       (MaybeNull(MetaSQL_Id) i).

public define Maybe(MetaSQL_Id)        convert_to_foreign            (MaybeNull(ByteArray) b).
public define MaybeNull(MetaSQL_Id)    convert_to_foreign_or_null    (MaybeNull(ByteArray) b).


         *** (8.3.6) Converting 'integer?' and 'integer?_or_null' to and fro.

   The next functions are used for integer16, integer32 and integer.

public define MetaSQL_Prepared         prepare_integer               (Int f).
public define MetaSQL_Prepared         prepare_integer_or_null       (MaybeNull(Int) f).

public define Maybe(Int)               convert_to_integer            (MaybeNull(ByteArray) b).
public define MaybeNull(Int)           convert_to_integer_or_null    (MaybeNull(ByteArray) b).


         *** (8.3.7) Converting 'char/text' and 'char_or_null/text_or_null' to and fro.

   The same functions are used for 'text/text_or_null' and 'vartext/vartext_or_null'.

   Litteral texts must be 'prepared' before they can be included into
   SQL commands (this amounts to doubling the single quotes).

public define MetaSQL_Prepared         prepare_text                  (String t).
public define MetaSQL_Prepared         prepare_text_or_null          (MaybeNull(String) t).

   Conversely, texts arrive from the database in the form of byte arrays which must be
   converted to strings. The two functions for converting to 'text' and to 'text_or_null'
   are almost the same one. The only difference is that 'null' is interpreted as an error in
   the case of 'text'.

public define Maybe(String)            convert_to_text               (MaybeNull(ByteArray) mbba).
public define MaybeNull(String)        convert_to_text_or_null       (MaybeNull(ByteArray) mbba).


   *** (9) Transactions.

   Transactions are absolutely required in some circumstances. They allow to consider a sequence of
   several queries as an 'atomic' operation, that is to say an operation which may either have no effect
   at all on the database it it fails, or which is correctly completed if it succeeds. In other words,
   executing the sequence of queries only partially becomes impossible, so that the integrity of the
   database is warranted, and other users of the database can see the final result only (or nothing at
   all if the sequence fails at some point).

   Define a function, say 'sequence', of type '(String -> MetaSQL_Answer) -> Result(String,T)' where
   the type T is the type of the result you want to get after the sequence of queries is successfully
   executed. The function 'sequence' is supposed to send several queries to the database. It must return the
   value 'error(msg)' (where 'msg' is an error message) if the sequence fails. Otherwise, it must return
   'ok(result)', where 'result' is the wanted result.

   In order to wrap this sequence of queries into a transaction just write:

               transaction(qf)(sequence)

   where 'qf' is the 'query function' (of type String -> MetaSQL_Answer). It is available as the component
   named 'query_function' of the 'specific datum' (of type MetaSQL_Specific) constructed by
   metaSQL_make_specific. For example, you can write instead of the above (where 'specific' is the
   'specific datum'):

         with qf = query_function(specific),
               transaction(qf)(sequence)

   or like this if you prefer:

         with qf = specific.query_function,
                sequence.(qf.transaction)

   (or a mix of both).

   The term 'transaction(qf)(sequence)' is of type 'Result(String,T)'. If it returns 'error(msg)',
   the database was not affected by its execution. If it returns 'ok(result)', the complete
   sequence of queries was successfully executed, and you have the result at hand.

   Notice that the function 'sequence' should decide to send a given query only if the previous ones
   in the sequence are successful. This is the reason why the sequence cannot be just a list of
   queries (or a list of descriptions of queries), but must be a function written outside the description
   of queries, and using all the expressiveness of the language.

   The function 'transaction' is declared as follows:

public define ((String -> MetaSQL_Answer) -> Result(String,$T)) -> Result(String,$T)
   transaction
     (
       String -> MetaSQL_Answer   query_function
     ).

   It is of type   Q -> ((Q -> R) -> R)   where Q is the type of the query function and R the type
   'Result(String,T)'. So, it takes two successive arguments. The first one of type Q is the query function
   which is needed to have access to the database, and the second one of type Q -> R is the 'sequence' function.
   What 'transaction' does is just:

     - send a 'BEGIN TRANSACTION' using the query function,
     - apply the sequence function (of type Q -> R) to the query function (of type Q); this sends the
       sequence of queries and yields a result of type T,
     - depending on the result of the previous step ('error' or 'ok'), send either a 'ROLLBACK' or a
       'COMMIT' to the database (using the query_function),
     - return the appropriate result.


   *** (10) Verifications performed by this metaprogram.

   This metaprogram check that all the conditions below are fulfilled. If it is not the case,
   error messages are sent (and the target Anubis file is not generated).

      *** (10.1) Cycles of references in the database.

   The foreign(_) data type implements references between tables. These references may create loops
   (or cycles), including loops of length 1 (self references) in the case the table refers to itself
   (which is useful for making lists). Since the tables are in a given order in your description of
   the database, there are 3 sorts of references: forwards references, self references and backwards
   references. It is a rule that forwards and self references must be nullable, while backwards
   references may be either nullable or not, but preferably not nullable.

   Notice that if you have a not null self reference in a table, it is impossible to insert any
   row in this table. Indeed, since the reference is not null, it must be provided as an argument
   which is impossible when there is still no row at all in the table.

   Actually this rule ensures that the references between your tables are well-founded.


      *** (10.2) Values provided to _INSERT.

   An INSERT command must provide values for all non null columns. Thus, the list of column names
   must contain the names of all non null columns. Furthermore, the names must be in the same order
   as in the description of the table, and must not appear twice in the list. The name 'id' must
   also not appear in the list since the database itself generates the value for this column.


      *** (10.3) Coherence of WHERE clauses.

   WHERE clauses must satisfy some (obvious) conditions, such as the fact that comparisons must apply
   to type for which they are meaningful. Also the 'is_null' and 'is_not_null' tests can only apply
   to nullable columns.


   *** (11) Creating the database and modifying its structure.

   Since your program contains a detailed description of your database, it is possible to automate
   the creation of the database and the modifications of its structure. Use the function 'database_check'
   for this purpose. It should be executed each time you restart your program. This function takes
   the following arguments:

      - the specific datum (in order to access the database),
      - the description of the database.

   It returns a result of type 'Result(List(String),One)'. In other words, if something fails,
   you get a list of error messages, otherwise execution is transparent.

   'database_check' first of all checks the coherence of your database description, i.e. it
   executes the verifications explained in section (9).

   If the database does not exist, 'database_check' creates it. It also creates, within the
   database, a special table (whose name is 'MetaSQL_Description').
   In this table, 'database_check' records the structure of the database, in other words the serialization
   of your database description. If you stop your program and change the description of the database,
   'database_check' will be able to compare the current description with the previous one. As a
   consequence, it will be able to modify the structure of the database. Such modifications may involve:

     - creating or deleting a table,
     - creating or deleting a column in a table,
     - modifying the type and attributes of a column,
     - creating or deleting an index.

   Of course, the previous structure of the database and data in deleted tables and columns are lost
   during these operations. During these operations, 'database_check' locks the database (if possible).
   After these operations are completed, 'database_check' updates the content of the special table
   'MetaSQL_Description'.

   Note: It is possible to change the structure of the database without stopping your program. Indeed,
   instead of including the description of the database within your source files, store a serialization
   of it into a file. This file can be read and unserialized (on demand) from within your running program.
   At that moment, a 'database_check' could be executed, and the database will be modified. If your program
   is a web site, it is likely that you will have to forbid normal client connections during that time.


   *** (12) Database administration through the web.

   The file 'library/data_base/metaSQL_admin.anubis' contains a function 'web_admin' of type:

                            (One -> MetaSQL_Specific) -> Web_Site

   (where the type 'Web_Site' is described in 'library/web/making_a_web_site.anubis'). You can
   include this web site in your program. It is suitable for administering your database. The argument
   of type 'One -> MetaSQL_Specific' must be a function constructing a specific datum each time it is
   applied to 'unique'.


   --- That's all for the public part !----------------------------------------------------------------


read tools/basis.anubis
read tools/base64.anubis
read system/string.anubis
read tools/ISO-8601.anubis


   *** [1] Tools.

      *** [1.1] Concatenating strings which may not exist.

   The concatenation function  '+'  for strings is defined in 'tools/basis.anubis'. Here we define
   an extension of it to strings which may not exist (i.e. data of type 'Maybe(String)'). Of course,
   the result is always of type 'Maybe(String)'.

define macro Maybe(String)
   Maybe(String) s + String t
     =
   if s is
     {
       failure then failure,
       success(s1) then success(s1+t)
     }.

define macro Maybe(String)
   String s + Maybe(String) t
     =
   if t is
     {
       failure then failure,
       success(t1) then success(s+t1)
     }.

define macro Maybe(String)
   Maybe(String) s + Maybe(String) t
     =
   if s is
     {
       failure then failure,
       success(s1) then if t is
         {
           failure then failure,
           success(t1) then success(s1+t1)
         }
     }.


      *** [1.2] A test for knowing if a database type is 'nullable'.

define Bool
   nullable
     (
       MetaSQL_Type t
     ) =
   if t is
     {
       anubis(_)                           then false,
       binary                              then false,
       binary_or_null                      then true,
       boolean                             then false,
       boolean_or_null                     then true,
       date                                then false,
       date_or_null                        then true,
       time                                then false,
       time_or_null                        then true,
       datetime                            then false,
       datetime_or_null                    then true,
       foreign(String table_name)          then false,
       foreign_or_null(String table_name)  then true,
       integer16                           then false,
       integer16_or_null                   then true,
       integer32                           then false,
       integer32_or_null                   then true,
       integer                             then false,
       integer_or_null                     then true,
       char(Int size)                      then false,
       char_or_null(Int size)              then true,
       text                                then false,
       text_or_null                        then true
     }.


      *** [1.3] Formatting error messages sent by this metaprogram.

define String
   error
     (
       String msg
     ) =
   "*** Error: "+msg+"\n".


      *** [1.4] Formatting error messages to be sent by the generated program.

define String
   make_error
     (
       String msg
     ) =
   "error(\""+msg+" in \"+__FILE__+\":\"+to_decimal(__LINE__))".


      *** [1.5] Finding a table by its name.

public define Maybe(MetaSQL_TabStruct)
   find_table
     (
       String                    table_name,
       List(MetaSQL_TabStruct)   tables
     ) =
   if tables is
     {
       [ ] then print(error("Table '"+table_name+"' not found.")); failure,
       [t1 . others] then
         if t1 is table(n,_,_) then
         if n = table_name
         then success(t1)
         else find_table(table_name,others)
     }.


      *** [1.6] Finding a column.

   The next function is public because used in 'web/metaWEB_Form.anubis'.

public define Maybe(MetaSQL_ColStruct)
   find_column
     (
       String                     col_name,
       String                     table_name,
       List(MetaSQL_ColStruct)    tab_cols
     ) =
   if tab_cols is
     {
       [ ] then print(error("There is no column '"+col_name+"' in table '"+table_name+"'.")); failure,
       [c . others] then
         if name(c) = col_name
         then success(c)
         else find_column(col_name,table_name,others)
     }.


define Maybe(MetaSQL_ColStruct)
   find_column
     (
       String                     col_name,
       MetaSQL_TabStruct          tab
     ) =
   if tab is table(name,pk,cols) then
   if pk is
     {
       with_pk then if col_name = "id"
                    then success(col("id",foreign(name),[]))
                    else find_column(col_name,name,cols),
       no_pk then find_column(col_name,name,cols)
     }.


      *** [1.7] _SELECT with only one table.

   'selection' must be converted from MetaSQL_Selection1 to MetaSQL_Selection and
   'condition' must be converted from MetaSQL_Where1 to MetaSQL_Where.

define MetaSQL_Selection
   convert
     (
       String               table_name,
       MetaSQL_Selection1   s
     ) =
   if s is
     {
       columns(l)   then columns(map((String cn) |-> table_name.cn,l))
       count(l)     then count(map((String cn) |-> table_name.cn,l))
     }.

define List(MetaSQL_Order)
   convert
   (
     String                 table_name,
     List(MetaSQL_Order1)   order
   ) =
   if order is
   {
     [ ] then [ ],
     [h . t] then if h is
     {
       asc(col)    then [asc(table_name.col)  . convert(table_name,t)]
       desc(col)   then [desc(table_name.col) . convert(table_name,t)]
     }
   }.

define MetaSQL_Where
   convert
     (
       String          table_name,
       MetaSQL_Where1  w
     ) =
   if w is
     {
       s = v             then table_name.s = v,
       s < v             then table_name.s < v,
       v < s             then v < table_name.s,
       s =< v            then table_name.s =< v,
       v =< s            then v =< table_name.s,
       is_null(s)        then is_null(table_name.s),
       is_not_null(s)    then is_not_null(table_name.s),
       contains(s,v)     then contains(table_name.s,v),
       true              then true,
       false             then false,
       w1 & w2           then convert(table_name,w1) & convert(table_name,w2),
       w1 | w2           then convert(table_name,w1) | convert(table_name,w2),
          ~ w1           then ~convert(table_name,w1)
     }.

public define MetaSQL_DML
   _SELECT
     (
       String                    query_name,
       String                    output_type_name,
       String                    table_name,
       MetaSQL_Selection1        selection,
       MetaSQL_Where1            condition,
       MetaSQL_Range             range
     ) =
   _SELECT(query_name,
           output_type_name,
           convert(table_name,selection),
           convert(table_name,condition),
           range,
           []).

public define MetaSQL_DML
   _SELECT
     (
       String                    query_name,
       String                    output_type_name,
       String                    table_name,
       MetaSQL_Selection1        selection,
       MetaSQL_Where1            condition,
       List(MetaSQL_Order1)      order
     ) =
   _SELECT(query_name,
           output_type_name,
           convert(table_name,selection),
           convert(table_name,condition),
           all,
           convert(table_name,order)).

   The same one without the 'range' argument:

public define MetaSQL_DML
   _SELECT
     (
       String                    query_name,
       String                    output_type_name,
       String                    table_name,
       MetaSQL_Selection1        selection,
       MetaSQL_Where1            condition
     ) =
   _SELECT(query_name,
           output_type_name,
           convert(table_name,selection),
           convert(table_name,condition),
           all,
           []).

 public define MetaSQL_DML
   _SELECT
     (
       String                    query_name,
       String                    output_type_name,
       String                    table_name,
       MetaSQL_Selection1        selection,
       MetaSQL_Where1            condition
     ) =
   _SELECT(query_name,
           output_type_name,
           convert(table_name,selection),
           convert(table_name,condition),
           all,
           []).


   *** [2] Verifications concerning the description of the database and queries.

      *** [2.1] Forbidden cycles in database references (also checks for duplicate table names).

define Maybe(String)
   refers_forwards
     (
       String                    tname,      // name of table refered to
       List(MetaSQL_TabStruct)   forwards    // forwards tables
     ) =
   if forwards is
     {
       [ ] then failure,
       [t1 . others] then
         with n = name(t1),
         if n = tname
         then success(n)
         else refers_forwards(tname,others)
     }.

define Bool
   is_forbidden_reference
     (
       String                    table_name,   // current table
       MetaSQL_ColStruct         column,       // of current table
       List(MetaSQL_TabStruct)   forwards,     // 'forwards' tables
       List(String)              backwards     // names of 'backwards' tables
     ) =
   if table_name:backwards then print(error("table name '"+table_name+
                                      "' given to more than one table.")); true else
   if column is col(cname,ctype,cattr) then
   if ctype is foreign(n)
   then if n = table_name
        then print(error("column '"+cname+
                   "' in table '"+table_name+
                   "' is a non nullable self reference.")); true
        else if refers_forwards(n,forwards) is
                  {
                    failure           then
                      if n:backwards
                      then false   // correct backwards reference
                      else print(error("column '"+cname+
                                 "' in table '"+table_name+
                                 "' refers to a non existing table.")); true,
                    success(ft_name)  then print(error("column '"+cname+
                                 "' in table '"+table_name+
                                 "' is a non nullable forward reference to table '"+ft_name+"'.")); true
                  }
   else false.   // non foreign columns and foreign_or_null columns are not forbidden

define Bool
   has_forbidden_references
     (
       String                      table_name,     // the first 3 arguments concern the current table.
       MetaSQL_PrimKey             pk,
       List(MetaSQL_ColStruct)     columns,
       List(MetaSQL_TabStruct)     forwards,       // subsequent tables
       List(String)                backwards       // list of backwards table names
     ) =
   if columns is
     {
       [ ]                  then false,   // no forbidden reference found
       [col1 . other_cols]  then
         if is_forbidden_reference(table_name,col1,forwards,backwards)
         then true
         else has_forbidden_references(table_name,pk,other_cols,forwards,backwards)
     }.

define Bool     // returns 'true' if there is at least one forbidden cycle.
   has_forbidden_cycles
     (
       String                     db_name,
       List(MetaSQL_TabStruct)    tables,
       List(String)               backwards     // names of 'backwards' tables
     ) =
   if tables is
     {
       [ ]                  then false,   // no forbidden cycle found
       [tab1 . other_tabs]  then if tab1 is table(name1,pk1,cols1) then
         if has_forbidden_references(name1,pk1,cols1,other_tabs,backwards)
         then true
         else has_forbidden_cycles(db_name,other_tabs,[name1 . backwards])
     }.

define Bool
   is_forbidden_column_name
   (
     String   name
   ) =
   name = "id" | name = "metasqlcheck".

define Bool
   has_forbidden_column_names
   (
     MetaSQL_TabStruct   tab
   ) =
   if tab is table(_,_,cols) then
   mapor((MetaSQL_ColStruct cs) |->
            if cs is col(name,_,_) then is_forbidden_column_name(name),
         cols).

define Bool
   has_forbidden_column_names
   (
     MetaSQL_DBStruct   db
   ) =
   if db is database(_,tables) then
   mapor(has_forbidden_column_names,tables).


      *** [2.2] Checking for missing arguments in INSERT.

define (List(String)   missing_names,
        List(String)   non_existing_names)
   check_cols
     (
       List(String)              colnames,     // the list of columns in the 'INSERT' query
       List(String)              tabcols       // names of non nullable columns in the table
     ) =
   (tabcols - colnames, colnames - tabcols).

define Bool
   _INSERT_has_too_few_arguments
     (
       List(MetaSQL_TabStruct)    tables,
       List(MetaSQL_DML)          queries
     ) =
   if queries is
     {
       [ ]            then false,
       [q1 . others]  then
         if q1 is _INSERT(qname,tname,colnames)
         then if find_table(tname,tables) is
           {
             failure         then print(error("_INSERT metaquery '"+qname+
                                        "' refers to a non existing table: '"+tname+"'.")); true,
             success(tab)    then
               if tab is table(_,pk,tcols) then
               if check_cols(colnames,map_select((MetaSQL_ColStruct c) |->
                                                    if nullable(type(c)) then failure else success(name(c)),
                                                  tcols)) is (missing,non_existing) then
                 if (missing = [] & non_existing = [])
                 then false
                 else (if missing /= [] then print(error("the _INSERT metaquery '"+qname+
                          "' must provide values for columns: "+concat(missing,",")+".")) else unique);
                      (if non_existing /= [] then print(error("the _INSERT metaquery '"+qname+
                          "' refers to non existing columns: "+concat(non_existing,",")+".")) else unique);
                      true
           }
         else _INSERT_has_too_few_arguments(tables,others)
     }.


      *** [2.3] Any table must have at least one ordinary column.

      *** [2.4]


      *** [2.?] Launching all verifications.

public type CheckDescriptionResult:
   forbidden_column_names,
   forbidden_cycles,
   insert_has_too_few_arguments,
   ok.

public define CheckDescriptionResult
   check_description
     (
       List(MetaSQL_DML)        queries,
       MetaSQL_DBStruct         db
     ) =
   if has_forbidden_column_names(db)                     then forbidden_column_names        else
   if db is database(name,tables) then
   if has_forbidden_cycles(name,tables,[])               then forbidden_cycles              else
   if _INSERT_has_too_few_arguments(tables,queries)      then insert_has_too_few_arguments  else
   ok.


   *** [3] Generating the target file.


      *** [3.1] Converting a database type into the corresponding Anubis type:

public define String     // used in 'web/metaWEB_Form.anubis'
   to_Anubis
     (
       MetaSQL_Type  typ
     ) =
   if typ is
     {
       anubis(_T)            then _T,
       binary                then "ByteArray",
       binary_or_null        then "MaybeNull(ByteArray)",
       boolean               then "Bool",
       boolean_or_null       then "MaybeNull(Bool)",
       date                  then "String",
       date_or_null          then "MaybeNull(String)",
       time                  then "String",
       time_or_null          then "MaybeNull(String)",
       datetime              then "String",
       datetime_or_null      then "MaybeNull(String)",
       foreign(_)            then "MetaSQL_Id",
       foreign_or_null(_)    then "MaybeNull(MetaSQL_Id)",
       integer16             then "Word16",
       integer16_or_null     then "MaybeNull(Word16)",
       integer32             then "Word32",
       integer32_or_null     then "MaybeNull(Word32)",
       integer               then "Int",
       integer_or_null       then "MaybeNull(Int)",
       char(_)               then "String",
       char_or_null(_)       then "MaybeNull(String)",
       text                  then "String"
       text_or_null          then "MaybeNull(String)"
     }.


      *** [3.2] Getting the column structure from a column notation

define Maybe(MetaSQL_ColStruct)
   col_struct
     (
       MetaSQL_Column     c,
       MetaSQL_DBStruct   db
     ) =
   if c is table_name.column_name then
   if find_table(table_name,tables(db)) is
     {
       failure       then failure,
       success(tab)  then find_column(column_name,tab)
     }.


      *** [3.3] Generating the names of conversion functions.


type ConversionName:
   convname(Bool     is_nullable,
            String   name).

define ConversionName
   to_conversion_name
     (
       MetaSQL_Type  typ
     ) =
   if typ is
     {
       anubis(_T)            then convname(false, "(Maybe("+_T+"))convert_to_anubis"),
       binary                then convname(false, "convert_to_binary"),
       binary_or_null        then convname(true,  "convert_to_binary_or_null"),
       boolean               then convname(false, "convert_to_boolean"),
       boolean_or_null       then convname(true,  "convert_to_boolean_or_null"),
       date                  then convname(false, "convert_to_date"),
       date_or_null          then convname(true,  "convert_to_date_or_null"),
       time                  then convname(false, "convert_to_time"),
       time_or_null          then convname(true,  "convert_to_time_or_null"),
       datetime              then convname(false, "convert_to_datetime"),
       datetime_or_null      then convname(true,  "convert_to_datetime_or_null"),
       foreign(_)            then convname(false, "convert_to_foreign"),
       foreign_or_null(_)    then convname(true,  "convert_to_foreign_or_null"),
       integer16             then convname(false, "convert_to_integer"),
       integer16_or_null     then convname(true,  "convert_to_integer_or_null"),
       integer32             then convname(false, "convert_to_integer"),
       integer32_or_null     then convname(true,  "convert_to_integer_or_null"),
       integer               then convname(false, "convert_to_integer"),
       integer_or_null       then convname(true,  "convert_to_integer_or_null"),
       char(_)               then convname(false, "convert_to_text"),
       char_or_null(_)       then convname(true,  "convert_to_text_or_null"),
       text                  then convname(false, "convert_to_text"),
       text_or_null          then convname(true,  "convert_to_text_or_null")
     }.

define Maybe(ConversionName)
   to_conversion_name
     (
       MetaSQL_Column    c,
       MetaSQL_DBStruct  db
     ) =
   if col_struct(c,db) is
     {
       failure      then failure,
       success(cs)  then if cs is col(cname,ctype,attr) then
         success(to_conversion_name(ctype))
     }.


      *** [3.4] Formatting a variable in a WHERE clause.

   A variable (of type MetaSQL_Var) occurs in WHERE clauses, where it has to be replaced by an
   actual value. This value has to be formatted into a character string. However, each variable
   occuring in a WHERE clause has a type, and knowing this type is necessary in order to generate
   correct conversion of an Anubis datum into such a character string.

   Such  variables (say 'v') occur only within the expressions (of type MetaSQL_Where), where
   'c' is a column:

       c = v
       c < v
       v < c
       c =< v
       v =< c
       contains(c,v)

   and in each case the tye of the variable 'v' is the type of the column 'c'.

   We begin by the formatting proper, the type being known. Functions called from
   within the body of the next paragraph are defined in 'metaSQL_tools.anubis'.

define String
   format_variable
     (
       String          name,   // name of variable
       MetaSQL_Type    type    // type of variable
     ) =
   if type is
     {
       anubis(_T)                          then "value(prepare_anubis"+"("+name+"))",
       binary                              then "value(prepare_binary"+"("+name+"))",
       binary_or_null                      then "value(prepare_binary_or_null"+"("+name+"))",
       boolean                             then "value(prepare_boolean"+"("+name+"))",
       boolean_or_null                     then "value(prepare_boolean_or_null"+"("+name+"))",
       date                                then "value(prepare_date"+"("+name+"))",
       date_or_null                        then "value(prepare_date_or_null"+"("+name+"))",
       time                                then "value(prepare_time"+"("+name+"))",
       time_or_null                        then "value(prepare_time_or_null"+"("+name+"))",
       datetime                            then "value(prepare_datetime"+"("+name+"))",
       datetime_or_null                    then "value(prepare_datetime_or_null"+"("+name+"))",
       foreign(String table_name)          then "value(prepare_foreign"+"("+name+"))",
       foreign_or_null(String table_name)  then "value(prepare_foreign_or_null"+"("+name+"))",
       integer16                           then "value(prepare_integer"+"("+name+"))",
       integer16_or_null                   then "value(prepare_integer_or_null"+"("+name+"))",
       integer32                           then "value(prepare_integer"+"("+name+"))",
       integer32_or_null                   then "value(prepare_integer_or_null"+"("+name+"))",
       integer                             then "value(prepare_integer"+"("+name+"))",
       integer_or_null                     then "value(prepare_integer_or_null"+"("+name+"))",
       char(Int size)                      then "value(prepare_text("+name+"))",
       char_or_null(Int size)              then "value(prepare_text_or_null("+name+"))",
       text                                then "value(prepare_text("+name+"))",
       text_or_null                        then "value(prepare_text_or_null("+name+"))"
     }.


   Next, we define formatting knowing the name of the table, the name of the column
   and knowing the description of the database.

define Maybe(String)
   format_variable
     (
       String             name,         // of variable
       String             table_name,
       String             column_name,
       MetaSQL_DBStruct   db
     ) =
   if find_table(table_name,tables(db)) is
     {
       failure       then failure,
       success(tab)  then
         if find_column(column_name,tab) is
           {
             failure       then failure,
             success(c)    then if c is col(cname,type,attr) then
               success(format_variable(name,type))
           }
     }.

   A variant of the same one:

define Maybe(String)
   format_variable
     (
       MetaSQL_Var        v,
       MetaSQL_Column     c,
       MetaSQL_DBStruct   db
     ) =
   if c is table.column then format_variable(name(v),table,column,db).


      *** [3.5] Formatting a WHERE clause.

   For WHERE clauses, we need to adapt format_variable.

define String
   make_quote
     (
       MetaSQL_Type  t
     ) =
   if t is
     {
       anubis(_T)                          then "'",
       binary                              then "'",
       binary_or_null                      then "'",
       boolean                             then "'",
       boolean_or_null                     then "'",
       date                                then "'",
       date_or_null                        then "'",
       time                                then "'",
       time_or_null                        then "'",
       datetime                            then "'",
       datetime_or_null                    then "'",
       foreign(String table_name)          then "",
       foreign_or_null(String table_name)  then "",
       integer16                           then "",
       integer16_or_null                   then "",
       integer32                           then "",
       integer32_or_null                   then "",
       integer                             then "",
       integer_or_null                     then "",
       char(Int size)                      then "'",
       char_or_null(Int size)              then "'",
       text                                then "'",
       text_or_null                        then "'"
     }.

define Maybe(String)
   format_variable_for_where
     (
       MetaSQL_Var        v,
       MetaSQL_Column     c,
       MetaSQL_DBStruct   db
     ) =
   if format_variable(v,c,db) is
     {
       failure then failure,
       success(fv) then if c is table_name.column_name then
         if find_table(table_name,tables(db)) is
           {
             failure then failure,
             success(tab) then if find_column(column_name,tab) is
               {
                 failure then failure,
                 success(cs) then
                   with q = make_quote(type(cs)),
                   success(q+"\"+"+fv+"+\""+q)
               }
           }
     }.

define String
   format
     (
       MetaSQL_Column  c
     ) =
   if c is table.column then table+"."+column.

define Maybe(String)
   format
     (
       MetaSQL_Where       w,
       MetaSQL_DBStruct    db
     ) =
   if w is
     {
       c =  v                     then format(c)+" = "+format_variable_for_where(v,c,db),
       c1 = c2                    then success(format(c1)+" = "+format(c2)),
       c <  v                     then format(c)+" < "+format_variable_for_where(v,c,db),
       v <  c                     then format(c)+" > "+format_variable_for_where(v,c,db),
       c =< v                     then format(c)+" <= "+format_variable_for_where(v,c,db),
       v =< c                     then format(c)+" >= "+format_variable_for_where(v,c,db),
       is_null(c)                 then success(format(c)+" IS NULL "),
       is_not_null(c)             then success(format(c)+" IS NOT NULL "),
       contains(c,v)              then format(c)+" ILIKE '%\"+"+format_variable(v,c,db)+"+\"%' ",
       true                       then success(" TRUE "),
       false                      then success(" FALSE "),
       w1 & w2                    then format(w1,db)+" AND\n               "+format(w2,db),
       w1 | w2                    then format(w1,db)+" OR\n               "+format(w2,db),
          ~ w1                    then " NOT "+format(w1,db)
     }.


      *** [3.6] Gathering generated function's arguments comming from WHERE clauses.

type Function_Arg:
   arg(MetaSQL_Type    type,
       String          name,
       String          comment).

define Maybe(List($T))
   merge
     (
       Maybe(List($T))   l1,
       Maybe(List($T))   l2
     ) =
   if l1 is
     {
       failure then failure,
       success(l1a) then if l2 is
         {
           failure then failure,
           success(l2a) then success(merge(l1a,l2a))
         }
     }.

define Maybe(List(Function_Arg))
   arg_by_name
     (
       MetaSQL_Column    c,
       MetaSQL_Var       v,
       MetaSQL_DBStruct  db
     ) =
   if col_struct(c,db) is
     {
       failure then failure,
       success(cs) then success([arg(type(cs),name(v),name(cs))])
     }.

define Maybe(List(Function_Arg))
   gather_where_args
     (
       MetaSQL_Where       w,
       MetaSQL_DBStruct    db
     ) =
   if w is
     {
       c =  v                     then arg_by_name(c,v,db),
       c1 = c2                    then success([]),
       c <  v                     then arg_by_name(c,v,db),
       v <  c                     then arg_by_name(c,v,db),
       c =< v                     then arg_by_name(c,v,db),
       v =< c                     then arg_by_name(c,v,db),
       is_null(c)                 then success([]),
       is_not_null(c)             then success([]),
       contains(c,v)              then arg_by_name(c,v,db),
       true                       then success([]),
       false                      then success([]),
       w1 & w2                    then merge(gather_where_args(w1,db),gather_where_args(w2,db)),
       w1 | w2                    then merge(gather_where_args(w1,db),gather_where_args(w2,db)),
          ~ w1                    then gather_where_args(w1,db)
     }.


      *** [3.7] Formatting generated function's arguments comming from WHERE clauses.

define Maybe(String)
   where_args_dec
     (
       MetaSQL_Where     w,
       Int               spaces,
       MetaSQL_DBStruct  db
     ) =
   if gather_where_args(w,db) is
     {
       failure then failure,
       success(args) then if args is
         {
           [ ] then success("One dummy"),
           [_ . _] then success(concat(
              map((Function_Arg a) |-> if a is arg(type,name,com) then to_Anubis(type)+" "+name,
                  args),
              ",\n"+constant_string(spaces,' ')))
         }
     }.


   *** [4] Carrying on.

      *** [4.1] Generating 'INSERT' queries.

define String
   format_value
     (
       Function_Arg a
     ) =
   if a is arg(type,name,_) then format_variable(name,type).

define Maybe(List(Function_Arg))
   function_args
     (
       MetaSQL_TabStruct     tab,
       List(String)          col_names
     ) =
   if col_names is
     {
       [ ]              then success([ ]),
       [col1 . others]  then
         if find_column(col1,tab) is
           {
             failure      then print(error("Cannot find column '"+col1+"' in table '"+name(tab)+"'.")); failure,
             success(cs)  then if cs is col(name,type,attr) then
               if function_args(tab,others) is
                 {
                   failure      then failure,
                   success(ot)  then success([arg(type,name,name) . ot])
                 }
           }
     }.

define Maybe(List(Function_Arg))
   function_args
     (
       String              table_name,
       MetaSQL_DBStruct    db,
       List(String)        column_names
     ) =
   if find_table(table_name,tables(db)) is
     {
       failure then print(error("Cannot find table '"+table_name+"'.")); failure,
       success(tab) then function_args(tab,column_names)
     }.

define String
   function_args_dec
     (
       List(Function_Arg)   args,
       Int                spaces,
     ) =
   concat(map((Function_Arg a) |-> if a is arg(t,n,c) then to_Anubis(t)+" "+n,args),
          ",\n"+constant_string(spaces,' ')).


define Maybe(String)
   make_INSERT_func
     (
       String              query_name,
       String              table_name,
       List(String)        column_names,
       MetaSQL_DBStruct    db,
       Bool                declare_only
     ) =
   if function_args(table_name,db,column_names) is
     {
       failure then failure,
       success(args) then success(
         (if declare_only
          then "\n\n\n   ---------------- "+query_name+".\n\n"
          else "")+
         "public define ("+function_args_dec(args,15)+") -> Result(String /* error message */,One)\n"+
         "   "+query_name+"\n"+
         "     (\n"+
         "       String -> MetaSQL_Answer    query_function\n"+
         "     )"+
         (if declare_only
          then "."
          else " =\n"+
               "   ("+function_args_dec(args,4)+") |-> \n"+
               "       send_protected(false,(String -> MetaSQL_Answer qf) |-> if qf(\n"+
               "  \"INSERT INTO "+table_name+" ("+
                      concat(["metasqlcheck". column_names],",")+")\n      VALUES ("+
                      concat(["'FALSE'" . map((Function_Arg a) |-> "'\"+"+format_value(a)+"+\"'",args)],
                  ",\n              ")+");\") is\n"+
                           "    {\n"+
                           "      error(msg)           then error(msg),\n"+
                           "      command_complete     then ok(unique),\n"+
                           "      table(rows)          then "+make_error("Unexpected answer")+",\n"+
                           "      informations(info)   then "+make_error("Unexpected answer")+"\n"+
                           "    },query_function).\n"
                     )+
                     "\n\n")
      }.


      *** [4.2] Generating SELECT queries.

      The condition may imply that there must be one and exactly one row in the result.
      This is based on the fact that the value of a primary key should be always
      valid.

define Bool
   implies_exists_and_unique
     (
       MetaSQL_Where    w
     ) =
   if w is c = (MetaSQL_Var)v
   then (if c is table_name.column_name then column_name = "id")
   else false.

define Bool
   implies_unique
     (
       MetaSQL_Column     c,
       MetaSQL_DBStruct   db
     ) =
   if col_struct(c,db) is
     {
       failure then should_not_happen(false),
       success(cs) then if cs is col(cname,ctype,attr) then (unique:attr)
     }.

define Bool
   implies_unique    // but may not exist
     (
       MetaSQL_Where       w,
       MetaSQL_DBStruct    db
     ) =
   if implies_exists_and_unique(w) then true else
   if w is
     {
       c =  v                     then implies_unique(c,db)
       c1 = c2                    then false,
       c <  v                     then false,
       v <  c                     then false,
       c =< v                     then false,
       v =< c                     then false,
       is_null(c)                 then false,
       is_not_null(c)             then false,
       contains(c,v)              then false,
       true                       then false,
       false                      then false,
       w1 & w2                    then implies_unique(w1,db) | implies_unique(w2,db),
       w1 | w2                    then false,
          ~ w1                    then false
     }.


define Maybe(List(Function_Arg))
   select_components
     (
       List(MetaSQL_Column)    col_names,
       MetaSQL_DBStruct        db,
       Int                     i
     ) =
   if col_names is
     {
       [ ] then success([]),
       [c1 . others] then if c1 is table_name.column_name then
         if find_table(table_name,tables(db)) is
           {
             failure then failure,
             success(tab) then if find_column(column_name,tab) is
               {
                 failure then failure,
                 success(cs) then if cs is col(cname,ctype,attr) then
                   if select_components(others,db,i+1) is
                     {
                       failure then failure,
                       success(oc) then success([arg(ctype,"_"+to_decimal(i),cname) . oc])
                     }
               }
           }
     }.

 define Maybe(String)
   select_components
     (
       List(MetaSQL_Column)    col_names,
       MetaSQL_DBStruct        db
     ) =
   if (Maybe(List(Function_Arg)))select_components(col_names,db,0) is
     {
       failure then failure,
       success(comps) then success(
         concat(map((Function_Arg a) |-> if a is arg(t,n,c) then to_Anubis(t)+" "+n+" /* "+c+" */",
                    comps),
                ",\n      "))
     }.


define String
  items_comma
    (
      Int n,
      Int i
    ) =
  if i = n
  then ""
  else "i"+to_decimal(i)+(if i+1 = n then "" else ",")+items_comma(n,i+1).


define Maybe(String)
   select_conversion_body
     (
       String                      output_type_name,
       List(MetaSQL_Column)        col_names,
       MetaSQL_DBStruct            db,
       String                      indent,
       Int                         count
     ) =
   if col_names is
     {
       [ ] then success(indent+"     ok("+to_lower(output_type_name)+"("+items_comma(count,0)+"))\n"),
       [h . t] then
         if to_conversion_name(h,db) is
           {
             failure then failure,
             success(cn) then if cn is convname(is_nullable,conv_name) then
               indent+" if row"+to_decimal(count)+" is\n"+
               indent+"  {\n"+
               indent+"   [ ] then "+make_error("Datum not received")+",\n"+
               indent+"   [item"+to_decimal(count)+" . row"+to_decimal(count+1)+"] then\n"+
               (if is_nullable
                then indent+"    with i"+to_decimal(count)+" = "+conv_name+"(item"+to_decimal(count)+"),\n"
                else indent+"    if "+conv_name+"(item"+to_decimal(count)+") is\n"+
                     indent+"    {\n"+
                     indent+"      failure then "+make_error("Cannot "+conv_name)+",\n"+
                     indent+"      success(i"+to_decimal(count)+") then\n")+
               select_conversion_body(output_type_name,t,db,indent+"   ",count+1)+
               indent+(if is_nullable
                       then "  } /* "+conv_name+" */ "
                       else "  }} /* "+conv_name+" */ ")+
                      (if count = 0 then "" else "\n")
           }
     }.


define List(String)
   collect_table_names
     (
       List(MetaSQL_Column) l
     ) =
   if l is
     {
       [ ] then [ ],
       [h . t] then with others = collect_table_names(t),
         if h is tn.cn then if tn:others then others else [tn . others]
     }.


define List(String)
   collect_table_names
     (
       MetaSQL_Where        cond
     ) =
   if cond is
     {
       c =  v                     then if c is tn.cn then [tn],
       c1 = c2                    then if c1 is tn1.cn1 then if c2 is tn2.cn2 then [tn1,tn2],
       c <  v                     then if c is tn.cn then [tn],
       v <  c                     then if c is tn.cn then [tn],
       c =< v                     then if c is tn.cn then [tn],
       v =< c                     then if c is tn.cn then [tn],
       is_null(c)                 then if c is tn.cn then [tn],
       is_not_null(c)             then if c is tn.cn then [tn],
       contains(c,v)              then if c is tn.cn then [tn],
       true                       then [],
       false                      then [],
       w1 & w2                    then merge(collect_table_names(w1),collect_table_names(w2)),
       w1 | w2                    then merge(collect_table_names(w1),collect_table_names(w2)),
          ~ w1                    then collect_table_names(w1)
     }.

define List(String)
   collect_table_names
     (
       List(MetaSQL_Column) l,
       MetaSQL_Where        cond
     ) =
   merge(collect_table_names(l),collect_table_names(cond)).

define String
   format
   (
     List(MetaSQL_Order)  l
   ) =
   if l is
   {
     [ ] then "",
     [h . t] then "\n       ORDER BY "+
       if h is
       {
           asc(col)  then format(col) + " ASC",
           desc(col) then format(col) + " DESC"
       } +
       (if t is [] then "" else "; ")
       + format(t)
   }.


define Maybe(String)
   make_SELECT_func
     (
       String                  query_name,
       String                  output_type_name,
       MetaSQL_Selection       selection,
       MetaSQL_Where           condition,
       String                  comps,      // components of output type computed in advance (see below)
       MetaSQL_Range           range,
       List(MetaSQL_Order)     order,
       MetaSQL_DBStruct        db,
       Bool                    declare_only,
       Bool                    dont_print_type
     ) =
   with    eu = implies_exists_and_unique(condition),     // if 'ue' the type of the result is output_type
            u = implies_unique(condition,db),             // otherwise if 'u' the type of the result is Maybe(output_type)
                                                       // otherwise, the type of the result is List(output_type)
    col_names = selected_columns(selection),
     counting = (Bool)if selection is count(_) then true else false,
        //
        //  Title and return type definition.
        //
        (if declare_only
         then "\n\n\n   ---------------- "+query_name+".\n\n"+
           if counting
           then ""
           else if dont_print_type
                then "   Type '"+output_type_name+"' is already defined above.\n\n"
                else "public type "+output_type_name+":\n"+
                     "   "+to_lower(output_type_name)+"\n     ("+
                     comps+
                     ").\n\n"
         else "")+
        //
        // Auxiliary function for converting one row.
        //
        (if (declare_only | counting) then success("") else
          "define Result(String,"+output_type_name+")\n"+
          "   "+query_name+"_aux\n"+
          "     (\n"+
          "       List(MaybeNull(ByteArray)) row0\n"+
          "     ) =\n"+
          select_conversion_body(output_type_name,col_names,db,"  ",0)+".\n"+
          "\n\n")+
        //
        // The main function.
        //
        "public define ("+where_args_dec(condition,15,db)+") -> Result(String /* error message */,"+
           (if counting
            then "Int"
            else (if eu
                  then ""
                  else if u
                       then "Maybe("
                       else "List(")+
                       output_type_name+
                       (if eu then "" else ")"))+
            ")\n"+
        "   "+query_name+"\n"+
        "     (\n"+
        "       String -> MetaSQL_Answer    query_function\n"+
        "     )"+
        (if declare_only
         then success(".")
         else " =\n"+
              "   ("+where_args_dec(condition,4,db)+") |->\n"+
              "      send_protected(false,(String -> MetaSQL_Answer qf) |-> if qf(\n"+
              "       \"SELECT "+
              (if selection is count(_) then "COUNT(" else "")+
              concat(map((MetaSQL_Column c) |-> if c is tn.cn then tn+"."+cn,
                         col_names),
                     ",\n                ")+
              (if selection is count(_) then ")" else "")+
              "\n"+
              "       FROM "+concat(collect_table_names(col_names,condition),",")+" \n"+
              "       WHERE "+
              format(condition,db)+
              format(order)+";\") is\n"+
              "    {\n"+
              "      error(msg)           then error(msg),\n"+
              "      command_complete     then "+make_error("Unexpected answer")+",\n"+
              "      table(rows)          then \n"+
              (if counting
               then (
                      "      if rows is\n"+
                      "        {\n"+
                      "          [ ] then "+make_error("Expected row not received")+",\n"+
                      "          [row1 . _] then if row1 is\n"+
                      "             {\n"+
                      "                [ ] then "+make_error("Expected row not received")+",\n"+
                      "                [cell1 . _] then if cell1 is\n"+
                      "                  {\n"+
                      "                     null then "+make_error("Count not received")+",\n"+
                      "                     not_null(b) then if decimal_scan(to_string(b)) is\n"+
                      "                       {\n"+
                      "                         failure then "+make_error("Count unreadable")+",\n"+
                      "                         success(n) then ok(n)\n"+
                      "                       }\n"+
                      "                  }\n"+
                      "             }\n"+
                      "        },\n"
                    )
               else (
                     if eu
                     then "      if rows is\n"+
                          "        {\n"+
                          "          [ ] then "+make_error("Expected row not received")+",\n"+
                          "          [h . t] then if t is\n"+
                          "            {\n"+
                          "              [ ] then "+query_name+"_aux(h),\n"+
                          "              [_ . _] then "+make_error("Several rows received")+",\n"+
                          "            }\n"+
                          "        }\n"
                     else if u
                          then "      if rows is\n"+
                               "        {\n"+
                               "          [ ]      then ok(failure),\n"+
                               "          [h . t]  then if "+query_name+"_aux(h) is\n"+
                               "            {\n"+
                               "              error(msg) then error(msg),\n"+
                               "              ok(x)      then if t is\n"+
                               "                {\n"+
                               "                  [ ] then ok(success(x)),\n"+
                               "                  [_ . _] then "+make_error("Several rows received")+",\n"+
                               "                }\n"+
                               "            }\n"+
                               "        }\n"
                          else "        map_escape("+query_name+"_aux,rows)\n"
                   )
              )+
              "      informations(info)   then "+make_error("Unexpected answer")+"\n"+
              "    },query_function).\n\n").


      *** [4.3] Generating UPDATE queries.

   In the case of '_UPDATE', the arguments to the function are comming from two sources:
   the list of columns (a new value must be provided for each) and the variables in the
   where clause. The name of the same column may appear in both lists, as in the example:

      _UPDATE("function_name",
              "table_name",
              ["col1"],
              "col1" = var("col1"))

   whose meaning is: put a new value in field "col1" in all records where this field has a
   given value (that of the value of the variable in the where clause). Clearly, this is
   misleading, and a better formulation would be:

      _UPDATE("function_name",
              "table_name",
              ["col1"],
              "col1" = var("old_col1"))

   The function will have the two arguments: 'col1' and 'old_col1', and the meaning of the
   action is to replace all occurences of the value of 'old_col1' in the column "col1" by the
   value of the argument 'col1'.

   As a consequence, we forbid the use of a column name in the variables of the where clause.
   The function 'forbidden_update_args' gives the list of where clause variable names which are
   column names. There is a 'Maybe', because we need 'find_table' and 'find_column' (which send
   a message in case of an error).

define Maybe(List(String))
   forbidden_update_args
     (
       List(String)       column_names,
       MetaSQL_Where      clause,
       MetaSQL_DBStruct   db
     ) =
   if gather_where_args(clause,db) is
     {
       failure then failure,
       success(args) then
         with var_names = map(name,args),
         success(inter(column_names,var_names))
     }.


define Bool     // true if no forbidden update args
   check_update_args
     (
       String              table_name,
       List(String)        column_names,
       MetaSQL_Where       condition,
       MetaSQL_DBStruct    db
     ) =
   if forbidden_update_args(column_names,condition,db) is
     {
       failure then false,
       success(l) then
         if l is
           {
             [ ] then true,
             [_ . _] then print("\nError: column names used as where variables in _UPDATE:\n"+
                                concat(l,",")+"\n"); false
           }
     }.


define Maybe(String)
   make_UPDATE_func
     (
       String              query_name,
       String              table_name,
       List(String)        column_names,
       MetaSQL_Where1      condition,
       MetaSQL_DBStruct    db,
       Bool                declare_only
     ) =
   if find_table(table_name,tables(db)) is
     {
       failure then failure,
       success(tab) then
         with cond = convert(table_name,condition),
               if function_args(table_name,db,column_names) is
                 {
                   failure then failure,
                   success(args) then
         if not(check_update_args(table_name,column_names,cond,db)) then failure
         else with all_args = " // columns to update\n               "+
                              function_args_dec(args,15)+
                              ",\n                // variables in 'WHERE' clause\n               "+
                              where_args_dec(cond,15,db),
         (if declare_only
          then "\n\n\n   ---------------- "+query_name+".\n\n"
          else "")+
         "public define ("+all_args+") -> Result(String /* error message */,One)\n"+
         "   "+query_name+"\n"+
         "     (\n"+
         "       String -> MetaSQL_Answer    query_function\n"+
         "     )"+
         (if declare_only
          then success(".")
          else " =\n"+
               "              ("+all_args+") |-> \n"+
               "       send_protected(false,(String -> MetaSQL_Answer qf) |-> if qf(\n"+
               "  \"UPDATE "+table_name+" SET ("+
                      concat(column_names,",")+")\n           = ("+
           concat(map((Function_Arg a) |-> "'\"+"+format_value(a)+"+\"'",args),
                  ",\n              ")+
                  ")           WHERE\n               "+
                  format(cond,db)+
                  ";\") is\n"+
                           "    {\n"+
                           "      error(msg)           then error(msg),\n"+
                           "      command_complete     then ok(unique),\n"+
                           "      table(rows)          then "+make_error("Unexpected answer")+",\n"+
                           "      informations(info)   then "+make_error("Unexpected answer")+"\n"+
                           "    },query_function).\n"
                     )+
                      "\n\n"
                      }
     }.


      *** [4.4] Generating DELETE queries.

define Maybe(String)
   make_DELETE_func
     (
       String              query_name,
       String              table_name,
       MetaSQL_Where1      condition,
       MetaSQL_DBStruct    db,
       Bool                declare_only
     ) =
   with cond = convert(table_name,condition),
   (if declare_only
    then "\n\n\n   ---------------- "+query_name+".\n\n"
    else "")+
   "public define ("+where_args_dec(cond,15,db)+") -> Result(String /* error message */,One)\n"+
   "   "+query_name+"\n"+
   "     (\n"+
   "       String -> MetaSQL_Answer    query_function\n"+
   "     )"+
   (if declare_only
    then success(".")
    else " =\n"+
         "   ("+where_args_dec(cond,4,db)+") |-> \n"+
         "       send_protected(false,(String -> MetaSQL_Answer qf) |-> if qf(\n"+
         "  \"DELETE FROM "+table_name+"\n         WHERE "+
         format(cond,db)+";\") is\n"+
         "    {\n"+
         "      error(msg)           then error(msg),\n"+
         "      command_complete     then ok(unique),\n"+
         "      table(rows)          then "+make_error("Unexpected answer")+",\n"+
         "      informations(info)   then "+make_error("Unexpected answer")+"\n"+
         "    },query_function).\n")+
    "\n\n".


      *** [4.5] Generating all sorts of query functions.

   A test for knowing if a given type definition has already been written to the target file.

type TypeSeen:
   type_seen
     (String name,
      List(Function_Arg)  components).

define Bool
   compare
     (
       Function_Arg   a1,
       Function_Arg   a2
     ) =
   if a1 is arg(n1,c1,_) then
   if a2 is arg(n2,c2,_) then
   (n1 = n2) & (c1 = c2).

define Bool
   compare
     (
       List(Function_Arg)   a1,
       List(Function_Arg)   a2
     ) =
   if a1 is
     {
       [ ] then a2 = [ ],
       [h1 . t1] then if a2 is
         {
           [ ] then false,
           [h2 . t2] then
             if compare(h1,h2)
             then compare(t1,t2)
             else false
         }
     }.

define Bool
   compare
     (
       TypeSeen  t1,
       TypeSeen  t2
     ) =
   if t1 is type_seen(n1,c1) then
   if t2 is type_seen(n2,c2) then
   (n1 = n2) & (compare(c1,c2)).


define Bool
   type_already_seen
     (
       TypeSeen               ts,
       List(TypeSeen)         already_seen
     ) =
   if already_seen is
     {
       [ ] then false,
       [h . t] then if compare(h,ts)
                    then true
                    else type_already_seen(ts,t)
     }.


define Maybe(String)
   make_query_functions
     (
       List(MetaSQL_DML)      queries,
       MetaSQL_DBStruct       db,
       Bool                   declare_only,
       List(TypeSeen)         types_so_far
     ) =
   if queries is
     {
       [ ] then success(""),
       [query . others] then
         if query is
           {
             _INSERT(query_name,table_name,column_names) then
               if make_INSERT_func(query_name,table_name,column_names,db,declare_only) is
                 {
                   failure then failure,
                   success(f) then if make_query_functions(others,db,declare_only,types_so_far) is
                     {
                       failure then failure,
                       success(o) then success(f+o)
                     }
                 },

             _SELECT(query_name,out_type,selection,condition,range,order) then
               with col_names = selected_columns(selection),
               if (Maybe(List(Function_Arg)))select_components(col_names,db,0) is
                 {
                   failure then failure,
                   success(comps) then
                     with scomps =
                        concat(map((Function_Arg a) |-> if a is arg(t,n,c) then to_Anubis(t)+" "+n+" /* "+c+" */",
                               comps),",\n      "),
                     if make_SELECT_func(query_name,
                                         out_type,
                                         selection,
                                         condition,
                                         scomps,
                                         range,
                                         order,
                                         db,
                                         declare_only,
                                         type_already_seen(type_seen(out_type,comps),types_so_far)) is
                       {
                         failure then failure,
                         success(f) then
                           if make_query_functions(others,db,declare_only,
                                                   [type_seen(out_type,comps) . types_so_far]) is
                             {
                               failure then failure,
                               success(o) then success(f+o)
                             }
                        }
                  },

             _UPDATE(query_name,table_name,column_names,condition) then
               if make_UPDATE_func(query_name,table_name,column_names,condition,db,declare_only) is
                 {
                   failure then failure,
                   success(f) then if make_query_functions(others,db,declare_only,types_so_far) is
                     {
                       failure then failure,
                       success(o) then success(f+o)
                     }
                 },

             _DELETE(query_name,table_name,condition) then
               if make_DELETE_func(query_name,table_name,condition,db,declare_only) is
                 {
                   failure then failure,
                   success(f) then if make_query_functions(others,db,declare_only,types_so_far) is
                     {
                       failure then failure,
                       success(o) then success(f+o)
                     }
                 }
           }
     }.


      *** [4.6] The 'make_queries' function.

public define One
   make_queries
     (
       String                  target_file_path,
       List(String)            files_to_be_read,
       List(MetaSQL_DML)       queries,
       MetaSQL_DBStruct        db
     ) =
   if make_query_functions(queries,db,true,[]) is
     {
       failure then unique,
       success(public_part) then
         if make_query_functions(queries,db,false,[]) is
           {
             failure then unique,
             success(private_part) then
               if file(target_file_path,new) is
                 {
                   failure     then print("\nError: Cannot create file '"+target_file_path+"'.\n\n"),
                   success(t)  then with target = (WStream)weaken(t),
                     print(target,
                       "\n   This file was generated automatically by the metaprogram 'data_base/metaSQL.anubis'.\n"+
                       "   Don't modify it. Your modifications would be crushed.\n\n"+
                       "read tools/basis.anubis\n"+
                       "read tools/base64.anubis\n"+
                       "read data_base/metaSQL.anubis\n"+
                       concat(map((String f) |-> "read "+f,files_to_be_read),"\n")+
                       "\n\n"+
                       public_part+
                       "\n\n\n\n   --- That's all for the public part ! ----------------------------------\n\n\n\n"+
                       private_part+
                       "\n\n")
                 }
           }
     }.


   *** [5] Creating and checking the database.


public type MetaSQL_Id:
   id(String value).         // not an 'Int' because the database reads it as a string
                             // and returns it as a byte array. This is just an identifier.
                             // No computation is made on it.

public type MetaSQL_Prepared:
   prepared(String value).   // a 'prepared' value is just a string, but defining it of
                             // a different type avoid errors.


public type MetaSQL_Specific:
   specific
     (
       String                      database_name,
       String -> MetaSQL_Answer    query_function,   // the query function
       MetaSQL_Type -> String      to_database_type  // convert type to a string for the specific software
     ).


public define Maybe($T)
   convert_to_anubis
     (
       MaybeNull(ByteArray) mbnb
     ) =
   if mbnb is
     {
       null then failure,
       not_null(b) then
         (Maybe($T))unserialize(base64_decode(b))
     }.

public define MetaSQL_Prepared
   prepare_foreign
     (
       MetaSQL_Id  i
     ) =
   prepared(value(i)).

public define MetaSQL_Prepared
   prepare_foreign_or_null
     (
       MaybeNull(MetaSQL_Id) i
     ) =
   if i is
     {
       null          then prepared("NULL"),
       not_null(id)  then prepared(value(id))
     }.

public define MetaSQL_Prepared
   prepare_date
     (
       String d
     ) =
   prepared(d).

public define MetaSQL_Prepared
   prepare_date_or_null
     (
       MaybeNull(String) b
     ) =
   if b is
     {
       null then prepared("NULL"),
       not_null(d) then prepared(d)
     }.

public define Maybe(String)
   convert_to_date
     (
       MaybeNull(ByteArray)  b
     ) =
   if b is
     {
       null then failure,
       not_null(ba) then success(to_string(ba))
     }.


     In the future, if type schemes are seen as 'functors' (in the sens of category theory)
     the function below could be written:  MaybeNull(to_string)

public define MaybeNull(String)
   convert_to_date_or_null
     (
       MaybeNull(ByteArray)  b
     ) =
   if b is
     {
       null then null,
       not_null(ba) then not_null(to_string(ba))
     }.

public define MetaSQL_Prepared
  prepare_boolean
    (
      Bool b
    ) =
  prepared(if b then "TRUE" else "FALSE").

public define MetaSQL_Prepared
   prepare_boolean_or_null
     (
       MaybeNull(Bool) b
     ) =
   prepared(if b is
     {
       null then "NULL",
       not_null(bl) then if bl then "TRUE" else "FALSE"
     }).


public define Maybe(Bool)
   convert_to_boolean
     (
       MaybeNull(ByteArray) b
     ) =
   if b is
     {
       null then failure,
       not_null(ba) then if nth(0,ba) is
         {
           failure then failure,
           success(c) then if (c = 'T' | c = 't' | c = '1')
                           then success(true)
                           else if (c = 'F' | c = 'f' | c = '0')
                                then success(false)
                                else failure
         }
     }.


public define MaybeNull(Bool)          convert_to_boolean_or_null    (MaybeNull(ByteArray) b).


public define MetaSQL_Prepared
   prepare_anubis
     (
       $T datum
     ) =
   prepared(to_string(base64_encode(serialize(datum)))).


public define MetaSQL_Prepared
   prepare_binary
    (
      ByteArray b
    ) =
  prepared(to_string(base64_encode(b))).

public define MetaSQL_Prepared
   prepare_binary_or_null
     (
       MaybeNull(ByteArray) b
     ) =
   prepared(if b is
     {
       null then "NULL",
       not_null(ba) then to_string(base64_encode(ba))
     }).


public define Maybe(ByteArray)
   convert_to_binary
     (
       MaybeNull(ByteArray) b
     ) =
   if b is
     {
       null         then failure,
       not_null(v)  then success(base64_decode(v))
     }.

public define MaybeNull(ByteArray)
   convert_to_binary_or_null
     (
       MaybeNull(ByteArray) b
     ) =
   if b is
     {
       null         then null,
       not_null(v)  then not_null(base64_decode(v))
     }.


public define Maybe(MetaSQL_Id)
   convert_to_foreign
     (
       MaybeNull(ByteArray)  b
     ) =
   if b is
     {
       null then failure,
       not_null(ba) then success(id(to_string(ba)))
     }.

public define MaybeNull(MetaSQL_Id)
   convert_to_foreign_or_null
     (
       MaybeNull(ByteArray)  b
     ).

define List(Word8)
   prepare_text         // an auxiliary function
     (
       List(Word8) t
     ) =
   if t is
     {
       [ ] then [ ],
       [c . d] then
         if c = '\''
         then [c,c . prepare_text(d)]
         else [c . prepare_text(d)]
     }.

public define MetaSQL_Prepared
   prepare_text         // the actual input conversion function
     (
       String t
     ) =
   prepared(implode(prepare_text(explode(t)))).


   Conversely, texts arrive from the database in the form of byte arrays which must be
   converted to strings. The two functions for converting to 'text' and to 'text_or_null'
   are almost the same one. The only difference is that 'null' is interpreted as an error in
   the case of 'text'.

public define Maybe(String)
   convert_to_text
     (
       MaybeNull(ByteArray) mbba
     ) =
   if mbba is
     {
       null         then failure,
       not_null(b)  then success(to_string(b))
     }.

public define MaybeNull(String)
   convert_to_nullable_text
     (
       MaybeNull(ByteArray) mbba
     ) =
   if mbba is
     {
       null         then null,
       not_null(b)  then not_null(to_string(b))
     }.


 public define Result($E,List($T))
   map_escape
     (
       List(MaybeNull(ByteArray)) -> Result($E,$T)    convert,
       List(List(MaybeNull(ByteArray)))               rows
     ) =
   if rows is
     {
       [ ]      then ok([ ]),
       [h . t]  then if convert(h) is
         {
           error(e)  then error(e),
           ok(h1)    then if map_escape(convert,t) is
             {
               error(e)  then error(e),
               ok(t1)    then ok([h1 . t1])
             }
         }
     }.


public define Result(String,$T)
   send_protected
     (
       Bool                                              within_transaction,
       (String -> MetaSQL_Answer) -> Result(String,$T)   query,
       String -> MetaSQL_Answer                          qf
     ) =
   protect // it is important that there is only one 'protect' for controling the communication
           // with the database. If we had several 'protect' the terms they protect could be
           // executed at the same time.
   if within_transaction
   then if (//print("'BEGIN' sent\n");
            qf("BEGIN;")) is
     {
       error(msg)         then error(msg),
       command_complete   then if query(qf) is
         {
           error(msg)    then forget(//print("'ROLLBACK' sent\n");
                                     qf("ROLLBACK;")); error(msg),
           ok(res)       then if (//print("'COMMIT' sent\n");
                                  qf("COMMIT;")) is
             {
               error(msg)        then error(msg),
               command_complete  then ok(res),
               table(_)          then error("Unexpected answer "+__FILE__+":"+to_decimal(__LINE__)),
               informations(_)   then error("Unexpected answer "+__FILE__+":"+to_decimal(__LINE__))
             }
         },
       table(_)           then error("Unexpected answer "+__FILE__+":"+to_decimal(__LINE__)),
       informations(_)    then error("Unexpected answer "+__FILE__+":"+to_decimal(__LINE__))
     }
   else query(qf).


public define ((String -> MetaSQL_Answer) -> Result(String,$T)) -> Result(String,$T)
   transaction
     (
       String -> MetaSQL_Answer   qf
     ) =
   ((String -> MetaSQL_Answer) -> Result(String,$T) sequence) |->
   send_protected(true,sequence,qf).


   *** Testing tables.

   We want to be able to test if a given table is operational for a given table description.
   To do this, we try to insert a new record into the table, to recover it (via SELECT), and
   check if it is unchanged. In any case we delete it after this operation.

   However, we encounter the following problems:
      - if a column has attribute UNIQUE, we must put in this column a value not already
        present. To that end, we create an 'unlikely' value for this column, and try to
        get all records having this value in this column. If there is none, the value can
        be used. If not we try another one, and repeat the process until we find an acceptable
        value.

        There is a (very very low actually) risk that after inserting our record, we recover
        several identical records. However, we cannot apply the method for UNIQUE columns to
        boolean columns for example.


   What we do is the following:

     (1) collect the names af all columns which have attribute unique, and create for
         each an acceptable value.
     (2) create values for all other columns (except id).
     (3) try to insert the datum, to recover it, and compare.
     (4) repeat (1) (2) and (3) several times. All tries must succeed.


        *** Checking if a table exists (and if not create it if possible).

define String
   format_attributes
     (
       MetaSQL_Type            type,
       List(MetaSQL_Attr)      attrs
     ) =
   (if unique:attrs   then " UNIQUE" else "").


define String
   format_column_def
     (
       MetaSQL_Specific    spec,
       MetaSQL_ColStruct   cs
     ) =
   if cs is col(name,type,attrs) then
   name+" "+spec.to_database_type(type)+format_attributes(type,attrs).


public type TableExistsResult:
   table_already_exists     (String     table_name),
   table_created            (String     table_name),
   cannot_create_table      (String     table_name).

define TableExistsResult
   table_exists
   (
     MetaSQL_TabStruct                    tab,
     MetaSQL_Specific                     spec,
   ) =
   if tab is table(tname,pk,columns) then
   if spec.query_function("SELECT COUNT(*) FROM "+tname+";") is
   table(_)
   then table_already_exists(tname)
   else if spec.query_function("CREATE TABLE "+tname+" ("+
            if pk is
              {
                with_pk       then "id bigserial NOT NULL UNIQUE PRIMARY KEY, ",
                no_pk         then ""
              } + concat(map((MetaSQL_ColStruct c) |-> format_column_def(spec,c),
                         [col("metasqlcheck",boolean,[default(prepare_boolean(false))]) . columns]),", ") +
                 ");") is command_complete
        then table_created(tname)
        else cannot_create_table(tname).


        *** Creating an unlikely value.

   The value is created in the form of a character string (directly usable for formating
   the query).

define String
   make_unlikely_value
   (
     MetaSQL_Type   t
   ) =
   if t is
   {
     anubis(_T)                   then to_ascii(sha1(random(400000000))),
     binary                       then to_ascii(sha1(random(400000000))),
     binary_or_null               then to_ascii(sha1(random(100000))),
     boolean                      then if random(2) = 0 then "t" else "f",
     boolean_or_null              then if random(2) = 0 then "t" else "f",
     date                         then _Int_to_ISO_8601_date(to_Int(random(400000000))),
     date_or_null                 then _Int_to_ISO_8601_date(to_Int(random(400000000))),
     time                         then _Int_to_ISO_8601_time(to_Int(random(400000000))),
     time_or_null                 then _Int_to_ISO_8601_time(to_Int(random(400000000))),
     datetime                     then _Int_to_ISO_8601_datetime(to_Int(random(400000000))),
     datetime_or_null             then _Int_to_ISO_8601_datetime(to_Int(random(400000000))),
     foreign(table_name)          then to_decimal(random(400000000)),
     foreign_or_null(table_name)  then to_decimal(random(400000000)),
     integer16                    then to_decimal(random(32000)),
     integer16_or_null            then to_decimal(random(32000)),
     integer32                    then to_decimal(random(400000000)),
     integer32_or_null            then to_decimal(random(400000000)),
     integer                      then to_decimal(random(400000000)),
     integer_or_null              then to_decimal(random(400000000)),
     char(size)                   then if sub_string(to_ascii(sha1(random(400000000))),0,min(40,size)) is
                                       {
                                         failure    then should_not_happen("5f520a74efc"),
                                         success(s) then s
                                       },
     char_or_null(size)           then if sub_string(to_ascii(sha1(random(400000000))),0,min(40,size)) is
                                       {
                                         failure    then should_not_happen("5f520a74efc"),
                                         success(s) then s
                                       },
     text                         then to_ascii(sha1(random(400000000))),
     text_or_null                 then to_ascii(sha1(random(400000000)))
   }.


define String
   format
   (
     MaybeNull(ByteArray) b
   ) =
   if b is
   {
     null then "[NULL]",
     not_null(v) then to_string(v)
   }.

define String
   format
   (
     List(List(MaybeNull(ByteArray))) l
   ) =
   concat(map((List(MaybeNull(ByteArray)) k) |->
      "|"+concat(map(format,k),"|")+"|\n"
       ,l),"").


define Maybe(String)       // returns 'failure' if we cannot query the table
   make_acceptable_value   // this requires querying the data base
   (
     MetaSQL_Type                         type,
     String                               table_name,
     String                               column_name,
     String -> MetaSQL_Answer             query_function,
     Int                                  retries
   ) =
   if column_name = "metasqlcheck" then success("TRUE") else
   if retries =< 0 then failure else
   with value = make_unlikely_value(type),
   if query_function("SELECT COUNT("+column_name+") FROM "+table_name+
                     " WHERE "+column_name+"='"+value+"';") is
   {
     error(message)       then //print("###### "+message+"\n");
                               make_acceptable_value(type,table_name,column_name,query_function,retries-1),
     command_complete     then make_acceptable_value(type,table_name,column_name,query_function,retries-1),
     table(rows)          then //print(format(rows));
        if rows is
        {
          [ ] then make_acceptable_value(type,table_name,column_name,query_function,retries-1),
          [row1 . _] then if row1 is
             {
                [ ] then make_acceptable_value(type,table_name,column_name,query_function,retries-1),
                [cell1 . _] then if cell1 is
                  {
                     null then make_acceptable_value(type,table_name,column_name,query_function,retries-1),
                     not_null(b) then if decimal_scan(to_string(b)) is
                       {
                         failure then make_acceptable_value(type,table_name,column_name,query_function,retries-1),
                         success(n) then //print("%%%% n = "+to_decimal(n)+"\n");
                           if n = 0
                           then success(value)
                           else make_acceptable_value(type,table_name,column_name,query_function,retries-1)
                       }
                  }
             }
        },
     informations(infos)  then make_acceptable_value(type,table_name,column_name,query_function,retries-1),
   }.


define Maybe((String,String))     // returns the name of the column and the generated value
   make_acceptable_value
   (
     String                               table_name,
     MetaSQL_ColStruct                    cs,
     String -> MetaSQL_Answer             query_function,
     Int                                  retries
   ) =
   if cs is col(name,type,attrs) then
   if unique:attrs
   then if make_acceptable_value(type,table_name,name,query_function,retries) is
        {
          failure         then failure,
          success(value)  then success((name,value))
        }
   else success((name,make_unlikely_value(type))).


define Maybe(List((String,String)))
   make_acceptable_values
   (
     String                               table_name,
     List(MetaSQL_ColStruct)              columns,
     String -> MetaSQL_Answer             query_function,
     Int                                  retries
   ) =
   if columns is
   {
     [ ] then success([ ]),
     [h . t] then
       if make_acceptable_value(table_name,h,query_function,retries) is
       {
         failure then failure,
         success(first) then if make_acceptable_values(table_name,t,query_function,retries) is
         {
           failure then failure,
           success(rest) then success([first . rest])
         }
       }
   }.


define String
   format_values
   (
     List(String) values
   ) =
   "{|"+concat(values,"||")+"|}\n".


public type CheckTableResult:
   table_does_not_exist             (String                       table_name),
   cannot_create_acceptable_values  (String                       table_name),
   cannot_insert_record             (String                       table_name),
   cannot_get_record                (String                       table_name),
   found_null_value                 (String                       table_name,
                                     List(String)                 original_values,
                                     List(MaybeNull(ByteArray))   returned_values),
   got_different_values             (String                       table_name,
                                     List(String)                 original_values,
                                     List(String)                 returned_values),
   got_several_records              (String                       table_name,
                                     Int                          num_records),
   ok                               (String                       table_name).

public define String
   format
   (
     CheckTableResult   r
   ) =
   if r is
   {
     table_does_not_exist(table_name) then
       "Table '"+table_name+"' does not exist.",
     cannot_create_acceptable_values(table_name)  then
       "Cannot create acceptable values for table '"+table_name+"'.",
     cannot_insert_record(table_name)  then
       "Cannot insert record in table '"+table_name+"'.",
     cannot_get_record(table_name)  then
       "Cannot retrieve my record from table '"+table_name+"'.",
     found_null_value(table_name,original_values,returned_values)  then
       "Found null values in record retrieved from table '"+table_name+"'.",
     got_different_values(table_name,original_values,returned_values)  then
       "Got different values in record retrieved from table '"+table_name+"'.\n"+
       format_values(original_values)+format_values(returned_values),
     got_several_records(table_name,num_records)  then
       "Got "+to_decimal(num_records)+" times my record from table '"+table_name+"'.",
     ok(table_name)  then
       "Table '"+table_name+"' is ok."
   }.


define Maybe(List(String))
   non_null_values
   (
     List(MaybeNull(ByteArray))  l
   ) =
   if l is
   {
     [ ] then success([]),
     [h . t] then
       if h is
       {
         null         then failure,
         not_null(v)  then if non_null_values(t) is
           {
             failure then failure,
             success(rest) then success([to_string(v) . rest])
           }
       }
   }.

public define CheckTableResult
   check_the_table
   (
     MetaSQL_TabStruct                    tab,
     MetaSQL_Specific                     spec,
     Int                                  retries
   ) =
   since tab is table(tname,_,cols),
   if table_exists(tab,spec) is cannot_create_table(_)
   then table_does_not_exist(tname)
   else with qf = spec.query_function,
        if make_acceptable_values(tname,cols,qf,retries) is
        {
          failure then cannot_create_acceptable_values(tname),
          success(names_vals) then
            with names = map(((String,String) p) |-> if p is (n,v) then n,names_vals),
                 vals  = map(((String,String) p) |-> if p is (n,v) then v,names_vals),
            if qf("INSERT INTO "+tname+" ("+concat(["metasqlcheck" . names],",")+") VALUES ("+
                 concat(["'TRUE'" . map((String v) |-> "'"+v+"'",vals)],",")+");") is
             {
               error(message)       then //print(message+"\n");
                                         cannot_insert_record(tname),
               command_complete     then
                 with query = "SELECT "+concat(names,",")+" FROM "+tname+" WHERE metasqlcheck='TRUE';",
                 //print("query: "+query+"\n");
                 if qf(query) is
                 {
                   error(message)       then cannot_get_record(tname),
                   command_complete     then cannot_get_record(tname),
                   table(rows)          then
                     if rows is
                     {
                       [ ] then cannot_get_record(tname),
                       [h . t] then
                         if t is [ ]
                         then if non_null_values(h) is
                           {
                             failure then found_null_value(tname,vals,h),
                             success(values) then
                               if vals = values
                               then ok(tname)
                               else //print("length(vals) = "+length(vals)+" length(values) = "+length(values)+"\n" );
                                    got_different_values(tname,vals,values)
                           }
                         else got_several_records(tname,length(rows))
                     },
                   informations(infos)  then cannot_get_record(tname),
                 },
               table(rows)          then cannot_insert_record(tname),
               informations(infos)  then cannot_insert_record(tname)
             }
        }.


define One
   cleanup_check_data
   (
     List(MetaSQL_TabStruct)              tables,
     MetaSQL_Specific                     spec,
   ) =
   map_forget((MetaSQL_TabStruct ts) |->
         spec.query_function("DELETE FROM "+name(ts)+" WHERE metasqlcheck='TRUE';"),tables).


public define List(CheckTableResult)
   check_the_database
   (
     MetaSQL_DBStruct                     db,
     MetaSQL_Specific                     spec,
     Int                                  retries
   ) =
   if db is database(name,tables) then
   with result = map((MetaSQL_TabStruct ts) |-> check_the_table(ts,spec,retries),tables),
   cleanup_check_data(tables,spec);
   result.


   *** [] 'database_check'.

   This function proceeds as follows:

   (1) Try to recover the previous description of the database. If not possible,
       this description is assumed to be empty (no table at all).
   (2) Check the current description, and if correct, save it into the database.
   (3) Compare the two descriptions, old and new, and
      (3.1) create tables required by the new description, not present (by their
            name) in the previous one,
      (3.2) create columns required by the new description, not present in
            the previous tables,
      (3.3) update type of columns if required,
      (3.4) update attributes of columns if required,
      (3.5) create the indexes required by the new description and delete
            any index required by the previous one but not by the new one.


      *** [] Collecting actions to be performed.

   In order to prepare this work, it is interesting to record all operations into
   a description before excuting them. This also allows to inform on what has to
   be done.

 public type MetaSQL_DBModify:
   create_table          (MetaSQL_TabStruct   table),         // table to be created
   create_column         (String              table_name,     // ordinary column to be created
                          MetaSQL_ColStruct   column),
   create_pk_column      (String              table_name),
   set_type              (String              table_name,     // change column type
                          String              column_name,
                          MetaSQL_Type        new_type),
   reset_attribute       (String              table_name,     // remove (reset) this attribute
                          String              column_name,
                          MetaSQL_Attr        attr),
   set_attribute         (String              table_name,     // set this attribute
                          String              column_name,
                          MetaSQL_Attr        attr),
   delete_table          (String              table_name),
   delete_column         (String              table_name,
                          String              column_name).


define String
   format
     (
       MetaSQL_Type t
     ) =
   if t is
     {
       anubis(_T)                          then "anubis("+_T+")",
       binary                              then "binary",
       binary_or_null                      then "binary_or_null",
       boolean                             then "boolean",
       boolean_or_null                     then "boolean_or_null",
       date                                then "date",
       date_or_null                        then "date_or_null",
       time                                then "time",
       time_or_null                        then "time_or_null",
       datetime                            then "datetime",
       datetime_or_null                    then "datetime_or_null",
       foreign(String table_name)          then "foreign("+table_name+")",
       foreign_or_null(String table_name)  then "foreign_or_null("+table_name+")",
       integer16                           then "integer16",
       integer16_or_null                   then "integer16_or_null",
       integer32                           then "integer32",
       integer32_or_null                   then "integer32_or_null",
       integer                             then "integer",
       integer_or_null                     then "integer_or_null",
       char(Int size)                      then "char("+size+")",
       char_or_null(Int size)              then "char_or_null("+size+")",
       text                                then "text",
       text_or_null                        then "text_or_null"
     }.


define String
   format
     (
       MetaSQL_Attr a
     ) =
   if a is
     {
       unique      then "unique"
       indexed     then "indexed"
       default(e)  then "default("+value(e)+")"
     }.

 define String
   format
     (
       MetaSQL_DBModify  c
     ) =
   "*** Database action: "+
   if c is
     {
       create_table(t)                  then "create the table "+name(t)
       create_column(tn,col)            then "create the column "+name(col)+" in table "+tn
       create_pk_column(tn)             then "create a primary key column in table "+tn
       set_type(tn,cn,type)             then "set type of column "+cn+" to "+format(type)+" in table "+tn
       reset_attribute(tn,cn,a)         then
         if a is unique
         then "*** Error: resetting attribute unique for an already existing column ("+tn+"."+cn+") is impossible"
         else "reset attribute "+format(a)+" for column "+cn+" in table "+tn
       set_attribute(tn,cn,a)           then
         if a is unique
         then "*** Error: setting attribute unique for an already existing column ("+tn+"."+cn+") is impossible"
         else "set attribute "+format(a)+" for column "+cn+" in table "+tn
       delete_table(tn)                 then "delete table "+tn
       delete_column(tn,cn)             then "delete column "+cn+" from table "+tn
     }.


   The next function gets the name of a table and a list of tables. It extracts (if possible)
   the table with the given name from the list. It returns a pair made of the extracted
   table and the list of remaining tables.

 define Maybe((MetaSQL_TabStruct        extracted,
              List(MetaSQL_TabStruct)  others))
   extract_table
     (
       String                   table_name,
       List(MetaSQL_TabStruct)  l
     ) =
    if l is
      {
        [ ] then failure,    // table not found
        [h . t] then
          if name(h) = table_name
          then success((h,t))
          else if extract_table(table_name,t) is
            {
              failure then failure,
              success(p) then if p is (a,r) then success((a,[h . r]))
            }
      }.


   The same one for a column.

 define Maybe((MetaSQL_ColStruct        extracted,
              List(MetaSQL_ColStruct)  others))
   extract_column
     (
       String                   column_name,
       List(MetaSQL_ColStruct)  l
     ) =
    if l is
      {
        [ ] then failure,    // column not found
        [h . t] then
          if name(h) = column_name
          then success((h,t))
          else if extract_column(column_name,t) is
            {
              failure then failure,
              success(p) then if p is (a,r) then success((a,[h . r]))
            }
      }.


 define String
   index_name
     (
       String tn,
       String cn
     ) =
    tn+"_"+cn+"_index".


   *** Finding the actions to be performed.

   The function 'db_actions' takes two database descriptions (the old one and the new one),
   and creates the list of all actions to be performed.


   The function 'set_type_action' produces either no action or a single 'set_type' action.

 define List(MetaSQL_DBModify)
   set_type_action
     (
       MetaSQL_Specific   spec,
       String             table_name,
       String             col_name,
       MetaSQL_Type       old_type,
       MetaSQL_Type       new_type
     ) =
   with tdt = spec.to_database_type,
   if tdt(old_type) = tdt(new_type)
   then []
   else [set_type(table_name,col_name,new_type)].


   The function 'reset_attribute_actions' produces the 'reset_attribute' actions for
   a given column.

 define List(MetaSQL_DBModify)
   reset_attribute_actions
     (
       String                table_name,
       String                col_name,
       List(MetaSQL_Attr)    old_attrs,
       List(MetaSQL_Attr)    new_attrs
     ) =
   if old_attrs is
     {
       [ ] then [ ],
       [attr1 . others] then
         if attr1:new_attrs
         then reset_attribute_actions(table_name,col_name,others,new_attrs)
         else [reset_attribute(table_name,col_name,attr1)
               . reset_attribute_actions(table_name,col_name,others,new_attrs)]
     }.

   The same one for setting the attributes

 define List(MetaSQL_DBModify)
   set_attribute_actions
     (
       String                table_name,
       String                col_name,
       List(MetaSQL_Attr)    old_attrs,
       List(MetaSQL_Attr)    new_attrs
     ) =
   if new_attrs is
     {
       [ ] then [ ],
       [attr1 . others] then
         if attr1:old_attrs
         then set_attribute_actions(table_name,col_name,old_attrs,others)
         else [set_attribute(table_name,col_name,attr1)
               . set_attribute_actions(table_name,col_name,old_attrs,others)]
     }.

   The function 'delete_index_column_set_actions' finds all the 'delete_index',
   'delete_column', 'set_type', 'reset_attribute' and 'set_attribute' actions to be
   performed for a given table.

 define List(MetaSQL_DBModify)
   delete_index_column_set_actions
     (
       MetaSQL_Specific          spec,
       String                    table_name,
       List(MetaSQL_ColStruct)   old_cols,
       List(MetaSQL_ColStruct)   new_cols
     ) =
   if old_cols is
     {
       [ ] then [ ],
       [oldcol1 . others] then if oldcol1 is col(cname,old_type,old_attrs) then
         if find_column(cname,table_name,new_cols) is
           {
             failure then
               [delete_column(table_name,cname) .
                       delete_index_column_set_actions(spec,table_name,others,new_cols)],
             success(newcol1) then if newcol1 is col(_,new_type,new_attrs) then
               set_type_action(spec,table_name,cname,old_type,new_type) +
               reset_attribute_actions(table_name,cname,old_attrs,new_attrs) +
               delete_index_column_set_actions(spec,table_name,others,new_cols)
           }
     }.

   The function 'delete_actions' finds all the 'delete_?' actions to be performed.

 define List(MetaSQL_DBModify)
   delete_set_actions
     (
       MetaSQL_Specific          spec,
       List(MetaSQL_TabStruct)   old_tables,
       List(MetaSQL_TabStruct)   new_tables
     ) =
   // for each old table, find what has changed
   if old_tables is
     {
       [ ] then [ ],
       [oldtab1 . others] then if oldtab1 is table(name,pk,cols) then
         if find_table(name,new_tables) is
           {
             failure then
               [delete_table(name) . delete_set_actions(spec,others,new_tables)],
             success(newtab1) then
               delete_index_column_set_actions(spec,name,columns(oldtab1),columns(newtab1))
                  + delete_set_actions(spec,others,new_tables)
           }
     }.


 define Maybe(MetaSQL_Prepared)
   has_default_value
     (
       List(MetaSQL_Attr)   attrs
     ) =
   if attrs is
     {
       [ ] then failure,
       [a1 . others] then
         if a1 is default(s)
         then success(s)
         else has_default_value(others)
     }.

   The function 'create_column_index_actions' finds the 'create_column' and 'create_index'
   actions to be performed.

 define List(MetaSQL_DBModify)
   create_column_index_actions
     (
       String                     table_name,
       List(MetaSQL_ColStruct)    old_cols,
       List(MetaSQL_ColStruct)    new_cols
     ) =
   if new_cols is
     {
       [ ] then [ ],
       [newcol1 . others] then if newcol1 is col(cname,ctype,attrs) then
         if find_column(cname,table_name,old_cols) is
           {
             failure    then
               [create_column(table_name,newcol1)] +
               (if indexed:attrs
                then [set_attribute(table_name,cname,indexed)]
                else [ ])
             success(oldcol1) then
               (if ((indexed:attrs) & not(indexed:attributes(oldcol1)))
                then [set_attribute(table_name,cname,indexed)]
                else [ ])
           } +
         create_column_index_actions(table_name,old_cols,others)
      }.


   The function 'create_pk_column_index_actions' finds the 'create_pk_column' actions
   and adds the corresponding mandatory 'create_index' actions.

 define List(MetaSQL_DBModify)
   create_pk_column_action
     (
       MetaSQL_TabStruct   old_table,
       MetaSQL_TabStruct   new_table
     ) =
   if new_table is table(tname,new_pk,_) then
   if old_table is table(_,old_pk,_) then
   if (old_pk,new_pk) = (no_pk,with_pk)
   then [create_pk_column(tname)]
   else [ ].


   The function 'create_actions' finds all the 'create_table', 'create_clumn' and 'create_index'
   actions to be performed.

 define List(MetaSQL_DBModify)
   create_actions
     (
       List(MetaSQL_TabStruct)   old_tables,
       List(MetaSQL_TabStruct)   new_tables
     ) =
   if new_tables is
     {
      [ ] then [ ],
      [newtab1 . others] then if newtab1 is table(tname,new_pk,new_cols) then
        if find_table(tname,old_tables) is
          {
            failure then
              [create_table(newtab1) . create_actions(old_tables,others)],
            success(oldtab1) then if oldtab1 is table(_,old_pk,old_cols) then
              create_column_index_actions(tname,old_cols,new_cols) +
              create_pk_column_action(oldtab1,newtab1) +
              create_actions(old_tables,others)
          }
     }.


 define List(MetaSQL_DBModify)
   db_actions
     (
       MetaSQL_Specific          spec,
       List(MetaSQL_TabStruct)   old_tables,
       List(MetaSQL_TabStruct)   new_tables
     ) =
   delete_set_actions(spec,old_tables,new_tables) +
   create_actions(old_tables,new_tables).


      *** [] Executing modifications.


 define String uda = "Unexpected database Answer".


 public define Result(String /* error message */, One)
   execute  // execute a database modification
     (
       MetaSQL_Specific            spec,
       MetaSQL_DBModify            modif
     ) =
   with qf = spec.query_function,
   if // first 'if' is for testing the result of the query (type of test: MetaSQL_Answer)
   if // second 'if' is for deciding which query must be sent (type of test: MetaSQL_Modify)
   modif is   // 'is' for the second 'if'
     {
       create_table(tab)  then
         qf(if tab is table(name,pk,columns) then
            "CREATE TABLE "+name+" ("+
            if pk is
              {
                with_pk       then "id bigserial NOT NULL UNIQUE PRIMARY KEY, ",
                no_pk         then ""
              } + concat(map((MetaSQL_ColStruct c) |-> format_column_def(spec,c),columns),", ") +


                 ");"),
                // ") DISTRIBUTE BY REPLICATION;"),

       create_column(table_name,c)  then
         if c is col(cname,ctype,attrs) then
         qf("ALTER TABLE "+table_name+" ADD "+cname+" "+spec.to_database_type(ctype)+
             (if nullable(ctype) then " NULL" else " NOT NULL")+
             (if unique:attrs then " UNIQUE" else "")+
             (if has_default_value(attrs) is
               {
                 failure     then ""
                 success(p)  then " DEFAULT "+value(p)
               })+
              ";"
             ),

       create_pk_column(table_name)  then
         qf("ALTER TABLE "+table_name+" ADD "+
           "id bigserial PRIMARY KEY UNIQUE NOT NULL;"),

       set_type(table_name,column_name,new_type)  then
         qf("ALTER TABLE "+table_name+" ALTER "+column_name+
                        " TYPE "+spec.to_database_type(new_type)+";"),

       reset_attribute(table_name,column_name,attr)  then
            if attr is
              {
                unique     then error("Cannot reset attribute UNIQUE in "+table_name+"."+column_name)
                indexed    then
                  qf("DROP INDEX "+index_name(table_name,column_name)+" RESTRICT;")
                default(p) then
                  qf("ALTER TABLE "+table_name+" ALTER "+column_name+" DROP DEFAULT"+";")
              },

       set_attribute(table_name,column_name,attr)  then
            if attr is
              {
                unique     then error("Cannot set attribute UNIQUE in "+table_name+"."+column_name)
                indexed    then
                  qf("CREATE INDEX "+index_name(table_name,column_name)+
                                 " ON "+table_name+" ("+column_name+");")
                default(p) then
                  qf("ALTER TABLE "+table_name+" ALTER "+column_name+" SET DEFAULT "+value(p)+";")
              },

       delete_table(table_name)  then
         qf("DROP TABLE "+table_name+" RESTRICT;"),

       delete_column(table_name,column_name)  then
         qf("ALTER TABLE "+table_name+" DROP COLUMN "+column_name+" RESTRICT;")

     } is // 'is' for the first 'if'
        {
          error(msg)          then error(msg),
          command_complete    then ok(unique),
          table(_)            then error(uda)
          informations(i)     then error(i)
        }.


 define Result(String /* error message*/, One)
   create_empty_control_table
     (
       String -> MetaSQL_Answer    query_function,
       String                      database_name
     ) =
   transaction(query_function)(
           (String -> MetaSQL_Answer qf) |->
              if qf("CREATE TABLE MetaSQL_Description (previous text);") is
                {
                  error(msg)        then error(msg),
                  command_complete  then
                    if qf("INSERT INTO TABLE MetaSQL_Description (previous) "+
                          "VALUES ('"+
                                   value(prepare_anubis(
                                      (MetaSQL_DBStruct)database(database_name,
                                                                 [ /* empty */ ])))+
                                   "');") is
                      {
                        error(msg)        then error(msg)
                        command_complete  then ok(unique)
                        table(_)          then error(uda)
                        informations(i)   then error(i)
                      }
                  table(_)          then error(uda)
                  informations(i)   then error(i)
                }).


 define Maybe(MetaSQL_DBStruct)
   extract_description
     (
       List(List(MaybeNull(ByteArray)))  rows
     ) =
   if rows is
     {
       [ ] then failure,
       [h . t] then
         if h is
           {
             [ ] then failure,
             [d . _] then if d is
               {
                 null         then failure,
                 not_null(e)  then (Maybe(MetaSQL_DBStruct))unserialize(base64_decode(e))
               }
           }
     }.


 define MetaSQL_DBStruct
   retrieve_previous_description
     (
       MetaSQL_Specific   spec
     ) =
   if spec is specific(db_name,qf,_) then
   if qf("SELECT * FROM MetaSQL_Description;") is
     {
       error(msg)           then database(db_name,[]),
       command_complete     then database(db_name,[]),
       table(rows)          then
         if extract_description(rows) is
           {
             failure then database(db_name,[]),
             success(old_db) then old_db
           }
       informations(infos)  then database(db_name,[])
     }.


 public define One
   database_check
     (
       MetaSQL_Specific             spec,
       MetaSQL_DBStruct             db
     ) =
   if spec is specific(db_name,qf,to_database_type) then
   with old_db = retrieve_previous_description(spec),
       actions = db_actions(spec,tables(old_db),tables(db)),
     map_forget((MetaSQL_DBModify c) |->
                   with msg = format(c)+"\n",
                   if msg = "" then unique else print(msg), actions);
     map_forget((MetaSQL_DBModify c) |->
                   if execute(spec,c) is
                     {
                       error(msg) then print("*** Database error: "+msg+"\n")
                       ok(_)      then unique
                     }, actions).