Re: [sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?

Dennis Cote schrieb: I think it should be possible to create a subset of the standard information schema in sqlite using virtual tables. That would be very nice and consistent ! Marten - To unsubscribe, send

Re: [sqlite] Re: to quote or not ?

Thanks very much Igor, that explains it, cheers, Stef Mientki Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: So I would expect that I now can always double quote the selection fields, but unfortunately that doesn't seem to be true. From a graphical design, I get for instance:

Re: [sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?

Samuel R. Neff wrote: Dennis, Do any database systems actually implement this part of the standard? MSSQL used the term information schema in their MSSQL 2000 version of metadata access but afaik it was not close to the ANSI standard at all. It's a been a while for me but I think the Oracle

[sqlite] sqlite Performance

I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real

[sqlite] Re: SPAM-LOW: RE: [sqlite] Format of csv file RE: [sqlite] date/time implementation question

I use the tilde '~' character a lot. On Thu, 15 Mar 2007, Griggs, Donald wrote: > Regarding: "What is the default separator?" [for using with .import in > the command line utility] > > It is the vertical bar ("|", virgule, pipe character). > > By the way, I don't *think* that .import allows

[sqlite] sqlite3_update_hook

Hello, I'm trying to use the update hook functionality. I have lookup (aka join) tables that provide many-to-many relationships between rows in other tables. The problem is when I get the delete notification for the join tables the rowid is not useful in that context. I really need to know the

Re: [sqlite] sqlite Performance

Ken wrote: I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output

RE: [sqlite] sqlite Performance

Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. Flat file is 13 MB, sqlite db is 11 MB. "Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. " I may be way off base if I'm not understanding correctly, but how can

Re: [sqlite] sqlite Performance

To answer your question: Yes I can use a flat file at this stage, but eventually it needs to be imported into some type of structure. So to that end I decided early on to use sqlite to write the data out. I was hoping for better performance. The raw I/O to read the data and process is

RE: [sqlite] sqlite Performance

ok my bad for poor wording... I'll try with Synchronous off. I may also try disabling the journal file since I can easily recreate the data if it is not successful. Thanks, Ken "Griggs, Donald" <[EMAIL PROTECTED]> wrote: Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to

Re: [sqlite] sqlite Performance

Scott, The whole job is wrapped in an explicit transaction. Variables are bound and statements prepared only once, using reset. This is a write only app. 100% insert. Ken Scott Hess <[EMAIL PROTECTED]> wrote: Are you using explicit transactions at all? If not, as a quick test,

Re: [sqlite] sqlite Performance

There are no free lunches. When Sqlite stores your data item it not only writes it into a linked list of pages in a file but also inserts at least on key into a B-Tree index. It does it quite efficiently so what you are seeing is the inevitable overhead of storing the data in a structured

Re: [sqlite] sqlite Performance

Hello, IIRC (it was a while ago), one way to speed up insertion for large data sets is to drop the indexes, do the inserts (wrapped around a transaction) and then rebuild the indexes. For smaller data sets, the drop/rebuild indexes solution doesn't make sense because the time it takes to

RE: [sqlite] sqlite Performance

Donald, I set the PRAGMA synchronous= OFF and here are the results: real0m2.258s user0m1.736s sys 0m0.168s -- Pragma synchronous= NORMAL real0m2.395s user0m1.520s sys 0m0.128s Pragma synchronous= FULL real0m3.228s user

[sqlite] How to set pragma page_size

How does one set the page_size ? according to the documentation "The page-size may only be set if the database has not yet been created. " So how do you execute the pragma prior to creating the DB? without calling sqlite3_open to get a DB handle that is needed to call prepare/step ?

Re: [sqlite] sqlite Performance

Tito, There are no indices built besides the default ones. Hmm maybe I should try this by dropping the primary Keys.. I'll give that a try as well, GOOD idea! The entire batch of inserts (about 8 tables) is done in a single transaction. As an Oracle DBA, I'm pretty familar with tuning.

Re: [sqlite] sqlite Performance

Ken <[EMAIL PROTECTED]> wrote: > > I should be able to run with synchronous=off. Since > the application maintains state in a seperate DB elsewhere. > Just to clarify the implications where, if you run with synchronous=off and you take a power failure or an OS crash in the middle of a

RE: [sqlite] sqlite Performance

"Griggs, Donald" wrote on 03/15/2007 01:49:30 PM: > Regarding: > Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. > Flat file is 13 MB, sqlite db is 11 MB. > > "Any ideas how to get the sqlite output timings to a more respectable > level would be appreciated. " > I

Re: [sqlite] How to set pragma page_size

Ken <[EMAIL PROTECTED]> wrote: > How does one set the page_size ? > > according to the documentation > "The page-size may only be set if the database has not yet been created. > " > > So how do you execute the pragma prior to creating the DB? without calling > sqlite3_open to get a

Re: [sqlite] sqlite Performance

Tito, Its even better now! Synchronous=normal and No primary keys (except 1 table) for auto increment. real0m1.975s user0m1.436s sys 0m0.140s Vs flat file test case: real0m0.862s user0m0.228s sys 0m0.188s This is now very respectable. Thanks, Ken

Re: [sqlite] How to set pragma page_size

Thanks DRH... That worked. Ken [EMAIL PROTECTED] wrote: Ken wrote: > How does one set the page_size ? > > according to the documentation > "The page-size may only be set if the database has not yet been created. > " > > So how do you execute the pragma prior to creating the DB?

Re: [sqlite] sqlite Performance

DRH, Thanks for your valuable insite. When the DB is closed when in synchrounous mode, is it then persistent at the OS level even from power failures etc? [EMAIL PROTECTED] wrote: Ken wrote: > > I should be able to run with synchronous=off. Since > the application maintains

Re: [sqlite] sqlite Performance

Ken <[EMAIL PROTECTED]> wrote: > > When the DB is closed when in synchrounous mode, > is it then persistent at the OS level even from power failures etc? > You don't have to close the DB. All you have to do is commit. Before the commit finishes, all of your data is guaranteed to be on

[sqlite] Adding columns of records and updating the result to a record

Greetings! I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00

[sqlite] Re: Adding columns of records and updating the result to a record

jose isaias cabrera <[EMAIL PROTECTED]> wrote: I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund

Re: [sqlite] database is locked error with 3.3.13

I'm experiencing the same problem on QNX compiled for Renesas SH4... > This is not a bug in SQLite - it is a bug in Apple's implementation >(or more precisely their lack of implementation) of POSIX advisory > locks for AppleShare mounted volumes. The SQLite sources include > an (Apple-supplied)

[sqlite] SQLite samples or templates, including PHP

Hi All, Following in a similar thread to Shilpa's request: Are there database schema's (eg. for Phonebook ) available on the net? Are there any templates out there for SQLite databases? In particular, I'm after SQLite and PHP integrated solutions, such as perhaps a shopping cart, or

Re: [sqlite] SQLite samples or templates, including PHP

On 3/15/07, T <[EMAIL PROTECTED]> wrote: Hi All, Following in a similar thread to Shilpa's request: > Are there database schema's (eg. for Phonebook ) available on the > net? Are there any templates out there for SQLite databases? In particular, I'm after SQLite and PHP integrated solutions,

Re: [sqlite] Adding columns of records and updating the result to a record

José, As Igor rightly points out, you are doing the "wrong" thing with this tool. A perfect database program, and SQLite approaches that in its simplicity, acts as simply an ultra-efficient device to store and retrieve your data. Everything else is up to you... like a perfect audio speaker that

Re: [sqlite] Re: Adding columns of records and updating the result to a record

"Igor Tandetnik" wrote, jose isaias cabrera <[EMAIL PROTECTED]> wrote: I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine,

Re: [sqlite] Adding columns of records and updating the result to a record

"P Kishor" wrote, José, As Igor rightly points out, you are doing the "wrong" thing with this tool. A perfect database program, and SQLite approaches that in its simplicity, acts as simply an ultra-efficient device to store and retrieve your data. Everything else is up to you... like a perfect

Re: [sqlite] Adding columns of records and updating the result to a record

On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: "P Kishor" wrote, .. > Any basic tutorial in normalization and table design > will help you tremendously in figuring an efficient solution to your > problem. Any suggestions on the web? http://www.google.com/search?q=normalization

Re: [sqlite] Adding columns of records and updating the result to a record

"P Kishor" wrote, On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: "P Kishor" wrote, .. > Any basic tutorial in normalization and table design > will help you tremendously in figuring an efficient solution to your > problem. Any suggestions on the web?

Re: [sqlite] sqlite Performance

Hi Ken, you can get the exact insert speed of the flatfile.dat: - dump your data into the flat file - create a virtual table implementation for your flat file http://www.sqlite.org/cvstrac/wiki?p=VirtualTables&1150734307 - and use it from SQLite http://www.sqlite.org/lang_createvtab.html

Re: [sqlite] Re: Adding columns of records and updating the result to a record

When you design a database do not mix record (row) structures in a single table (variant records). That used to be done with ISAM files but is no longer necessary with the advent of the RDBMS. Also you should design your database to have "Third Normal Form" which means that each data item is