Re: [sqlite] date field with default current date

[Pavel Ivanov]

>> What does "SELECT sqlite_version()" gives you in python with sqlite3
>> module?
> [(u'3.5.9',)]

Well, CURRENT_DATE should work then, it was added in 3.1.0. Could you
show us an exact statement you are trying to execute and the exact
text of error you get?


Pavel


On Thu, Apr 21, 2011 at 1:43 PM, Fabio Spadaro  wrote:
> Hi.
>
> 2011/4/21 Pavel Ivanov 
>
>> > Does not work on python with sqlite3 module
>>
>> What does "SELECT sqlite_version()" gives you in python with sqlite3
>> module?
>>
>>
>> Pavel
>>
>>
>> On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro 
>> wrote:
>> > Hi.
>> >
>> > 2011/4/21 Black, Michael (IS) 
>> >
>> >> create table t (d default CURRENT_DATE,i number);
>> >> insert into t (i) values(1);
>> >> select * from t;
>> >> 2011-04-21|1
>> >>
>> >>
>> >>
>> >> Use CURRENT_TIME if you want hours/minutes too.
>> >>
>> >>
>> >>
>> >> Michael D. Black
>> >>
>> >> Senior Scientist
>> >>
>> >> NG Information Systems
>> >>
>> >> Advanced Analytics Directorate
>> >>
>> >>
>> >>
>> >> 
>> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> >> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
>> >> Sent: Thursday, April 21, 2011 5:37 AM
>> >> To: General Discussion of SQLite Database
>> >> Subject: EXT :[sqlite] date field with default current date
>> >>
>> >> Hi.
>> >> I'm working with python and sqlite3 and i ask how to create a table with
>> a
>> >> date
>> >> field wih defaults current date.
>> >> Thanks.
>> >> --
>> >> Fabio Spadaro
>> >> www.fabiospadaro.com
>> >> ___
>> >> 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
>> >>
>> >
>> > Does not work on python with sqlite3 module
>> >
>> > --
>> > Fabio Spadaro
>> > www.fabiospadaro.com
>> > ___
>> > 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
>>
>
> [(u'3.5.9',)]
>
>
> --
> Fabio Spadaro
> www.fabiospadaro.com
> ___
> 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 handling of EINTR

[Rich Rattanni]

That would be it.  Thanks Roger.  Next time I will update first and
ask questions later.

--
Rich

On Thu, Apr 21, 2011 at 5:55 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/21/2011 02:24 PM, Rich Rattanni wrote:
>> The result was errno 4, which according to
>> my flavor of Linux is EINTR.
>
> I suggest looking into the source 3.7.6 (more recent than your version) and
> find EINTR.  It looks like your issue is addressed.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk2wp7QACgkQmOOfHg372QTQFACgji9ECCmRHl/xlnTbO186GnAi
> YtMAoKAjanGmivqWAIUTRH9MpZgsDjGQ
> =CJ0O
> -END PGP SIGNATURE-
> ___
> 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 handling of EINTR

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/21/2011 02:24 PM, Rich Rattanni wrote:
> The result was errno 4, which according to
> my flavor of Linux is EINTR.

I suggest looking into the source 3.7.6 (more recent than your version) and
find EINTR.  It looks like your issue is addressed.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2wp7QACgkQmOOfHg372QTQFACgji9ECCmRHl/xlnTbO186GnAi
YtMAoKAjanGmivqWAIUTRH9MpZgsDjGQ
=CJ0O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Request for help with SQLite Query to return missing Date/Time Ranges

[Pete Attkins]

On 2011-04-21, at 17:13, H. Phil Duby wrote:

> [...]
> With possible 'adjustments' for switching to daylight savings time and
> back, depending on what timezone the original data is stored in.  If
> it was UTC, then no problem. But if it was in you local timezone, that
> used daylight savings time, then daylight savings time changes will
> cause a 1 hour gap, and a 1 hour overlap in the data once a year
> [each].

That is one of the main reasons why database timestamps should be  
recorded only in UTC and placed in a column with UTC in its name.  
SQLite date and time functions do not support the full syntax of ISO  
8601 (why should they), but a column may usefully be assigned a  
default value of CURRENT_TIMESTAMP, which will be UTC if the operating  
system time is synchronized via an NTP or SNTP client.

I've only ever needed to store timestamps as integer unix timestamps  
because it reduces both data bandwidth and CPU overhead, and makes  
finding "not logged records" quite easy. This is just from memory so  
it's just the essence of a possible solution, but it's well worth  
refining while designing temporal database systems:

