Re: [sqlite] Best way to store key,value pairs

Consider encoding the headers as JSON and storing them in a single column. SQLite has a JSON extension that makes it easy to access values from JSON data in a query. You can even index them. Simon’s suggestion (a row per header) is correct in theory, but the large numbers of headers you’ll be s

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert (Docs Update?)

On Tuesday, 7 January, 2020 17:05, Simon Slavin wrote: >On 8 Jan 2020, at 12:00am, Michael Kappert wrote: >> REPLACE INTO >REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume >that the command will do either an INSERT or a REPLACE. >See the notes about REPLACE on this page:

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 2020/01/08 2:00 AM, Michael Kappert wrote: -- Modify one parent entry of foreign key fk_t1_id REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new'); If I understand correctly, the upsert should behave like UPDATE in the

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 8.01.20 01:04, Simon Slavin wrote: > On 8 Jan 2020, at 12:00am, Michael Kappert wrote: > >> REPLACE INTO > > REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume that > the command will do either an INSERT or a REPLACE. > > See the notes about REPLACE on this page: > >

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 8 Jan 2020, at 12:00am, Michael Kappert wrote: > REPLACE INTO REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume that the command will do either an INSERT or a REPLACE. See the notes about REPLACE on this page: Is the behaviour yo

[sqlite] Cascaded delete unexpectedly triggered by upsert

Hi, as this is my first posting to this list, let me first say thank you for providing such a great library. It's fun to work with it. I'm intending to use SQLite in a game engine. I've encountered some unexpected behavior of foreign keys w.r.t an upsert: When an entry of the parent table is 'tou

Re: [sqlite] Best way to store key,value pairs

On 7 Jan 2020, at 7:13pm, Andy wrote: > But it is variable number this fields and column are strictly defined. Maybe > all in one Sqlite text field or define for example 10 field which often will > empty? Parent Child database. One table has one entry per message. The other table has one en

[sqlite] Best way to store key,value pairs

I am trying using Sqlite to news reader and local small NNTP server. NNTP message has header and body. Some special headers field needed to XOVER I keep in one table, body I will compress and store as blob, some other required header fields I will store in own columns, but also exists not required,

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

Hi, On Tue, Jan 7, 2020 at 2:01 AM Clemens Ladisch wrote: > > Richard Hipp wrote: > > On 1/5/20, Keith Medcalf wrote: > >> select * from a, b, c using (id); -- very strange result > > > > PostgreSQL and MySQL process the query as follows: > > > >SELECT * FROM a, (b JOIN c USING(id)); > > > >

Re: [sqlite] RTree function information

On 1/7/20, R Smith wrote: > On 2020/01/07 3:46 PM, Richard Hipp wrote: >> On 1/7/20, R Smith wrote: >>> I cannot find any mention of the other two. >> Those other routines are for testing and debugging use only. They >> will (likely) be made inaccessible to normal application code in the >> next

Re: [sqlite] RTree function information

On 2020/01/07 3:46 PM, Richard Hipp wrote: On 1/7/20, R Smith wrote: I cannot find any mention of the other two. Those other routines are for testing and debugging use only. They will (likely) be made inaccessible to normal application code in the next release. Thank you kindly. May I ass

Re: [sqlite] How create database, tables,indices...

To create a new database you just open the file name you want, and if it doesn't exist it will be created. The actual new file creation is slightly delayed until it has to write something to the disk. There are a couple of options that can only be set at file creation time like page size, text

Re: [sqlite] How create database, tables,indices...

On 7 Jan 2020, at 2:11pm, Andy wrote: > Which function are for crating new database, tables, indices, columns, fill > tables. You do this with SQL commands, just like the commands you use to read data out of the database. On 7 Jan 2020, at

Re: [sqlite] How create database, tables,indices...

I see: http://zetcode.com/db/sqlitec/ New database is simply open not existing file in path? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How create database, tables,indices...

I am beginner in Sqlite programming. I already open and read tables. I compiled sqlite3.c (8 MB) amalgamation file do DLL and call functions from FreePascal. Which function are for crating new database, tables, indices, columns, fill tables. I see #define SQLITE_CREATE_INDEX 1 /* Index N

Re: [sqlite] Size limits

I try create NNTP server. For text groups blobs will small - 5-80 kB, for binary - 0.8 MB wt., 7 sty 2020 o 13:31 David Raymond napisał(a): > Along with the other replies already mentioned I'll pipe in with a > reminder that any large blob fields should be the final field in a table's > definit

Re: [sqlite] RTree function information

On 1/7/20, R Smith wrote: > I see three R*Tree functions in the function list compiled-in with > latest binaries, namely: > rtreecheck(), > rtreedepth(), and > rtreenode(). > > While rtreecheck() is explained ... > > I cannot find any mention of the other two. Those other routines are for testing

[sqlite] RTree function information

I see three R*Tree functions in the function list compiled-in with latest binaries, namely: rtreecheck(), rtreedepth(), and rtreenode(). While rtreecheck() is explained at the bottom of the R*Tree documentation page at: https://sqlite.org/rtree.html#integrity_check_using_the_rtreecheck_sql_fun

Re: [sqlite] Size limits

Along with the other replies already mentioned I'll pipe in with a reminder that any large blob fields should be the final field in a table's definition. Due to how the data is stored, to get the data for any particular field, SQLite has to go through/decode the data for all previous fields in a

Re: [sqlite] Size limits

Hi Andy, What are reasonable limits for size Sqlite3 database file and large blobs? Here are some benchmarks on BLOB performance: https://sqlite.org/intern-v-extern-blob.html However, see the note here in section 4.1 about using the incremental BLOB I/O routines so that you don't thrash yo

Re: [sqlite] Size limits

Andy, I can state that SQLite easily went up to 100GB with zero issues for us. We decided to change the structure of our database and we reduced our database size as we didn't want to incur extra VPN costs. We also realised we designed the database wrong and it had a lot of useless data in...

Re: [sqlite] Size limits

On 7 Jan 2020, at 10:29am, Andy wrote: > What are reasonable limits for size Sqlite3 database file and large blobs? Unfortunately, recommendations (rather than hard limits) are closely tied to your OS and hardware. This is because the things you want to avoid are things like busting your cach

[sqlite] Size limits

What are reasonable limits for size Sqlite3 database file and large blobs? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

Richard Hipp wrote: > On 1/5/20, Keith Medcalf wrote: >> select * from a, b, c using (id); -- very strange result > > PostgreSQL and MySQL process the query as follows: > >SELECT * FROM a, (b JOIN c USING(id)); > > SQLite processes the query like this: > >SELECT * FROM (a,b) JOIN c USING (