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 is being added >> to the applicable base GUI

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 each

Re: [sqlite] BLOBs and NULLs

> Sent: Wednesday, April 23, 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 > wrote: > > > You don't ever really need a GUID at all. Simply use an "integ

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 will still > be unique a

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

SQLite seemed to provide good randomness in my (admittedly informal) tests.   Peter From: jose isaias cabrera >To: Peter Aronson ; General Discussion of SQLite Database > >Sent: Tuesday, April 22, 2014 1:06 PM >Subject: Re: [sqlite] BLOBs and NULLs > > > >"Peter Ar

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 for

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_ SQLITE_TRANSIENT, to avoid that sqli

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 very very large numbe

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 in base >> 256 and on which you cannot do ar

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 nothing. The GUID is n

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 128-bit is large enoug

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, it's actually very hard >>> to generate 2 random-based GUIDS [t

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 > use it as a PK / FK. >

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

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 was taking 2^64 was 4 b

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 128-bit is a >> very very large number.

Re: [sqlite] BLOBs and NULLs

-boun...@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 < &

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 must and GUIDs so

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 Paradox". Ask Google for mor

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: >> >> SELECT lower(hex(randomblob(20))); >>

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 origi

Re: [sqlite] BLOBs and NULLs

...@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 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 and

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 > wrote: > > On 22 Apr 2014, at 17:33, Richard Hipp wrote: > > > Use the small integer "id" value for internal foreign keys and whatnot. > > > And use the guid_id table to map GUIDs to id when

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

AM 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 wrote: >>I myself prefer create table foo (guid blob primary key [NOT NULL], ...). > > If a genuine GUID looks like this: 37af1247-2e77-4880-

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 PRIMARY KEY, guid TEXT UNIQUE); > > > > Use

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 keys and whatnot. >

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 because of the extra space needed

Re: [sqlite] BLOBs and NULLs

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 wrote: > On Mon, 21 Apr 2014 13:30:15 + > "Drago, William @ MWG - NARDAEAST"

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 to V1 or V4 > Algorithm generation, or some

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: Sto

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 for IL, Phase, RL, Isolation)? > Your database design

Re: [sqlite] BLOBs and NULLs

ite.org [mailto:sqlite-users-boun...@sqlite.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" wrote: > Should I s

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 Dr. Hipp's answer focussed o

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 by themselves, they take h

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

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

[sqlite] Blobs and ordering [was: Consequences of lexicographic sorting of keys in SQLite4?]

Simon, Thanks for your considered comments. On 2 Jul 2012, at 12:20, Simon Slavin wrote: > Worth remembering that BLOBs don't have a well-ordering function. You can > compare two BLOBs and tell whether they're the same (usually, but lossless > encoding defeats this), but if th

Re: [sqlite] BLOBs' affect on DB performance

Hello Paul, My experiences with blobs suggests it's better to keep them in a different DB file. My uses sounded very similar to yours, tables of normal data interleaved with blob inserts. The physical process of having to move from page to page seems to be the bottleneck, not Sqlite itself. I was

Re: [sqlite] BLOBs' affect on DB performance

On Tue, Mar 2, 2010 at 10:54 AM, Paul Vercellotti wrote: > Now I'm guessing that storing all those blobs will slow down access to the > main tables (assuming records are added gradually - most without associated > blobs, some with), because records would be spread out over many more pages > (more

[sqlite] BLOBs' affect on DB performance

Hi there, I'm wondering how larger BLOBs in a database affect performance of accessing the non-blob data. We've got a database with potentially a few million records in the main tables (of just strings and ints), but joined to that data set we want to store up to thousands (maybe 75000 max)

[sqlite] blobs

hi- i never had to do serious import/export. if a blob happens to be a document like a microsoft word document i have trouble visualizing where the document goes when it gets exported from the sqlite table. i saw the maestro and data wizard with its blob export command. when i think of export in

Re: [sqlite] BLOBs and sqlite_exec

On Oct 16, 2004, at 2:04 AM, D. Richard Hipp wrote: Will Leshner wrote: Sorry if this is terribly obvious, but I'm assuming that, in SQLite3, we can't use the sqlite_exec convenience API to store BLOBs, right? Correct. The only way to insert a BLOB is using sqlite3_prepare() followed by sqlite3_b

Re: [sqlite] BLOBs and sqlite_exec

Daniel K wrote: I think there might be something, but I can't remember exactly. It might be: INSERT INTO tbl VALUES ( X'ABCD' ); where ABCD is the hex representation of the blob data. If you do a ".dump" command from the shell on a database that contains blobs and look at the output it might prove

Re: [sqlite] BLOBs and sqlite_exec

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > Will Leshner wrote: > > Sorry if this is terribly obvious, but I'm > assuming that, in SQLite3, we > > can't use the sqlite_exec convenience API to store > BLOBs, right? > > I think there might be something, but I can't remember exactly. It m

Re: [sqlite] BLOBs and sqlite_exec

Will Leshner wrote: Sorry if this is terribly obvious, but I'm assuming that, in SQLite3, we can't use the sqlite_exec convenience API to store BLOBs, right? Correct. The only way to insert a BLOB is using sqlite3_prepare() followed by sqlite3_bind_blob(). -- D. Richard Hipp -- [EMAIL PROTECTE

Re: [sqlite] BLOBs and sqlite_exec

On Oct 15, 2004, at 9:43 AM, john mcnicholas wrote: Sorry, I couldn't be of more help but perhaps this will lead you in the right direction. Actually, this is extremely helpful. I should have realized that I could use the binding interface to bind blob data to a SQL statement. I'll play with tha

RE: [sqlite] BLOBs and sqlite_exec

e blob data type is TEXT as opposed to BLOB. I'm not sure why that is the case. Sorry, I couldn't be of more help but perhaps this will lead you in the right direction. John -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Friday, October 15, 2004 11:05 AM

Re: [sqlite] BLOBs and sqlite_exec

On Oct 15, 2004, at 7:50 AM, Will Leshner wrote: Sorry if this is terribly obvious, but I'm assuming that, in SQLite3, we can't use the sqlite_exec convenience API to store BLOBs, right? I see that if we go through the VM directly the API allows us to specify a data size, so that it doesn't depe

[sqlite] BLOBs and sqlite_exec

Sorry if this is terribly obvious, but I'm assuming that, in SQLite3, we can't use the sqlite_exec convenience API to store BLOBs, right? I see that if we go through the VM directly the API allows us to specify a data size, so that it doesn't depend on NULL-terminated C strings. But sqlite_exec

[sqlite] BLOBs and Encoding

Hello all. Does 3.x support BLOBs without encoding (via API) or do we need to encode it and use SQL INSERT? Also, I can't find any 8859 macros - is it only UTF-8 now? TIA for the help