[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-13 Thread Olivier Mascia
Thanks.  I'm reading you with attention.

> Clemens:
> Please note that transactions work on the connection level.

That was clear.

> Simon:
> Which, of course, decreases the point of you having competing threads in the 
> first place.  Which is related to the FAQ pointing at 
> .

Those threads which I deal with are there for other purposes than accessing the 
DB, but they happen to have. Besides, the issue is not at first about those 
threads but about the SQLite API which quite clearly *could* easily know and 
keep within the statement state information, the count of changes and the last 
inserted rowid, which could then in turn be happily queried by the application. 
Keeping them at the connection level is essentially useless (since they could 
be wrong or undefined) except in very restrictive conditions. I just would not 
like to fix holes in the road by asking people to stop driving cars. :)

Reading: https://www.sqlite.org/threadsafe.html the default for SQLite, unless 
compiled differently is serialized which means that "SQLite can be safely used 
by multiple threads with no restriction". It obviously implies more contention 
than transaction isolation should dictate, but it states a clear contract for 
the application developer. Contract which is very quickly violated by API like 
last_insert_rowid() and changes() reporting on the connection level rather than 
statement level to the extent of having to document their inability to report 
any useful result in a multi-threaded configuration. 

> Keith:
> INSERT INTO table VALUES ('somedata');
> SELECT rowid FROM table WHERE data='somedata';

My question cited last_insert_rowid and changes. Regarding last_insert_rowid() 
the whole purpose was indeed to know what rowid got assigned by an insert, 
without having to run yet another query for that, not to mention that in a not 
fully normalized world, there might not be other unique combination of values 
to uniquely select the just inserted row, except its rowid (or whatever else 
integer primary key it uses).

Other SQLs have INSERT ... RETURNING ..., solving that case very effectively 
(as the answer is known and does not need to be looked up again after the 
facts).

As I wrote, UPDATE OR INSERT has value when needing to update some attributes 
of a row, if it exist (WHERE clause) and INSERT a new row, when not found. The 
behavior cannot be mimicked by INSERT OR REPLACE which, if it happens to have 
to REPLACE does just that: substitute the whole row (DELETing it, INSERTing 
it).  Doesn't allow ? and that's expected for that statement ? for partial 
update of a row.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om




[sqlite] Bug with DATETIME('localtime')

2015-12-13 Thread Scott Robison
On Sun, Dec 13, 2015 at 6:44 PM, Keith Medcalf  wrote:

> We have been doing daylight savings changes to and from twice a year for
> as long as I remember (that is more than 100 times) and we still cannot
> manage to do it properly.  Leap years have been occurring for a long time
> and somehow we still manage to get that wrong too.  Multiple timezones have
> existed for a long time and we cannot get that right either.  In
> combination with Daylight Savings Rules, Timezones, and the want of
> Politicians to fiddle with daylight savings time completely arbitrarily,
> the situation is indeed grim.


> As to whether they could be incorporated into SQLite, that is
> interesting.  Overall the manipulations are rather simple -- the problem is
> the database of tz data (the IANA aka Olsen database).  Of course, the data
> could be stored in the database itself, but this raises the issue of having
> UDFs access the database.  I suppose it would not be too difficult (or too
> large) to load the entire database at extension load time -- the entire
> IANA database of all known timezone/transition information is just slightly
> larger than 512KB...
>

Not a horrible size for a modern desktop operating system, but certainly
not something you would want to foist onto smaller embedded systems.

Speaking of which, that reminds me of something I once heard:
>
> "On hearing the explanation of Daylight Savings Time, the old Indian Chief
> said with a shake of the head:  "Only a white man could believe that
> cutting a foot off the bottom of a blanket and sewing it on the top would
> make the blanket any longer...""
>

Amusing, though of course the intent is not make the day longer, just to
save the daylight in the early morning and use it in the evening. It's a
sliding window algorithm. :)

I'm just glad I didn't live in the "wild west" of DST. Since 1966 DST has
been pretty stable. A couple 'permanent' changes and some short term
changes at specific times. Prior to 1966, individual states and localities
were free to do whatever the heck they wanted, and it was confusing.

I haven't looked at the IANA database in depth, but I did download it so
that I could take a glance. It does include a lot of information, but it'll
never be complete. Complete enough for the needs of most people, of course,
who just care about adjusting UTC to legal local time for a general area,
but there are places (like West Wendover, NV) that have been effectively on
in the mountain time zone while legally in the pacific time zone (until
they were legally moved to mountain time in 1999). If you depend on the
timezone database it looks like you'll always get the "observed" time for
West Wendover, but if you want the legal time, there is no rule for that.

Note: I am not advocating yet more changes to the time database, especially
for such a little quirk. Just observing that if they have the most complete
time database (which even includes many state & locality specific rules for
the US during periods that the US didn't enforce an official start and end
date) but don't have everything, then it's likely impossible to ever be
100% accurate (or rather to get everyone to agree on what 100% accurate
really means).

