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