Getting Row Number From a SQLite query

[bobonwhidbey]

The following works but seems to be overly complicated.

Code: Pascal  [Select][+][-]

1. function GetSeqFromID(aID, QWhere, QOrder: string): integer;
2. begin
3.   Conn.ExecuteDirect('DROP VIEW IF EXISTS SUB');
4.   Conn.ExecuteDirect('CREATE VIEW SUB AS SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QOrder +
5.     ') AS Row_Num FROM Game INNER JOIN Player ON ID = IDNo ' + 'WHERE ' + QWhere);
6.   Trans.Commit;
7.  
8.   Q1.Close;
9.   Q1.SQL.Text := 'SELECT ID,Row_Num FROM SUB WHERE ID="' + aID + '";';
10.   Q1.Open;
11.   Result := Q1.FieldByName('Row_Num').AsInteger - 1;
12. end;
13.  

Where Conn, Trans, and Q1 are previously created as  TSQLite3Connection, TSQLTransaction, and TSQLQuery objects.

I've also notice that it seems to work faster when the VIEW is committed. Is there a better approach to find out where, within a Query, a certain key (ID) is?

[Awkward]

what about computed fields?
https://stackoverflow.com/questions/1124695/can-i-create-computed-columns-in-sqlite

[Zvoni]

Use CTE's instead of VIEW

BTBH, i don't get what you are trying to achieve with that Row-Number

[CharlyTango]

I don't understand the purpose of the query or what you want to achieve. An explanation would be good here so that we can help you in a targeted manner.

It is understandable that a query on an existing view feels faster, because when the view is created, it is already parsed and an access plan is created.

This access plan is then used for the subsequent query and does not have to be created again. This saves time.

However, I am convinced that deleting the view, recreating it and the query as a whole is slower than a cleanly designed query with the necessary indices.


In an SQL server (such as SQLite), a VIEW is originally there to have different predefined views of the data, which are then used by users and programmes that cannot see the original tables due to certain rights. Speed optimisation can also be a reason for views.
I consider it pointless and slow to create a view in the programme and then only use it for a single query.

[Zvoni]

I don't understand the purpose of the query or what you want to achieve. An explanation would be good here so that we can help you in a targeted manner.

It is understandable that a query on an existing view feels faster, because when the view is created, it is already parsed and an access plan is created.

This access plan is then used for the subsequent query and does not have to be created again. This saves time.

However, I am convinced that deleting the view, recreating it and the query as a whole is slower than a cleanly designed query with the necessary indices.


In an SQL server (such as SQLite), a VIEW is originally there to have different predefined views of the data, which are then used by users and programmes that cannot see the original tables due to certain rights. Speed optimisation can also be a reason for views.
I consider it pointless and slow to create a view in the programme and then only use it for a single query.

Since when is SQLite an SQL-Server?

[bobonwhidbey]

My experience with SQLite is fairly limited since I never heard of Common Table Expressions until today and I've only known about Views for a month or so. While I think Zvoni's response comes closest to answering my question, perhaps Charly's response will suit my needs better as I am deleting and creating the View far more than I need to. Still I would like to learn more about CTEs.

The user has great flexibility in defining the QWhere and QOrder variables in order to create a subset of the DB. I can not always step through this dataset (query) with Next, Prior, First, and Last. I want to know the row number (sequence in the dataset) to display the data in a TDrawGrid, especially after jumping to a location in the dataset.

This is my failed attempt at using the CTE approach. Perhaps you can suggest where I've gone wrong.

Code: Pascal  [Select][+][-]

1.  Q1.SQL.Text := 'WITH CTE as (SELECT *, ROW_NUMBER() as Row_Num From Game INNER JOIN ' +
2.     'Player ON ID = IDNo where ' + QWhere + ' ORDER BY ' + QOrder +
3.     ') Select ID,Row_Num FROM CTE WHERE ID="' + aID + '";';

The error message says "..misuse of window function Row_Number()"

[CharlyTango]

Since when is SQLite an SQL-Server?

