Re: [sqlite] Understanding temp tables

On 2/7/2011 8:30 PM, Sam Carleton wrote: > Assuming the connection pooling works correctly, it is my understanding that > there is absolutely no way for another request to mess with that temp table, > correct? In other words: a temp table lives for the life of a connection ... or until

[sqlite] Understanding temp tables

I am using SQLite in a Apache module on Windows. On Windows, Apache is a single multi-threaded process. The Apache DBD is used to leverage connection pooling. At one point in the code, the web request gets a connection, creates a temp table, used the temp table, and then deletes the temp table

Re: [sqlite] EXT : Speed up count(distinct col)

You have: CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id)); CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag)); You can solve your problem, using pure SQL. No need to resort to the application layer. Just execute the SQL transaction below. It takes care of

Re: [sqlite] EXT : Speed up count(distinct col)

On 7 Feb 2011, at 11:19pm, Yuzem wrote: > One more thing: > I have the tables "movies" and "people" > Those two tables are related by tables "directors", "writers", etc... > movies: id, title > people: id, name > > directors: movieId, peopleId > writers: movieId, peopleId > etc... > > How can

Re: [sqlite] EXT : Speed up count(distinct col)

Ok, thanks all for your answers, I guest I will have to normalize the database and explore the foreign key feature. One more thing: I have the tables "movies" and "people" Those two tables are related by tables "directors", "writers", etc... movies: id, title people: id, name directors:

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby wrote: >>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision"; > > What about: > > UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB); Y’know the urban legend about the folks

Re: [sqlite] Regarding "Manual Control Of Query Plans"

On 7 Feb 2011, at 9:37pm, Sven L wrote: > Thanks for input. So you're saying that creating the tables manually is not > the same as issuing the "ANALYZE" command? You have no idea what the ANALYZE command does because it's not documented. For all you know different versions of SQLite put

Re: [sqlite] Regarding "Manual Control Of Query Plans"

On Mon, Feb 7, 2011 at 4:37 PM, Sven L wrote: > > Thanks for input. So you're saying that creating the tables manually is not > the same as issuing the "ANALYZE" command? > You are not allows to "CREATE TABLE sqlite_stat1" or "DROP TABLE sqlite_stat1". (You cannot CREATE

Re: [sqlite] EXT : Speed up count(distinct col)

> Mabye one of our experts can explain why distinct takes to long. > Seems to me if you have an index you're just returning the values in the > index, aren't you? If index is unique then you just count number of values in the index and that's it. If index is not unique then it contains many

Re: [sqlite] Regarding "Manual Control Of Query Plans"

Thanks for input. So you're saying that creating the tables manually is not the same as issuing the "ANALYZE" command? However, ".dump sqlite_stat2" does NOT work. And I have been using the SQLITE_ENABLE_STAT2 switch for decades now :P This is what this command produces: PRAGMA

Re: [sqlite] EXT : Speed up count(distinct col)

Mabye one of our experts can explain why distinct takes to long. Seems to me if you have an index you're just returning the values in the index, aren't you? But it looks like it's comparing all keys to all keys. Do you just want a count of distinct values? select count(distinct(tag)) from

Re: [sqlite] Speed up count(distinct col)

On Feb 7, 2011, at 9:32 PM, Yuzem wrote: >> Hey... sounds like IMdb :P >> > Yes, I'm coding a http://yuzem.blogspot.com/p/figuritas-screenshots.html > movie manager that grabs the info from imdb. A fine hobby :) > I thought sqlite didn't handle foreign keys correctly, I thought wrong :)

Re: [sqlite] Speed up count(distinct col)

I would create a tagList table (integer tagID, string tagName, unique(tagName)) Before performing your batch of inserts, query the tagList table (integer tagID, string tagName), and generate a map (key on tagName, value of tagID). For each tag you want to insert, see if it exists in the map. If

Re: [sqlite] Speed up count(distinct col)

Yuzem wrote: > I thought sqlite didn't handle foreign keys correctly, I am using > triggers to automatically delete tags when a movie gets deleted. There's a new thing, 'PRAGMA foreign_keys=1', that causes SQLite to enforce them. Check out the docs on that. > Anyway, to use integers in

Re: [sqlite] Speed up count(distinct col)

Petite Abeille-2 wrote: > > Hey... sounds like IMdb :P > Yes, I'm coding a http://yuzem.blogspot.com/p/figuritas-screenshots.html movie manager that grabs the info from imdb. Petite Abeille-2 wrote: > > In any case, as mentioned by Michael Black, you might benefit greatly by > normalizing

Re: [sqlite] EXT : Speed up count(distinct col)

Yuzem wrote: > > > > Test#1 > > create index tags_index on tags(tag); > > You should have an index for any fields you query on like this. > > > > Thanks Michael but I don't see any speed improvement: > create index test on tags(tag); > select count(distinct tag) from tags; > > This is much

Re: [sqlite] EXT : Speed up count(distinct col)

