Re: [sqlite] ChangePassword method problem

Sorry, I should clarify, I meant the chances of the database corruption. You are right, in a properly designed system the access to the database would take into account a changed password, which would be the normal scenario. But, if there is even a small possibility that a database would be

Re: [sqlite] ChangePassword method problem

On 24 Oct 2011, at 4:42am, Farhan Husain wrote: > So, I was just wondering how you would deal with multiple processes accessing > the database. You can't guarantee that all would be closing the connection > properly. It would seem that if all connections needed to be closed before a > proper

Re: [sqlite] ChangePassword method problem

So, I was just wondering how you would deal with multiple processes accessing the database. You can't guarantee that all would be closing the connection properly. It would seem that if all connections needed to be closed before a proper changepassword call can take place, then the chances of

Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23

On Sun, 23 Oct 2011 10:26:14 -0700, Pete wrote: >Apologies, I omitted what is the real cause of the problem. This simplified >SELECT illustrates the error: > >SELECT sum( colc * cold ) as total from tst where total > 1000 > >The error message is "misuse of aggregate:

[sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

Yuriy Kaminskiy wrote: > When WHERE condition is constant, there are no need to evaluate and check it > for > each row. It works, but only partially: ... > [In fact, you can move out out loop not only *whole* constant WHERE, but also > all constant AND terms of WHERE, like this: > SELECT * FROM t

Re: [sqlite] database like ms northwind

As i understand it, Northwind is simply an example of Ms access (populated db). So, if you need an example of access, buy access and you get Northwind; If, on the other hand, you dont buy it, you cant use access at all (unless by broking the law); If you are looking for something like Northwind

[sqlite] database like ms northwind

i want to make a databse like Northwind of ms Access.from where i can get guidance or download something similar? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23

Apologies, I omitted what is the real cause of the problem. This simplified SELECT illustrates the error: SELECT sum( colc * cold ) as total from tst where total > 1000 The error message is "misuse of aggregate: sum()". No error if I remove the where clause. Pete > > Message: 2 > Date:

[sqlite] [patch] constant WHERE elimination (partially) ineffective

When WHERE condition is constant, there are no need to evaluate and check it for each row. It works, but only partially: sqlite> explain SELECT * FROM t; 0|Trace|0|0|0||00| 1|Goto|0|17|0||00| 2|OpenRead|0|60|0|9|00| 3|Rewind|0|15|0||00| 4|Column|0|0|1||00| 5|Column|0|1|2||00| 6|Rowid|0|3|0||00|

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

On 23 Oct 2011, at 4:13pm, Bo Peng wrote: > Other than using a SSD to speed up random access, I hope a VACUUM > operation would copy tables one by one so content of the tables would > not scatter around the whole database. If this is the case, disk > caching should work much better after

Re: [sqlite] Database file structure

2011/10/23 Simon Slavin > > In that case, try defragging your file sometime. May make a big > difference. > > If you mean Windows defrag, it would be pointless, since it doesn't change the database structure? If you mean VACUUM, it will generate the exact same structure as

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

On Sun, Oct 23, 2011 at 8:57 AM, Simon Slavin wrote: > It seems that this was the first problem he found with the way he arranged > this database.  But our solution to it would be different depending on > whether he wanted to do this just the once, or it was a regular

Re: [sqlite] UNIQUE constraint

2011/10/23 Simon Slavin > > In this example, the indexed column is a text column. The text fields > could have been very long, and checking long text fields for uniqueness can > involve comparing every byte. Nevertheless, I do not understand the results > you quoted. I

Re: [sqlite] Database file structure

On 23 Oct 2011, at 4:03pm, Fabian wrote: > It's Windows/NTFS, but I get the point. In that case, try defragging your file sometime. May make a big difference. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] UNIQUE constraint

On 23 Oct 2011, at 3:49pm, Fabian wrote: > So the only overhead for UNIQUE is that extra check? [snip] Right. When doing an INSERT or UPDATE, it checks to see whether the value it's trying to add to the index already exists in the index. If it does, the result is an error. There is no

Re: [sqlite] Database file structure

2011/10/23 Simon Slavin > > My immediate question is why this is two rows in two separate tables rather > than one row in one table. After all, if tables always have the same rows > in, they might as well be the same row in one table. I would love to have those rows into

Re: [sqlite] Database file structure

On 23 Oct 2011, at 3:41pm, Fabian wrote: > I have two tables, both containing 1 million rows, which frequently need to > be joined by rowid. Right now, the insert loop is like this: > > For I = 1 to 1000 > INSERT INTO TABLE1 ... > INSERT INTO TABLE2 ... > Next [snip] My immediate

Re: [sqlite] UNIQUE constraint

> > > No, a UNIQUE index and a regular index are implemented the exact same way. > It's just that, at INSERT and UPDATE time, after finding a proper place to > insert the new value, an additional check is made that the place isn't > already occupied. > So the only overhead for UNIQUE is that

[sqlite] Database file structure

I have two tables, both containing 1 million rows, which frequently need to be joined by rowid. Right now, the insert loop is like this: For I = 1 to 1000 INSERT INTO TABLE1 ... INSERT INTO TABLE2 ... Next When I look at the structure of the created database-file, the rows for the

Re: [sqlite] UNIQUE constraint

Fabian wrote: > I have a column with a normal INDEX, and I would like to turn it into an > UNIQUE index, but I'm a bit worried about the performance implications for > inserts. Can someone give some insight into how UNIQUE is implemented in > SQLite, does it create extra

[sqlite] UNIQUE constraint

I have a column with a normal INDEX, and I would like to turn it into an UNIQUE index, but I'm a bit worried about the performance implications for inserts. Can someone give some insight into how UNIQUE is implemented in SQLite, does it create extra tables compared to a normale index, are there

Re: [sqlite] :Re: Concurrent readonly access to a large database.

So you are definitely thrashing disk then. An SSD might help as head seek time is constant for those. But if your gronking 288G in 5m22s that is 894MB/sec (relative to database size). With the default 2M cache_size your flushing cache 450 times per second. What happens if you bump up

Re: [sqlite] How about a proper forum rather than an e-mail list

On 23 Oct 2011, at 3:00pm, John Drescher wrote: >> If the mailing list was replaced by a forum, everybody would go to the forum. > > The failure in this logic is that is not true. I already said I would > not bother with the forum and I was not the only one. I would bother with a web forum

Re: [sqlite] Problem with binding parameters to LIKE

Navaneeth.K.N wrote: > I am trying to use parameters in a LIKE query. I have the following > code which uses Sqlite C/C++ API. > > const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%' > ORDER BY freq DESC LIMIT 10;"; > > int rc = sqlite3_prepare_v2 (db,

Re: [sqlite] How about a proper forum rather than an e-mail list

> If the mailing list was replaced by a forum, everybody would go to the forum. > The failure in this logic is that is not true. I already said I would not bother with the forum and I was not the only one. John ___ sqlite-users mailing list

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

On 23 Oct 2011, at 2:47pm, Bo Peng wrote: > On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS) > wrote: >> #1 What's the size of your database? > > 288G, 5000 table, each with ~1.4 million records Worth adding here Bo's original post: On 22 Oct 2011, at 8:52pm, Bo

Re: [sqlite] Tables as ASCII - is it possible?

On Sun, Oct 23, 2011 at 05:06:46AM +0100, Paul Linehan scratched on the wall: > Hi all, > > Is there a way of storing SQLite data (tables) as ASCII text rather > than as binary data? > > I want to be able to run scripts against my data as well as use SQLite. SQLite has drivers for most

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS) wrote: > #1 What's the size of your database? 288G, 5000 table, each with ~1.4 million records > #2 What's your cache_size setting? default > #3 How are you loading the data?  Are your table inserts interleaved or by

