Re: [sqlite] [EXTERNAL] Boolean casting

Since there is no BOOLEAN dataype in SQLite, and BOOLEAN is assigned NUMERIC affinity, the sqlite3_column_type() function must be returning SQLITE_INTEGER (because storing 0 and 1 as integers is preferred over floats). So how does the getColumnType() function determine a datatype of BOOLEAN? My

[sqlite] UPSERT

Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since it doesn’t has an UPSERT? So, if I have a table with 30 columns and my code updates sub-sets out of these columns, I don’t want to write queries that manually retrieve the old values one by one. insert or replace into

Re: [sqlite] [EXTERNAL] UPSERT

No. Insert or replace will delete any and all records that violate constraints, and then insert a new record. This may cause ON DELETE CASCADE foreign key constraints to fire. Do it in code as you suggested yourself. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] UPSERT

On 16 Mar 2018, at 10:24am, Robert M. Münch wrote: > for every column I don’t want to change I need to add a sub-select statement. > If I need to build this statement dynamically, IMO it would be better to > handle this code directly in code: > >

Re: [sqlite] UPSERT

On 16 March 2018 at 18:24, Robert M. Münch wrote: > Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since > it doesn’t has an UPSERT? > > So, if I have a table with 30 columns and my code updates sub-sets out of > these columns, I don’t want to

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

True, I mean in case there were multiple dimensions. I should write down hidden thinking processes more. On Fri, Mar 16, 2018 at 9:58 AM Simon Slavin wrote: > On 16 Mar 2018, at 8:38am, Wout Mertens wrote: > > > It seems to me that the only real

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

I have the same problem but luckily O(n) performance is fast enough for me right now. It seems to me that the only real option is to maintain a derived table, the table could even be R*TREE to allow range queries. Triggers seem to be the accepted way to derive tables, but I'm a little afraid of

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

So in your application you don't care about when a temperature was measured? Creating a table temps (patient_id, timestamp, temperature) with an index on (timestamp,temperature) would yield a fast way to access patients with elevated temperatures within a time frame. Other than that, using

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

On 16 Mar 2018, at 8:38am, Wout Mertens wrote: > It seems to me that the only real option is to maintain a derived table, > the table could even be R*TREE to allow range queries. You can always do range queries on tables in SQLite. Not sure what you mean by the above.

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

Typically none, though in very rare cases there may be one or two out of a dozen or so tables. Generally speaking, I have found no particular advantage in most circumstances to having "integer primary key" with the "AUTOINCREMENT" property (that is, guaranteed ascending unique assignment

Re: [sqlite] Programmatically corrupting a database

On 3/16/18, Deon Brewis wrote: > I am trying to write unit tests for some error detection & recovery > scenarios and would like to simulate the following type of corruptions: > > 1) Header corruption (easy) > 2) Corrupting a specific SQL Table/Index so that it can be read, but

[sqlite] How many AUTOINCREMENT tables are in your schema?

This is a survey, the results of which will help us to make SQLite faster. How many tables in your schema(s) use AUTOINCREMENT? I just need a single integer, the count of uses of the AUTOINCREMENT in your overall schema. You might compute this using: sqlite3 yourfile.db '.schema --indent' |

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 In all of the few dozens of schemas. Rowid logic is almost perfect. In extremely rare cases, when primary key id is exposed outside of database, a custom table that keeps last allocated id is used. 16 March 2018, 17:37:31, by "Richard Hipp" : This is a survey, the

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

Pretty much every table of every database, with the obvious exceptions like virtual tables. -j > On Mar 16, 2018, at 10:37 AM, Richard Hipp wrote: > > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

> On Mar 16, 2018, at 1:24 AM, Hick Gunter wrote: > > So in your application you don't care about when a temperature was measured? > Creating a table temps (patient_id, timestamp, temperature) with an index on > (timestamp,temperature) would yield a fast way to access

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

On 3/16/18, Jens Alfke wrote: > Yes, the solution involves creating a table as you described, but the table > is derived from and dependent on the original JSON data, and needs to be > kept up to date as that data changes. Triggers are the best way I know of to > do that, but

[sqlite] Programmatically corrupting a database

I am trying to write unit tests for some error detection & recovery scenarios and would like to simulate the following type of corruptions: 1) Header corruption (easy) 2) Corrupting a specific SQL Table/Index so that it can be read, but inserts/updates will fail 3) Corrupting a specific SQL

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 On Mar 16, 2018 9:37 AM, "Richard Hipp" wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the AUTOINCREMENT > in your overall

Re: [sqlite] Daily test coverage results published online

Hi Richard, thanks for the feedback. And sorry about the late reply: On Wed, 7 Mar 2018, Richard Hipp wrote: as of earlier this week you'll find the daily code coverage results of the tests bundled with SQLite online at: http://www.opencoverage.net/sqlite With over 89% condition coverage

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0, across approx 20 databases, ranging from small 3 table schemas, to a couple of ERP systems using 120+ tables. Thanks, Chris On Fri, Mar 16, 2018 at 4:09 PM, R Smith wrote: > Across 8 production systems and about 120 SQLite DBs for us - Not a single > AUTOINCREMENT -

