[sqlite] documentation: add PRAGMA journal_mode form without schema
The pragma documentation for journal_mode forms all specify a schema: https://sqlite.org/pragma.html#pragma_journal_mode PRAGMA schema.journal_mode; PRAGMA schema.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF The WAL documentation uses a form without a schema: https://sqlite.org/wal.html#activating_and_configuring_wal_mode PRAGMA journal_mode=WAL; Could we add the form without a schema to the pragma documentation? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrent reads for VTs with in-memory data structures
If it is singleton data the I suppose you could keep a static pointer to the data structure, a static "use" counter, and a static mutex. Then, for each connection (xConnect), lock the mutex, if the static use counter is zero then build the data structure and increment the use counter, then set the new connection to use the shared data structure, then release the mutex. For each xDisconnect, lock the mutex, release that set of virtual table resources, decrement the counter and if the counter is zero, release the static data, then release the mutex. This assumes that the data needs "reloading" after nothing is using it. If this is not the case you do exactly the same thing except to never rebuild the data structure after the first time, all you need (inside the mutex) on the xConnect it to build the static data structure if it does not exist, and on the xDisconnect simply release the virtual table resources (but never the shared static data structure). This would mean, of course, that the data would be "static" across all connections in a process, but different processes would have their own static data. If you need to release and rebuild the data structure this will happen only when no connection in the process is using connected to the data. By diddling with the data attributes (basically making them all extern references to a specially constructed data segment) you could make the static data structure a singleton across multiple processes. However this is not the default because static data segments are "process local" not "library local" ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Dimitris Bil > Sent: Monday, 3 July, 2017 11:14 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Concurrent reads for VTs with in-memory data structures > > I have some virtual tables that keep in-memory data structures. Data > loading is happening at table creation (xCreate and xConnect are the same) > and after that, during querying, only read access is needed. Queries do > not access any other tables. Is there a way to achieve concurrent > execution without having to keep multiple copies of each data structure? > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concurrent reads for VTs with in-memory data structures
I have some virtual tables that keep in-memory data structures. Data loading is happening at table creation (xCreate and xConnect are the same) and after that, during querying, only read access is needed. Queries do not access any other tables. Is there a way to achieve concurrent execution without having to keep multiple copies of each data structure? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
pragma foreign_key_list(table_name) may help Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 3 July 2017 at 15:05, Keith Medcalf wrote: > > You can get foreign key constraints with a pragma. > Check constraints need to parse the SQL. > > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Clemens Ladisch > > Sent: Monday, 3 July, 2017 08:00 > > To: sqlite-users@mailinglists.sqlite.org > > Subject: Re: [sqlite] FOREING KEY constraint > > > > J. King wrote: > > > The sqlite_master table should have this information. > > > > > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name > AND > > tbl_name IS your_table_name; > > > > Constraints do not have separate entries in the sqlite_master table. > > And there is no other mechanism to get this information without parsing > > the SQL. > > > > > > Regards, > > Clemens > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
You can get foreign key constraints with a pragma. Check constraints need to parse the SQL. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Clemens Ladisch > Sent: Monday, 3 July, 2017 08:00 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] FOREING KEY constraint > > J. King wrote: > > The sqlite_master table should have this information. > > > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND > tbl_name IS your_table_name; > > Constraints do not have separate entries in the sqlite_master table. > And there is no other mechanism to get this information without parsing > the SQL. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
This is not true. You can only get the constraints from sqlite_master by parsing the table creation sql statements. constraints are not added independantly to sqlite_master -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of J. King > Sent: Monday, 3 July, 2017 07:51 > To: SQLite mailing list > Subject: Re: [sqlite] FOREING KEY constraint > > The sqlite_master table should have this information. > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND > tbl_name IS your_table_name; > > On July 3, 2017 9:37:04 AM EDT, Igor Korot wrote: > >Hi, Keith et al, > > > >On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf > >wrote: > >> > >> From what I can tell the answer is (A). The constraint_name is > >simply a comment to be reported (if possible) when the constraint is > >violated. > > > >So is it possible to check that the foreign key with the given name > >already present for a given > >table? > > > >Thank you. > > > >> > >> -- > >> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > >> > >>> -Original Message- > >>> From: sqlite-users > >[mailto:sqlite-users-boun...@mailinglists.sqlite.org] > >>> On Behalf Of Simon Slavin > >>> Sent: Monday, 3 July, 2017 04:49 > >>> To: SQLite mailing list > >>> Subject: Re: [sqlite] FOREING KEY constraint > >>> > >>> > >>> > >>> On 3 Jul 2017, at 4:37am, Keith Medcalf wrote: > >>> > >>> > What do you mean "check for uniqueness? > >>> > >>> If you give two constraints the same name, does SQLite > >>> > >>> A) Ignore the problem > >>> B) Reject the second one complaining "duplicate name" > >>> C) Replace the first one with the second > >>> > >>> Simon. > >>> ___ > >>> sqlite-users mailing list > >>> sqlite-users@mailinglists.sqlite.org > >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
J. King wrote: > The sqlite_master table should have this information. > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND > tbl_name IS your_table_name; Constraints do not have separate entries in the sqlite_master table. And there is no other mechanism to get this information without parsing the SQL. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
The sqlite_master table should have this information. SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND tbl_name IS your_table_name; On July 3, 2017 9:37:04 AM EDT, Igor Korot wrote: >Hi, Keith et al, > >On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf >wrote: >> >> From what I can tell the answer is (A). The constraint_name is >simply a comment to be reported (if possible) when the constraint is >violated. > >So is it possible to check that the foreign key with the given name >already present for a given >table? > >Thank you. > >> >> -- >> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı >> >>> -Original Message- >>> From: sqlite-users >[mailto:sqlite-users-boun...@mailinglists.sqlite.org] >>> On Behalf Of Simon Slavin >>> Sent: Monday, 3 July, 2017 04:49 >>> To: SQLite mailing list >>> Subject: Re: [sqlite] FOREING KEY constraint >>> >>> >>> >>> On 3 Jul 2017, at 4:37am, Keith Medcalf wrote: >>> >>> > What do you mean "check for uniqueness? >>> >>> If you give two constraints the same name, does SQLite >>> >>> A) Ignore the problem >>> B) Reject the second one complaining "duplicate name" >>> C) Replace the first one with the second >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my Android device with K-9 Mail. Please excuse my brevity. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
Hi, Keith et al, On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf wrote: > > From what I can tell the answer is (A). The constraint_name is simply a > comment to be reported (if possible) when the constraint is violated. So is it possible to check that the foreign key with the given name already present for a given table? Thank you. > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Simon Slavin >> Sent: Monday, 3 July, 2017 04:49 >> To: SQLite mailing list >> Subject: Re: [sqlite] FOREING KEY constraint >> >> >> >> On 3 Jul 2017, at 4:37am, Keith Medcalf wrote: >> >> > What do you mean "check for uniqueness? >> >> If you give two constraints the same name, does SQLite >> >> A) Ignore the problem >> B) Reject the second one complaining "duplicate name" >> C) Replace the first one with the second >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
From what I can tell the answer is (A). The constraint_name is simply a comment to be reported (if possible) when the constraint is violated. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Monday, 3 July, 2017 04:49 > To: SQLite mailing list > Subject: Re: [sqlite] FOREING KEY constraint > > > > On 3 Jul 2017, at 4:37am, Keith Medcalf wrote: > > > What do you mean "check for uniqueness? > > If you give two constraints the same name, does SQLite > > A) Ignore the problem > B) Reject the second one complaining "duplicate name" > C) Replace the first one with the second > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] documentation: typo in foreign keys section
https://sqlite.org/foreignkeys.html#fk_indexes There is a typo under Foreign Keys Section 3. Required and Suggested Database Indexes: "Foreign key DML errors are may be reported if:" Thank you for the excellent documentation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOREING KEY constraint
On 3 Jul 2017, at 4:37am, Keith Medcalf wrote: > What do you mean "check for uniqueness? If you give two constraints the same name, does SQLite A) Ignore the problem B) Reject the second one complaining "duplicate name" C) Replace the first one with the second Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
