[sqlite] Feature Suggestions

[Allen]

> The problem with going to EXCLUSIVE manually is that you lock out other 
> connections for longer than necessary.

I have only one connection that does writes.  In any event, I wanted
to confirm the readers would work correctly while another connection
held an EXCLUSIVE lock, because if they didn't, and the lock
eventually went EXCLUSIVE automatically, that might be a problem, and
I would prefer to open up a big window for the problem to occur, so I
can see it right away, rather than have it hidden in a little tiny
window that pops up only rarely.

[sqlite] Feature Suggestions

[Allen]

> Why on earth are you using BEGIN EXCLUSIVE?

If WAL works "as advertised", I might as well go straight to EXCLUSIVE so I
don't have to later escalate the lock from RESERVED to EXCLUSIVE.  And it
does work just fine, once I turned off the shared cache mode.

[sqlite] Feature Suggestions

[Allen]

> I was under the apparently mistaken impression that starting any
transaction would block writes, even in WAL mode.

My problem was that I was using sqlite3_enable_shared_cache(true).  I
enabled this because I was under the impression that is required for
multiple connections to share an in-memory database (one opened with the
URL parameter "?mode=memory").  With a WAL database, the shared cache was
causing the readers to block the writer and vice-versa.  I fixed the
problem by opening the WAL database with the URL parameter
"?cache=private", and verified that readers no longer block the writer, the
writer does not block readers, and the readers see the snapshot of the
database that exists when they call BEGIN, regardless of later writes.

That is exactly what I was looking for in my feature suggestion #1.  #2 and
#3 (especially #2) would also be nice to have--they would allow a more
elegant and efficient solution to my problem--but I think I should be able
to get it working without them...

[sqlite] Feature Suggestions

[Simon Slavin]

On 24 Sep 2015, at 5:37pm, Allen  wrote:

> I was under the apparently mistaken impression that starting any
> transaction would block writes, even in WAL mode.

In WAL mode, a writing connection writes to the journal.  Other connections 
will continue to read from the 'live' database.  When the writing connection 
commits its transaction

1) the database is locked
2) the 'live' database is updated with the changes in the journal
3) the journal is zero'd out
4) the database is unlocked again

[above is simplified]

This means you can take as long as you like in writing your updates since no 
changes are made until you commit.  But it also means that only one connection 
can make changes at a time, since the to allow two connections to make changes 
at the same time would violate ACID.

Simon.

[sqlite] Feature Suggestions

[Clemens Ladisch]

Allen wrote:
> 1. It would be nice to have read-only transactions for use with WAL
> databases.  When a read-only transaction was started, it would take a
> "lock" on the WAL and then not advance further into the WAL until the
> transaction ended.  Thus, all select statements issued while the read
> transaction was open would see all writes that had been committed prior to
> the start of the read transaction, but would not see any writes that are
> not yet committed when the read transaction is started or writes that start
> or complete while the read transaction is open.  This would apply to all
> (multiple) select statements that would all see the same view of the data
> during the duration of the read transaction.

How is this different from a normal transaction?

> 3. It would be nice if there were an in-memory mode for WAL databases that
> could be shared by multiple database connections on multiple threads (or
> possibly multiple processes) and that had the benefits of "multi-version
> concurrency" but the data was kept entirely in memory

You could create your own VFS for that.

(While WAL allows _some_ concurrency, SQLite is not really designed for
high concurrency.)


Regards,
Clemens

[sqlite] Any database unique ID across multiple connections ?

[Scott Hess]

On Thu, Sep 24, 2015 at 4:56 AM, ALBERT Aur?lien <
aurelien.albert at alyotech.fr> wrote:

> @ Stephan Beal
>
> "Every instance of a :memory: db is a unique instance, so you cannot have
> multiple connections to a single :memory: db."
>
> >> I know, this is one of the various reasons that made my solution not
> really ideal
>
> @ Simon Slavin


You can share in-memory databases using URI names.
   https://www.sqlite.org/inmemorydb.html

"There's a PRAGMA for storing and retrieving a 'user-version' in the
> database:
>
> 
>
> It's not used by SQLite itself, you can use it to store any 32-bit signed
> integer.  So you could store a different number to each of your databases
> and check to see whether the user-version of one connection is the same as
> that from a different connection."
>
> >> That's a good idea, but this 'user-version' pragma is persistent and
> stored on disk. So it's difficult to use in my case : if the user quit the
> application and restart it without loading the same databases (or in the
> same order) assigning 'user-version' id will be really difficult.
>