Black, Michael (IS) wrote: > > Test#1 > create index tags_index on tags(tag); > You should have an index for any fields you query on like this. > Thanks Michael but I don't see any speed improvement: create index test on tags(tag); select count(distinct tag) from tags; This is much faster:

Re: [sqlite] Speed up count(distinct col)

On Feb 7, 2011, at 4:26 PM, Yuzem wrote: > > I have many movies by tag and many tables like "tags" (keywords, countries, > languages, genres, etc..) Hey... sounds like IMdb :P > This solution implies one additional table and two triggers by each table. To paraphrase: Some people, when

Re: [sqlite] Regarding "Manual Control Of Query Plans"

On Mon, Feb 7, 2011 at 1:48 PM, Sven L wrote: > > Yes, this is what I have read on the net too. But, tests show that without > the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted > table scans etc). > Real-life data... can't I simply take my

Re: [sqlite] Regarding "Manual Control Of Query Plans"

On 7 Feb 2011, at 6:48pm, Sven L wrote: > Yes, this is what I have read on the net too. But, tests show that without > the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted > table scans etc). > Real-life data... can't I simply take my real-life database and extract the

Re: [sqlite] Regarding "Manual Control Of Query Plans"

Yes, this is what I have read on the net too. But, tests show that without the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted table scans etc). Real-life data... can't I simply take my real-life database and extract the data in sqlite_stat*? Btw, this command

Re: [sqlite] Regarding "Manual Control Of Query Plans"

On 7 Feb 2011, at 4:38pm, Sven L wrote: > So, with this in mind, it makes sense to precompute the sqlite_stat* tables. > Right? Which you do by running ANALYZE, but since it needs real-life data to work on there's no point doing it until your customer has put some data in. I don't write

Re: [sqlite] Regarding "Manual Control Of Query Plans"

Sounds reasonable! So, with this in mind, it makes sense to precompute the sqlite_stat* tables. Right? > From: slav...@bigfraud.org > Date: Mon, 7 Feb 2011 15:33:50 + > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans" > > > On 7 Feb 2011, at

Re: [sqlite] Regarding "Manual Control Of Query Plans"

On 7 Feb 2011, at 3:12pm, Sven L wrote: > I've discovered that the ANALYZE command is extremely slow on my large > databases. I read the documentation and it says that the command should be > issued when the schema is changed "a lot". Hmm, when exactly could that be? > Currently, I issue the

Re: [sqlite] EXT : Speed up count(distinct col)

Test#1 create index tags_index on tags(tag); You should have an index for any fields you query on like this. Test#2 Normalize your tags into a separate table so you then store the rowid in your tags table instead of the string. Your compares will be notably faster using integers rather than

Re: [sqlite] Regarding "Manual Control Of Query Plans"

Sven L wrote: > I've discovered that the ANALYZE command is extremely slow on my > large databases. I read the documentation and it says that the > command should be issued when the schema is changed "a lot". Hmm, > when exactly could that be? Currently, I issue the command every time > I have

[sqlite] Speed up count(distinct col)

Hi. I have this tables: CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id)); CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag)); I have many movies by tag and many tables like "tags" (keywords, countries, languages, genres, etc..) I can count different movies

[sqlite] Regarding "Manual Control Of Query Plans"

I've discovered that the ANALYZE command is extremely slow on my large databases. I read the documentation and it says that the command should be issued when the schema is changed "a lot". Hmm, when exactly could that be? Currently, I issue the command every time I have inserted ~1000 rows in

Re: [sqlite] Trigger to filter out characters

On 7 Feb 2011, at 1:06pm, venkat easwar wrote: > As far as I am aware trim will remove the characters only in ends, not in the > middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I > wrong > somewhere. Guess NO. Venkat, you are quite right. Sorry about that. Simon.

Re: [sqlite] EXT :Re: Sqlite3.h file for X64 Windows

Unless you have DLL as a requirement I agree with Teg... Just download the amalgamation and include sqlite3.c and sqlite3.h in your program -- that's what I do. I try to avoid all non-windows DLLs...they cause too much headache. Michael D. Black Senior Scientist NG Information Systems

Re: [sqlite] Sqlite3.h file for X64 Windows

I use the same header for both. It's so easy to build, I'm unclear why you went down this path as opposed to just building the lib from source. Monday, February 7, 2011, 7:57:53 AM, you wrote: IT> Dileep wrote: >> Hi, >> I could not found Precompiled Binaries

Re: [sqlite] Blob in the callback of sqlite3_exec

Ray wrote: > What's the actual type of blob in the callback of sqlite3_exec? Is it > string or remains binary? Bytes are reported as-is, with an added zero terminator. But since there's no length indicator, you'll have trouble with embedded NULs. Drop sqlite3_exec, use

Re: [sqlite] Trigger to filter out characters

Harish, Consider doing things outside sqlite. Definitely you should be using C API or php API or some other for making query to DB. Why don't you think of doing things outside sqlite VENKAT Bug the Bugs From: Harish CS To:

Re: [sqlite] Trigger to filter out characters

