' FreeBasic example for GNome Data Access library (libgda5.bi)
' (C) 2013-2014 by Thomas{ dot ]Freiherr{ at }gmx[ dot }at, licence GPLv3
' Details: http://developer.gnome.org/platform-overview/stable/gda

#INCLUDE ONCE "crt/stdio.bi"
#INCLUDE ONCE "Gir/Gda-5.0.bi"
#INCLUDE ONCE "Gir/_GLibMacros-2.0.bi"
#INCLUDE ONCE "Gir/_GObjectMacros-2.0.bi"

''' unbelievable: they forgot to declare this in the .gir file
'EXTERN "C" LIB "gda-5.0"
'DECLARE SUB gda_init ()
'END EXTERN

'/*
'* run a non SELECT command and stops if an error occurs
'*/
SUB run_sql_non_select(BYVAL Cnc AS GdaConnection PTR, BYVAL Sql AS CONST gchar PTR)
  DIM AS GError PTR errr
  DIM AS CONST gchar PTR remain

  VAR parser = g_object_get_data(G_OBJECT(Cnc), "parser")
  VAR stmt = gda_sql_parser_parse_string(parser, sql, @remain, @errr)
  IF remain THEN g_print(!"REMAINS: %s\n", remain)

  VAR nrows = gda_connection_statement_execute_non_select(Cnc, stmt, NULL, NULL, @errr)
  IF nrows = -1 THEN _
    g_error(!"NON SELECT error: %s\n", IIF(errr ANDALSO errr->message, errr->message, @"no detail"))
  g_object_unref(stmt)
END SUB


'/*
'* Create a "products" table
'*/
SUB create_table(BYVAL Cnc AS GdaConnection PTR)

  run_sql_non_select(Cnc, "DROP table IF EXISTS products")
  run_sql_non_select(Cnc, "CREATE table products (ref string not null primary key, " _
                          "name string not null, price real)")
END SUB


'/*
'* Insert some data
'*
'* Even though it is possible to use SQL text which includes the values to insert into the
'* table, it's better to use variables (place holders), or as is done here, convenience functions
'* to avoid SQL injection problems.
'*/
SUB insert_data(BYVAL Cnc AS GdaConnection PTR)
  TYPE RowData
    AS gchar PTR ref
    AS gchar PTR NAME
    AS gboolean price_is_null
    AS gfloat price
  END TYPE

  DIM AS RowData dat(...) = { _
     TYPE(@!"p1", @!"chair", FALSE, 2.0) _
   , TYPE(@!"p2", @!"table", FALSE, 5.0) _
   , TYPE(@!"p3", @!"glass", FALSE, 1.1) _
   , TYPE(@!"p1000", @!"???", TRUE, 0.) _
   , TYPE(@!"p1001", @!"???", TRUE, 0.) _
   }

  DIM AS GValue PTR v3
  DIM AS GError PTR errr

  FOR i AS INTEGER = 0 TO UBOUND(dat)
    VAR v1 = gda_value_new_from_string(dat(i).ref, G_TYPE_STRING)
    VAR v2 = gda_value_new_from_string(dat(i).name, G_TYPE_STRING)
    IF dat(i).price_is_null THEN
      v3 = NULL
    ELSE
      v3 = gda_value_new(G_TYPE_FLOAT)
      g_value_set_float(v3, dat(i).price)
    END IF

    VAR res = gda_connection_insert_row_into_table( _
                Cnc, "products", @errr, "ref", v1, "name", v2, "price", v3, NULL)

    IF 0 = res THEN _
      g_error("Could not INSERT data into the 'products' table: %s\n", _
       IIF(errr ANDALSO errr->message, errr->message, @"no detail"))

    gda_value_free(v1)
    gda_value_free(v2)
    IF v3 THEN gda_value_free(v3)
  NEXT
END SUB


'/*
'* Update some data
'*/
SUB update_data(BYVAL Cnc AS GdaConnection PTR)
  DIM AS GError PTR errr

'/* update data where ref is 'p1000' */
  VAR v1 = gda_value_new_from_string("p1000", G_TYPE_STRING)
  VAR v2 = gda_value_new_from_string("flowers", G_TYPE_STRING)
  VAR v3 = gda_value_new(G_TYPE_FLOAT)
  g_value_set_float(v3, 1.99)

  VAR res = gda_connection_update_row_in_table( _
          cnc, "products", "ref", v1, @errr, "name", v2, "price", v3, NULL)

  IF 0 = res THEN _
    g_error(!"Could not UPDATE data in the 'products' table: %s\n", _
       IIF(errr ANDALSO errr->message, errr->message, @"no detail"))

  gda_value_free(v1)
  gda_value_free(v2)
  gda_value_free(v3)
END SUB

'/*
'* Delete some data
'*/
SUB delete_data(BYVAL Cnc AS GdaConnection PTR)
  DIM AS GError PTR errr

'/* delete data where name is 'table' */
  VAR v = gda_value_new_from_string("table", G_TYPE_STRING)
  VAR res = gda_connection_delete_row_from_table(Cnc, "products", "name", v, @errr)
  IF 0 = res THEN _
    g_error(!"Could not DELETE data from the 'products' table: %s\n", _
       IIF(errr ANDALSO errr->message, errr->message, @"no detail"))
  gda_value_free(v)

'/* delete data where price is NULL */
  res = gda_connection_delete_row_from_table(Cnc, "products", "price", NULL, @errr)
  IF 0 = res THEN _
    g_error(!"Could not DELETE data from the 'products' table: %s\n", _
       IIF(errr ANDALSO errr->message, errr->message, @"no detail"))
END SUB


'/*
'* display the contents of the 'products' table
'*/
SUB display_products_contents(BYVAL Cnc AS GdaConnection PTR)
  DIM AS GError PTR errr
  VAR sql = @"SELECT ref, name, price FROM products"

  VAR parser = g_object_get_data(G_OBJECT(Cnc), "parser")
  VAR stmt = gda_sql_parser_parse_string(parser, sql, NULL, NULL)
  VAR data_model = gda_connection_statement_execute_select(Cnc, stmt, NULL, @errr)
  g_object_unref(stmt)
  IF 0 = data_model THEN _
     g_error("Could not get the contents of the 'products' table: %s\n", _
             IIF(errr ANDALSO errr->message, errr->message, @"No detail"))

  gda_data_model_dump(data_model, stdout)
  g_object_unref(data_model)
END SUB


' ##### main #####

g_print(!"STARTING\n")

gda_init()

'/* open a connection */
g_print(!"CONNECTING\n")

DIM AS GError PTR errr
VAR connection = gda_connection_open_from_string( _
                   "SQLite", "DB_DIR=.;DB_NAME=example_db", NULL, _
                   GDA_CONNECTION_OPTIONS_NONE, @errr)

IF connection THEN g_print(!"CONNECTED\n") _
              ELSE g_error(!"\n%s", errr->message)

VAR parser = gda_connection_create_parser(connection)

'/* @cnc doe snot provide its own parser => use default one */
IF 0 = parser THEN parser = gda_sql_parser_new()

' /* attach the parser object to the connection */
g_object_set_data_full(G_OBJECT(connection), "parser", parser, @g_object_unref)

'/* use the connection */
create_table(connection)

insert_data(connection)
display_products_contents(connection)

update_data(connection)
display_products_contents(connection)

delete_data(connection)
display_products_contents(connection)

'/* close the connection */
g_object_unref(G_OBJECT(connection))

g_print(!"ENDING\n")