I'm not exactly sure what your concern is.  If you assign it a random
number, each database will still see a different value next time you open
things, regardless of ordering.  If you're greatly concerned about birthday
paradox, you could do something like:

   CREATE VIEW IF NOT EXISTS GUID AS SELECT '';

and then use that to differentiate databases.

In fact, I expected something like a unique memory address (void*) per
> database.


For the most part they're entirely distinct connections each accessing the
same underlying file, there's no reason to have such a unique memory
address.

You may be able to do something using shared-cache mode:
   https://www.sqlite.org/sharedcache.html
I can't immediately tell if this would mean a single underlying handle to
the file or not.  If so, you could maybe use sqlite3_file_control() to dig
into things.  But shared-cache mode changes how things work a bit, so it
might not be appropriate for this use case.

-scott

[sqlite] safety of WAL with SQLITE_THREADSAFE=0 on SMP system with multiple processes

[Török Edwin]

Hi,

When -DSQLITE_THREADSAFE=0 is used then unixShmBarrier becomes a noop because 
unixEnterMutex/unixLeaveMutex are empty functions.
Digging through the SQLite source code I found this comment:

/* Read the header. This might happen concurrently with a write to the
** same area of shared memory on a different CPU in a SMP,
** meaning it is possible that an inconsistent snapshot is read
** from the file. If this happens, return non-zero.
** There are two copies of the header at the beginning of the wal-index.
** When reading, read [0] first then [1].  Writes are in the reverse order.
** Memory barriers are used to prevent the compiler or the hardware from
** reordering the reads and writes.
*/

IIUC then with SQLITE_THREADSAFE=0 on an SMP system with a database in WAL mode 
SQLite wouldn't be able to detect
that an inconsistent snapshot is read.

Should this be documented, and how serious would be the consequences of reading 
an inconsistent snapshot there?
Would it be recommended to always build applications that use WAL with 
SQLITE_THREADSAFE!=0?


Best regards,
--Edwin

[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Daniel Polski]

Hello again,
I think I can simplify the actual question:

Can I use a SELECT returning multiple rows as input to some INSERT 
logic, and in that logic INSERT into multiple tables?

Like if I have a table t1 containing id 1, 2, 3.

For each id in t1, do
INSERT INTO t2 (...)
INSERT INTO t3 (...) --But this depends on the last_insert_rowid() from 
the insert into t2 above

The problem for me is that I don't know how to fetch the 
last_insert_rowid() needed for the t3 table. (If I didn't have to split 
up the insert I could simply run something like INSERT INTO t2 SELECT id 
FROM t1; )

Best Regards,
Daniel



Den 2015-09-24 kl. 13:31, skrev Marco:
> Excerpts from Daniel Polski's message of 2015-09-24 07:38:24 -0300:
>> Hello!
>>
>> --Table t1 contains some "base" data
>> CREATE TABLE t1 (
>>   idINTEGER PRIMARY KEY,
>>   name VARCHAR(10),
>>   value INT NOT NULL
>> );
>>
>> INSERT INTO t1 VALUES(1, 't1 1', 0);
>> INSERT INTO t1 VALUES(2, 't1 2', 0);
>>
>> --Table t2 can contain extra data for a row in table t1
>> CREATE TABLE t2(
>>   id INTEGER PRIMARY KEY,
>>   t1_idINT NOT NULL,
>>   dataINT NOT NULL,
>>   CONSTRAINT 'FK_t2_001' FOREIGN KEY( t1_id ) REFERENCES t1 ( id )
>> );
>> INSERT INTO t2 VALUES(1, 1, 10);
>> INSERT INTO t2 VALUES(2, 1, 20);
>> INSERT INTO t2 VALUES(3, 2, 30);
>>
>> CREATE TABLE t3 (
>>   idINTEGER PRIMARY KEY,
>>   name VARCHAR(10)
>> );
>>
>> CREATE TABLE t4(
>>   t2_idINT NOT NULL,
>>   t3_idINT NOT NULL,
>>   CONSTRAINT 'FK_t4_001' FOREIGN KEY( t2_id ) REFERENCES t2 ( id ),
>>   CONSTRAINT 'FK_t4_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id )
>> );
>>
>>
>>
>> -- Here is where my real question starts. Can I fetch a list of rows in
>> a trigger
>> -- and update different tables from that data? Im looking for some kind
>> -- of looping over the rows found in t2, where I can insert data to t3 and
>> -- t4 for each row found in t2.
>>
>> CREATE TRIGGER trigger_1 AFTER UPDATE OF value ON t1
>> WHEN new.value = 1
>> AND EXISTS (SELECT id FROM t2 WHERE t1_id = new.id)
>> AND (some more rules)
>> ...
>> -- For each row in t2 with a t1_id referencing the t1 id,
>> -- 1: insert a row into t3
>> -- 2: insert a row into t4 binding the newly created row in t3 to the
>> corresponding row in t2
> may try this to insert into t3.
>
> CREATE TRIGGER trigger_on_table_1
>AFTER UPDATE OF value ON t1
>FOR EACH ROW WHEN NEW.VALUE = 1 AND EXISTS (SELECT id FROM te WHERE
>ti_id = NEW.id)
>BEGIN
>
>  -- SELECT THE ROWS FROM t2 to insert values
>  INSERT INTO t3 VALUES(id, name) SELECT t2.t1_id, 'dummy' FROM t2
>  WHERE t2.t1_id == NEW.id;
>
>END;
>
> Then you may wish to set a trigger to t4 itself to handle the newly
> created t3 rows.
>

