Re: [sqlite] When is the decltype recognized?

[Peter Otten]

Clemens Ladisch wrote:

> Peter Otten wrote:
>> select * from (select alpha from demo union all select alpha from demo)
>> order by alpha decltype: (null)
>>
>> select * from (select alpha from demo union all select alpha from demo)
>> decltype: custom
>>
>> Even taking http://sqlite.org/c3ref/column_decltype.html
>> """
>> If this statement is a SELECT statement and the Nth column of the
>> returned result set of that SELECT is a table column (not an expression
>> or subquery) then the declared type of the table column is returned. If
>> the Nth column of the result set is an expression or subquery, then a
>> NULL pointer is returned.
>> """
>> into account I would have expected either NULL or "custom" for both
>> queries.
> 
> The first query uses a temporary table for sorting.  The column in that
> temporary table does not have a declared type.
> 
> The second query returns the values directly from the underlying table
> column, so the declared type is also from that table.

Thank you. Is there a way around that limitation?

PS: The bug reporter has since confirmed that her code used to work with 
sqlite 3.3.4. Where can I download historical versions of sqlite to look 
into that myself?

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

[sqlite] System.Data.SQLite version 1.0.91.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.91.0 (with SQLite 3.8.3.1) is now available
on the System.Data.SQLite website:

 http://system.data.sqlite.org/

Further information about this release can be seen at

 http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin

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

Re: [sqlite] help needed for major SQLite problem

[Stephen Chrzanowski]

Information on how to open SQLite files:
http://www.sqlite.org/c3ref/open.html
How the locking mechanisms work: http://sqlite.org/lockingv3.html

Specifically, it'll depend on the language or wrapper you're using to
access the database.  In my case (Delphi) there is an option in the open
function that will allow me to specify exactly HOW I want to open a file.
If you use an exclusive lock, no other application will be able to do
anything with it.  DropBox will complain about it, but it won't be able to
do anything about it.  However, the question remains if or how the WAL file
will get written if your main database is open exclusively.

As I said before, I've used DB for a long while, been the EXCLUSIVE user on
this particular database, I'm never writing to the database at anytime
other than the machine I happen to be sitting at.  I've only ever
exclusively locked a file to one program because there COULD be a chance
that I'd accidentally run multiple versions of the program in production
mode, and I'd rather the error out on the second app rather than duplicate
writes. If I realize that I have a lock on a file in my version control
system at home, and I'm at work, I'll log into my machine at home, check in
the changes, then check out at work, and continue on.  However, if I'm at
home and realize what I want to work on is at work... err.. well I'll
go blow up some Kerbals (Kerbal Space Program) or drive manically into the
back end of the competition in Gran Tourismo 5 and take the day off.  ;)



On Wed, Feb 12, 2014 at 12:59 PM, C M  wrote:

>
>
> > I'd suggest looking into opening the database with an exclusive lock,
>
>
> Can you say more about that?  I'm not familiar with that option.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Stephen Chrzanowski]

If you write your information to the cheap* USB key instead of the SD card,
from a mile-high view, you're looking at a bad data disk instead of a bad
OS disk.  You could backup daily your USB version of the database to the SD
card, or to a network drive (If available) so then you're only writing to
the SD card once per day.  If you put an hours worth of data in memory (Via
an array, or an in-memory database) you won't write data out anywhere,
unless the OS decides it has to (Swap), which would end up being out of
your control anyways.



* Cheap has two meanings here.  Both price, and ease of replacement.
Relatively speaking, its easier to put a new USB key in and get it
formatted, versus reloading the OS and getting everything running again.  I
don't own a Pi, so I don't know much much more work it is to load versus
putting a 'nix distro on a PC.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] documentation for datetime functions

[Richard Hipp]

On Wed, Feb 12, 2014 at 2:56 PM, Igor Tandetnik  wrote:

> On 2/12/2014 2:31 PM, Bernd wrote:
>
>> but I think that 2013-10-07 08:23:19.120-04:00 ought to be
>> 2013-10-07 04:23:19.120-04:00
>>
>
> That's what I see right now. Perhaps the page has already been corrected.
>

