[sqlite] offset of file position is beyond EOF

[Jacky Lam]

Hi All,
I recently meet a case that the file position offset of a pager is beyond
EOF position of db.
I would like to know if it is normal or it is  a bug makes from my OS (
SQLITE_OS_OTHER=1)?

Thanks.

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

Re: [sqlite] possible json1 bug?

[Richard Hipp]

On 8/18/17, Jose Manuel  wrote:
> There is a problem whenever you try to define an INDEX over a json column
> and then using a Query with a JOIN over the same table.

Thanks for the report.  But OSSFuzz already found that bug.  It was
fixed four days ago:

https://sqlite.org/src/timeline?c=d0da791ba0edfb65=ci

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible json1 bug?

[Jose Manuel]

There is a problem whenever you try to define an INDEX over a json column
and then using a Query with a JOIN over the same table.

Here is the code that triggers the problem:



SQLiteConnection _dbc1 = new SQLiteConnection("Data
Source=test.db;Version=3;");
 _dbc1.Open();
 _dbc1.EnableExtensions(true);
 _dbc1.LoadExtension(@"SQLite.Interop.dll", "sqlite3_json_init");
SQLiteCommand sqlcmd1 = _dbc1.CreateCommand();

sqlcmd1.CommandText = "CREATE TABLE docs (id int primary key, type text,
body json NOT NULL);";
sqlcmd1.ExecuteNonQuery();

sqlcmd1.CommandText = "create index index1 on docs (type,
json_extract(body, '$.color'));";
sqlcmd1.ExecuteNonQuery();

sqlcmd1.CommandText = "INSERT INTO docs (id, type, body) VALUES(1,
\"customcolor\", json('{\"typecolor\": \"red\" }'));";
sqlcmd1.ExecuteNonQuery();

sqlcmd1.CommandText = "INSERT INTO docs (id, type, body) VALUES(2, \"car\",
json('{\"color\": 1 }'));";
sqlcmd1.ExecuteNonQuery();

string sql = "SELECT main.body as maindoc FROM docs as main INNER JOIN docs
as jointable ON jointable.id = json_extract(maindoc, '$.color') WHERE
main.type = \"car\" AND json_extract(jointable.body, '$.typecolor') =
\"red\" LIMIT 0,-1";

SQLiteCommand command  = new SQLiteCommand(sql, _dbc1);
*SQLiteDataReader reader = command.ExecuteReader();*
reader.Read();
Debug.WriteLine(reader.GetString(0));





*Error*
*System.AccessViolationException*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enforcing uniqueness from multiple indexes

[David Raymond]

Tried it and found a  issue with update, though it might actually 
work the way he wants. Good call though, I keep forgetting the on conflict 
thing can be on table creation and not just for a query.

--continuing from your script...

sqlite> select * from demo;
--EQP-- 0,0,0,SCAN TABLE demo
id|k|otherstuff
1|10|One-Mississippi
2|40|Four-Mississippi
3|30|Three-Mississippi

sqlite> update demo set id = 1 where k = 30;
--EQP-- 0,0,0,SEARCH TABLE demo USING INDEX sqlite_autoindex_demo_1 (k=?)

sqlite> select * from demo;
--EQP-- 0,0,0,SCAN TABLE demo
id|k|otherstuff
1|30|Three-Mississippi
2|40|Four-Mississippi

sqlite> update demo set k = 40 where id = 1;
--EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?)
Error: UNIQUE constraint failed: demo.k

sqlite> update demo set id = 1, k = 30 where id = 2;
--EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?)

sqlite> select * from demo;
--EQP-- 0,0,0,SCAN TABLE demo
id|k|otherstuff
1|30|Four-Mississippi

sqlite>

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Friday, August 18, 2017 12:18 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Enforcing uniqueness from multiple indexes



On 2017/08/18 6:08 PM, R Smith wrote:
>
> Isn't this what conflict clauses on constraints are for?
>

Apologies, I usually add the test-case scripts in case anyone else wish 
to test it or similar, the case in question herewith added below:

   -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed 
