Re: [sqlite] How to convert SQL file into database when a column value is Inf?

[skywind mailing lists]

Hi Ryan,

I just became aware that I might give synchronisation level 3 a chance 
(currently the level is 1, default setting).  I was not aware of this setting 
since a couple of minutes ago. Otherwise, the database is in serialised 
threading mode, though only one thread is accessing it. The operation system is 
iOS (therefore, it is difficult to remove the battery and do several tests 
after each other) and the database is opened with read-write access and no 
further options used but the default ones. The old style journal mode is used. 
I could never reproduce it by myself but as I said with iOS it is difficult to 
do a lot of tests. The incident occurs one or two times a year at different 
customers.

I do not own the database therefore I am not able to share it publicly. I might 
get the permission to share it privately.

Regards,
Hartwig

> Am 2018-06-13 um 21:32 schrieb R Smith :
> 
> On 2018/06/13 6:35 PM, skywind mailing lists wrote:
>> Hi Ryan,
>> 
>> my problem is that I use the "most safest" mode that exists for SQLite and 
>> it still fails… Therefore, I need to know why it fails.
> 
> Alright, but this implies a very serious flaw in SQLite, so mind if we 
> double-check some things?
> 
> - What exactly is the "most safest" mode that you use?
> - May we see the full schema?,
> - May we see the initialization calls (pragma calls esp.) when opening the DB 
> file?,
> - What is the specific file-system and file-locking methods it supports?
> - Can you reproduce this error by removing the phone battery mid-write? (or 
> perhaps by any other service interruption?)
> - If so, can you reproduce it with a DB with fake data (to protect privacy) 
> and then send us the broken DB file?
> - if unable to reproduce it, how often have you had reports of it breaking? 
> And,
> - Any chance we can get such a broken DB? (If the data is sensitive, perhaps 
> only send it to Richard).
> 
> "Why it fails" is easy - it fails because it has data half-written during 
> power cut in a way that puts it out of sync with the schema, but then the 
> "most safest" journal modes actually protect against that, so if you use the 
> correct modes and it still fails, it means either SQLite is broken or your 
> implementation is doing something weird (such as non-standard file-locking 
> mechanism, or the OS lying about data being committed to storage) or you 
> might be assuming something that might not work as you expect, and we can 
> only learn which of these it is when having the above information.
> 
> Let us know,
> Ryan
> 
> 
> ___
> 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] How to convert SQL file into database when a column value is Inf?

[skywind mailing lists]

Hi Ryan,

my problem is that I use the "most safest" mode that exists for SQLite and it 
still fails… Therefore, I need to know why it fails.

Regards,
Hartwig

> Am 2018-06-13 um 01:23 schrieb R Smith :
> 
> 
> On 2018/06/13 12:21 AM, skywind mailing lists wrote:
>> Hi,
>> 
>> the original database is malformed. So, I cannot access it anymore besides 
>> doing a dump.
> 
> There is currently no known way to read this since Inf and -Inf are not 
> recognized as floats but in stead look like identifiers. Perhaps this is a 
> useful enhancement to SQLite - but either way, right now your best bet is 
> indeed to search-replace the entire text file.
> 
> As to your other question about why it is malformed - it simply means that 
> the schema doesn't match the data structure/layout/constraints anymore and 
> sqlite can't fix it and can't even know what all is wrong. Asking why 
> specifically is not useful.
> 
> It's like when you see two random people on the street and ask "Are they 
> brother and sister?".  If the answer is "No", there is no more that can be 
> said. It is not useful to ask "which one is not the brother?" or "why are 
> they not brother and sister?".
> 
> It's a silly analogy, but it's hard to think of a better one now - The schema 
> and data are simply not happy together. In most cases this would be because a 
> Unique key (perhaps Primary) got written halfway when disk access died and 
> now has a situation where it has duplicate values in the Key. It might 
> however also be a root page index that falls outside the file, or indeed a 
> myriad of possible things - or worse - a combination of things so that if it 
> reported "Index has duplicates" and you manage to fix that (assuming you know 
> some magic), then you will find the next index is broken, and then page isn't 
> where it should be, and... and... and... - so there really is no point in in 
> saying what is wrong, just to know that the DB is broken to the point where 
> SQLite knows it is broken, but doesn't know if any of it is still o.k. or 
> what all is specifically broken.
> 
> The corruptions may be vast and wide, but the reason is always singular - 
> There was a media write that failed to complete. That's all that needs to be 
> known, and to help prevent these things from happening, there is only one 
> good solution: choose more safe Journal modes. And yes, it may come at a 
> speed penalty.  (On phones specifically you can also avoid DB updates when 
> battery is low, but that is not a fool-proof solution. A user can yank out a 
> battery, etc.)
> 
> 
> I hope this helps to make sense of it somewhat, but I know none of it really 
> provides a solution, so apologies for that.
> cheers,
> Ryan
> 
> ___
> 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] How to convert SQL file into database when a column value is Inf?

[skywind mailing lists]

Hi Keith,

thanks for the suggestion.

Regards,
Hartwig

> Am 2018-06-13 um 00:26 schrieb Keith Medcalf :
> 
> 
> You can replace the "Inf" with 1e400 and -Inf with -1e400.  These values will 
> be parsed and stored as the appropriate plus/minus Infinity since they are 
> larger than the maximum representable IEEE-754 Double Precision Binary Float.
> 
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
> 
> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org] On Behalf Of skywind mailing lists
>> Sent: Tuesday, 12 June, 2018 16:06
>> To: SQLite mailing list
>> Subject: [sqlite] How to convert SQL file into database when a column
>> value is Inf?
>> 
>> Hi,
>> 
>> I issued the following commands:
>> 
>> echo .dump | sqlite3 Database.sldb > D.sldb
>> sqlite3 -init D.sldb NewDatabase.sldb
>> 
>> Unfortunately, reading the SQL file produced the following error:
>> Error: near line 56721: no such column: Inf
>> 
>> The corresponding SQL command is:
>> INSERT INTO "Flights"
>> VALUES(36,80,1470395806,'','','',NULL,0.0,0,0,0,0,0.0,-Inf,0.0,-
>> Inf,0.0,-
>> Inf,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,0,NULL,NULL,NULL
>> ,NULL,4,0,'');
>> 
>> OK, it seems to be that the column values are not really correct (-
>> Inf) but I would still like to do the conversion. Is there a
>> command/trick in sqlite3 that makes it work?
>> 
>> A workaround is of course to use a text editor and try to find and
>> replace all occurrences of Inf or -Info.
>> 
>> Regards,
>> Hartwig
>> 
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to convert SQL file into database when a column value is Inf?

[skywind mailing lists]

Hi,

the original database is malformed. So, I cannot access it anymore besides 
doing a dump.

Regards,
Hartwig

> Am 2018-06-13 um 00:17 schrieb Bob Friesenhahn :
> 
> On Wed, 13 Jun 2018, skywind mailing lists wrote:
>> 
>> A workaround is of course to use a text editor and try to find and replace 
>> all occurrences of Inf or -Info.
> 
> Can you use update queries on the original database to change the Inf and 
> -Inf values to huge positive or negative values that sqlite is willing to 
> consume?
> 
> The range of a double value is typically 2.225074e-308 to 1.797693e+308.
> 
> Bob
> -- 
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.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 is malformed but no further information

[skywind mailing lists]

Hi,

when I load my database into sqlite3 and run an integrity check I only get the 
error message: Error: database disk image is malformed

I do not get any further information. What causes this simple error message? I 
expected to get some more information what is actually the reason why SQLite3 
thinks that it is malformed. Interestingly I can do a dump to a SQL file for 
the (whole?) database.

I would really like to find out the root cause of this issue because I 
experience a corrupt database once a while on iOS. This only happens when the 
app is terminated while running when the iDevice switches off due to low 
battery issues.

Best regards,
Hartwig

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

[sqlite] How to convert SQL file into database when a column value is Inf?

[skywind mailing lists]

Hi,

I issued the following commands:

echo .dump | sqlite3 Database.sldb > D.sldb
sqlite3 -init D.sldb NewDatabase.sldb

Unfortunately, reading the SQL file produced the following error:
Error: near line 56721: no such column: Inf

The corresponding SQL command is:
INSERT INTO "Flights" 
VALUES(36,80,1470395806,'','','',NULL,0.0,0,0,0,0,0.0,-Inf,0.0,-Inf,0.0,-Inf,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,0,NULL,NULL,NULL,NULL,4,0,'');

OK, it seems to be that the column values are not really correct (-Inf) but I 
would still like to do the conversion. Is there a command/trick in sqlite3 that 
makes it work?