We can talk about terminology (although I'm sure you know how it was meant)

But without going into the details of the individual "server" types....

I am referring to a table-style data management system that is independent of the programme and is operated using SQL statements. Regardless of whether it is a single user database or a database cluster with many nodes.

From an application point of view, they essentially work in a similar or identical way (apart from individual peculiarities)

All the necessary components can be found in the "SQLDB" component tab in Lazarus (or as third-party components in ZEOS)

[CharlyTango]

I suspect you're thinking in the wrong direction. the key to a valid solution lies in a clean design of the primary keys of the tables. These are used to obtain any related information from the database

Furthermore, I suspect that you might be missing basic concepts in database modelling. If that's the case, have a quick look here,

I still don't know what you really want to achieve -- how should the result be presented to the user? The average user should NEVER have direct access to SQL statements.

By the way, I still don't understand how you come up with the idea of displaying data in a TDrawgrid.
Lazaraus (like Delphi) has a number of fairly sophisticated data-sensitive components in the "Data Controls" and "Data Access" tabs that provide all the usual tasks for displaying data from an SQL query with a few mouse clicks.

And this can also be done as a master/detail concept or manually triggered in an OnChangeEvent of the TSQLQuery.

https://wiki.freepascal.org/Portal:Databases

Why don't you create a small compilable test project including a test database so that your goals and intentions can be better recognised?

[bobonwhidbey]

These are small SQLite DBs, contained on the user's hard drive, normally < 500 records, with the largest I've seen about 110,00 records. The user, by clicking on various check boxes, can select how they want to filter the data in the DB. The app then creates the necessary SQL select statements (QWhere and QOrder). The user needs to know nothing about SQL.

Here's an example of a question I'd like to be able to answer: I'd like to jump to the median result. Assume my query includes all bridge hands where the opening lead was a trump, sorted from the worst to best score for the opening leader, and I want to jump to the median score. If there are 300 games in my query, I want to jump to the 150th game.

[CharlyTango]

Well, this is not yet a test project, but it does shed some light on the matter.

The initial requirements that the user creates the query by clicking and selecting from a wide variety of controls is clear and absolutely normal.

Then I would actually use next to scroll to the desired record. This doesn't take long, because any data displays are switched off. Then you display the result as you want.

Here follows some kind of mockup code

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, SQLDB, Forms, Controls, Graphics, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    SQLQuery1: TSQLQuery;
  private
    function ProduceSQLStatement:string;
    procedure RunSQLQuery;
    procedure DisplayDetailData;
  public

  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

function TForm1.ProduceSQLStatement: string;
begin
  //Here you create the SQL Statement from user input
  result:= 'SELECT ID,... FROM... WHERE... ORDER by....'

end;

//call this procedure in every Controls OnChange event
//with which the user builds the SQL statement
procedure TForm1.RunSQLQuery;
var
  icnt,i:integer;
begin
   SQLQuery1.DisableControls;
   SQLQuery1.Close;
   SQLQuery1.SQL.Text:=ProduceSQLStatement;
   SQLQuery1.Open;
   SQLQuery1.Last;

   icnt:=round(SQLQuery1.RecordCount/2);
   SQLQuery1.First;

   For i:=1 to icnt do
     SQLQuery1.Next;

   DisplayDetailData;

   SQLQuery1.EnableControls;
end;

procedure TForm1.DisplayDetailData;
begin
  //Display Detail Data however you want;
end;

end.

If the result table is very wide (i.e. a lot of data is retrieved) then it could also be a strategy to retrieve only a list of primary keys, scroll to the desired data record and make another query with this primary key, which only queries and displays the data record searched for. The bottleneck is usually not the time that a query takes, but the time in which the result is transported.

[teos]

This is my failed attempt at using the CTE approach. Perhaps you can suggest where I've gone wrong.

Code: Pascal  [Select][+][-]

1.  Q1.SQL.Text := 'WITH CTE as (SELECT *, ROW_NUMBER() as Row_Num From Game INNER JOIN ' +
2.     'Player ON ID = IDNo where ' + QWhere + ' ORDER BY ' + QOrder +
3.     ') Select ID,Row_Num FROM CTE WHERE ID="' + aID + '";';

The error message says "..misuse of window function Row_Number()"

I'm not sure if get the issue. But from what I know, SQLLite has internal row numbers. Those are not used in a query by a developer.

As developer you would have one ID field, with unique integer ID's (autoincrement) and you use that in SQL.
I'm allmost sure that "With" is not a valid SQL statement. You first "Select" will return all fields where you would want Row_Num only.

I would allmost say: 'Select ID from CTE where ID = ' + inttostr(aId); will at least run without errors.

Open your database table in a SQLite database tool and see for yourself: data is stored with a unique identifier, in a primary key field.

[Zvoni]

I'm not sure if get the issue. But from what I know, SQLLite has internal row numbers. Those are not used in a query by a developer.

Wrong. They are used everytime a Table has an Integer-column with Attribute Primary Key, since that column actually becomes an Alias for "rowid"
Note: This only applies to tables NOT created with "without rowid"-modifier (or whatever it's called)
Tables created with "without rowid"-modifier of course don't have that, but such tables are 1 in a billion

I can create tables without an ID-PrimaryKey (Integer), and still use "rowid" in my queries.
There is only one caveat: You can't use "rowid" as a "link" to a Foreign Key

As developer you would have one ID field, with unique integer ID's (autoincrement) and you use that in SQL.

Don't use Autoincrement in SQLite. Period!

I'm allmost sure that "With" is not a valid SQL statement. You first "Select" will return all fields where you would want Row_Num only.

I would allmost say: 'Select ID from CTE where ID = ' + inttostr(aId); will at least run without errors.

Open your database table in a SQLite database tool and see for yourself: data is stored with a unique identifier, in a primary key field.

Utter Nonsense.
CTE's ("With") are supported pretty much across all modern Database-Engines.
I use SQL's with "WITH"-Statements (CTE's) every day, and yes, even in SQLite (which is only my test Database)

at TS
you were close...
untested, though

Code: Pascal  [Select][+][-]

1.      Q1.SQL.Text := 'WITH CTE as (SELECT *, ROW_NUMBER() OVER(ORDER BY '+QOrder+') as Row_Num From Game INNER JOIN ' +
2.         'Player ON ID = IDNo where ' + QWhere +') Select ID,Row_Num FROM CTE WHERE ID="' + aID + '";';

EDIT:
A word of advice:
1) Don't use "SELECT * FROM, especially not on joined tables
2) use aliases on your tables, or at least fully qualify your columns to which table they belong
the line with "... ON ID=IDno.." i would always ask myself "is ID in Game or in Player?"