We need a metric calendar. I propose redefining the second so that a day is
100,000 seconds long... ;)

-- 
Scott Robison


[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

2015-12-13 Thread a...@zator.com
I apologize for the mistake, but you must forget my last two posts (probably 
due the tiredness or the excitement of the moment).

In fact, the query "DELETE TABLE IF EXIST database_name.table_name" goes 
flawlessly, but indeed does not drop the table.

Sorry for the confusion.

--
Adolfo J. Millan



[sqlite] Bug with DATETIME('localtime')

2015-12-13 Thread Keith Medcalf

Or in python using pytz:

import datetime
import pytz

UTC = pytz.UTC
EST = pytz.timezone('Canada/Eastern')
MSK = pytz.timezone('Europe/Moscow')

tm = datetime.datetime(2014, 10, 25, 20, 0 ,0)
tm
tm = UTC.localize(tm)
tm
ms = MSK.normalize(tm.astimezone(MSK))
ms
es = EST.normalize(tm.astimezone(EST))
es

>>> import datetime
>>> import pytz
>>>
>>> UTC = pytz.UTC
>>> EST = pytz.timezone('Canada/Eastern')
>>> MSK = pytz.timezone('Europe/Moscow')
>>>
>>> tm = datetime.datetime(2014, 10, 25, 20, 0 ,0)
>>> tm
datetime.datetime(2014, 10, 25, 20, 0)
>>> tm = UTC.localize(tm)
>>> tm
datetime.datetime(2014, 10, 25, 20, 0, tzinfo=)
>>> ms = MSK.normalize(tm.astimezone(MSK))
>>> ms
datetime.datetime(2014, 10, 26, 0, 0, tzinfo=)
>>> es = EST.normalize(tm.astimezone(EST))
>>> es
datetime.datetime(2014, 10, 25, 16, 0, tzinfo=)


Or using my dt library which provides some simple wrappers and formatting 
functions (uses pytz Olsen/IANA database also):

import dt
tm = dt.localize('2014-10-25 20:00:00', dt.utc)
tm
dt.convert(tm, 'Europe/Moscow')
dt.convert(tm, 'Canada/Eastern')

>>> import dt
>>> tm = dt.localize('2014-10-25 20:00:00', dt.utc)
>>> tm
datetime.datetime(2014, 10, 25, 20, 0, tzinfo=)
>>> dt.convert(tm, 'Europe/Moscow')
datetime.datetime(2014, 10, 26, 0, 0, tzinfo=)
>>> dt.convert(tm, 'Canada/Eastern')
datetime.datetime(2014, 10, 25, 16, 0, tzinfo=)

There are a number of extensions for python that make timezone manipulation 
easy.  pytz uses the Olsen (IANA) database.  There are others that use more or 
less accurate databases.  Arrow is a replacement datetime which uses the 
builtin datetime as a delegate.  There are plans to incorporate "proper" 
datetime/timezone handling as a native feature (it was delayed for the latest 
python 3.5 release).

ICU also provides library functions to do proper datetime/timezone handling, 
though I have not looked at it.

As you say, most TZ handling is pathetic.

We have been doing daylight savings changes to and from twice a year for as 
long as I remember (that is more than 100 times) and we still cannot manage to 
do it properly.  Leap years have been occurring for a long time and somehow we 
still manage to get that wrong too.  Multiple timezones have existed for a long 
time and we cannot get that right either.  In combination with Daylight Savings 
Rules, Timezones, and the want of Politicians to fiddle with daylight savings 
time completely arbitrarily, the situation is indeed grim.

As to whether they could be incorporated into SQLite, that is interesting.  
Overall the manipulations are rather simple -- the problem is the database of 
tz data (the IANA aka Olsen database).  Of course, the data could be stored in 
the database itself, but this raises the issue of having UDFs access the 
database.  I suppose it would not be too difficult (or too large) to load the 
entire database at extension load time -- the entire IANA database of all known 
timezone/transition information is just slightly larger than 512KB...

Speaking of which, that reminds me of something I once heard:

"On hearing the explanation of Daylight Savings Time, the old Indian Chief said 
with a shake of the head:  "Only a white man could believe that cutting a foot 
off the bottom of a blanket and sewing it on the top would make the blanket any 
longer...""


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Sunday, 13 December, 2015 19:00
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Bug with DATETIME('localtime')
> 
> On Thu, 10 Dec 2015 06:34:44 -0700
> "Keith Medcalf"  wrote:
> 
> > The only way to convert datetime data on windows is to use a
> > third-party package that does it properly, or write it yourself.
> > Using the WinAPI functions is equivalent to "writing it yourself"
> > because they do not actually do anything -- you have to manage all
> > the meaningful data and deal with the vagaries of the
> > under-documented closed proprietary function implementations in
> > windows (which Microsoft even admits do not work properly).
> 
> Keith, your answer was so disheartening that I was impelled to revisit
> where the C standard is on time zones.  I remembered it was messy, but
> thought it had surely been fixed.
> 
> It's not fixed, although gacial progress is being made.  Even though
> we've had the TZ database & Posix datetime functions since 1986, 30
> years later we're still struggling with it, and not only on Windows.
> 
> The C standard library defines functions for "local time", defined
> globally with tzset(3).  To work with two time zones (even if one of
> them is UTC) requires manipulating the TZ environment variable,
> and "setting the time" with tzset(3) for each each one.
> 
> The latest version of the tz database[1] incorporates changes that
> originated with NetBSD in 2014.  NetBSD introduced some new functions
> e.g. 

[sqlite] Bug with DATETIME('localtime')

2015-12-13 Thread Scott Robison
On Sun, Dec 13, 2015 at 5:00 PM, James K. Lowden 
wrote:

> On Thu, 10 Dec 2015 06:34:44 -0700
> "Keith Medcalf"  wrote:
>
> > The only way to convert datetime data on windows is to use a
> > third-party package that does it properly, or write it yourself.
> > Using the WinAPI functions is equivalent to "writing it yourself"
> > because they do not actually do anything -- you have to manage all
> > the meaningful data and deal with the vagaries of the
> > under-documented closed proprietary function implementations in
> > windows (which Microsoft even admits do not work properly).
>
> Keith, your answer was so disheartening that I was impelled to revisit
> where the C standard is on time zones.  I remembered it was messy, but
> thought it had surely been fixed.
>
> It's not fixed, although gacial progress is being made.  Even though
> we've had the TZ database & Posix datetime functions since 1986, 30
> years later we're still struggling with it, and not only on Windows.
>

The problem would be that SQLite could not depend on the presence of TZ
functions even if they were added to the standard:

1. SQLite generally avoids non ANSI C so as to be compatible with the
largest possible number of platforms. ANSI C (aka C89 or C90 for the ISO
version) will never be updated to add new requirements.

2. Let's say that that the next version of the C standard does add TZ
functionality. That functionality would almost certainly only be required
for hosted implementations. Freestanding implementations have a much
smaller set of requirements (they don't even require the *current* time
functions!), and are the types of implementations used in targeting all
these embedded devices that make SQLite (likely) the most deployed software
in the world.

-- 
Scott Robison


[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

2015-12-13 Thread a...@zator.com

Oops:

Sorry for the typo. I mean

"DROP TABLE IF EXISTS database_name.table_name"

--
Adolfo J. Millan





[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-13 Thread Keith Medcalf
On Sunday, 13 December, 2015 17:04, Olivier Mascia  wrote:

> Thanks.  I'm reading you with attention.

> > Clemens:
> > Please note that transactions work on the connection level.

> That was clear.

> > Simon:

> > Which, of course, decreases the point of you having competing threads in
> the first place.  Which is related to the FAQ pointing at
> .
> 
> Those threads which I deal with are there for other purposes than
> accessing the DB, but they happen to have. Besides, the issue is not at
> first about those threads but about the SQLite API which quite clearly
> *could* easily know and keep within the statement state information, the
> count of changes and the last inserted rowid, which could then in turn be
> happily queried by the application. Keeping them at the connection level
> is essentially useless (since they could be wrong or undefined) except in
> very restrictive conditions. I just would not like to fix holes in the
> road by asking people to stop driving cars. :)

The documentation for sqlite3_last_insert_rowid clearly states that it returns 
the rowid last inserted on the connection, as does the documentation for 
sqlite3_changes.  While I agree that it may very well be possible to maintain 
the data by statement, that is not what the current functions as documented do.

Perhaps you can/might want to request an enhancement?

> Reading: https://www.sqlite.org/threadsafe.html the default for SQLite,
> unless compiled differently is serialized which means that "SQLite can be
> safely used by multiple threads with no restriction". It obviously implies
> more contention than transaction isolation should dictate, but it states a
> clear contract for the application developer. Contract which is very
> quickly violated by API like last_insert_rowid() and changes() reporting
> on the connection level rather than statement level to the extent of
> having to document their inability to report any useful result in a multi-
> threaded configuration.

You are misconstruing the serialization and what threadsafe means.  
"Threadsafe" means there is no distinction between threads and no per-thread 
state information maintained between calls into the engine.  Thus you can 
execute a select on one thread, and "step" on 10,000 different threads to each 
retrieve one row of the result set -- there is no isolation between threads -- 
only between connections.  "Serialized" means "side-by-each" (to use 
newfy-speak).  In other words, as if made from THE SAME THREAD.  The calls are 
interleaved so that only one occurs at a time, but that is it.  Serialized data 
access requires transactions.  Transactions are per connection.  Hence, 
multiple threads performing operations on the same connection (and thus are 
part of the same transaction) which change database state are not isolated from 
each other in any way.

> > Keith:
> > INSERT INTO table VALUES ('somedata');
> > SELECT rowid FROM table WHERE data='somedata';
> 
> My question cited last_insert_rowid and changes. Regarding
> last_insert_rowid() the whole purpose was indeed to know what rowid got
> assigned by an insert, without having to run yet another query for that,
> not to mention that in a not fully normalized world, there might not be
> other unique combination of values to uniquely select the just inserted
> row, except its rowid (or whatever else integer primary key it uses).

> Other SQLs have INSERT ... RETURNING ..., solving that case very
> effectively (as the answer is known and does not need to be looked up
> again after the facts).

> As I wrote, UPDATE OR INSERT has value when needing to update some
> attributes of a row, if it exist (WHERE clause) and INSERT a new row, when
> not found. The behavior cannot be mimicked by INSERT OR REPLACE which, if
> it happens to have to REPLACE does just that: substitute the whole row
> (DELETing it, INSERTing it).  Doesn't allow ? and that's expected for that
> statement ? for partial update of a row.

Then you will have to create your own mutex to control the contention you are 
creating on the database connection/transaction.






[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

2015-12-13 Thread a...@zator.com

>
>  Mensaje original 
> De: Dan Kennedy 
> Para:  sqlite-users at mailinglists.sqlite.org
> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 
> toFTS5modules(revisited)
>
> 
>
>Are you able to compile a debugging build of SQLite and set a breakpoint 
>in sqlite3CorruptError()?
>
>Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS 
>statement (pretty good chance), the stack trace will tell us more about 
>the form of corruption SQLite thinks it has found.
>
>

By the way, diggin in Google for solutions, I gave a try to the complete syntax 
for the DROP TABLE query, and has found that the form "DROP TABLE IF EXIST 
table_name.ftsm" goes flawlessly.

So:

- I've a workaround to the problem.

- I'm pretty sure that the problem isn't in my code.

HTH.

--
Adolfo J. Millan




[sqlite] Bug with DATETIME('localtime')

2015-12-13 Thread James K. Lowden
On Thu, 10 Dec 2015 06:34:44 -0700
"Keith Medcalf"  wrote:

> The only way to convert datetime data on windows is to use a
> third-party package that does it properly, or write it yourself.
> Using the WinAPI functions is equivalent to "writing it yourself"
> because they do not actually do anything -- you have to manage all
> the meaningful data and deal with the vagaries of the
> under-documented closed proprietary function implementations in
> windows (which Microsoft even admits do not work properly).

Keith, your answer was so disheartening that I was impelled to revisit
where the C standard is on time zones.  I remembered it was messy, but
thought it had surely been fixed.  

It's not fixed, although gacial progress is being made.  Even though
we've had the TZ database & Posix datetime functions since 1986, 30
years later we're still struggling with it, and not only on Windows.  

The C standard library defines functions for "local time", defined
globally with tzset(3).  To work with two time zones (even if one of
them is UTC) requires manipulating the TZ environment variable,
and "setting the time" with tzset(3) for each each one. 

The latest version of the tz database[1] incorporates changes that
originated with NetBSD in 2014.  NetBSD introduced some new functions
e.g. mktime_z(3) that add a timezone argument to the traditional time
functions of the C standard library.  This lets you allocate a timezone
variable for each zone you're interested in, and thereby to handle the
two time zones without touching global structures.  

I don't know the status of these functions beyond NetBSD.  From the
docs[2] they don't seem to have yet been incorporated in GNU libc.
Perhaps they would be possible to incorporate them in SQLite?  

Below are two programs -- one Posix, one NetBSD 7.0 -- that produce the
output the OP expects.  Neither one requires any heavy lifiting,
although I think you'll agree the second is more interesting.  

--jkl

[Posix]
#include 
#include 
#include 

int main(int argc, char *argv[] )
{
  // > 2014-10-25 20:00:00
  struct tm tm = { .tm_hour = 20,
   .tm_mday = 25,
   .tm_mon = 9,
   .tm_year = 114 };

  putenv("TZ=no time like the present");
  tzset();

  time_t time = mktime();
  printf( "time is %lu\n", (long)time );

  struct tm *greenwich_tm = gmtime();

  printf( "time in %s is %s",
  greenwich_tm->tm_zone, asctime(greenwich_tm) );

  putenv("TZ=:Europe/Moscow");
  tzset();

  struct tm *moscow_tm;
  moscow_tm = localtime();

  printf( "time in %s is %s",
  moscow_tm->tm_zone, asctime(moscow_tm) );

  return EXIT_SUCCESS;
}
[xisoP]

make && ./moscow
c99 -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -g -o moscow main.c
time is 1414267200
time in GMT is Sat Oct 25 20:00:00 2014
time in MSK is Sun Oct 26 00:00:00 2014


[NetBSD]
#include 
#include 
#include 

int main(int argc, char *argv[] )
{
  // > 2014-10-25 20:00:00
  struct tm tm = { .tm_hour = 20,
   .tm_mday = 25,
   .tm_mon = 9,
   .tm_year = 114 };

  time_t time = mktime_z(NULL, );
  printf( "time is %lu\n", (long)time );

  struct tm *greenwich_tm = gmtime();

  timezone_t moscow_tz = tzalloc("Europe/Moscow");
  const char *name = tzgetname(moscow_tz, 1);
  printf("Moscow time zone name is '%s'\n", name);

  struct tm *moscow_tm, data;
  moscow_tm = localtime_rz(moscow_tz, , );

  printf( "time in %s is %s",
  greenwich_tm->tm_zone, asctime(greenwich_tm) );

  printf( "time in %s is %s",
  moscow_tm->tm_zone, asctime(moscow_tm) );

  tzfree(moscow_tz);

  return EXIT_SUCCESS;
}
[DSBteN]

make && ./moscow
c99 -g -o moscow main.c
time is 1414267200
Moscow time zone name is 'MST'
time in GMT is Sat Oct 25 20:00:00 2014
time in MSK is Sun Oct 26 00:00:00 2014

[1] https://www.iana.org/time-zones/repository/tz-link.html

[2]https://www.gnu.org/software/libc/manual/html_node/Broken_002ddown-Time.html#Broken_002ddown-Time


[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-13 Thread Simon Slavin

On 13 Dec 2015, at 5:34pm, Clemens Ladisch  wrote:

> Olivier Mascia wrote:
>> should the design of competing threads revolve around each one having
>> a distinct connection handle?
> 
> Yes.

Which, of course, decreases the point of you having competing threads in the 
first place.  Which is related to the FAQ pointing at



Simon.


[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-13 Thread Clemens Ladisch
Olivier Mascia wrote:
> even if using two distinct sqlite3_stmt* handles (linked to a same
> sqlite3* handle), two competing threads would get unusable answers
> from both these API.

Yes.

Please note that transactions work on the connection level.

> should the design of competing threads revolve around each one having
> a distinct connection handle?

Yes.


Regards,
Clemens


[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-13 Thread Olivier Mascia
Dear all,

Why isn't there some:

sqlite3_int64 sqlite3_stmt_last_insert_rowid(sqlite3_stmt*);
int sqlite3_stmt_changes(sqlite3_stmt*);

in addition to these:

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);
int sqlite3_changes(sqlite3*);

Reading this:

"If a separate thread makes changes on the same database connection while 
sqlite3_changes() / sqlit3_last_insert_rowid() is running then the value 
returned is unpredictable and not meaningful."

I interpret that (I may be wrong): even if using two distinct sqlite3_stmt* 
handles (linked to a same sqlite3* handle), two competing threads would get 
unusable answers from both these API.

Wouldn't it be more useful to have this info restricted to some statement 
handle?

Or should the design of competing threads revolve around each one having a 
distinct connection handle?

Reason for asking: I was about to consider using sqlite3_changes() after some 
specific UPDATEs (returning 0 changes) to implement an UPDATE OR INSERT similar 
to some other SQL engines offer. But as far as I understand from these API 
descriptions, it looks like I would have to manage a distinct single connection 
per thread, instead of sharing the connection and using only distinct statement 
handles.

I'm really new to SQLite and still need to learn a lot from source code, so I 
can't yet appreciate if sharing connection handle between threads is 
interesting or if a model where each thread have all their private handles is a 
better world, without major performance impact.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om




[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-13 Thread Keith Medcalf

On Sun 2015-12-13 13:47, Simon Slavin  wrote:
> On 13 Dec 2015, at 5:34pm, Clemens Ladisch  wrote:
> > Olivier Mascia wrote:

> >> should the design of competing threads revolve around each one having
> >> a distinct connection handle?

> > Yes.

> Which, of course, decreases the point of you having competing threads in
> the first place.  Which is related to the FAQ pointing at

> 

That fellow merely points out that coders who do not know what they are doing 
should not attempt to use multiple threads.  
Proper use of multiple threads of concurrent execution requires a programmer 
versed in concurrency.

Only co-operating threads can share a single connection betwixt themselves and 
then only for use for the purpose for which they are co-operating.  Competing 
threads will need separate connections per thread.

In other words, if execution in one thread can change the state seen by another 
thread "in undesirable ways" then you made a design error by classifying 
obviously "competing" uses of "shared state" improperly as "co-operating" uses 
of "shared state", in this case a database connection.

You can either (a) stop sharing state; (b) make the sharing of state moot by 
not depending on it.  In this case (b) would mean using a select to get the 
rowid for the target row and not using a function which accesses shared state.  
(a) could mean assigning a separate connection per thread or it could mean 
creating a mutex that serializes "insert and get last insert rowid" operations 
so that only one at a time may affect the "state" of the connection.  (assuming 
that your shared connection does not introduce any other anomalies for which 
you have failed to account).

In other words:

INSERT INTO table VALUES ('somedata');
SELECT LAST_INSERT_ROWID();

presumes that no other thread is permitted to execute *ANY* operation affecting 
the last_insert_rowid between the two statements.  This can be guaranteed by 
not allowing other "things" to issue update/inserts on the connection:

block_waiting_for_mutex('INSERT AND GET ROWID')
INSERT INTO table VALUES ('somedata');
SELECT LAST_INSERT_ROWID();
release_mutex('INSERT AND GET ROWID')


Alternatively,  if you use something like:

INSERT INTO table VALUES ('somedata');
SELECT rowid FROM table WHERE data='somedata';

will always work properly since it is not dependent on shared state (assuming 
that your database is properly normalized, or course).







[sqlite] Checking Foreign Keys

2015-12-13 Thread R Smith


On 2015/12/13 2:17 PM, Cecil Westerhof wrote:
> I am continuing with my exploration of SQLite. :-)
>
> At the moment I am working with Foreign Keys. They need to be enabled. When
> you do not do this it is possible to enter records that break the Foreign
> Key rules. Is there a way to check for this.
>
> For example in a session where Foreign Keys where not enabled I did the
> first INSERT from:
>  https://www.sqlite.org/foreignkeys.html
>
> When opening the database in a session with Foreign Keys enabled, is there
> a method to find this record that breaks the rules?

No.

Foreign Key checks only happen on data changes. I don't think there 
would be a point of turning off FK checks, adding non-relation data to 
the tables, then turning it back on, and simply get a permanent error 
condition.

You could just re-insert or update the records, which should then break 
once FK checking is turned on. This next script demonstrates:

CREATE TABLE t (
   ID INTEGER PRIMARY KEY,
   Name TEXT
);

CREATE TABLE ct (
   ID INTEGER PRIMARY KEY,
   tID INTEGER REFERENCES t(ID) ON DELETE RESTRICT ON UPDATE CASCADE
);

PRAGMA foreign_keys=0;

INSERT INTO t (Name) VALUES ('John'), ('Jane'), ('Joe');

INSERT INTO ct (tID) VALUES (1), (2), (5);  -- 5 is an error but 
succeeds here since FK=Off

PRAGMA foreign_keys=1;

UPDATE ct SET tID=tID WHERE 1;  -- This fails because of the 5


There is however no way of knowing which specific record caused the 
failure if you are group-updating anything. The reason for that was 
discussed at length some weeks ago on this forum, but basically the 
engine "counts" the foreign key violations and then counts back down as 
they get resolved throughout a transaction. If the end result is "Zero" 
violations, the transaction succeeds, if however there are one or more 
outstanding violations, it fails. There is no reason or rhyme to keeping 
record of which one (or more) of the many possible constraints were 
violated. (This might number in the millions on large tables).




[sqlite] Checking Foreign Keys

2015-12-13 Thread Simon Slavin

On 13 Dec 2015, at 12:52pm, R Smith  wrote:

> I don't think there would be a point of turning off FK checks, adding 
> non-relation data to the tables, then turning it back on, and simply get a 
> permanent error condition.

Agreed.  There is a reason to turn the checks off, however.  Suppose you want 
to change the rows and columns of a table.  Because SQLite lacks "ALTER TABLE 
DROP COLUMN" and "ALTER TABLE RENAME COLUMN" commands you have to create a new 
table and eventually move all references to it.  You can'd do that without the 
ability to disable FOREIGN KEYs for a while.

Simon.


[sqlite] Set update time, but not always

2015-12-13 Thread R Smith

On 2015/12/13 1:31 PM, Cecil Westerhof wrote:
> I have a table where I would most of the time update a field lastChecked to
> current_date when I update the record. But it is possible that I sometimes
> want to update a record without updating lastChecked. Is this possible, or
> should I update it (almost) always manually?

Not sure what you mean by this...  You can either have a field update 
automatically, or manually. There is no "sometimes" update automatically.

If you can define another field or table or some way of specifying 
whether the date updating should happen, you could use an ON-UPDATE 
trigger to update the row's lastChecked when some other queryable value 
is TRUE - but in my experience it is much easier in this case to simply 
have your program code decide and then add the date update bit to the 
update query when needed.

Also - SQLite doesn't have a MySQL-esque "on_update_current_datetime" 
specification for a column - it has to be a trigger, though I have used 
DEFAULT values that set current date/time with success - like this:

CREATE TABLE t (
a INT,
b NUMERIC DEFAULT (datetime('now','localtime'))
);

INSERT INTO t (a) VALUES (1), (2);

SELECT * FROM t;

  a |  b
---|-
  1 | 2015-12-13 14:17:36
  2 | 2015-12-13 14:17:36




[sqlite] Checking Foreign Keys

2015-12-13 Thread Cecil Westerhof
2015-12-13 13:53 GMT+01:00 Dominique Devienne :

> On Sun, Dec 13, 2015 at 1:17 PM, Cecil Westerhof 
> wrote:
>
> > At the moment I am working with Foreign Keys. They need to be enabled.
> When
> > you do not do this it is possible to enter records that break the Foreign
> > Key rules. Is there a way to check for this.
> >
> > For example in a session where Foreign Keys where not enabled I did the
> > first INSERT from:
> > https://www.sqlite.org/foreignkeys.html
> >
> > When opening the database in a session with Foreign Keys enabled, is
> there
> > a method to find this record that breaks the rules?
>
>
> https://www.sqlite.org/pragma.html#pragma_foreign_key_check
>

?That is very interesting information. Thanks.

-- 
Cecil Westerhof


[sqlite] Checking Foreign Keys

2015-12-13 Thread Cecil Westerhof
2015-12-13 13:52 GMT+01:00 R Smith :

>
>
> On 2015/12/13 2:17 PM, Cecil Westerhof wrote:
>
>> I am continuing with my exploration of SQLite. :-)
>>
>> At the moment I am working with Foreign Keys. They need to be enabled.
>> When
>> you do not do this it is possible to enter records that break the Foreign
>> Key rules. Is there a way to check for this.
>>
>> For example in a session where Foreign Keys where not enabled I did the
>> first INSERT from:
>>  https://www.sqlite.org/foreignkeys.html
>>
>> When opening the database in a session with Foreign Keys enabled, is there
>> a method to find this record that breaks the rules?
>>
>
> No.
>
> Foreign Key checks only happen on data changes. I don't think there would
> be a point of turning off FK checks, adding non-relation data to the
> tables, then turning it back on, and simply get a permanent error condition.
>

?I agree, but you never know what someone else is doing.
?
-- 
Cecil Westerhof


[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

2015-12-13 Thread a...@zator.com
>
>  Mensaje original 
> De: Dan Kennedy 
> Para:  sqlite-users at mailinglists.sqlite.org
> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 
> toFTS5modules(revisited)
>
>>>
 1a.- Delete the previous table.
 DROP TABLE IF EXIST ftsm

 This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives 
 an erroro in 1a:  "database disk image is malformed".

 Note that in previous attemps I believed that the problem was into try to 
 delete a ftsm table build with the previous modules, but the error happen 
 when trying delete a table build with the FTS5 module.

 ...
>> Some clues?
> Not really sure why it might fail there. Can you post the entire
> database schema (results of "SELECT * FROM sqlite_master" or the output
> of the .schema shell tool command)?
>
 Dan:

 Here is the schema:
>>> That all looks Ok.
>>>
>>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>>> command does it report an error as well?
>>>
>> Dan:
>>
>> Surprisingly, the shell does not complain when using the same query, and 
>> indeed, drop the table.
>>
>> Yeah, I also believe that the problem is in my roof, although that code has 
>> been behaving Ok from ages with the FTS3/4 modules (only changes some 
>> directives in other places of the code).
>>
>> Any way, when running again the code when the table has been previously 
>> erased (with the shell), the routine works without complaint (executes the 
>> query correctly), and despite the warning, the rest of the app behaves 
>> correctly and the queries related with the search works fine.
>>
>> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, 
>> but sqlite3_step() ends with error 11.
>
>Are you able to compile a debugging build of SQLite and set a breakpoint 
>in sqlite3CorruptError()?
>
>Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS 
>statement (pretty good chance), the stack trace will tell us more about 
>the form of corruption SQLite thinks it has found.
>

Dan:

I managed a breakpoint int the requested function, and can tell that it has 
been called twice inside the sqlite3_step() function before it returns.

Here you have the call's stack:

First call of sqlite3CorrupError()  lineno == 56209

sqlite3CorruptError(int lineno) Line 133961
decodeFlags(MemPage * pPage, int flagByte) Line 56209
btreeInitPage(MemPage * pPage) Line 56251
getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, BtCursor 
* pCur, int bReadOnly) Line 56495
clearDatabasePage(BtShared * pBt, unsigned int pgno, int freePageFlag, int * 
pnChange) Line 62907
sqlite3BtreeClearTable(Btree * p, int iTable, int * pnChange) Line 62970
btreeDropTable(Btree * p, unsigned int iTable, int * piMoved) Line 63028
sqlite3BtreeDropTable(Btree * p, int iTable, int * piMoved) Line 63111
sqlite3VdbeExec(Vdbe * p) Line 77954
sqlite3Step(Vdbe * p) Line 71546
sqlite3_step(sqlite3_stmt * pStmt) Line 71608
sqlite3_exec(sqlite3 * db, const char * zSql, int (void *, int, char * *, char 
* *) * xCallback, void * pArg, char * * pzErrMsg) Line 103955
fts5ExecPrintf(sqlite3 * db, char * * pzErr, const char * zFormat, ...) Line 
180863
sqlite3Fts5DropAll(Fts5Config * pConfig) Line 180876
fts5DestroyMethod(sqlite3_vtab * pVtab) Line 178532
sqlite3VtabCallDestroy(sqlite3 * db, int iDb, const char * zTab) Line 117587
sqlite3VdbeExec(Vdbe * p) Line 79084
sqlite3Step(Vdbe * p) Line 71546
sqlite3_step(sqlite3_stmt * pStmt) Line 71608

Secon call of sqlite3CorrupError() lineno == 56251

sqlite3CorruptError(int lineno) Line 133961
btreeInitPage(MemPage * pPage) Line 56251
getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, BtCursor 
* pCur, int bReadOnly) Line 56495
...
Rest the same...

Note that the line numbers can be slightly greater than the ones in the 
original file (SQLite 3.9.1) because the inclusion of some comments.

As I can reproduce the problem as needed, In case of interest, I can try to 
provide any intermediate value inside the stack.

Thanks again for yours attention.

--
Adolfo J. Millan




[sqlite] Checking Foreign Keys

2015-12-13 Thread Dominique Devienne
On Sun, Dec 13, 2015 at 1:17 PM, Cecil Westerhof 
wrote:

> At the moment I am working with Foreign Keys. They need to be enabled. When
> you do not do this it is possible to enter records that break the Foreign
> Key rules. Is there a way to check for this.
>
> For example in a session where Foreign Keys where not enabled I did the
> first INSERT from:
> https://www.sqlite.org/foreignkeys.html
>
> When opening the database in a session with Foreign Keys enabled, is there
> a method to find this record that breaks the rules?


https://www.sqlite.org/pragma.html#pragma_foreign_key_check --DD


[sqlite] Set update time, but not always

2015-12-13 Thread Cecil Westerhof
2015-12-13 13:18 GMT+01:00 R Smith :

>
> On 2015/12/13 1:31 PM, Cecil Westerhof wrote:
>
>> I have a table where I would most of the time update a field lastChecked
>> to
>> current_date when I update the record. But it is possible that I sometimes
>> want to update a record without updating lastChecked. Is this possible, or
>> should I update it (almost) always manually?
>>
>
> Not sure what you mean by this...  You can either have a field update
> automatically, or manually. There is no "sometimes" update automatically.
>

?That was what I thought, but it never hurts to verify.?




> If you can define another field or table or some way of specifying whether
> the date updating should happen, you could use an ON-UPDATE trigger to
> update the row's lastChecked when some other queryable value is TRUE - but
> in my experience it is much easier in this case to simply have your program
> code decide and then add the date update bit to the update query when
> needed.
>

?That was what I was thinking: in all the statements where it is required I
add:
lastChecked = CURRENT_DATE?

?and the one situation it is not needed I do not add it.


Also - SQLite doesn't have a MySQL-esque "on_update_current_datetime"
> specification for a column - it has to be a trigger, though I have used
> DEFAULT values that set current date/time with success - like this:
>
> CREATE TABLE t (
> a INT,
> b NUMERIC DEFAULT (datetime('now','localtime'))
> );
>


?I use something like that already, but sligth?ly different, because I only
need the date:
entered TEXTDEFAULT CURRENT_DATE

-- 
Cecil Westerhof


[sqlite] Checking Foreign Keys

2015-12-13 Thread Cecil Westerhof
I am continuing with my exploration of SQLite. :-)

At the moment I am working with Foreign Keys. They need to be enabled. When
you do not do this it is possible to enter records that break the Foreign
Key rules. Is there a way to check for this.

For example in a session where Foreign Keys where not enabled I did the
first INSERT from:
https://www.sqlite.org/foreignkeys.html

When opening the database in a session with Foreign Keys enabled, is there
a method to find this record that breaks the rules?

-- 
Cecil Westerhof


[sqlite] Set update time, but not always

2015-12-13 Thread Cecil Westerhof
I have a table where I would most of the time update a field lastChecked to
current_date when I update the record. But it is possible that I sometimes
want to update a record without updating lastChecked. Is this possible, or
should I update it (almost) always manually?

-- 
Cecil Westerhof


[sqlite] website documentation wording

2015-12-13 Thread Mohit Sindhwani
On 5/12/2015 2:08 AM, Keith Medcalf wrote:
> Well, a Gartner Report paid for by Microsoft, which said that if you 
> pronounced it "ess queue ell" you were labelling yourself as a professional 
> programmer who understood relational database technologies, had probably used 
> them since the 1970's or before, and belonged in a dinosaur pen.
>
> On the other hand, the modern "buzzword compliant" pronunciation for "mere 
> coders" that had no knowledge of how relational database systems worked and 
> could not distinguish Boyce-Codd Normal Form from an Eggplant was "sequel".

Well, at least as far as SQLite goes, I think this settles it 
unambiguously for me:
https://www.youtube.com/watch?v=giAMt8Tj-84

Best Regards,
Mohit.