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 kind of indexed lookup.
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.
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
sqlite-users@maili
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 AUTOINCREMENT?
>
> I
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
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
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
> ___
> sqlite-u
0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 corrupting a database
On 3/16/18, Deon Brewis wrote:
> I am trying to write unit tes
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 trigge
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
__
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
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 t
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
> inserts/updates wi
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'
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 Tabl
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 && defined(HAVE_ST
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 I'm open to other id
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 - so 0 .
>
> I have to c
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
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 larg
> 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 patients with
> elevate
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 AUTOINCREMENT?
>
> I
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
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 results of which will h
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 schema. You might com
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' |
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:
* Wh
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
betwee
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 exist.
>> …
>
> Hi, yes, I thoug
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
that
> 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:
>>
>> if(record-exists?){
>> UPDATE …
>> }
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 option is to maintain a derived table,
> > the ta
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 write queries that manually retri
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:
>
> if(record-exists?){
> UPDATE …
> } else
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 [mailto:sqlite-users-boun.
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 Boo
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.
Simon.
_
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 t
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 trig
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
41 matches
Mail list logo