Simon, As far as I am aware trim will remove the characters only in ends, not in the middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I wrong somewhere. Guess NO. Thanks VENKAT Bug the Bugs. From: Simon Slavin

Re: [sqlite] Trigger to filter out characters

Simon Slavin wrote: > On 7 Feb 2011, at 8:38am, Harish CS wrote: > >> Table T has two varchar columns A and B. UI allows entering values into >> column A only. >> On insert/update of records, I need to take out the value of A, filter out >> anything other than '0' to '9',

Re: [sqlite] Sqlite3.h file for X64 Windows

Dileep wrote: > Hi, > I could not found Precompiled Binaries For Windows For X64 windows in > http://www.sqlite.org/download.html > > I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated > lib and Def files > > But I am not able to get

Re: [sqlite] Trigger to filter out characters

On 7 Feb 2011, at 12:38pm, Harish CS wrote: > Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - ' > '9' and a few more characters. > 180+ characters need to be removed (filtered out). > So I will need to call Trim() so many times. > Since we cannot write loops I wonder

Re: [sqlite] Trigger to filter out characters

Harish, neither trigger nor trim will be useful for you directly as such. I suggest writing a custom sqlite function to perform the operation. You try writing custom functions, if I figure out any idea, I will get back to you. http://www.sqlite.org/c3ref/create_function.html VENKAT Bug the

Re: [sqlite] Trigger to filter out characters

>Could you please show me how to write the condition (to filter out >characters)? Use the strfilter function found in extension-functions.c downloadable from http://www.sqlite.org/contrib/ Once built and loaded, you can use a trigger similar to create trigger if not exists trFilterAB after

[sqlite] Sqlite3.h file for X64 Windows

Hi, I could not found Precompiled Binaries For Windows For X64 windows in http://www.sqlite.org/download.html I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated lib and Def files But I am not able to get sqlite3.h header file for X64 windows version of sqlite3.

[sqlite] Blob in the callback of sqlite3_exec

Hi guys, What's the actual type of blob in the callback of sqlite3_exec? Is it string or remains binary? Since when I query the database to get all rows and columns the blob values are not same as what I get from other SQLite tools. ___ sqlite-users

Re: [sqlite] Sub-Select reports only row one value?

Igor Tandetnik was right on this. Thank you. > Scalar Subqueries > > A SELECT statement enclosed in parentheses may appear as a scalar > quantity. A SELECT used as a scalar quantity must return a result set > with a single column. The result of the expression is the value of the > only column

Re: [sqlite] Trigger to filter out characters

Simon, Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - ' '9' and a few more characters. 180+ characters need to be removed (filtered out). So I will need to call Trim() so many times. Since we cannot write loops I wonder how I can call it many times. -Harish Simon

Re: [sqlite] Trigger to filter out characters

Venkat, Thanks. But this is not filtering characters. For example, if user enters '12ab34', it should remove 'ab' and enter '1234'. Thanks, Harish venkat easwar wrote: > > Well. I thought it should be an easy deal for you hence left that part. > Now > giving the conditioned trigger

Re: [sqlite] Trigger to filter out characters

On 7 Feb 2011, at 8:38am, Harish CS wrote: > Table T has two varchar columns A and B. UI allows entering values into > column A only. > On insert/update of records, I need to take out the value of A, filter out > anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy > it to

Re: [sqlite] Trigger to filter out characters

Well. This is the one I did not expect. Update without condition is updating all the rows. Hence make it with conditioned update :) :) create trigger if not exists after insert on when new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w') begin update set B=new.A where

Re: [sqlite] Trigger to filter out characters

OOPS.. a mistake.. find the corrected things below. create trigger if not exists after insert on when new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w') beginupdate set B=new.A; end; Missed set in my last mail. It would have given you a syntax error. VENKAT Bug the Bugs

Re: [sqlite] Trigger to filter out characters

Well. I thought it should be an easy deal for you hence left that part. Now giving the conditioned trigger assuming the following condition. I leave the testing part to your concern. >I need to take out the value of A, filter out > anything other than '0' to '9', '*', '#', '+' 'p', 'w' create

Re: [sqlite] Trigger to filter out characters

Hi Venkat, Could you please show me how to write the condition (to filter out characters)? Thanks, Harish venkat easwar wrote: > > Hi Harish, > > Yes it is possible. Look below for solution. > > create trigger if not exists after insert on when > new.A= > begin > update B=new.A; >

Re: [sqlite] Trigger to filter out characters

Hi Harish, Yes it is possible. Look below for solution. create trigger if not exists after insert on when new.A= begin update B=new.A; end; There is nothing tricky or hard for inserting into another table, same things should go. VENKAT Bug the Bugs

[sqlite] Trigger to filter out characters

Table T has two varchar columns A and B. UI allows entering values into column A only. On insert/update of records, I need to take out the value of A, filter out anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy it to column B. Is it possible to write a trigger to achieve

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision"; What about: UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB); As a one-time command to correct the table. There is also the option of using triggers to ensure future "Data" values are kept as BLOB if the INSERT code