A workaround is of course to use a text editor and try to find and replace all 
occurrences of Inf or -Info.

Regards,
Hartwig

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

Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail

[skywind mailing lists]

Hi,

but to which directory should I set it? There is no general tmp directory 
accessible.

Regards,
Hartwig

> Am 2018-06-10 um 02:30 schrieb Bob Friesenhahn :
> 
> On Sat, 9 Jun 2018, skywind mailing lists wrote:
> 
>> Hi,
>> 
>> currently I am not creating large subqueries or views and therefore storing 
>> the temporary data in memory is a solution but I would like to have a future 
>> proof solution. And I do not like to think about it anymore in the future.
> 
> Have you tried setting the POSIX standard TMPDIR environment variable? This 
> might have useful influence under Android.
> 
> Bob
> -- 
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.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] Usage of temporary files of SQLite3 on Android / SQLite commands fail

[skywind mailing lists]

Hi,

currently I am not creating large subqueries or views and therefore storing the 
temporary data in memory is a solution but I would like to have a future proof 
solution. And I do not like to think about it anymore in the future.

Or can we stop deprecating sqlite3_temp_directory? Why was it at all 
deprecated? It seems to be (according to the comments) that there has been some 
trouble with assigning and releasing the variable but this can be overcome by 
not exposing it publicly and using an appropriate function instead.

Regards,
Hartwig

> Am 2018-06-09 um 19:16 schrieb Dan Kennedy :
> 
> On 06/10/2018 12:00 AM, skywind mailing lists wrote:
>> Hi,
>> 
>> what is your experience with temporary files requested by SQLite3 on 
>> Android? I am using SQLite3 natively on Android - I compiled SQLite3 by 
>> myself - and get an error when SQLite3 tries to create temporary files 
>> because:
>> 
>> - the directories
>>  /var/tmp
>>  /usr/tmp
>>  /tmp
>>  .
>>   are not accessible by a standard user (and therefore the app);
>> - getenv is not guaranteed to be supported (or?);
>> - sqlite3_temp_directory should not be used because it is deprecated.
>> 
>> What are you doing?
> 
> People usually end up building with:
> 
>   -DSQLITE_TEMP_STORE=3
> 
> This causes SQLite to use main memory for all temp files.
> 
> Dan.
> 
> 
> ___
> 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] Usage of temporary files of SQLite3 on Android / SQLite commands fail

[skywind mailing lists]

Hi,

what is your experience with temporary files requested by SQLite3 on Android? I 
am using SQLite3 natively on Android - I compiled SQLite3 by myself - and get 
an error when SQLite3 tries to create temporary files because:

- the directories 
/var/tmp
/usr/tmp
/tmp
.
  are not accessible by a standard user (and therefore the app);
- getenv is not guaranteed to be supported (or?);
- sqlite3_temp_directory should not be used because it is deprecated.

What are you doing?

Regards,
Hartwig

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

Re: [sqlite] INSERT OR REPLACE statement and triggers

[skywind mailing lists]

Hello,

thanks for the documentation references!

Regards,
Hartwig

> Am 2017-12-23 um 13:18 schrieb J. King <jk...@jkingweb.ca>:
> 
> The documentation states that in cases of replacement the DELETE trigger only 
> fires if recursive triggers are enabled. Recursive triggers are currently off 
> by default. 
> 
> <https://sqlite.org/lang_conflict.html>
> <https://sqlite.org/pragma.html#pragma_recursive_triggers>
> 
> On December 23, 2017 7:05:59 AM EST, R Smith <ryansmit...@gmail.com> wrote:
>> 
>> 
>> On 2017/12/23 1:57 PM, skywind mailing lists wrote:
>>> Hello,
>>> 
>>> I have checked the documentation but did not find an explicit answer
>> concerning my case. Of course I can test it but this does not mean that
>> the functionality is guaranteed also for future versions. Assume I have
>> an insertion statement like
>>> 
>>> INSERT OR REPLACE INTO TestTable VALUES(1,2);
>>> 
>>> Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the
>> table. Which triggers are triggered in case of
>>> 
>>> a) insertion (pretty obvious, should always be the INSERT trigger)
>>> b) replace (DELETE and INSERT trigger or UPDATE trigger or depends
>> on... .)
>> 
>> INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then 
>> INSERT" based on whether or not the Constraint/Key is violated, so you 
>> are guaranteed to have the INSERT trigger fire, but if the item did 
>> exist before, the DELETE trigger will fire first.
>> 
>> HTH,
>> Ryan
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> -- 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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] INSERT OR REPLACE statement and triggers

[skywind mailing lists]

Hello,

I have checked the documentation but did not find an explicit answer concerning 
my case. Of course I can test it but this does not mean that the functionality 
is guaranteed also for future versions. Assume I have an insertion statement 
like

INSERT OR REPLACE INTO TestTable VALUES(1,2);

Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the table. Which 
triggers are triggered in case of

a) insertion (pretty obvious, should always be the INSERT trigger)
b) replace (DELETE and INSERT trigger or UPDATE trigger or depends on... .)

Regards,
Hartwig

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

Re: [sqlite] Massively multithreaded SQLite queries

[skywind mailing lists]

Hi,

just my few cents: if you are using the RTree module I made very bad 
experiences running SQLite in parallel, even when running only two or three 
threads in parallel. In this case I use a single thread-safe queue that is 
handling all SQLite access.

Regards,
Hartwig

> Am 2017-03-04 um 16:54 schrieb James K. Lowden :
> 
> On Fri, 3 Mar 2017 20:13:52 +
> Andrew Brown  wrote:
> 
>> once we start running them on a large server with a lot of
>> multithreading going on, I find that we spend a lot of time in
>> __raw_spin_lock - perhaps 75%+ of the time (In one case, 87% of 350
>> seconds x 72 cores was spent in __raw_spin_lock). This is being run
>> on 64 and 72 core machines, and the more cores I run it on, the
>> slower it ends up going.
> 
> My initial reaction is that your 72 cores are apt to block on I/O
> contention.  Your description is consistent with that hypothesis.  
> 
> Whatever else you do, I recommend SQLightning.  It's a version of
> SQLite that uses a memory-mapped key-value store, LMDB.  It's designed
> for problems like yours, and demonstrated to sometimes be 10-100x
> faster.  
> 
> You don't mention RAM size.  Even if your data fit in memory, though,
> you still have to pass through the kernel to retrieve each row.
> Memory-mapped files bring the data into userspace.  
> 
> https://github.com/LMDB/sqlightning
> 
> --jkl
> ___
> 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] Bug in mkFullPathname

[skywind mailing lists]

Assume I have got a zPath like abc.def passed to mkFullPathname. Inside 
mkFullPathname zPath[0] != '/' will evaluate to true. Assume further that 
osGetcwd(..) will return '/' (which can actually be the case under Android and 
theoretically under other systems as well but probably less likely). In this 
case zout will contain finally "//abc.def". This is an invalid pathname, I 
believe.

Best regards,
Hartwig


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

Re: [sqlite] Strange SQLite bug(?!)

[skywind mailing lists]

Thanks!

In the meantime I found out that version 3.8.8.3 is still working while 3.9.0.0 
fails.

Regards,
Hartwig