[sqlite] Any database unique ID across multiple connections ?

[Kees Nuyt]

On Thu, 24 Sep 2015 11:42:27 +0200, ALBERT Aur?lien
 wrote:

>Hi,
>
>I'm using SQLite C API and my application use multiple databases, each 
>database having multiple connections.
>I need to identify which connection use the same database as another 
>connection.
>For the moment, I use the absolute database filename as an "unique database 
>identifier", and I store this information with my "sqlite3*" connection handle.
>Is there any simpler/safer way to get a unique database identifier across 
>multiple connections ? (for example, if one day I need the same about 
>":memory:" databases, bad things are gonna to happen)
>Maybe there is already a database identifier in the SQLite API and I missed it 
>?

I think 
 https://www.sqlite.org/pragma.html#pragma_application_id
is applicable.

-- 
Regards,

Kees Nuyt

[sqlite] Any database unique ID across multiple connections ?

[ALBERT Aurélien]

@ Stephan Beal

"Every instance of a :memory: db is a unique instance, so you cannot have 
multiple connections to a single :memory: db."

>> I know, this is one of the various reasons that made my solution not really 
>> ideal

@ Simon Slavin

"There's a PRAGMA for storing and retrieving a 'user-version' in the database:



It's not used by SQLite itself, you can use it to store any 32-bit signed 
integer.  So you could store a different number to each of your databases and 
check to see whether the user-version of one connection is the same as that 
from a different connection."

>> That's a good idea, but this 'user-version' pragma is persistent and stored 
>> on disk. So it's difficult to use in my case : if the user quit the 
>> application and restart it without loading the same databases (or in the 
>> same order) assigning 'user-version' id will be really difficult.

In fact, I expected something like a unique memory address (void*) per database.

Thanks.

[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Daniel Polski]

Den 2015-09-24 kl. 13:03, skrev Simon Slavin:
> On 24 Sep 2015, at 11:38am, Daniel Polski  wrote:
>
>> -- Here is where my real question starts. Can I fetch a list of rows in a 
>> trigger
>> -- and update different tables from that data?
> Create a VIEW which does this
>
> SELECT ... FROM t1 JOIN t2 ON t2.t1_id = t1.id
>
> Then set the triggers for the VIEW rather than either of the tables.
>

Hmmm.. I don't understand how I can split up the INSERT in the trigger 
to insert the rows in the view into t3, t4?

...
--Should make the trigger run:
INSERT INTO t3 VALUES(1, 'dummy');
INSERT INTO t4 VALUES(1, last_insert_rowid());

INSERT INTO t3 VALUES(2, 'dummy');
INSERT INTO t4 VALUES(2, last_insert_rowid());

(The insert into t4 need the last_insert_rowid() from the insert into t3)

/Daniel

[sqlite] Feature Suggestions

[Keith Medcalf]

> > WAL does this already.  You just need to BEGIN a transaction when you
> want it to BEGIN and COMMIT or ROLLBACK when you are done with it.
> 
> I was under the apparently mistaken impression that starting any
> transaction would block writes, even in WAL mode.
> 
> Just to be clear, this would work:
> 
> On multiple concurrent reader threads/connections:
> 
> BEGIN;
> SELECT col FROM table;
> SELECT col FROM table;
> ROLLBACK;
> 
> On a single concurrent writer thread/connection:
> 
> BEGIN EXCLUSIVE;
> UPDATE table SET col = z;
> COMMIT;
> 
> In a WAL database, none of the threads will block (the BEGIN EXCLUSIVE
> will
> not block the readers, and vice-versa), and the two SELECT's will always
> get the same value?