version 2.0.2.4.
   -- Script Items: 7  Parameter Count: 0
   -- 


CREATE TABLE demo(
   id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
   k TEXT UNIQUE ON CONFLICT ABORT,
   otherstuff ANY
);

INSERT INTO demo VALUES
(1,10,'One-Mississippi'),
(2,20,'Two-Mississippi'),
(3,30,'Three-Mississippi')
;

   -- This one works as expected, replacing the previous key.
INSERT INTO demo VALUES (2,40,'Four-Mississippi');

SELECT * FROM demo;
   --  id  |  k  | otherstuff
   --  | --- | -
   --   1  |  10 | One-Mississippi
   --   2  |  40 | Four-Mississippi
   --   3  |  30 | Three-Mississippi

   -- This one should fail since the id is new but k conflicts...
INSERT INTO demo VALUES (5,40,'Four-Mississippi-Again');
   -- and does:

   -- 2017-08-18 18:14:20.463  |  [ERROR]  UNIQUE constraint failed: 
demo.k
   --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.025s
   -- Total Script Query Time: 0d 00h 00m and 
00.004s
   -- Total Database Rows Changed: 4
   -- Total Virtual-Machine Steps: 167
   -- Last executed Item Index:5
   -- Last Script Error: Script Failed in Item 4: UNIQUE 
constraint failed: demo.k
   -- 


   -- 2017-08-18 18:14:20.465  |  [Info]   Script failed - Rolling 
back...
   -- 2017-08-18 18:14:20.466  |  [Success]Transaction Rolled back.
   -- 2017-08-18 18:14:20.466  |  [ERROR]  Failed to complete: 
Script Failed in Item 4: UNIQUE constraint failed: demo.k
   -- 


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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Simon Slavin]

On 18 Aug 2017, at 5:39pm, Jens Alfke  wrote:

> Actually, "multi-threaded mode" usually means using a _single_ connection on 
> multiple threads.
> 
> What you're doing — a separate connection for each thread — is effectively* 
> the same as running multiple single-threaded processes, i.e. it doesn't 
> involve any concurrency within SQLite.

We need a grid, with number of connections along the top and number of threads 
down the side.  Might be useful in the documentation.

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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jens Alfke]

> On Aug 18, 2017, at 2:04 AM, sanhua.zh  wrote:
> 
> I am using SQLite in multi-thread mode, which means that different threads 
> using different SQLite connection.

Actually, "multi-threaded mode" usually means using a _single_ connection on 
multiple threads.

What you're doing — a separate connection for each thread — is effectively* the 
same as running multiple single-threaded processes, i.e. it doesn't involve any 
concurrency within SQLite.

—Jens

* There are slight differences, because of some global state in the SQLite 
library (like logging callbacks), but that's irrelevant to what's going on here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enforcing uniqueness from multiple indexes

[R Smith]

On 2017/08/18 6:08 PM, R Smith wrote:


Isn't this what conflict clauses on constraints are for?



Apologies, I usually add the test-case scripts in case anyone else wish 
to test it or similar, the case in question herewith added below:


  -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed 
version 2.0.2.4.

  -- Script Items: 7  Parameter Count: 0
  -- 



CREATE TABLE demo(
  id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
  k TEXT UNIQUE ON CONFLICT ABORT,
  otherstuff ANY
);

INSERT INTO demo VALUES
(1,10,'One-Mississippi'),
(2,20,'Two-Mississippi'),
(3,30,'Three-Mississippi')
;

  -- This one works as expected, replacing the previous key.
INSERT INTO demo VALUES (2,40,'Four-Mississippi');

SELECT * FROM demo;
  --  id  |  k  | otherstuff
  --  | --- | -
  --   1  |  10 | One-Mississippi
  --   2  |  40 | Four-Mississippi
  --   3  |  30 | Three-Mississippi

  -- This one should fail since the id is new but k conflicts...
INSERT INTO demo VALUES (5,40,'Four-Mississippi-Again');
  -- and does:

  -- 2017-08-18 18:14:20.463  |  [ERROR]  UNIQUE constraint failed: 
