Re: [sqlite] Aggregate and query limit
Hi, Thanks for reply. I'm still confused about that. I don't know much about SQLite internals, But what i understanded from your reply that SQLite fetch any row match the query condation and then apply to it the GROUP BY then apply to it the ORDER BY is there is any, And when the LIMIT appears SQLite keep fetching the result from the "start" until the result set size equals the requested limit. Do you mean the LIMIT in SQLite is not optimized? and the performance of selecting from table contains 1000 rows match the query condation equal selecting the same condation with limit 990,10? Thanks, Mina. On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- Mina R Waheeb <[EMAIL PROTECTED]> wrote: > > I have some questions about the behavior of aggregate functions and > > the result LIMIT. > > After register TEST function and executing query (SELECT TEST() FROM > > objects group by id LIMIT 30,3) > > > > I was expect calling back TEST() only 3 times but what happened is the > > TEST() is called 33 time, and the result set is correct 3 rows. > > Test() would (and should) be called for each row in the > table "objects". How else could it aggregate the information? > > > My questions: > > - Why TEST() is called for non-result rows? Is this designed feature? > > Regarding non-results rows - you only know that after the fact that it's > a non result row - after all rows are processed. Do the query yourself > manually on paper and see. > > > - When the evaluation of the result-column functions happen? > > - How SQLite preform the LIMIT? > > Logically, it operates on the result set after the GROUP BY. > In code, it is more involved than that. > > Also keep in mind that you're assuming that GROUP BY does an ordering > on "id". In SQLite it happens to be the case because of its use > of btrees, but this is not guaranteed on other databases where they > may use a hash map for group by. You need an explicit "ORDER BY id" > to guarantee an order. > > > > > Got a little couch potato? > Check out fun summer activities for kids. > http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite]Inmemory database
Hi, I am working in 3.3.6 Now I am working with inmemory concept. Actually I am copying all the contents of the database into temporary table. Then I am inserting one record in that temporary table. But I need that record to be returned finally in to the original database file. Is there any other way to do this. I did the following. "ATTACH DATABASE 'test.db' AS Newdb" "CREATE TABLE INMEMORYTABLE AS SELECT * FROM Newdb.temp" "DETACH DATABASE Newdb" insert into INMEMORYTABLE(d,name) values(7,'name7');" select * from INMEMORYTABLE ;" Can any one help.I want that inserted record in the temporary table to be in the original database[ test.db] Best Regards, A.Sreedhar.
[sqlite] To increase search speed
Hi I am working in 3.3.6. I have created a table with 4 records with 12 fields as follows. "create table MUSIC (Id integer primary key, AlbumName text not null collate nocase,Track text not null collate nocase,ArtistName text not null collate nocase,URL text not null collate nocase , Duration integer, TrackFormat text not null collate nocase, BitRate integer, sampleRate integer, Channels integer, Filesize integer GenreName text not null collate nocase);" I will often search for the following fields only. Select distinct AlbumName from MUSIC; Select distinct ArtistName from MUSIC; Select distinct GenreName from MUSIC; Select distinct AlbumName for particular ArtistName Select Track for particular AlbumName Select distinct ArtistName for particular GenreName To obtain nice search speed which method will work fine. I have tried wilth the following methods. Method 1: It's the one described above Method 2: By doing indexing.I tried with the following. "create index Musicidx1 on MUSIC(ArtistName collate nocase,AlbumName collate nocase);" "create index Musicidx2 on MUSIC(AlbumName collate nocase,ArtistName collate nocase,URL collate nocase);" This gives better performance than method 1 for the following searches; Select distinct AlbumName for particular ArtistName Select Track for particular AlbumName Select distinct ArtistName for particular GenreName Method 3: Joining Multiple tables. I have created 4 tables as follows; "create table ALBUMS (id integer primary key,Album text,unique(Album));" "create table ARTISTS (id integer primary key,Artist text,unique(Artist));" "create table GENRES (id integer primary key,Genre text,unique(Genre));" "create table MUSIC (Id integer primary key, AlbumName text not null collate nocase,Track text not null collate nocase,ArtistName text not null collate nocase,URL text not null collate nocase , Duration integer, TrackFormat text not null collate nocase, BitRate integer, sampleRate integer, Channels integer, Filesize integer,album_id integer,artist_id integer, GenreName text not null collate nocase ,Genre_Id integer);" Here album_id , artist_id and Genre_Id are the id values of ALBUMS,ARTISTS,GENRES Tables. This shows better performance than indexing for the following searches. Select distinct AlbumName from MUSIC; Select distinct ArtistName from MUSIC; Select distinct GenreName from MUSIC; Method 4: Inmemory method.I will copy all the content from the temporary database to inmemory and then performing search. If I am using this method means then while inserting records , that record will be inserted into the temporary memory only. But I want to be inserted in to the original database also. Is there any other way to do this. Can any one help to increase my search speed. Thanks in Advance. Regards, Sreedhar
Re: [sqlite] Re: SELECT INTO ... not supported?
On 18/08/2007 12:17 PM, Igor Tandetnik wrote: John Machin <[EMAIL PROTECTED]> wrote: Something as simple as SELECT * INTO tblcopy FROM tbl; (where tbl is an existing table) gets the following error: SQL error: near "INTO": syntax error [version: 3.4.2 on Windows XP] I note that this syntax is not mentioned on the supported SQL syntax web page for SELECT This should have given you a hint. A web page for CREATE TABLE however documents this: create table tblcopy as select * from tbl; Thanks, Igor, that does the trick. Cheers, John - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd error on "BEGIN IMMEDIATE"
On 8/17/07, Scott Derrick <[EMAIL PROTECTED]> wrote: > exec a "BEGIN IMMEDIATE", with a sleep loop if I can't acquire the > reserved lock. > > Then prepare, step, finalize, exit the function > > When I come back into the function and exec a "BEGIN IMMEDIATE" I get an > error > > "Cannot start a transaction within a transaction". > > Whats wrong? Doesn't sqlite3_finalize(stmt), release the locks, > deletes the prepared statement and causes the database to be updated? It deletes the prepared statement. The statement's action would have been performed by a previous sqlite3_step(). Locks and database updates are another matter, because... > Why does the engine think I'm still in a transaction? ...you started a transaction with BEGIN. Unless the statement you stepped is a COMMIT, your transaction isn't done yet :) Exec a COMMIT at the end of the loop. Incidentally, you can keep the prepared statement around if it's appropriate. You can open the database, prepare the statement, and only step() and reset() within the loop. This way you don't have to keep preparing it over and over again. You must finalize() it before closing the database, though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd error on "BEGIN IMMEDIATE"
On 8/18/07, Scott Derrick <[EMAIL PROTECTED]> wrote: > I have a process that repeatedly, once a second, inserts data into the > database. > > I open the database, on the class instantiation. > > once a second I call a function that; > > exec a "BEGIN IMMEDIATE", with a sleep loop if I can't acquire the > reserved lock. > > Then prepare, step, finalize, exit the function > > When I come back into the function and exec a "BEGIN IMMEDIATE" I get an > error > > "Cannot start a transaction within a transaction". > > Whats wrong? Doesn't sqlite3_finalize(stmt), release the locks, > deletes the prepared statement and causes the database to be updated? No, your assumptions are wrong, that's not it's job. If it did then you couldn't have more than a single statement inside a transaction, don't you think? > Why does the engine think I'm still in a transaction? You didn't end the transaction with either "COMMIT"/"END" or "ROLLBACK". Regards, ~Nuno Lucas > thanks, > > Scott - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Odd error on "BEGIN IMMEDIATE"
I have a process that repeatedly, once a second, inserts data into the database. I open the database, on the class instantiation. once a second I call a function that; exec a "BEGIN IMMEDIATE", with a sleep loop if I can't acquire the reserved lock. Then prepare, step, finalize, exit the function When I come back into the function and exec a "BEGIN IMMEDIATE" I get an error "Cannot start a transaction within a transaction". Whats wrong? Doesn't sqlite3_finalize(stmt), release the locks, deletes the prepared statement and causes the database to be updated? Why does the engine think I'm still in a transaction? thanks, Scott -- - The only security of all is in a free press. The force of public opinion cannot be resisted when permitted freely to be expressed. The agitation it produces must be submitted to. It is necessary, to keep the waters pure. Thomas Jefferson to Lafayette, 1823. ME 15:491 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT INTO ... not supported?
On 8/18/07, John Machin <[EMAIL PROTECTED]> wrote: > Something as simple as > SELECT * INTO tblcopy FROM tbl; > (where tbl is an existing table) gets the following error: > SQL error: near "INTO": syntax error > [version: 3.4.2 on Windows XP] > > I note that this syntax is not mentioned on the supported SQL syntax web > page for SELECT, but it's also not mentioned AFAICT in the unsupported > features list on the wiki. > > Am I missing something simple, or is "SELECT INTO" definitely not supported? > > Can anybody help with a workaround that doesn't need an explict (and > rather long) list of fields in the 3-table join that is my real > non-simple requirement? http://www.sqlite.org/lang_insert.html Regards, ~Nuno Lucas > Thanks in advance, > > John - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SELECT INTO ... not supported?
John Machin <[EMAIL PROTECTED]> wrote: Something as simple as SELECT * INTO tblcopy FROM tbl; (where tbl is an existing table) gets the following error: SQL error: near "INTO": syntax error [version: 3.4.2 on Windows XP] I note that this syntax is not mentioned on the supported SQL syntax web page for SELECT This should have given you a hint. A web page for CREATE TABLE however documents this: create table tblcopy as select * from tbl; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SELECT INTO ... not supported?
Hi all, Something as simple as SELECT * INTO tblcopy FROM tbl; (where tbl is an existing table) gets the following error: SQL error: near "INTO": syntax error [version: 3.4.2 on Windows XP] I note that this syntax is not mentioned on the supported SQL syntax web page for SELECT, but it's also not mentioned AFAICT in the unsupported features list on the wiki. Am I missing something simple, or is "SELECT INTO" definitely not supported? Can anybody help with a workaround that doesn't need an explict (and rather long) list of fields in the 3-table join that is my real non-simple requirement? Thanks in advance, John - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Columns from nested joins aren't properly propagated
Given that this issue has existed for years, and many simple workarounds are known, I doubt it will change any time soon. If you want to use SQLite, you have to rewrite the queries. The benefit of changing the generated SQL is that it will work on all the databases you mentioned. The sqlite source code is available. You can modify it as see you fit. --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > As I told you, I know how to avoid this. But I can't rewrite these > queries. That's my problem... > > Thanks for your attention. > > Regards, > Bruno > > On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > SELECT t_dados.id > > FROM Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id > > ORDER BY t_dados.id, Points1.geom_id; > > > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > > I'm having problems with the following query (and, in general, in > > > queries using left joins): > > > > > > [EMAIL PROTECTED]:~$ sqlite3 parana > > > SQLite version 3.4.1 > > > Enter ".help" for instructions > > > sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON > > > Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY > > > t_dados.id, Points1.geom_id; > > > SQL error: no such column: t_dados.id Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Columns from nested joins aren't properly propagated
Regarding: "The issue is: the above and similar queries are generated automatically, the same code (query) is used in MySQL, Postgrees and Oracle AND, as it is not my code that generates these expressions, I cannot modify the generating code of these queries." I don't mean to be pessimistic, but do you think your current problem with LEFT OUTER JOIN might be the tip of the iceberg? There are so many vendor-specific SQL syntax variations that I hope this isn't merely the first problem of many. [Pessimistic opinions are my own, not those of my company nor its board of directors.] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Columns from nested joins aren't properly propagated
Hi, As I told you, I know how to avoid this. But I can't rewrite these queries. That's my problem... Thanks for your attention. Regards, Bruno On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > SELECT t_dados.id > FROM Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id > ORDER BY t_dados.id, Points1.geom_id; > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > I'm having problems with the following query (and, in general, in > > queries using left joins): > > > > [EMAIL PROTECTED]:~$ sqlite3 parana > > SQLite version 3.4.1 > > Enter ".help" for instructions > > sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON > > Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY > > t_dados.id, Points1.geom_id; > > SQL error: no such column: t_dados.id > > > > > > Looking for a deal? Find great prices on flights and hotels with Yahoo! > FareChase. > http://farechase.yahoo.com/ > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] Aggregate and query limit
--- Mina R Waheeb <[EMAIL PROTECTED]> wrote: > I have some questions about the behavior of aggregate functions and > the result LIMIT. > After register TEST function and executing query (SELECT TEST() FROM > objects group by id LIMIT 30,3) > > I was expect calling back TEST() only 3 times but what happened is the > TEST() is called 33 time, and the result set is correct 3 rows. Test() would (and should) be called for each row in the table "objects". How else could it aggregate the information? > My questions: > - Why TEST() is called for non-result rows? Is this designed feature? Regarding non-results rows - you only know that after the fact that it's a non result row - after all rows are processed. Do the query yourself manually on paper and see. > - When the evaluation of the result-column functions happen? > - How SQLite preform the LIMIT? Logically, it operates on the result set after the GROUP BY. In code, it is more involved than that. Also keep in mind that you're assuming that GROUP BY does an ordering on "id". In SQLite it happens to be the case because of its use of btrees, but this is not guaranteed on other databases where they may use a hash map for group by. You need an explicit "ORDER BY id" to guarantee an order. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] like operator
The % is an escape character in the WWW form protocol. You need to interpret it and regard the next two bytes as a hex char. RaghavendraK 70574 wrote: Hi, we have given a web interface which receive delete request. Now in the req we get "%" and in the delete impl we do this delete from table where itemName like xxx.%; since the key is % the above statement becomes, "delete from table where itemName like %.%";And result in fatal problem of erasing all records. Is there any api to deal with like operator for these conditions, pls help. Hopefully fix will not degrade performance. regrds ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Columns from nested joins aren't properly propagated
SELECT t_dados.id FROM Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id ORDER BY t_dados.id, Points1.geom_id; --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > I'm having problems with the following query (and, in general, in > queries using left joins): > > [EMAIL PROTECTED]:~$ sqlite3 parana > SQLite version 3.4.1 > Enter ".help" for instructions > sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON > Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY > t_dados.id, Points1.geom_id; > SQL error: no such column: t_dados.id Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] like operator
I'm not sure I correctly understand your question, but: escaping the % in your query may be what you're looking for, i.e. delete from table where itemName like '\%.%' escape '\' Maybe. :) -Tom > -Original Message- > From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 16, 2007 9:03 PM > To: SQLite > Subject: [sqlite] like operator > > Hi, > > we have given a web interface which receive delete request. > Now in the req we get "%" and in the delete impl we do this > delete from table where itemName like xxx.%; > > since the key is % the above statement becomes, > "delete from table where itemName like %.%";And result in > fatal problem of erasing all records. > > Is there any api to deal with like operator for these > conditions, pls help. Hopefully fix will not > degrade performance. > > regrds > ragha > > > ** > > This email and its attachments contain confidential > information from HUAWEI, which is intended only for the > person or entity whose address is listed above. Any use of > the information contained herein in any way (including, but > not limited to, total or partial disclosure, reproduction, or > dissemination) by persons other than the intended > recipient(s) is prohibited. If you receive this e-mail in > error, please notify the sender by phone or email immediately > and delete it! > > ** > *** > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Columns from nested joins aren't properly propagated
Hi all, I'm having problems with the following query (and, in general, in queries using left joins): [EMAIL PROTECTED]:~$ sqlite3 parana SQLite version 3.4.1 Enter ".help" for instructions sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY t_dados.id, Points1.geom_id; SQL error: no such column: t_dados.id sqlite> .q Apparently, as I've discovered in the SQLite's "Known Issues with descriptions" page ( http://www.sqlite.org/cvstrac/rptview?rn=15 , mainly issue #1994 ) and in related posts in the mailing lists (such as http://marc.10east.com/?t=11537869901 ), this is related to the columns from nested joins which aren't propagated correctly and is a low-priority bug. In order to avoid this problem, one can simply remove the parenthesis or set proper aliases. That's fine. The issue is: the above and similar queries are generated automatically, the same code (query) is used in MySQL, Postgrees and Oracle AND, as it is not my code that generates these expressions, I cannot modify the generating code of these queries. Does anyone has an idea of how can I solve this? Will this issue be corrected in a near future SQLite update? Any hint will be pretty much appreciated. Thanks in advance. Best regards, Bruno -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
RE: [sqlite] Dump with where clause
But then I have to create an actual table in the database? I suppose I can do the following: BEGIN; create table image_temp as select * from file_folder_data; .dump image_temp Rollback; That seems to work as expected Just another question though, how can I script this and run it from command line, using sqlite3.exe ? Thanks. -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: 17 August 2007 01:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dump with where clause Updating Igor's suggestion, just remove 'temp' from the line create temp table image_temp as select * from file_folder_data; For me .dump then works as expected. Rgds, Simon On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Hi Dennis this seems like a good idea, but there is a problem: > > I use the following statement: > > .mode insert > select * from FILE_FOLDER_DATA; > > > This is a snippet of what I get > INSERT INTO table VALUES(1285,4323,2,'╪α','Thumb','JPEG'); > INSERT INTO table VALUES(1286,4324,2,'╪α','Thumb','JPEG'); > INSERT INTO table VALUES(1287,4325,2,'╪α','Thumb','JPEG'); > INSERT INTO table VALUES(1288,4326,2,'╪α','Thumb','JPEG'); > > As you can see first of all the insert statement inserts into 'table' which > is obviously not the right name, > > Secondly the image data should be a HEX string as is what .dump does. > > .schema file_folder_data > > CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, > FOLDER_ID I > NTEGER, FOLDER_TYPE INTEGER, > CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT); > CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE > ASC > ); > > > Also the .dump with temp table as Igor suggested does not work. > > This works: > .dump file_folder_data > > This does NOT > create temp table image_temp as select * from file_folder_data; > .dump image_temp > > All I get is: > > BEGIN TRANSACTION; > COMMIT; > > > > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 16 August 2007 05:49 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dump with where clause > > Andre du Plessis wrote: > > HI, how can I use .dump or something similar but specify a where clause, > > I cant see that the .dump command allows this, > > > > Without any arguments it seems to dump the whole db, the only argument > > supported is the table name, > > > > > > > > I would like to be able to do something like: > > > > .dump table1 where ID > 1000 > > > > > > > > I don't have a problem with the INSERT into statements, in fact I think > > I prefer it because the main idea is to extract parts of the db > > (revisions), > > > > And then to be able to rebuild the db in case of corruption... > > > > > > > > I know there is also the COPY command in SQL I have not really tried it > > by the documentation it seems to be able to dump the table in comma or > > tab delimited, but Preferably I don't want to write too much code to do > > this. > > > > > > > > > Andre, > > You can use the insert mode in the shell to do what you want. It will > format the select output as insert statements. > >.mode insert >select * from table1 where ID > 1000; > > This doesn't generate the transaction wrapper, or the table's create > statement, but you can add those yourself if needed. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > >
Re: [sqlite] Dump with where clause
Updating Igor's suggestion, just remove 'temp' from the line create temp table image_temp as select * from file_folder_data; For me .dump then works as expected. Rgds, Simon On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Hi Dennis this seems like a good idea, but there is a problem: > > I use the following statement: > > .mode insert > select * from FILE_FOLDER_DATA; > > > This is a snippet of what I get > INSERT INTO table VALUES(1285,4323,2,'�nα','Thumb','JPEG'); > INSERT INTO table VALUES(1286,4324,2,'�nα','Thumb','JPEG'); > INSERT INTO table VALUES(1287,4325,2,'�nα','Thumb','JPEG'); > INSERT INTO table VALUES(1288,4326,2,'�nα','Thumb','JPEG'); > > As you can see first of all the insert statement inserts into 'table' which > is obviously not the right name, > > Secondly the image data should be a HEX string as is what .dump does. > > .schema file_folder_data > > CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, > FOLDER_ID I > NTEGER, FOLDER_TYPE INTEGER, > CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT); > CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE > ASC > ); > > > Also the .dump with temp table as Igor suggested does not work. > > This works: > .dump file_folder_data > > This does NOT > create temp table image_temp as select * from file_folder_data; > .dump image_temp > > All I get is: > > BEGIN TRANSACTION; > COMMIT; > > > > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 16 August 2007 05:49 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dump with where clause > > Andre du Plessis wrote: > > HI, how can I use .dump or something similar but specify a where clause, > > I cant see that the .dump command allows this, > > > > Without any arguments it seems to dump the whole db, the only argument > > supported is the table name, > > > > > > > > I would like to be able to do something like: > > > > .dump table1 where ID > 1000 > > > > > > > > I don't have a problem with the INSERT into statements, in fact I think > > I prefer it because the main idea is to extract parts of the db > > (revisions), > > > > And then to be able to rebuild the db in case of corruption... > > > > > > > > I know there is also the COPY command in SQL I have not really tried it > > by the documentation it seems to be able to dump the table in comma or > > tab delimited, but Preferably I don't want to write too much code to do > > this. > > > > > > > > > Andre, > > You can use the insert mode in the shell to do what you want. It will > format the select output as insert statements. > >.mode insert >select * from table1 where ID > 1000; > > This doesn't generate the transaction wrapper, or the table's create > statement, but you can add those yourself if needed. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > >
RE: [sqlite] Dump with where clause
Hi Dennis this seems like a good idea, but there is a problem: I use the following statement: .mode insert select * from FILE_FOLDER_DATA; This is a snippet of what I get INSERT INTO table VALUES(1285,4323,2,' ╪ α','Thumb','JPEG'); INSERT INTO table VALUES(1286,4324,2,' ╪ α','Thumb','JPEG'); INSERT INTO table VALUES(1287,4325,2,' ╪ α','Thumb','JPEG'); INSERT INTO table VALUES(1288,4326,2,' ╪ α','Thumb','JPEG'); As you can see first of all the insert statement inserts into 'table' which is obviously not the right name, Secondly the image data should be a HEX string as is what .dump does. .schema file_folder_data CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, FOLDER_ID I NTEGER, FOLDER_TYPE INTEGER, CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT); CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE ASC ); Also the .dump with temp table as Igor suggested does not work. This works: .dump file_folder_data This does NOT create temp table image_temp as select * from file_folder_data; .dump image_temp All I get is: BEGIN TRANSACTION; COMMIT; -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 16 August 2007 05:49 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dump with where clause Andre du Plessis wrote: > HI, how can I use .dump or something similar but specify a where clause, > I cant see that the .dump command allows this, > > Without any arguments it seems to dump the whole db, the only argument > supported is the table name, > > > > I would like to be able to do something like: > > .dump table1 where ID > 1000 > > > > I don't have a problem with the INSERT into statements, in fact I think > I prefer it because the main idea is to extract parts of the db > (revisions), > > And then to be able to rebuild the db in case of corruption... > > > > I know there is also the COPY command in SQL I have not really tried it > by the documentation it seems to be able to dump the table in comma or > tab delimited, but Preferably I don't want to write too much code to do > this. > > > > Andre, You can use the insert mode in the shell to do what you want. It will format the select output as insert statements. .mode insert select * from table1 where ID > 1000; This doesn't generate the transaction wrapper, or the table's create statement, but you can add those yourself if needed. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Unique ids for each record
Thanks Marcus
I am working in 3.3.6 version.
I created a table with id as integer primary key and with constraint id<6.
For eg,
"create table Test(id INTEGER PRIMARY KEY CHECK (id > 0 and id < 6),name
integer);"
"insert into Test (name) values('name1');"
"insert into Test (name) values('name2');"
"insert into Test (name) values('name3');"
"insert into Test (name) values('name4');"
"insert into Test (name) values('name5');"
The output for :"select rowid,* from Test;"
Rowid id name
1 1 name1
2 2 name2
3 3 name3
4 4 name4
5 5 name5
Now I deleted 2 records.
"delete from Test where id=3;"
"delete from Test where id=4;"
"vacuum Test;"
The output for :"select rowid,* from Test;"
Rowid id name
1 1 name1
2 2 name2
5 5 name5
My doubt is
. if I tried to insert one more file ,I cant able to insert.error
like "constraint failed" flashes.Since I deleted 2 records that space is
freed only.i tried after doing vacuum also.Is there any other way to insert.
. The rowid is also not updated after doing vacuum.Is there any
other way to update.If I tried the same by creating a table with INTEGER
alone at that time rowed is updating properly after "Vacuum Test".
Can any one please clarify my doubts.
Best Regards,
A.Sreedhar.
-Original Message-
From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 16, 2007 7:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unique ids for each record
I assume you'd rather want three separate tables (artist, album,
track) with an autoincrementing ID field per table. Your approach would not
allow users to own more than 100 albums.
regards,
Markus
Quoting "Sreedhar.a" <[EMAIL PROTECTED]>:
> Hi,
>
> I have a table with 3 columns.
>
> Artist Album and tracks.
>
> Can i fix a range of ids for each column like 1-100 for Artist 101-200
> for Album and 201-300 for tracks
>
> So that I can have a unique number(id) for each record.
>
> Will there be a problem while deleting and inserting the records?
>
> Thanks and best regards,
> A.Sreedhar.
>
>
>
>
--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Aggregate and query limit
Hi, What I got 33 call to the xFinal (NOT EXPECTED) and 33 call to xStep and only 3 rows as a result (AS EXPECTED). Even if I have 11 elements grouped, I should got only 3 calls to xFinal and 33 to xStep. Also by debuging the code xStep and xFinal called with non-result data. Thanks, Mina. On 8/17/07, Sreedhar.a <[EMAIL PROTECTED]> wrote: > Hi, > > One idea,Please check whether each row has 11 elements. > That's could be the reason why u have got 33 times call back. > > > Best Regards, > A.Sreedhar. > > > -Original Message- > From: Mina R Waheeb [mailto:[EMAIL PROTECTED] > Sent: Friday, August 17, 2007 12:12 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Aggregate and query limit > > Hi, > I have some questions about the behavior of aggregate functions and the > result LIMIT. > After register TEST function and executing query (SELECT TEST() FROM objects > group by id LIMIT 30,3) > > I was expect calling back TEST() only 3 times but what happened is the > TEST() is called 33 time, and the result set is correct 3 rows. > > My questions: > - Why TEST() is called for non-result rows? Is this designed feature? > - When the evaluation of the result-column functions happen? > - How SQLite preform the LIMIT? > > Thanks, > Mina. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregate and query limit
Hi, One idea,Please check whether each row has 11 elements. That's could be the reason why u have got 33 times call back. Best Regards, A.Sreedhar. -Original Message- From: Mina R Waheeb [mailto:[EMAIL PROTECTED] Sent: Friday, August 17, 2007 12:12 PM To: sqlite-users@sqlite.org Subject: [sqlite] Aggregate and query limit Hi, I have some questions about the behavior of aggregate functions and the result LIMIT. After register TEST function and executing query (SELECT TEST() FROM objects group by id LIMIT 30,3) I was expect calling back TEST() only 3 times but what happened is the TEST() is called 33 time, and the result set is correct 3 rows. My questions: - Why TEST() is called for non-result rows? Is this designed feature? - When the evaluation of the result-column functions happen? - How SQLite preform the LIMIT? Thanks, Mina. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Aggregate and query limit
Hi, I have some questions about the behavior of aggregate functions and the result LIMIT. After register TEST function and executing query (SELECT TEST() FROM objects group by id LIMIT 30,3) I was expect calling back TEST() only 3 times but what happened is the TEST() is called 33 time, and the result set is correct 3 rows. My questions: - Why TEST() is called for non-result rows? Is this designed feature? - When the evaluation of the result-column functions happen? - How SQLite preform the LIMIT? Thanks, Mina. - To unsubscribe, send email to [EMAIL PROTECTED] -