Why on earth are you using BEGIN EXCLUSIVE?

Have you looked up the meaning of EXCLUSIVE in a lexicon of the English 
Language (or the SQLITE documentation)?  
And if you have and you really want EXCLUSIVE, then what you get is what you 
asked for (which is not Repeatable Read transactions in other connections).

Perhaps you meant BEGIN IMMEDIATE ...

I also note that you said concurrent "threads/connections".  Note that you need 
to have separate connections.  A connection shared between threads is still one 
connection and that transactions apply to connections, not threads (which are 
irrelevant).  Also, you must not be using shared cache since that changes the 
semantics considerably (multiple shared cache connections are only a single 
connection to the database).

[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Daniel Polski]

Hello!

--Table t1 contains some "base" data
CREATE TABLE t1 (
 idINTEGER PRIMARY KEY,
 name VARCHAR(10),
 value INT NOT NULL
);

INSERT INTO t1 VALUES(1, 't1 1', 0);
INSERT INTO t1 VALUES(2, 't1 2', 0);

--Table t2 can contain extra data for a row in table t1
CREATE TABLE t2(
 id INTEGER PRIMARY KEY,
 t1_idINT NOT NULL,
 dataINT NOT NULL,
 CONSTRAINT 'FK_t2_001' FOREIGN KEY( t1_id ) REFERENCES t1 ( id )
);
INSERT INTO t2 VALUES(1, 1, 10);
INSERT INTO t2 VALUES(2, 1, 20);
INSERT INTO t2 VALUES(3, 2, 30);

CREATE TABLE t3 (
 idINTEGER PRIMARY KEY,
 name VARCHAR(10)
);

CREATE TABLE t4(
 t2_idINT NOT NULL,
 t3_idINT NOT NULL,
 CONSTRAINT 'FK_t4_001' FOREIGN KEY( t2_id ) REFERENCES t2 ( id ),
 CONSTRAINT 'FK_t4_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id )
);



-- Here is where my real question starts. Can I fetch a list of rows in 
a trigger
-- and update different tables from that data? Im looking for some kind
-- of looping over the rows found in t2, where I can insert data to t3 and
-- t4 for each row found in t2.

CREATE TRIGGER trigger_1 AFTER UPDATE OF value ON t1
WHEN new.value = 1
AND EXISTS (SELECT id FROM t2 WHERE t1_id = new.id)
AND (some more rules)
...
-- For each row in t2 with a t1_id referencing the t1 id,
-- 1: insert a row into t3
-- 2: insert a row into t4 binding the newly created row in t3 to the 
corresponding row in t2


-- For example:
UPDATE t1 SET value = 1 WHERE id = 1;

--Should make the trigger run:
INSERT INTO t3 VALUES(1, 'dummy');
INSERT INTO t4 VALUES(1, last_insert_rowid());

INSERT INTO t3 VALUES(2, 'dummy');
INSERT INTO t4 VALUES(2, last_insert_rowid());


How would you accomplish this?

Thank you!
/Daniel

[sqlite] Feature Suggestions

[Allen]

> WAL does this already.  You just need to BEGIN a transaction when you
want it to BEGIN and COMMIT or ROLLBACK when you are done with it.

I was under the apparently mistaken impression that starting any
transaction would block writes, even in WAL mode.

Just to be clear, this would work:

On multiple concurrent reader threads/connections:

BEGIN;
SELECT col FROM table;
SELECT col FROM table;
ROLLBACK;

On a single concurrent writer thread/connection:

BEGIN EXCLUSIVE;
UPDATE table SET col = z;
COMMIT;

In a WAL database, none of the threads will block (the BEGIN EXCLUSIVE will
not block the readers, and vice-versa), and the two SELECT's will always
get the same value?

[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Simon Slavin]

On 24 Sep 2015, at 11:38am, Daniel Polski  wrote:

> -- Here is where my real question starts. Can I fetch a list of rows in a 
> trigger
> -- and update different tables from that data?

Create a VIEW which does this

SELECT ... FROM t1 JOIN t2 ON t2.t1_id = t1.id

Then set the triggers for the VIEW rather than either of the tables.

Simon.

