Skip to content

TSQLite3Select

Jump to bottom
Ivan Semenkov edited this page Feb 3, 2021 · 1 revision

Table of contents

About

TSQLite3Select class encapsulates the SQLite3 database table select part of query.

uses
  sqlite3.select;
  
type
  TSQLite3Select = class

Create

A new select query can be created by call its constructor.

constructor Create (AErrorsStack : PSQL3LiteErrorsStack; ADBHandle :
  ppsqlite3; ATableName : String);
Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.select;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  select : TSQLite3Select;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := TSQLite3Select.Create(@errors, @handle, 'table_name');
  
  FreeAndNil(select);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

Or use TSQLite3Builder object.

function Select : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select;
  
  FreeAndNil(builder);
end;

Fields

All

Select all data from table. Details on SQLite C/C++ API page.

function All : TSQLite3Select;

SQL query SELECT *.

Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select.All;
  
  FreeAndNil(builder);
end;

Field

Select concrete column. The function can be called multiple times. Details on SQLite C/C++ API page.

function Field (AColumnName : String) : TSQLite3Select;

SQL query SELECT AColumnName.

Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .Field('id')
    .Field('value');
  
  FreeAndNil(builder);
end;

Field alias

Select concrete column and set its alias. The function can be called multiple times. Details on SQLite C/C++ API page.

function Field (AColumnName, AColumnAlias : String) : TSQLite3Select;

SQL query SELECT AColumnName AS AColumnAlias.

Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .Field('id')
    .Field('value', 'val');
  
  FreeAndNil(builder);
end;

Distinct

Duplicate rows are removed from the set of result rows. Details on SQLite C/C++ API page.

function Distinct : TSQLite3Select;

SQL query SELECT ... DISTINCT.

Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Distinct;
  
  FreeAndNil(builder);
end;

Where

Where comparison operators

There are several comparison operators which can used in where clause.

TWhereComparisonOperator = (
COMPARISON_EQUAL

Equal comparison operator.

  COMPARISON_EQUAL,               { =   }
COMPARISON_NOT_EQUAL

Not equal comparison operator.

  COMPARISON_NOT_EQUAL,           { <>  }
COMPARISON_LESS

Less comparison operator.

  COMPARISON_LESS,                { <   }
COMPARISON_GREATER

Greater comparison operator.

  COMPARISON_GREATER,             { >   }
COMPARISON_LESS_OR_EQUAL

Less or equal comparison operator.

  COMPARISON_LESS_OR_EQUAL,       { <=  }
COMPARISON_GREATER_OR_EQUAL

Greater or equal comparison operator.

  COMPARISON_GREATER_OR_EQUAL,    { >=  }
COMPARISON_NOT

Not comparison operator.

  COMPARISON_NOT                  { IS NOT }
);

Where

If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing. Rows are excluded from the result if the WHERE clause evaluates to either false or NULL. Details on SQLite C/C++ API page.

function Where (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : String) : TSQLite3Select;
function Where (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Integer) : TSQLite3Select;
function Where (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Double) : TSQLite3Select;

For equal comparison operator there are present short form.

function Where (AColumnName : String; AValue : String) : TSQLite3Select;
function Where (AColumnName : String; AValue : Integer) : TSQLite3Select;
function Where (AColumnName : String; AValue : Double) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('id', COMPARISON_GREATER, 2);
  
  FreeAndNil(builder);
end;

WhereNull

It is a short form of where clause with value equal NULL .