1. Create and populate "ReferenceTable" having a column "r" with all  
expected time entries. Once per minute would store the values of  
unix_timestamp DIV 60 (obviously each row increments by 1 up until the  
current moment or the last expected database entry).

2. ALTER TABLE LoggedData ADD COLUMN r INTEGER DEFAULT 0

3. UPDATE LoggedData SET r =  
function_returning_unix_timestamp(TimestampColumn) DIV 60 WHERE r=0

Note DIV means integer division: either add a user defined function or  
synthesize it from built-in functions.

4. SELECT function_returning_UTC_string(ReferenceTable.r * 60) FROM  
ReferenceTable LEFT OUTER JOIN LoggedData USING (r) WHERE LoggedData.r  
IS NULL

Now we have a lists of each minute for which there was no data logged.  
It should be easier to report "not logged time spans" by using  
external programming logic than by constructing endless SQL.



This and other solutions will not work unless the logging is supposed  
to be unique to each minute. For data logged approximately each minute  
the phase of the sampling must be adjust to suit. If the following are  
valid and expected time values

00:00:05
00:00:55
[apparent gap]
00:02:10
00:03:15
00:03:45
[apparent gap]
00:05:10

then the sampling point would be better placed on 30 second boundaries  
instead of zero second boundaries. In this case the integer division  
function DIV must be modified to account for the offset, as must the  
SELECT query.


Regards,
Pete

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

[sqlite] SQLite handling of EINTR

[Rich Rattanni]

I have been tracing the source of a low-occurrence anomaly in my C#
application, running on Linux 2.6 under Mono.  My application is using
Robert Simpson's SQLite .NET adapter + SQLite 3.7.5.  After I resolved
my own bug, which prevented me from seeing the exception thrown by
System.Data.SQLite, I found out that SQLite was bailing due to
SQLITE_IOERR.  I recompiled SQLite to make extended result codes on by
default (not sure if I could do this from System.Data.SQLite).  The
extended result code was consistently SQLITE_IOERR_WRITE.  I found 2
placed in the source that returned this error on the UNIX system:
fcntlSizeHint and unixWrite.  A few lines of code later, I narrowed it
down to unixWrite.  So as a quick patch I modified the return
statement from (SQLITE_IOERR_WRITE) to (SQLITE_IOERR_WRITE |
(pFile->lastErrno << 16)).  The result was errno 4, which according to
my flavor of Linux is EINTR.

So, I wanted to ask if my analysis seem correct?  If write() is
interrupted by a signal, then an SQLITE_IOERR is generated.

With that being said, would I be out of line to suggest that this is
an oversight?  Would it be acceptable to ask SQLite to detect EINTR
and retry the write?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] just fyi: iPhone and iPad 3G's tracking their owners' movements and saving to sqlite db

[Simon Slavin]

On 21 Apr 2011, at 2:26pm, Pavel Ivanov wrote:

> It's not related to the list but still...
> 
>> Technically, the data referred to is as follows.  An iPhone logs details of 
>> which phone base stations it connects to, and the 'status' data obtained 
>> from the base station when it was connected.  The location (long & lat) of 
>> the base station is part of the base station's status string.  That is all.
> 
> I believe it's not location of the base station, it's location of the
> phone itself based on GPS information.

The dot locations don't have a very high resolution.  Near where I live the 
grid means that 'touching' dots are at closest half a mile apart.  So you can't 
tell the pinpoint close enough to see whether it's indicating the phone's 
location or the base station's location.

There are still battling articles about this: one author writes one thing 
another writes something else that can't be conciled with it.  I guess we won't 
know for sure until someone reads the source code, or Apple explains precisely 
what it does.

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

[sqlite] Help with the code: vdbe

[Lucas Cotta]

Hi!!

I'm studying databases in my college and I'd like to implement an algorithm
to SQLite... since I'm not familiar with C, could you guys help me a little?
For now, I just need to, every time the VDBE enters in OP_COLUMN, get the
column value and put it to a string variable, that's all !

Thank you very much!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] date field with default current date

[Fabio Spadaro]

Hi.

2011/4/21 Pavel Ivanov 

