Re: [sqlite] Unexplained table bloat

On Fri, Jan 10, 2020 at 7:03 PM Richard Hipp wrote: > On 1/10/20, Dominique Devienne wrote: > > There's no way at all, to know the length of a text column with embedded > > NULLs? > > You can find the true length of a string in bytes from C-code using > the sqlite3_column_bytes() interface. Bu

Re: [sqlite] Unexplained table bloat

On Monday, 13 January, 2020 02:27, Dominique Devienne wrote: >On Fri, Jan 10, 2020 at 7:03 PM Richard Hipp wrote: >> On 1/10/20, Dominique Devienne wrote: >>> There's no way at all, to know the length of a text column with >>> embedded NULLs? >> You can find the true length of a string in

Re: [sqlite] Unexplained table bloat

On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote: > On Monday, 13 January, 2020 02:27, Dominique Devienne > wrote: > >> I'd vote for a lengthof(col) that's always O(1) for both text and blob > > So what should lengthof(something) return the number of bytes in the > 'database encoding' or so

Re: [sqlite] Unexplained table bloat

On 2020/01/13 12:24 PM, Dominique Devienne wrote: On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote: On Monday, 13 January, 2020 02:27, Dominique Devienne wrote: I'd vote for a lengthof(col) that's always O(1) for both text and blob So what should lengthof(something) return the number o

Re: [sqlite] Unexplained table bloat

On 2020/01/13 12:24 PM, Dominique Devienne wrote: Bytes of course. Of the data stored, i.e. excluding the header byte I checked, I was apparently correct about the casting. This following extract from a DB I got from a forum member with Greek-to-Danish translations where I added length checks

Re: [sqlite] Unexplained table bloat

If the register object contains "text" and you cast it to a blob (remove the text affinity) you are left with just the bag-o-bytes, and length() will return the size of the bag encoded in the register. If the data in the register is other than type "text" then it must be converted to text firs

Re: [sqlite] Unexplained table bloat

On 1/13/20 5:24 AM, Dominique Devienne wrote: On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote: On Monday, 13 January, 2020 02:27, Dominique Devienne wrote: I'd vote for a lengthof(col) that's always O(1) for both text and blob So what should lengthof(something) return the number of byt

Re: [sqlite] Unexplained table bloat

On Mon, Jan 13, 2020 at 1:10 PM Keith Medcalf wrote: > If the register object contains "text" and you cast it to a blob (remove the > text affinity) you are left with just the bag-o-bytes, and length() will > return the size of the bag encoded in the register. If the data in the > register is

[sqlite] Net provider not found

Hello   With the new version (112) I get this error. With 111 it works fine.   Der angeforderte .Net Framework-Datenprovider kann nicht gefunden werden. Er ist ggf. nicht installiert.   What is wrong? Until now it worked.   my packages.config is                                

Re: [sqlite] Unexplained table bloat

On 13 Jan 2020, at 9:26am, Dominique Devienne wrote: > Which implies length(text_val) is O(N), while > length(blob_val) is O(1), > something I never quite realized. For this reason, and others discussed downthread, some languages which store Unicode strings store the number of graphemes as well

[sqlite] More job postings mention SQL than anything else

If you want to justify the hours you spend fiddling with SQLite you should feel a little happier today. Graph from Burning Glass Analytics comparing demand for various buzzwords in job postings. SQL is mentioned >10% m

[sqlite] Bug fixes only branch.

We are at 3.14.2 Current version = 3.14.2 Date : 2016-09-12 https://www.sqlite.org/changes.html how can i take latest stable branch which include only bug fixes . no new features. Is there any way? ___ sqlite-users mailing list sqlite-users@mailinglis

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

> On Jan 12, 2020, at 4:12 PM, James K. Lowden wrote: > > What is the motivation behind this advice? It's completely unnecessary. Thanks for your opinion, James! I disagree. RFC822 headers are schemaless, and in a Usenet or email database they have rather high volume (probably 20+ per me

Re: [sqlite] Unexplained table bloat

On the original topic... How does one end up with a database in this state? I.e with a binary value that contains 0x00 bytes followed by other bytes but a type of TEXT? If the definition of a text string in SQLite is that it ends at the first 0x00 byte, then it seems that anything stored as a t

Re: [sqlite] Unexplained table bloat

Well, I believe this is the relevant bit from the docs for binding: https://www.sqlite.org/c3ref/bind_blob.html "If a non-negative fourth parameter is provided to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter must be the byte offset where the NUL termi

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

> On Jan 13, 2020, at 19:37, Jens Alfke wrote: > >> What is the motivation behind this advice? It's completely unnecessary. > > Thanks for your opinion, James! I disagree. Arnt Gulbrandsen, of Archiveopteryx fame, would disagree with you, Jens :) https://archiveopteryx.org/schema In any

[sqlite] System.Data.SQLite Verson 111

Hello   Can I download somewhere System.Data.SQLite Version 111 (the older version). I should generete a edmx of an existing database.   Thanks Urs ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/