[sqlite] Any database unique ID across multiple connections ?

[Stephan Beal]

On Thu, Sep 24, 2015 at 11:42 AM, ALBERT Aur?lien <
aurelien.albert at alyotech.fr> wrote:

>  (for example, if one day I need the same about ":memory:" databases, bad
> things are gonna to happen)
>

Every instance of a :memory: db is a unique instance, so you cannot have
multiple connections to a single :memory: db.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Any database unique ID across multiple connections ?

[ALBERT Aurélien]

Hi,

I'm using SQLite C API and my application use multiple databases, each database 
having multiple connections.

I need to identify which connection use the same database as another connection.

For the moment, I use the absolute database filename as an "unique database 
identifier", and I store this information with my "sqlite3*" connection handle.

Is there any simpler/safer way to get a unique database identifier across 
multiple connections ? (for example, if one day I need the same about 
":memory:" databases, bad things are gonna to happen)

Maybe there is already a database identifier in the SQLite API and I missed it ?

Thanks.

[sqlite] Feature Suggestions

[Allen]

Suggestions for SQLite features:

1. It would be nice to have read-only transactions for use with WAL
databases.  When a read-only transaction was started, it would take a
"lock" on the WAL and then not advance further into the WAL until the
transaction ended.  Thus, all select statements issued while the read
transaction was open would see all writes that had been committed prior to
the start of the read transaction, but would not see any writes that are
not yet committed when the read transaction is started or writes that start
or complete while the read transaction is open.  This would apply to all
(multiple) select statements that would all see the same view of the data
during the duration of the read transaction.

2. It would be nice if the WAL operation did not require anything more than
passive checkpoints, and had a mechanism to restart or truncate the WAL
even in the presence of a steady stream of reads and writes.  One possible
way to do this would be to use two WAL files and "ping-pong" between
them--writes would go to one WAL file while the other is being fully
checkpointed and reset, after which the WAL files switch roles and the
empty WAL written to while the full one is checkpointed.

3. It would be nice if there were an in-memory mode for WAL databases that
could be shared by multiple database connections on multiple threads (or
possibly multiple processes) and that had the benefits of "multi-version
concurrency" but the data was kept entirely in memory, and no disk files
were created except possibly temporary files to map shared memory, etc.

There might be a way to do some or all of this already that I didn't see or
understand when reading the documentation, but if not, I think these would
be valuable additions.

[sqlite] Any database unique ID across multiple connections ?

[Simon Slavin]

On 24 Sep 2015, at 10:42am, ALBERT Aur?lien  
wrote:

> I need to identify which connection use the same database as another 
> connection.
> 
> For the moment, I use the absolute database filename as an "unique database 
> identifier", and I store this information with my "sqlite3*" connection 
> handle.

There's a PRAGMA for storing and retrieving a 'user-version' in the database:



It's not used by SQLite itself, you can use it to store any 32-bit signed 
integer.  So you could store a different number to each of your databases and 
check to see whether the user-version of one connection is the same as that 
from a different connection.

Simon.

[sqlite] Fwd: Outdated section of docs?

[Dave McKee]

Hmmm... actually, looks like Windows 10 has regressed back to only having
one set of DST rules...

Manually changed timezone to Pacific US.  Results are from before reboot
but don't seem to have changed after.

David.

Windows 10
Dates from https://www.timeanddate.com/time/dst/1985.html
Using sqlite-shell-win32-x86-3081101.zip
--
2012: Sunday, 11 March
sqlite> select datetime("2012-03-10T12:00", 'localtime');
2012-03-10 04:00:00
sqlite> select datetime("2012-03-11T12:00", 'localtime');
2012-03-11 05:00:00

2007: Sunday, 11 March
sqlite> select datetime("2007-03-10T12:00", 'localtime');
2007-03-10 04:00:00
sqlite> select datetime("2007-03-11T12:00", 'localtime');
2007-03-11 05:00:00

2006: Sunday, 2 April
sqlite> select datetime("2006-04-01T12:00", 'localtime');
2006-04-01 05:00:00
sqlite> select datetime("2006-04-02T12:00", 'localtime');
2006-04-02 05:00:00
sqlite> select datetime("2006-04-03T12:00", 'localtime');
2006-04-03 05:00:00

sqlite> select datetime("2006-03-11T12:00", 'localtime');
2006-03-11 04:00:00
sqlite> select datetime("2006-03-12T12:00", 'localtime');
2006-03-12 05:00:00