> Am 2016-08-02 um 18:47 schrieb Dan Kennedy <danielk1...@gmail.com>:
> 
> On 08/02/2016 01:01 PM, skywind mailing lists wrote:
>> HI,
>> 
>> it seems to be that I am not allowed to add zip files to these e-mails.
> 
> Thanks for reporting this. Should now be fixed here:
> 
>  http://sqlite.org/src/info/e64a4173d2899acf 
> <http://sqlite.org/src/info/e64a4173d2899acf>
> 
> Dan.
> 
> 
> 
>> 
>> This is what I am doing to create the database:
>> 
>>  ExecuteStatement(databaseHandle,"CREATE TABLE A(ID INTEGER 
>> PRIMARY KEY,AnotherID INTEGER, Notes TEXT);");
>>  ExecuteStatement(databaseHandle,"CREATE INDEX A_1 ON A 
>> (AnotherID);");
>>  ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE AFTS 
>> USING FTS3 (Notes);");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER A_DeleteTrigger 
>> AFTER DELETE ON A FOR EACH ROW BEGIN DELETE FROM AFTS WHERE rowid=OLD.ID; 
>> END;");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER A_InsertTrigger 
>> AFTER INSERT ON A FOR EACH ROW BEGIN INSERT INTO AFTS (rowid,Notes) VALUES 
>> (NEW.ID,NEW.Notes); END;");
>>  ExecuteStatement(databaseHandle,"INSERT INTO A 
>> (AnotherID,Notes) VALUES(1,'Record A1');");
>>  
>>  ExecuteStatement(databaseHandle,"CREATE TABLE B(ID INTEGER 
>> PRIMARY KEY,Notes TEXT);");
>>  ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE BFTS 
>> USING FTS3 (Notes);");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER B_DeleteTrigger 
>> AFTER DELETE ON B FOR EACH ROW BEGIN DELETE FROM BFTS WHERE rowid=OLD.ID; 
>> END;");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER B_InsertTrigger 
>> AFTER INSERT ON B FOR EACH ROW BEGIN INSERT INTO BFTS (rowid,Notes) VALUES 
>> (NEW.ID,NEW.Notes); END;");
>>  ExecuteStatement(databaseHandle,"INSERT INTO B (Notes) 
>> VALUES('Record B1');");
>> 
>> To reproduce the error I run the following commands firstly with „#if 1“ and 
>> the second time with „#if 0":
>> 
>>  if (sqlite3_open_v2("Test.sldb",,SQLITE_OPEN_CREATE | 
>> SQLITE_OPEN_READWRITE,NULL) == SQLITE_OK)
>>  {
>>  ExecuteStatement(databaseHandle,"BEGIN TRANSACTION;");
>> 
>> #if 0
>>  ExecuteStatement(databaseHandle,"CREATE TABLE A(ID INTEGER 
>> PRIMARY KEY,AnotherID INTEGER, Notes TEXT);");
>>  ExecuteStatement(databaseHandle,"CREATE INDEX A_1 ON A 
>> (AnotherID);");
>>  ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE AFTS 
>> USING FTS3 (Notes);");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER A_DeleteTrigger 
>> AFTER DELETE ON A FOR EACH ROW BEGIN DELETE FROM AFTS WHERE rowid=OLD.ID; 
>> END;");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER A_InsertTrigger 
>> AFTER INSERT ON A FOR EACH ROW BEGIN INSERT INTO AFTS (rowid,Notes) VALUES 
>> (NEW.ID,NEW.Notes); END;");
>>  ExecuteStatement(databaseHandle,"INSERT INTO A 
>> (AnotherID,Notes) VALUES(1,'Record A1');");
>>  
>>  ExecuteStatement(databaseHandle,"CREATE TABLE B(ID INTEGER 
>> PRIMARY KEY,Notes TEXT);");
>>  ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE BFTS 
>> USING FTS3 (Notes);");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER B_DeleteTrigger 
>> AFTER DELETE ON B FOR EACH ROW BEGIN DELETE FROM BFTS WHERE rowid=OLD.ID; 
>> END;");
>>  ExecuteStatement(databaseHandle,"CREATE TRIGGER B_InsertTrigger 
>> AFTER INSERT ON B FOR EACH ROW BEGIN INSERT INTO BFTS (rowid,Notes) VALUES 
>> (NEW.ID,NEW.Notes); END;");
>>  ExecuteStatement(databaseHandle,"INSERT INTO B (Notes) 
>> VALUES('Record B1');");
>> #endif
>>  
>>  ExecuteStatement(databaseHandle,"DELETE FROM A WHERE 
>> AnotherID=1;");
>>  ExecuteStatement(databaseHandle,"DELETE FROM B WHERE ID=1;");
>>  
>>  ExecuteStatement(databaseHandle,"COMMIT;");
>>  sqlite3_close(databaseHandle);
>>  } /* if */
>>  els

[sqlite] Branch Apple-OSX

[skywind mailing lists]

Hi,

I have seen that the branch Apple-OSX is still active. Is there any reason why 
the contents of this branch are not merged into trunk? Or is the trunk always 
merged into Apple-OSX? What is the best branch to use for OS X / iOS?

Regards,
Hartwig

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

Re: [sqlite] Strange SQLite bug(?!)

[skywind mailing lists]

HI,

it seems to be that I am not allowed to add zip files to these e-mails. 

This is what I am doing to create the database:

ExecuteStatement(databaseHandle,"CREATE TABLE A(ID INTEGER 
PRIMARY KEY,AnotherID INTEGER, Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE INDEX A_1 ON A 
(AnotherID);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE AFTS 
USING FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_DeleteTrigger 
AFTER DELETE ON A FOR EACH ROW BEGIN DELETE FROM AFTS WHERE rowid=OLD.ID; 
END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_InsertTrigger 
AFTER INSERT ON A FOR EACH ROW BEGIN INSERT INTO AFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO A 
(AnotherID,Notes) VALUES(1,'Record A1');");

ExecuteStatement(databaseHandle,"CREATE TABLE B(ID INTEGER 
PRIMARY KEY,Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE BFTS 
USING FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_DeleteTrigger 
AFTER DELETE ON B FOR EACH ROW BEGIN DELETE FROM BFTS WHERE rowid=OLD.ID; 
END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_InsertTrigger 
AFTER INSERT ON B FOR EACH ROW BEGIN INSERT INTO BFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO B (Notes) 
VALUES('Record B1');");

To reproduce the error I run the following commands firstly with „#if 1“ and 
the second time with „#if 0":

if (sqlite3_open_v2("Test.sldb",,SQLITE_OPEN_CREATE | 
SQLITE_OPEN_READWRITE,NULL) == SQLITE_OK)
{
ExecuteStatement(databaseHandle,"BEGIN TRANSACTION;");

#if 0
ExecuteStatement(databaseHandle,"CREATE TABLE A(ID INTEGER 
PRIMARY KEY,AnotherID INTEGER, Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE INDEX A_1 ON A 
(AnotherID);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE AFTS 
USING FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_DeleteTrigger 
AFTER DELETE ON A FOR EACH ROW BEGIN DELETE FROM AFTS WHERE rowid=OLD.ID; 
END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_InsertTrigger 
AFTER INSERT ON A FOR EACH ROW BEGIN INSERT INTO AFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO A 
(AnotherID,Notes) VALUES(1,'Record A1');");

ExecuteStatement(databaseHandle,"CREATE TABLE B(ID INTEGER 
PRIMARY KEY,Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE BFTS 
USING FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_DeleteTrigger 
AFTER DELETE ON B FOR EACH ROW BEGIN DELETE FROM BFTS WHERE rowid=OLD.ID; 
END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_InsertTrigger 
AFTER INSERT ON B FOR EACH ROW BEGIN INSERT INTO BFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO B (Notes) 
VALUES('Record B1');");
#endif

ExecuteStatement(databaseHandle,"DELETE FROM A WHERE 
AnotherID=1;");
ExecuteStatement(databaseHandle,"DELETE FROM B WHERE ID=1;");

ExecuteStatement(databaseHandle,"COMMIT;");
sqlite3_close(databaseHandle);
} /* if */
else
std::cout << sqlite3_errmsg(databaseHandle) << std::end;

I could not reproduce the error from the shell.

Regards,
Hartwig

> Am 2016-08-02 um 07:56 schrieb skywind mailing lists 
> <mailingli...@skywind.eu>:
> 
> Hi,
> 
> I have added a database but it seems to be that the zip file got lost.
> 
> Regards,
> Hartwig
> 
> 
>> Am 2016-08-02 um 01:13 schrieb Richard Hipp <d...@sqlite.org>:
>> 
>> Do you have a database schema to go with your sample program?
>> 
>> On 8/1/16, skywind mailing lists <mailingli...@skywind.eu 
>> <mailto:mailingli...@skywind.eu>> wrote:
>>> Hi,
>>> 
>>> I have got a database that works using SQLite 3.7.7 but not with version
>>> SQLite 3.13.0. I get the assertion
>>> 
>>> Assertion failed: (((Fts3Table *)pVtab)->mxSavepoint < iSavepoint), function
>>> fts3SavepointMethod, file /.../sqlite/sqlite3.c, line 144649.
>>> 
>>> I h

Re: [sqlite] Strange SQLite bug(?!)

[skywind mailing lists]

Hi,

I have added a database but it seems to be that the zip file got lost.

Regards,
Hartwig


> Am 2016-08-02 um 01:13 schrieb Richard Hipp <d...@sqlite.org>:
> 
> Do you have a database schema to go with your sample program?
> 
> On 8/1/16, skywind mailing lists <mailingli...@skywind.eu 
> <mailto:mailingli...@skywind.eu>> wrote:
>> Hi,
>> 
>> I have got a database that works using SQLite 3.7.7 but not with version
>> SQLite 3.13.0. I get the assertion
>> 
>> Assertion failed: (((Fts3Table *)pVtab)->mxSavepoint < iSavepoint), function
>> fts3SavepointMethod, file /.../sqlite/sqlite3.c, line 144649.
>> 
>> I have compiled the SQLite amalgamation with the options SQLITE_DEBUG=1
>> SQLITE_MEMDEBUG=1 SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1
>> SQLITE_ENABLE_FTS3=1 SQLITE_ENABLE_FTS3_PARENTHESIS=1. I use this program to
>> reproduce the assertion:
>> 
>> #include 
>> 
>> #include "sqlite3.h"
>> 
>> static void ExecuteStatement(sqlite3* databaseHandle, std::string const&
>> sqlStatement)
>> {
>>  int   result;
>>  sqlite3_stmt* statementPtr(NULL);
>>  
>>  result =
>> sqlite3_prepare_v2(databaseHandle,sqlStatement.c_str(),static_cast(sqlStatement.size()),,NULL);
>>  if (result == SQLITE_OK)
>>  {
>>  result = sqlite3_step(statementPtr);
>>  if ((result == SQLITE_OK) || (result == SQLITE_DONE))
>>  result = sqlite3_finalize(statementPtr);
>>  } /* if */
>>  if (result != SQLITE_OK)
>>  std::cout << sqlite3_errmsg(databaseHandle) << " (" << 
>> sqlStatement << ')'
>> << std::endl;
>> }
>> 
>> int main(int argc, const char * argv[])
>> {
>>  sqlite3* databaseHandle(NULL);
>> 
>>  if (sqlite3_open_v2(„Test.sldb",,SQLITE_OPEN_CREATE |
>> SQLITE_OPEN_READWRITE,NULL) == SQLITE_OK)
>>  {
>>  ExecuteStatement(databaseHandle,"BEGIN TRANSACTION;");
>>  
>>  ExecuteStatement(databaseHandle,"DELETE FROM A WHERE 
>> AnotherID=1;");
>>  ExecuteStatement(databaseHandle,"DELETE FROM B WHERE ID=1;");
>>  
>>  ExecuteStatement(databaseHandle,"COMMIT;");
>>  sqlite3_close(databaseHandle);
>>  } /* if */
>>  else
>>  std::cout << sqlite3_errmsg(databaseHandle) << std::end;
>> }
>> 
>> 
>> I do not get any error output besides the failed assertion. I have run an
>> integrity check on the database and I get the response „ok“.
>> 
>> I have seen that there has been a change in „sqlite3VtabSavepoint“ a while
>> ago. Can this change be the source of this error?
>> 
>> Basically, I have no clue what is going on…
>> 
>> Best regards,
>> Hartwig
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org 
>> <mailto:sqlite-users@mailinglists.sqlite.org>
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
>> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org <mailto:d...@sqlite.org>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org 
> <mailto:sqlite-users@mailinglists.sqlite.org>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
> <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] Strange SQLite bug(?!)

[skywind mailing lists]

Hi,

I have got a database that works using SQLite 3.7.7 but not with version SQLite 
3.13.0. I get the assertion

Assertion failed: (((Fts3Table *)pVtab)->mxSavepoint < iSavepoint), function 
fts3SavepointMethod, file /.../sqlite/sqlite3.c, line 144649.

I have compiled the SQLite amalgamation with the options SQLITE_DEBUG=1 
SQLITE_MEMDEBUG=1 SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1 
SQLITE_ENABLE_FTS3=1 SQLITE_ENABLE_FTS3_PARENTHESIS=1. I use this program to 
reproduce the assertion:

#include 

#include "sqlite3.h"

static void ExecuteStatement(sqlite3* databaseHandle, std::string const& 
sqlStatement)
{
int   result;
sqlite3_stmt* statementPtr(NULL);

result = 
sqlite3_prepare_v2(databaseHandle,sqlStatement.c_str(),static_cast(sqlStatement.size()),,NULL);
if (result == SQLITE_OK)
{
result = sqlite3_step(statementPtr);
if ((result == SQLITE_OK) || (result == SQLITE_DONE))
result = sqlite3_finalize(statementPtr);
} /* if */
if (result != SQLITE_OK)
std::cout << sqlite3_errmsg(databaseHandle) << " (" << 
sqlStatement << ')' << std::endl;
}

int main(int argc, const char * argv[])
{
sqlite3* databaseHandle(NULL);

if (sqlite3_open_v2(„Test.sldb",,SQLITE_OPEN_CREATE | 
SQLITE_OPEN_READWRITE,NULL) == SQLITE_OK)
{
ExecuteStatement(databaseHandle,"BEGIN TRANSACTION;");

ExecuteStatement(databaseHandle,"DELETE FROM A WHERE 
AnotherID=1;");
ExecuteStatement(databaseHandle,"DELETE FROM B WHERE ID=1;");

ExecuteStatement(databaseHandle,"COMMIT;");
sqlite3_close(databaseHandle);
} /* if */
else
std::cout << sqlite3_errmsg(databaseHandle) << std::end;
}


I do not get any error output besides the failed assertion. I have run an 
integrity check on the database and I get the response „ok“.

I have seen that there has been a change in „sqlite3VtabSavepoint“ a while ago. 
Can this change be the source of this error? 

Basically, I have no clue what is going on…

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

[sqlite] Preprocessing directives for compiling SQLite for Universal Windows

[skywind mailing lists]

Hi,

I checked SQLite’s documentation and a bit the internet but it seems to be that 
nobody has a definite answer which preprocessor directives have to be used to 
compile SQLite for Universal Windows. Without any preprocessor directives the 
compilation badly fails (system calls are the problem).
Using with VS 2015 

nmake /f Makefile.msc sqlite3

also fails. And there are preprocessor directives in the makefile that are 
nowhere found in the SQLite documentation (e.g. SQLITE_THREAD_OVERRIDE_LOCK).

On Microsoft Dev

SQLITE_OS_WINRT

and 

SQLITE_API=__declspec(dllexport)

is suggested. Any other suggestions?

Regards,
Hartwig

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

Re: [sqlite] UPDATE statement without FROM clause

[skywind mailing lists]

Hi,

of course there is in general a difference between syntax complexity and 
performance but unfortunately not in this case. And the „just“ is very often 
the most difficult part.

Regards,
Hartwig

> Am 2016-06-07 um 07:39 schrieb James K. Lowden <jklow...@schemamania.org>:
> 
> On Sat, 4 Jun 2016 18:18:36 +0200
> skywind mailing lists <mailingli...@skywind.eu> wrote:
> 
>> At the moment I have to run something like:
>> 
>> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),...
>> itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
>> 
>> Using a FROM clause I just need one scan through B (at least in
>> principle). Now, I need N+1 scans.
> 
> Nonsense.  SQL provides no instruction to the implementation on how to
> organize or traverse the data.  SQLite is free to scan B once, twice,
> or not at all.  
> 
> Syntax has nothing to do with performance.  A correlated subquery is an
> expression of logic; it's not meant to be taken literally, and often
> isn't.  This particular form "just" needs to be recognized by the
> optimizer.  
> 
> --jkl
> ___
> 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] UPDATE statement without FROM clause

[skywind mailing lists]

Hi,

why? At the moment I have to run something like:

UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE 
EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

Using a FROM clause I just need one scan through B (at least in principle). 
Now, I need N+1 scans.

Regards,
Hartwig

> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <mesmerizer...@gmail.com>:
> 
> If SQLite implemented the FROM it would just be a translation into the
> complex and slow statements you want to avoid.
> 
> Gerry Snyder
> On Jun 4, 2016 9:19 AM, "skywind mailing lists" <mailingli...@skywind.eu>
> wrote:
> 
>> Hi,
>> 
>> I am using quite often SQL statements that update the data of one table
>> with data from another table. This leads to some quite complex (and slow)
>> statements because SQLite3 is not supporting a FROM clause in update
>> statements. I am just wondering why the FROM clause is not supported by
>> SQLite3?! Is this too complex to implement or is there simply no demand for
>> these type of statements?
>> 
>> Regards,
>> Hartwig
>> 
>> 
>> 
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE statement without FROM clause

[skywind mailing lists]

Hi,

I am using quite often SQL statements that update the data of one table with 
data from another table. This leads to some quite complex (and slow) statements 
because SQLite3 is not supporting a FROM clause in update statements. I am just 
wondering why the FROM clause is not supported by SQLite3?! Is this too complex 
to implement or is there simply no demand for these type of statements?

Regards,
Hartwig



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

[sqlite] why is searching for a range of rowids in an FTS table a slow operation?

[skywind mailing lists]

Hi,

according to the documentation this is a slow query for FTS tables:

SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;

while 

SELECT * FROM mail WHERE rowid=15;

is fast.
As far as I know both queries are fast on normal tables. Where is the 
difference / what is the reason for the performance difference?

Regards,
Hartwig

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

this is the problem. I could never reproduce it by myself and even my customers 
have normally no problems. But it happens once a year or so to one of my 
customers.
Still this is very annoying as it results in data loss.

Regards,
Hartwig

> Am 14.08.2015 um 17:15 schrieb Simon Slavin :
> 
> 
> On 14 Aug 2015, at 4:10pm, skywind mailing lists  
> wrote:
> 
>> I do not get any error message from SQLite. And the database only gets 
>> corrupted when the iDevice has to shut down due to battery issues. I have 
>> never had a customer complaining about a corrupt database during normal 
>> operation.
> 
> If you run the application on your own iDevice, and let it run out of power, 
> do you get the corruption yourself ?
> 
> Do you have any way to log when you're getting background and quit 
> notifications ?  Could you have your application store them in a text file 
> (not a SQLite database, obviously) so you can check to see whether it's 
> getting them before the device runs out of power ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

I think that I can exclude for 99.99% that there are two SQLite version because 
I should get linker errors. Anyway, what is definitely guaranteed that these 
two versions know anything from each other and that they are working on the 
same database.

Regards,
Hartwig

> Am 13.08.2015 um 21:42 schrieb Eric Sink :
> 
> https://www.sqlite.org/howtocorrupt.html
> 
> I know you said you already checked this, so just ignore the following
> remark:
> 
> iOS is one of the easiest platforms to accidentally end up with "Multiple
> copies of SQLite linked into the same application".
> 
> Just sayin'.
> 
> --
> E
> 
> 
> On Thu, Aug 13, 2015 at 2:04 PM, Simon Slavin  wrote:
> 
>> 
>> On 13 Aug 2015, at 6:32pm, skywind mailing lists 
>> wrote:
>> 
>>> Before my app closes I close the database explicitly. Though I do not
>> know if this happens always when the iDevice shuts down due to battery
>> issues.
>> 
>> iDevices shut down quite a time before they'd actually run out of power.
>> Before your device shuts down your App will get notified that it's going to
>> background, and then get notified that it's going to quit.  If you're
>> handling those two notifications properly (i.e. closing all SQLite
>> connections at one or the other) then you should not be getting database
>> corruption.
>> 
>> Things to check are running your App inside some sort of memory logger,
>> e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I
>> think it's built in as a 'Tool'.  And also to check the values returned
>> from /all/ your SQLite calls, even ones like _close() where if they fail
>> there's nothing you can do about it.  If one of them is not SQLITE_OK then
>> you should show an error message.  The call which corrupts the database is
>> sometimes an apparently harmless call.
>> 
>> Simon.
>> ___
>> 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

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

I do not get any error message from SQLite. And the database only gets 
corrupted when the iDevice has to shut down due to battery issues. I have never 
had a customer complaining about a corrupt database during normal operation.

Regards,
Hartwig

> Am 13.08.2015 um 21:04 schrieb Simon Slavin :
> 
> 
> On 13 Aug 2015, at 6:32pm, skywind mailing lists  
> wrote:
> 
>> Before my app closes I close the database explicitly. Though I do not know 
>> if this happens always when the iDevice shuts down due to battery issues.
> 
> iDevices shut down quite a time before they'd actually run out of power.  
> Before your device shuts down your App will get notified that it's going to 
> background, and then get notified that it's going to quit.  If you're 
> handling those two notifications properly (i.e. closing all SQLite 
> connections at one or the other) then you should not be getting database 
> corruption.
> 
> Things to check are running your App inside some sort of memory logger, e.g. 
> valgrind .  I don't know if this is possible on the Xcode simulator, I think 
> it's built in as a 'Tool'.  And also to check the values returned from /all/ 
> your SQLite calls, even ones like _close() where if they fail there's nothing 
> you can do about it.  If one of them is not SQLITE_OK then you should show an 
> error message.  The call which corrupts the database is sometimes an 
> apparently harmless call.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

does anybody have any similar experience that an SQLite database becomes 
corrupt on iOS? It seems to be that when the iDevice is running out of battery 
it can happen that the SQLite becomes corrupt. I have currently not found a 
possibility to reproduce it but once a while my customers experience this 
behavior.

I am compiling SQLite with

SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1 SQLITE_ENABLE_FTS3=1 
SQLITE_ENABLE_FTS3_PARENTHESIS=1

Before my app closes I close the database explicitly. Though I do not know if 
this happens always when the iDevice shuts down due to battery issues.

Any help is appreciated.

PS: I have checked the information how an SQLite can become corrupted but none 
of them apply to my app.

Regards,
Hartwig

Re: [sqlite] Request to change int parameter to size_t parameter / potential bug on iOS (64 bit)

[skywind mailing lists]

Am 07.09.2014 um 21:52 schrieb Simon Slavin :

> 
> On 7 Sep 2014, at 7:19pm, Richard Hipp  wrote:
> 
>> mailingli...@skywind.eu> wrote:
>> 
>>> Is it possible to change the fourth parameter in sqlite3_bind_XXX (and
>>> probably other locations) because this seems to be for me the appropriate
>>> type?!
>> 
>> No.  That would be a compatibility break.
> 
> To supplement Richard's answer, I understand that this problem will be solved 
> in SQLite4, which can rely on size_t from the outset.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Can it also be solved in SQLite3 by overloading versions that support size_t as 
a parameter?

As Roger pointed out ignoring the warning or implementing just a cast is 
insufficient. In case of an overloaded function the SQLite limits could also be 
handled correctly.

Regards,
Hartwig

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

[sqlite] Request to change int parameter to size_t parameter / potential bug on iOS (64 bit)

[skywind mailing lists]

Hello,

I have seen that SQLite uses normally parameters of type "int" to pass the size 
of a variable (see sqlite3_bind_blob, fourth parameter). When compiling SQLite3 
with Clang and some warnings enabled I get warnings when passing sizeof(...) as 
the fourth parameter. The reason is that sizeof(...) and other similar 
functions return the size as a size_t variable. And a size_t variable does not 
have to have the same size as an int variable.

Is it possible to change the fourth parameter in sqlite3_bind_XXX (and probably 
other locations) because this seems to be for me the appropriate type?! On iOS 
64bit the size of int is 4 bytes and the size of size_t is 8 bytes. In this 
case the fourth parameter is actually not even able (theoretically) to store 
the length of a blob or text variable correctly.

Regards,
Hartwig

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

Re: [sqlite] Sqlite RTree nearest neighbour

[skywind mailing lists]

Hi Clemens,

thanks for the link!

Regards,
Hartwig

Am 22.08.2014 um 22:26 schrieb Clemens Ladisch <clem...@ladisch.de>:

> skywind mailing lists wrote:
>> I hoped that somebody already tried to implement a nearest neighbor
>> algorithm.
> 
> Typically, objects are not axis-aligned rectangles, and the R-tree is
> just an index based on the bounding boxes.  Computing the (nearest)
> distance would require the actual geometries.
> 
>> Is the format of the shadow tables somewhere documented or do I have
>> to analyze the source code?
> 
> rtree.c says:
> 
> ** Database Format of R-Tree Tables
> ** 
> **
> ** The data structure for a single virtual r-tree table is stored in three
> ** native SQLite tables declared as follows. In each case, the '%' character
> ** in the table name is replaced with the user-supplied name of the r-tree
> ** table.
> **
> **   CREATE TABLE %_node(nodeno INTEGER PRIMARY KEY, data BLOB)
> **   CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER)
> **   CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER)
> **
> ** The data for each node of the r-tree structure is stored in the %_node
> ** table. For each node that is not the root node of the r-tree, there is
> ** an entry in the %_parent table associating the node with its parent.
> ** And for each row of data in the table, there is an entry in the %_rowid
> ** table that maps from the entries rowid to the id of the node that it
> ** is stored on.
> **
> ** The root node of an r-tree always exists, even if the r-tree table is
> ** empty. The nodeno of the root node is always 1. All other nodes in the
> ** table must be the same size as the root node. The content of each node
> ** is formatted as follows:
> **
> **   1. If the node is the root node (node 1), then the first 2 bytes
> **  of the node contain the tree depth as a big-endian integer.
> **  For non-root nodes, the first 2 bytes are left unused.
> **
> **   2. The next 2 bytes contain the number of entries currently
> **  stored in the node.
> **
> **   3. The remainder of the node contains the node entries. Each entry
> **  consists of a single 8-byte integer followed by an even number
> **  of 4-byte coordinates. For leaf nodes the integer is the rowid
> **  of a record. For internal nodes it is the node number of a
> **  child page.
> 
> For a simple search algorithm, see <http://stackoverflow.com/q/25241406>.
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Sqlite RTree nearest neighbour

[skywind mailing lists]

Hello,

I hoped that somebody already tried to implement a nearest neighbor algorithm. 
Is the format of the shadow tables somewhere documented or do I have to analyze 
the source code?

Regards,
Hartwig

Am 22.08.2014 um 02:58 schrieb Richard Hipp :

> On Thu, Aug 21, 2014 at 8:54 PM, Peter Aronson  wrote:
> 
>>1. You would need to access SQLite's R-Tree "shadow" tables
>> (xx_node, xx_parent, xx_rowid) directly in to perform the traversals
>> required by all of the algorithms -- I don't know if this is officially
>> supported by SQLite's developers, or if these tables are guaranteed not to
>> change;
>> 
> 
> The format of the shadow tables will not change in ways that would break
> older versions of SQLite.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Sqlite RTree nearest neighbour

[skywind mailing lists]

Hello,

does anybody have any experience with implementing a nearest neighbor search 
using SQLite's RTree functionality? Is a nearest neighbor search possible?

Regards,
Hartwig


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

[sqlite] Unnecessary implicit conversion (may lead to a bug)

[skywind mailing lists]

In 

afpUnlock(sqlite3_file *, int)

the sharedLockByte is defined as an int (int sharedLockByte = 
SHARED_FIRST+pInode->sharedByte;) although all other related variables and the 
following function parameters are defined as unsigned long longs. At least 
theoretically this can lead to information loss and wrong sharedLockBytes 
values. If this is also practically the case I do not know.

Regards,
Hartwig


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

Re: [sqlite] [BUG; 3.7.13] ANALYZE leads to a wrong query plan for RTrees

[skywind mailing lists]

Am 10.12.2013 um 10:59 schrieb Dan Kennedy <danielk1...@gmail.com>:

> On 12/10/2013 02:44 PM, skywind mailing lists wrote:
>> This is an example that the ANALYZE command leads to a wrong query plan for 
>> RTrees:
>> 
>> CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
>> CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
>> FromLongitude,TillLongitude);
>> INSERT INTO A VALUES(1,0,0,0);
>> INSERT INTO A VALUES(2,1,1,1);
>> INSERT INTO B VALUES(1,0,0,0,0);
>> INSERT INTO B VALUES(2,1,1,1,1);
>> 
>> Without an analyze command the query plan seems to be OK:
>> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND 
>> (B.FromLongitude > 5) AND (B.TillLongitude < 10);
>> 0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd (~0 rows)
>> 0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>> 
>> After running the ANALYZE command the query plan has changed and the result 
>> is a worse query plan than before:
>> ANALYZE;
>> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND 
>> (B.FromLongitude > 5) AND (B.TillLongitude < 10);
>> 0|0|0|SCAN TABLE A (~2 rows)
>> 0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows)
>> 
> 
> If possible, please try with 3.8.2. 3.8.2 adds a way for virtual
> table queries in general, and r-tree tables in particular, to take
> advantage of ANALYZE data:
> 
>  http://www.sqlite.org/src/info/5a3cfd747a
> 
> As Clemens says in the other post, with such small tables the join
> order is not very important. So a better test would be with the
> original database - the one you used to spot the problem in the
> first place.
> 
> Dan.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

I used my original database with slightly more than 70 000 datasets and I get 
the same result as in the small database. To make sure that the query works as 
expected I use a CROSS JOIN, now.

I will try 3.8.x at a later stage.

Regards,
Hartwig



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

[sqlite] [BUG; 3.7.13] ANALYZE leads to a wrong query plan for RTrees

[skywind mailing lists]

This is an example that the ANALYZE command leads to a wrong query plan for 
RTrees:

CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
FromLongitude,TillLongitude);
INSERT INTO A VALUES(1,0,0,0);
INSERT INTO A VALUES(2,1,1,1);
INSERT INTO B VALUES(1,0,0,0,0);
INSERT INTO B VALUES(2,1,1,1,1);

Without an analyze command the query plan seems to be OK:
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND 
(B.FromLongitude > 5) AND (B.TillLongitude < 10);
0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd (~0 rows)
0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

After running the ANALYZE command the query plan has changed and the result is 
a worse query plan than before:
ANALYZE;
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND 
(B.FromLongitude > 5) AND (B.TillLongitude < 10);
0|0|0|SCAN TABLE A (~2 rows)
0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows)

Regards,
Hartwig

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

Re: [sqlite] RTrees and query speed

[skywind mailing lists]

Am 09.12.2013 um 10:06 schrieb Clemens Ladisch <clem...@ladisch.de>:

> skywind mailing lists wrote:
>> Assume I have the following tables:
>> 
>> CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
>> CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
>> FromLongitude,TillLongitude);
>> 
>> According to the RTree documentation this query should be fast (demo_data / 
>> demo_index example):
>> 
>> SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (FromLongitude > 5) AND 
>> (TillLongitude < 10);
>> 
>> Actually, the query is pretty slow.
> 
> According to the EXPLAIN QUERY PLAN output (<http://www.sqlite.org/eqp.html>),
> it is fast:
> 
> 0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd
> 0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?)
> 
> To you get the same output for EXPLAIN QUERY PLAN?
> If not, what SQLite version are you using?
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Hi Clemens,

I am using SQLite 3.7.13.

If I do not run ANALYZE my query plan is the same (I rebuild the tables). BUT 
my query plan is different after ANALYZE:

0|0|0|SCAN TABLE A (~74067 rows)
0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows)

The problem seems to be (~0 rows) for TABLE B. There are definitely 74067 rows 
in table B. Therefore, I conclude that ANALYZE is not able to analyze RTree 
tables correctly and therefore the wrong query plan is chosen.

Regards,
Hartwig

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

[sqlite] RTrees and query speed

[skywind mailing lists]

Assume I have the following tables:

CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, 
FromLongitude,TillLongitude);

and there is an index on A for Latitude,Longitude. B is filled using

INSERT INTO B SELECT ID,Latitude,Latitude,Longitude,Longitude FROM A;


According to the RTree documentation this query should be fast (demo_data / 
demo_index example):

SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (FromLongitude > 5) AND 
(TillLongitude < 10);

Actually, the query is pretty slow. It is slower than a direct (complete) 
search on A (SELECT COUNT(*) FROM A WHERE (Longitude > 5) AND (Longitude < 
10);).

Though this query is fast:

SELECT COUNT(*) FROM A WHERE (ID IN (SELECT ID FROM B WHERE (FromLongitude > 5) 
AND (TillLongitude < 10)));


Am I doing anything wrong or is the documentation not correct?

Regards,
Hartwig

PS: I used for testing only 70 000 records but I do not think that it really 
matters.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update statement

[skywind mailing lists]

Hi Peter,

thanks.

But it does not seem to work in my environment. Probably somewhere a bug on my 
side.

Hartwig

Am 07.05.2013 um 19:55 schrieb Peter Aronson:

> At least for SQLite it appears to be.  From the Update doc page 
> (http://www.sqlite.org/lang_update.html):
> 
> "The modifications made to each row affected by an UPDATE statement are 
> determined by the list of assignments following the SET keyword. Each 
> assignment 
> specifies a column name to the left of the equals sign and a scalar 
> expression 
> to the right. For each affected row, the named columns are set to the values 
> found by evaluating the corresponding scalar expressions. If a single 
> column-name appears more than once in the list of assignment expressions, all 
> but the rightmost occurrence is ignored. Columns that do not appear in the 
> list 
> of assignments are left unmodified. The scalar expressions may refer to 
> columns 
> of the row being updated. In this case all scalar expressions are evaluated 
> before any assignments are made."
> 
> And in fact I rely on it behaving this way for in one place in my SQLite code 
> and it seems to be working correctly.
> 
> Peter
> 
> - Original Message 
>> From: skywind mailing lists <mailingli...@skywind.eu>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Sent: Tue, May 7, 2013 10:46:42 AM
>> Subject: Re: [sqlite] Update statement
>> 
>> Hi,
>> 
>> my question is: is it guaranteed that it works?
>> 
>> Regards,
>> Hartwig
>> 
>> Am 07.05.2013 um 03:24 schrieb James K. Lowden:
>> 
>>> On Mon, 6 May 2013 23:53:40 +0100
>>> Simon Slavin <slav...@bigfraud.org> wrote:
>>> 
>>>>> How do I create this kind of update statement?
>>>>> 
>>>>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
>>>>> 
>>>>> The RHS should always be used with the values of a and b before the
>>>>> assignment.
>>>>> 
>>>>> I think that the result of this kind of statement is undefined, or?
>>>> 
>>>> No need to worry, it will work the way you want it to work:
>>>> 
>>>> The row is read.
>>>> The new values are calculated.
>>>> The new values are written to the database.
>>> ...
>>>> That was a terrible description. 
>>> 
>>> Actually that's not a bad approximation of what happens.  Here's a
>>> simpler example:
>>> 
>>> sqlite> create table t(a int, b int);
>>> sqlite> insert into t values (1,2);
>>> sqlite> select * from t;
>>> a  b
>>> --  --
>>> 1  2
>>> sqlite> update t set a=b, b=a;  -- Et Voila! 
>>> sqlite> select * from t;
>>> a  b
>>> --  --
>>> 2  1
>>> 
>>> There is no "RHS".  The syntax and semantics of SQL are its own; they
>>> cannot be extrapolated from other languages.  
>> 
>> I know but everybody knows what I meant, or? And its a quite brief 
> description.
>> 
>>> 
>>> --jkl
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Update statement

[skywind mailing lists]

Hi,

my question is: is it guaranteed that it works?

Regards,
Hartwig

Am 07.05.2013 um 03:24 schrieb James K. Lowden:

> On Mon, 6 May 2013 23:53:40 +0100
> Simon Slavin  wrote:
> 
>>> How do I create this kind of update statement?
>>> 
>>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
>>> 
>>> The RHS should always be used with the values of a and b before the
>>> assignment.
>>> 
>>> I think that the result of this kind of statement is undefined, or?
>> 
>> No need to worry, it will work the way you want it to work:
>> 
>> The row is read.
>> The new values are calculated.
>> The new values are written to the database.
> ...
>> That was a terrible description. 
> 
> Actually that's not a bad approximation of what happens.  Here's a
> simpler example:
> 
> sqlite> create table t(a int, b int);
> sqlite> insert into t values (1,2);
> sqlite> select * from t;
> a   b 
> --  --
> 1   2 
> sqlite> update t set a=b, b=a;  -- Et Voila! 
> sqlite> select * from t;
> a   b 
> --  --
> 2   1 
> 
> There is no "RHS".  The syntax and semantics of SQL are its own; they
> cannot be extrapolated from other languages.  

I know but everybody knows what I meant, or? And its a quite brief description.

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

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

[sqlite] Update statement

[skywind mailing lists]

Hi,

How do I create this kind of update statement?

UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);

The RHS should always be used with the values of a and b before the assignment.

I think that the result of this kind of statement is undefined, or?

Regards,
Hartwig


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

[sqlite] Ticket 13137dccf36902cdd4e20c11d857c7ad7f5d8777 / FTS3 and last_insert_rowid()

[skywind mailing lists]

Hi,

why is the above mentioned still open? It seems to be that the bug does not 
exist anymore under 3.7.7.

Best regards,
Hartwig

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

Re: [sqlite] Database becomes corrupted on iPhone

[skywind mailing lists]

Hi Simon,

why is the database corrupted when sqlite3_close() is not called? I thought 
that I might lose data but the database is actually corrupted and not 
accessible anymore!

Can you explain what happens?

Regards,
Hartwig

Am 07.08.2011 um 22:43 schrieb Simon Slavin:

> 
> On 7 Aug 2011, at 9:19pm, skywind mailing lists wrote:
> 
>> A few user have reported that when the iPhone/iPad runs out of batteries the 
>> SQLite3 database may become corrupted. I have received one database back and 
>> it is indeed defect and unrecoverable. Does anybody have an idea what is 
>> going wrong? I tried to reproduce the error by myself but I did not have any 
>> luck so far.
>> For information: only the main thread is writing to the database. There is 
>> no other thread having access to the database and there is also no read 
>> operation from the main thread. The write operation is not within an 
>> explicit transaction.
> 
> Don't worry about transactions, your key question is when you run 
> sqlite3_close().  If your database is being corrupted by low-battery-shutoff 
> then you are failing to correctly trap the 'application will quit' 
> notification, because the iPhone sends this to all apps before it shuts down 
> due to low batteries.
> 
> (Following is for those less used to iOS development.)
> 
> There are two common tactics for iPhone apps.  One is to close database 
> handles whenever the application is notified it will be switched to the 
> background.  The other is to close your database handles whenever your 
> application gets notified that it will be terminated.  There are 
> notifications for both of these, and they're easy to react to.  It's possible 
> those users have found an obscure bug in some version of iOS where it's not 
> notifying apps properly, but I think this is unlikely.  I've not seen such 
> problems reported elsewhere.
> 
> Since you say only your main thread writes to the database your best tactic 
> comes down to whether your app is one that has background functionality (like 
> something that logs your GPS movements while you're doing other things) or 
> one which only really has functionality when it's the front app.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Database becomes corrupted on iPhone

[skywind mailing lists]

Hi Richard,

yes, I had a look at the link before but I am not doing any of the actions that 
are mentioned on the cited page. If the file system is broken I do not know but 
I doubt (but I cannot prove it). Therefore, I am looking for something iOS 
specific. It only happens when the iPhone/iPad is running out of batteries 
(perhaps during a write operation?).

BTW: I assume that the rollback journal is in the same folder as the database 
file, right?

Best regards,
Hartwig

Am 07.08.2011 um 22:25 schrieb Richard Hipp:

> On Sun, Aug 7, 2011 at 4:19 PM, skywind mailing lists <
> mailingli...@skywind.eu> wrote:
> 
>> I am using SQLite 3.6.x and higher for two apps for the iPhone/iPad. I do
>> not use the built-in SQLite library because I need Rtree and FTS support.
>> Therefore, I compile it by myself with the following preprocessor
>> directives:
>> 
>> SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1 SQLITE_ENABLE_FTS3=1
>> SQLITE_ENABLE_FTS3_PARENTHESIS=1
>> 
>> A few user have reported that when the iPhone/iPad runs out of batteries
>> the SQLite3 database may become corrupted. I have received one database back
>> and it is indeed defect and unrecoverable. Does anybody have an idea what is
>> going wrong? I tried to reproduce the error by myself but I did not have any
>> luck so far.
>> For information: only the main thread is writing to the database. There is
>> no other thread having access to the database and there is also no read
>> operation from the main thread. The write operation is not within an
>> explicit transaction.
>> 
>> Any ideas are appreciated!
>> 
> 
> http://www.sqlite.org/howtocorrupt.html
> 
> 
>> 
>> Regards,
>> 
>> Hartwig
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Database becomes corrupted on iPhone

[skywind mailing lists]

I am using SQLite 3.6.x and higher for two apps for the iPhone/iPad. I do not 
use the built-in SQLite library because I need Rtree and FTS support. 
Therefore, I compile it by myself with the following preprocessor directives:

SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1 SQLITE_ENABLE_FTS3=1 
SQLITE_ENABLE_FTS3_PARENTHESIS=1

A few user have reported that when the iPhone/iPad runs out of batteries the 
SQLite3 database may become corrupted. I have received one database back and it 
is indeed defect and unrecoverable. Does anybody have an idea what is going 
wrong? I tried to reproduce the error by myself but I did not have any luck so 
far.
For information: only the main thread is writing to the database. There is no 
other thread having access to the database and there is also no read operation 
from the main thread. The write operation is not within an explicit transaction.

Any ideas are appreciated!

Regards,

Hartwig

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

[sqlite] sqlite3 amalgamation and unused variables

[skywind mailing lists]

There seem to be a couple of variables in the sqlite3 amalgamation that get 
assigned a value but actually their value is not used. If there is an intention 
to get rid of these statements I will submit samples like

  h = pFile->h;

(sqlite3.c in line 26039 version 3.7.7)

But perhaps these lines originate from merging source code files into the 
amalgamation file?!

Hartwig

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

Re: [sqlite] FTS & sqlite3_last_insert_rowid

[skywind mailing lists]

Hi,

FTS and sqlite3_last_insert_rowid do not work together. This is a known 
shortcoming. Basically this also means that you can't use any triggers 
involving FTS.

Regards,

Hartwig

Am 13.05.2011 um 17:38 schrieb Steven Parkes:

> I gather sqlite3_last_insert_rowid doesn't play well with FTS? I don't see an 
> exception noted in the docs but neither are there non-manually managed 
> examples.
> 
> I'd prefer not to manually mange them but ...
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Out of memory? How could that be on a 32G iPhone?

[skywind mailing lists]

Hi Rolf,

I am using FMDB and SQLite for more than 2 years now and have not experienced 
any problems so far. Therefore, I expect that the bug is somewhere else but not 
inside SQLite (3.7.2) nor FMDB (2009-10-18).

Greetings,
Hartwig

Am 04.05.2011 um 23:22 schrieb Rolf Marsh:

> 
> 
> Prior to getting this error, I opened the d/b and inserted one (1) very 
> small record...
> Where do I start looking?  I am using FMDB, ZBarSDK (used to read 
> barcodes), but I can't imagine that's using all of my memory... and I 
> have the d/b set to be a singleton, as indicated by the NSLog entries...
> 
> How do I tell how much active memory I'm using?  Where do I start 
> looking (I'm a newbie, as you can probably tell by now) :-P
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] SQLite GUI comparison

[skywind mailing lists]

Hi Tom,

if you do not have a command line tool in one of the software package you can 
do two things:

 1) try to create an FTS or RTree table. It will fail if the extensions are not 
supported.
 2) create a database having all to be tested extension and then issue a SELECT 
* statement on the table

In both cases you will get an error message.

Hartwig

Am 22.02.2011 um 00:16 schrieb BareFeetWare:

> On 22/02/2011, at 4:31 AM, skywind mailing lists wrote:
> 
>> "Supports SQLite extension" would be an accurate feature description. And in 
>> the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., 
>> otherwise a "-". A yes or no is insufficient because some support RTree but 
>> not FTS and vice versa.
> 
> OK, that sounds good. I'll probably use "no" or "none" if no extension is 
> supported.
> 
> Can anyone please tell me what should go in this cell for any SQLite GUI app 
> they know (ie either FTS2, FTS3, RTree or none). Or how can I easily test 
> this feature?
> 
> Thanks,
> Tom
> BareFeetWare
> 
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] SQLite GUI comparison (was: ANN: Base 2.0, Mac SQLite GUI)

[skywind mailing lists]

Hi Tom,

"Supports SQLite extension" would be an accurate feature description. And in 
the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., 
otherwise a "-". A yes or no is insufficient because some support RTree but not 
FTS and vice versa.
Alternatively you may have a row for each type of extension but I find this a 
bit too much.

Hartwig

Am 20.02.2011 um 21:48 schrieb BareFeetWare:

> On 21/02/2011, at 3:20 AM, skywind mailing lists wrote:
> 
>> in your comparison chart it would also be nice to see which software is able 
>> to support SQLite extension. A couple of them do not support the FTS nor 
>> RTree capabilities of SQLite.
> 
> Sure, I'd be happy to add that. How do you suggest that the feature is worded 
> in the table? Is "Supports SQLite extension" accurate? Please let me know 
> what value (eg yes or no) I should show for this feature for any apps you 
> know so I can add those entries.
> 
> Thanks,
> Tom
> BareFeetWare
> 
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] ANN: Base 2.0, Mac SQLite GUI

