Re: [sqlite] Data encryption

2007-11-20 Thread Günter Greschenz
hi everybody, the magic spell worked, but onky for me, not for my server: at the moment i have problems with my (free) dyndns account. my server (a little nslu2 in my living room) was online for some hours yesterday but disappeared again this morning ... until i solved my problem here are the

Re: [sqlite] Memory Usage

2007-11-20 Thread Joe Wilson
Once again you're missing the point. Of course you can get a malloc/free implementation that performs garbage collection, such as Boehm's conservative GC. But C garbage collection and malloc/free memory fragmentation are quite different things. You can still get heavily fragmented memory with

Re: [sqlite] Can't checkout from cvs

2007-11-20 Thread Rael Bauer
Hi, Yes that does seem to bypass the login prompt. It says "logging in to...". But still can't actually connect to server. windows has only recently been installed (winxp sp2). also get same problem with firewall turned off. Any other reason why I can't connect to server?

[sqlite] String manipulation with pure sqlite3?

2007-11-20 Thread Alexander Skwar
Hello. Suppose I've got tables like this: sqlite> .schema t1 CREATE TABLE t1 (id integer primary key not null, name); sqlite> .schema t2 CREATE TABLE t2 (t1id integer, txt STRING NOT NULL); Filled with: sqlite> select * from t1; 1|foo.bar.boing

Re: [sqlite] String manipulation with pure sqlite3?