Re: [sqlite] How about a proper forum rather than an e-mail list

On 18.10.2011 16:40 CE(S)T, Simon Slavin wrote: > The way to settle this is easy: leave the mailing list in place. > Create a web forum. If people abandon the mailing list and start > using the web forum instead, it worked. If people stay with the > mailing list, the mailing list is superior. I

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

#1 What's the size of your database? #2 What's your cache_size setting? #3 How are you loading the data? Are your table inserts interleaved or by table? Your best bet would be by interleaving during insert so cache hits would be better. Looks to me like you're getting disk thrashing in

Re: [sqlite] Problem with binding parameters to LIKE

On Sat, Oct 22, 2011 at 11:53 PM, Navaneeth.K.N wrote: > I hooked up sqlite3_trace and > sqlite3_profile and printed the SQL being executed. Unfortunatly, > these routines won't give the SQL with values bound to it. > sqlite3_trace() does, since version 3.6.21

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

Yuriy Kaminskiy wrote: > Alternative 2: (partially tested) > Explicitly use case-insensitive comparison for table/indexes, no matter what > case_sensitive_like is. > > Index: sqlite3-3.7.8/src/shell.c > === > ---

[sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

Two alternative patches, choose whichever you like. Alternative 1: (IMO, preferred; tested) Don't lowercase argument of .schema. With PRAGMA case_sensitive_like = ON, you just need to use right case for table names. The author or authors of this code dedicate any and all copyright interest in

Re: [sqlite] Tables as ASCII - is it possible?

On Oct 23, 2011, at 6:06 AM, Paul Linehan wrote: > Is there a way of storing SQLite data (tables) as ASCII text rather > than as binary data? Perhaps you might be better off with something along the lines of KirbyBase or such. http://www.netpromi.com/kirbybase_python.html

Re: [sqlite] Problem with binding parameters to LIKE

I have done something similar and it worked for me, but there is an issue with indexes you should take into account, as discussed here: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html . Out of curiosity (since this query and it's field names seem very similar to

Re: [sqlite] ChangePassword method problem

Farhan Husain wrote: > > Aah, ok. So, for all the methods that act on the database I should explicitly add > conn.Close() within the using conn scope? > Well, I'm not familiar with your specific project; however, that does not sound like a bad idea. -- Joe Mistachkin

Re: [sqlite] Tables as ASCII - is it possible?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/10/11 21:06, Paul Linehan wrote: > Is there a way of storing SQLite data (tables) as ASCII text rather > than as binary data? > > I want to be able to run scripts against my data as well as use > SQLite. Yes, and it is very easy. SQLite has