Re: [sqlite] datetime, its customary column affinity, and storage class info

2012-10-22 Thread John Gabriele
On Mon, Oct 22, 2012 at 11:38 PM, Simon Slavin wrote: > > On 23 Oct 2012, at 3:42am, John Gabriele wrote: > >> Which column affinity is most customary to use for storing "-MM-DD >> HH:MM:SS" datetime values? > > Text. They are just text. As you've

Re: [sqlite] datetime, its customary column affinity, and storage class info

2012-10-22 Thread Simon Slavin
On 23 Oct 2012, at 3:42am, John Gabriele wrote: > Which column affinity is most customary to use for storing "-MM-DD > HH:MM:SS" datetime values? Text. They are just text. As you've figured out, SQLite has no datetime datatype. > The docs at

Re: [sqlite] insert if in SQL cmd

2012-10-22 Thread Igor Tandetnik
YAN HONG YE wrote: > I need a function to run step 2 in sql cmd, but I don't know how to write in > the sql environment. > > 1. BEGIN TRANSACTION; > 2. if today is mondy to fridy,then run 3-6 >ELSE run 6. > 3. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A

Re: [sqlite] insert if in SQL cmd

2012-10-22 Thread Simon Slavin
On 23 Oct 2012, at 3:01am, YAN HONG YE wrote: > 3. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A is not > finalized or reset; > 4. execute stmt B (DELETE FROM foo WHERE rowid=1), B is prepared, executed > and finalized; > 5. access column data via stmt A

Re: [sqlite] datetime, its customary column affinity, and storage class info

2012-10-22 Thread Igor Tandetnik
John Gabriele wrote: > Which column affinity is most customary to use for storing "-MM-DD > HH:MM:SS" datetime values? Text. > I tried this: > > ~~~sql > create table t1 ( > id integer primary key, > this_date text, > that_date int, > other_date none); > > insert

Re: [sqlite] datetime, its customary column affinity, and storage class info

2012-10-22 Thread Richard Hipp
On Mon, Oct 22, 2012 at 10:42 PM, John Gabriele wrote: > Hi, > > Which column affinity is most customary to use for storing "-MM-DD > HH:MM:SS" datetime values? > I always use DATE or DATETIME or TIME, depending on what I'm storing. I believe these always have affinity

[sqlite] datetime, its customary column affinity, and storage class info

2012-10-22 Thread John Gabriele
Hi, Which column affinity is most customary to use for storing "-MM-DD HH:MM:SS" datetime values? I tried this: ~~~sql create table t1 ( id integer primary key, this_date text, that_date int, other_date none); insert into t1 (this_date, that_date, other_date) values

[sqlite] insert if in SQL cmd

2012-10-22 Thread YAN HONG YE
I need a function to run step 2 in sql cmd, but I don't know how to write in the sql environment. 1. BEGIN TRANSACTION; 2. if today is mondy to fridy,then run 3-6 ELSE run 6. 3. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A is not finalized or reset; 4. execute stmt B (DELETE

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Simon Slavin
On 22 Oct 2012, at 8:35pm, Paul van Helden wrote: >> It would be possible to implement TRUNCATE TABLE on top of that, but >> this would be only syntactic sugar. > > ..or better portability. TRUNCATE TABLE works (since only a few years) > nearly everywhere. So when writing

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Kees Nuyt
On Mon, 22 Oct 2012 16:44:12 -0400, Guillaume Saumure wrote: >. Anyway Something I can >do with >PureBasic is : > >Procedure.b TruncateDatabaseTable(DataBaseID.l, TableName.s) > > If DatabaseUpdate(DatabaseID, "TRUNCATE TABLE " + TableName) > Protected Success.b =

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Igor Tandetnik
On 10/22/2012 4:44 PM, Guillaume Saumure wrote: Personally, I have ask the question why TRUNCATE TABLE don't work because this command is not listed on http://www.sqlite.org/omitted.html This site list the non-supported feature of SQL92 standard and TRUNCATE TABLE is not listed so it should

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Duquette, William H (318K)
On 10/22/12 1:44 PM, "Guillaume Saumure" wrote: >Le 2012-10-22 15:35, Paul van Helden a écrit : >>> It would be possible to implement TRUNCATE TABLE on top of that, but >>> this would be only syntactic sugar. >>> >> ..or better portability. TRUNCATE TABLE works (since

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Guillaume Saumure
Le 2012-10-22 15:35, Paul van Helden a écrit : It would be possible to implement TRUNCATE TABLE on top of that, but this would be only syntactic sugar. ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Paul van Helden
> > It would be possible to implement TRUNCATE TABLE on top of that, but > this would be only syntactic sugar. > ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit silly to make an exception for SQLite if

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread giris
Thanks. Good to know. From: Clemens Ladisch To: sqlite-users@sqlite.org Sent: Monday, October 22, 2012 1:02 PM Subject: Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM giris wrote: > Rather, the implementation/internals of SQLite need

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Clemens Ladisch
giris wrote: > Rather, the implementation/internals of SQLite need to change to allow this > very low overhead form of DELETE. Actually, SQLite already implements the low-overhead from of DELETE. src/delete.c has in sqlite3DeleteFrom(): #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

2012-10-22 Thread Black, Michael (IS)
Have you tried making your own DLL from each source and comparing them when they are compiled the same? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From:

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread giris
Hi: Mapping TRUNCATE table to DELETE * is not going to help and seems like a bad idea. TRUNCATE table is an artifact implemented in many SQL implementations, and can make a world of difference in performance when deleting all rows  in a table. It avoids all the overhead of transaction logging,

[sqlite] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Paul van Helden
Hi, TRUNCATE TABLE is now in the SQL:2008 standard. http://en.wikipedia.org/wiki/Truncate_(SQL) It would make portability easier if SQLite understood TRUNCATE TABLE to be the same as DELETE FROM without WHERE. Yes? No? Paul. ___ sqlite-users mailing

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

2012-10-22 Thread Imanuel
Hi Dan I don't use a wrapper in the application (at least not in the test application), and I don't issue any PRAGMAs. I have tried changing cache_size, but that didn't change the results (I have tried 128 (default), 2000 and 10). I don't know how to intercept calls to the VFS interface, so I

Re: [sqlite] Transaction inside transaction

2012-10-22 Thread Clemens Ladisch
Igor Korot wrote: > On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan > wrote: >> If you want a rollback on the inner to rollback the outer unconditionally, >> you never needed a nested transaction in the first place, but rather just >> the one outer transaction 1, > > 1.

Re: [sqlite] Transaction inside transaction

2012-10-22 Thread Igor Korot
Darren, On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan wrote: > Igor Korot wrote: >> >> Hi, ALL, >> Is it possible to have transaction inside transaction? Will it be >> handled correctly? >> >> What I mean is: crate transaction 1 by issuing "BEGIN", create >>

Re: [sqlite] Transaction inside transaction

2012-10-22 Thread Darren Duncan
Igor Korot wrote: Hi, ALL, Is it possible to have transaction inside transaction? Will it be handled correctly? What I mean is: crate transaction 1 by issuing "BEGIN", create transaction 2 by issuing "BEGIN". Close transaction 2 by issuing either "COMMIT" or "ROLLBACK". If "ROLLBACK" is issued,