ACDK SQLite

// acdk_sql_sqlite/cfg/csf/tests/acdk/sql/sqlite/acdk_sqlite_Standard_Test.csf
using acdk.sql.sqlite;
using acdk.sql;
using acdk.io;
/**
  this tests does not use the acdk.sql.* classes, but goes directly to the LiteDb classes
*/


File f = new File("acdk_sql_sqlite_test1.sqlite3");
if (f.exists() == true)
  f.deleteFile();

RString dbUrl = "jdbc:sqlite:/acdk_sql_sqlite_test1.sqlite3";
RDriver driver = DriverManager::getDriver(dbUrl);
RConnection connection = driver->connect(dbUrl, Nil);
{
  RStatement statement = connection->createStatement();
  statement->executeUpdate("CREATE TABLE ttable (intcol int, stringcol VARCHAR)");
  connection->createStatement()->executeUpdate("INSERT INTO ttable (intcol, stringcol) VALUES (1, 'first')");
  connection->createStatement()->executeUpdate("INSERT INTO ttable (intcol, stringcol) VALUES (2, 'second')");
  connection->createStatement()->executeUpdate("INSERT INTO ttable (intcol, stringcol) VALUES (3, 'third')");
}

{
  RStatement statement = connection->createStatement();
  RResultSet rset = statement->executeQuery("SELECT * from ttable"); 
  while (rset->next() == true) 
  {
    int intcol = rset->getInt(1);
    RString sval = rset->getString("stringcol");
    System::out->println("" + intcol + ": " + sval);
  }
}

// acdk_sql_sqlite/cfg/csf/tests/acdk/sql/sqlite/SQLiteTable_Test.csf
using acdk.sql.sqlite;
using acdk.io;
/**
  this tests does not use the acdk.sql.* classes, but goes directly to the LiteDb classes
*/

File f = new File("acdk_sql_sqlite_test1.sqlite3");
if (f.exists() == true)
  f.deleteFile();
  
LiteDb db = LiteDb.openDb(f.getName());

db.execute(
  "CREATE TABLE ttable (intcol int, stringcol VARCHAR);
   INSERT INTO ttable (intcol, stringcol) VALUES (1, 'first');
   INSERT INTO ttable (intcol, stringcol) VALUES (2, 'second');
   INSERT INTO ttable (intcol, stringcol) VALUES (3, 'third');
  "
  );

LiteMemTable table = db.execDirect("SELECT * from ttable");
table.printTable(out);

LiteMemTable table = db.execDirect("SELECT * from ttable");
out.println("use iteration:\n");
// iterate throw rows
foreach (LiteMemTableIterator r in table.iterator())
{
  // iterates through cols
  foreach (String f in r.iterator())
  {
    out.print(f + "|");  
  }
  out.println("");
}

// call for each row the delegate function
// this may be a lambda expression or a dynamic/static object/class method
// first version without signature and implicit NamedRest rest parameter
db.execute("SELECT * from ttable", 
  lambda 
  {
    // first argument is __db for the database
    // following arguments are the columns, named corresponding the column names in the SQL table
    out.println("intcol: " + intcol + "; stringcol: " + stringcol);
  }
  );
out.println("");

// second version with explicit method signature
// The fields of the row can be named different to the original SQL column names.
db.execute("SELECT * from ttable", 
  lambda int (LiteDb db, String aintcol, String astringcol)
  {
    out.println("intcol: " + aintcol + "; stringcol: " + astringcol);
    return 0;
  }
  );

// ...
LiteDb db = LiteDb.openDb(f.getName());
// create some entries
db.execute(
  "CREATE TABLE ttable (intcol int, stringcol VARCHAR);
   INSERT INTO ttable (intcol, stringcol) VALUES (1, 'first');
   INSERT INTO ttable (intcol, stringcol) VALUES (2, 'second');
   INSERT INTO ttable (intcol, stringcol) VALUES (3, 'third');
  "
  );

// now the important step
// we create a user defined function, which can
// be used inside a SQL expression
db.createSqlFunction(
  "dneg", // defining a function named "dneg" 
  1, // with 1 argument
  lambda         // use any DmiDelegate (here as lambda expression)
    int (int v)
    {
      return -v;  // does not much, just invert the value
    }
  );

// now we can use the new SQL function dneg in SQL commands
// the argument of dneg is the column ttable.intcol in the SELECT statement
LiteMemTable table = db.execDirect("SELECT dneg(ttable.intcol) as negitcol, stringcol from ttable");

// just print it
table.printTable(out);