Re: [sqlite] does sqlite support mutil-thread be run in samedatabase?
But I still get orignal error message, a pice of code as follow, suppose all
routines open the same database:
void fillSchedule(void *param)
{
Open();
...
Close();
}
void fillCredits(void *param)
{
Open();
...
Close();
}
int main(void)
{
Open()
...
Close();
hThreadSchedule = CreateThread(..., fillSchedule, ...);
hThreadCredits = CreateThread(..., fillCredits, ...);
WaitForSingleObject(.., hThreadSchedule,...);
WaitForSingleObject(.., hThreadCredits,...);
}
So does the through is right?
- Original Message -
From: "Dan Kennedy" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 19, 2007 1:08 PM
Subject: Re: [sqlite] does sqlite support mutil-thread be run in samedatabase?
>
> On Fri, 2007-01-19 at 11:42 +0800, LuYanJun wrote:
>> The next question is that if I want to use multi-thread in sqlite, so
>> I need to open same database for two times, right?
>
> Right. Each thread has to have it's own sqlite3* handle.
>
>
>> - Original Message -
>> From: "LuYanJun" <[EMAIL PROTECTED]>
>> To: "sqlite-users sqlite.org"
>> Sent: Friday, January 19, 2007 10:53 AM
>> Subject: [sqlite] does sqlite support mutil-thread be run in same database?
>>
>>
>> > Hi guys
>> > does sqlite support mutil-thread be run in same database? because of I
>> > am stuck in such below sqlite error message:
>> > error number = 21
>> > error message = library routine called out of sequence
>> > certainly, I get some useful hints from stuff about interface of SQLite,
>> > but I am not sure that weather sqlite support multi-thread.
>> > Tks in advance.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
Re: [sqlite] Incorrect trigger behavior?
On Thu, 2007-01-18 at 11:21 -0800, mikpol wrote:
> Hi,
>
> I got a weid behavior while writing some triggers. It would be best to show
> it using an example. Basically, I have two tables A and B. When something is
> inserted into A, it is inserted into B with a trigger using the rowid of
> newly inserted row of A. However, if this id already exists in B, nothing
> should be inserted. Here's the SQL code:
>
> create table A (stuff text not null);
> create table B (id int not null unique, stuff text not null);
> create trigger trig after insert on A for each row begin
> insert or ignore into B values (new.rowid, new.stuff);
> end;
>
> Notice how the trigger does "or ignore", so if a row with that id was
> already inserted into B manually, inserting a row with that rowid into A
> should not change B (since id column of B has "unique" property). Now some
> table manipulations:
>
> insert into A values ('hello');
> select * from B;
> id stuff
> -- --
> 1 hello
>
> This worked as it should. It correctly inserted a row into B with rowid and
> stuff passed to A. Next I do:
>
> insert into B values (2, 'THIS SHOULD NOT CHANGE');
> insert into A (rowid,stuff) values (2, 'I WANT TO CHANGE THIS');
> select * from B;
> id stuff
> --
> 1 hello
> 2 THIS SHOULD NOT CHANGE
>
> Here I inserted a row with id 2 into B before inserting into A with rowid 2.
> In this case I don't want the trigger to change B, and "on ignore" conflict
> strategy in the trigger should take care of that. This case works correctly
> since the value in B stayed the same. Now comes the weird case:
>
> insert into B values (3, 'THIS SHOULD ALSO NOT CHANGE');
> insert or replace into A (rowid,stuff) values (3, 'I CHANGED THIS');
> select * from B;
> id stuff
> ---
> 1hello
> 2THIS SHOULD NOT CHANGE
> 3I CHANGED THIS
>
> Again, I inserted row with id 3 into B, expecting the trigger not to change
> it when row with rowid 3 is inserted into A. However, specifying "or
> replace" in the insert statement while inserting into A changes the behavior
> of the trigger, and now my value in B is changed. In other words, "or
> replace" in insert statement that fires the trigger has overridden the "or
> ignore" behavior in the trigger itself.
>
> Why does this happen? From my understanding, the trigger should just fire
> after insert is done into A, and should behave the same no matter what
> on-conflict strategy is used. My version of sqlite is 3.2.8.
Not so, as it turns out. It's tricky to find, but in the CREATE TRIGGER
documentation:
"An ON CONFLICT clause may be specified as part of an UPDATE or
INSERT trigger-step. However if an ON CONFLICT clause is specified
as part of the statement causing the trigger to fire, then this
conflict handling policy is used instead."
http://www.sqlite.org/lang_createtrigger.html
Dan.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Pager Question... Open Source Project
You may find that adding backward pointers will allow you to do
deletions better.
Cesar Rodas wrote:
project called, PDBM.
The project is DBM like project, with a B+tree, and key -> value data,
similar to BDB, QDBM or GDBM.
For that project I need implement a Pager system.
As I understand a Page is the minimum IO block, and a Data could have more
than a Page but a Page just one Data.. Am I right?
Here I will write you my structures... please correct me if i am wrong...
/*
**This is the Page 0. This is a special Page that have information
**about total of pages, total of allocated but free pages, a pointer to
the
**first and last allocated but free pages.
*/
typedef struct MainPage
{
unsigned char lock;
size_t first_free;
size_t last_free;
size_t total;
size_t free;
} MainPage;
/*
**The pager struct. A page is a chunk of 1024 bytes of data.
**A data cold have more than a page, but a page could have only
**a data. So a Page is the minimun allocated space for a data.
*/
typedef struct Pager
{
/***/
unsigned char lock; /* 1-Byte, if the Page is locked.
*/
size_t main; /* 4-Byte, a pointer to the begin of the data
*/
size_t next; /* 4-Byte, a pointer to the next Page position
*/
unsigned char file; /* 1-Byte, In what file part if the next Pager
*/
unsigned char data[1024]; /* 1 KB, the content of a Page
*/
/***/
} Pager;
#define PAGER_LOCKED 0xAF
#define PAGER_UNLOCK 0xFF
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Fw: Problem in version 3.3.10
Try recompiling with Intel C++ Compiler. 2007/1/19, A.J.Millan <[EMAIL PROTECTED]>: Hi: An application was running Ok under version 3.3.7. When recompiling with v. 3.3.10, crashed at certain point in runtime. "invalid page in module MSVCRT.DLL of 0167:780027a3". The Sistem is running MS Windows98 SE fully patched. Msvcrt.dll is version 6.10.8924.0. I compile with GNU G++ 3.4.2-20040916-1 for Windows (Dev-C++). Is there some known bug? Some way to compile without the Msvcrt.dll dependency? I'd be grateful for any help. A. J. Millan - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] File db to memory db and back
On Fri, Jan 19, 2007 at 12:51:36AM +0100, Nemanja Corlija wrote: > On 1/19/07, Nicolas Williams <[EMAIL PROTECTED]> wrote: > >Why don't you use a trigger to duplicate INSERTs into the memory db to > >the disk db? > > > Nicolas, thanks for the idea. Didn't really think of that. > > But in my experience inserting into disk db with a unique index is > very slow when you have millions of rows. I think last time I did that > table had around 6-7 million rows and it took 2 hours or maybe even > more to insert 500.000 rows. If you kept the rowids in sync in the two DBs then you could keep the updates very fast by not making that column a primary key. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Fw: Problem in version 3.3.10
Hi: An application was running Ok under version 3.3.7. When recompiling with v. 3.3.10, crashed at certain point in runtime. "invalid page in module MSVCRT.DLL of 0167:780027a3". The Sistem is running MS Windows98 SE fully patched. Msvcrt.dll is version 6.10.8924.0. I compile with GNU G++ 3.4.2-20040916-1 for Windows (Dev-C++). Is there some known bug? Some way to compile without the Msvcrt.dll dependency? I'd be grateful for any help. A. J. Millan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Incorrect trigger behavior?
Hi,
I got a weid behavior while writing some triggers. It would be best to show
it using an example. Basically, I have two tables A and B. When something is
inserted into A, it is inserted into B with a trigger using the rowid of
newly inserted row of A. However, if this id already exists in B, nothing
should be inserted. Here's the SQL code:
create table A (stuff text not null);
create table B (id int not null unique, stuff text not null);
create trigger trig after insert on A for each row begin
insert or ignore into B values (new.rowid, new.stuff);
end;
Notice how the trigger does "or ignore", so if a row with that id was
already inserted into B manually, inserting a row with that rowid into A
should not change B (since id column of B has "unique" property). Now some
table manipulations:
insert into A values ('hello');
select * from B;
id stuff
-- --
1 hello
This worked as it should. It correctly inserted a row into B with rowid and
stuff passed to A. Next I do:
insert into B values (2, 'THIS SHOULD NOT CHANGE');
insert into A (rowid,stuff) values (2, 'I WANT TO CHANGE THIS');
select * from B;
id stuff
--
1 hello
2 THIS SHOULD NOT CHANGE
Here I inserted a row with id 2 into B before inserting into A with rowid 2.
In this case I don't want the trigger to change B, and "on ignore" conflict
strategy in the trigger should take care of that. This case works correctly
since the value in B stayed the same. Now comes the weird case:
insert into B values (3, 'THIS SHOULD ALSO NOT CHANGE');
insert or replace into A (rowid,stuff) values (3, 'I CHANGED THIS');
select * from B;
id stuff
---
1hello
2THIS SHOULD NOT CHANGE
3I CHANGED THIS
Again, I inserted row with id 3 into B, expecting the trigger not to change
it when row with rowid 3 is inserted into A. However, specifying "or
replace" in the insert statement while inserting into A changes the behavior
of the trigger, and now my value in B is changed. In other words, "or
replace" in insert statement that fires the trigger has overridden the "or
ignore" behavior in the trigger itself.
Why does this happen? From my understanding, the trigger should just fire
after insert is done into A, and should behave the same no matter what
on-conflict strategy is used. My version of sqlite is 3.2.8.
Any help would be greatly appreciated.
--
View this message in context:
http://www.nabble.com/Incorrect-trigger-behavior--tf3036078.html#a8437145
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: RE: [sqlite] DROP INDEX not freeing up memory
Well, those extra 2MB sure would be nice when we go 'in-game'. We primarily use Sqlite while we're 'in-UI' and when we transition to going in-game, we try to free up as much memory as possible. Thus, our usage pattern of creating/dropping the same indices over and over. We might be able to find 2MB elsewhere, or cut down other in-game memory usage...but like I said, 2MB is like gold in the world of game development. Thanks, Dave -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Thursday, January 18, 2007 6:18 AM To: sqlite-users@sqlite.org Subject: RE: RE: [sqlite] DROP INDEX not freeing up memory Do you have to drop the index? Why not just keep it around, its obviously useful if you need to create it in the first place right? Dave Gierok <[EMAIL PROTECTED]> wrote: It does in fact look like the memory isn't being freed up entirely. I am properly tracking xMalloc, xRealloc, and xFree. I have a memory database and wrote some test code to loop a few times creating/dropping the same index. The results of that are (numbers are total bytes allocated): 7632746 Before 1st Create Index 7637587 After 1st Create Index 7637434 After 1st Drop Index (and Vacuum -- the vacuum doesn't change memory allocated though) 7637587 After 2nd Create Index 7637434 After 2nd Drop Index (and Vacuum) 7637587 After 3rd Create Index 7637434 After 3rd Drop Index (and Vacuum) Notice that the memory slightly decreases after the 1st Drop Index, but doesn't nearly drop to what it should (it should drop to 7632746 -- the same level as before the 1st Create Index). Also notice that after the 1st create/drop, the memory allocated is the same after each create and after each drop. So it implies there is not a leak -- but we can't get down to the original level before we created the first index. So what's the big deal you might ask -- this example shows 5K that can't be reclaimed. But in our game we create hundreds of indices that take up about 2MB -- 2MB is quite a bit of memory in our world. Any suggestions or explanations? Thanks, Dave -Original Message- From: Dave Gierok [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 9:06 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] DROP INDEX not freeing up memory OK, thanks, I'll do some more digging and let you know. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok wrote: > xTruncate is not being called because this is an in-memory database. Memor= > y databases should have memory freed instead I assume? > In-memory databases call sqliteFree() to release their memory. I checked, and this does appear to work. Perhaps the sqliteFree() call is somehow not making it down into your measurement layer. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pragma for headers ( pipe or redirect a series of commands)
On Jan 18, 2007, at 8:01 AM, [EMAIL PROTECTED] wrote: I don't know what capability Applescript has. The easy way to do it with a script in the various Linux shell languages is with a "HERE" document, where the input to the command is redirected from the block of lines which follows the command. In your case, you'd do something like: sqlite3 filename.db <<'EOF' .headers on .mode column customers select * from customers ; EOF If Applcscript doesn't support HERE documents (it's unlikely it does), you can accomplish something similar with redirecting from a separate file. I would hope it has that capability. You'd then do something like this to create a "commands" file: echo '.headers on' > commands echo '.mode column customers' >> commands echo 'select * from customers;' >> commands and then run sqlite using that command file for input: sqlite3 filename.db < commands Hope that helps. Derrell Derrell, Your solution solved my problem to a tee. Using a command file to issue multiple commands to sqlite when calling it from Applescript worked perfect. I knew that I probably could not mix and match "dot commands" with "sql commands", but I thought perhaps I could do so using "pragma & sql" commands. Thanks for taking the time to help me, especially so quickly. I shut down my computer about 2:30AM last night, and this morning when I woke up around 8:30AM I checked the email, just in case, and I immediately saw the light when I read your message... THANKS A MILLION FOR YOUR HELP... Best regards, Bill Hernandez Plano, Texas The term AppleScript comes in two flavors (both which are free and come with the operating system): ( 1 ) AppleScript which allows you to access (set/get) info from almost all applications, including the OS itself. It provides incredible flexibility, and power to an average user. Most all commercial software is scriptable. The interface is not a strong point, there are dialogs to display as well as get user input, it also provides choice lists that you can build on the fly, and have the user pick one or more items, it does a lot more, but there are GUI limitations. ( 2 ) Applescript Studio is full blown rapid application build environment that allows a user to build complete GUI applications using anywhere from a few lines to a few hundred lines of applescript code, instead of having to use thousands of lines of Objective C code. Not only that but but the xCode environment provides all the interface items one could ever hope for, along with all code that has already been in production to build the OS X operating system itself. The OSX xCode development environment provides all the interface tools, and allows the user to use any number of languages to create an application. Once completed you cannot tell what language it was written in. So you could write a massive amount of Objective C code, or a few lines of AppleScript code, and the end result once compiled will be identical. Anything you build using xCode is automatically scriptable. If I attach a data source containing the results of a query, to a GUI "Table View" object, anytime the user clicks on any of the column headers, the object handles the sorting of the records (up/down) without any code on my part whatsoever, there is no need to query/ fetch again from the database just to get the sorted records. I have been doing structured programming on the Mac for about 19 years, and always hated the AppleScript syntax, but a few months ago I decided to really try to learn it in earnest, and I now realize what an incredible environment it truly is, it's like the iPod of development environments with built in GUI objects, debuggers, etc. Talk about minimal amount of code... OSX comes with sqlite already installed. A guy named "Adam Bell" wrote a sample AppleScript that showed how to access sqlite, create, search, modify, etc from within AppleScript issuing the commands directly to any one of the Unix Shells. Basically I can have AppleScript open a shell, issue a numbe of commands, retrieve the results, massage them, etc, and close the shell when done, or I can run shell commands in the background so the user never sees the shell itself, which is what I will be doing. One of the basic handlers (functions) within the script that "Adam Bell" wrote, and I used to create the DB and enter a few records : -- +-+-+-+-+-+-+ on SQLite_CreateDatabase() set loc to space & "~/desktop/TestDB.db" & space set head to "sqlite3" & loc & quote set tail to quote -- "head" tells SQLite where to put our db if it doesn't exist, identifies it if it does. -- "head" is the opening statement of every future command to our db. -- "tail" ends every query started with "head". -- Next, we set up a table and give the columns labels
RE: RE: [sqlite] DROP INDEX not freeing up memory
Do you have to drop the index? Why not just keep it around, its obviously useful if you need to create it in the first place right? Dave Gierok <[EMAIL PROTECTED]> wrote: It does in fact look like the memory isn't being freed up entirely. I am properly tracking xMalloc, xRealloc, and xFree. I have a memory database and wrote some test code to loop a few times creating/dropping the same index. The results of that are (numbers are total bytes allocated): 7632746 Before 1st Create Index 7637587 After 1st Create Index 7637434 After 1st Drop Index (and Vacuum -- the vacuum doesn't change memory allocated though) 7637587 After 2nd Create Index 7637434 After 2nd Drop Index (and Vacuum) 7637587 After 3rd Create Index 7637434 After 3rd Drop Index (and Vacuum) Notice that the memory slightly decreases after the 1st Drop Index, but doesn't nearly drop to what it should (it should drop to 7632746 -- the same level as before the 1st Create Index). Also notice that after the 1st create/drop, the memory allocated is the same after each create and after each drop. So it implies there is not a leak -- but we can't get down to the original level before we created the first index. So what's the big deal you might ask -- this example shows 5K that can't be reclaimed. But in our game we create hundreds of indices that take up about 2MB -- 2MB is quite a bit of memory in our world. Any suggestions or explanations? Thanks, Dave -Original Message- From: Dave Gierok [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 9:06 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] DROP INDEX not freeing up memory OK, thanks, I'll do some more digging and let you know. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok wrote: > xTruncate is not being called because this is an in-memory database. Memor= > y databases should have memory freed instead I assume? > In-memory databases call sqliteFree() to release their memory. I checked, and this does appear to work. Perhaps the sqliteFree() call is somehow not making it down into your measurement layer. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DROP INDEX not freeing up memory
If you are using the OS to determine if memory is freed then perhaps that is
where the problem lies. Many operating systems do not return memory back to the
OS after a call to free. Instead the process will retain that in a "free pool"
for later re-allocation.
Ken
Dave Gierok <[EMAIL PROTECTED]> wrote:
xTruncate is not being called because this is an in-memory database. Memory
databases should have memory freed instead I assume?
Thanks,
Dave
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 16, 2007 3:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory
Dave Gierok wrote:
>
> I am running an in-memory db and am using the functionality where
> we override all the OS calls such that we implement our own memory
> management and file IO. Therefore, I am able to easily track the
> amount of memory Sqlite is taking up. I have noticed that when I
> call CREATE INDEX and then the corresponding DROP INDEX, I do not
> see the memory drop to the same level as it was before the CREATE
> INDEX. I also call VACUUM after DROP INDEX, which makes no difference.
>
I tried this using the built-in database functionality and the
memory is being freed there. I did:
CREATE TABLE t1(x);
INSERT INTO t1 VALUES('hello');
CREATE INDEX i1 ON t1(x);
DROP INDEX i1;
VACUUM;
And then set a breakpoint at pager.c:1972 where the freeing of
memory occurs, and it does happen.
But I have no way to trouble-shoot your overridden OS calls.
The first place I would look would be in your implementation
of sqlite3OsTruncate(). Are you sure you are doing it right?
Does sqlite3OsTruncate() get called when you VACUUM?
--
D. Richard Hipp
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] pragma for headers ( pipe or redirect a series of commands)
Bill Hernandez <[EMAIL PROTECTED]> writes: > I tried finding a pragma command for .headers on, but didn't have any luck. > > sqlite> .headers on | .mode column customers | select * from customers ; > I also tried : > > sqlite> select * from customers ; < .headers off > > and that didn't work either > You're mixing (incorrect use of) shell pipe commands with commands to the sqlite shell in these examples. > The reason I am trying to do this in one call rather than using multiple > lines is that one "do shell script" call is totally independent from the > next, unlike scripting to a shell window which I don't want to do. > > sqlite> .headers on > sqlite> .mode column customers > sqlite> select * from customers ; > > is that I am calling it from Applescript, and one "do shell script" to > sqlite doesn't have a clue what the previous one did. I don't know what capability Applescript has. The easy way to do it with a script in the various Linux shell languages is with a "HERE" document, where the input to the command is redirected from the block of lines which follows the command. In your case, you'd do something like: sqlite3 filename.db <<'EOF' .headers on .mode column customers select * from customers ; EOF If Applcscript doesn't support HERE documents (it's unlikely it does), you can accomplish something similar with redirecting from a separate file. I would hope it has that capability. You'd then do something like this to create a "commands" file: echo '.headers on' > commands echo '.mode column customers' >> commands echo 'select * from customers;' >> commands and then run sqlite using that command file for input: sqlite3 filename.db < commands Hope that helps. Derrell - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance test on windows
I try an very long insert without BEGIN and COMMIT and this is very slow.. with a transaction is very very fast... just try and tell us the result Gaurav On 18/01/07, Ian Frosst <[EMAIL PROTECTED]> wrote: It could be that there is no transaction block wrapped around the inserts (a BEGIN TRANSACTION before the start of the insert loop, and a COMMIT TRANSACTION at the end.) If there is no explicit transaction, then every insert has an implied transaction, which considerably slows down the database engine. Ian On 1/18/07, Gaurav Arora <[EMAIL PROTECTED]> wrote: > > Hi All, > I am a newbie to SQlite, just saw that the performance numbers on > www.sqlite.org are not reliable (as per the notce on website, > http://www.sqlite.org/speed.html http://www.sqlite.org/speed.html> > ) > So, I thought of profiling SQlite operations, on linux platform > the performance time is quite good. > I build sqlite on Windows too, and saw discouraging numbers coming out of > it, following are some of them. > e.g. > RH9: > inserting 1000 records ~.07 seconds. > > WindowsXP: > inserting 100 records ~11 seconds. > inserting 1000 records ~113 seconds. > > May be I did something wrong for building, or the parameters passed for > building sqlite werent correct. > I am willing to update the test results on the site, could anyone here > guide > me for the things which I might be doing wrong. > > Thanks in advance. > //Gaurav > > -- Cesar Rodas http://www.sf.net/projects/pagerank (The PageRank made easy...) http://www.sf.net/projects/fastfs ( The Fast File System) Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] Performance test on windows
It could be that there is no transaction block wrapped around the inserts (a BEGIN TRANSACTION before the start of the insert loop, and a COMMIT TRANSACTION at the end.) If there is no explicit transaction, then every insert has an implied transaction, which considerably slows down the database engine. Ian On 1/18/07, Gaurav Arora <[EMAIL PROTECTED]> wrote: Hi All, I am a newbie to SQlite, just saw that the performance numbers on www.sqlite.org are not reliable (as per the notce on website, http://www.sqlite.org/speed.html http://www.sqlite.org/speed.html> ) So, I thought of profiling SQlite operations, on linux platform the performance time is quite good. I build sqlite on Windows too, and saw discouraging numbers coming out of it, following are some of them. e.g. RH9: inserting 1000 records ~.07 seconds. WindowsXP: inserting 100 records ~11 seconds. inserting 1000 records ~113 seconds. May be I did something wrong for building, or the parameters passed for building sqlite werent correct. I am willing to update the test results on the site, could anyone here guide me for the things which I might be doing wrong. Thanks in advance. //Gaurav
[sqlite] pragma for headers ( pipe or redirect a series of commands)
On page 18 of the book "SQLite" by Chris Newman, he states "... it is
possible to pipe or redirect a series of commands to the program
rather than key them in"
I'm trying to get this to work using the shell, and cannot seem to do
it. Once I get it working then I can call them as shown below in the
"do shell script..." code below
I tried finding a pragma command for .headers on, but didn't have any
luck.
sqlite> .headers on | .mode column customers | select * from customers ;
I also tried :
sqlite> select * from customers ; < .headers off
and that didn't work either
The reason I am trying to do this in one call rather than using
multiple lines is that one "do shell script" call is totally
independent from the next, unlike scripting to a shell window which I
don't want to do.
sqlite> .headers on
sqlite> .mode column customers
sqlite> select * from customers ;
is that I am calling it from Applescript, and one "do shell script"
to sqlite doesn't have a clue what the previous one did.
do shell script "sqlite3 ~/desktop/TestDB.db " & ".headers on"
do shell script "sqlite3 -column ~/desktop/TestDB.db " & quote &
thisQuery & quote)
I realize that I am trying to mix dot commands with sql commands, but
there doesn't seem to be a pragma for the headers
so I could do something like :
sqlite> pragma set_headers("on") ; select * from customers ;
Which I could then convert to :
do shell script "sqlite3 -column ~/desktop/TestDB.db " & quote &
pragma set_headers("on") ; select * from customers ; & quote)
handler snippet :
on run
set thisTable to "customers"
set searchField to "lastname"
set searchValue to "somevalue"
set sortField to "firstname"
set sortDir to "asc"
set whichChoice to "contains"
my sqlite_DoTheSearch(whichChoice, thisTable, searchField,
searchValue, sortField, sortDir)
end run
on sqlite_DoTheSearch(whichChoice, thisTable, searchField,
searchValue, sortField, sortDir)
... missing lines here
else if (whichChoice = "contains") then
set thisQuery to "select * from " & thisTable & " where " &
searchField & " LIKE '%" & searchValue & "%' order by " & sortField &
" " & sortDir & ";"
... missing lines here
display dialog ("QUERY : " & quote & thisQuery & quote & return &
return & (do shell script "sqlite3 -column ~/desktop/TestDB.db " &
quote & thisQuery & quote))
end sqlite_DoTheSearch
I'd be grateful for any insights...
Bill Hernandez
Plano, Texas
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
