Re: [sqlite] BLOBs and NULLs

UNSUBSCRIBE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

On Wed, 23 Apr 2014 17:51:17 +0200 Stephan Beal wrote: >On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf >wrote: >> You don't ever really need a GUID at all. Simply use an "integer primary >> key" (an integer starting at 1) and simply pretend that it

Re: [sqlite] BLOBs and NULLs

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Gerry Snyder > Sent: Wednesday, April 23, 2014 2:36 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] BLOBs and NULLs > > On 4

Re: [sqlite] BLOBs and NULLs

On 4/23/2014 10:21 AM, Drago, William @ MWG - NARDAEAST wrote: If I was sure I wouldn't be merging data I might use timer ticks as my ID, but I'm not sure and I can't take the chance. -Bill Would it be possible to use INTEGER PRIMARY KEY AUTOINCREMENT for the ID, and manually start

Re: [sqlite] BLOBs and NULLs

, 2014 11:51 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] BLOBs and NULLs > > On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf <kmedc...@dessus.com> > wrote: > > > You don't ever really need a GUID at all. Simply use an "integer > &g

Re: [sqlite] BLOBs and NULLs

On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf wrote: > You don't ever really need a GUID at all. Simply use an "integer primary > key" (an integer starting at 1) and simply pretend that it is being added > to the applicable base GUID of your random choosing. Everything

Re: [sqlite] BLOBs and NULLs

