Re: [sqlite] Most efficient storage for arrays

>>> Your data is from MongoDB :) Note they do have an extended JSON to >>> deal with types like ObjectId, binary and dates: >> Yes, it is. But I control the document structure and it does not have >> any types that can't be converted easily after a pull over the net. > >Does that mean you can

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] Most efficient storage for arrays

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/04/14 15:00, Neville Dastur wrote: > On 22 Apr 2014, at 21:58, Roger Binns wrote: >> Your data is from MongoDB :) Note they do have an extended JSON to >> deal with types like ObjectId, binary and dates: > Yes, it is.

Re: [sqlite] CTEs and unions

On 4/22/2014 5:55 PM, Andy Goth wrote: On 4/22/2014 5:16 PM, Dominique Devienne wrote: sqlite> with cte(a) as (select 1) ...> select * from cte ...> union all ...> select * from cte; Error: no such table: cte All these queries work for me without error.

Re: [sqlite] CTEs and unions

On 4/22/2014 5:16 PM, Dominique Devienne wrote: sqlite> with cte(a) as (select 1) ...> select * from cte; a 1 sqlite> with cte(a) as (select 1) ...> select * from cte ...> union all ...> select * from cte; Error: no such table: cte sqlite> with cte(a) as (select 1), ...>

Re: [sqlite] CTEs and unions

On Apr 23, 2014, at 12:16 AM, Dominique Devienne wrote: > is there no way to reuse a CTE several times? Hrm… of course you can… that’s the entire point of *Common* Table Expression: with DataSet as ( select 1 as value ) select * fromDataSet union all select * from

Re: [sqlite] Most efficient storage for arrays

On Apr 22, 2014, at 2:24 PM, Neville Dastur wrote: > So wondering is anyone that has done this sort of thing and worked out the > best way? Yes. Normalize your data. And that’s that: http://www.schemamania.org/sql/#lists Quoting a few words: "Questions are

[sqlite] CTEs and unions

so we can't use CTEs to avoid stuttering in queries? This is a contrived example of course, but is there no way to reuse a CTE several times? In this case, to get two rows of one column, both 1s, without repeating the query? https://sqlite.org/lang_with.html shows a CTE with two named queries,

Re: [sqlite] Most efficient storage for arrays

On 22 Apr 2014, at 21:58, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 22/04/14 05:24, Neville Dastur wrote: >> I am looking for some advice on storing and searching data that comes >> from an external JSON source and needs to be stored on

Re: [sqlite] Most efficient storage for arrays

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/04/14 05:24, Neville Dastur wrote: > I am looking for some advice on storing and searching data that comes > from an external JSON source and needs to be stored on device in a > Sqlite3 database. Your data is from MongoDB :) Note they do have

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:

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

I can generate the GUID as a 16 byte hex string, so the dashes are no problem. I'm working with VEE (similar to LabView) and .Net, so a lot of the mundane stuff is done for me with nifty little methods like ToArray(), ToString(), etc.. -Original Message- From:

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

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 originally designed to promote readability -

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 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] cannot set connection while a datareader is active

Il 21/04/2014 22.03, rava ha scritto: I know, you have all code wich is involved in the error... for the parte involved, no other function was called... i call executereader only for one datareader and two Times directly to the command... do you found the other datareader in the sent code? If

Re: [sqlite] BLOBs and NULLs

Cool. So it's treating each 2 digit pair as a single byte hex value, but what does blob do with the dashes? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Tuesday, April 22, 2014 11:55 AM To:

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

>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? -Bill -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On

Re: [sqlite] cannot set connection while a datareader is active

Il 21/04/2014 21.54, Joe Mistachkin ha scritto: Stefano Ravagni wrote: Thanks for answer Joe, but as just sayed i have only one datareader, That is not possible. There must be at least two different data reader instances because the references in the watch window did not match. Every time

Re: [sqlite] question for auto increament

Hi Yang, Most designers are lacking a bit inside the dev environments. You could use the sqlite command-line tool available from the downloads page at http://sqlite.org/ or indeed any of the DB Management GUIs out there for SQLite such as these: http://www.sqliteexpert.com/

[sqlite] Most efficient storage for arrays

Hi all I am looking for some advice on storing and searching data that comes from an external JSON source and needs to be stored on device in a Sqlite3 database. The JSON records are formatted as (simplified): { "_id" : ObjectId(“xx"), "name" : “Description”, “array1" :

[sqlite] question for auto increament

Hello, all: I am using VS2013 + sqlite.net for 4.5.1 with 32 bit. I have question for a primary key with auto increment. I can't figure out how to do it in C#. when I use sqlite datasource designer in VS2013. It has option to add identity checking, however, this feature doesn't work even I

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