[sqlite] Missing/undocumented HAVE_STRERROR_R

I'm debugging a problem that involves some errors logged by SQLite via unixLogErrorAtLine(). The messages contain an errno value but not the associated message. The message is missing because the call to strerror_r() is #ifdef'd out by this line: #if SQLITE_THREADSAFE &&

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

Across 8 production systems and about 120 SQLite DBs for us - Not a single AUTOINCREMENT - so   0 . I have to confess though, there are less critical places where we use the ability of SQLite to insert and automatically incremented INT primary keys (so Non-AUTOINCREMENT keys), in case that is

[sqlite] Bug on update in extension zipfile

Hi, i found a problem in duplicating files in zipfile extension Code: SELECT load_extension("sqlite3_mod_zipfile"); CREATE VIRTUAL TABLE temp.zip USING zipfile('zipfile.zip'); DELETE FROM temp.zip; INSERT INTO temp.zip (name,data) VALUES ('test','test'); UPDATE  temp.zip set name=name||'new'

Re: [sqlite] UPSERT

A few years back I've been asking the same question. To be honest, there's no more efficient alternative, than the one that can be implemented within library itself. Both performance-wise and productivity-wise. Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with problems: *

Re: [sqlite] UPSERT

On 16 Mar 2018, at 11:35, Simon Slavin wrote: > You can use INSERT OR IGNORE for this. First, do an INSERT OR IGNORE command > which will add a dummy entry, with the right key, if one doesn't already > exist. > … Hi, yes, I thought about this too but really don’t like it and this assumes

Re: [sqlite] UPSERT

On 16 Mar 2018, at 12:43pm, Robert M. Münch wrote: > On 16 Mar 2018, at 11:35, Simon Slavin wrote: > >> You can use INSERT OR IGNORE for this. First, do an INSERT OR IGNORE >> command which will add a dummy entry, with the right key, if one doesn't >> already

Re: [sqlite] UPSERT

On 16 Mar 2018, at 11:41, Rowan Worth wrote: > Doing it with an if means you always run two queries - the first to > determine whether a row exists: > > SELECT EXISTS (SELECT ID from Book where Name = "SearchName") Hi, yes that’s true. Not critical in my case but if you have a slow FFI in

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Thank you for the information, Keith. It comes as a surprise to me that the conflict resolution clause of the statement that causes a trigger to fire can override an explicit conflict resolution clause in the body of a trigger. But, as you pointed out, it is documented. How can I write the

Re: [sqlite] Site error

Getting same error message trying to search today. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Friday, March 09, 2018 7:47 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Site error It seems to me

Re: [sqlite] Programmatically corrupting a database

Works like a charm. Thanks! - Deon -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Friday, March 16, 2018 10:39 AM To: SQLite mailing list Subject: Re: [sqlite] Programmatically

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

create trigger users_after_insert after insert on users when not exists ( select 1 from user_extras where user_id = new.id ) begin insert into user_extras (user_id) values (new.id); end; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 When needed I use a declared INTEGER PRIMARY KEY. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Site error

On 3/16/18, David Raymond wrote: > Getting same error message trying to search today. Fixed. Apparently one of the automatic site-update scripts is messing up the file permissions on the full-text search databases... -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

100s - we use it as part of the definition of some dynamically created tables that are dependent on the shape of the data we are receiving so can end up with a large number of them. On Sat, 17 Mar 2018 at 5:57 am, Doug Currie wrote: > 0 >

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

It is kind of hard to write a specific trigger since your "purpose" is confusing. You have defined some referential integrity and are then ignoring it. If your goal is to update the parent, then why not use the statement designed to do that (UPDATE) ... ? --- The fact that there's a Highway

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

It's interesting to fathom what hypothesis is being tested with this pole... But it seems (from the on-forum replies anyway) that there are two very clear AUTOINCREMENT factions: Those who use it Everywhere, and those who use it Nowhere - which already is somewhat surprising to me.

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 AUTOINCREMENT columns. A per column overload-able nextValue() interface could have its uses though. On Fri, Mar 16, 2018 at 8:37 AM, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On Friday, 16 Mar 2018 11:37 AM -0400, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? 0 -- Will ___ sqlite-users mailing list

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On 3/16/18, R Smith wrote: > It's interesting to fathom what hypothesis is being tested with this pole... INSERT operations on a table with AUTOINCREMENT do a full-table scan against the sqlite_sequence table. I'm wondering if it is worthing adding extra logic to do some

Re: [sqlite] UPSERT

> Le 16 mars 2018 à 11:35, Simon Slavin a écrit : > >> for every column I don’t want to change I need to add a sub-select >> statement. If I need to build this statement dynamically, IMO it would be >> better to handle this code directly in code: >> >>