Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

script: #!/bin/bash if [ ! -f test.db ] ; then sqlite3 test.db "CREATE VIRTUAL TABLE tab USING fts5(x)"; fi sqlite3 test.db ".import wikipedia tab" a=$(sqlite3 test.db "SELECT count(*) FROM tab_data") echo "# records after import: $a" sqlite3 test.db "DELETE FROM tab" a=$(sqlite3 test.db

Re: [sqlite] more efficient JSON encoding: idle musing

On Fri, Feb 21, 2020 at 6:03 AM Richard Hipp wrote: > On 2/21/20, Wout Mertens wrote: > > The idea is that upon storing the JSON > > data, the JSON1 extension parses it, extracts the layouts recursively, > > stores them when they are not known yet, and then only stores the > > values in the

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

This might be to do with how an FTS index works under the hood, involving various levels of "b-tree" that grow as entries are added, but aren't always shrunk when entries are deleted. There were a bunch of emails on the list around 4th to the 13th May 2014: sample below from Dan Kennedy (one of

[sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

Dear sqlite users, I noticed that when you delete lines from a FTS virtual table, somehow there is some data remaining in the sqlite db, so that's it does not shrink much in size. $ sqlite3 test.sql "CREATE VIRTUAL TABLE tab USING fts5(x)" $ curl -s https://www.wikipedia.org | tr -cd

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

Assuming no blocking issues, the release should be out by the first week of March at the latest. The changes have been made to permit the limits to be changed and the default attached database limit has been increased to 30. Sent from my iPhone > On Feb 24, 2020, at 9:34 AM, Keith Bertram

Re: [sqlite] Performance Issue on Large Table

I guess is this slowness is because of triggers (most probably) amplified by extra indexes. Lets discuss indexes first: if most of queries uses BibleID as SARG along with Book & Chapter then INX_Verses_BID & INX_Verses_BID_B_C are not required. These are unnecessary adding slowness to write

Re: [sqlite] Performance Issue on Large Table

Am 23.02.2020 um 20:23 schrieb Richard Damon: An amount of 140 tables in such a "BibleVersions.db" is not uncommon and can be managed by SQLite in a good performance. I'm not sure that form of division would be good. One basic rule of database normalization is that you don't break-up data

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

Hi Joe, Do you have an ETA on the new build? Also I wanted to confirm the change to the variable that would allow me to set the number of attachments to a number higher than 10. I would like to set it to 25. Keith -Original Message- From: sqlite-users On Behalf Of Joe Mistachkin

Re: [sqlite] Sql update script. check for existing rows before inserting...

Send-before-checking failure. :) Corrections to my previous mail: 1. "... is to check in my code if the table exists" must read: "... is to check in my code if the table is empty" 2. "...ON CONFLICT DO UPDATESET (Antenna..." must read: "...ON CONFLICT DO UPDATE  SET (Antenna..."

Re: [sqlite] Sql update script. check for existing rows before inserting...

On 2020/02/24 06:17, Andy KU7T wrote: Hi, I would like to write a script that checks whether certain records already exist, and if not, insert them. If they do exist, it should be a no op. I am trying this: IF (SELECT COUNT(*) FROM [Antennas]) = 0 BEGIN /* Table data [Antennas] Record

Re: [sqlite] [EXTERNAL] Sql update script. check for existing rows before inserting...

SQLite is not a procedural language. IF is not a programming construct, it is part of an expression. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Andy KU7T Gesendet: Montag, 24. Februar 2020 05:17 An: SQLite mailing

Re: [sqlite] Sql update script. check for existing rows before inserting...

IF is not an SQL statement. IF is a part of your host application programming language. It may also be part of a proprietary vendor specific extension to the SQL language to permit programmability such as the Sybase TRANSACT-SQL (licensed to Microsoft as Microsoft SQL Server to run on

[sqlite] Sql update script. check for existing rows before inserting...

Hi, I would like to write a script that checks whether certain records already exist, and if not, insert them. If they do exist, it should be a no op. I am trying this: IF (SELECT COUNT(*) FROM [Antennas]) = 0 BEGIN /* Table data [Antennas] Record count: 16 */ INSERT OR REPLACE INTO

[sqlite] Materialized views