http://www.sqlite.org/docsrc/info/5cb71ea877

-- 
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] sqlite3.exe: incomplete CSV import

[Christoph Appel]

Hello,

when importing a .csv file which contains less columns than
specified in the corresponding TABLE's schema, sqlite3.exe
does not import the last column.

Attached You can find a simple test case:

#
data.csv:
a,b
d,e

SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .separator ,
sqlite> .mode csv
sqlite> .import data.csv test
sqlite> CREATE TABLE test (A NUMERIC, B TEXT, C TEXT);
sqlite> .import data.csv test
data.csv:1: expected 3 columns but found 2 - filling the rest with NULL
data.csv:2: expected 3 columns but found 2 - filling the rest with NULL
sqlite> select * from test;
a,,
d,,

#
- Encoding (UTF8, latin1) and line ends (CR, CR LF, LF etc.) do not matter
- the result is the same with a trailing delimiter in data.csv,
   e.g. a,b,
d,e,
- tested with different TABLE schemata, always the same result
- tested with sqlite3 3.8.2, 3.8.3.1, OS: Win 7 64 bit
- Is it a bug, my fault, or intended behaviour?

Cheers,

  Christoph

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

Re: [sqlite] documentation for datetime functions

[Igor Tandetnik]

On 2/12/2014 2:31 PM, Bernd wrote:

but I think that 2013-10-07 08:23:19.120-04:00 ought to be
2013-10-07 04:23:19.120-04:00


That's what I see right now. Perhaps the page has already been corrected.
--
Igor Tandetnik

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

[sqlite] documentation for datetime functions

[Bernd]

I think there is a small mistake in
https://sqlite.org/lang_datefunc.html.
It says that the following time strings are equivalent

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 08:23:19.120-04:00
2456572.84952685

but I think that 2013-10-07 08:23:19.120-04:00 ought to be
2013-10-07 04:23:19.120-04:00

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

Re: [sqlite] help needed for major SQLite problem

[C M]

On Mon, Feb 10, 2014 at 4:40 PM, Stephen Chrzanowski wrote:

> Personally, I don't buy that DropBox is the culprit as I've done this kind
> of thing a few times in a few applications of my own, however, I'm the
> single user that works on that single account, and any app that uses DB is
> usually under development and "closed" on any other geographical site.
>

That is the same situation with me; I only develop and use this
application, and thus this database, on one computer only.  I haven't tried
the Dropbox syncing in at least two years, though it used to work.

But I'm getting the sense from others here responding to this post that
Dropbox does put a lock on the database file in order to back it up, and it
has to do that whenever the data is changed, and so one might wind up
having Dropbox lock the file when you need to write to it.  Oddly, I make
changes to the database hundreds of times a day and only get this lock
conflict--if that is indeed what is happening--every now and then, like
every few weeks or months, particularly when the RAM is filled up with a
memory leaky Firefox and other open applications...possibly because the
lock is held longer during those times?


> I'd suggest looking into opening the database with an exclusive lock,


Can you say more about that?  I'm not familiar with that option.



> or look into using the Backup API that SQLite uses.
>
Using the SQLite Backup API, when your program starts, do a flat-file
> standard file copy from DropBox to a different location (%USERPATH% or
> something similar) and wait for the file to finish to copy.  Then open that
> backed up file and work on it.  When your user saves, or closes your
> application, use the backup API to put the file back to DropBox directory.
> This isolates WAL file writes to the local system and not to DB.
>

That sounds similar to what James Lowden is suggesting in this thread.
I'll look into that, and will probably have questions for the list later.

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

Re: [sqlite] database table is locked issue

[Simon Slavin]

On 12 Feb 2014, at 4:44pm, Sandu Buraga  wrote:

> I have a process with several threads working in the same time on a
> database file. I have 0 or 1 writers and 0 or N readers at a moment. All
> write accesses are isolated in transactions, I am using WAL and shared
> cache, but sometimes during the DELETE statemens I get "database table is
> locked" errors, either in the write or read thread.

Have you set a timeout value for all your connections ?  If you haven't, SQLite 
never does any waiting for locks at all, it just returns an error.  Use either 
of these:





