Anubis / Anubis


 *Project*                                   Anubis
   
 *Title*                            SQLite wrapper library
   
 *Copyright*                     Copyright (c) David René 2005-2006. 

   
   
 *Author*       David René

 *Created*      2005 12 27
 *Revised*      july 2006
 *Satus*        Released
 *Compatibility* 1.7.1
   
 *Overview*
 This library is intended to make easy to use the SQLite Database. We tried to made
 just all necessaries functions for using SQLite with no Dr Shadoko's principle.
 All boring stuff are made by this library. But if, we forgotten some function, you are welcome
 to add them in this file.

 *Public*  
   
read tools/basis.anubis   
   
 *Description* 
   
   This function print on current output, the content of datatabase table. The content of table
   result of sql_query. All rows and columns are stored in list of list of string. the result of
   print are show below.

   Row_1_String1 | Row1_String2 | Row1_String3
   Row_2_String1 | Row2_String2 | Row2_String3
   ...

public type DB_Result($T):
  error,
  empty,
  success($T).
  
public define One 
  db_print_table
  ( 
    List(List(String)) l 
  ) =
   if l is 
     {
       [ ] then print("\n"), 
       [h . t] then 
         print("\n| ");
         forget(map((String s) |-> print(s+" | "),h));
         db_print_table(t)
     }.

define One
   db_print_row
     (
       Int32 -> SQLite3Datum   row,
       Int32                   i
     ) =
   if row(i) is 
     {
       no_such_column        then print("|"), 
       integer(n)            then print(" "+n+" |"); db_print_row(row,i+1),
       float(f)              then print(" "+float_to_string(f,2)+" |"); db_print_row(row,i+1), 
       text(t)               then print(" "+t+" |"); db_print_row(row,i+1),
       blob(b)               then print(" <blob "+length(b)+"> |"); db_print_row(row,i+1),
       null                  then print(" NULL |"); db_print_row(row,i+1)
     }.
   
public define Int32 -> String
    text
      (
        Int32 -> SQLite3Datum row
      ) =
    (Int32 i) |->
    if row(i) is
    {
       no_such_column        then "N/A", 
       integer(n)            then integer_to_string(n),
       float(f)              then float_to_string(f,2), 
       text(t)               then t,
       blob(b)               then " <blob "+length(b)+">",
       null                  then " NULL "
    }.
   
public define Int32 -> Maybe(Int32)
    db_integer
      (
        Int32 -> SQLite3Datum row
      ) =
    (Int32 i) |->
    if row(i) is integer(n) then 
      success(n)
    else
      failure.

public define Int32 -> Int32
    db_integer
      (
        Int32 -> SQLite3Datum row
      ) =
    (Int32 i) |->
    if row(i) is integer(n) then 
      n
    else
      0.
     
public define Int32 -> Bool
    db_bool
      (
        Int32 -> SQLite3Datum row
      ) =
    (Int32 i) |->
    if row(i) is integer(n) then 
      if n = 1 then 
        true
      else
        false
    else
      false.

public define List(String)
  db_make_string_list
  (
    List(String)              so_far,
    SQLite3HeadersOrRow -> SQLite3Row cursor
  ) =
  if cursor(next_row) is
  {
    error(_)      then so_far,
    no_more_row   then so_far,
    row(explorer) then 
      with data = text(explorer)(0),
        db_make_string_list( [ data . so_far], cursor)
  }
  .
public define One 
   db_print_table
     (
       One -> Maybe(Int32 -> SQLite3Datum)    cursor
     ) =
   if cursor(unique) is 
     {
       failure then print("\n"),
       success(row) then 
         print("\n|"); 
         db_print_row(row,0);
         db_print_table(cursor)
     }.

define Int32
   count_rows_private
     (
       SQLite3HeadersOrRow -> SQLite3Row  cursor,
       Int32  so_far
     ) =
  if cursor(next_row) is 
  {
    error(_)    then 0,  
    no_more_row then so_far,
    row(_)      then count_rows_private(cursor, so_far + 1)
  }.

/** Count the number of rows in the cursor. So this function call
 *  itself recursively until the cursor become empty. At each call
 *  it count the row.
 *  @param cursor, Database cursor, which must be counted
 *  @param so_far number of recursive call 
 */ 