[skywind mailing lists]

Hi Tom,

in your comparison chart it would also be nice to see which software is able to 
support SQLite extension. A couple of them do not support the FTS nor RTree 
capabilities of SQLite.

Hartwig

Am 20.02.2011 um 07:19 schrieb BareFeetWare:

> Hi Ben,
> 
> In reply to your announcement of Base 2:
> 
>> Just a short message to announce that version 2.0 of Base, our Mac SQLite 
>> GUI is now available.
> 
> The new version looks great. Congrats :-)
> 
>> It's a major upgrade, the highlight of which is the ability for the app to 
>> view, create & alter tables with support for *all* table- and column-level 
>> constraints.
> 
> Neat.
> 
> When I choose "Alter Table", it nicely shows the list of columns, but 
> mistakenly also shows the constraints as if they were columns, with nothing 
> in the Constraints list. I tested a few schemas, including this one:
> 
> create table "Products Detail"
> (
>   Supplier integer
>   not null
>   references Suppliers (ID)
>   on delete restrict
>   on update cascade
> , Code text
>   not null
>   collate nocase
> , Detail text
>   not null
>   collate nocase
> , primary key (Supplier, Code)
> , foreign key (Supplier, Code)
>   references "Products Base" (Supplier, Code)
>   on delete cascade
>   on update cascade
> )
> ;
> 
> which shows in Base 2 as:
> 
>>> Columns:
>>> 
>>> Name  Type Constraints
>>> Supplier  integer  NF
>>> Code  test NC
>>> Detailtext NC
>>> primary   key
>>> 
>>> Constraints:
>>> 
>>> none listed
> 
> The "Alter" panel also shows an "Origin" column, which I think only makes 
> sense in a view.
> 
> When saving a changed table, Base seems to try to explicitly save any auto 
> indexes (which of course fails). For instance, when saving changes to the 
> above table, Base informed me:
> 
>>> There were problems re-creating table indexes. One or more indexes present 
>>> in the table before modification could not be recreated. Their original SQL 
>>> is listed below:
>>> 
>>> CREATE UNIQUE INDEX "sqlite_autoindex_Products Detail_1" ON "Products 
>>> Detail" ("Supplier", "Code");
> 
> I also failed to save the trigger, but that was probably due to it messing up 
> the create table columns (eg adding a column called "primary" etc).
> 
>> You can read the full announcement (with a link to release notes) here: 
>> http://menial.co.uk/2011/02/18/base-2-0/
> 
> I've updated the details for Base 2.0 on my SQLite GUI comparison page at:
> http://www.barefeetware.com/sqlite/compare/?ml
> 
> If there are any corrections or additions you'd like, please let me know.
> 
> Thanks,
> Tom
> BareFeetWare
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Optimizing queries using RTrees

