Object ID

[lsakizada]

Hi,

I want to creatre a uniqe data raw in database table.

So, I want to create a column's table that will contain a unique 'Object ID' data.

The objetct ID is should be so unique that I will never has same even if the table will be populated with milions of raws.

What is the approach to create such a unique data?

Should it be same as clasid format in the ergistry?

Please advice?

[KaFu]

What kind of database? SQLite adds the Column RowID by default.

[lsakizada]

What kind of database? SQLite adds the Column RowID by default.

That's not enought for my needs even though I have the RowID (indeed, I am using SQLite).

My program is a Client/Server.

The client can create/modify/delete any raw and the server can send to client update per raw data.

The client get a database file and do the merge on both database files.

since I am supposed to merge data from two database files which created from different resources,

The app. must perform the merge based on unique column, not the RowID.

[jchd]

Still speaking of SQLite:

Yes, every SQLite table has a rowid column autoincremented (up to 2¨^63 - 1) but I strongly recommend to explicitely declare your own column for that, thusly:

create mytable (Id integer not null primary key, colA text, ... any other column);

The big advantage to explicitely declaring an integer primary key yourself is that the values are not mutable accross backup/restore and such, while implicit rowid hidden column doesn't garantee it will be the same on backup/restore or table copy operations. This turns out to be very important if, as any well-designed DB, the Id serves as a parent foreign key as well.

You dont need to input a value in your declared column as SQLite will autoincrement to the next available key for you automagically:

insert into mytable (colA) values ('the primary key will be generated for you'); -- will work fine

In short, there is never a good reason not to declare your integer primary key explicitely. Also note that the type INTEGER (note full spelling) is needed for this to work as intended and is a different beast than a column declared INT PRIMARY KEY.

Personal advice: keep away of GUIDs and other pseudo-unique identifiers as they offer _no_ garantee of uniqueness, eat up much more space & are magnitude slower than integers and are a pain to use in your applications.

EDIT: after your last answer, what I would do in your case is assign each server a rowid range: server A starts rowids at 2^50, server B at 2^51, ... That or somthing equivalent should leave you plenty of room for inserting a large enough number of rows.

EDIT2: even then are you sure you absolutely need to transfer primary key across machines?

Edited February 9, 2011 by jchd

[lsakizada]

EDIT: after your last answer, what I would do in your case is assign each server a rowid range: server A starts rowids at 2^50, server B at 2^51, ... That or somthing equivalent should leave you plenty of room for inserting a large enough number of rows.

EDIT2: even then are you sure you absolutely need to transfer primary key across machines?

My case is actually about how to update a database1.table1 with database2.table2 per designated row.

The RowId of equivalent tables are differents

e.g.

- Open the two databases

- perform the following process

For i=1 to 'max Databse1 rows'

    For j=1 to 'max databases2 rows'
     
     if Database1.Table1.UniqueColumn ==Database2.Table2.UniqueColumn Then
       'Update the other fields of that database1.Table1 designated row'

    EndIf

    Next
Next

EDIT: So, I need a unique data in the unique columns so the procedure update that table's row.

Edited February 9, 2011 by lsakizada

[KaFu]

So... is there a unique combo of columns in your database, or may complete rows be duplicate? Heres an idea in pseudocode:

for $i = 0 to lastrow_of_DB1
    
    select $customer, $date, $invoicenumber, $invoicerow from DB1
    
    select * from DB2 where customer=$customer AND date=$date AND invoicenumber=$invoicenumber AND invoicerow = $invoicerow
    (do some $error checking, exactly 1 row returned?)
    
    update values in DB1 with results from call to DB2
    
next

Edit: From a performance standpoint it's a really bad idea to iterate through all rows of both DBs ...

Edited February 9, 2011 by KaFu

[jchd]

Aren't you better off placing a decent hash (MD5 would do, but some SHA* will do nicely as well) in the mergeable columns. Computing an MD5 is very fast and has the advantage of relying exclusively on actual row contents (partial or full) rather than being some pseudo-unique token picked at random. Every modern OS has fast primitives for computing any hash on data in memory or file or mapped file. There are numerous examples of UDFs posted in the example forum.

