Anubis / Xlib

/*
 * Created by PyramIDE.
 * User: フランスのトトロ 
 * Date: 01/01/2016
 * Time: 1:34
 * © Calexium 
 */

                                  The Anubis project

                          Interfacing and generating a database with Anubis


   Author: David René


   In order to generate such tools, DB_Model needs a formal
   description of the database. 
   
   
   *** (1) Formal description of the database. 
   
      *** (1.1) 'DB_Model_Field' (data types of table columns).
      
public type DB_Datetime_Field_Attr:
  none,           //Nothing special, normal behaviour
  auto_now,       //Always update datetime at SQL update
  auto_now_add.   //Set current datetime when row is created and can't be edited anymore
  
public type DB_Model_Field:    
   //anubis(String _T),                     // the datum of type _T is serialized and base64 encoded
   p_key,
   //binary,                                // contains a byte array
   boolean_field,                           // true or false
   date_field(DB_Datetime_Field_Attr),      // date with the precision of the day
   time_field(DB_Datetime_Field_Attr),      // time with the precision of the second
   datetime_field(DB_Datetime_Field_Attr),  // datetime with the precision of the second
   foreign_key(String table_name),          // foreign key to 'id' in another (or same) table and column_name for choice selector.
   integer_field,                           // integer of arbitrary size
   char_field(Int size),                    // text of maximal size 'size' (number of characters)
   text_field.                              // text of variable size

   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)
   p_key           | DB_id             | primary key (integer)
   //binary        | ByteArray         | text (the byte array base64 encoded)
   boolean_field   | Bool              | boolean
   date_field      | DB_date           | text (date in ISO-8601 format: yyyy-mm-dd)
   time_field      | DB_time           | text (date in ISO-8601 format: hh:mm:ss)
   datetime_field  | DB_datetime       | text (date in ISO-8601 format: yyyy-mm-dd hh:mm:ss)
   foreign_key     | Int               | integer
   integer_field   | Int               | arbitrary size integer (numeric)
   char_field      | String            | max size text
   text_field      | String            | arbitrary size text

   
   
      *** (1.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'. 
   
   
      *** (1.3) 'DB_Model_Attr' (attributes of columns).
  
   Possible attributes of columns: (each column has a list of such attributes)

public type DB_Model_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(String).             // default value (used in case of creation of a NON NULL column
                                //   in a table which already contains some rows)

public type DB_Help_Text:
  no_help_text,                 //no help text available
  help_text_TAG(String),        //TAG
  help_text(String).            //TAG

public define String
  to_Anubis_source
  (
    DB_Help_Text help
  )=
  if help is
  {
    no_help_text        then  "no_help_text",
    help_text_TAG(tag)  then  "help_text_TAG(\""+tag+"\")",
    help_text(str)      then  "help_text(\""+str+"\")"
  }.
  
   Of course, 'NOT NULL' is not an alternative of 'DB_Model_Attr' because it is already coded into the 
   database type itself (type 'DB_Model_Field' above). 


      *** (1.4) 'DB_Model_Column' (describing a column in a table). 

   Description of a column:

public type DB_Model_Column:     
   db_column (String              name,               // name of ordinary column (i.e. all but 'id')
              DB_Model_Field      type, 
              List(DB_Model_Attr) attributes,
              DB_Help_Text        help
              ).

public define DB_Model_Column
  db_column
  (
    String              name,
    DB_Model_Field      type,
    List(DB_Model_Attr) attributes,
  )=
  db_column(name, type, attributes, no_help_text).
  
public define DB_Model_Column
  db_column
  (
    String          name,
    DB_Model_Field  type   
  )=
  db_column(name, type, [], no_help_text).
  

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

   Description of a table:

public type DB_Model:
  db_model( List(DB_Model_Column)  columns,    // columns other than the primary key column (if any)
            String                 show_string).


public type DB_Display:
  db_display(List(String) list_view,
             List(String) edit_view).
             
public type DB_Table:           
   db_table ( String     name,   // name of the table
              DB_Model   model,
              DB_Display display).

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

   Description of a whole database:

public type DB_Database:       
   db_database  (String             name,       // name of the database
                List(DB_Table)     tables).    
// list of all tables in the database
  

        
//public define Maybe(String)            convert_to_date               (MaybeNull(ByteArray) b). 
//public define MaybeNull(String)        convert_to_date_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 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'. 
      
   --- 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)
         }
     }.


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

   
 define Bool   
   has_forbidden_references
     (
       String                      table_name,     // the first 3 arguments concern the current table. 
       MetaSQL_PrimKey             pk, 
       List(DB_Model_Column)     columns, 
       List(DB_Model_Column)     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(DB_Model_Column)    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
   (
     DB_Model_Column   tab
   ) =
   if tab is table(_,_,cols) then 
   mapor((DB_Model_Column cs) |-> 
            if cs is col(name,_,_) then is_forbidden_column_name(name),
         cols).
   
 define Bool
   has_forbidden_column_names
   (
     DB_Model_DB   db
   ) =
   if db is database(_,tables) then 
   mapor(has_forbidden_column_names,tables). 
         

   
   *** [3] Generating the target file. 
   
      *** [3.1] Converting a database type into the corresponding Anubis type: 

public define String
  to_Anubis_type
  (
    DB_Model_Field  t
  ) =
  if t is 
  {
    //anubis(_T)                          then "ByteArray", 
    p_key                               then "DB_id",
    //binary                              then "ByteArray",
    boolean_field                       then "Bool",
    date_field(_)                       then "DB_date",
    time_field(_)                       then "DB_time",
    datetime_field(_)                   then "DB_datetime",      
    foreign_key(_)                      then "Int",
    integer_field                       then "Int",
    char_field(Int size)                then "String",
    text_field                          then "String"
  }.


public define String
  to_Anubis_default
  (
    DB_Model_Field  t
  ) =
  if t is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                               then "none",
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                       then "false",
    date_field(_)                       then "db_date(\"\")",
    time_field(_)                       then "db_time(\"\")",
    datetime_field(_)                   then "db_datetime(\"\")",      
    foreign_key(_)                      then "0",
    integer_field                       then "0",
    char_field(Int size)                then "\"\"",
    text_field                          then "\"\""
  }.