2007-11-20 Thread Daniel Önnerby
This should be a simple task for any programming language to do once the results has been retrieved. With the current expressions in SQLite I believe there is no way to do this unless you extend SQLite with your own "string_find_last" or "replace_last" function. Alexander Skwar wrote: Hello.

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread Dennis Cote
Michael Ruck wrote: Ah. I have been reading a PHP/MySQL book, that I thought said a MySQL server would see the common column names and automagically join the 2. Either I misremember what the book said (it's not with me here), or this is a feature of MySQL, not present in SQLite. Anyway, what

AW: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread Michael Ruck
I know that a natural join exists, but it is not automatic as it seems to be in MySQL. > -Ursprüngliche Nachricht- > Von: Dennis Cote [mailto:[EMAIL PROTECTED] > Gesendet: Dienstag, 20. November 2007 18:32 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Re: Performance tuning, and

Re: [sqlite] String manipulation with pure sqlite3?

2007-11-20 Thread Alexander Skwar
Daniel Önnerby schrieb: This should be a simple task for any programming language to do once the results has been retrieved. Yes, of course. But it would be nice, if that could be done on SQL level. With the current expressions in SQLite I believe there is no way to do this unless you extend

[sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Scott Krig
What are the 'biggest bang for the buck' sqlite optimization techniques to apply to a working system to tune performance? Q1)) PRAGMA: Does anyone have experience and good results optimizing sqlite performance using PRAGMA's? If so, which ones, how were they used, and what was the performance

[sqlite] trouble with TRIGGERs

2007-11-20 Thread P Kishor
I have a table CREATE TABLE foo ( foo_id INTEGER PRIMARY KEY, foo_name TEXT, foo_text TEXT ); I have created a virtual table to do full text search CREATE VIRTUAL TABLE fts_foo ( USING fts2(foo_text); I have populated this table INSERT INTO fts_foo (rowid, foo_text) VALUES (foo.foo_id,

RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Tom Briggs
Which pragmas will be most effective (and what values you should use for each) depends on what you're trying to do with the database. Synchronous is important if you're writing frequently, for example, but won't matter much in a read-only setting. Appropriate values for the page_size and

[sqlite] Re: trouble with TRIGGERs

2007-11-20 Thread P Kishor
solved... case of the missing ; after the UPDATE and INSERT statements. On Nov 20, 2007 12:35 PM, P Kishor <[EMAIL PROTECTED]> wrote: > I have a table > > CREATE TABLE foo ( > foo_id INTEGER PRIMARY KEY, > foo_name TEXT, > foo_text TEXT > ); > > I have created a virtual table to do full

RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Scott Krig
To the point, the questions are: Q1)) PRAGMA: Does anyone have experience and good results optimizing sqlite performance using PRAGMA's? If so, which ones, how were they used, and what was the performance increase? Q2)) Other techniques: Any success stories on sqlite optimization methods of any

Re: [sqlite] Need help reading 3.3.2 database files with 3.5.2...

2007-11-20 Thread drh
Richard Klein <[EMAIL PROTECTED]> wrote: > > The previous statement is actually more general: SQLite > > version 3.x.y can read and write any database created by > > any prior version of SQLite. > > Even SQLite 2.w.z ? No. Any prior 3.x.x version of SQLite.

RE: [sqlite] Resetting a Primary Key

2007-11-20 Thread Griggs, Donald
Hi Mitchell, I don't know that resetting the primary key would be productive, since rollover of INTEGER PRIMARY KEY would not occur anywhere even remotely close to 15000. Are you sure you aren't somehow attempting an insert of a key that has already been used -- perhaps because of some race

RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Joe Wilson
Why not try benchmarking the pragmas yourself and posting your findings to the list? http://www.sqlite.org/pragma.html --- Scott Krig <[EMAIL PROTECTED]> wrote: > Q1)) PRAGMA: Does anyone have experience and good results optimizing > sqlite performance using PRAGMA's? If so, which ones, how

RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Tom Briggs
re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think that your question is too broad to be answerable (unless you're actually attempting to assemble a collection of optimal values in all possible situations for all existing pragmas... ;shrug) re: Q2 - At the risk of sounding

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread drh
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > ... is giving me "primary key must be unique" errors. > > How can I reset the sequence for a

Re: [sqlite] String manipulation with pure sqlite3?

2007-11-20 Thread Ken
I think you just need to implement an INSTR(x,y,z) where X is the input string. Y is the search string, and z is the search starting location. Typically negative numbers indicate the end of the string to search backwards. So Substr(t1.col, 0, instr(t1.col, '.', -1) ) || '(' t2.col ||')'

RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Scott Krig
There are apparently no folks with the experience to answer the questions as given? -Original Message- From: Tom Briggs [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
No doubt, it is obviously something that has been screwed up. Unfortunately I'm in "hot fix" mode right now and the investigation into why it happened will happen after I get this customer back up and running. I have a feeling it has something to do with the ODBC driver being used. I'll gather and

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text ,

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread James Steward
> Michael Ruck <[EMAIL PROTECTED]> wrote: > > I know that a natural join exists, but it is not automatic as > it seems to be in MySQL. Thanks , and thanks to all who replied to my questions. I've been testing SQLite's speed, for inserting the type of data I gather from the field. I've tried

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Dennis Cote
Mitchell Vincent wrote: Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text ,

RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Lee Crain
Tom Brigg's response to your question 2 was an excellent response. Efficiently constructed queries will generally produce the best results. Also, indexes on fields contained in WHERE clauses will generally produce good results. Lee _ -Original

Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread drh
James Steward <[EMAIL PROTECTED]> wrote: > > Michael Ruck <[EMAIL PROTECTED]> wrote: > > > > I know that a natural join exists, but it is not automatic as > > it seems to be in MySQL. > > Thanks , and thanks to all who replied to my questions. > > I've been testing SQLite's speed, for

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread drh
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > Here we go. I am accessing the database through an ODBC connection > using the most recent version from Christian's site.. > > The table schema : > > CREATE TABLE invoice_items ( > item_id INTEGER PRIMARY KEY, > invoice_id int4, > product_id

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
No triggers at all in the database. It's pretty vanilla.. On Nov 20, 2007 6:04 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > >

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread Dennis Cote
James Steward wrote: I can receive up to 2,000,000 records in about 30 seconds from the field, and I can't seem to jam them into an SQLite database any faster than about 100 seconds at best, on my system. So unless anyone can suggest some magic I have not thought of, I will have to abandon the

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Dennis Cote
Mitchell Vincent wrote: select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread James Steward
Dennis Cote <[EMAIL PROTECTED]> wrote: Hi Dennis, > You haven't said what your system is, but to record your data in real > time you will need to insert about 70K records per second. That is high, > > but not impossible for SQLite, so I wouldn't give up yet. I have had > SQLite doing 60K

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how the corruption happened but

Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread Ken
Or you will need to get a faster disk drive! And more of them. Consider a raid 0 system using striping. High speed fiber connects will also be helpful. Can you do a test load to a memory database. What is the performance (row/sec) of your data using an in memory database? Is

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread James Steward
> James Steward <[EMAIL PROTECTED]> wrote: > > Dennis Cote <[EMAIL PROTECTED]> wrote: > > > You have said you tried both the TCL and C APIs, but you didn't say if > > you were using prepared insert statements in the C API. If not, that > > will save the overhead of parsing and code generation

Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread James Steward
> Ken <[EMAIL PROTECTED]> wrote: > > Can you do a test load to a memory database. What is the performance > (row/sec) of your data using an in memory database? I tried a RAMDisk on Windows. I haven't tried at home on Linux. I'm not sure if I tried the C API to RAMDisk database combination,

Re: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Nuno Lucas
On 11/20/07, Scott Krig <[EMAIL PROTECTED]> wrote: > There are apparently no folks with the experience to answer the > questions as given? Those who have experience know better than to try to answer in an e-mail what 1000 mails in the mailing list are not enough. The wiki and documentation have