BTW your vision of merging DBs may be suited to your use case, but is essentially impossible in the general case. Why do you think you need two imbricated loops?

Attach the client to the server and perform a select Id with INTERSECT on the unique_column, resulting in a array of rowids on which you can act in a single loop.

Anyway I strongly advise you place the whole stuff in exclusive transactions!

[lsakizada]

Aren't you better off placing a decent hash (MD5 would do, but some SHA* will do nicely as well) in the mergeable columns. Computing an MD5 is very fast and has the advantage of relying exclusively on actual row contents (partial or full) rather than being some pseudo-unique token picked at random. Every modern OS has fast primitives for computing any hash on data in memory or file or mapped file. There are numerous examples of UDFs posted in the example forum.

BTW your vision of merging DBs may be suited to your use case, but is essentially impossible in the general case. Why do you think you need two imbricated loops?

Attach the client to the server and perform a select Id with INTERSECT on the unique_column, resulting in a array of rowids on which you can act in a single loop.

Anyway I strongly advise you place the whole stuff in exclusive transactions!

My vision to pass over the network sqlite databases to be merged with others has many advantages.

But in order to merge data from two or more database tables I must make the rows uniques.

I was thinking about MD5 hashing such as using ' _Crypt_HashData ' but still this method wont be enough to create uniqueness.

Because I do wnow what hash string to use for each row in order to be unique .

I created two loops just to demonstrate my case, not realy to reach the goal efficiently.

And certainly you and Kafu are right that it will be expensive to merge two databases...

Edited February 9, 2011 by lsakizada

[jchd]

[back with you after lunch.]

I was thinking about MD5 hashing such as using ' _Crypt_HashData ' but still this method wont be enough to create uniqueness.

Because I do wnow what hash string to use for each row in order to be unique .

I find it hard to understand: either you _do_ know which column to use for creating a hash, in which case a hash will be fine. Or you _don't_ know. Having an example context would help better understand.

What you can do is have a pseudo-unique column filled with randomblob. It won't be less random than any kind of GUID or the like and doesn't need external component. Use an AFTER INSERT trigger to fill the column. If you need example code, I'll be glad to help.

I tried this with SQLite Expert and it whould work fine in your case:

CREATE TABLE [test] (
  [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [colA] CHAR(20), 
  [colB] CHAR, 
  [marker] BLOB);

CREATE TRIGGER [trInsTest]
AFTER INSERT
ON [test]
BEGIN
    update test set marker = randomblob(32) where Id = new.Id;
END;
CREATE UNIQUE INDEX [ixMarker] ON [test] ([marker]);

Using this, to insert in the client DB you do:

INSERT into Test (colA, colB) values ('this is', 'one row');

From the server side, you do [untested!!!]:

attach clientDB as cli;
begin exclusive;
update main.test set main.colA = cli.colA, main.colB = cli.colB where main.marker in (select marker from main.test intersect select marker from cli.test);
commit;
detach cli;

Notes:

o) randomblob(32) is a bit of an mouthful, 16 bytes should suffice in practice!

o) I made a unique index over marker not only to insure uniqueness (within the client) but also to speed up searches and merging

o) the Id column isn't required, but is still always a good idea if ever things turn out to need more complex interaction.

o) that the "marker" generated this way will not be "more unique" than any kind of GUID or similar: it's a random string of bits with low probability for collision. It uses SQLite core function and is portable accross all implementations.

Finally I apologize to "regular" AutoIt users as this topic is fairly SQLite-centered but there are enough opportunities in everyday use of AutoIt to use SQLite as well that I can't resist answering here.

[lsakizada]

Hi Jchd,

What you can do is have a pseudo-unique column filled with randomblob

Thanks alot for the effort you put here.

I was not aware of the sqlite's randomblob function.

Looks for me that this is the direction I need to follow.

[jchd]

You're welcome. At least randomblob has the great advantage of telling what it is: a random string of random bytes with no formal guarantee of uniqueness either within one system nor across systems.

Chime again if something isn't clear.

