Sign in to follow this  
Followers 0
lsakizada

Object ID

21 posts in this topic

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?


Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites



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.


Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by KaFu

Share this post


Link to post
Share on other sites

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!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

[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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.


Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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
1 person likes this

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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 !)


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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]);"

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

jhcd,

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

Also,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0