[skywind mailing lists]

I have created an RTree table with the command

CREATE VIRTUAL TABLE AirspaceRTree USING rtree 
(ID,MinLatitude,MaxLatitude,MinLongitude,MaxLongitude,MinAltitude,MaxAltitude);

Furthermore, I have a table Airspace. The contents besides the ID (primary key) 
are irrelevant for this context.

When using the query below no index seems to be used

SELECT Airspace.ID FROM Airspace,AirspaceRTree WHERE (Airspace.ID = 
AirspaceRTree.ID) AND (AirspaceRTree.MinLatitude > = 1.0);

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 Real   0 1 0 2  00   
2 Goto   0 18000   
3 VOpen  1 0 0 vtab:10081EE08:10009A9E0  00 
  
4 OpenRead   0 980 0  00   
5 SCopy  1 4 000   
6 Integer2 2 000   
7 Integer1 3 000   
8 VFilter1 152 Db 00   
9 Rowid  1 6 000   
10MustBeInt  6 14000   
11NotExists  0 14600   
12Rowid  0 7 000   
13ResultRow  7 1 000   
14VNext  1 9 000   
15Close  1 0 000   
16Close  0 0 000   
17Halt   0 0 000   
18Transaction0 0 000   
19VerifyCookie   0 75000   
20TableLock  0 980 Airspace   00   
21Goto   0 3 000   