public define Int32
   count_rows
     (
       SQLite3HeadersOrRow -> SQLite3Row  cursor
     ) =
  if cursor(next_row) is 
  {
    error(_)    then 0,
    no_more_row then 0,
    row(_)      then count_rows_private(cursor, 1)
  }.

   
  *Description*
  
 public define Maybe(List(String)) db_get_row(Int32 row_index, List(List(String)) row_list) =
  if row_list is
  {
    [] then failure,
    [h . t] then
      if row_index = 0 then success(h)
      else db_get_row(row_index-1, t)
  }.

 public define Maybe(String) db_row_get_datum(Int32 idx, List(String) data_list) =
  if data_list is
  {
    [] then failure,
    [h . t] then
      if idx = 0 then success(h)
      else db_row_get_datum(idx-1, t)
  }.

 public define Maybe(String) db_get_datum(Int32 datum_index, Int32 row_index,  List(List(String)) rows_list) =
  if db_get_row(row_index, rows_list) is
  {
    failure then failure,
    success(row) then db_row_get_datum(datum_index, row) 
  }.


 *Public*  
   
 *Description* 
      
   This function print on current output, the database error number with your own message
   in msg

public define One print_db_error( Int32 n, String msg) =
  print("Database Error: "+ msg +"\n" + "code[" + integer_to_string(n) + "]\n").

public define One print_db_error( SQLite3Error err, String msg) =
  print("Database Error: "+ msg +"\n" + "code[" + err.code + "] "+err.text+"\n").

public define One print_db_error( SQLite3Error err) =
  print("Database Error: code[" + err.code + "] "+err.text+"\n").

 define One insertData( String theQuery) =
   if l is 
     {
       [ ] then print("\n"), 
       [h . t] then 
         print("\n| ");
         forget(map((String s) |-> print(s+" | "),h));
         print(t)
     }.

 define One master_table_each_entry( List(List(String)) l) =
   if l is 
     {
        [ ] then print(""),
        [ h . t ] then 
        if h is
        {
          [ ] then print(""),
          [ h . t ] then print("      type : " + h + "\n");
        if t is
        {
          [ ] then print(""),
          [ h . t ] then print("      name : " + h + "\n");
        if t is
        {
          [ ] then print(""),
          [ h . t ] then print("table name : " + h + "\n");
        if t is
        {
          [ ] then print(""),
          [ h . t ] then print("  rootpage : " + h + "\n");
        if t is
        {
          [ ] then print(""),
          [ h . t ] then print("sql string : " + h + "\n"); unique
        }}}}}; master_table_each_entry(t)
     }.

 public define One db_print_master_table( SQLite3DataBase db) =
    //make a query on the master table for ask all tables in database
    //the table sqlite_master can only list by user 
    if sql_query(db, "select * from sqlite_master") is
    {
      error(n) then db_print_error(n, "Can't list all database table"),
      ok(table) then
        print("MASTER TABLE list\n");
        master_table_each_entry(table)
    }.

 public define Bool db_table_exists( SQLite3DataBase db, String table) =
  //if sql_query(db, "select " + table + " from sqlite_master") is
  if sql_query(db, "select tbl_name from sqlite_master where tbl_name = '"+table+"'") is
  {
    error(n) then print("e Table '"+table+"' does not exist.\n"); false,
    ok(table_content) then 
    if table_content is
    {
      [] then print("o Table '"+table+"' does not exist.\n"); false,
      [ h. t] then print("content of table " +table + ":");db_print_table(table_content); true
    }
  }.
   
   
public define Bool db_table_exists_test( SQLite3DataBase db, String table) =
  if sql_query(db, "select tbl_name from sqlite_master where tbl_name = '"+table+"'") is
  {
    error(n) then print("e Cannot read master_table.\n"); false,
    ok(table_content) then 
      if table_content(next_row) is 
      {
        error(_)    then false,
        no_more_row then print("Table "+table+" does not exist\n"); false, 
        row(row1)   then print("Table "+table+" exists\n"); true
      }
  }. 
   
   
   
public define Bool 
   db_table_exists
    (
      SQLite3DataBase   db, 
      String            table
    ) =
  if sql_query(db, "select tbl_name from sqlite_master where tbl_name = '"+table+"'") is
    {
      error(n)          then false,
      ok(table_content) then 
        if table_content(next_row) is 
        {
          error(_)    then false,
          no_more_row then false, 
          row(row1)   then true
        }
  }. 
   
   
   
define List(Word8)
	db_make_sql_string
	(
		List(Word8) str
	)
	=
	if str is
	{
		[ ] 		then [],
		[h . t] then with tail = db_make_sql_string(t),
			if h = '\'' then [h . [h . tail]]
			else [h . tail] 
	}.

/**
 * Convert to a SQL certified string and add quotes around.
 */
public define String
	db_make_sql_string
	(
		String str
	)
	=
	"'" + implode(db_make_sql_string(explode(str))) + "'".