A proper UPSERT command would be very useful. Here is the simple ON DELETE
example showing the problem using the single REPLACE command, even with
deferred foreign keys.
CREATE TABLE a(
id_a INTEGER PRIMARY KEY AUTOINCREMENT,
data_a);
CREATE TABLE b(
id_b INTEGER PRIMARY KEY AUTOINCREMENT,
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
SQLite 3.10 changed the xCurrentTime (note not -64 version) method for
the "unix" VFS from a function to a NULL pointer. This has broken
things for me, because I have a VFS that calls back into the "unix"
vfs. A SQLite shared library upgrade will
On 2016-01-20 18:14, Yuri wrote:
> Does SQLite optimizer look at the row counts? (I think it does because
> the plans are different when there are no rows present).
> How to correct the problem?
If you want to do a manual optimization, the following describes how to
set the order of joins
Have you tried running ANALYZE on a representative dataset? This will determine
the "shape" of your tables and allow the query planner to make better guesses
of the costs associated with each join.
If, after ANALYZE, you still feel the need to improve over the query planners'
ordering, you can
What is the reason to pay extra overhead if it can be omitted?
And actually, SQLite does a great job with concurrency, from my experience.
There's really smart locking model employed.
And this is really tedious to work around:
1) You cannot disable FK constraints inside a transaction
2)
Just to let you know that I have this all sorted now and that the problem
was the fact that
sqlite3.dll (the Windows dll) is compiled with the cdecl convention, which
is a problem with
callbacks back to a VB6 ActiveX dll as that expects std_call convention.
Once I compiled sqlite3.dll with
hello,
.read name.sql has a bug for mac os x 10.10 for the latest edition of SQLITE on
Jan 20th
I can confirm that I have created table C.
INPUT
I think you could also just defer foreign key checking to the end of the
transaction.
On 2016/01/20 12:20 PM, chromedout64 at yahoo.com wrote:
> The problem with REPLACE is: " the REPLACE algorithm deletes pre-existing
> rows that are causing the constraint violation prior to inserting or
https://www.sqlite.org/lang_replace.html
Time for new compatibility keyword UPSERT?
cheers,
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Benoit
Mortgat
Sent: Wednesday, January 20, 2016 12:00
Using SQLite Universal App Platform v3.10.1
Software sends query:
SELECT * FROM table WHERE field LIKE %string
Returned results are not what it?s expected.
For example
SELECT * FROM words WHERE word LIKE ??
Query returns fields with values such as ???
That is unexpected behavior.
V3.9.2
REPLACE will delete the offending row in the referenced table and insert a new
one with a newly generated rowid. If the foreign key references the rowid, the
DELETE CASCADE will be invoked because there is no longer a record with the
referenced rowid.
-Urspr?ngliche Nachricht-
Von:
On 1/20/16, Yuri wrote:
>
> Does SQLite optimizer look at the row counts?
It does if they are available. Run ANALYZE to generate the row counts.
--
D. Richard Hipp
drh at sqlite.org
Thanks for the info so far.
I attempted to set the PRAGMA defer_foreign_keys = on for the transaction, but
a REPLACE still deletes data in another table with the ON DELETE CASCADE clause.
chromedout64 at yahoo.com wrote:
> Will SQLite ever get an UPSERT command similar to other DBMS?
These other DMBSes use a client/server architecture and want to achieve
high concurrency.
SQLite has no client/server communication overhead, so there is no large
downside to using two SQL statements
Hello All,
My last successful build was SQLite version 3.11.0 2016-01-12 14:10:05
I build with: ./configure --disable-editline; make
./.libs/libsqlite3.a(sqlite3.o): In function `sqlite3ThreadCreate':
/usr/home/sean/fossil-repos/sqlite3/sqlite3.c:24385: undefined
reference to `pthread_create'
Hello,
It already has, it is called INSERT OR REPLACE
https://www.sqlite.org/lang_insert.html
2016-01-20 2:40 GMT+01:00 :
> Will SQLite ever get an UPSERT command similar to other DBMS? Thanks for
> any info.
> ___
> sqlite-users mailing list
>
The problem with REPLACE is: " the REPLACE algorithm deletes pre-existing rows
that are causing the constraint violation prior to inserting or updating the
current row"
When using REPLACE with a table containing a?foreign key clause ON DELETE
CASCADE causes data in other tables to be deleted.
I have an sqlite database running on a Raspberry Pi 2 (Raspbian
Jessie), and I compile new versions of sqlite on a regular basis.
When I try to compile 3.10.1 (autoconf), configure couldn't find
readline libraries. Last successfully compiled version was 3.9.2
(autoconf).
In both cases, is the same
On 01/20/2016 09:21, Hick Gunter wrote:
> ave you tried running ANALYZE on a representative dataset? This will
> determine the "shape" of your tables and allow the query planner to make
> better guesses of the costs associated with each join.
>
> If, after ANALYZE, you still feel the need to
I have the select query over the linked chain of tables with all needed
indices present.
All but one tables (m,h,e,w) have row count ~300,000-700,000, mt table
has only ~150 rows.
Obviously, based on the row count, scan should begin from the table mt.
However, SQLite actually scans beginning
Thanks for the bug report. The trouble ticket is
https://www.sqlite.org/src/info/80369eddd5c94d49
I suspect this problem will result in a 3.10.2 release, but that
decision is not yet final.
On 1/19/16, Vaidas B wrote:
> Using SQLite Universal App Platform v3.10.1
> Software sends query:
>
Will SQLite ever get an UPSERT command similar to other DBMS? Thanks for any
info.
22 matches
Mail list logo
