Re: [sqlite] Can an online backup happen during a nested transaction?

On May 12, 2010, at 2:53 AM, Shaun Seckman (Firaxis) wrote: > Hello, > >I'm attempting to save a backup of my in-memory > database > using the online backup routines. I noticed that I cannot seem to > make > backups of the database when there is a pending save point. The

Re: [sqlite] Insert large data question ??

> Thanks Kishor ,I will note it !! > > I already used transaction to doing this job. > I tried to remove all of index ,this time the job used about 31600 seconds > > ps. I had use "PRAGMA synchronous=OFF" in front of my transaction. > > someone can help me do this job more faster ?? have you

Re: [sqlite] Insert large data question ??

Thanks Kishor ,I will note it !! I already used transaction to doing this job. I tried to remove all of index ,this time the job used about 31600 seconds ps. I had use "PRAGMA synchronous=OFF" in front of my transaction. someone can help me do this job more faster ?? thank everybody

Re: [sqlite] DB files are different between PC sideandinstrumentside.

OK, got it. Thanks! BR Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, May 12, 2010 10:01 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] DB files are different between

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Thank you very much! It may be because my system's resource is limited. It's a embedded system containing 32M RAM, ARM9 CPU. My "reiterating 20 times" is already using usleep(). After I add the loop in the prepare statements, the system performance is still very bad... And there are still many

Re: [sqlite] DB files are different between PC side andinstrumentside.

On 12 May 2010, at 2:39am, Lei, Rick (GE EntSol, SensInsp) wrote: > Another question is if Sqlite uses at least 1 page for each table and index, > does it means if the contents in a table doesn't fill 1 page, Sqlite will not > request a new page when operating this table. Is it right? Any

Re: [sqlite] DB files are different between PC side andinstrumentside.

Hi, Pavel, Thanks for your comments. Another question is if Sqlite uses at least 1 page for each table and index, does it means if the contents in a table doesn't fill 1 page, Sqlite will not request a new page when operating this table. Is it right? BR Rick -Original Message-

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

On 11/05/2010, at 6:12 PM, Ben Harper wrote: > To determine the type of columns in a view I use > SELECT typeof(column) FROM viewname LIMIT something; > > Unfortunately if most of the column data is NULL then you can end up having > to scan the entire table. Yes, I also do that as a last

Re: [sqlite] feature request: specify database name in table-name part of CREATE INDEX

Sorry, big duh. Of course a SQLite index needs to be in the same file as the indexed table; and that is specified. Having an index for a table in one database stored in a different database would not be consistent with SQLite, as both would need to be opened together.

[sqlite] feature request: specify database name in table-name part of CREATE INDEX

http://www.sqlite.org/lang_createindex.html shows the optional [DATABASE] DOT for where the index lives, but not for where the indexed table lives. Just in case anyone is keeping track of weird edge cases that are actually being used somewhere and why, I'm modifying a DDL dump from a different

[sqlite] SQLITE_CORRUPT: database disk image is malformed

Hello Sir/Maam, I am getting the error "SQLITE_CORRUPT: database disk image is malformed" while opening the attached database in SQlite Expert. Can you please tell me the reason as well as solution to rectify this issue. Thanks, Kundan Bharti

Re: [sqlite] SQLITE_CORRUPT error

Thank you Roger, TH3 is proprietary and requires a license,Do you know how can I obtain a license to access and use TH3? Thanks, Daksh From: Roger Binns To: General Discussion of SQLite Database Sent: Tue, May

[sqlite] Can an online backup happen during a nested transaction?

Hello, I'm attempting to save a backup of my in-memory database using the online backup routines. I noticed that I cannot seem to make backups of the database when there is a pending save point. The error code is SQLITE_BUSY. Is this the expected behavior? Are there any ways

Re: [sqlite] SQLite Database in Shared Memory