public define String
  from_DB_cursor
  (
    DB_Model_Field  t,
    String         cursor,
    Int            index
  ) =
  with cursor_index = "("+cursor+")("+index+")",
  if t is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                               then "db_id((Int)db_integer"+cursor_index+")",
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                       then "db_bool"+cursor_index,
    date_field(_)                       then "db_date(text"+cursor_index+")",
    time_field(_)                       then "db_time(text"+cursor_index+")",
    datetime_field(_)                   then "db_datetime(text"+cursor_index+")",      
    foreign_key(_)                      then "(Int)db_integer"+cursor_index,
    integer_field                       then "(Int)db_integer"+cursor_index,
    char_field(Int size)                then "text"+cursor_index,
    text_field                          then "text"+cursor_index
  }.

define List(String)
/** 
 */
  components
  (
    List(DB_Model_Column) columns,
    String                cursor_name,
    List(String)          so_far,
    Int                   idx
  )=
  if columns is
  {
    []        then reverse(so_far),
    [h . t ]  then
      since h is db_column(name, col_type, _, _),
      with line = fill(from_DB_cursor(col_type, cursor_name, idx), 35)+ "/* "+name+" */",
      components(t, cursor_name, [line . so_far], idx + 1 )
  }.
  
public define String
  generate_constructor_from_cursor
  (
    String                constructor_name,
    String                cursor_name,
    List(DB_Model_Column) columns,
    String                indent
  )=
  indent+constructor_name+"(\n"+indent+"  "+join(",\n"+indent+"  ", components(columns, cursor_name, [], 0))+"\n"+indent+")".


public define String
  from_web_arg
  (
    DB_Model_Field  t,
    String         name
  ) =
  if t is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                             then "with "+name+" = get_DB_id(lwa, \""+name+"\"),",
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                     then "with "+name+" = get_Bool(lwa, \""+name+"\"),",
    date_field(attrs)                 then if attrs = none then "get_DB_date(lwa, \""+name+"\")" else "with "+name+" = get_dummy_DB_date,",
    time_field(attrs)                 then if attrs = none then "get_DB_time(lwa, \""+name+"\")" else "with "+name+" = get_dummy_DB_time,",
    datetime_field(attrs)             then if attrs = none then "get_DB_datetime(lwa, \""+name+"\")" else "with "+name+" = get_dummy_DB_datetime,",      
    foreign_key(String table)         then "get_Int(lwa, \""+name+"\")",
    integer_field                     then "get_Int(lwa, \""+name+"\")",
    char_field(Int size)              then "get_String(lwa, \""+name+"\")",
    text_field                        then "get_String(lwa, \""+name+"\")"
  }.