Good luck anyway.

[Bowmore]

If you wand an ID that is guaranteed to be unique when a database records are created off line then I suggest that you use GUIDs Global Unique IDentifiers

Each client could create their own IDs with no worries about duplicates being generated

A quick Autoit function for creating GUIDs with an example of how to use it.

#Include <WinAPI.au3>

For $i = 1 To 10
    $ID = _GetGuid()
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $ID = ' & $ID & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
Next

Func _GetGuid()
  Static Local $tGuid = DllStructCreate($tagGUID)
  Static Local $ptrtGuid = DllStructGetPtr($tGuid)
  
  DllCall("OLE32.DLL", "dword", "CoCreateGuid", "ptr", $ptrtGuid)
  Return  _WinAPI_StringFromGUID($ptrtGuid)
    
EndFunc   ;==>_GetGuid

[jchd]

GUID offer _no_ formal guarantee of uniqueness. Of course collisions are rare but can't just be ruled out, just like randomblob.

Then GUIDs need to be generated by the application while randomblob is a fast core function.

Formatted GUIDs are terrible (0AFDC426-C52E-46F0-8752-5F9C71159FD9) and hide the fact that a number of bits have fixed value, leaving only the rest for actual identification.

Depending on the system used for their generation, they may be replicable (Dell once had this problem with a series of PCs !!!).

For instance v4 GUIDs have 122 bits of randomness, which is less than classical randomblob(16). If your paranoia demands it, you can use randomblob(1024) while GUIDs are limited to 122 random bits.

I can see no good reason to select GUIDs over simple randomblobs. The worst is when those fashionable GUIDs are used as primary keys or simply indexed, which make the indexes provably _perfectly fragmented_ defeating most indexation benefit.

[lsakizada]

I agree with jchd that his method is fastest but not uniqueness.

BTW: If you want to use GUIID you can try this (use Yashid's WinAPIEx.au3 UDF)

#Include <WinAPIEx.au3>
#include <string.au3>
For $i = 1 To 5
    ConsoleWrite(Binary(_WinAPI_CreateGUID()) & @CR)
Next

This is the output:

0x7B41433642363944312D373932452D343343422D413532352D3145323936423036323141367D

0x7B36414431363745442D414437382D344445332D423943332D3445343736464332433044337D

0x7B33424542343739392D423644332D344133422D413632452D4530314639393646374231417D

0x7B42373030423136462D374241342D344534442D413344372D3933423241373938313032337D

0x7B33323944393336302D424546392D343735332D423538342D4230373937444532374143447D

Edited February 10, 2011 by lsakizada

[jchd]

You can use that as well. Nonethelees what gets created is an _ASCII_ GUID. Displaying these as binary only displays hex values of each ASCII character within the string.

OTOH, this is a view of using the previously mentionned SQLite table (but limited to a randomblob(16) instead of ~(32)), using hex(marker) to to display the blob as hex:

Id colA   colB   Marker                         
-- ------ ------ -------------------------------- 
 1 a    (null) 858F1463A829E88D8495510D79BCBB17 
 2 b    (null) B7D055EC7C6C9D77B8391435247FA06B 
 3 c    (null) 299E47E79C8B1CE2CB1C11E5E3360A59 
 4 d    (null) E21CD93413EC75AFCD2699156C4B9257 
 5 e    (null) 16E049BF9F2E3618BEE1BCC92667A2FF 
 6 f    (null) C33B0E112C1C3ED1AE2B852D044D8122 
 7 (null) g    8587B5CE63475668B95444B1096F8CFC 
 8 (null) h    274CF6EA59718434C3C094A4860B16FB 
 9 (null) i    B1150F7467715DAEB7F7D66A9B16E3BD 
10 (null) j    C34D0B6E278873A7112E522CA0D96A0F 
11 (null) k    96DD39FC51DD50B26660DD2DB8A1B4F9 
12 (null) l    0C6641C3D129DACBEF733B077652258D

I give up trying to align the above correctly after 15' unsuccessful tries (that's utterly irritating !)

[lsakizada]

Hi Jchd