> > Does not work on python with sqlite3 module
>
> What does "SELECT sqlite_version()" gives you in python with sqlite3
> module?
>
>
> Pavel
>
>
> On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro 
> wrote:
> > Hi.
> >
> > 2011/4/21 Black, Michael (IS) 
> >
> >> create table t (d default CURRENT_DATE,i number);
> >> insert into t (i) values(1);
> >> select * from t;
> >> 2011-04-21|1
> >>
> >>
> >>
> >> Use CURRENT_TIME if you want hours/minutes too.
> >>
> >>
> >>
> >> Michael D. Black
> >>
> >> Senior Scientist
> >>
> >> NG Information Systems
> >>
> >> Advanced Analytics Directorate
> >>
> >>
> >>
> >> 
> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on
> >> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
> >> Sent: Thursday, April 21, 2011 5:37 AM
> >> To: General Discussion of SQLite Database
> >> Subject: EXT :[sqlite] date field with default current date
> >>
> >> Hi.
> >> I'm working with python and sqlite3 and i ask how to create a table with
> a
> >> date
> >> field wih defaults current date.
> >> Thanks.
> >> --
> >> Fabio Spadaro
> >> www.fabiospadaro.com
> >> ___
> >> 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
> >>
> >
> > Does not work on python with sqlite3 module
> >
> > --
> > Fabio Spadaro
> > www.fabiospadaro.com
> > ___
> > 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
>

[(u'3.5.9',)]


-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.6 and 3.7.6.1 -- Performance issue with Triggers

[kball...@kennethballard.com]

I created the two additional indexes and ran some timing tests on the applicable
tables and the timings came out much faster than anything I'd seen before, so
thank you very much for your help on this.
 
It is very interesting that you found the query to be performing "universally
slow" all the way back to 3.6.23 as that does not fit my observations at all.
The database file I sent you was initially created with an earlier version of
SQLite and isn't the file automatically rebuilt with every build iteration, so
perhaps that has something to do with it. If you were to dump the database and
recreate it with an earlier version, you may not see the performance hit with
the earlier versions, but will see it with 3.7.6.

As I provided, it was only with 3.7.6 that I started noticing the performance
degradation, and not just with this query but with a lot of the queries in my
application. Something definitely changed, and it forced me to make several
schema changes to the database that actually turned out to be for the better
anyway.
 
So again, thanks for your help on this.  
Kenneth 


On April 20, 2011 at 10:46 AM Richard Hipp  wrote:

> On Sat, Apr 16, 2011 at 5:35 PM, Kenneth Ballard <
> kball...@kennethballard.com> wrote:
>
> > Good afternoon,
> >
> > Here is an issue I started to experience after upgrading from SQLite
> > 3.7.5 to 3.7.6 involving a trigger I have on a database table.
> >
>
> Using the database you sent me by private email, I found that SQLite was
> uniformly slow for all versions of SQLite back through 3.6.23.  (I didn't
> test anything further back than that.)  There was no speed difference going
> from 3.7.5 to 3.7.6.
>
> However, I did find that I could make the query fast as follows:
>
>    CREATE INDEX newidx1 ON shoppes(island);
>    CREATE INDEX newidx2 ON offers(shoppe_id);
>
> As a rule of thumb, you should always have in index on foreign keys.  SQLite
> does *not* create such indices for you automatically.
>
>
> >
> > The table with the trigger is a 2-column table with the following
> > trigger installed to it:
> >
> > CREATE TABLE [table_a] (
> >   [col_a] INTEGER NOT NULL PRIMARY KEY,
> >   [col_b] INT64 NOT NULL);
> >
> > CREATE TRIGGER [RemoveOffers]
> > BEFORE DELETE
> > ON [table_a]
> > BEGIN
> > DELETE FROM table_b
> > WHERE col_a IN
> > (SELECT col_a FROM table_c WHERE col_b = old.col_a);
> > END;
> >
> > On 3.7.5, a single delete statement from this table executed almost
> > instantaneously even on the largest sets of data it would be clearing
> > out. However, after upgrading to 3.7.6 and 3.7.6.1, this isn't the case.
> > To have the same performance as I did on 3.7.5, I have to use the
> > trigger query separately -- a delete statement on table_a and the delete
> > statement on table_b separately.
> >
> > Again, as a trigger this statement takes seconds to run -- one run took
> > as much as 15 seconds to execute. As separate delete statements, it
> > takes a fraction of a second, similar to how it ran as a trigger in 3.7.5.
> >
> > I've also noticed issues where queries with an IN clause (like the
> > trigger above) took significantly longer to execute than before, but
> > that was cleared up by adding some more indexes to the table
> > definitions. But even adding additional indexes didn't help the
> > performance of this trigger running as a trigger.
> >
> > So this begs the question: what changed?
> >
> > ___
> > 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

Re: [sqlite] Problems while upgrading to 3.7.3

[Dan Kennedy]