public define List(String)
/** 
 */
  components
  (
    List(DB_Model_Column) columns,
    List(String)          so_far
  )=
  if columns is
  {
    []        then reverse(so_far),
    [h . t ]  then
      since h is db_column(name, col_type, _, _),
      components(t, [ name . so_far])
  }.
  
public define String
  generate_constructor
  (
    String  constructor_name,
    List(DB_Model_Column) columns,
    String  indent
  )=
  indent+constructor_name+"(\n"+indent+"  "+join(",\n"+indent+"  ", components(columns, []))+")".
  
public define String
  to_Bind
  (
    DB_Model_Field  t,
    String         type_name, //name of the type    
    String         name,      //name of component into the type
    Bool           insert     //true if insert time else false for update
  ) =
  if t is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                               then "",
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                       then "bind_Bool(\":v_"+name+"\", "+type_name+"."+name+")",
    date_field(attrs)                   then "bind_Date(\":v_"+name+"\", "+type_name+"."+name+")",
    time_field(attrs)                   then "bind_Time(\":v_"+name+"\", "+type_name+"."+name+")",
    datetime_field(attrs)               then  if attrs is 
                                              {
                                                none         then "bind_Datetime(\":v_"+name+"\", "+type_name+"."+name+")",
                                                auto_now     then "bind_Datetime(\":v_"+name+"\", now)",
                                                auto_now_add then
                                                  if insert then 
                                                    "bind_Datetime(\":v_"+name+"\", now)"
                                                  else 
                                                    ""
                                              },
    foreign_key(_)                          then "bind_Int(\":v_"+name+"\", "+type_name+"."+name+")",
    integer_field                     then "bind_Int(\":v_"+name+"\", "+type_name+"."+name+")",
    char_field(Int size)              then "bind_String(\":v_"+name+"\", "+type_name+"."+name+")",
    text_field                        then "bind_String(\":v_"+name+"\", "+type_name+"."+name+")"
  }.

public define List(String)
/** 
 */
  to_Bind_list
  (
    List(DB_Model_Column) columns,
    String                type_name, //name of the type    
    List(String)          so_far,
    Bool                  insert
  )=
  if columns is
  {
    []        then reverse(so_far),
    [h . t ]  then
      since h is db_column(name, col_type, _, _),
      with result = to_Bind(col_type, type_name, name, insert),
      //we eliminate the empty string because during the join it will have an empty line
      if result = "" then
        to_Bind_list(t, type_name, so_far, insert)
      else
        to_Bind_list(t, type_name, [result . so_far], insert)
  }.
  
public define String
  generate_Bind_list
  (
    List(DB_Model_Column) columns,
    String                type_name,
    String                indent,
    Bool                  insert
  )=
  indent+"[\n"+indent+"  "+join(",\n"+indent+"  ", to_Bind_list(columns, type_name, [], insert))+"]".


public define Maybe(String)
  get_column_name
  (
    DB_Model_Column col,
    Bool            insert     //true if insert time else false for update
  ) =
  since col is db_column(name, col_type, _, _),
  if col_type is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                               then failure,
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                       then success(name),
    date_field(attrs)                   then success(name),
    time_field(attrs)                   then success(name),
    datetime_field(attrs)               then  if attrs is 
                                              {
                                                none         then success(name),
                                                auto_now     then success(name),
                                                auto_now_add then
                                                  if insert then success(name) else failure
                                              },
    foreign_key(_)                      then success(name),
    integer_field                       then success(name),
    char_field(Int size)                then success(name),
    text_field                          then success(name)
  }.

public define List(String)
  columns_name
  (
    List(DB_Model_Column) columns,
    Bool                  insert,     //true if insert time else false for update
    List(String)          so_far
  ) =
  if columns is
  {
    []      then reverse(so_far),
    [h . t] then
      if get_column_name(h, insert) is
      { 
        failure         then columns_name(t, insert, so_far)
        success(result) then 
          if insert then
            columns_name(t, insert, [result . so_far])
          else
            columns_name(t, insert, [result+" = :v_"+result . so_far])
      }
  }.
  
public define String
/* 
 Generate the SQL "(xxx, yyy, ...) VALUES ( :v_xxx, :v_yyy, ...)" for the insert
 SQL query.
 */
  insert_values
  (
    List(DB_Model_Column) columns,
    String                indent
  )=
  with columns_list = columns_name(columns, true, []),
  indent+"  ("+join(", ", columns_list)+")\n"+indent+"VALUES\n"+indent+"  ("+prefixed_join(":v_", columns_list, ", ")+")".