1985: Sunday, 28 April
sqlite> select datetime("1985-03-09T12:00", 'localtime');
1985-03-09 04:00:00
sqlite> select datetime("1985-03-10T12:00", 'localtime');
1985-03-10 05:00:00


On 22 September 2015 at 17:32, Dave McKee  wrote:

>
> -- Forwarded message --
> From: Jonathan Moules 
> Date: Mon, Sep 21, 2015 at 5:31 PM
> Subject: [sqlite] Outdated section of docs?
> To: sqlite-users at mailinglists.sqlite.org
>
>
> Hi,
> I was reading this page (http://sqlite.org/lang_datefunc.html), and at
> the very bottom it says:
> /
> /
>
>/"Non-Vista Windows platforms only support one set of DST rules.
>Vista only supports two. Therefore, on these platforms, historical
>DST calculations will be incorrect. For example, in the US, in 2007
>the DST rules changed. Non-Vista Windows platforms apply the new
>2007 DST rules to all previous years as well. Vista does somewhat
>better getting results correct back to 1986, when the rules were
>also changed."/
>
>
> I wonder if that's a section that was written years ago, and the bits that
> apply to Vista also apply to the Windows releases since then? I don't know
> enough about Windows Timezone things to be able to find out easily but this
> reads like it was written back in the era of Vista and probably holds for
> newer releases too.
> Cheers,
> Jonathan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Marco]

Excerpts from Daniel Polski's message of 2015-09-24 09:50:36 -0300:
> 
> Hello again,
> I think I can simplify the actual question:
> 
> Can I use a SELECT returning multiple rows as input to some INSERT 
> logic, and in that logic INSERT into multiple tables?

That was exactly what I showed you using only one table. (INSERT always
insert in one Table, only exception is for views (INSTEAD OF) triggers).
> 
> Like if I have a table t1 containing id 1, 2, 3.
> 
> For each id in t1, do
> INSERT INTO t2 (...)
> INSERT INTO t3 (...) --But this depends on the last_insert_rowid() from 
> the insert into t2 above
> 
> The problem for me is that I don't know how to fetch the 
> last_insert_rowid() needed for the t3 table. (If I didn't have to split 
> up the insert I could simply run something like INSERT INTO t2 SELECT id 
> FROM t1; )

You can't use last_insert_rowid because it only stores a single value.
In order to have the list of values you have somehow to create a
variable for that. One way is to use temporary tables, but I think this
is not possible in a trigger (to have a CREATE statement). The other way
is what I suggested you: create a new trigger for t2 itself to check
on the values inserted.

> 
> Best Regards,
> Daniel

-- 
Marco Arthur @ (M)arco Creatives

[sqlite] Feature Suggestions

[Keith Medcalf]

On Thursday, 24 September, 2015 08:53, Allen  said:

> Suggestions for SQLite features:

> 1. It would be nice to have read-only transactions for use with WAL
> databases.  When a read-only transaction was started, it would take a
> "lock" on the WAL and then not advance further into the WAL until the
> transaction ended.  Thus, all select statements issued while the read
> transaction was open would see all writes that had been committed prior 
> to the start of the read transaction, but would not see any writes that 
> are not yet committed when the read transaction is started or writes 
> that start or complete while the read transaction is open.  This would 
> apply to all (multiple) select statements that would all see the same 
> view of the data during the duration of the read transaction.

WAL does this already.  You just need to BEGIN a transaction when you
want it to BEGIN and COMMIT or ROLLBACK when you are done with it.  Of
course the Repeatable-Read isolation only applies within the transaction
only on the single connection.  If you perform an UPDATE during the 
transaction the changes will, of course, be visible on that connection
during the transaction.

That is to say that if on one connection you do:

BEGIN;
SELECT col FROM table;
(go away and do some stuff)
SELECT col FROM table;
ROLLBACK;
BEGIN;
SELECT col FROM table;
ROLLBACK;


and during the (go away and do some stuff) part A DIFFERENT CONNECTION
does:

BEGIN;
INSERT INTO table VALUES (10),(11),(12);
COMMIT;

the second select in the first connection will see and obtain the same
results as the first select did.  The new data inserted into table will
not be visible until the transaction is ended.  Those inserts will be
visible only to the third select in the first connection.

Your thinking that this is not the case probably arises from not using
explicit transactions.  For example, if you executed the same code but 
did not explicitly put the BEGIN/ROLLBACK on the first connection, SQLITE 
would automatically wrap each SELECT in an implicit transaction.  Since each
SELECT is now in a separate transaction, Repeatable Read does not apply
between selects and the second select would see the inserts that were 
done on the second connection.