A reasonable number of milliseconds is however long you would want your program 
to wait before giving up and reporting an error to the user.  Values like 6 
are not out of place.

Simon,


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

[sqlite] database table is locked issue

[Sandu Buraga]

Hi,

I have a process with several threads working in the same time on a
database file. I have 0 or 1 writers and 0 or N readers at a moment. All
write accesses are isolated in transactions, I am using WAL and shared
cache, but sometimes during the DELETE statemens I get "database table is
locked" errors, either in the write or read thread.

Since the new content is not committed yet, why I am getting this errors?
My expectations would've been that during the end transaction to have such
errors, but not long before.

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

Re: [sqlite] When is the decltype recognized?

[Clemens Ladisch]

Peter Otten wrote:
> select * from (select alpha from demo union all select alpha from demo) order 
> by alpha
> decltype: (null)
>
> select * from (select alpha from demo union all select alpha from demo)
> decltype: custom
>
> Even taking http://sqlite.org/c3ref/column_decltype.html
> """
> If this statement is a SELECT statement and the Nth column of the returned
> result set of that SELECT is a table column (not an expression or subquery)
> then the declared type of the table column is returned. If the Nth column of
> the result set is an expression or subquery, then a NULL pointer is
> returned.
> """
> into account I would have expected either NULL or "custom" for both queries.

The first query uses a temporary table for sorting.  The column in that
temporary table does not have a declared type.

The second query returns the values directly from the underlying table
column, so the declared type is also from that table.


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

Re: [sqlite] Bug in SQLite 3.8.2

[Richard Hipp]

Thanks for the bug report.

Ticket: http://www.sqlite.org/src/info/c34d0557f740c45070
Fixed here: http://www.sqlite.org/src/info/5d01426ddf


On Wed, Feb 12, 2014 at 5:31 AM, Paweł Salawa  wrote:

> Hi,
>
> The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions.
>
> *Preconditions:*
>
> - 2 databases: A and B.
>
> - database A has table "test":
> CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID
>
> - database B has table "test2":
> CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER)
>
>
> *To reproduce bug:*
>
> - open database A and attach database B:
> ATTACH 'database_b.db' AS 'attached';
>
> - execute query:
> select test.*, t2.ROWID from attached.test2 t2, test
>
> SQLite says: *no such column: t2.ROWID*
>
> Weird thing is that when you switch test2 and test table positions with
> each other, the same query will work:
> select test.*, t2.ROWID from test, attached.test2 t2
>
> *^^^ this works just fine.*
>
> Problem occurres only if following conditions are met:
> - table in local database is WITHOUT ROWID
> - table in attached database is a regular table with ROWID
> - query selects ROWID from the regular table
> - both tables must be mentioned in the FROM clause
> - the WITHOUT ROWID table must be mentioned as the second one
>
> Regards,
> --
> Paweł Salawa
> pawelsal...@gmail.com
> ___
> 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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi Stephen,

> *First*, buy a bulk amount of cheap, inexpensive USB keys and start
> throwing your data at the key instead of your OS's card.

Don't see how that will change things: Once the SD card fails, data
logging will fail too - same is true for the usb key.

> *Second*, instead of writing to the SD card 8640 times a day, why not once
> every hour?

This is exactly what I am looking for by reducing the frequency of fsync.
This way the data will stay in the page cache without beeing flused to
the device, sans any ugly workarounds in my software.

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

[sqlite] When is the decltype recognized?

[Peter Otten]

Hello!

Over at python.org there is a bug report that its sqlite3 module sometimes 
doesn't correctly deserialize a typed column. From my limited understanding 
it boils down to the following once you go down to sqlite's C API:

#include 
#include "sqlite3.h"

int main(int argc, char **argv)
{
sqlite3 * db;
sqlite3_stmt * statement;
const char * type;

printf("%s\n", sqlite3_libversion());

sqlite3_open(":memory:", &db);
sqlite3_exec(db, "create table demo (alpha custom);", 0, 0, 0);

sqlite3_prepare_v2(db, "select * from (select alpha from demo union all 
select alpha from demo) order by alpha", -1, &statement, NULL);
type = sqlite3_column_decltype(statement, 0);
printf("decltype: %s\n", type);

sqlite3_prepare_v2(db, "select * from (select alpha from demo union all 
select alpha from demo)", -1, &statement, NULL);
type = sqlite3_column_decltype(statement, 0);
printf("decltype: %s\n", type);

return 0;
}

