how do i generate a uniqueidentifier ?

[Chase]

how do i generate a uniqueidentifier ?

- chase



-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Mark Richards]

Post by Chase
how do i generate a uniqueidentifier ?

Define a column as follows:
{fieldname} INTEGER NOT NULL PRIMARY KEY

eg:

CREATE TABLE hardware_types (record_key INTEGER NOT NULL PRIMARY
KEY,hardware_key INTEGER default 0);

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Chase]

sorry. should have been more specific. i'm talking about guids (or uuids).

here's the create table line:

CREATE TABLE foobar (id uniqueidentifier, foo text, bar text, PRIMARY
KEY (id));

that works great, but i have not been able so far to generate a fresh
guid to insert into the table.

in ms-sql, you'd use newid(), for example:

insert into foobar values (newid(), "Aaa", "Bbb");

and then you'd get something like:

select * from foobar;

id foo bar
___________________________________________________________

{00000109-0000-0010-8000-00AA006D2EA4} "Aaa" "Bbb"


so how is this done in sqlite3?

- chase

Post by Mark Richards

Post by Chase
how do i generate a uniqueidentifier ?

{fieldname} INTEGER NOT NULL PRIMARY KEY
CREATE TABLE hardware_types (record_key INTEGER NOT NULL PRIMARY
KEY,hardware_key INTEGER default 0);
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Samuel R. Neff]

I don't think there's any built-in way but you can create a custom function
for it pretty easily. Are you using sqlite directly or a wrapper?

Sam