public define String
/* 
 Generate the SQL "xxx = :v_xxx, yyy = :v_yyy, ..." for the update
 SQL query.
 */
  update_values
  (
    List(DB_Model_Column) columns,
    String                indent
  )=
  with columns_list = columns_name(columns, false, []),
  indent+"  "+join(", ", columns_list).


public define Maybe(String)
  get_VT_edit_entry
  (
    DB_Model_Column col,
    String          type_name
  ) =
  since col is db_column(name, col_type, _, help),
  if col_type is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                               then success("primary_key(to_String("+type_name+"."+name+"))"),
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                       then success("boolean(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+")"),
    date_field(attrs)                   then success("date(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+")"),
    time_field(attrs)                   then success("time(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+")"),
    datetime_field(attrs)               then  if attrs is 
                                              {
                                                none         then success("datetime(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+".datetime, "+to_Anubis_source(help)+")"),
                                                auto_now     then success("information(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+".datetime, "+to_Anubis_source(help)+")"),
                                                auto_now_add then success("information(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+".datetime, "+to_Anubis_source(help)+")")
                                              },
    foreign_key(_)                      then success("information(\"FOREIGN_TODO\", \""+name+"\", to_String("+type_name+"."+name+"), "+to_Anubis_source(help)+")"),
    integer_field                       then success("integer(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+")"),
    char_field(Int size)                      then success("text(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+", "+to_Anubis_source(help)+")"),
    text_field                          then success("text_area(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+")")
  }.

public define String
  edit_entries
  (
    List(DB_Model_Column) columns,
    String                type_name,    
    String                indent,
    List(String)          so_far
  ) =
  if columns is
  {
    []      then   indent+"  "+join(",\n"+indent+"  ", reverse(so_far)),
    [h . t] then
      if get_VT_edit_entry(h, type_name) is
      { 
        failure         then edit_entries(t, type_name, indent, so_far)
        success(result) then edit_entries(t, type_name, indent, [result . so_far])
      }
  }.

public define String
  make_list_view_header
  (
    List(String) list_view,
    String       indent
  )=
  indent+join(",\n"+indent, map((String text) |-> "text(\""+to_upper(text)+"\")", list_view)).

public define Maybe(DB_Model_Column)
  get_column_type
  (
    List(DB_Model_Column) columns,      //columns in wich we search 
    String                name_to_find  //Column name to find in previous list
  )=
  if columns is
  {
    []      then failure,
    [h . t] then
      since h is db_column(name, _, _, _),
      if name_to_find = name then
        success(h)
      else
        get_column_type(t, name_to_find)
  }.
  
public define String
  to_cell_view
  (
    DB_Model_Column col,
    String          type_name
  ) =
  since col is db_column(name, col_type, _, _),
  if col_type is 
  {
    //anubis(_T)                          then "constant_byte_array(0,0)", 
    p_key                               then "link(to_String("+type_name+".id), to_String("+type_name+".id))",
    //binary                              then "constant_byte_array(0,0)",
    boolean_field                       then "text(to_String("+type_name+"."+name+"))",
    date_field(attrs)                   then "text(to_String("+type_name+"."+name+"))",
    time_field(attrs)                   then "text(to_String("+type_name+"."+name+"))",
    datetime_field(attrs)               then "text(to_String("+type_name+"."+name+"))"
    foreign_key(foreign_table)          then "text(get_"+foreign_table+"_show_string(db, "+type_name+"."+name+"))",
    integer_field                       then "text(to_String("+type_name+"."+name+"))",
    char_field(Int size)                then "text("+type_name+"."+name+")",
    text_field                          then "text("+type_name+"."+name+")"
  }.
  
public define String
  to_cell_view
  (
    List(DB_Model_Column) columns,
    String                column_name,
    String                data_name
  )=
  if get_column_type(columns, column_name) is
  {
    failure          then "column name "+column_name+"doesn't exist",
    success(column)  then to_cell_view(column, data_name)
  }.
  
public define String
  make_list_view_row
  (
    DB_Model      model,
    List(String)  list_view,
    String        data_name,
    String        indent
  )=
  since model is db_model(columns, _),
  indent+join(",\n"+indent, map((String column_name) |-> to_cell_view(columns, column_name, data_name), list_view)).