demo.k
  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.025s
  -- Total Script Query Time: 0d 00h 00m and 
00.004s

  -- Total Database Rows Changed: 4
  -- Total Virtual-Machine Steps: 167
  -- Last executed Item Index:5
  -- Last Script Error: Script Failed in Item 4: UNIQUE 
constraint failed: demo.k
  -- 



  -- 2017-08-18 18:14:20.465  |  [Info]   Script failed - Rolling 
back...

  -- 2017-08-18 18:14:20.466  |  [Success]Transaction Rolled back.
  -- 2017-08-18 18:14:20.466  |  [ERROR]  Failed to complete: 
Script Failed in Item 4: UNIQUE constraint failed: demo.k
  -- 



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

Re: [sqlite] Enforcing uniqueness from multiple indexes

[R Smith]

On 2017/08/18 1:47 PM, Richard Hipp wrote:

On 8/18/17, Wout Mertens  wrote:

So, bottom line, is there a way to insert or replace a row so that first
the id constraint is observed (replacing a previous row with the same id),
and then the k constraint is verified (failing to replace if k is already
present in the table)?


CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
CREATE INDEX demo_k ON demo(k);
CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
   SELECT raise(ABORT,'uniqueness constraint failed on k')
FROM demo WHERE k=new.k;
END;

The above will force uniqueness on k for INSERT statements.  You'll
want a second "BEFORE UPDATE" trigger to do similar enforcement for
UPDATEs if that is an issue for you.


Isn't this what conflict clauses on constraints are for?

Wouldn't the following achieve the same?:

CREATE TABLE demo(
  id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
  k TEXT UNIQUE ON CONFLICT ABORT,
  otherstuff ANY
);

To amuse myself, I've tested it and it works as expected - New IDs that 
conflict are replaced, but a conflicting k gets aborted - but am I 
missing some fine-print or hidden caveat?


Cheers,
Ryan







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

Re: [sqlite] Enforcing uniqueness from multiple indexes

[Nico Williams]

An INSERT OR UPDATE sure would be nice.  What i often do in cases like this
is: UPDATE .. WHERE; INSERT .. SELECT .. WHERE NOT EXISTS (SELECT ..);.
That's two statements -- if that's a problem, then you should use D. R.
Hipp's trigger-based solution.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 18 Aug 2017, at 12:30am, Fahad G  wrote:

> I do not have a way to reproduce this just yet, but I've been seeing way too 
> many diagnostic logs from customers where their databases are being corrupt 
> primarily on the Mac (the exact same code is shared between a Mac app, iPhone 
> and iPad) past several months - more so when I switched to WAL and started 
> dedicating a 'reader' connection for all reads, and a 'writer' for all 
> writes. 

I would say that I don’t see anything wrong with your compiler settings and 
PRAGMAs but that doesn’t mean much because I don’t know much about that stuff.

Would like to check and find out some facts.

At what point does your software detect this corruption ?  Is the database okay 
when it’s opened but get corrupted while he program is working ?  Or is the 
corruption in the file on disk and gets noticed immediately after opening ?  
This might help us figure out whether something in your program is stomping on 
SQLite3 memory.

This started happening several months ago ?  Let’s call it May.  Did you change 
development environments or versions of your Dev tools ?  Do you develop in 
Xcode ?  If so, did you start using a new version fo Xcode ?  Is your 
development computer using a stable version of the OS or the latest Developer 
Release we’re not meant to talk about ?

Are your customers using all the same version of macOS and iOS or are they 
varied ?

Does your application use sqlite3_shutdown() when it quits ?  If not, can you 
make this change ?

Do you check the value returned when you close a database connection and show 
an appropriate error message ?

> I read on the forums that mmap could be at fault (as I was using it). 
> Disabling it almost immediately felt that it solved the problem. However I'm 
> still occasionally now getting reports (weekly) of users running into a 
> "database disk image is malformed" error.

You are correct that use of mmap was (rarely) causing corruption and/or false 
reports of corruption.  Current versions of SQLite no longer use mmap because 
of this.  I’m see you’re using the latest SQLite amalgamation version.