Hello, I didn't find this question in the mailing list archive (although it's difficult to search). Is there any plan to support materialized views? By that I mean views whose result is stored permanently in a table and that is updated automatically every time the underlying tables are updated.

Re: [sqlite] Performance Issue on Large Table

On 2/23/20 3:06 PM, R.Smith wrote: On 2020/02/23 21:23, Richard Damon wrote: On 2/23/20 8:31 AM, Olaf Schmidt wrote: An amount of 140 tables in such a "BibleVersions.db" is not uncommon and can be managed by SQLite in a good performance. I'm not sure that form of division would be good.

Re: [sqlite] Performance Issue on Large Table

On 23 Feb 2020, at 7:23pm, Richard Damon wrote: > I'm not sure that form of division would be good. One basic rule of database > normalization is that you don't break-up data based on the value of one of > the fields because you can't make the table to lookup data from be taken from > a field

Re: [sqlite] Performance Issue on Large Table

On 2020/02/23 21:23, Richard Damon wrote: On 2/23/20 8:31 AM, Olaf Schmidt wrote: An amount of 140 tables in such a "BibleVersions.db" is not uncommon and can be managed by SQLite in a good performance. I'm not sure that form of division would be good. One basic rule of database

Re: [sqlite] Performance Issue on Large Table

Chip Beaulieu wrote: > I also recently vacuumed the file. The issue really showed up after the FTS5 > was > setup on the table. I suspect it’s got something to do with the triggers more > than > the indexes. I am definitely open to suggestions. I’ve not been able to find > much > info on

Re: [sqlite] Performance Issue on Large Table

On 2/23/20 8:31 AM, Olaf Schmidt wrote: Am 21.02.2020 um 02:24 schrieb Chip Beaulieu: I have a table with 4.5 million records with full text indexing.  > Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes

Re: [sqlite] Performance Issue on Large Table

Am 21.02.2020 um 02:24 schrieb Chip Beaulieu: I have a table with 4.5 million records with full text indexing. > Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes / inserts at a time. The last batch took

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

I do the exact same pragmas as Jens, and also in this order. I was even convinced that it was working, so I'll need to double check. The documentation might be correct if you know what to look for, but from current experience it's not obvious. Wout. On Sat., Feb. 22, 2020, 4:02 a.m. Keith

[sqlite] Consider Adding Plus Sign To Allowed Character List in althttpd

Dr. Hipp, I'm using althttpd to serve files from a personal Linux package repository. There are 38 instances of package names which contain at least one plus sign, currently one of the characters substituted with an underscore by althttpd. This results in a 404 file not found error and makes

Re: [sqlite] unexpected sqlite_busy behaviour within transactions

On Saturday, 22 February, 2020 09:26, Andy Bennett wrote: >This other process has called "BEGIN IMMEDIATE TRANSACTION" and >https://sqlite.org/rescode.html#busy says >"The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it >succeeds, then SQLite guarantees that no subsequent

Re: [sqlite] unexpected sqlite_busy behaviour within transactions

Hi, A busy_timout is honored by write commands outside of transactions and by single write commands inside transactions, but not if preceded by a read command. I did not find this behaviour in the documentation, thus it might be a bug. It's documented in the description of

Re: [sqlite] unexpected sqlite_busy behaviour within transactions

On 2/22/2020 7:50 AM, softw...@quantentunnel.de wrote: A busy_timout is honored by write commands outside of transactions and by single write commands inside transactions, but not if preceded by a read command. I did not find this behaviour in the documentation, thus it might be a bug. It's

[sqlite] unexpected sqlite_busy behaviour within transactions

Hi A busy_timout is honored by write commands outside of transactions and by single write commands inside transactions, but not if preceded by a read command. I did not find this behaviour in the documentation, thus it might be a bug. To reproduce (in a linux terminal): sqlite3 ~/test.db

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

On Friday, 21 February, 2020 19:36, Simon Slavin wrote: >On 22 Feb 2020, at 2:28am, Keith Medcalf wrote: >> When a database is to be created these commands must be given BEFORE >any command which opens or creates the database: >> >> pragma auto_vacuum >> pragma encoding >> pragma page_size >>

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

On 22 Feb 2020, at 2:28am, Keith Medcalf wrote: > When a database is to be created these commands must be given BEFORE any > command which opens or creates the database: > > pragma auto_vacuum > pragma encoding > pragma page_size > pragma data_store_directory > > The issuance (or preparation)

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