-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact careers-b4xFXuYVD/yiub/***@public.gmane.org

-----Original Message-----
From: Chase [mailto:xanadu-***@public.gmane.org]
Sent: Friday, July 27, 2007 4:03 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: Re: [sqlite] how do i generate a uniqueidentifier ?


sorry. should have been more specific. i'm talking about guids (or uuids).

here's the create table line:

CREATE TABLE foobar (id uniqueidentifier, foo text, bar text, PRIMARY
KEY (id));

that works great, but i have not been able so far to generate a fresh
guid to insert into the table.

in ms-sql, you'd use newid(), for example:

insert into foobar values (newid(), "Aaa", "Bbb");

and then you'd get something like:

select * from foobar;

id foo bar
___________________________________________________________

{00000109-0000-0010-8000-00AA006D2EA4} "Aaa" "Bbb"


so how is this done in sqlite3?

- chase


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Igor Tandetnik]

Post by Chase
CREATE TABLE foobar (id uniqueidentifier, foo text, bar text, PRIMARY
KEY (id));
that works great, but i have not been able so far to generate a fresh
guid to insert into the table.
insert into foobar values (newid(), "Aaa", "Bbb");

There's nothing built into SQLite for this, but you can always create a
custom function named "newid" that would do the same.

Igor Tandetnik


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Alberto Simões]

Hi, Chase.

Post by Chase
id foo bar
___________________________________________________________
{00000109-0000-0010-8000-00AA006D2EA4} "Aaa" "Bbb"

I can't understand why do you need a big identifier like that, instead
of just a different integer as a key (as said below).

Post by Chase

Post by Mark Richards
{fieldname} INTEGER NOT NULL PRIMARY KEY

Note that the integer will be quite faster.
Cheers

[Chase]

hhmmm... i just noticed something weird. if i insert a string of ANY
length or format into the uniqueidentifier field, sqlite allows it...

insert into foobar values("{68753A44-4D6F-1225-9C60-0050E4C00067}",
"Aaa", "Bbb");
insert into foobar values("whatever", "Ccc", "Ddd");

select * from foobar;

id foo bar
____________________________________________________

{68753A44-4D6F-1225-9C60-0050E4C00067} Aaa Bbb
whatever Ccc Ddd



so i guess it's just a text type under the hood with no rules enforced
on the format of the string inserted into it.

not sure if i like that.

- chase

ps: thanks for the help on generating a guid.

{946D4A14-3A72-45F9-E162-0C397A}

sqlite> select "{" || hex(randomblob(4)) || "-" || hex(randomblob(2))
|| "-" || hex(randomblob(2)) || "-" || hex(randomblob(2)) || "-" ||
hex(randomblob(6)) || "}";
{4EA4A842-6ACA-0A81-5B42-F9E1D295BC27}
might work...

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Igor Tandetnik]

Post by Chase
hhmmm... i just noticed something weird. if i insert a string of ANY
length or format into the uniqueidentifier field, sqlite allows it...

SQLite allows any data in any column. This is a feature, not a bug. For
more details, see

http://sqlite.org/datatype3.html

Post by Chase
not sure if i like that.

Then I guess SQLite is not suitable for your needs. Manifest typing is
one of fundamental architectural decisions behind SQLite engine.

Igor Tandetnik


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Trevor Talbot]

Post by Chase
hhmmm... i just noticed something weird. if i insert a string of ANY
length or format into the uniqueidentifier field, sqlite allows it...
so i guess it's just a text type under the hood with no rules enforced
on the format of the string inserted into it.

sqlite datatypes are not like other database systems you may be used
to: http://sqlite.org/datatype3.html

Post by Chase
not sure if i like that.

Browse through the docs to make sure sqlite is actually what you want :)

Post by Chase

sqlite> select "{" || hex(randomblob(4)) || "-" || hex(randomblob(2))
|| "-" || hex(randomblob(2)) || "-" || hex(randomblob(2)) || "-" ||
hex(randomblob(6)) || "}";
{4EA4A842-6ACA-0A81-5B42-F9E1D295BC27}

Note that this is *NOT* a GUID/UUID. Be very sure of your
requirement: if you need a GUID, write a function that uses your
platform tools to get one. If you just need some long random
identifier, the above is fine, but don't call it a GUID. See RFC
4122.

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[d***@public.gmane.org]

Post by Trevor Talbot

sqlite> select "{" || hex(randomblob(4)) || "-" || hex(randomblob(2))
|| "-" || hex(randomblob(2)) || "-" || hex(randomblob(2)) || "-" ||
hex(randomblob(6)) || "}";
{4EA4A842-6ACA-0A81-5B42-F9E1D295BC27}

Note that this is *NOT* a GUID/UUID. Be very sure of your
requirement: if you need a GUID, write a function that uses your
platform tools to get one. If you just need some long random
identifier, the above is fine, but don't call it a GUID. See RFC
4122.

Please reread RFC-4122, especially section 4.4. While Chase's
guid is technically in violation of RFC-4122 in that it contains
4 extra bits of randomness, it is very close. And in a sense,
the extra 4 bits of randomness provide a stronger GUID than
RFC-4122 specifies.

I personally find all the syntax in RFC-4122 to be annoying
and so I usually do my GUIDs using hex(randomblob(20)). But
I guess that is just personal preference.

Note that SQLite contains a decent PRNG which is well seeded,
if you are running on Mac or Linux. (We need to work on better
seeding on Win32...)

--
D. Richard Hipp <***@hwaci.com>


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Trevor Talbot]

Post by d***@public.gmane.org

Post by Trevor Talbot

sqlite> select "{" || hex(randomblob(4)) || "-" || hex(randomblob(2))
|| "-" || hex(randomblob(2)) || "-" || hex(randomblob(2)) || "-" ||
hex(randomblob(6)) || "}";
{4EA4A842-6ACA-0A81-5B42-F9E1D295BC27}

Note that this is *NOT* a GUID/UUID. Be very sure of your
requirement: if you need a GUID, write a function that uses your
platform tools to get one. If you just need some long random
identifier, the above is fine, but don't call it a GUID. See RFC
4122.

Please reread RFC-4122, especially section 4.4. While Chase's
guid is technically in violation of RFC-4122 in that it contains
4 extra bits of randomness, it is very close. And in a sense,
the extra 4 bits of randomness provide a stronger GUID than
RFC-4122 specifies.

The reason RFC 4122 defines the format that way is to avoid collision
of the randomly generated version with other versions. Random
generation of GUIDs is weaker than the timestamp-based version, which
is actually guaranteed to be unique rather than merely
probabilistically unique. (Assuming perfect implementations, that is
-- I'm aware of the related practical problems of MAC number reuse,
clock sequencing storage, etc.)

If you don't pay attention to RFC 4122's defined format, what you're
generating simply isn't a GUID, and you run the risk of bad things
happening if you try to interoperate with a system that deals with
GUIDs.

Hence my warning :)

Post by d***@public.gmane.org
I personally find all the syntax in RFC-4122 to be annoying
and so I usually do my GUIDs using hex(randomblob(20)). But
I guess that is just personal preference.

Yeah, if all you need is something that's unique with extremely high
probability, there's not much sense in bothering with syntax designed
for working with other systems. You already know how to meet your
needs, the rest is just pointless overhead.

Post by d***@public.gmane.org
Note that SQLite contains a decent PRNG which is well seeded,
if you are running on Mac or Linux. (We need to work on better
seeding on Win32...)

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Trevor Talbot]

Hm, something ate the last part of my message...

Post by d***@public.gmane.org
Note that SQLite contains a decent PRNG which is well seeded,
if you are running on Mac or Linux. (We need to work on better
seeding on Win32...)

The crypto framework on recent versions of Windows can be used to get
a good quality seed. I'm not sure it's worth trying to do better on
older versions. It's something that can be done dynamically, much
like the Unicode filename stuff.

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[d***@public.gmane.org]

Post by Trevor Talbot
Hm, something ate the last part of my message...

Post by d***@public.gmane.org
Note that SQLite contains a decent PRNG which is well seeded,
if you are running on Mac or Linux. (We need to work on better
seeding on Win32...)

The crypto framework on recent versions of Windows can be used to get
a good quality seed. I'm not sure it's worth trying to do better on
older versions. It's something that can be done dynamically, much
like the Unicode filename stuff.

I don't do windows. Can you send suggested patches to the
sqlite3WinRandomSeed() function in os_win.c that access the
crypto framework on versions of windows that support such a
thing? The patches will need to fall back to using
some other universal seeding mechanism on systems that
lack the crypto framework.

--
D. Richard Hipp <***@hwaci.com>


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[d***@public.gmane.org]

Post by Chase
how do i generate a uniqueidentifier ?

hex(randomblob(16))

--
D. Richard Hipp <***@hwaci.com>


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------