The type of corruption done by this bug was not detectable immediately the file 
was opened.  It might only be noticed when the program tried to read a specific 
record or use a specific index.  Is it possible that your users who are still 
reporting corruption are still using databases which were corrupted earlier ?  
In other words the software is no longer corrupting databases but your users 
have 'legacy corruption' in their files ?

Hope some of this helps or another reader can help you.

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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jay Kreibich]

On Aug 18, 2017, at 7:37 AM, Clemens Ladisch  wrote:

> Jay Kreibich wrote:
>> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:
>>> sanhua.zh wrote:
 1. Conn A: Open, PRAGMA journal_mode=WAL
 2.ConnB: Open, PRAGMA journal_mode=WAL
 3.ConnA: CREATE TABLE sample (i INTEGER);
 4.ConnB: PRAGMA table_info('sample')
 
 Firstly, both thread 1 and 2 do initialization for their own conn, which 
 is to read to schema into memory.
 Then, Conn A creates a table with Conn A.
 Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and 
 it returns nothing.
 
 I do know the reason should be the expired in-memory-schema.
>>> 
>>> No, SQLite automatically detects schema changes.
>> 
>> …but only automatically re-prepares the expired statements if the statement
>> was originally prepared using sqlite3_prepare*_v2 or _v3.
> 
> The word "expired" in the OP is misleading; this problem has nothing to do
> with schema expiration (that would result in an SQLITE_SCHEMA error).

Ah…. OK yes.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



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

[sqlite] Enforcing uniqueness from multiple indexes

[wout.mertens]

The UNIQUE will either do nothing, or will erase existing lines if a
trigger is forgotten. So not having the UNIQUE seems safer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enforcing uniqueness from multiple indexes

[Richard Hipp]

On 8/18/17, Gerry Snyder  wrote:
> Should it be :
>
> CREATE UNIQUE INDEX ...

I don't think it matters in this case.  The trigger is also enforcing
uniqueness.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Clemens Ladisch]

Jay Kreibich wrote:
> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:
>> sanhua.zh wrote:
>>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>>> 4.ConnB: PRAGMA table_info('sample')
>>>
>>> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
>>> to read to schema into memory.
>>> Then, Conn A creates a table with Conn A.
>>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and 
>>> it returns nothing.
>>>
>>> I do know the reason should be the expired in-memory-schema.
>>
>> No, SQLite automatically detects schema changes.
>
> …but only automatically re-prepares the expired statements if the statement
> was originally prepared using sqlite3_prepare*_v2 or _v3.

The word "expired" in the OP is misleading; this problem has nothing to do
with schema expiration (that would result in an SQLITE_SCHEMA error).


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

Re: [sqlite] Enforcing uniqueness from multiple indexes

[Gerry Snyder]

Should it be :

CREATE UNIQUE INDEX ...


Gerry Snyder

On Aug 18, 2017 4:47 AM, "Richard Hipp"  wrote:

> On 8/18/17, Wout Mertens  wrote:
> >
> > So, bottom line, is there a way to insert or replace a row so that first
> > the id constraint is observed (replacing a previous row with the same
> id),
> > and then the k constraint is verified (failing to replace if k is already
> > present in the table)?
>
>
> CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
> CREATE INDEX demo_k ON demo(k);
> CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
>   SELECT raise(ABORT,'uniqueness constraint failed on k')
>FROM demo WHERE k=new.k;
> END;
>
> The above will force uniqueness on k for INSERT statements.  You'll
> want a second "BEFORE UPDATE" trigger to do similar enforcement for
> UPDATEs if that is an issue for you.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enforcing uniqueness from multiple indexes

[Wout Mertens]

Splendid! Many thanks!

On Fri, Aug 18, 2017 at 1:47 PM Richard Hipp  wrote:

