Re: [sqlite] NFS--how to test?
On Sat, 2008-11-08 at 22:34 -0800, Peter A. Friend wrote: > Simultaneous access over NFS will *not* work...Access will have to > serialized somehow. Multiple readers should still work, right? Like many web apps, mine tend to be very read-heavy. But I think if we're talking about having to do some other, out-of-band thing to prevent corruption for writes, I had better just use MySQL if I use Mosso. -- Joshua Paine LetterBlock: Web applications built with joy http://letterblock.com/ 301-576-1920 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving a memory-based SQLite database to disk
On Sat, Nov 08, 2008 at 03:34:29PM -0800, Tito Ciuro scratched on the wall: > Hello, > > If I open a SQLite database in memory (using :memory:), would it be > possible to save it on disk? Here's the reason: > Manipulating data in memory is ~3.4x faster than using the file system > (YMMV). If I could process the bulk of the data in RAM, then I could > save the final database on disk saving precious time. Another option is to use an on-disk database, but turn off most of the safety and security features. -- Turn the page cache up so it is large enough to hold the whole database in the cache. -- Turn the journal file off. -- Turn synchronous off. (See http://sqlite.org/pragma.html for how to do all that) There will be some start-up cost as the database is pulled into the cache, and it won't be quite as fast as a true :memory: database, since it still writes out data into the OS file-cache buffers. Overall your performance will be very similar to a fully in-memory database, plus you won't have to deal with shuffling data back and forth between a memory database and a disk database. Of course, you get most of that performance by turning off most of the safety and security features so the reliability of a database in this mode is more or less the same as an in-memory database... the database is very likely to get corrupt if you suffer a process or system failure. But it is faster. And sometimes that's what counts. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] selecting top entry from each day
Hi all, I have a table 'stats' with following schema date varchar (25), utilization int, os_name varchar(25) ... I am recording host utilization for windows and linux operating systems (os_names) at various times in a day in this table. The date column has a format of -mm-dd. I want to do the following: 1. select top utilization for each day for all days recorded so far. 2. display a table with columns: date, utilization_linux, utilization_windows from the recorded data. any idea how to structure a query? for item 1 I am using the following query but it gives me only one row of max utilization so far. "select date, max(utilization) from stats where os='win2k3' and date in (select distinct date from stats ) order by date;" I have absolutely no idea how to write the second query. Any help is a appreciated. Thanks, T. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deleting sqlite rows
On 11/9/08, LordEricO <[EMAIL PROTECTED]> wrote: > > I am using jdbc to work with my sqlite database in java. I need to remove a > row of in my database. However, I never really designed a column that could > be a primary key. I think sqlite puts it's own auto-incremented value for > each row in automatically. Is there a way I could use that to delete the > row? Or do I have to create an "ID" column? > Thanks, > Eric > > -- did you read the docs at all? See http://www.sqlite.org/lang_createtable.html "Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately." -- 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] hierarchical queries
On Sun, 9 Nov 2008 20:39:23 +0100, "Sébastien Roux" <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >Hi, > >Is there a way of doing hierarchical queries with SQLite. I'm not a killer >with SQL language but I've seen that there are some hierarchical functions >under Oracle. >I'm having a hierarchy stored as a parent/child relationship. SQLite doesn't support recursive statements. Perhaps you can convert the structure to a nested set model, so you can avoid recursion. http://www.dbazine.com/oracle/or-articles/tropashko4 http://www.developersdex.com/gurus/articles/112.asp >Thanks for your help. > >Sébastien Roux -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting top entry from each day
"Tejas" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have a table 'stats' with following schema > date varchar (25), utilization int, os_name varchar(25) ... > > I am recording host utilization for windows and linux operating > systems (os_names) at various times in a day in this table. The date > column has a format of -mm-dd. > > I want to do the following: > 1. select top utilization for each day for all days recorded so far. select date, max(utilization) group by date; > 2. display a table with columns: date, utilization_linux, > utilization_windows from the recorded data. select date, max(case when os_name='linux' then utilization else 0 end) utilization_linux, max(case when os_name='windows' then utilization else 0 end) utilization_windows group by date; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving a memory-based SQLite database to disk
Hi Jay, Definitely worth investigating. Thanks for the tip! -- Tito On 9 Nov 2008, at 8:44 AM, Jay A. Kreibich wrote: > On Sat, Nov 08, 2008 at 03:34:29PM -0800, Tito Ciuro scratched on > the wall: >> Hello, >> >> If I open a SQLite database in memory (using :memory:), would it be >> possible to save it on disk? Here's the reason: > >> Manipulating data in memory is ~3.4x faster than using the file >> system >> (YMMV). If I could process the bulk of the data in RAM, then I could >> save the final database on disk saving precious time. > > > Another option is to use an on-disk database, but turn off most of > the safety and security features. > > -- Turn the page cache up so it is large enough to hold the whole > database in the cache. > > -- Turn the journal file off. > > -- Turn synchronous off. > > (See http://sqlite.org/pragma.html for how to do all that) > > There will be some start-up cost as the database is pulled into the > cache, and it won't be quite as fast as a true :memory: database, > since it still writes out data into the OS file-cache buffers. > Overall your performance will be very similar to a fully in-memory > database, plus you won't have to deal with shuffling data back and > forth between a memory database and a disk database. > > Of course, you get most of that performance by turning off most > of the safety and security features so the reliability of a database > in this mode is more or less the same as an in-memory database... > the database is very likely to get corrupt if you suffer a process or > system failure. > > But it is faster. And sometimes that's what counts. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > 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] hierarchical queries
Hi, Is there a way of doing hierarchical queries with SQLite. I'm not a killer with SQL language but I've seen that there are some hierarchical functions under Oracle. I'm having a hierarchy stored as a parent/child relationship. Thanks for your help. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selecting top entry from each day
On Sun, Nov 9, 2008 at 1:40 PM, Tejas <[EMAIL PROTECTED]> wrote: > Hi all, > > I have a table 'stats' with following schema > date varchar (25), utilization int, os_name varchar(25) ... > > I am recording host utilization for windows and linux operating systems > (os_names) at various times in a day in this table. The date column has a > format of -mm-dd. > > I want to do the following: > 1. select top utilization for each day for all days recorded so far. > 2. display a table with columns: date, utilization_linux, > utilization_windows from the recorded data. > > any idea how to structure a query? > for item 1 I am using the following query but it gives me only one row of > max utilization so far. > "select date, max(utilization) from stats where os='win2k3' and date in > (select distinct date from stats ) order by date;" If I understand you correctly, what you want can be provided by GROUP BY: select date, max(utilization) from stats where os='win2k3' group by date -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving a memory-based SQLite database to disk
Thank you for the pointer Joshua. -- Tito On 8 Nov 2008, at 3:46 PM, Joshua Paine wrote: > Tito Ciuro wrote: >> If I open a SQLite database in memory (using :memory:), would it be >> possible to save it on disk? > > Open a disk db and use the ATTACH sql command to add a memory DB. Do > your ops in the memory DB, then insert select the results into your > disk db. > > -- > Joshua Paine > LetterBlock: Web applications built with joy > http://letterblock.com/ > 301-576-1920 > ___ > 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] Deleting sqlite rows
I am using jdbc to work with my sqlite database in java. I need to remove a row of in my database. However, I never really designed a column that could be a primary key. I think sqlite puts it's own auto-incremented value for each row in automatically. Is there a way I could use that to delete the row? Or do I have to create an "ID" column? Thanks, Eric -- View this message in context: http://www.nabble.com/Deleting-sqlite-rows-tp20412330p20412330.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