There are three pragma's which affect the "organization" of a newly created database. When a database is to be created these commands must be given BEFORE any command which opens or creates the database: pragma auto_vacuum pragma encoding pragma page_size pragma data_store_directory The

[sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

I just discovered that none of our databases are auto-vacuuming, even though we set auto_vacuum to 'incremental' when a database is created, and periodically call 'pragma incremental_vacuum'. If I go into the CLI, open a database and run "pragma auto_vacuum", it returns 0. After some

[sqlite] Record serialization infrastructure of SQLITE

Inspired by the json encoding thread. We have a need for multi-dimensional data (like json), and currently use a record format for that that is derived from the SQLITE format with Serial Type/Size stored in varint Huffman encoding of twos-complement. It is a great fast, compact storage format.

Re: [sqlite] more efficient JSON encoding: idle musing

On 21.02.2020 15:03 Richard Hipp wrote: > On 2/21/20, Wout Mertens wrote: >> The idea is that upon storing the JSON >> data, the JSON1 extension parses it, extracts the layouts recursively, >> stores them when they are not known yet, and then only stores the >> values in the binary format with

Re: [sqlite] more efficient JSON encoding: idle musing

> On Feb 21, 2020, at 4:20 AM, Wout Mertens wrote: > > I was wondering if the JSON extension could not do the same thing: for each > table, keep a hidden stash of object layouts, and store the values as > sqlite primitives. (you'd be able to disable this, in case the layouts > rarely repeat)

Re: [sqlite] more efficient JSON encoding: idle musing

> On Feb 21, 2020, at 4:20 AM, Wout Mertens wrote: > > In JavaScript, objects are key-value collections with unique keys, where the > order of the keys is important. JSON is not JavaScript. The order of keys is NOT significant in JSON, and many, many JSON implementations parse JSON objects

Re: [sqlite] Performance Issue on Large Table

> On Feb 20, 2020, at 5:24 PM, Chip Beaulieu wrote: > > CREATE INDEX `INX_Verses_BID` ON `Verses` (`BibleID`) > CREATE INDEX `INX_Verses_BID_B_C` ON `Verses` (`BibleID`,`Book`,`Chapter`) > CREATE INDEX `INX_Verses_BI_B_C_V` ON `Verses` > (`BibleID`,`Book`,`Chapter`,`VerseNumber`) I believe

Re: [sqlite] more efficient JSON encoding: idle musing

If you have example code for a mechanism that is more space efficient and/or faster, please share it with us. "Bencode" is approximately the same space-wise as JSON, but encoding/decoding is potentially faster since it doesn't have to do any escaping for strings:

Re: [sqlite] more efficient JSON encoding: idle musing

On Fri, Feb 21, 2020 at 3:03 PM Richard Hipp wrote: > If you > have example code for a mechanism that is more space efficient and/or > faster, please share it with us. I'll see if I can prototype something in JS - I'd be keeping the layouts in a helper table, and I wouldn't be storing the values

Re: [sqlite] more efficient JSON encoding: idle musing

On 2/21/20, Wout Mertens wrote: > The idea is that upon storing the JSON > data, the JSON1 extension parses it, extracts the layouts recursively, > stores them when they are not known yet, and then only stores the > values in the binary format with the layout identifiers. I experimented with a

Re: [sqlite] more efficient JSON encoding: idle musing

On Fri, Feb 21, 2020 at 2:37 PM Warren Young wrote: > > On Feb 21, 2020, at 5:20 AM, Wout Mertens wrote: > > Queries can go faster, because a query like `where json_extract(json, > > '$.foo') = 'bar'` can first check the layouts to see which ones apply, > > SQLite’s JSON1 extension is a storage

Re: [sqlite] more efficient JSON encoding: idle musing

On Feb 21, 2020, at 5:20 AM, Wout Mertens wrote: > > In JavaScript, objects are key-value collections with unique keys, where > the order of the keys is important. ECMAScript §13.7.5.15 (2019 edition) says, "The mechanics and order of enumerating the properties is not specified but must

Re: [sqlite] Performance Issue on Large Table

On 21-2-2020 02:24, Chip Beaulieu wrote: I have a table with 4.5 million records with full text indexing. Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes / inserts at a time. The last batch took 10

[sqlite] more efficient JSON encoding: idle musing

Hi, I use SQLite as a MaybeSQL store, mixing fixed columns with schemaless JSON columns. It's really great. In JavaScript, objects are key-value collections with unique keys, where the order of the keys is important. Most JSVMs store them as a pointer to a layout and then the values. The layout

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On 2/21/20, Andy KU7T wrote: > Are you saying the PRNG on Windows is not good enough to use > randomblob(16) in Sqlite? All I need is a reasonable assurance that is are > unique... The default PRNG on Windows is fine for generating globally unique identifiers. The complaint is that the seeding

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On Thursday, 20 February, 2020 22:06, Andy KU7T wrote: >I admit I do not fully understand all the arguments. I am running on >Windows. Are you saying the PRNG on Windows is not good enough to use >randomblob(16) in Sqlite? All I need is a reasonable assurance that is >are unique... Yes, it is

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

I admit I do not fully understand all the arguments. I am running on Windows. Are you saying the PRNG on Windows is not good enough to use randomblob(16) in Sqlite? All I need is a reasonable assurance that is are unique... Andy Sent from my T-Mobile 4G LTE Device Get Outlook for

[sqlite] Performance Issue on Large Table

I have a table with 4.5 million records with full text indexing. Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes / inserts at a time. The last batch took 10 hours to complete. Here are the details:

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On Fri, 21 Feb 2020 at 03:59, Jens Alfke wrote: > > On Feb 20, 2020, at 10:48 AM, Richard Hipp wrote: > > > > That assumption is not correct for SQLite, which does you a > > cryptographically strong PRNG. And the SQLite PRNG is seeded from > > /dev/random on unix. > > Not quite; I'm looking at

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

> On Feb 20, 2020, at 10:48 AM, Richard Hipp wrote: > > That assumption is not correct for SQLite, which does you a > cryptographically strong PRNG. And the SQLite PRNG is seeded from > /dev/random on unix. Not quite; I'm looking at the function unixRandomness() in SQLite 3.28. It's seeded

[sqlite] .dump

I noticed that the .dump command in the CLI doesn't contain the "user_version" and "application_id" fields. I don't know whether this is intentional, but would you consider including these values in the output of .dump? ___ sqlite-users mailing list

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On Thursday, 20 February, 2020 11:48, Richard Hipp wrote: >The author of that article, "Raymond", assumes that the random number >generator in the SQL database engine is not cryptographically strong. Actaully, what "Raymond" is on about is the fact that the original definition of a GUID,

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On 2/20/20, Keith Medcalf wrote: > > randomblob(16) does not generate a valid UUID (it does not set the version > and variant flags in the resulting 16-bytes of random data). If you need a UUID in the "standard format", rather than just an ID that its universally unique, you can use the uuid.c

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On 2/20/20, Andy KU7T wrote: > Hi, > I added a randomblob(16) to each record of a Sqlite table via a trigger with > the goal of global uniqueness. Is that the correct approach or would it be > better to pass Guid from .Net? I am using System.Data.Sqlite. The following > article got me questioning

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

randomblob(16) generates 16 random bytes. randomblob(16) does not generate a valid UUID (it does not set the version and variant flags in the resulting 16-bytes of random data). If you set the version to 4 and the variant to 1 then randomblob(16) does produce valid version 4 uuids with

[sqlite] Is randomblob(16) a good guid generation across multiple computers?

Hi, I added a randomblob(16) to each record of a Sqlite table via a trigger with the goal of global uniqueness. Is that the correct approach or would it be better to pass Guid from .Net? I am using System.Data.Sqlite. The following article got me questioning the usage of randomblob:

Re: [sqlite] DRH interview on why/how SQLite succeeded

In the ever expanding bloat of tooling, DRH is my hero. On Wed, Feb 19, 2020 at 2:56 PM Stephen Chrzanowski wrote: > I just finished listening to this. Really cool. > > Thanks for ALL of your hard work SQLite team. I appreciate it sincerely. > > > On Wed, Feb 19, 2020 at 12:39 PM Simon Slavin

Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

Ah, so rtrim(X,Y) removes all characters in the Y slot; NOT the string Y. Apologies. I thought that it was the string that it removed. Ok, replace it is, then. From: sqlite-users on behalf of Hick Gunter Sent: Thursday, February 20, 2020 11:09 AM To:

Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

Round(1299.6) returns the floating point number 1300.0, passing 1300.0 to the rtrim function converts it tot he string '1300.0' removing all '.' and '0' characters from '1300.0' yields 13 This is no suprise -Ursprüngliche Nachricht- Von: sqlite-users

[sqlite] rtrim and round functions unexpected result

Greetings. Please take a look at the following: sqlite> select rtrim(round(1235.6)); 1236.0 This is expected. sqlite> select rtrim(round(1235.6),'.0'); 1236 Also expected. sqlite> select rtrim(round(1299.6),'.0'); 13 is not expected. I was hoping for 1300. Also, just rtrim, sqlite> select

Re: [sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

On Wednesday, 19 February, 2020 21:24, ethan he wrote: >There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY >AUTOINCREMENT), >Is that possible to delete the data but still keep the MeslocallD >consistence? Assuming that by "consistence" you mean the high-water mark for inserted

Re: [sqlite] [EXTERNAL] question about INTEGER PRIMARY KEY AUTOINCREMENT

The next value for an INTEGER PRIMARY KEY AUTOINCREMENT does not depend on the current contents of the table, only its history. While ROWIDs are monotnically increasing, there may be gaps in the sequence, caused by rows that failed to insert due to constraint violations. However, ROWIDs that

[sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

Hi, There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY AUTOINCREMENT), Is that possible to delete the data but still keep the MeslocallD consistence? Thanks for your help ___ sqlite-users mailing list

Re: [sqlite] Why do I only get one record?

Igor Korot, on Wednesday, February 19, 2020 04:30 PM, wrote... > > Hi, > > On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera > wrote: > > > > > > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working > now. Thanks. > > > > > > > sqlite-users on

Re: [sqlite] Why do I only get one record?

Hi, On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera wrote: > > > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working now. > Thanks. > > > > From: sqlite-users on behalf > of Keith Medcalf > Sent: Wednesday, February 19, 2020 03:09 PM >

Re: [sqlite] Why do I only get one record?

Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working now. Thanks. From: sqlite-users on behalf of Keith Medcalf Sent: Wednesday, February 19, 2020 03:09 PM To: SQLite mailing list Subject: Re: [sqlite] Why do I only get one record? 1)

Re: [sqlite] Why do I only get one record?

1) In the first two query's, why do you have a condition on the LHS table in the LEFT JOIN conditions? 2) In the last query, why do you have a condition on the RHS table of the LEFT JOIN in the WHERE clause? These would seem to indicate that you are using a LEFT JOIN when you really do not