On 04/21/2011 05:42 PM, Gaurav Srivastava wrote:
> Hi
>
> I have recently upgraded my sqlite version from 3.5.1 to 3.7.3. With the
> update I have been seeing some issues with sqlite3_prepare_v2() API.
> The workflow here is:
> sqlite3VdbeSetSql(pVDbe, zSql, saveSqlflag)
>  ^
>  |
>  |
> sqlite3prepare(...)
>  ^
>  |
>  |
> sqlite3LockandPrepare(...)
>  ^
>  |
>  |
> sqlite3_prepare_v2(savesqlflag=1).
>
> I have created a statement using sqlite3_prepare_v2. I execute a query and
> then use sqlite3_step(stmt) to step over the results. Firstly, it fails due
> to SQLITE_SCHEMA error and then uses sqlite3_reprepare to retry. In that
> function the isPrepareV2 variable becomes false and so I am getting the
> value SQLITE_OK instead of SQLITE_ROW. The second thing is in
> sqlite3VdbeSwap function, we are not properly swapping the isPrepareV2
> values.

I think the code is Ok there. The important part is that after the
call to sqlite3Reprepare() returns, the isPrepareV2 flag is still set.
Are you finding this is not the case?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Request for help with SQLite Query to return missing Date/Time Ranges

[Black, Michael (IS)]

A slight mod on my solution makes it work for DST changes too.  Again...rowid 
must be maintained.



PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE log(d date);
INSERT INTO "log" VALUES('2011-03-13 01:55');
INSERT INTO "log" VALUES('2011-03-13 01:56');
INSERT INTO "log" VALUES('2011-03-13 01:58');
INSERT INTO "log" VALUES('2011-03-13 03:00');
INSERT INTO "log" VALUES('2011-03-13 03:01');
INSERT INTO "log" VALUES('2011-03-13 03:03');
INSERT INTO "log" VALUES('2011-03-13 03:04');
INSERT INTO "log" VALUES('2011-03-13 03:05');
COMMIT;
sqlite> select 
l2.d,l1.d,(strftime('%s',datetime(l1.d,'utc'))-strftime('%s',datetime(l2.d,'utc'))-60)/60
 from log l1,log l2 where (l1.rowid=l2.rowid+1) and 
(strftime('%s',datetime(l1.d,'utc')) - strftime('%s',datetime(l2.d,'utc')) != 
60);
2011-03-13 01:56|2011-03-13 01:58|1
2011-03-13 01:58|2011-03-13 03:00|1
2011-03-13 03:01|2011-03-13 03:03|1



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




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

Re: [sqlite] Request for help with SQLite Query to return missing Date/Time Ranges

[H. Phil Duby]

On Thu, Apr 21, 2011 at 12:57 AM, Oliver Peters  wrote:
>
> Andrew Lindsay  writes:
>
> [...]
>
> > I am trying to search an SQL database that is meant to have entries logged
> > every minute for a period of approximately 15 months.
> >
> > I want to create a query that will search through the database and tell me
> > for which periods I do not have any entries.
> >
> [...]
>
> very easy:
>
> 1. calculation
> 60*24        =   1,440 minutes ( 1 day)
> 1,440  * 31  =  44,640 minutes ( 1 month, only an approximation
>                                 because not every month has 31 days)
> 44,640 * 15  = 669,600 minutes (15 months)
>
> -> produce ~669,600 entries that represent your minutes of a day
>
> assuming you have a timestamp like -MM-DD HH:MM:SS in your database
> your 669,600 look like this:
> 2011-04-21 00:00
> 2011-04-21 00:01
> ...
>

[..]

>
> That's it - I assume here that you want to check every minute but it's
> easy to adapt this

With possible 'adjustments' for switching to daylight savings time and
back, depending on what timezone the original data is stored in.  If
it was UTC, then no problem. But if it was in you local timezone, that
used daylight savings time, then daylight savings time changes will
cause a 1 hour gap, and a 1 hour overlap in the data once a year
[each].

> greetings
> oliver

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

Re: [sqlite] date field with default current date

[Pavel Ivanov]

> Does not work on python with sqlite3 module

What does "SELECT sqlite_version()" gives you in python with sqlite3 module?


Pavel


On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro  wrote:
> Hi.
>
> 2011/4/21 Black, Michael (IS) 
>
>> create table t (d default CURRENT_DATE,i number);
>> insert into t (i) values(1);
>> select * from t;
>> 2011-04-21|1
>>
>>
>>
>> Use CURRENT_TIME if you want hours/minutes too.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
>> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
>> Sent: Thursday, April 21, 2011 5:37 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :[sqlite] date field with default current date
>>
>> Hi.
>> I'm working with python and sqlite3 and i ask how to create a table with a
>> date
>> field wih defaults current date.
>> Thanks.
>> --
>> Fabio Spadaro
>> www.fabiospadaro.com
>> ___
>> 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
>>
>
> Does not work on python with sqlite3 module
>
> --
> Fabio Spadaro
> www.fabiospadaro.com
> ___
> 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] date field with default current date