Long time no write C, I hope it doesn't show ;)

$ gcc demo.c sqlite3.c -lpthread -ldl
$ ./a.out 
3.8.3.1
decltype: (null)
decltype: custom

A minor change (the order by clause) defeats type detection.

Even taking http://sqlite.org/c3ref/column_decltype.html
"""
If this statement is a SELECT statement and the Nth column of the returned 
result set of that SELECT is a table column (not an expression or subquery) 
then the declared type of the table column is returned. If the Nth column of 
the result set is an expression or subquery, then a NULL pointer is 
returned. 
"""
into account I would have expected either NULL or "custom" for both queries.

PS: the original bug report is http://bugs.python.org/issue20587

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

Re: [sqlite] Bug in SQLite 3.8.2

[Kees Nuyt]

On Wed, 12 Feb 2014 11:31:05 +0100, Pawe? Salawa
 wrote:

> - open database A and attach database B:
> ATTACH 'database_b.db' AS 'attached';

This is not the main cause, but that should be:

ATTACH 'database_b.db' AS attached;

(attached should not be a literal but an identifier, just like
table names and column names.)

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Stephen Chrzanowski]

There are a few reasons for the two-database method would be useful versus
a single database connection, depending on the volume of data in the pot.

1> Having a single hourly database will keep the database size minimal.
~8000 entries holding just temperature data, it'll be small so I can't see
a write taking longer than 10 seconds.
2> Backing up a large database that could potentially take longer than 10
seconds to backup, you might end up in a race condition.  If an insert at
the next 10 second mark causes the backup API to start over again, who's to
say that 20 seconds down the road the backup will restart again?
3> Depending on the kind of historical look up needed, picking a day/time
range would be as easy as attaching the required databases, in code,
(re)generating a temporary view to join the look ups would be simplistic.
The data could be considered "read only" and be safely read from a network
share on another machine as to not disrupt the Pi.

Depending on the structure of the code, if a two-database setup is daunting
to implement, the other option available, I suppose, is to do a bulk insert
from the memory database to the SD database in one transaction once an
hour, and when the bulk insert is completed, delete the in-memory contents
and continue on.  You should still be looking at a decent output times
(Just a few thousand small rows which probably amounts to only a small-few
hundred pages) so I think even writing to an old school 720k floppy
diskette should be quick enough.

Depending on the language, you could just entirely skip the in-memory
database and just remember the data recorded from the Pi by dumping it into
an array, then hourly dump the data into the database.

On Wed, Feb 12, 2014 at 4:03 AM, RSmith  wrote:

>
> Actually, the Backup-API is clever like that, it will automatically void
> and re-start the backup when data changes happen. Of course this might be
> equally detrimental if you update more frequently than the backup takes to
> complete, in which case the system would be in permanent backup and any
> gains voided.
>
> Maybe stop the updater till the backup is finished... it should however
> not take 10s, so it _should_ be safe, but I would put it in the
> precautionary wait state just to be safe.
>
>
> ___
> 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] MATCH and ESCAPE

[Attila]

Dan provided the solution. Thanks!

---
It's because by default the "[" character is treated as a
punctuation or separator character and ignored. As a result
the FTS query "[*" is equivalent to "" - which always returns
zero rows.

You can change the set of characters treated a punctuation
by changing using a different tokenizer:

  http://www.sqlite.org/fts3.html#tokenizer

For example, to treat both "[" and "]" as part of tokens instead
of punctuation:

  CREATE VIRTUAL TABLE xyz USING fts4(tokenize=unicode61 
"tokenchars=[]");


---




On 2014-02-12 11:05, Richard Hipp wrote:

On Wed, Feb 12, 2014 at 4:57 AM, Attila  wrote:


I tried that one as well.