Re: [sqlite] DRH interview on why/how SQLite succeeded

I just finished listening to this. Really cool. Thanks for ALL of your hard work SQLite team. I appreciate it sincerely. On Wed, Feb 19, 2020 at 12:39 PM Simon Slavin wrote: > > > Podcast / transcription of DRH interview: > > " We talked to Richard about

[sqlite] Why do I only get one record?

Greetings! Newbie here again... ;-) Please take a look at this query, sqlite> SELECT ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date, ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier, ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC ...> FROM

Re: [sqlite] DRH interview on why/how SQLite succeeded

Don V Nielsen, on Wednesday, February 19, 2020 01:43 PM, wrote... > > DRH sounds so much more human in the podcast than the DRH I have > assembled > in my head from reading this mailing list. :) > He is human like us, just smarter than most of us. :-) josé

Re: [sqlite] DRH interview on why/how SQLite succeeded

DRH sounds so much more human in the podcast than the DRH I have assembled in my head from reading this mailing list. :) On Wed, Feb 19, 2020 at 11:39 AM Simon Slavin wrote: > > > Podcast / transcription of DRH interview: > > " We talked to Richard about the

[sqlite] DRH interview on why/how SQLite succeeded

Podcast / transcription of DRH interview: " We talked to Richard about the history of SQLite, where it came from, why it succeeded as a database, how its development is sustainably funded and also how it’s the most widely deployed database engine in the