[Fabio Spadaro]

2011/4/21 Mihai Militaru 

> On Thu, 21 Apr 2011 15:17:00 +0200
> Fabio Spadaro  wrote:
>
> > Does not work on python with sqlite3 module
>
> Try using the date and time functions, 'date' or 'datetime' in your case:
> INSERT INTO table(..., date) VALUES(..., datetime('now'));
>
> http://www.sqlite.org/lang_datefunc.html
>
> --
> Mihai Militaru 
>  ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Is 'create table ...' does not work ! :)

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Use an Apostrophe in a Text Field?

[Jean-Christophe Deschamps]

>True. I will get rid of the habit of using double quotes for string 
>literals.
>Thanks for information. But most of the databases support this non 
>standard
>behavior.


Yeah ... until things break under your feet due to a new version not 
sticking to the "non-standard" behavior anymore or parser unable to 
raise ambiguity in the way you expect in unexpected corner case.  I 
hate it when such simple details get an opportunity to cause premium havoc.


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

[sqlite] Documentation error was Re: date field with default current date

[David Garfield]

http://www.sqlite.org/lang_datefunc.html

This page has an error in documenting the range of values when using
modifier 'unixepoch'.  It says the limit is 10675199167.  There should
be one more digit in that to get the documented year 5352 result.

--David Garfield

Mihai Militaru writes:
> On Thu, 21 Apr 2011 15:17:00 +0200
> Fabio Spadaro  wrote:
> 
> > Does not work on python with sqlite3 module
> 
> Try using the date and time functions, 'date' or 'datetime' in your case:
> INSERT INTO table(..., date) VALUES(..., datetime('now'));
> 
> http://www.sqlite.org/lang_datefunc.html
> 
> -- 
> Mihai Militaru 
> ___
> 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] How to Use an Apostrophe in a Text Field?

[venkat easwar]

Hi,

True. I will get rid of the habit of using double quotes for string literals. 
Thanks for information. But most of the databases support this non standard 
behavior.

Thanks
Venkat

 VENKAT





From: Jean-Christophe Deschamps 
To: General Discussion of SQLite Database 
Sent: Thu, April 21, 2011 4:03:49 PM
Subject: Re: [sqlite] How to Use an Apostrophe in a Text Field?


>  The apostrophes are escaped by apostrophes.

Correct.  http://www.sqlite.org/faq.html#q14

>  One more way you can do.
>
>insert into  () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
>It is double quotes before and after *. Similarly double quotes will 
>be escaped by one more double quote

Don't do that: it can reveal a pitfall.
It's not SQL even if SQLite does its best to interpret it without 
issuing an error.

Double quotes should be reserved to enclose database, table and column 
names, not string literals. SQLite also accepts square brackets as 
well: "My Table" is the same as [My Table].

Only use single quotes (apostrophes) for string literals.

A statement like:
delete from "my table" where column = "column";
is prone to disapoint you!
http://www.sqlite.org/faq.html#q24

___
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] SQLite3 memory leaks in my c++ dll application

[jeff archer]

>From: Khanh Nguyen   
>Date: Wed, 20 Apr 2011 11:00:57 -0500
>
>SQLite...memory leaks...

The VS debugger will stop at the specific allocation number if you add this 
line 
early on in your program execution.
_crtBreakAlloc = ;
Where  is the allocation number reported in the Detected Memory Leaks!

I use SQLite from VS 2010 C++ 64 bit very heavily in my product.  I am not 
aware 
of any memory leaks which can be attibuted to SQLite.

Also, you should be aware that under the right circumstances it is possible to 
trick the debug memory allocator into reporting false positives in the leaked 
memory. Your said that your are using GetProcAddress() to make calls which 
makes 
me think you *may* be getting to these false positives.  Basically, I believe 
it 
comes down to allocating memory in one dll and deallocating in another.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] date field with default current date

[Mihai Militaru]

On Thu, 21 Apr 2011 15:17:00 +0200
Fabio Spadaro  wrote:

> Does not work on python with sqlite3 module

Try using the date and time functions, 'date' or 'datetime' in your case:
INSERT INTO table(..., date) VALUES(..., datetime('now'));

http://www.sqlite.org/lang_datefunc.html

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

Re: [sqlite] just fyi: iPhone and iPad 3G's tracking their owners' movements and saving to sqlite db

[Pavel Ivanov]

It's not related to the list but still...