function WhereNull (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .WhereNull('value');
  
  FreeAndNil(builder);
end;

WhereNotNull

It is a short form of where clause with value not equal NULL .

function WhereNotNull (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .WhereNotNull('value');
  
  FreeAndNil(builder);
end;

AndWhere

Adds AND WHERE clause to query.

 function AndWhere (AColumnName : String; AComparison : 
   TWhereComparisonOperator; AValue : String) : TSQLite3Select;
 function AndWhere (AColumnName : String; AComparison : 
   TWhereComparisonOperator; AValue : Integer) : TSQLite3Select;
 function AndWhere (AColumnName : String; AComparison : 
   TWhereComparisonOperator; AValue : Double) : TSQLite3Select;

For equal comparison operator there are present short form.

function AndWhere (AColumnName : String; AValue : String) : TSQLite3Select;
function AndWhere (AColumnName : String; AValue : Integer) : TSQLite3Select;
function AndWhere (AColumnName : String; AValue : Double) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('value', 2)
    .AndWhere('value', COMPARISON_GREATER, 45);
  
  FreeAndNil(builder);
end;

AndWhereNull

It is a short form of where clause with value equal NULL .

function AndWhereNull (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('value', 2)
    .AndWhereNull('value');
  
  FreeAndNil(builder);
end;

AndWhereNotNull

It is a short form of where clause with value not equal NULL .

function AndWhereNotNull (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('id', COMPARISON_GREATER, 45)
    .AndWhereNotNull('value');
  
  FreeAndNil(builder);
end;

OrWhere

Adds OR WHERE clause to query.

function OrWhere (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : String) : TSQLite3Select;
function OrWhere (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Integer) : TSQLite3Select;
function OrWhere (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Double) : TSQLite3Select;

For equal comparison operator there are present short form.

function OrWhere (AColumnName : String; AValue : String) : TSQLite3Select;
function OrWhere (AColumnName : String; AValue : Integer) : TSQLite3Select;
function OrWhere (AColumnName : String; AValue : Double) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('value', 2)
    .OrWhere('value', 45);
  
  FreeAndNil(builder);
end;

OrWhereNull

It is a short form of where clause with value equal NULL .

function OrWhereNull (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('value', 2)
    .OrWhereNull('value');
  
  FreeAndNil(builder);
end;

OrWhereNotNull

It is a short form of where clause with value not equal NULL .

function OrWhereNotNull (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Where('id', COMPARISON_GREATER, 45)
    .OrWhereNotNull('value');
  
  FreeAndNil(builder);
end;

Join

Join types

There are several join types which can used.

TJoinType = (
JOIN_INNER
SELECT a1, a2, b1, b2
FROM A
INNER JOIN B on B.f = A.f;

For each row in the A table, the INNER JOIN clause compares the value of the f column with the value of the f column in the B table. If the value of the f column in the A table equals the value of the f column in the B table, it combines data from a1, a2, b1, b2, columns and includes this row in the result set.

  JOIN_INNER,
JOIN_OUTER_LEFT
SELECT
	a,
	b
FROM
	A
LEFT JOIN B ON A.f = B.f
WHERE search_condition;

The statement returns a result set that includes:

  1. Rows in table A (left table) that have corresponding rows in table B.
  2. Rows in the table A and the rows in the table B filled with NULL values in case the row from table A does not have any corresponding rows in table B.

In other words, all rows in table A are included in the result set whether there are matching rows in table B or not.

  JOIN_OUTER_LEFT,
JOIN_CROSS
SELECT *
FROM A
INNER JOIN B;

Suppose, the A table has N rows and B table has M rows, the CROSS JOIN of these two tables will produce a result set that contains NxM rows.

  JOIN_CROSS
);

Join

To query data from multiple tables, uses JOIN clause. This clause combines columns from correlated tables.

function Join (ATableName : String; AJoinType : TJoinType; AColumnName : 
  String; ACurrentTableColumn : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Join('table2', JOIN_OUTER_LEFT, 'id');
  
  FreeAndNil(builder);
end;

InnerJoin

For most used join types there are present short forms.

function InnerJoin (ATableName : String; AColumnName : String; 
  ACurrentTableColumn : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .InnerJoin('table2', 'value', 'id');
  
  FreeAndNil(builder);
end;

LeftJoin

function LeftJoin (ATableName : String; AColumnName : String; 
  ACurrentTableColumn : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .LeftJoin('table2', 'value', 'id');
  
  FreeAndNil(builder);
end;

OrderBy

OrderBy types

There are several order by types which can used.

TOrderByType = (
ORDER_ASC

The ASC keyword means ascending.

  ORDER_ASC,
ORDER_DESC

And the DESC keyword means descending.

  ORDER_DESC
);

OrderBy

Order by clause.

function OrderBy (AColumnName : String; AOrderBy : TOrderByType) :
  TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .OrderBy(ORDER_ASC);
  
  FreeAndNil(builder);
end;

GroupBy

Group by clause.

function GroupBy (AColumnName : String) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .OrderBy(ORDER_ASC)
    .GroupBy('id')
    .GroupBy('value');
  
  FreeAndNil(builder);
end;

Limit

Use the LIMIT clause to constrain the number of rows returned by the query.

function Limit (ACount : Cardinal) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Limit(2);
  
  FreeAndNil(builder);
end;

Offset

function Offset (ACount : Cardinal) : TSQLite3Select;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.select;
  
var
  builder : TSQLite3Builder;
  select : TSQLite3Select;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  select := builder.Table('table_name').Select
    .All
    .Limit(2)
    .Offset(4);
  
  FreeAndNil(builder);
end;

Get

Run query and get result.

function Get : TSQLite3Result;

Result presents by TSQLite3Result object.

Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.result;
  
var
  builder : TSQLite3Builder;
  res : TSQLite3Result;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  res := builder.Table('table_name').Select
    .All
    .Get;
  
  FreeAndNil(builder);
end;
Clone this wiki locally