[sqlite] Creating recurrent appointments?
Hello I need to write an application to manage appointments. Most of them are recurrent, with no end-date, while others are one-shots, and the user must be able to schedule reccurent appointments but be able to tweak some of them if need be. To investigate whether I need to bother keeping two tables (one-shots, recurrent, and merge both to display the actual appointment book), I'd like to see how SQLite performs if I use the brute-force approach, which is pre-creating appointments till eg. 2100. Not very smart, but if it works well... Do I need to loop through this, or is there a command that lets me use a single INSERT using something like pseudo "START_DATE, END_DATE, FREQUENCY_EVERY_MONDAY_AT_10AM", etc? == sqlite> create table agenda (id INTEGER PRIMARY KEY, date VARCHAR, label VARCHAR ); sqlite> insert into agenda (id,date,label) values (NULL,"20080902", "Test"); sqlite> commit; == Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating recurrent appointments?
Gilles Ganault wrote: Hello I need to write an application to manage appointments. Most of them are recurrent, with no end-date, while others are one-shots, and the user must be able to schedule reccurent appointments but be able to tweak some of them if need be. To investigate whether I need to bother keeping two tables (one-shots, recurrent, and merge both to display the actual appointment book), I'd like to see how SQLite performs if I use the brute-force approach, which is pre-creating appointments till eg. 2100. Not very smart, but if it works well... Do I need to loop through this, or is there a command that lets me use a single INSERT using something like pseudo "START_DATE, END_DATE, FREQUENCY_EVERY_MONDAY_AT_10AM", etc? == sqlite> create table agenda (id INTEGER PRIMARY KEY, date VARCHAR, label VARCHAR ); sqlite> insert into agenda (id,date,label) values (NULL,"20080902", "Test"); sqlite> commit; == Thank you. You will need to loop through this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] statement is not executing
To further complicate things.. I removed all of the s and the code works.. I readd them and I get the same Error Executing Database Query. statement is not executing The error occurred in C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc: line 92 The Stack Trace looks like : java.sql.SQLException: statement is not executing at org.sqlite.Stmt.checkOpen(Stmt.java:41) at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86) at coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277) at coldfusion.sql.Executive.getRowSet(Executive.java:513) at coldfusion.sql.Executive.executeQuery(Executive.java:1205) at coldfusion.sql.Executive.executeQuery(Executive.java:1008) at coldfusion.sql.Executive.executeQuery(Executive.java:939) ... On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote: > I think it has to do with the JDBC driver I'm using.. I updated to > v053 of the sqlitejdbc driver and am still getting the error.. > > > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> wrote: >> I've got ColdFusion 8 connecting to my SQLite3 database.. >> >> When I run an insert using CFCs I get the error : >> Error Executing Database Query. >> statement is not executing >> >> >> What's odd is that the row gets inserted into the table... >> If I hit refresh in the browser I get the error that the Primary Key >> must be unique.. >> >> Anyone ever seen this? >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] statement is not executing
I understand this is a sqlite list, I had hoped that I wasnt the only one using it or trying to use it with ColdFusion.. I've also asked this on a CF list, just hoping somewhere out there is the answer.. Anyways, as I just posted the problem seems to be around the wrote: > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: >> To further complicate things.. I removed all of the s >> and the code works.. I readd them and I get the same >> >> >> Error Executing Database Query. >> statement is not executing >> >> >> The error occurred in >> >> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc: >> line 92 >> >> The Stack Trace looks like : >> >> java.sql.SQLException: statement is not executing >> at org.sqlite.Stmt.checkOpen(Stmt.java:41) >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86) >> at >> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277) >> at coldfusion.sql.Executive.getRowSet(Executive.java:513) >> at coldfusion.sql.Executive.executeQuery(Executive.java:1205) >> at coldfusion.sql.Executive.executeQuery(Executive.java:1008) >> at coldfusion.sql.Executive.executeQuery(Executive.java:939) >> ... >> >> >> >> >> >> On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote: >> > I think it has to do with the JDBC driver I'm using.. I updated to >> > v053 of the sqlitejdbc driver and am still getting the error.. >> > >> > >> > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> wrote: >> >> I've got ColdFusion 8 connecting to my SQLite3 database.. >> >> >> >> When I run an insert using CFCs I get the error : >> >> Error Executing Database Query. >> >> statement is not executing >> >> >> >> >> >> What's odd is that the row gets inserted into the table... >> >> If I hit refresh in the browser I get the error that the Primary Key >> >> must be unique.. >> >> >> >> Anyone ever seen this? >> >> >> > > > > Hi Greg, > > (a long time ago I used to use CF circa version 2 and 3). > > As you have yourself identified, the problem seems to be either with > your own queries, examples of which you have not provided in any of > your emails, or in the JDBC driver. Since neither CF nor the JDBC > driver are the focus of this list, you are unlikely to get much > response here... after all, this is a SQLite list, and SQLite seems to > be working fine. > > Perhaps if you were to provide example of your code, the query you are > doing, perhaps someone might be able to point out possible > logic/syntax errors, but other than that, you will likely get more > mileage from either CF list or from the provider of the JDBC driver. > > Fwiw, I have found that the JDBC driver does work fine, but most of my > queries have been rather simple via straight-ahead Java. > > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.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] statement is not executing
It's just a simple insert into table (columns) values (values) INSERT INTO BILLS ( PAYEEID, PAYEE, MINIMUMDUE, DUEDAY, ISCC, ISACTIVE, AMOUNTOWED, APR ) VALUES ( , , , , , , , ) and the DB looks like.. CREATE TABLE bills ( payeeid integer primary key, payee TEXT, minimumdue numeric, apr numeric, amountowed real, iscc numeric DEFAULT 1, dueday TEXT, isactive numeric DEFAULT 1 ) On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote: > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: >> I understand this is a sqlite list, I had hoped that I wasnt the only >> one using it or trying to use it with ColdFusion.. >> I've also asked this on a CF list, just hoping somewhere out there is >> the answer.. > > No, no... don't get me wrong. There is no problem with describing the > tools you are using (CF and JDBC driver, in this case). However, you > have given no other context... no db schema, no example code, the > query you are executing, etc. There is little here for anyone to try > and help, other than if there happens to be another CF/SQLite/JDBC > user. > > Just offer more details, and you have a slightly better chance of > getting a meaningful reply. > >> >> Anyways, as I just posted the problem seems to be around the >> > >> I suppose since this is a local app, I don't need the > tags, just looking for answers... >> >> Thanks >> >> >> >> On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote: >> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: >> >> To further complicate things.. I removed all of the s >> >> and the code works.. I readd them and I get the same >> >> >> >> >> >> Error Executing Database Query. >> >> statement is not executing >> >> >> >> >> >> The error occurred in >> >> >> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc: >> >> line 92 >> >> >> >> The Stack Trace looks like : >> >> >> >> java.sql.SQLException: statement is not executing >> >> at org.sqlite.Stmt.checkOpen(Stmt.java:41) >> >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86) >> >> at >> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277) >> >> at coldfusion.sql.Executive.getRowSet(Executive.java:513) >> >> at coldfusion.sql.Executive.executeQuery(Executive.java:1205) >> >> at coldfusion.sql.Executive.executeQuery(Executive.java:1008) >> >> at coldfusion.sql.Executive.executeQuery(Executive.java:939) >> >> ... >> >> >> >> >> >> >> >> >> >> >> >> On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote: >> >> > I think it has to do with the JDBC driver I'm using.. I updated to >> >> > v053 of the sqlitejdbc driver and am still getting the error.. >> >> > >> >> > >> >> > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> >> wrote: >> >> >> I've got ColdFusion 8 connecting to my SQLite3 database.. >> >> >> >> >> >> When I run an insert using CFCs I get the error : >> >> >> Error Executing Database Query. >> >> >> statement is not executing >> >> >> >> >> >> >> >> >> What's odd is that the row gets inserted into the table... >> >> >> If I hit refresh in the browser I get the error that the Primary Key >> >> >> must be unique.. >> >> >> >> >> >> Anyone ever seen this? >> >> >> >> >> > >> > >> > >> > Hi Greg, >> > >> > (a long time ago I used to use CF circa version 2 and 3). >> > >> > As you have yourself identified, the problem seems to be either with >> > your own queries, examples of which you have not provided in any of >> > your emails, or in the JDBC driver. Since neither CF nor the JDBC >> > driver are the focus of this list, you are unlikely to get much >> > response here... after all, this is a SQLite list, and SQLite seems to >> > be working fine. >> > >> > Perhaps if you were to provide example of your code, the query you are >> > doing, perhaps someone might be able to point out possible >> > logic/syntax errors, but other than that, you will likely get more >> > mileage from either CF list or from the provider of
Re: [sqlite] statement is not executing
Greg, I'm sorry to hear about the troubles you are running into. I've been using SQLite since I've been doing AIR development although my real bread and butter is CF development. I've never seen anyone connect CF8 and SQLite and am wondering why you have chosen that? The reason I ask is CF8 comes with Derby embedded and I would think that would be the best answer for you. Sorry I can't help any further. Ciao! Javi On Mon, Sep 1, 2008 at 11:46 AM, Greg Morphis <[EMAIL PROTECTED]> wrote: > It's just a simple insert into table (columns) values (values) > > returntype="boolean"> > > > > > > >INSERT INTO > BILLS > ( > PAYEEID, > PAYEE, > MINIMUMDUE, > DUEDAY, > ISCC, > ISACTIVE, > AMOUNTOWED, > APR > ) >VALUES > ( > > value="#arguments.bill.getPayeeID()#" > cfsqltype="cf_sql_numeric" />, > value="#arguments.bill.getPayee()#" > cfsqltype="cf_sql_longvarchar" />, > > value="#arguments.bill.getMinimumDue()#" > cfsqltype="cf_sql_numeric" />, > > value="#arguments.bill.getDueDay()#" > cfsqltype="cf_sql_longvarchar" />, > value="#arguments.bill.getIsCC()#" > cfsqltype="cf_sql_numeric" />, > > value="#arguments.bill.getIsActive()#" > cfsqltype="cf_sql_numeric" />, > > value="#arguments.bill.getAmountOwed()#" > cfsqltype="cf_sql_numeric" />, > value="#arguments.bill.getAPR()#" > cfsqltype="cf_sql_numeric" /> > ) > > > > > > > and the DB looks like.. > > CREATE TABLE bills > ( > payeeid integer primary key, > payee TEXT, > minimumdue numeric, > apr numeric, > amountowed real, > iscc numeric DEFAULT 1, > dueday TEXT, > isactive numeric DEFAULT 1 > ) > > > > > > > > On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote: > > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: > >> I understand this is a sqlite list, I had hoped that I wasnt the only > >> one using it or trying to use it with ColdFusion.. > >> I've also asked this on a CF list, just hoping somewhere out there is > >> the answer.. > > > > No, no... don't get me wrong. There is no problem with describing the > > tools you are using (CF and JDBC driver, in this case). However, you > > have given no other context... no db schema, no example code, the > > query you are executing, etc. There is little here for anyone to try > > and help, other than if there happens to be another CF/SQLite/JDBC > > user. > > > > Just offer more details, and you have a slightly better chance of > > getting a meaningful reply. > > > >> > >> Anyways, as I just posted the problem seems to be around the > >> >> > >> I suppose since this is a local app, I don't need the >> tags, just looking for answers... > >> > >> Thanks > >> > >> > >> > >> On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote: > >> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: > >> >> To further complicate things.. I removed all of the s > >> >> and the code works.. I readd them and I get the same > >> >> > >> >> > >> >> Error Executing Database Query. > >> >> statement is not executing > >> >> > >> >> > >> >> The error occurred in > >> >> > C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc: > >> >> line 92 > >> >> > >> >> The Stack Trace looks like : > >> >> > >> >> java.sql.SQLException: statement is not executing > >> >> at org.sqlite.Stmt.checkOpen(Stmt.java:41) > >> >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86) > >> >> at > coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277) > >> >> at coldfusion.sql.Executive.getRowSet(Executive.java:513) > >> >> at > coldfusion.sql.Executive.executeQuery(Executive.java:1205) > >> >> at > coldfusion.sql.Executive.executeQuery(Executive.java:1008) > >> >> at
Re: [sqlite] statement is not executing
I rebuilt the table as CREATE TABLE bills ( payeeid integer primary key, payee TEXT, minimumdue float, apr float, amountowed float, iscc integer DEFAULT 1, dueday TEXT, isactive integer DEFAULT 1 ) On Mon, Sep 1, 2008 at 10:46 AM, Greg Morphis <[EMAIL PROTECTED]> wrote: > It's just a simple insert into table (columns) values (values) > > > > > > > > >INSERT INTO > BILLS > ( > PAYEEID, > PAYEE, > MINIMUMDUE, > DUEDAY, > ISCC, > ISACTIVE, > AMOUNTOWED, > APR > ) >VALUES > ( > value="#arguments.bill.getPayeeID()#" > cfsqltype="cf_sql_numeric" />, > value="#arguments.bill.getPayee()#" > cfsqltype="cf_sql_longvarchar" />, > > value="#arguments.bill.getMinimumDue()#" > cfsqltype="cf_sql_numeric" />, > value="#arguments.bill.getDueDay()#" > cfsqltype="cf_sql_longvarchar" />, > value="#arguments.bill.getIsCC()#" > cfsqltype="cf_sql_numeric" />, > value="#arguments.bill.getIsActive()#" > cfsqltype="cf_sql_numeric" />, > > value="#arguments.bill.getAmountOwed()#" > cfsqltype="cf_sql_numeric" />, > value="#arguments.bill.getAPR()#" > cfsqltype="cf_sql_numeric" /> > ) > > > > > > > and the DB looks like.. > > CREATE TABLE bills > ( > payeeid integer primary key, > payee TEXT, > minimumdue numeric, > apr numeric, > amountowed real, > iscc numeric DEFAULT 1, > dueday TEXT, > isactive numeric DEFAULT 1 > ) > > > > > > > > On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote: >> On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: >>> I understand this is a sqlite list, I had hoped that I wasnt the only >>> one using it or trying to use it with ColdFusion.. >>> I've also asked this on a CF list, just hoping somewhere out there is >>> the answer.. >> >> No, no... don't get me wrong. There is no problem with describing the >> tools you are using (CF and JDBC driver, in this case). However, you >> have given no other context... no db schema, no example code, the >> query you are executing, etc. There is little here for anyone to try >> and help, other than if there happens to be another CF/SQLite/JDBC >> user. >> >> Just offer more details, and you have a slightly better chance of >> getting a meaningful reply. >> >>> >>> Anyways, as I just posted the problem seems to be around the >>> >> >>> I suppose since this is a local app, I don't need the >> tags, just looking for answers... >>> >>> Thanks >>> >>> >>> >>> On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote: >>> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote: >>> >> To further complicate things.. I removed all of the s >>> >> and the code works.. I readd them and I get the same >>> >> >>> >> >>> >> Error Executing Database Query. >>> >> statement is not executing >>> >> >>> >> >>> >> The error occurred in >>> >> >>> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc: >>> >> line 92 >>> >> >>> >> The Stack Trace looks like : >>> >> >>> >> java.sql.SQLException: statement is not executing >>> >> at org.sqlite.Stmt.checkOpen(Stmt.java:41) >>> >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86) >>> >> at >>> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277) >>> >> at coldfusion.sql.Executive.getRowSet(Executive.java:513) >>> >> at coldfusion.sql.Executive.executeQuery(Executive.java:1205) >>> >> at coldfusion.sql.Executive.executeQuery(Executive.java:1008) >>> >> at coldfusion.sql.Executive.executeQuery(Executive.java:939) >>> >> ... >>> >> >>> >> >>> >> >>> >> >>> >>
[sqlite] Difference in performance between 32 and 64 bit versions of SQLite?
Does anyone have any benchmarks to share that compare common SQLite operations running under a 32 and 64 bit versions of SQLite? This question is OS neutral so please feel to share your experience with 32 and 64 bit versions of Windows or Linux. Background: Will 64 bit versions of SQLite buy us any significant difference in performance? I may have a chance to get our department 64 bit AMD workstations with 8G, but I need to justify the extra cost against a reasonable guess at what the performance improvement may be against an equivalent 32 bit AMD 4G workstation. These workstations will be processing very large text based log files (> 16G each). We will be using Python 2.52 as our SQLite scripting tool. Thank you, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference in performance between 32 and 64 bit versions of SQLite?
On Mon, Sep 01, 2008 at 03:10:05PM -0400, [EMAIL PROTECTED] scratched on the wall: > Does anyone have any benchmarks to share that compare common SQLite > operations running under a 32 and 64 bit versions of SQLite? This > question is OS neutral so please feel to share your experience with 32 > and 64 bit versions of Windows or Linux. > > Background: Will 64 bit versions of SQLite buy us any significant > difference in performance? I may have a chance to get our department 64 > bit AMD workstations with 8G, but I need to justify the extra cost > against a reasonable guess at what the performance improvement may be > against an equivalent 32 bit AMD 4G workstation. These workstations will > be processing very large text based log files (> 16G each). We will be > using Python 2.52 as our SQLite scripting tool. If money is limited, the first thing I'd be looking at is I/O systems and fast storage. It doesn't matter how fast your CPU and application are if they're sitting around waiting for the drives to process data. I'd be looking at the fastest drives you can find or, if funds are sufficient, a RAID systems with lots of drives (and lots of heads) to get as much parallel I/O as you can. This is a case when eight smaller drives is better than four larger ones, assuming all else is the same. If you're processing text based log files, I assume you're just scanning those top to bottom. As a result, cache size is fairly unimportant, as you're going to read everything once and then dump it. Caches systems only provide an advantage if you come back to the data later. As the file size grows, that all comes down to raw-I/O speeds. The memory situation is less demanding as memory-mapped I/O, double-/triple-buffered asyc I/O calls, or one of the other mass-I/O techniques is very likely going to serve quite well with only 256MB or less, even for the faster RAID systems. Where memory and cache size does become very important is the database size. If you process a 20GB log file into a 1GB database, the extra address space afforded by the 64 bit system isn't going to buy you anything, since you can comfortably cache the whole database within the 32 bit address space. On the other hand, if you're talking about databases in the 3 to 6GB range and, to a lesser degree, beyond, the 64 bit system (and the extra RAM) may serve you quite well. BUT... (and there is always a "but") don't forget that writing the database doesn't get a massive boost from caching anyways, unless you play things fast and loose and turn off most of SQLite's safety nets. Assuming you're running in the normal mode where disk writes are sync'ed as much as possible, I/O speeds still play a very strong part of your total processing time any time INSERTs or UPDATEs are involved. If the processing just INSERTs records to the database, you get back into a situation much like reading the log files... pages tend to go one way (out to disk) and caching is less important. As long as you have enough cache to hold your indexes, you're fairly good to go. On the other hand, if you do stuff like record event counts, incrementing fields with each log file record that is processed, the total database cache size becomes a lot more important. That gets back to the question of database size, and if you really need the 64 bit system or not, and how much it really matters once you get much beyond ~6GB. There is also the question of what you do with these database files. If you do a lot of processing once the database files are formed, then total memory becomes important, as we can assume you're pulling a lot of pages in and out and doing all kinds of interesting things. On the other hand, if all you do is scan the data and generate a report or two and that's it, you're back to the issue of limited cache use and I/O becoming king. The "best" decision really depends on what you're trying to do with all this data. The two important points I'd make, however, are that cache doesn't matter unless you re-use the data, and that disk I/O tends to be thousands (if not millions) of times slower than CPU actions-- and that SQLite is extremely disk I/O intensive. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating recurrent appointments?
On Mon, 01 Sep 2008 15:44:05 +0300, Mihai Limbasan <[EMAIL PROTECTED]> wrote: >You will need to loop through this. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference in performance between 32 and 64 bit versionsof SQLite?
[EMAIL PROTECTED] wrote: > Does anyone have any benchmarks to share that compare common SQLite > operations running under a 32 and 64 bit versions of SQLite? This > question is OS neutral so please feel to share your experience with 32 > and 64 bit versions of Windows or Linux. > > Background: Will 64 bit versions of SQLite buy us any significant > difference in performance? I may have a chance to get our department 64 > bit AMD workstations with 8G, but I need to justify the extra cost > against a reasonable guess at what the performance improvement may be > against an equivalent 32 bit AMD 4G workstation. These workstations will > be processing very large text based log files (> 16G each). We will be > using Python 2.52 as our SQLite scripting tool. Given the potential expense, wouldn't it be justifiable to invest in a single test machine to benchmark the operations? John Elrick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in conversion from unixepoch
Using sqlite 3.4.0 (on both OS X 10.5.4 and iPhone 2.0.2) I'm seeing a
problem converting unix timestamps from today and yesterday.
In the sqlite console I get...
sqlite> select date('1220302462','unixepoch');
2008-09-01
sqlite> select date('1220249914','unixepoch');
2008-09-01
...however, the equivalent in MySQL produces...
mysql> select from_unixtime(1220302462);
+---+
| from_unixtime(1220302462) |
+---+
| 2008-09-01 13:54:22 |
+---+
1 row in set (0.00 sec)
mysql> select from_unixtime(1220249914);
+---+
| from_unixtime(1220249914) |
+---+
| 2008-08-31 23:18:34 |
+---+
1 row in set (0.00 sec)
The MySQL version is correct in the sense that it returns dates/times
that correspond to what my clock said when I created the records that
contain the timestamps in question. This was all done in the Pacific
timezone and the records were created on my iPhone.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in conversion from unixepoch
Brandon Fosdick wrote:
> sqlite> select date('1220302462','unixepoch');
> 2008-09-01
> sqlite> select date('1220249914','unixepoch');
> 2008-09-01
>
Unix times contain no time zone information (they are relative to an
epoch in the UTC time zone), and SQLite's date/time functions return
values in the UTC time zone by default:
sqlite> select datetime('1220302462','unixepoch');
2008-09-01 20:54:22
sqlite> select datetime('1220249914','unixepoch');
2008-09-01 06:18:34
But you can make them return values in the local time zone with the
|localtime| modifier:
sqlite> select datetime('1220302462','unixepoch', 'localtime');
2008-09-01 13:54:22
sqlite> select datetime('1220249914','unixepoch', 'localtime');
2008-08-31 23:18:34
> The MySQL version is correct in the sense that it returns dates/times
> that correspond to what my clock said when I created the records that
> contain the timestamps in question.
That's just a coincidence. MySQL's from_unixtime
returns values in the "current time zone," so if you'd changed zones
since inserting the records, it would show you different values.
-myk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in conversion from unixepoch
Myk Melez wrote: > Unix times contain no time zone information (they are relative to an > epoch in the UTC time zone), and SQLite's date/time functions return > values in the UTC time zone by default: argh, I should have thought of that. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.2
Are there expected performance differences (better or worse) as a result of the code factoring? On Aug 30, 2008, at 8:01 PM, D. Richard Hipp wrote: > SQLite version 3.6.2 is now available on the SQLite website: > http://www.sqlite.org/download.html > > SQLite version 3.6.2 contains rewrites of the page-cache subsystem and > the procedures for matching identifiers to table columns in SQL > statements. These changes are designed to better modularize the code > and make it more maintainable and reliable moving forward. Nearly 5000 > non-comment lines of core code (about 11.3%) have changed from the > previous release. Nevertheless, there should be no application-visible > changes, other than bug fixes. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Default Column Value to Local Time
Okay, I give up. I've been searching for a while now. I've found a number of discussions about how CURRENT_DATE returns the date in UTC, and that it can be converted to local time. But I've yet to find one article that clearly states how one might do this. I would love to have a table column default to the current date, but I want it to be the date where the computer is located, rather than some far off place. Can anyone tell me if this is possible? Thanks. -- View this message in context: http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263208.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default Column Value to Local Time
On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:
>
> Okay, I give up. I've been searching for a while now. I've found a number
> of
> discussions about how CURRENT_DATE returns the date in UTC, and that it can
> be converted to local time. But I've yet to find one article that clearly
> states how one might do this.
>
> I would love to have a table column default to the current date, but I want
> it to be the date where the computer is located, rather than some far off
> place.
>
> Can anyone tell me if this is possible?
I think this is the page you're looking for:
http://www.sqlite.org/lang_datefunc.html
In particular, a query that returns the current time in the local (to
sqlite) time zone is:
SELECT datetime('now', 'localtime');
or for just the date:
SELECT date('now', 'localtime');
Note, however, that if sqlite is being accessed by your web server,
'localtime' is in reference to the web server's time zone, not the time zone
of the web browser which could be anyplace in the world. If your
application is running all locally, the above should work.
Cheers,
Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default Column Value to Local Time
On 9/1/08, jonwood <[EMAIL PROTECTED]> wrote:
>
> Okay, I give up. I've been searching for a while now. I've found a number of
> discussions about how CURRENT_DATE returns the date in UTC, and that it can
> be converted to local time. But I've yet to find one article that clearly
> states how one might do this.
>
> I would love to have a table column default to the current date, but I want
> it to be the date where the computer is located, rather than some far off
> place.
>
> Can anyone tell me if this is possible?
Don't think it is possible. As you noted, the docs say very clearly
(http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
constraint specifies a default value to use when doing an INSERT. ..
default value may also be one of the special case-independant keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
date and/or time is inserted into the columns. "
So, you can store only UTC date/time, but you can always convert on
retrieving the values by using datetime('now', 'localtime')
One thing to note -- the UTC date/time doesn't seem to account for
daylight savings adjustment. For example, I am right now in Washington
DC where it is 10:30 PM. It is 3:30 AM in the UK right now, but per
SQLite (the UTC bit), it is 2:30 AM. I guess I have to do all this
adjustment in my queries and application.
>
> Thanks.
>
> --
> View this message in context:
> http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263208.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default Column Value to Local Time
Derrell Lipman wrote:
>
> I think this is the page you're looking for:
> http://www.sqlite.org/lang_datefunc.html
>
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
>
Well, okay. That's one of the articles I did see. Right now, I'm having two
issues:
1. I'm not sure how to use those functions. You've gave me some examples,
which I appreciate. But your examples don't work with columns. Perhaps the
first argument needs to be my column name?
2. I'm currently using a default value of CURRENT_DATE in several tables. At
this stage, it would be a lot less work if I could continue to not explictly
specify the initial value for these columns. Can anyone confirm that this is
simply not possible to do this with the local time? (I want the value
actually stored in the database to be local time, and not to simply convert
the UTC value every time it is used.)
Thanks!
--
View this message in context:
http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263339.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default Column Value to Local Time
P Kishor-3 wrote: > > Don't think it is possible. As you noted, the docs say very clearly > (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT > constraint specifies a default value to use when doing an INSERT. .. > default value may also be one of the special case-independant keywords > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC > date and/or time is inserted into the columns. " > Thanks for your input. This is really a shame, and quite surprising, that it works this way with no means to modify the behavior. There are all sorts of flags to modify how the source code works, but I could find nothing related to this. Based on my previous searches, I am not the only one who thinks it should work this way. It should at least be an option. Thanks. -- View this message in context: http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263371.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference in performance between 32 and 64 bit versions of SQLite?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > Background: Will 64 bit versions of SQLite buy us any significant > difference in performance? You need to have some benchmark code first, ideally in the form of a representative script to feed to the sqlite shell. That way we can run the code for you and give feedback. In the specific case of SQLite the biggest determinant of performance are the hard drives and the speed they spin at. For transactions, data has to be on the oxide and confirmed as such by the drive. For 7200rpm drives, each spot is under the heads 120 times per second. Each transaction requires two syncs so the maximum possible transaction rate is 60 per second. This will apply even if the data is spread across raid stripes since every drive in the array still has to confirm the data is committed. For random access the latency is also affected by the drive speed, limiting you to a maximum of 120 random accesses per second. Sequential access will obviously be significantly faster but again the maximum rate will be a factor of packing density and rpm. You can increase the amount of data that can be sent to/read from the drives by using a RAID array, or by using drives that support NCQ. For example my bog standard Seagate 750GB drive will accept 100MB/s for writes and does 70MB/s for mostly sequential reads. A two drive array would roughly double that. > I may have a chance to get our department 64 bit AMD workstations It is a very good idea to get 64 bit machines and all current workstation processors are 64 bit anyway. 64 bit Windows and Linux will also run and compile 32 bit binaries so you can test compatibility. > with 8G, It is pointless having more than about 3G with 32 bit operating systems as they need the last 1G of physical address space for peripherals, video memory etc. > These workstations will > be processing very large text based log files (> 16G each). Generally using 64 bit operating systems makes life considerably easier for developers. As an example you can just memory map the files and then treat them as a char*. You can assemble multiple files in memory, do sorting etc without having to worry about hitting limits. Note that SQLite itself has limits which are 32 bit based even in a 64 bit process. For example the largest a single record could be is 2GB (signed 32 bit maxint). In terms of general 32 bit vs 64 bit performance, the AMD64 instruction set doubles the number of registers from 8 to 16. But pointers take up twice the space (8 rather than 4 bytes). So basically the code can have more balls in the air, but requires more memory accessed to operate. If you have access to 64 bit Linux you can compile 64 and 32 bit versions of the SQLite shell for easy comparison. $ ./configure --disable-shared CC="gcc -m32" && make && mv sqlite3 \ sqlite3-32 Repeat replacing 32 with 64. The resulting binary is 371kb (32 bit) and 428kb (64 bit). > We will be > using Python 2.52 as our SQLite scripting tool. Good tool choice. That is far more productive for developers than C development :-) You may also want to try the APSW bindings as well as pysqlite. APSW is reported to be faster by others doing benchmarking. (Disclaimer: I am the author of APSW). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIvK5pmOOfHg372QQRAs0zAJ9IIxOEFE+p9eLXNlyPOOoQFUu5pwCgoJiJ bgnWRsZ58n3bGkgeGFwuHS8= =QEH7 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default Column Value to Local Time
On 9/1/08, jonwood <[EMAIL PROTECTED]> wrote: > > > P Kishor-3 wrote: > > > > Don't think it is possible. As you noted, the docs say very clearly > > (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT > > constraint specifies a default value to use when doing an INSERT. .. > > default value may also be one of the special case-independant keywords > > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is > > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC > > date and/or time is inserted into the columns. " > > > > > Thanks for your input. This is really a shame, and quite surprising, that it > works this way with no means to modify the behavior. There are all sorts of > flags to modify how the source code works, but I could find nothing related > to this. Based on my previous searches, I am not the only one who thinks it > should work this way. It should at least be an option. well, maybe. How data are stored is/should_be irrelevant to the end user. In fact, the best way to store dates might well be just numbers (search the archives for discussions on how to best store dates/times). The key is in retrieving and displaying (or doing whatever one wants to do with them) the data. You have all the options in the world to use the datetime functions for that, or, even more so in your application. All you have to do is to remember how your data were inserted in the first place. > > Thanks. > > -- > View this message in context: > http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263371.html > > Sent from the SQLite mailing list archive at Nabble.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] Default Column Value to Local Time
On Mon, Sep 1, 2008 at 10:29 PM, Derrell Lipman <
[EMAIL PROTECTED]> wrote:
>
>
> On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:
>
>>
>> Okay, I give up. I've been searching for a while now. I've found a number
>> of
>> discussions about how CURRENT_DATE returns the date in UTC, and that it
>> can
>> be converted to local time. But I've yet to find one article that clearly
>> states how one might do this.
>>
>> I would love to have a table column default to the current date, but I
>> want
>> it to be the date where the computer is located, rather than some far off
>> place.
>>
>> Can anyone tell me if this is possible?
>
>
> I think this is the page you're looking for:
> http://www.sqlite.org/lang_datefunc.html
>
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
>
> Note, however, that if sqlite is being accessed by your web server,
> 'localtime' is in reference to the web server's time zone, not the time zone
> of the web browser which could be anyplace in the world. If your
> application is running all locally, the above should work.
>
I guess I didn't quite complete the picture here. I don't think you can use
functions as default values, but you can easily use triggers to accomplish
the same thing, like this:
sqlite> CREATE TABLE x (i INTEGER PRIMARY KEY, t TIMESTAMP);
sqlite> CREATE TRIGGER x_insert_tr
...> AFTER INSERT ON X
...> FOR EACH ROW
...> BEGIN
...> UPDATE x
...> SET t = datetime('now', 'localtime')
...>WHERE i = new.i;
...> END;
sqlite> INSERT INTO x (i) VALUES (1);
sqlite> INSERT INTO x (i) VALUES (2);
sqlite> INSERT INTO x (i) VALUES (3);
sqlite> SELECT * FROM x;
1|2008-09-01 23:32:49
2|2008-09-01 23:32:55
3|2008-09-01 23:33:01
sqlite>
Those times shown above are when I ran the INSERT queries, in my local time
zone.
Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating recurrent appointments?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gilles Ganault wrote: > I need to write an application to manage appointments. Most of them > are recurrent, with no end-date, while others are one-shots, and the > user must be able to schedule reccurent appointments but be able to > tweak some of them if need be. BTW the way most apps, phones and even standards manage this is by storing a list of events each of which has start and end dates for repeating, and a list of exceptions. A one shot event would have no start or end date. An event that repeats forever would have a start date but no end date. The exceptions override existing repeating events. For example say you have an event that repeats every Tuesday at 3pm. If I want it to be 4pm for next Tuesday then I'd create an exception event pointing at the original but with a different time. You also need exception events that can cancel an existing one. In your user interface or equivalent you have to establish when a user is editing a repeating event whether they are changing just that one instance or the series. The former edits/creates exceptions and the later edits the original. Most applications just flat out ask you when editting which one it is. You should also look into vCalendar/iCalendar standards since that will make import/export easier and make your life easier if you structure your data similar to what they require. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIvMA1mOOfHg372QQRAmY4AJ9JC2Muslq/ollOwCBuVK2nBRTJvgCfYKBM LnYj+ZCEXTIDK0Dzios7yKI= =5wtg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