Code: Pascal  [Select][+][-]

1.      Q1.SQL.Text := 'WITH CTE as
2.                         (SELECT G.ID, ROW_NUMBER() OVER(ORDER BY '+QOrder+') as Row_Num From Game AS G '+
3.                         'INNER JOIN Player AS P ON G.ID = P.IDNo where ' + QWhere +
4.                         ') Select ID,Row_Num FROM CTE WHERE ID="' + aID + '";';

And is ID really a Text/String?? Note the doublequotes at the end

EDIT2: Of Course, in case of aliasing QOrder and QWhere MUST contain the correct aliases!
e.g.
QOrder:='P.PlayerName'
QWhere:='P.IDno=42'
and similar

[teos]

Wrong. They are used everytime a Table has an Integer-column with Attribute Primary Key, since that column actually becomes an Alias for "rowid"
Note: This only applies to tables NOT created with "without rowid"-modifier (or whatever it's called)
Tables created with "without rowid"-modifier of course don't have that, but such tables are 1 in a billion

I can create tables without an ID-PrimaryKey (Integer), and still use "rowid" in my queries.
There is only one caveat: You can't use "rowid" as a "link" to a Foreign Key

Thanks for explaining. What you try to explain is correct but I don't understand what you try to tell. The SQLlite manual does a way better job:

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value. 

Don't use Autoincrement in SQLite. Period!

Thanks for pointing that out so friendly and without expaining why.

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

Utter Nonsense.

If you say that, then explain why. 

This would have been much more helpfull:

The SQLite WITH clause is used to specify temporary tables that exist for the duration of a query. These temporary tables can be used to store intermediate results and improve performance.

I am not an SQL(ite) expert in any way and I don't (exactly) know it all. But I know where the SQLlite manual is. ;-)

Zvoni: be aware that your words as "Utter Nonsense" can be offensive.

[Zvoni]

If someone asks a question/asks for help here, i EXPECT them to actually have read the offical documentation/manuals pertaining to their Problem
(as TS did for CTE's after i pointed out to him it might be an alternative).

Funny, how many people (including you), only read the manuals AFTER being caught out telling nonsense.

At least you admit not being knowledable about SQL/SQLite, but the "nonsense" was for this

I'm allmost sure that "With" is not a valid SQL statement.

Instead of writing something like that, a simple google with "sqlite with-statement" would have given you the answer.

It's what i do before a hang my neck out on the chop-block about topics i'm not familiar with

[teos]

Funny, how many people (including you), only read the manuals AFTER being caught out telling nonsense.

Zvoni, I don't like being offended when I reply to a topic. So I start testing your answers. And I learned that own ID fields are aliasses. I knew that because I use them. As foreign keys in relational databases.

CTE and VIEWS are suggested in a situation where a simple subselect would suffice. Lecture me as you like but your CTE suggestions combined with the internal ID field use still are overkill.

And I leave the discussion here.