> Technically, the data referred to is as follows.  An iPhone logs details of 
> which phone base stations it connects to, and the 'status' data obtained from 
> the base station when it was connected.  The location (long & lat) of the 
> base station is part of the base station's status string.  That is all.

I believe it's not location of the base station, it's location of the
phone itself based on GPS information. That's why these news are so
shocking.


Pavel


On Wed, Apr 20, 2011 at 7:17 PM, Simon Slavin  wrote:
>
> On 21 Apr 2011, at 12:03am, Donald Griggs wrote:
>
>> The video mentions that the file "consolidated.db" is an ordinary sqlite
>> file, containing latitude, longitude, and timestamps.   The data they
>> examined went back several months.
>>
>> http://radar.oreilly.com/2011/04/apple-location-tracking.html
>>
>> There's no allegation the data is *transmitted* to Apple or anyone else, but
>> this is intriguing nonetheless.
>
> SQLite is not really related to this, other than the data is held in an 
> SQLite database file, as is almost all database-suitable data (rows and 
> columns) in an iPhone.
>
> Technically, the data referred to is as follows.  An iPhone logs details of 
> which phone base stations it connects to, and the 'status' data obtained from 
> the base station when it was connected.  The location (long & lat) of the 
> base station is part of the base station's status string.  That is all.  Many 
> phones for many manufacturers do this: it's a vital part of the information 
> used when looking at poor signal strengths and dropped calls.
>
> I am not at all surprised that this data is collected, since it's invaluable 
> when diagnosing problems with the phone functions.  What surprises me is that 
> entries in that database are not deleted after, say, a month.
>
> 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] date field with default current date

[Fabio Spadaro]

Hi.

2011/4/21 Black, Michael (IS) 

> create table t (d default CURRENT_DATE,i number);
> insert into t (i) values(1);
> select * from t;
> 2011-04-21|1
>
>
>
> Use CURRENT_TIME if you want hours/minutes too.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
> Sent: Thursday, April 21, 2011 5:37 AM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] date field with default current date
>
> Hi.
> I'm working with python and sqlite3 and i ask how to create a table with a
> date
> field wih defaults current date.
> Thanks.
> --
> Fabio Spadaro
> www.fabiospadaro.com
> ___
> 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
>

Does not work on python with sqlite3 module

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SHA1 codes for 3.6.21 precompiled distribution

[Aydinoz, Baris]

Hi,
 
Could you please help me to find SHA1 codes or official download link
for version 3.6.21?
 
We want to upgrade to mentioned version, but I could not find any
official download link for version 3.6.21. Therefore, I plan to verify
unofficial download with SHA1 codes.
 
Thanks in advance
 
Baris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fastest way to insert MASSIVE data quantities

[Phoenix]

Hi,

I have a database with 130 million rows. It's an RDBMS.

I am thinking of using Sqlite3 as a kind of a backup, just for the
important bits of data.

Questions.

1. Is this advisable? Will Sqlite3 hold up to this volume?
2. What's the best way to do this -- dump from my RDBMS and then
"copy" back into Sqlite3?

Thanks for any ideas or pointers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] date field with default current date

[Black, Michael (IS)]

create table t (d default CURRENT_DATE,i number);
insert into t (i) values(1);
select * from t;
2011-04-21|1



Use CURRENT_TIME if you want hours/minutes too.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Fabio Spadaro [fabiolinos...@gmail.com]
Sent: Thursday, April 21, 2011 5:37 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] date field with default current date

Hi.
I'm working with python and sqlite3 and i ask how to create a table with a date
field wih defaults current date.
Thanks.
--
Fabio Spadaro
www.fabiospadaro.com
___
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] Request for help with SQLite Query to return missing Date/Time Ranges

[Black, Michael (IS)]

Assuming your database ONLY contains the log entries this should workand be 
pretty fast too since rowid is already indexed and there areYou  no other 
lookups.

You can add your own rowid to make this work otherwise.  Just do a 
max(myrowid)+1 on your insert.



PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE log(d date);
INSERT INTO "log" VALUES('2011-04-21 00:00');
INSERT INTO "log" VALUES('2011-04-21 00:01');
INSERT INTO "log" VALUES('2011-04-21 00:02');
INSERT INTO "log" VALUES('2011-04-21 00:03');
INSERT INTO "log" VALUES('2011-04-21 00:04');
INSERT INTO "log" VALUES('2011-04-21 00:06');
INSERT INTO "log" VALUES('2011-04-21 00:07');
INSERT INTO "log" VALUES('2011-04-21 00:08');
INSERT INTO "log" VALUES('2011-04-21 00:10');
INSERT INTO "log" VALUES('2011-04-21 00:13');
COMMIT;