WAL mode selects using implicit transactions is effectively Cursor Stability
isolation level.  If you use explicit transactions, you effectively place 
the connection into Repeatable Read for the duration of your explicit
transaction.

[sqlite] safety of WAL with SQLITE_THREADSAFE=0 on SMP system with multiple processes

[Richard Hipp]

On 9/24/15, T?r?k Edwin  wrote:
> Hi,
>
> When -DSQLITE_THREADSAFE=0 is used then unixShmBarrier becomes a noop
> because unixEnterMutex/unixLeaveMutex are empty functions.

Good observation.  This problem is addressed by
https://www.sqlite.org/src/info/c6ab807b72ddfc14

Note that if unixShmBarrier does not work, then that does not
guarantee problems.  It only opens up the possibility of a race
condition.

Further observe that this deficiency has existed in the Fossil
instances that run SQLite's version control system use WAL mode and
are compiled with SQLITE_THREADSAFE=0 and they have run for many years
without issues.  So it is probably not a severe problem, but it is
worth fixing.

> Digging through the SQLite source code I found this comment:
>
> /* Read the header. This might happen concurrently with a write to the
> ** same area of shared memory on a different CPU in a SMP,
> ** meaning it is possible that an inconsistent snapshot is read
> ** from the file. If this happens, return non-zero.
> ** There are two copies of the header at the beginning of the wal-index.
> ** When reading, read [0] first then [1].  Writes are in the reverse order.
> ** Memory barriers are used to prevent the compiler or the hardware from
> ** reordering the reads and writes.
> */
>
> IIUC then with SQLITE_THREADSAFE=0 on an SMP system with a database in WAL
> mode SQLite wouldn't be able to detect
> that an inconsistent snapshot is read.
>
> Should this be documented, and how serious would be the consequences of
> reading an inconsistent snapshot there?
> Would it be recommended to always build applications that use WAL with
> SQLITE_THREADSAFE!=0?
>
>
> Best regards,
> --Edwin
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?

[Marco]

Excerpts from Daniel Polski's message of 2015-09-24 07:38:24 -0300:
> Hello!
> 
> --Table t1 contains some "base" data
> CREATE TABLE t1 (
>  idINTEGER PRIMARY KEY,
>  name VARCHAR(10),
>  value INT NOT NULL
> );
> 
> INSERT INTO t1 VALUES(1, 't1 1', 0);
> INSERT INTO t1 VALUES(2, 't1 2', 0);
> 
> --Table t2 can contain extra data for a row in table t1
> CREATE TABLE t2(
>  id INTEGER PRIMARY KEY,
>  t1_idINT NOT NULL,
>  dataINT NOT NULL,
>  CONSTRAINT 'FK_t2_001' FOREIGN KEY( t1_id ) REFERENCES t1 ( id )
> );
> INSERT INTO t2 VALUES(1, 1, 10);
> INSERT INTO t2 VALUES(2, 1, 20);
> INSERT INTO t2 VALUES(3, 2, 30);
> 
> CREATE TABLE t3 (
>  idINTEGER PRIMARY KEY,
>  name VARCHAR(10)
> );
> 
> CREATE TABLE t4(
>  t2_idINT NOT NULL,
>  t3_idINT NOT NULL,
>  CONSTRAINT 'FK_t4_001' FOREIGN KEY( t2_id ) REFERENCES t2 ( id ),
>  CONSTRAINT 'FK_t4_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id )
> );
> 
> 
> 
> -- Here is where my real question starts. Can I fetch a list of rows in 
> a trigger
> -- and update different tables from that data? Im looking for some kind
> -- of looping over the rows found in t2, where I can insert data to t3 and
> -- t4 for each row found in t2.
> 
> CREATE TRIGGER trigger_1 AFTER UPDATE OF value ON t1
> WHEN new.value = 1
> AND EXISTS (SELECT id FROM t2 WHERE t1_id = new.id)
> AND (some more rules)
> ...
> -- For each row in t2 with a t1_id referencing the t1 id,
> -- 1: insert a row into t3
> -- 2: insert a row into t4 binding the newly created row in t3 to the 
> corresponding row in t2

may try this to insert into t3.

CREATE TRIGGER trigger_on_table_1 
  AFTER UPDATE OF value ON t1
  FOR EACH ROW WHEN NEW.VALUE = 1 AND EXISTS (SELECT id FROM te WHERE
  ti_id = NEW.id)
  BEGIN

