/* * 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)"), auto_now then success("information(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+".datetime)"), auto_now_add then success("information(\""+to_upper(name)+"\", \""+name+"\", "+type_name+"."+name+".datetime)") }, foreign_key(_) then success("information(\"FOREIGN_TODO\", \""+name+"\", to_String("+type_name+"."+name+"))"), 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+")"), 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)).