[sqlite] Capturing the changes in columns in a table

Greetings! Please observe the following, create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11'); insert into t (a, b, c,

Re: [sqlite] Unexplained table bloat

On Monday, 13 January, 2020 06:36, Dominique Devienne wrote: > Please remind me, is the encoding a "client-side" setting, or also a > "server-side" (i.e. stored) setting? "pragma encoding " sets the internal storage format for text encoding in a database (on the "inside SQLite" side of the AP

[sqlite] lemon - namespace support

I've fairly recently been using lemon for building several parsers in C++ and found what I believe to be a *minimalist* means of avoiding symbol clashes without adding bloat, or affecting C code generation. - New '-e' command line option to define the code extension. By default this is 'c', but

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

I’d like to raise this issue again and give my support for what Maks Verver recommended in  https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg110107.html Independently I came to this bug while working on an issue in Simplenote’s Android app where our data was being corrupted

Re: [sqlite] Unexplained table bloat

On Sun, 12 Jan 2020 15:48:05 -0700 "Keith Medcalf" wrote: > SQL is simply a Structured Query Language that can be overlaid on a > variety of datastore models, one of which is the Relational model. I think that's misleading, Keith, for a couple of reasons. SQL was invented by IBM expressly an

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

On Mon, 13 Jan 2020 10:37:57 -0800 Jens Alfke wrote: > > On Jan 12, 2020, at 4:12 PM, James K. Lowden > > wrote: > > > > What is the motivation behind this advice? It's completely > > unnecessary. > > Thanks for your opinion, James! I disagree. > ... > IMHO there are too many of them to d

Re: [sqlite] Unexplained table bloat

On Sun, 12 Jan 2020 16:24:53 -0700 "Keith Medcalf" wrote: > while one may be tempted to claim that "consistency is the hobgoblin > of little minds" You might have forgotten that the much overpraised Ralph Waldo specified "a foolish consistency". He only meant: don't try to hang your hat on too

Re: [sqlite] Bug fixes only branch.

Hi, Syed, === On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad wrote: > We are at 3.14.2 Date : 2016-09-12 > > how can i take latest stable branch which include only bug fixes . no new > features. > > Is there any way? > == I may well not be

Re: [sqlite] Bug fixes only branch.

On Tue, 14 Jan 2020 at 7:00 am, Donald Griggs wrote: > Hi, Syed, > > === > On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad > wrote: > > > We are at 3.14.2 Date : 2016-09-12 > > > > how can i take latest stable branch which include only bug fixes . no new > > features. >

Re: [sqlite] Capturing the changes in columns in a table

On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote: Greetings! Please observe the following, create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11'); ... p001|1|10|column b changed on 2019-02-12 p002|2|4|

Re: [sqlite] Bug fixes only branch.

On Monday, 13 January, 2020 15:00, Donald Griggs wrote: >On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad >wrote: >> We are at 3.14.2 Date : 2016-09-12 >> how can i take latest stable branch which include only bug fixes . no >> new features. >> Is there any way? > I may well not be understand

Re: [sqlite] Capturing the changes in columns in a table

R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​ > On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:​ > > Greetings!​ > >​ > > Please observe the following,​ > >​ > >​ > > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);​ > >​ > > insert into t (a, b, c, d, e, idate) values ('

Re: [sqlite] Capturing the changes in columns in a table

On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote: R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​ ​ Wow! Thanks for this. I had not thought about your questions. My boss said, I need to know all the changes per project whenever it happened. So,... I will have to revise my th

Re: [sqlite] Capturing the changes in columns in a table

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2

Re: [sqlite] Capturing the changes in columns in a table

Note this only requires that "idate" be a unique orderable sequence within "a" in order to work. It does not have to be particular (such as a date/datetime). It can be a date, a datetime, an integer (as in unixtime), a real (as in julianday number), or any old sequence number and it will still

Re: [sqlite] Capturing the changes in columns in a table

Jose, I like Keith's version better using the Windowing functions assuming your version of SQLite is newer than 3.27 (or whenever Window functions were introduced, again my memory fails...) Most importantly, the CTE query /requires/ changes be day-on-day to be seen, which is the case in your e

Re: [sqlite] Bug fixes only branch.

On 1/13/20, Syed Ahmad wrote: > We are at 3.14.2 > > Current version = 3.14.2 Date : 2016-09-12 > > https://www.sqlite.org/changes.html > > how can i take latest stable branch which include only bug fixes . no new > features. > > Is there any way? We sometimes do things like that for paid support

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

> On Jan 13, 2020, at 1:45 PM, James K. Lowden wrote: > > So, basically, a nomalized design requires too much use of INSERT? > You're making an efficiency argument here, or maybe > ease-of-implementation assertion. For me, inserting one header row or > 20 is the same coding effort (still need

Re: [sqlite] Capturing the changes in columns in a table

And this version is several times faster since only the changes are union'd which minimizes the total number of records processed. The index should be "create index i on t (a, idate);" Because of the way indexes work, entries on the same a, idate will be ordered by n. (though really idate shou