-- SELECT THE ROWS FROM t2 to insert values
INSERT INTO t3 VALUES(id, name) SELECT t2.t1_id, 'dummy' FROM t2
WHERE t2.t1_id == NEW.id;

  END;

Then you may wish to set a trigger to t4 itself to handle the newly
created t3 rows.

-- 
Marco Arthur @ (M)arco Creatives

[sqlite] Fwd: Outdated section of docs?

[Keith Medcalf]

On Thursday, 24 September, 2015 03:36, Dave McKee  
said:

> Hmmm... actually, looks like Windows 10 has regressed back to only having
> one set of DST rules...

Windows at Vista and later can have an (unlimited) set of DST rules for each 
Microsoft Time Zone (Microsoft Time Zone's can only be loosely translated into 
"standard" time zones).

However, you are mixing up the HAVING with the USING.  While Windows versions 
at or later than Vista have the provision to store more than a single 
transition rule in the registry, it only uses one at a time, and that one is 
NOT based on applicability -- it chooses the latest set of rules in effect 
TODAY, and uses that one rule to translate all past and future datetimes.  This 
is well documented by Microsoft.

If you need to do datetime handling on Windows you will need to store all 
datetime data in UT1 and use third-party code to handle the translations to and 
from localtime (for all datetime's that are not "now", as that is the only one 
Microsoft keeps correct), or otherwise use the registry data to do your own 
translations.

> Manually changed timezone to Pacific US.  Results are from before reboot
> but don't seem to have changed after.
> 
> David.
> 
> Windows 10
> Dates from https://www.timeanddate.com/time/dst/1985.html
> Using sqlite-shell-win32-x86-3081101.zip
> --
> 2012: Sunday, 11 March
> sqlite> select datetime("2012-03-10T12:00", 'localtime');
> 2012-03-10 04:00:00
> sqlite> select datetime("2012-03-11T12:00", 'localtime');
> 2012-03-11 05:00:00
> 
> 2007: Sunday, 11 March
> sqlite> select datetime("2007-03-10T12:00", 'localtime');
> 2007-03-10 04:00:00
> sqlite> select datetime("2007-03-11T12:00", 'localtime');
> 2007-03-11 05:00:00
> 
> 2006: Sunday, 2 April
> sqlite> select datetime("2006-04-01T12:00", 'localtime');
> 2006-04-01 05:00:00
> sqlite> select datetime("2006-04-02T12:00", 'localtime');
> 2006-04-02 05:00:00
> sqlite> select datetime("2006-04-03T12:00", 'localtime');
> 2006-04-03 05:00:00
> 
> sqlite> select datetime("2006-03-11T12:00", 'localtime');
> 2006-03-11 04:00:00
> sqlite> select datetime("2006-03-12T12:00", 'localtime');
> 2006-03-12 05:00:00
> 
> 1985: Sunday, 28 April
> sqlite> select datetime("1985-03-09T12:00", 'localtime');
> 1985-03-09 04:00:00
> sqlite> select datetime("1985-03-10T12:00", 'localtime');
> 1985-03-10 05:00:00
> 
> 
> On 22 September 2015 at 17:32, Dave McKee  wrote:
> 
> >
> > -- Forwarded message --
> > From: Jonathan Moules 
> > Date: Mon, Sep 21, 2015 at 5:31 PM
> > Subject: [sqlite] Outdated section of docs?
> > To: sqlite-users at mailinglists.sqlite.org
> >
> >
> > Hi,
> > I was reading this page (http://sqlite.org/lang_datefunc.html), and at
> > the very bottom it says:
> > /
> > /
> >
> >/"Non-Vista Windows platforms only support one set of DST rules.
> >Vista only supports two. Therefore, on these platforms, historical
> >DST calculations will be incorrect. For example, in the US, in 2007
> >the DST rules changed. Non-Vista Windows platforms apply the new
> >2007 DST rules to all previous years as well. Vista does somewhat
> >better getting results correct back to 1986, when the rules were
> >also changed."/
> >
> >
> > I wonder if that's a section that was written years ago, and the bits
> that
> > apply to Vista also apply to the Windows releases since then? I don't
> know
> > enough about Windows Timezone things to be able to find out easily but
> this
> > reads like it was written back in the era of Vista and probably holds
> for
> > newer releases too.
> > Cheers,
> > Jonathan
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users