Use unified ID (primary key) for multiple tables?

edwinsn · 2014-07-04 17:02:20

Hi Arnaud,

Sqlite generate new rowids for each table separately (uses the internal table called sqlite_sequences), but if I want to use a unified rowid sequence, I mean, no rowids in tableA will appear in TableB or tableC, all rowids are unique in the scope of the entire DB (with a few exceptions). What is the simple and reliable way of doing that that you would suggest?

I need to do that, because I want to index data from multiple tables into a single FTS table, and use the original ID from the source tables as the rowid in the FTS table.

Thanks!

ab · 2014-07-04 19:28:54

The easiest is to use a TRecordReference published field for the TSQLRecordFTS4 record.
Take a look at the documentation about this TRecordReference type and associated RecordRef record.
They can store in one integer field an ID + table index in the TSQLModel.

So you can later retrieve a reference to a table and ID within the FTS RowID, supplied when you add the FTS table record - setting ForceID: boolean parameter to TRUE and specifying the new ID encoded as RecordRef.Value.

Other options (like an 'unified rowid sequence' you propose) would be much difficult: you need to retrieve the table from the composite ID, which is not obvious but by maintaining a separate table.
RecordRef kind of solution sounds much reliable.

edwinsn · 2014-07-05 03:29:16

Thanks for the help. You are right, in additional to the source record ID, I also have to store the table ID in order to be able to retrieve the source record at a later time.

I just checked the document, looks like it has some restriction on using TRecordReference, I'd better off store 'source record ID' and 'source table ID' (define as enum) as strings separately in the FTS4 record (TSQLRecordFTS4).

Thank you, things are clarified now.

ab · 2014-07-05 05:37:47

Store them as integer, not as string.

edwinsn · 2014-07-05 07:32:22

ab wrote:

Store them as integer, not as string.

I haven't tried, but since sqlite document says a FTS table can only contain string fields except for the rowid, I thought I can have string fields in the TSQLRecordFTS4 decedents. But it's great to know I can also have Integers! How about TDateTime?

For the 'source table', I'll use table names (strings), in order to take advantage of the ":" FTS search operation, for example:

table:order

Will search only orders, but not other kind of data.

Last edited by edwinsn (2014-07-05 07:51:31)

ab · 2014-07-05 09:06:13

You are right, you can not store any integer but the RowID. Columns are all full-text indexed text.

I suspect it may not be the best idea to add string field to the FTS table to identify it.
I would stick to the RecordRef trick, and use RowID to store both TableIndex + ID of the indexed field.

edwinsn · 2014-07-05 10:32:51

@ab, could you give a very simple example of your idea? I couldn't understand how to define a TSQLRecordFTS4 child class utilizing the RecordRef you are talking about, especially how to set the rowid value for a new FTS record...

My current record definition:

  TSQLFTSDoc = class(TSQLRecordFTS4Porter)
  private
    FKind: String;
    FSrcRecId: Integer;
    FContent: String;
    FDate: String;
  published
    ///  the kind of data, eg 'order', 'contact', etc. usually matches the
    ///  corresponding table name.
    property Kind: String read FKind write FKind;

    ///  the ID of the original record in the original table in the origiinal DB.
    ///  When we have 'Kind' and 'SrcRecId', we'll be able to locate the original record.
    property SrcRecId: Integer read FSrcRecId write FSrcRecId;

    property Content: String read FContent write FContent;

    ///  Human readable date, such as "2014 may 1", please note the spaces in the date string,
    ///  because we want to utilize the sqlite FTS search query language, so that the user can enter
    ///  'date:2014' and search all data within the year of 2014.
    property Date: String read FDate write FDate;
  end;

Note: With is approach, with the explicit Kind (tableName) and SrcRecId fields, I can write joined SQL easily...

Last edited by edwinsn (2014-07-05 10:38:12)

ab · 2014-07-05 13:33:03

Yes, but such JOINed query will be dead slow, as stated by the FTS documentation: it will perform a full scan to search equality of Kind and SrcRecID.
Even if you define "property SrcRecId: Integer", the column type will be ignored and it will be a string full text column.
AFAIR a FTS table is not able to retrieve its content. So you Date field won't be available.

You should not store any data within a FTS table, but full text to search.
You have to use a separate normal (non FTS) table to store the content.
Then you provide a ID/RowID when you add/insert each FTS/record.
FTS.RowID is not auto-generated by a sequence, but specified at insertion.

My proposal was to use RecordRef to compute a Table/ID combination, then set FTSRecord.ID to RecordRef.Value at insertion.
Only the property Content: String field needs to be defined for the TSQLFTSDoc.
Then you will be able to retrieve the Table/ID after a MATCH search on TSQLFTSDoc, by using RecordRef.Value := RowID then retrieving the corresponding Table/ID.

edwinsn · 2014-07-05 15:05:21

@ab, I understand you now! Sounds like a good idea, but I still have one concern, according to the comment about RecordRef, it seems that the max record ID will be 4294967295(maxUInt) shr 6 = 67,108,863 on win32 platform, right?

ab · 2014-07-05 15:57:46

Indeed.

But it is a juge number imho...

edwinsn · 2014-07-06 03:07:56

Yes, it should be sufficient in most cases...

edwinsn · 2014-11-14 12:35:56

HI Ab, with the recent changes, the ID is of type int64 now, so looks like I can use unified IDs (primary key) for multiple tables now!!??

ab · 2014-11-14 13:47:02

@edwinsn
Indeed - this was one of the potential use case of supporting TSQLRecord.ID: TID as Int64.

But one unified ID for all tables is not yet available at ORM level yet.
It is available from client code only.
It is still recommended to use TRecordReference / TRecordRefernenceToBeDeleted if you want to refer to several tables.
See http://synopse.info/files/html/Synopse% … l#TITLE_71

mORMot Open Source

#1 2014-07-04 17:02:20

Use unified ID (primary key) for multiple tables?

#2 2014-07-04 19:28:54

Re: Use unified ID (primary key) for multiple tables?

#3 2014-07-05 03:29:16

Re: Use unified ID (primary key) for multiple tables?

#4 2014-07-05 05:37:47

Re: Use unified ID (primary key) for multiple tables?

#5 2014-07-05 07:32:22

Re: Use unified ID (primary key) for multiple tables?

#6 2014-07-05 09:06:13

Re: Use unified ID (primary key) for multiple tables?

#7 2014-07-05 10:32:51

Re: Use unified ID (primary key) for multiple tables?

#8 2014-07-05 13:33:03

Re: Use unified ID (primary key) for multiple tables?

#9 2014-07-05 15:05:21

Re: Use unified ID (primary key) for multiple tables?

#10 2014-07-05 15:57:46

Re: Use unified ID (primary key) for multiple tables?

#11 2014-07-06 03:07:56

Re: Use unified ID (primary key) for multiple tables?

#12 2014-11-14 12:35:56

Re: Use unified ID (primary key) for multiple tables?

#13 2014-11-14 13:47:02

Re: Use unified ID (primary key) for multiple tables?

Board footer