select l2.d,l1.d,(strftime('%s',l1.d)-strftime('%s',l2.d)-60)/60 from log 
l1,log l2 where (l1.rowid=l2.rowid+1) and (strftime('%s',l1.d) - 
strftime('%s',l2.d) != 60);

2011-04-21 00:04|2011-04-21 00:06|1
2011-04-21 00:08|2011-04-21 00:10|1
2011-04-21 00:10|2011-04-21 00:13|2



The 3rd column can have a sum() to find out how many periods have been missed.  
Easy to modify this into a daily/weekly/monthly report..



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Andrew Lindsay [andrew.lind...@westnet.com.au]
Sent: Wednesday, April 20, 2011 6:34 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Request for help with SQLite Query to return missing 
Date/Time Ranges

Dear Group,



I am trying to search an SQL database that is meant to have entries logged
every minute for a period of approximately 15 months.



I want to create a query that will search through the database and tell me
for which periods I do not have any entries.



Any assistance would be greatly appreciated.



Regards



Andrew Lindsay

___
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] (no subject)

[Patkó Last namesándor]

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

[sqlite] Problems while upgrading to 3.7.3

[Gaurav Srivastava]

Hi

I have recently upgraded my sqlite version from 3.5.1 to 3.7.3. With the
update I have been seeing some issues with sqlite3_prepare_v2() API.
The workflow here is:
sqlite3VdbeSetSql(pVDbe, zSql, saveSqlflag)
^
|
|
sqlite3prepare(...)
^
|
|
sqlite3LockandPrepare(...)
^
|
|
sqlite3_prepare_v2(savesqlflag=1).

I have created a statement using sqlite3_prepare_v2. I execute a query and
then use sqlite3_step(stmt) to step over the results. Firstly, it fails due
to SQLITE_SCHEMA error and then uses sqlite3_reprepare to retry. In that
function the isPrepareV2 variable becomes false and so I am getting the
value SQLITE_OK instead of SQLITE_ROW. The second thing is in
sqlite3VdbeSwap function, we are not properly swapping the isPrepareV2
values.

I did not face these issues in 3.5.1 as it was executing the statements on
value zSql and not isPrepareV2. Could you specify the issue here or tell if
I am going wrong somewhere.

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

[sqlite] date field with default current date

[Fabio Spadaro]

Hi.
I'm working with python and sqlite3 and i ask how to create a table with a date
field wih defaults current date.
Thanks.
-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Use an Apostrophe in a Text Field?

[Jean-Christophe Deschamps]

>  The apostrophes are escaped by apostrophes.

Correct.  http://www.sqlite.org/faq.html#q14

>  One more way you can do.
>
>insert into  () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
>It is double quotes before and after *. Similarly double quotes will 
>be escaped by one more double quote

Don't do that: it can reveal a pitfall.
It's not SQL even if SQLite does its best to interpret it without 
issuing an error.

Double quotes should be reserved to enclose database, table and column 
names, not string literals. SQLite also accepts square brackets as 
well: "My Table" is the same as [My Table].

Only use single quotes (apostrophes) for string literals.

A statement like:
delete from "my table" where column = "column";
is prone to disapoint you!
http://www.sqlite.org/faq.html#q24

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

Re: [sqlite] How to Use an Apostrophe in a Text Field?

[Alan Holbrook]

Sorry if my reply didn't make it through the first time, but doubling up on
the apostrophes worked just fine.  My thanks to those of you who suggested
it.

On Thu, Apr 21, 2011 at 4:27 AM, venkat easwar  wrote:

>
>
>  The apostrophes are escaped by apostrophes. One more way you can do.
>
> insert into  () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
> It is double quotes before and after *. Similarly double quotes will be
> escaped
> by one more double quote
> VENKAT
>
>
>
>
> 
> From: Jim Morris 
> To: General Discussion of SQLite Database 
> Sent: Mon, April 18, 2011 8:25:54 PM
> Subject: Re: [sqlite] How to Use an Apostrophe in a Text Field?
>
> Did you try doubling the apostrophes?
>
> *Goin'' Down the Road Feelin'' Bad*
>
>
> On 4/17/2011 6:16 PM, Simon Slavin wrote:
> > On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
> >
> >> I'm using SQLite with VBE2008.  I've defined a table with a number of
> text
> >> fields in it.  If the information I want to write to the database
> contains
> >> an embedded apostrophe, the program throws an error.  That is, if I set
> >> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
> >> correctly and the program continues.  But if I set textfield1 to *Goin'
> Down
> >> the Road Feelin' Bad*, I get an error.
> >>
> >> Is there a way I can use an apostrophe in the data to be written?
> > Your library might do it for you.  If you're writing directly to the
> SQLite
> >library then I believe you can double the apostrophe:
> >
> > Goin'' Down the Road Feelin'' Bad
> >
> > so it might be worth trying that.
> >
> > 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
> ___
> 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] How to Use an Apostrophe in a Text Field?