Changing the query to

SELECT Airspace.ID FROM Airspace WHERE Airspace.ID IN (SELECT rowid FROM 
AirspaceRTree WHERE AirspaceRTree.MaxLatitude >= 2.0);

leads to

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 Goto   0 27000   
2 OpenRead   0 980 0  00   
3 If 2 17000   
4 Integer1 2 000   
5 OpenEphemeral  3 1 0 keyinfo(1,nil)  00   
6 Real   0 3 0 2  00   
7 VOpen  1 0 0 vtab:10081EE08:10009A9E0  00 
  
8 SCopy  3 6 000   
9 Integer2 4 000   
10Integer1 5 000   
11VFilter1 164 Db 00   
12Rowid  1 7 000   
13MakeRecord 7 1 8 c  00   
14IdxInsert  3 8 000   
15VNext  1 12000   
16Close  1 0 000   
17Rewind 3 25000   
18Column 3 0 100   
19IsNull 1 24000   
20MustBeInt  1 24000   
21NotExists  0 24100   
22Rowid  0 9 000   
23ResultRow  9 1 000   
24Next   3 18000   
25Close  0 0 000   
26Halt   0 0 000   
27Transaction0 0 000   
28VerifyCookie   0 75000   
29TableLock  0 980 Airspace   00   
30Goto   0 2 000   

Here at least on Airspace an index is used. But is the second version the 
fastest possible query?

Hartwig



___
sqlite-users mailing list
sqlite-users@sqlite.org