Re: [sqlite] Does VACUUM imply REINDEX?

On 19 Feb 2020, at 4:36pm, Olivier Mascia wrote: > having an apparently sane database (pragma integrity_check and pragma > foreign_key_check successful at least), it would make sense to do : REINDEX, > ANALYZE and then VACUUM as part of final preparation step for archive (the DB > would be

Re: [sqlite] Does VACUUM imply REINDEX?

> Le 19 févr. 2020 à 17:26, Simon Slavin a écrit : > > On 19 Feb 2020, at 4:18pm, Richard Hipp wrote: > >> There is no benefit to running >> ANALYZE after running VACUUM if the ANALYZE data (the content of the >> sqlite_stat1 table) is still up-to-date. > > However, if you have a yearly

Re: [sqlite] Does VACUUM imply REINDEX?

On 19 Feb 2020, at 4:18pm, Richard Hipp wrote: > There is no benefit to running > ANALYZE after running VACUUM if the ANALYZE data (the content of the > sqlite_stat1 table) is still up-to-date. However, if you have a yearly maintenance procedure, it might make sense to include ANALYZE in it.

Re: [sqlite] Does VACUUM imply REINDEX?

On 2/19/20, Olivier Mascia wrote: > Hello, > > As part of a rare database "maintenance"... > Does VACUUM, in essence, implies whatever actions REINDEX would do? > Would REINDEX before VACUUM make any more sense? > > And what about ANALYZE? Would it be wise or useless, to run it after VACUUM?

[sqlite] Does VACUUM imply REINDEX?

Hello, As part of a rare database "maintenance"... Does VACUUM, in essence, implies whatever actions REINDEX would do? Would REINDEX before VACUUM make any more sense? And what about ANALYZE? Would it be wise or useless, to run it after VACUUM? So... Would "VACUUM; REINDEX; ANALYZE;" make any

Re: [sqlite] last_insert_rowid() returns every growing list

Thanks, that explains what I saw. What I was trying to accomplish was retrieve the autoincrement key for the row that had just been previously inserted. I missed the "last_insert_rowid" method in the docs for the Tcl Sqlite interface -- it's only 3 lines :-)

Re: [sqlite] last_insert_rowid() returns every growing list

On 2/17/20, The Tick wrote: > sql eval { insert into test (id, number, data) values( $a, $b, $c ) } What were you hoping to accomplish here? It seems like you might be wanting the rowid of the last insert by any database connection into the "test" table. If so, that is not what

Re: [sqlite] last_insert_rowid() returns every growing list

On Monday, 17 February, 2020 17:20, The Tick wrote: >I'm running tcl 8.6.8 on win7x64. I built the latest sqlite Tcl package >with the 3310100 source using mingw gcc under msys2. >Everything seems to work but I ran into a strange result with >last_insert_rowid(). >The following example

[sqlite] last_insert_rowid() returns every growing list

I'm running tcl 8.6.8 on win7x64. I built the latest sqlite Tcl package with the 3310100 source using mingw gcc under msys2. Everything seems to work but I ran into a strange result with last_insert_rowid(). The following example returns an ever-growing list of rowid's: -8x- package

Re: [sqlite] O_NOFOLLOW issue with /dev/null on Solaris

Thanks for the fix, I just verified that our issue is gone. We will keep the fact that this should not happen in the first place in mind. Jakub On 2/13/20 2:50 PM, Richard Hipp wrote: On 2/13/20, jakub.ku...@oracle.com wrote: Recently, O_NOFOLLOW was added to several calls into

Re: [sqlite] How to compile sqlite with gcov support?

When I deleted the --enable-debug flag, the .gcno file can be generated. Not sure why the --enable-debug flag will influence gcov compilation. On Tue, Feb 18, 2020 at 3:16 PM Xingwei Lin wrote: > Hi, > > I decompressed the sqlite-autoconf-3310100.tar.gz and compiled it with the > following

[sqlite] How to compile sqlite with gcov support?

Hi, I decompressed the sqlite-autoconf-3310100.tar.gz and compiled it with the following commands: ``` cd sqlite-autoconf-3310100 CFLAGS="-g -ftest-coverage -fprofile-arcs" ./configure --enable-fts3 --enable-debug --enable-session make -j$(nproc) ``` But when I completed the building process, I

[sqlite] alter table

I'd just like to kindly ask whether there are any new plans for a full ALTER TABLE support? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

'rowid' is a common name and could be in anyone's schema regardless of table type. On Mon, Feb 17, 2020 at 2:35 AM x wrote: > Re my earlier post (which didn’t go out to mailing list) > > > sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK > returns false if table

Re: [sqlite] WITHOUT ROWID tables

Re my earlier post (which didn’t go out to mailing list) sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK returns false if table tblName is a without rowid table seems to work for me. Sent from Mail for Windows 10

[sqlite] SQLITE_HAS_CODEC

Have just seen (in https://www.sqlite.org/src/timeline), that this compiletime-option was removed (2020-02-07). Speaking as a wrapper-author... Are there alternatives in sight, to further support: - a "ReKey"-feature in a relatively compatible manner, without breaking existing (user-)code,

Re: [sqlite] WITHOUT ROWID tables

If you have control over the schema you can put a schema version in some table. On Sat, Feb 15, 2020, 13:21 J. King wrote: > On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz > wrote: > >Wouldn't be something like > > > >SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table' >

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On Sonntag, 16. Februar 2020 21:50:18 CET Simon Slavin wrote: > On 16 Feb 2020, at 8:44pm, Stefan Brüns wrote: > > On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote: > >>> One use case I am aware of (although this targets places.sqlite, not > >>> cookies.sqlite) is reading the

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On 16 Feb 2020, at 8:44pm, Stefan Brüns wrote: > On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote: > >>> One use case I am aware of (although this targets places.sqlite, not >>> cookies.sqlite) is reading the history, bookmarks and tags. >> >> These things can be done using the

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote: > > One use case I am aware of (although this targets places.sqlite, not > > cookies.sqlite) is reading the history, bookmarks and tags. > These things can be done using the bookmarks API, WebExtensions API, and > other methods.

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On Sunday, 16 February, 2020 10:25, Richard Hipp wrote: >> Why the database can not be read by another sqlite3 session when the >> corresponding -wal file exists? Thanks. >Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;" Perhaps on some platforms, but Firefox 73.0.0 on Windows 10

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On 16 Feb 2020, at 8:02pm, Stefan Brüns wrote: > Will this stop anyone from just copying the DB without the -wal file? > Afterwards, the DB can be read, as there is no longer any associated log. The purpose of the locking is to prevent changes being made to the database during a browser

Re: [sqlite] Feature request: more robust handling of invalid UTF-16 data

*Richard:* the issue with the JSON extension seems unrelated to the issue that I reported originally, which relates to the SQLite C API (specifically, the sqlite3_bind_text16() and sqlite3_bind_text16() functions). My issue is still not fixed. I've expanded my original sample code to make it

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On Sonntag, 16. Februar 2020 18:36:15 CET Simon Slavin wrote: > On 16 Feb 2020, at 5:15pm, Peng Yu wrote: > > Why the database can not be read by another sqlite3 session when the > > corresponding -wal file exists? Thanks. > > This is done on purpose by the developers of Firefox to prevent a

Re: [sqlite] WITHOUT ROWID tables

On 16 Feb 2020, at 6:00pm, Bernardo Ramos wrote: > I've got no rows for both with and without rowid tables: " as of SQLite version 3.30.0 on 2019-10-04 " ___ sqlite-users mailing list

Re: [sqlite] WITHOUT ROWID tables

I've got no rows for both with and without rowid tables: SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> create table t1(name); sqlite> create table t2(name primary key, phone) without rowid; sqlite> pragma index_info(t1); sqlite> pragma index_info(t2); sqlite>

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On 16 Feb 2020, at 5:15pm, Peng Yu wrote: > Why the database can not be read by another sqlite3 session when the > corresponding -wal file exists? Thanks. This is done on purpose by the developers of Firefox to prevent a security vulnerability which I will not describe in public. One of the

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On 2/16/20, Peng Yu wrote: >> Does it work when you close Firefox? If it works when Firefox is closed >> but >> not when Firefox is open, then the answer is probably no. > > I can check the content when Firefox is closed (the -wal file > disappears after Firefox is closed). > > Why the database

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

> Does it work when you close Firefox? If it works when Firefox is closed but > not when Firefox is open, then the answer is probably no. I can check the content when Firefox is closed (the -wal file disappears after Firefox is closed). Why the database can not be read by another sqlite3

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

On Saturday, 15 February, 2020 19:27, Peng Yu wrote: >I am trying to see what tables are available in sqlite_master from >firefox cookies sqlite3 fire. >~/Library/Application >Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite >But the error message says "Error: database

<    1   2   3   4   5   6   7   8   9   10   >