SQLite has no built-in MATCH function.  If you want to use the MATCH
syntax, then you need to register your own MATCH function using
sqlite3_create_function().


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

[sqlite] Bug in SQLite 3.8.2

[Paweł Salawa]

Hi,

The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions.

*Preconditions:*

- 2 databases: A and B.

- database A has table "test":
CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID

- database B has table "test2":
CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER)


*To reproduce bug:*

- open database A and attach database B:
ATTACH 'database_b.db' AS 'attached';

- execute query:
select test.*, t2.ROWID from attached.test2 t2, test

SQLite says: *no such column: t2.ROWID*

Weird thing is that when you switch test2 and test table positions with
each other, the same query will work:
select test.*, t2.ROWID from test, attached.test2 t2

*^^^ this works just fine.*

Problem occurres only if following conditions are met:
- table in local database is WITHOUT ROWID
- table in attached database is a regular table with ROWID
- query selects ROWID from the regular table
- both tables must be mentioned in the FROM clause
- the WITHOUT ROWID table must be mentioned as the second one

Regards,
-- 
Paweł Salawa
pawelsal...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MATCH and ESCAPE

[Richard Hipp]

On Wed, Feb 12, 2014 at 4:57 AM, Attila  wrote:

> I tried that one as well.
>

SQLite has no built-in MATCH function.  If you want to use the MATCH
syntax, then you need to register your own MATCH function using
sqlite3_create_function().
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MATCH and ESCAPE

[Attila]

I tried that one as well.

On 2014-02-12 10:26, Hick Gunter wrote:

Maybe you should be using single quotes as string delimiters?

-Ursprüngliche Nachricht-
Von: Attila [mailto:dex...@xyzones.org]
Gesendet: Mittwoch, 12. Februar 2014 10:18
An: sqlite-users@sqlite.org
Betreff: [sqlite] MATCH and ESCAPE

Hello,

Based on http://www.sqlite.org/lang_expr.html#match first diagram i
would expect that MATCH "\[*" ESCAPE "\" to work. Actually it return
Error: wrong number of arguments to function MATCH()

Could you please advise?


Thanks,

Attila

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged.
If you have received it in error, you are on notice as to its status
and accordingly please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it
for any purposes, or disclose its contents to any person as to do so
could be a breach of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] MATCH and ESCAPE

[Richard Hipp]

On Wed, Feb 12, 2014 at 4:17 AM, Attila  wrote:

> Hello,
>
> Based on http://www.sqlite.org/lang_expr.html#match first diagram i would
> expect that MATCH "\[*" ESCAPE "\" to work. Actually it return Error: wrong
> number of arguments to function MATCH()
>
> Could you please advise?
>

The syntax  "a MATCH b"  means the same thing as "MATCH(b,a)" and "a MATCH
b ESCAPE c" means the same thing as "MATCH(b,a,c,)".  If you are getting an
error on the latter, it means that whatever "match" function you registered
only accepts two arguments, not three.

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

Re: [sqlite] MATCH and ESCAPE

[Hick Gunter]

Maybe you should be using single quotes as string delimiters?

-Ursprüngliche Nachricht-
Von: Attila [mailto:dex...@xyzones.org]
Gesendet: Mittwoch, 12. Februar 2014 10:18
An: sqlite-users@sqlite.org
Betreff: [sqlite] MATCH and ESCAPE

Hello,

Based on http://www.sqlite.org/lang_expr.html#match first diagram i would 
expect that MATCH "\[*" ESCAPE "\" to work. Actually it return
Error: wrong number of arguments to function MATCH()

Could you please advise?


Thanks,

Attila

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MATCH and ESCAPE

[Attila]

Hello,

Based on http://www.sqlite.org/lang_expr.html#match first diagram i 
would expect that MATCH "\[*" ESCAPE "\" to work. Actually it return 
Error: wrong number of arguments to function MATCH()


Could you please advise?


Thanks,

Attila

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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[RSmith]

On 2014/02/12 10:09, Stephen Chrzanowski wrote:

