{ DISQLite3 demos common unit.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de/delphi/

  Copyright (c) 2005-2009 Ralf Junker, The Delphi Inspiration <delphi@yunqa.de>

------------------------------------------------------------------------------ }

unit DISQLite3_Demos_Common;

{$I DI.inc}
{$I DISQLite3.inc}

interface

uses
  DISystemCompat, DISQLite3Api;

const
  CRLF = #13#10;

procedure Close_Demo_Database;

procedure Create_Demo_Tables;

procedure Open_Demo_Database;

function Demo_Database_Name: String;

{ Writes a prepared statement to StdOut. }
procedure WritePreparedStatement(
  const DB: sqlite3;
  const Stmt: sqlite3_stmt);

{ Creates a string composed of random letters from A to Z. }
function RandomString8(const AMaxLength: Integer): utf8String;

var
  DB: sqlite3;

implementation

uses
  SysUtils;

//------------------------------------------------------------------------------

procedure Close_Demo_Database;
begin
  sqlite3_check(sqlite3_close(DB), DB);
end;

//------------------------------------------------------------------------------

procedure Create_Demo_Tables;
begin
  sqlite3_exec_fast(DB,
    'CREATE TABLE IF NOT EXISTS RandomTable (' +
    'RandomText TEXT,' +
    'RandomInt INTEGER);');
end;

//------------------------------------------------------------------------------

procedure Open_Demo_Database;
var
  s8 : utf8string;
begin
  s8 := sqlite3_encode_utf8(Demo_Database_Name);
  sqlite3_check(sqlite3_open(PUtf8Char(s8), @DB), DB);
end;

//------------------------------------------------------------------------------

function Demo_Database_Name: String;
begin
  Result := ExpandFileName(ExtractFilePath(ParamStr(0)) + '\..\Demo.db3');
end;

//------------------------------------------------------------------------------

procedure WritePreparedStatement(
  const DB: sqlite3;
  const Stmt: sqlite3_stmt);
var
  ColumnCount: Integer;
  e: Integer;
  i: Integer;
begin
  { Did our SQL query compile into a prepared SQL statement? }
  if Assigned(Stmt) then
    begin
      { sqlite3_step executes the prepared statement. }
      e := sqlite3_check(sqlite3_step(Stmt), DB);
      { Check if the query returnd at least one row of data. }
      if e = SQLITE_ROW then
        begin
          { If we have data rows, store the number of columns returned. }
          ColumnCount := sqlite3_column_count(Stmt);

          { First write a single line with the column names. }
          for i := 0 to ColumnCount - 1 do
            begin
              if i > 0 then Write('|');
              Write(sqlite3_column_name(Stmt, i));
            end;
          WriteLn; WriteLn;

          { Next iterate all data rows and columns, retrieve the data and
            write it to the console. }
          repeat

            for i := 0 to ColumnCount - 1 do
              begin
                if i > 0 then Write('|');
                { We optimize the data output according to the column type. }
                case sqlite3_column_type(Stmt, i) of
                  SQLITE_INTEGER:
                    Write(sqlite3_column_int64(Stmt, i));
                  SQLITE_FLOAT:
                    Write(sqlite3_column_double(Stmt, i));
                  SQLITE_TEXT:
                    Write(sqlite3_column_str(Stmt, i));
                  SQLITE_BLOB:
                    Write('BLOB'); // Don't write BLOB data.
                  SQLITE_NULL:
                    Write('NULL'); // Write NULL indicator.
                end;
              end;

            WriteLn;

            { Retrieve next row of data and continue until no more rows found. }
            e := sqlite3_check(sqlite3_step(Stmt), DB);
          until e <> SQLITE_ROW;
        end;
    end;
end;

//------------------------------------------------------------------------------

function RandomString8(const AMaxLength: Integer): Utf8String;
var
  l: Integer;
  p: PUtf8Char;
begin
  Assert(AMaxLength > 0);
  repeat
    l := Random(AMaxLength);
  until l > 0;

  SetString(Result, nil, l);

  p := Pointer(Result);
  repeat
    p^ := AnsiChar(Ord('A') + Random(24));
    Inc(p); Dec(l);
  until l = 0;
end;

end.