>> In summary: the context of a GUID defines its "scope of required >> uniqueness," and a 16-byte GUID is essentially globally unique so long >> as >> it has no collisions within its context(s). (i.e. who cares if SHA1s >> collide, so long as it's not in the same repo?) > >You might be interested

Re: [sqlite] BLOBs and NULLs

, 2014 1:06 PM >Subject: Re: [sqlite] BLOBs and NULLs > > > >"Peter Aronson" wrote... > > >> If you want to use sqlite3_randomness to generate a Version 4 UUID >> according to RFC4122, the following code will can be used: >> >> unsigned

Re: [sqlite] BLOBs and NULLs

"Peter Aronson" wrote... If you want to use sqlite3_randomness to generate a Version 4 UUID according to RFC4122, the following code will can be used: unsigned char uuid_data[16]; /* We'll generate a version 4 UUID as per RFC4122. Start by generating 128 bits of randomness (we will use 122

Re: [sqlite] BLOBs and NULLs

If you want to use sqlite3_randomness to generate a Version 4 UUID according to RFC4122, the following code will can be used:     unsigned char  uuid_data[16];   /* We'll generate a version 4 UUID as per RFC4122.  Start by generating 128 bits of randomness (we will use 122 of them).

Re: [sqlite] BLOBs and NULLs

>> That's why I wrote "our galaxy", not the "whole universe" ;) --DD > > > Hehe, my bad... but that only changes a few orders of magnitude, there's only > a few billion galaxies :D OK, you got me! After reading http://www.universetoday.com/36302/atoms-in-the-universe/, 1e38 is not even enough

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 8:55 PM, Dominique Devienne wrote: > > than using string-format data (be sure to use SQLITE_TRANSIENT when > binding > > the memory, too). > Sorry - i meant SQLITE_STATIC. If your memory will outlive the step() call then use that, _NOT_

Re: [sqlite] BLOBs and NULLs

On 2014/04/22 20:52, Dominique Devienne wrote: On Tue, Apr 22, 2014 at 8:46 PM, RSmith wrote: On 2014/04/22 20:06, Dominique Devienne wrote: Regarding the uniqueness argument made by DRH, it's actually very hard to generate 2 random-based GUIDS, given that a 128-bit is a

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 8:47 PM, Stephan Beal wrote: > On Tue, Apr 22, 2014 at 8:25 PM, Dominique Devienne > wrote: > >> Yet I don't see the point of a BIGINT either. A blob can effectively >> act as a arbitrary sized integer already, albeit one stored

Re: [sqlite] BLOBs and NULLs

On 22 Apr 2014, at 4:55pm, Dominique Devienne wrote: > Simply because of the extra space needed to store it. 36 bytes vs 16 > bytes. That's 20 wasted bytes for the PK, and everytime that PK is > references in other tables' FKs too. Times millions of rows, it adds > up, for

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 8:46 PM, RSmith wrote: > On 2014/04/22 20:06, Dominique Devienne wrote: >> Regarding the uniqueness argument made by DRH, it's actually very hard >> to generate 2 random-based GUIDS, given that a 128-bit is a very very >> large number. It is said that

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 8:36 PM, Dominique Devienne wrote: > On Tue, Apr 22, 2014 at 8:16 PM, Richard Hipp wrote: >> On Tue, Apr 22, 2014 at 2:06 PM, Dominique Devienne >> wrote: >> >>> Regarding the uniqueness argument made by DRH,

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 8:25 PM, Dominique Devienne wrote: > Yet I don't see the point of a BIGINT either. A blob can effectively > act as a arbitrary sized integer already, albeit one stored in base > 256 and on which you cannot do arithmetic, but that's OK and enough to >

Re: [sqlite] BLOBs and NULLs

On 2014/04/22 20:06, Dominique Devienne wrote: Regarding the uniqueness argument made by DRH, it's actually very hard to generate 2 random-based GUIDS, given that a 128-bit is a very very large number. It is said that 128-bit is large enough to store the estimated number of atoms in our

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 2:36 PM, Dominique Devienne wrote: > Said Google tells me 2^128 - 1 = 3.4028237e+38 > > and that sqrt(2^128 - 1) = 1.8446744e+19 > > You've confused a 128-bit with a 64-bit integer in your 4 billion > approximation, no? > Yes. For a moment there, I

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 8:16 PM, Richard Hipp wrote: > On Tue, Apr 22, 2014 at 2:06 PM, Dominique Devienne > wrote: > >> Regarding the uniqueness argument made by DRH, it's actually very hard >> to generate 2 random-based GUIDS [that collide], given that a

Re: [sqlite] BLOBs and NULLs

...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RSmith Sent: Tuesday, April 22, 2014 1:57 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] BLOBs and NULLs On 2014/04/22 19:12, Richard Hipp wrote: > On Tue, Apr 22, 2014 at 1:07 PM, Drago, William @ MWG - NARDAEAST < > w

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 6:48 PM, Richard Hipp wrote: > On Tue, Apr 22, 2014 at 12:37 PM, Neville Dastur >> I would hazard a guess that most mobile apps that use an internal DB, use >> sqlite. With inconsistent mobile network coverage, having pure client side >> PK generation is a

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 2:06 PM, Dominique Devienne wrote: > Regarding the uniqueness argument made by DRH, it's actually very hard > to generate 2 random-based GUIDS [that collide], given that a 128-bit is a > very very > large number. > This is called the "Birthday

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 6:57 PM, Stephan Beal wrote: > On Tue, Apr 22, 2014 at 6:48 PM, Richard Hipp wrote: >> Fossil generates some of its "GUID"s using the SHA1 hash algorithm. Other >> GUIDs (for example for ticket IDs) are generated using: >> >>

Re: [sqlite] BLOBs and NULLs

On 2014/04/22 19:12, Richard Hipp wrote: On Tue, Apr 22, 2014 at 1:07 PM, Drago, William @ MWG - NARDAEAST < william.dr...@l-3com.com> wrote: Does blob ignore them if they are included? No. That would be a syntax error. The dashes in (strict) GUIDs are an arbitrary construct (perhaps

Re: [sqlite] BLOBs and NULLs

qlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Tuesday, April 22, 2014 12:56 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] BLOBs and NULLs > > On Tue, Apr 22, 2014 at 12:55 PM, Drago, William @ MWG - NARDAEAST < &

Re: [sqlite] BLOBs and NULLs

Does blob ignore them if they are included? -Bill -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, April 22, 2014 12:56 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] BLOBs

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 12:55 PM, Drago, William @ MWG - NARDAEAST < william.dr...@l-3com.com> wrote: > Cool. So it's treating each 2 digit pair as a single byte hex value, but > what does blob do with the dashes? > Since the dashes carry no information, you could leave them out. -- D. Richard

Re: [sqlite] BLOBs and NULLs

To: General Discussion of SQLite Database Subject: Re: [sqlite] BLOBs and NULLs On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST <william.dr...@l-3com.com> wrote: >>I myself prefer create table foo (guid blob primary key [NOT NULL], ...). > > If a genuine GUID looks l

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 12:37 PM, Neville Dastur wrote: > > On 22 Apr 2014, at 17:33, Richard Hipp wrote: > > > The usual solution here is to have a table that maps GUIDs into small > > locally-unique integers: > > > >CREATE TABLE guid_id(id INTEGER

Re: [sqlite] BLOBs and NULLs

On 22 Apr 2014, at 17:33, Richard Hipp wrote: > The usual solution here is to have a table that maps GUIDs into small > locally-unique integers: > >CREATE TABLE guid_id(id INTEGER PRIMARY KEY, guid TEXT UNIQUE); > > Use the small integer "id" value for internal foreign

Re: [sqlite] BLOBs and NULLs

The usual solution here is to have a table that maps GUIDs into small locally-unique integers: CREATE TABLE guid_id(id INTEGER PRIMARY KEY, guid TEXT UNIQUE); Use the small integer "id" value for internal foreign keys and whatnot. And use the guid_id table to map GUIDs to id when moving data

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST wrote: >>I myself prefer create table foo (guid blob primary key [NOT NULL], ...). > > If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a, then > why blob and not text? Simply

Re: [sqlite] BLOBs and NULLs

org] On Behalf Of Dominique Devienne Sent: Tuesday, April 22, 2014 5:07 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] BLOBs and NULLs On Mon, Apr 21, 2014 at 4:58 PM, James K. Lowden <jklow...@schemamania.org> wrote: > On Mon, 21 Apr 2014 13:30:15 + > "

Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 12:05 PM, Simon Slavin wrote: > On 22 Apr 2014, at 10:07am, Dominique Devienne wrote: > Store them as 32 hex digits, or 32 hex digits with the minus signs in, or as > a 32-bit-length integer, I don't care, but have them conform

Re: [sqlite] BLOBs and NULLs

On 22 Apr 2014, at 10:07am, Dominique Devienne wrote: > using GUIDs Don't particularly mind if anyone is using GUIDs, but if anyone is using calling something GUID can you please make sure it's a real GUID ? They look like this:

Re: [sqlite] BLOBs and NULLs

On Mon, Apr 21, 2014 at 4:58 PM, James K. Lowden wrote: > On Mon, 21 Apr 2014 13:30:15 + > "Drago, William @ MWG - NARDAEAST" wrote: > >> Should I split this table up into smaller tables to eliminate the >> NULLs (e.g. use one table each

Re: [sqlite] BLOBs and NULLs

ite.org] On Behalf Of James K. Lowden Sent: Monday, April 21, 2014 10:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] BLOBs and NULLs On Mon, 21 Apr 2014 13:30:15 + "Drago, William @ MWG - NARDAEAST" <william.dr...@l-3com.com> wrote: > Should I spl

Re: [sqlite] BLOBs and NULLs

On Mon, 21 Apr 2014 13:30:15 + "Drago, William @ MWG - NARDAEAST" wrote: > Should I split this table up into smaller tables to eliminate the > NULLs (e.g. use one table each for IL, Phase, RL, Isolation)? I'm not > sure what the best design choice would be. While

Re: [sqlite] BLOBs and NULLs

On Mon, 21 Apr 2014 13:30:15 +, "Drago, William @ MWG - NARDAEAST" wrote: > Should I split this table up into smaller tables to > eliminate the NULLs (e.g. use one table each for IL, > Phase, RL, Isolation)? Adding to what Richard said: (3) NULLs are not a problem

Re: [sqlite] BLOBs and NULLs

On Mon, Apr 21, 2014 at 9:30 AM, Drago, William @ MWG - NARDAEAST < william.dr...@l-3com.com> wrote: > All, > > One of the tables in my database has 4 columns that will hold small (under > 5K) BLOBs. In many cases there will be no data at all in one or more of > these columns (see sample below).

[sqlite] BLOBs and NULLs

All, One of the tables in my database has 4 columns that will hold small (under 5K) BLOBs. In many cases there will be no data at all in one or more of these columns (see sample below). Does this present any kind of problem? Should I split this table up into smaller tables to eliminate the