The other thing I'd look into is that because of the varying speeds of SD,
the volume of information you could be writing, you may run into an issue
where you call the backup API but due to write speeds, something else
writes to the live in-memory database, and then your data becomes expunged
when the DELETE command is executed while the backup is happening.


Actually, the Backup-API is clever like that, it will automatically void and re-start the backup when data changes happen. Of course 
this might be equally detrimental if you update more frequently than the backup takes to complete, in which case the system would be 
in permanent backup and any gains voided.


Maybe stop the updater till the backup is finished... it should however not take 10s, so it _should_ be safe, but I would put it in 
the precautionary wait state just to be safe.


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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Stephen Chrzanowski]

Three thoughts;

*First*, buy a bulk amount of cheap, inexpensive USB keys and start
throwing your data at the key instead of your OS's card.  I'm not 100%
clear on how a USB key handles itself as far as writing to certain parts of
its memory, but you could partition off chunks of space and just move the
mount point to a different partition periodically.  I picked up an
EXTREMELY small 16gig USB key (Smaller than a freak'n quarter) for like
$30.  This particular keys life span is going to be in a read-only state in
my car stereo.  I'm sure they sell smaller (Volume size, not physical) for
cheaper.

*Second*, instead of writing to the SD card 8640 times a day, why not once
every hour?  Since you losing an hour worth of data isn't significant, and
since the Pi has a decent chunk of memory (If you've got the B version),
you could just throw the data you're accumulating into memory, then at the
top of the hour, dump the data to the SD via the Backup API naming it with
a date/time stamp as the file name, dump the memory contents, and start
over again.  You'll save significant writes, pending swaps to cache by the
OS you'll be writing to the disk only 24 times a day instead of 8640 times.

The only changes you'd need to do to your code is to create the table in
memory, or, have a 'template' database sitting around that can use the
Backup API to put into memory.  Create the in-memory database with "
:memory: " as the file name (Excluding quotes, INCLUDING colons), and
you're off to the races.

The other thing I'd look into is that because of the varying speeds of SD,
the volume of information you could be writing, you may run into an issue
where you call the backup API but due to write speeds, something else
writes to the live in-memory database, and then your data becomes expunged
when the DELETE command is executed while the backup is happening.

So what I would do is;
- use a temporary variable to hold the memory location of the current
in-memory database, (Essentially NewTempDatabase = OldLiveDatabase)
- directly free-and-nill the OldLiveDatabase variable
- re-create a new :memory: database assigned to OldLiveDatabase,
- recreate/reload the template database against OldLiveDatabase,
- call the temporary variables backup API.
- I'm unsure if you'll get an event when the backup is complete, however,
once you call the backup, you should be able to use close the database,
then the object will free itself as usual.

So for a short period of time, you'll have two in-memory databases, one
containing the previous hours worth of data, and a new, fresh new
database.  The only issue that I can see coming up is if you're re-opening
the database each time you add a new record, which would be really bad to
begin with first off (Very expensive to do, but since you're doing one
thing every 10 seconds, not such a big deal I suppose), second, if you're
functions are aware of how to use a globally accessible database, or if
you're passing the database into your functions, etc, then using a memory
database is a total scrub.

*Third*, if you're thinking about using the second option, throw the backup
at a network drive, there by ELIMINATING writes to SD card by your
application.


On Mon, Feb 10, 2014 at 8:40 AM, Clemens Eisserer wrote:

> Hi,
>
> I would like to use sqlite for storing temperature data acquired every
> 10s running on my raspberry pi.
> As my first SD card died within a week with this workload, I am
> looking for opportunities to reduce write operations triggered by
> fsyncs to flash.
> For me loosing 1h of data at a power failure isn't an issue, however
> the DB shouldn't be corrupt afterwards.
>
> I found the pragma "synchronous", which when set to "NORMAL" does seem
> to do exactly what I am looking for - when sqlite is used in WAL mode.
> Am I right that with this configuration, fsync is only executed very
> seldomly?
>
> > In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized
> before each checkpoint
> > and the database file is synchronized after each completed checkpoint
> and the WAL file header is synchronized
> > when a WAL file begins to be reused after a checkpoint, but no sync
> operations occur during most transactions.
>
> Thank you in advance, 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