Delphi DuckDB Library

{
  Example: Creating a Database, Adding Data, and Querying Results with Different
           Data Types.

  This example demonstrates:

  - Inserting specific values for UUID, DATE, TIME, and TIMESTAMP into a
    DuckDB table.
  - Extracting and converting components from date, time, and timestamp.
  - Displaying the results in a readable format.
}

program DuckDBExample;

{$IFDEF MSWINDOWS}
  {$APPTYPE CONSOLE}
{$ENDIF}

uses
  SysUtils,
  ddb;

var
  db           : DuckDB;
  con          : Connection;
  res          : MaterializedQueryResult;
  iter         : QueryResultIterator;
  row          : QueryResultRow;
  uuidval      : string;
  dateval      : date_t;
  timeval      : dtime_t;
  timestampval : timestamp_t;
  year, month, day            : Integer;
  hour, minute, second, millisecond : Integer;

begin


  // Initialize a new DuckDB database and create a connection
  db.Init(nil);
  con.Init(db);

  // Create a table with UUID, date, time, and timestamp columns
  con.Query('CREATE TABLE mixed_data (uuid_col UUID, date_col DATE, ' +
            'time_col TIME, timestamp_col TIMESTAMP)');

  // Insert data into the table
  con.Query(
    'INSERT INTO mixed_data (uuid_col, date_col, time_col, timestamp_col) ' +
    'VALUES (gen_random_uuid(), CAST(''2022-01-10'' AS DATE), ' +
    'CAST(''13:00:17.250'' AS TIME), ' +
    'CAST(''2023-01-10 14:00:17'' AS TIMESTAMP))');

  // Fetch the data and extract UUID, date, time, and timestamp values
  res := con.Query('SELECT uuid_col, date_col, time_col, timestamp_col ' +
                   'FROM mixed_data');
  iter := res.MakeIterator();

  if not iter.IsEnd() then
  begin
    row := iter.current_row;

    // Extract and display UUID value
    uuidval := string(row.GetValueStr(0));
    WriteLn('UUID Value: ', uuidval);

    // Extract and convert date components
    dateval := row.GetValueDate(1);
    ddb.Date.Convert(dateval, @year, @month, @day);
    WriteLn('Date Conversion - Year: ', year, ' Month: ', month, ' Day: ', day);

    // Extract and convert time components
    timeval := row.GetValueTime(2);
    ddb.Time.Convert(timeval, @hour, @minute, @second, @millisecond);
    WriteLn(
      'Time Conversion - Hour: ', hour, ' Minute: ', minute, 
      ' Second: ', second, ' Millisecond: ', millisecond);

    // Extract and convert timestamp to date and time
    timestampval := row.GetValueTimestamp(3);
    ddb.Timestamp.Convert(timestampval, dateval, timeval);
    ddb.Date.Convert(dateval, @year, @month, @day);
    ddb.Time.Convert(timeval, @hour, @minute, @second, @millisecond);

    WriteLn(
      'Timestamp Conversion - Year: ', year, ' Month: ', month, 
      ' Day: ', day);
    WriteLn(
      'Time Conversion - Hour: ', hour, ' Minute: ', minute, 
      ' Second: ', second, ' Millisecond: ', millisecond);

    // Move to the next row (if any)
    iter.Next();
  end;

end.