I need to exchange messages across processes, not threads. And one of the reasons that I am inclined towards SQLite is that I do not want a separate Queue-manager process. I'll just write wrapper APIs around SQLite and embed them into each application, so I have a manager-less implementation. I

Re: [sqlite] SQLite Database in Shared Memory

Hm... You can use the dedicated thread in your application for SQLite in-memory database. Why you want to build external application for this? And SQL for you task is not needed I think - you can use the simple hash table or any other simple structure. If you have same additional needs or ideas -

Re: [sqlite] Side effects of commit

On Tue, May 11, 2010 at 12:12:09PM -0700, Jim Terman scratched on the wall: > We have sqlite databases in a memory shared cache environment where > individual tables may be locked out by other processes. If other processes are doing the locking, the whole database is locked. > This means

[sqlite] Side effects of commit

We have sqlite databases in a memory shared cache environment where individual tables may be locked out by other processes. This means that we have to worry about SQLITE-BUSY errors and make sure are code can handle this. There is some internal debate about whether we have to worry about table

Re: [sqlite] create virtual table if not exists table_id???

On Tue, May 11, 2010 at 1:50 PM, Matt Young wrote: > sqlite> create virtual table if not exists words using fts3  (f1 ); > Error: near "not": syntax error > sqlite> create  table if not exists U (w1 ); > sqlite> > > Different syntax? Yes. > virtual tables don't persist?

Re: [sqlite] create virtual table if not exists table_id???

Syntax says they are different...virtual tables don't have the same flexibility apparently...I suppose you're looking for "why" though? http://www.sqlite.org/lang_createvtab.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From:

Re: [sqlite] PRAGMA auto_vacuum

I'd recommend NONE (or 0). If you have fairly balanced insertions and deletions then there will be no excessive disk space consumption, vacuuming won't help much. But with auto-vacuuming turned on you won't have the same performance because additional code will be executed after each transaction.

[sqlite] PRAGMA auto_vacuum

