Quick to check if keyfields exist...

jonsafi · 2011-08-25 20:09:48

Hello,

I have a Table where the key consists of 2 fields. Prior
to adding a new record (am using batch insertion)
I'd like to check that no previous identical key exists.

So am looking for a quick way to test if the given 2 key values
already exist in the table.

I used a multiindex method to setup the index,
but instead of using validate I tried the following,
which does work, although it's a bit slow.

Is there a better way to do this?
Much obliged for any help.

Const QTS= '''';
Var 
 Tbl_Classe:TSQLRecordClass;
 TableEssaie :  TSQLmytbl;
   parenth1,   parenth2,
 where_str :RawUTF8;

  parenth1     := '=:(';   
  parenth2     := '):';
    where_str := G_CRSTBL_INDEXES_Ar[0] + parenth1 +
        QTS       +   TableEssaie.KeyField1 +QTS + parenth2    + ' AND ' +
        G_CRSTBL_INDEXES_Ar[1] +parenth1  +
            QTS +  TableEssaie.KeyField2 + QTS + parenth2;

     
Tbl_Classe :=  model.Table[Tname];
   
If globalClient.MultiFieldValue(Tbl_Classe,
      G_CRSTBL_INDEXES_Ar, Fld_vals,where_str) Then
       Begin
           Showmessage("Key violation!");
      End
     Else
       globalClient.BatchAdd(TableEssaie, true);

ab · 2011-08-25 20:37:28

You can create an unique index on both keys for this table.
See the documentation for doing this.

Therefore, you will let SQL do all the checking for you.

jonsafi · 2011-08-26 06:51:56

Thanks for your tip,
I already tried the following to create a multifield index ( 6 fields).

It seems to have no effect,
as the application happily keep inserting duplicates
into the same table without giving any error..

  TSQLRestClientDB(globalClient).Server.CreateMissingTables(0);


  TSQLRestClientDB(globalClient).Server.CreateSQLMultiIndex(TSQLmyTable,
    
  G_CRSTBL_INDEXES_Ar,True);

G_CRSTBL_INDEXES_Ar contains
6 field names starting from index 0.

I'd like to have control of the code when there
is a key violation to update
the existing record if necessary.

Know that there is a method such as

TSynTableFieldProperties.Validate()
but couldn't find an example on how to use it.

Could you give me one more hint, please?
Thanks!

ab · 2011-08-26 08:27:14

Are you sure there was no existing index with those fields when you called CreateSQLMultiIndex(....,TRUE)?
If there is an existing index over the fields, it won't be created twice.

Normally it should execute:
CREATE UNIQUE INDEX IF NOT EXISTS IndexName ON MyTable(fields,...);

Then SQLite3 should avoid any duplicate.

You can use our SynDBExplorer tool to open the SQlite3 file, then use DROP INDEX to remove an existing index.

For an example of auto validation using TSynValidate, take a look at TSynValidateUniqueField class as implemented in SQLite3Commons. It will validate an unique value for one field, but the current field-based implementation won't let easily create your own class for multiple fields.

jonsafi · 2011-08-26 08:51:33

Thanks for pointing out, it was my mistake with the call
CreateSQLMultiIndex(....,TRUE)?

If was returning False indeed.
So now indexes are created and am getting the
exception 'constraint failed'.

However, am using BatchAdd(..) to add the record,
so I imagine the key violation error/exception
doesn't get triggered until BatchSend(..)

Is there a way to trap this key violation
easily and get hold of the control so as to
perform a record update on key violation?

Am really grateful for any suggestions.

ab · 2011-08-26 09:19:34

In the current Batch implementation, the error is indeed available only when BatchSend is finished...
In batch mode, you should check before inserting that the fields values are not already existing: not so optimized... you loose the benefits of batch mode, here!

There is no auto conversion from INSERT to UPDATE in case of INSERT failure.
But could be a nice feature.

jonsafi · 2011-08-26 10:01:59

OK, thanks a lot for your kind help.
Can figure out a manual workaround then..

mORMot Open Source

#1 2011-08-25 20:09:48

Quick to check if keyfields exist...

#2 2011-08-25 20:37:28

Re: Quick to check if keyfields exist...

#3 2011-08-26 06:51:56

Re: Quick to check if keyfields exist...

#4 2011-08-26 08:27:14

Re: Quick to check if keyfields exist...

#5 2011-08-26 08:51:33

Re: Quick to check if keyfields exist...

#6 2011-08-26 09:19:34

Re: Quick to check if keyfields exist...

#7 2011-08-26 10:01:59

Re: Quick to check if keyfields exist...

Board footer