> On 8/18/17, Wout Mertens  wrote:
> >
> > So, bottom line, is there a way to insert or replace a row so that first
> > the id constraint is observed (replacing a previous row with the same
> id),
> > and then the k constraint is verified (failing to replace if k is already
> > present in the table)?
>
>
> CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
> CREATE INDEX demo_k ON demo(k);
> CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
>   SELECT raise(ABORT,'uniqueness constraint failed on k')
>FROM demo WHERE k=new.k;
> END;
>
> The above will force uniqueness on k for INSERT statements.  You'll
> want a second "BEFORE UPDATE" trigger to do similar enforcement for
> UPDATEs if that is an issue for you.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'database disk image is malformed' only on the mac

[Fahad G]

Hi

Although I've read all the rules (and am otherwise aware of what it takes to 
report a bug), I want to apologise upfront. I do not have a way to reproduce 
this just yet, but I've been seeing way too many diagnostic logs from customers 
where their databases are being corrupt primarily on the Mac (the exact same 
code is shared between a Mac app, iPhone and iPad) past several months - more 
so when I switched to WAL and started dedicating a 'reader' connection for all 
reads, and a 'writer' for all writes. 

I have read and tried every possible combination of flags and setting up the 
connections, making sure (via numerous unit tests) that the code in question is 
working, thread safe etc. I recently also switched to SERIALIZED mode (compile 
time option) in hope that this would go away. When this started happening on a 
daily basis a coupe of months ago, I read on the forums that mmap could be at 
fault (as I was using it). Disabling it almost immediately felt that it solved 
the problem. However I'm still occasionally now getting reports (weekly) of 
users running into a "database disk image is malformed" error. I've asked one 
of the users to send us a copy of the corrupt database, but this isn't always 
possible (waiting on them).

I open for writing using:

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, , 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, NULL) == 
SQLITE_OK);

if (sqlite3_exec(dbConnection, "PRAGMA main.journal_mode=WAL; PRAGMA 
synchronous=normal;", NULL, NULL, NULL) != SQLITE_OK) {
 ...
}


And for reading:

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, , 
SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READONLY | SQLITE_OPEN_WAL, NULL) == 
SQLITE_OK)


if (sqlite3_exec(readOnlyDB, "PRAGMA read_uncommitted=1; PRAGMA query_only=1; 
PRAGMA synchronous=normal;", NULL, NULL, NULL) != SQLITE_OK) {
 ...
}


I frequently would run VACUUM and ANALYZE but stopped doing that as well (in 
order to single this issue out), but am still seeing these error reports come 
in. 

I'm using the latest SQL amalgamation (v3.20.0) with the following compile time 
options:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_THREADSAFE 2
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_OMIT_DEPRECATED 1
#define SQLITE_OMIT_SHARED_CACHE 1

Any help would be appreciated.

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

Re: [sqlite] Enforcing uniqueness from multiple indexes

[Richard Hipp]

On 8/18/17, Wout Mertens  wrote:
>
> So, bottom line, is there a way to insert or replace a row so that first
> the id constraint is observed (replacing a previous row with the same id),
> and then the k constraint is verified (failing to replace if k is already
> present in the table)?


CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
CREATE INDEX demo_k ON demo(k);
CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
  SELECT raise(ABORT,'uniqueness constraint failed on k')
   FROM demo WHERE k=new.k;
END;

The above will force uniqueness on k for INSERT statements.  You'll
want a second "BEFORE UPDATE" trigger to do similar enforcement for
UPDATEs if that is an issue for you.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jay Kreibich]

On Aug 18, 2017, at 4:04 AM, sanhua.zh  wrote:

> I am using SQLite in multi-thread mode, which means that different threads 
> using different SQLite connection.
> And now I find an issue that the results of SQLite C interface returned is 
> expired while the schema of database is changed.
> 
> 
> The following sample runs in different threads, but I force them to 
> runsequentially.
> 
> 
> Thread 1:
> 1. Conn A: Open, PRAGMA journal_mode=WAL
> Thread 2:
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> Thread 1:
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> Thread 2:
> 4.ConnB: PRAGMA table_info('sample')
> 
> 
> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
> to read to schema into memory.
> Then, Conn A creates a table with Conn A.
> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
> returns nothing.
> The same thing could happen if I change the step 4 to 
> `sqlite3_table_column_metadata` or some other interfaces.
> 
> 
> I do know the reason should be the expired in-memory-schema. But I find no 
> docs about which interface will or will not update the schema and what should 
> I do while I call a non-update-schema interface ?