[venkat easwar]

 The apostrophes are escaped by apostrophes. One more way you can do.

insert into  () values ("*Goin' Down
>> the Road Feelin' Bad*");

It is double quotes before and after *. Similarly double quotes will be escaped 
by one more double quote
VENKAT





From: Jim Morris 
To: General Discussion of SQLite Database 
Sent: Mon, April 18, 2011 8:25:54 PM
Subject: Re: [sqlite] How to Use an Apostrophe in a Text Field?

Did you try doubling the apostrophes?

*Goin'' Down the Road Feelin'' Bad*


On 4/17/2011 6:16 PM, Simon Slavin wrote:
> On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
>
>> I'm using SQLite with VBE2008.  I've defined a table with a number of text
>> fields in it.  If the information I want to write to the database contains
>> an embedded apostrophe, the program throws an error.  That is, if I set
>> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
>> correctly and the program continues.  But if I set textfield1 to *Goin' Down
>> the Road Feelin' Bad*, I get an error.
>>
>> Is there a way I can use an apostrophe in the data to be written?
> Your library might do it for you.  If you're writing directly to the SQLite 
>library then I believe you can double the apostrophe:
>
> Goin'' Down the Road Feelin'' Bad
>
> so it might be worth trying that.
>
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Security issues with SQLite3.6.23.1

[thilo]

On 4/21/2011 3:59 AM, Roger Binns wrote:
> On 04/20/2011 06:54 AM, thilo wrote:
> > They are a great tool ensuring programs have fewer memory leaks, thread
> > issues and the like and if one has access to their results, please USE
> > it and judge the false positives with human eyes - strcpy & fprintf are
> > not security risks by themselves but only in an application context.
> > Reviews (human & automated) are always a good step towards a stable
> > codebase!
>
> What you have missed is that the tool you pointed to is crap.  It gives
> noisy useless results.
Apologies, *I* didn't point to that tool!  I used coverity in a
professional environment and
found this particular great for sniffing those uninitialized or freed
pointers and dead code!
And for their advertisment (as well homeland securities)  sake they make
the results to open-source projects available for free.
Tools like coverity do have an exaggerated price tag.
>
> In addition to their brains the SQLite team also uses other tools such as
> the compiler, Coverity, clang etc.  Then they have a test suite with full
Your mail did leave the impression to me that the sqlite owners did not
read the coverity results,
if I got this wrong - good!!

cheers thilo

> MCDC coverage which means all code has to be read to be tested that
> much (on
> several platforms).  All changes are public (see the timeline) and on rare
> occasions other people may have observations.
>
> In other words the existing tools and brains are orders of magnitude
> better
> than that tool.  No one is against tools to improve the integrity of
> products but that one contributes nothing.
> If you believe it shows things existing tools don't then please enlighten
> us.  The evidence at the moment is that it wastes time better spent with
> other tools and human review.
>
> Roger
___
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] Request for help with SQLite Query to return missing Date/Time Ranges

[Oliver Peters]

Andrew Lindsay  writes:

[...]

> I am trying to search an SQL database that is meant to have entries logged
> every minute for a period of approximately 15 months.
> 
> I want to create a query that will search through the database and tell me
> for which periods I do not have any entries.
> 
[...]

very easy:

1. calculation
60*24=   1,440 minutes ( 1 day)
1,440  * 31  =  44,640 minutes ( 1 month, only an approximation
 because not every month has 31 days)
44,640 * 15  = 669,600 minutes (15 months)

-> produce ~669,600 entries that represent your minutes of a day

assuming you have a timestamp like -MM-DD HH:MM:SS in your database
your 669,600 look like this:
2011-04-21 00:00
2011-04-21 00:01
...

you can produce this very simply with a spreadsheet program like
OpenOffice Calc (the latest version has more than 1,000,000 rows)


2. Import the whole thing in a sqlite table
CREATE Table check(
minute TEXT,
PRIMARY KEY(minute)
);


3. EXCEPT
SELECT minute
FROM check
EXCEPT
SELECT substr(timestamp,1,16)
FROM
yourtable
;


That's it - I assume here that you want to check every minute but it's
easy to adapt this

greetings
oliver

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