/** this is test program for accesing sql databse
    as for now it requires Postgres database server running on localhost
    with database user 'postgres' (password 'sql')
 */

// NO-TEST
// REFERENCE: Npgsql.dll

using System;
using System.Data;
using Npgsql;
using Nemerle.Data;

// this is how compiler connects to database to validate queries occuring in program
[assembly: ConfigureConnection ("Npgsql.NpgsqlConnection", "Server=localhost;Database=test;"
                      "User ID=postgres;Password=sql;")]
public class Test 
 {
    static insert (conn : NpgsqlConnection, x : string, y : string) : void
    {
      /// this is the usage of macro ExecuteNonQuery, which creates Npgsql parameters
      /// [:x] and [:y], set their value to corresponding variables [x] and [y],
      /// verify syntax and type correctness of query at compile-time, and generating
      /// code for runtime insertion of data to database
      
      _ = ExecuteNonQuery ("INSERT INTO employee VALUES ($x, $y)", conn);
    }

    public static Main() : void
    {
    
       Console.WriteLine("start...");

       def connectionString = 
          "Server=localhost;" +
          "Database=test;" +
          "User ID=postgres;" +
          "Password=sql;";

       def dbcon = NpgsqlConnection (connectionString);
       dbcon.Open ();
       Console.WriteLine("connection opened...");

       // insert (dbcon, "Kocia", "Bicia");

       def myparm = "Kasia";

       // requires a table to be created named employee
       // with columns firstname and lastname
       // such as,
       //        CREATE TABLE employee (
       //           firstname varchar(32),
       //           lastname varchar(32));

       /// This is standard C#-like implementation of database inteoperation,
       /// whithout using Nemerle macros
       def sql = "SELECT (firstname) AS ff, lastname " +
           "FROM employee WHERE firstname = :a";

       def dbtran = dbcon.BeginTransaction ();
       def dbcmd = NpgsqlCommand (sql, dbcon, dbtran);
 
       _ = dbcmd.Parameters.Add("a", myparm);

       def reader = dbcmd.ExecuteReader();
       while(reader.Read()) {
         def firstname = reader.GetString (0);
         def lastname = reader.GetString (1);
         Nemerle.IO.printf ("Name: %s %s\n", firstname, lastname)            
       };
       reader.Close();
       dbtran.Rollback ();
       dbcmd.Dispose();

       /// and this is equivalent code utilizing ExecuteReaderLoop, which is
       /// a macro checking syntax and type validness of query at compile-time
       /// (by connecting to database)
       ExecuteReaderLoop ("SELECT * FROM employee WHERE firstname = $myparm", dbcon,
       {
         Nemerle.IO.printf ("Name: %s %s\n", firstname, lastname)
       });

       //// another examples using Nemerle sql macros       
       def tt = 4; def ty = "dfd4";
       /// CREATE TABLE intstr (a INT4, b VARCHAR(32));
       
       ExecuteReaderLoop (
          "SELECT a AS number, b, COUNT(*) AS amount FROM intstr " 
          "WHERE a = $tt or b = $ty GROUP BY a, b", dbcon, {
         Nemerle.IO.printf ("%d %s\n", number.Value, b);
         Console.WriteLine (amount)
       });

       def max = ExecuteScalar ("SELECT MAX(a) FROM intstr", dbcon);
       Nemerle.IO.printf ("%d\n", max);

       /// transaction mechanism used by macros' implementation prevents any
       /// data to be changed in database by compile-time queries
       /// here insertion is rolled back 
       _ = ExecuteReader ("INSERT INTO intstr VALUES (5, '7');"
                          " SELECT * FROM intstr;", dbcon);

       dbcon.Close();
    }
 }