Hi All, I have the database which has a lot of insertion and deletion. Do you have any recomendation about what value that need to be set for auto_vacuum in this case to improve the performance for deletion as well as insertion the new row to the database. (0 | NONE | 1 | FULL | 2 |

[sqlite] create virtual table if not exists table_id???

sqlite> create virtual table if not exists words using fts3 (f1 ); Error: near "not": syntax error sqlite> create table if not exists U (w1 ); sqlite> Different syntax? virtual tables don't persist? ___ sqlite-users mailing list

Re: [sqlite] sqlite connection?

On Tue, May 11, 2010 at 01:45:03PM -0400, john cummings scratched on the wall: > hi all, > > i'm new to this forum and sqlite. > > is it possible to have an executable (i.e. .exe) with connections to 2 > sqlite databases? > > i've read doc and it doesn't speak to this one way or the other.

Re: [sqlite] sqlite connection?

On 11 May 2010, at 6:45pm, john cummings wrote: > is it possible to have an executable (i.e. .exe) with connections to 2 > sqlite databases? > > i've read doc and it doesn't speak to this one way or the other. Sure. Use sqlite_open() two times, and keep the returned values in two separate

Re: [sqlite] sqlite connection?

Yes. One can also attach 'somedatabase path' as anothername ; and you can run a query accessing both at the same time. regards, Adam On Tue, May 11, 2010 at 1:45 PM, john cummings wrote: > hi all, > > i'm new to this forum and sqlite. > > is it possible to have an

Re: [sqlite] sqlite connection?

On Tue, May 11, 2010 at 12:45 PM, john cummings wrote: > hi all, > > i'm new to this forum and sqlite. > > is it possible to have an executable (i.e. .exe) with connections to 2 > sqlite databases? I've never made an executable, but given that I can do so with Perl, I

[sqlite] sqlite connection?

hi all, i'm new to this forum and sqlite. is it possible to have an executable (i.e. .exe) with connections to 2 sqlite databases? i've read doc and it doesn't speak to this one way or the other. thanks, john ___ sqlite-users mailing list

Re: [sqlite] join performance query

Let's try that again : expose the [number] column to the outer selection (** are for emphasis**): ( select id_song, **number** from ( select id_song, **number** from PLAYLIST_SONG where id_playlist=2 {and|or } number > 258 ) as MYPLAYLISTSONGS Regards Tim Romano On Tue, May 11, 2010

Re: [sqlite] join performance query

And you would put move your title-condition to the outer query: . . . ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song where your title-condition and|or your title-number condition Regards Tim Romano ___ sqlite-users mailing list

Re: [sqlite] join performance query

Arrrgh, Google Chrome ate the top half of my reply. You must also expose the number column in the inner query against PLAYLIST_SONG; include your number-condition there and also specify the number column in the select-list: ( select id_song, number from ( select id_song from PLAYLIST_SONG

Re: [sqlite] join performance query

You could remove the title condition from the inner SONGS select, limiting your conditions to artist and genre; an index on column [artist] would make this subquery run quickly: ( select id_song from SONG where genre_id = 0 AND artist = 'Las ketchup' // AND title >= 'Asereje(karaoke

Re: [sqlite] SQLite Database in Shared Memory

I think you may be worrying too much about file speed as it's already pretty fast. But if you want AIX ramdisk check here: http://www.ee.pw.edu.pl/~pileckip/aix/mkramdisk.htm Michael D. Black Senior Scientist Northrop Grumman Mission Systems From:

Re: [sqlite] SQLite Database in Shared Memory

I am developing this solution for an AIX machine. I am not sure if it does any such optimization for the temp file system. As someone recommended, I can probably implement a VFS for Shared-memory, but that seems to be too much work :) I am inclining towards a file-based DB with syncs turned off.

Re: [sqlite] SQLITE_CORRUPT error

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/10/2010 04:07 PM, daksh jasra wrote: > I have ported SQLITE over VRTX based embedded platform, I'd suggest licensing the TH3 test suite in order to verify your port. You could be doing something like getting an operating wrong once the

Re: [sqlite] SQLite Database in Shared Memory

I am sorry... when I said IPC-based I really meant an IPC-Queue-based (the standard msgget()/mq_open() stuff). I have nothing against IPC :). It's just that an IPC-queue solution will not satisfy all my requirements. I will definitely be using IPC semaphore/mutex facility to avoid having to poll

Re: [sqlite] join performance query

Sorry but in your solution, how can I solve the condition AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke >> version)' OR number > 258) ? title is on song and number is song_number on Playlist_Song AS PS. Furthermore I also need title and number in place of your select *

Re: [sqlite] SQLite Database in Shared Memory

Manuj Bhatia wrote: > I do not have a requirement of persistence in my current design, but I > expect that we might extend this shared-queue solution to more areas of > the server and will require some sort of persistence then. > That is one of the main reasons I do not want to use IPC

Re: [sqlite] DB files are different between PC side and instrumentside.

> But I think at least > Sqlite3 should have used most space on the sector when it request a new > sector. Due to internal SQLite specifics it uses at least 1 page for each table and index. So even if you don't store there anything with a big schema database will still consume significant amount

Re: [sqlite] Update: set multiple values

but... ...but I LOVE my hammer! How dare every problem not be a nail? ;) Good point. Likely all the updates can fit nicely into a transaction. On Mon, May 10, 2010 at 5:11 PM, Simon Slavin wrote: > > On 10 May 2010, at 9:25pm, Adam DeVita wrote: > > > Simon, can

Re: [sqlite] SQLite Database in Shared Memory

> In short, using a SQLite-backed queue solution gives me a lot of options > that a simple IPC based (and, for that matter, even a professional Messaging > Product) does not give. Also SQLite-backed solution gives you a big restriction that IPC doesn't: you have to poll the queue instead of

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Your "reiterating 20 times" is not using a usleep so you'll blow by this most every time it's busy. Do this instead in all your proc's ret = sqlite3_step (p_stmt); if (SQLITE_BUSY == ret) { int n=0; usleep(10); // try one more time