I implemented the SQL sentence on my application and seems to work.

When I will look for the OBJID, I must convert to Hex string. right?

Global Const $sql_CreateTable_Links = "CREATE TABLE [Links] " & _
        "(" & _
        "[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," & _
        "[GroupName]," & _
        "[LinkName]," & _
        "[Version] INTEGER," & _
        "[PermissionLevel]," & _
        "[UpdateDate]," & _
        "[HK1]," & _
        "[HK2]," & _
        "[IsActive]," & _
        "[URL]," & _
        "[Type]," & _
        "[Description]," & _
        "[Obsolete]," & _
        "[marker] BLOB" & _
        ");" & _
        "CREATE TRIGGER [trInsLinks] " & _
        "AFTER INSERT " & _
        "ON [Links] " & _
        "BEGIN " & _
        "update Links set marker = randomblob(32) where Id = new.Id; " & _
        "END; " & _
        "CREATE UNIQUE INDEX [ixMarker] ON [Links] ([marker]);"

[jchd]

Yep, hex(column) produces what I posted previously. Here's the view DDL I was using:

CREATE VIEW [viewtest] AS

select Id, ColA, ColB, hex(Marker) Marker from test;

Internally the value is stored as a BLOB, in your case 32 bytes of raw random binary data (512 bits, woah!).

Allow me an advice: don't leave your column types undefined, that makes SQLite affinity useless (affinity = NONE). Most of the times you have a pretty good idea of the type you expect to store in each column and you should specify that type. Defining a column type doesn't preclude you to insert another type in that column: you can insert a string in a FLOAT or BLOB or whatever.

Only cases where you need to leave column type undefined is when you really intend to be able to store say values like 123 and '123' and retieve an integer in the first case and a string in the latter one. I believe this is only marginal use/need in most applications.

[kylomas]

Gentlemen,

If you are trying to ensure uniqueness by row across multiple DB's how can this ever be true?

(code from post #5)

For i=1 to 'max Databse1 rows'

    For j=1 to 'max databases2 rows'
     
     if Database1.Table1.UniqueColumn ==Database2.Table2.UniqueColumn Then
       'Update the other fields of that database1.Table1 designated row'

    EndIf

    Next
Next

I am trying to follow this as I am converting an app from VBS/Access to AutoIT/SQLLITE? (unsure if I should convert the DB).

@ISakizada - please excuse the interruption

@jhcd - Is there some other more appropriate way to get into an in depth discussion of DB's from AutoIT's perspective?

kylomas

[jchd]

Hi kylomas,

I'm always happy to help AutoIt people get on par with what SQLite can bring them, and there are a _huge_ number of situations where such a lightweight, no fuss DB can be utterly useful. The decent integration we enjoy in AutoIt makes it even more attractive.

I always feel the need to apologize when discussion shifts from "how can I do this in AutoIt" towards pure SQLite or even pure SQL issues/solutions. Of course the sqlite mailing list is appropriate for SQLite discussion but the topics typically discussed there are often well above beginners head (I've been there too!).

Trying to minimize non-pure AutoIt discussion here, I often accept PMs for SQLite discussions but this is private hence completely uninformative for subsequent readers/questionners and has turned out to (my) cannibalism at occasion.

I'd like to have time to put together a piece of wiki about how to start using SQLite in AutoIt applications and how to use SQLite efficiently. I've written SQLite extensions modules which I happily share with requesters and I'm working on another one at spare time (meaning "with slow progress").

All in all and if Mods don't object that a few threads (like this one) slip towards SQLite-centric topics, I'll keep on answering reasonable questions here, possibly in detail. Again, I do so because I've realized that the AutoIt-SQLite couple makes a winning ticket in really many situations, even when the mere idea of using of a DB looks like a ridiculous overkill, but creating an SQLite memory DB and performing complex actions/transformations/queries on it often reveals to be orders of magnitude simpler and faster than pedestrian AutoIt loops and code.

[kylomas]

jhcd,

Thank you...where can I find the SQLite mainling list?

Also,

What about the ? I asked above (way off base?)

kylomas