See the bottom of the sqlite3_prepare*() docs:

https://www.sqlite.org/c3ref/prepare.html

And the SQLITE_SCHEMA docs:

https://www.sqlite.org/rescode.html#schema



As the docs say, make sure you’re using sqlite3_prepare*_v2() or _v3().  If a 
statement is prepared with these newer versions, it will handle most expiration 
situations automatically by re-preparing the statement.

Generally speaking, if you do get an SQLITE_SCHEMA error, you need to rollback 
the current transaction, re-prepare the statements, and try again.

   -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jay Kreibich]

On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:

> sanhua.zh wrote:
>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>> 4.ConnB: PRAGMA table_info('sample')
>> 
>> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
>> to read to schema into memory.
>> Then, Conn A creates a table with Conn A.
>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and 
>> it returns nothing.
>> 
>> I do know the reason should be the expired in-memory-schema.
> 
> No, SQLite automatically detects schema changes.

…but only automatically re-prepares the expired statements if the statement
was originally prepared using sqlite3_prepare*_v2 or _v3.


> It's likely that the second connection started its transaction before
> the first connection committed its own, so it still sees the old state
> of the database.

Unlike most RDBMS environments, SQLite handles DDL as part of
normal transactions.  I don’t think this situation would be a problem,
and would be handled by the normal locking mechanisms.

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



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

[sqlite] Enforcing uniqueness from multiple indexes

[Wout Mertens]

Hi,

I have the following problem: I have data where two independent values need
to be unique. I'm using one (id) as the primary key, and the other (let's
call it k) should just cause insertion or updating to fail if it already
exists in another row with a different id in the table.

Furthermore, I'd like this to be free from race conditions :)

If I just use two different UNIQUE indexes, doing a "INSERT OR REPLACE"
will cause another row that has the same k to be replaced, instead of
failing to update due to the differing id. I suppose this is entirely
logical, but it's not what I'd like to achieve :)

So, bottom line, is there a way to insert or replace a row so that first
the id constraint is observed (replacing a previous row with the same id),
and then the k constraint is verified (failing to replace if k is already
present in the table)?

I'm ok with doing some multi-step thing, perhaps first trying UPDATE and
then INSERT, but I'm not sure what would be most efficient, and I'm also
not sure how to prevent racing when checking for k…

Thanks in advance for any insights!

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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Clemens Ladisch]

sanhua.zh wrote:
> 1. Conn A: Open, PRAGMA journal_mode=WAL
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> 4.ConnB: PRAGMA table_info('sample')
>
> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
> to read to schema into memory.
> Then, Conn A creates a table with Conn A.
> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
> returns nothing.
>
> I do know the reason should be the expired in-memory-schema.

No, SQLite automatically detects schema changes.

It's likely that the second connection started its transaction before
the first connection committed its own, so it still sees the old state
of the database.


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

[sqlite] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

I am using SQLite in multi-thread mode, which means that different threads 
using different SQLite connection.
And now I find an issue that the results of SQLite C interface returned is 
expired while the schema of database is changed.


The following sample runs in different threads, but I force them to 
runsequentially.


Thread 1:
1. Conn A: Open, PRAGMA journal_mode=WAL
Thread 2:
2.ConnB: Open, PRAGMA journal_mode=WAL
Thread 1:
3.ConnA: CREATE TABLE sample (i INTEGER);
Thread 2:
4.ConnB: PRAGMA table_info('sample')


Firstly, both thread 1 and 2 do initialization for their own conn, which is to 
read to schema into memory.
Then, Conn A creates a table with Conn A.
Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
returns nothing.
The same thing could happen if I change the step 4 to 
`sqlite3_table_column_metadata` or some other interfaces.


I do know the reason should be the expired in-memory-schema. But I find no docs 
about which interface will or will not update the schema and what should I do 
while I call a non-update-schema interface ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users