Re: [sqlite] Insert large data question ??

On Tue, May 11, 2010 at 12:47 AM, 風箏 wrote: > Dear > > I have about 9 million data insert string need to insert into an table ,each > row data is unique > > this is a sample: > insert into mydata > VALUES(38824801,56888,'AABBCC',4.999,157,'2009/9/10 >

Re: [sqlite] SQLite Database in Shared Memory

Just 'cuz you don't need persitence now of course doesn't mean you can't use it. That solves your "shared memory" problem even though it's not as elegant. You can even access via file shares that way too which sounds a bit like what you may want do anyways. Michael D. Black Senior Scientist

Re: [sqlite] Documentation typo

2010/5/10 "Carlos Andrés Ramírez C." > > Hello guys, > I was breaking my head trying to figure out how to obtain the last > inserted row's ID --- using SQLite from Ruby. > > I found 'last_insert_rowid()' in your documentation at > http://www.sqlite.org/lang_corefunc.html

Re: [sqlite] Documentation typo

2010/5/10 "Carlos Andrés Ramírez C." : > > Hello guys, > I was breaking my head trying to figure out how to obtain the last > inserted row's ID --- using SQLite from Ruby. > > I found 'last_insert_rowid()' in your documentation at > http://www.sqlite.org/lang_corefunc.html  

Re: [sqlite] Insert large data question ??

Try to use transaction syntax. Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of �L�~ Sent: Tuesday, May 11, 2010 1:48 PM To: sqlite-users@sqlite.org Subject: [sqlite] Insert large data question ?? Dear I have about 9

Re: [sqlite] Documentation typo

On 10 May 2010, at 4:11pm, Carlos Andrés Ramírez C. wrote: > I was breaking my head trying to figure out how to obtain the last > inserted row's ID --- using SQLite from Ruby. > > I found 'last_insert_rowid()' in your documentation at > http://www.sqlite.org/lang_corefunc.html and still

Re: [sqlite] Insert large data question ??

Does the following document help? http://www.sqlite.org/faq.html#q19 On Tue, May 11, 2010 at 1:47 AM, 風箏 wrote: > Dear > > I have about 9 million data insert string need to insert into an table > ,each > row data is unique > > this is a sample: > insert into mydata >

Re: [sqlite] SQLite Database in Shared Memory

Pavel, I do not have a requirement of persistence in my current design, but I expect that we might extend this shared-queue solution to more areas of the server and will require some sort of persistence then. That is one of the main reasons I do not want to use IPC queues (there are other reasons

[sqlite] Insert large data question ??

Dear I have about 9 million data insert string need to insert into an table ,each row data is unique this is a sample: insert into mydata VALUES(38824801,56888,'AABBCC',4.999,157,'2009/9/10 19:55:50'); this is my schema: table|mydata|mydata|2|CREATE TABLE mydata ( itno

[sqlite] Documentation typo

Hello guys, I was breaking my head trying to figure out how to obtain the last inserted row's ID --- using SQLite from Ruby. I found 'last_insert_rowid()' in your documentation at http://www.sqlite.org/lang_corefunc.html and still did not do it. After spending a lot of time searching, I

Re: [sqlite] Returning column to default

- Original Message - From: "Alexey Pechnikov" Newsgroups: gmane.comp.db.sqlite.general Sent: Saturday, May 08, 2010 2:27 PM Subject: Re: Returning column to default Please send to me this patch. I think it may be added to unofficial http://sqlite.mobigroup.ru repository. No

Re: [sqlite] join performance query

1. Try discrete single-column indexes rather than multi-column composite indexes. 2. Try breaking the query down into subsets expressed as parenthetical queries; you can treat these parenthetical queries as if they were tables by assigning them an alias, and then you can join against the aliases.

Re: [sqlite] join performance query

On 11 May 2010 11:07, Andrea Galeazzi wrote: > Hi guys, > I'm in a bind for a huge time consuming query! . . . > The second case is about 35 times slower... so the scrolling is quite > impossible (or useless)! > SELECT song_number AS number,title FROM Song AS S, Playlist_Song

Re: [sqlite] Select via Wi-fi very slow

N.B. Queries with LIKE will not use an index if the particular implementation of SQLite overrides LIKE. The .NET implementation I'm familiar with has done so; the OP's may have done so too. However, GLOB was left intact and does make use of an index on "starts with" and "equals" substring

[sqlite] join performance query

Hi guys, I'm in a bind for a huge time consuming query! I made the following database schema: CREATE TABLE Song ( idINTEGER NOT NULL UNIQUE, titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, artistVARCHAR(40) NOT NULL DEFAULT ''

[sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Multi processes, getting so many errores of SQLITE_BUSY and SQLITE_MISUSE... And the system performance is very bad because of the three processes of insert/read/update database. How to improve the sqlite3's operations? _my codes___

Re: [sqlite] Foreign constraints and table recreation

On 11 May 2010, at 8:09am, Patrick Earl wrote: > sqlite> begin transaction; > sqlite> > sqlite> DROP TABLE "ParkingLotLevel"; > sqlite> DROP TABLE "Car"; > sqlite> DROP TABLE "ParkingLot"; > sqlite> > sqlite> Commit transaction; > Error: foreign key constraint failed > > And now, we switch Car

Re: [sqlite] Select via Wi-fi very slow

> Sometimes search found 200 records.  When I do a query via wi-fi takes 1 > minute. > How can I decrease this time? Time taken to search for the records does not depend on how many records found. It depends on how many records were searched through. Most probably for your query no indexes are

Re: [sqlite] DB files are different between PC side and instrumentside.

Hi, Dan, Yes, I noticed this setting. The sector size in SDHC card is 4Kbyte which is different from the size of harddriver. But I think at least Sqlite3 should have used most space on the sector when it request a new sector. It looks like that the Sqlite wastes a lot of space on SDHC card. Of

Re: [sqlite] DB files are different between PC side and instrument side.

On May 11, 2010, at 2:54 PM, Lei, Rick (GE EntSol, SensInsp) wrote: > > Hi, > > I ported Sqlite3 to my instrument. The database file is stored in a > SDHC > card. Sqlite3 runs ok. However I found the database file generated on > instrument side is much bigger than the file on PC side. I

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

To determine the type of columns in a view I use SELECT typeof(column) FROM viewname LIMIT something; Unfortunately if most of the column data is NULL then you can end up having to scan the entire table. I'm not sure how SQlite calculates these types, but this simple workaround has been OK for

Re: [sqlite] find same type

On 11 May 2010 08:49, Andrea Galeazzi wrote: > I've got this table > TABLE T ( >    id                    INTEGER NOT NULL UNIQUE, >   file_type            VARCHAR(10) NOT NULL) > My goal is to check if a certain selection has all the same values. I > thought that the following

[sqlite] DB files are different between PC side and instrument side.

Hi, I ported Sqlite3 to my instrument. The database file is stored in a SDHC card. Sqlite3 runs ok. However I found the database file generated on instrument side is much bigger than the file on PC side. I checked the files generated on instrument by UltraEdit. I found a lot of space which is

[sqlite] find same type

I've got this table TABLE T ( idINTEGER NOT NULL UNIQUE, file_typeVARCHAR(10) NOT NULL) My goal is to check if a certain selection has all the same values. I thought that the following statement should be enough for my aim: SELECT (SELECT file_type FROM T

Re: [sqlite] Foreign constraints and table recreation

Okay, I tried the strategy discussed previously but I'm still having problems. Either I'm not seeing something, or there's a bug in the foreign constraint support. Take a look at the following two execution snippets: sqlite> sqlite> commit transaction; sqlite> sqlite> begin transaction; sqlite>