Re: [sqlite] SQLITE is not working in pthread

[Pramoda M. A]

Hi,



  We r using Fedora Linux 10. Our application is, when any device is inserted, 
we will detect that create thread using pthread_create API. Which will find all 
MP3 files in the device and extract the metadata present in the mp3 files and 
fill a structre and send it to sqlite.



Before that, I will create database like;



int EDB_Init(void)

{

Printf("entered");

if(sqlite3_open(DATABASE,&db) == SQLITE_OK)

{

Printf("entetred 2\n");

if(sqlite3_exec(db,"create table File(Song_id 
int primary key, path varchar(100), FileType int)", NULL, 0, &GucpErrMsg) == 
SQLITE_OK)

printf("entered 3");

{

if(sqlite3_exec(db,"create 
table Metadata(Song_id int primary key, Album varchar(30), Artist varchar(30), 
Title varchar(30), Genre varchar(30), Time int, playlistName varchar(15))", 
NULL, 0, &GucpErrMsg) == SQLITE_OK)

{


sqlite3_close(db);

return SUCCESS;

}

else

{


sqlite3_free(GucpErrMsg);


sqlite3_close(db);

return 
CREATE_METADATA_ERROR;

}

}

else

{

Printf("printing 4");

sqlite3_free(GucpErrMsg);

sqlite3_close(db);

return CREATE_FILE_TABLE_ERROR;

}

}

else

{

printf("NOT Opened DataBase\n");

return DATABASE_OPEN_ERROR;

}

}

This will run in thread. But here, "entered"  and "entered 2"is printing.  But 
"entered 3" and "printing 4" both are not printing.

After that, if I tried to query the database using command line tools, "Select 
* from File", it is giving error as

"encrypted file or is not a database"



Please help me.





Thanks & Regards

Pramoda.M.A

CREST | KPIT Cummins Infosystems Limited | Bengaluru | Cell: +91 91640 57663



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of pierr
Sent: Friday, July 10, 2009 11:59 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLITE is not working in pthread









Pramoda M. A wrote:

>

> Hi All

>

>Sqlite is not running in pthread. At first time, sqlite_open will

> execute in pthread. From next time, no one API is executing.

> Please guide me to make SQLite run in pthread.

> Please anybody help me.

>

>

Pramoda ,

 Would you please provide some code snip so that we can know what exactly

are you doing?

--

View this message in context: 
http://www.nabble.com/SQLITE-is-not-working-in-pthread-tp24410382p24422426.html

Sent from the SQLite mailing list archive at Nabble.com.



___

sqlite-users mailing list

sqlite-users@sqlite.org

http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE is not working in pthread

[pierr]

Pramoda M. A wrote:
> 
> Hi All
> 
>Sqlite is not running in pthread. At first time, sqlite_open will
> execute in pthread. From next time, no one API is executing.
> Please guide me to make SQLite run in pthread.
> Please anybody help me.
> 
> 
Pramoda ,
 Would you please provide some code snip so that we can know what exactly
are you doing?
-- 
View this message in context: 
http://www.nabble.com/SQLITE-is-not-working-in-pthread-tp24410382p24422426.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to break subquery into 2 simply query

[pierr]

Following 2 statements took 400ms to be excuted on a 350M MIPS CPU and it is
a memory database:

 "DELETE FROM tblEvent_type WHERE eguid in (SELECT rowid FROM tblEvent_basic
WHERE sguid=11);";
 "DELETE FROM tblEvent_group WHERE eguid in (SELECT rowid FROM
tblEvent_basic WHERE sguid=11);";
(An index has been created on tblEvent_group(eguid) , no other index so
far.)

And also I notice that  "(SELECT rowid FROM tblEvent_basic WHERE
sguid=11)"`was excuted twice ,so I'd like to try to split the subquery to
something like below to see if there will be any performance gain:

result =  exe_sql "(SELECT rowid FROM tblEvent_basic WHERE sguid=11);";
 exe_sql "DELETE FROM tblEvent_type WHERE eguid in  (result)
 exe_sql "DELETE FROM tblEvent_group WHERE eguid in (result)

How to get the parmater (result) binding to follwing statement in sqlite?
"DELETE FROM tblEvent_group WHERE eguid in (?) #how to bind result here
I am using sqlite3 C API directly.



-- 
View this message in context: 
http://www.nabble.com/how-to-break-subquery-into-2-simply-query-tp24422352p24422352.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE is not working in pthread

[Pramoda M. A]

Hi All

   Sqlite is not running in pthread. At first time, sqlite_open will execute in 
pthread. From next time, no one API is executing.
Please guide me to make SQLite run in pthread.
Please anybody help me.



Thanks & Regards
Pramoda.M.A
CREST | KPIT Cummins Infosystems Limited | Bengaluru | Cell: +91 91640 57663

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Viewer Tool

[灵感之源]

why not windows 2003/2008?

2009/7/10, Simon Slavin :
>
> On 8 Jul 2009, at 7:20pm, TSGames wrote:
>
> > I have developed a gui-based tool to create, edit and view SQLite
> > databases.
>
> Just a note that this is a Windows 2000/XP/Vista/7 product only.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
灵感之源
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?

[liubin liu]

Thank you very much for your post

Just after I post the last code I know the concept of "reentrant" and
"thread-safety".

And you should notice the wrong usage of printf() in signal handle func. ^_^

So I re-writed the code and re-test. And the problem is still in someplace.
So I re-post the question.

Again, I appreciate your answer, :)



Pavel Ivanov-2 wrote:
> 
> Why do you re-post your code as if it's another question not related
> to previous one (along with the answer)?
> 
>> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
> 
> If SQLite is compiled with SQLITE_THREADSAFE = 1 or 2 then API is
> thread-safe (with some limitations when it's 2), if SQLITE_THREADSAFE
> = 0 then API is not thread-safe. But in both cases I have a doubt
> about signal-safety and also API is not re-entrant for sure except for
> some specially designed functions.
> 
> Pavel
> 
> On Thu, Jul 9, 2009 at 3:52 AM, liubin liu<7101...@sina.com> wrote:
>>
>> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
>>
>>
>> 
>>
>>
>> #include       // for printf()
>> #include      // for signal()
>> #include      // for alarm()
>> #include      // for system()
>> #include     // for pthread_create()
>> #include     // for sqlite3_***
>>
>>
>>
>> sqlite3 *db = NULL;
>>
>> int timer_handle_mark1 = 0;
>> int timer_handle_mark2 = 9;
>>
>>
>>
>> pthread_t trd1;
>>
>> void trd1_task ( void );
>>
>>
>>
>> int sqlite3_helper_create_db (void);
>>
>> int sqlite3_helper_insert_data (void);
>>
>> // psf - prepare, step, finalize
>> int sqlite3_helper_get_data_psf_from_tb1 ( int id );
>>
>> void timer_handler()
>> {
>>    int ret = -1;
>>    int i = 9;
>>
>>    char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
>>    char *query_string = NULL;
>>
>>
>>    sqlite3_stmt *p_stmt = NULL;
>>
>>    timer_handle_mark2 = 0;
>>    query_string = sqlite3_mprintf ( query_format2, i%500 );
>>    timer_handle_mark1++;
>>
>>    timer_handle_mark2 = 1;
>>    ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
>>    timer_handle_mark1++;
>>
>>    timer_handle_mark2 = 2;
>>    ret = sqlite3_step ( p_stmt );
>>    timer_handle_mark1++;
>>
>>    //if ( SQLITE_ROW == ret )
>>    //    printf ( "# IN timer_handler(), id: %d, length: %d\n",
>> sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
>>
>>    timer_handle_mark2 = 3;
>>    sqlite3_finalize ( p_stmt );
>>    timer_handle_mark1++;
>>
>>
>>    alarm(1);
>> }
>>
>> int inittimer()
>> {
>>    signal ( SIGALRM, timer_handler );
>>    alarm(1);
>>    return 0;
>> }
>>
>> void trd1_task ( void )
>> {
>>    sleep (30);
>>    printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
>> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>>    sleep (50);
>>    printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
>> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>> }
>>
>>
>>
>> int main ( void )
>> {
>>    int ret = -1;
>>    int i = 0;
>>
>>    ret = pthread_create ( &trd1, 0, (void *)trd1_task, 0 );
>>
>>    ret = sqlite3_open ( "testsignal.db", &db );
>>
>>    sqlite3_helper_create_db ();
>>
>>    sqlite3_helper_insert_data ();
>>
>>    ret = inittimer();
>>
>>    for ( i=0; ; i++ )
>>        ret = sqlite3_helper_get_data_psf_from_tb1 ( i );
>>
>>    ret = sqlite3_close ( db );
>>
>>    system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );
>>
>>    return 0;
>> }
>>
>>
>> int sqlite3_helper_create_db (void)
>> {
>>    int ret = -1;
>>    char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
>> INTEGER, data CHAR(50));";
>>    char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
>> INTEGER, data CHAR(50));";
>>
>>    ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
>>    //printf ( "ret: %d\n", ret );
>>    ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
>>    //printf ( "ret: %d\n", ret );
>>    if ( SQLITE_OK == ret )
>>        printf ( "# IN main(), create db file, SUCCESS!\n" );
>>
>>
>>    return 0;
>> }
>>
>>
>> int sqlite3_helper_insert_data (void)
>> {
>>    int ret = -1;
>>    int i = 0;
>>
>>    sqlite3_stmt *p_stmt = NULL;
>>
>>
>>    char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
>> VALUES (%d, %d, %Q);";
>>    char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
>> VALUES (%d, %d, %Q);";
>>    char *sql = NULL;
>>
>>
>>    ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
>>    for ( i=0; i<500; i++ )
>>    {
>>        sql = sqlite3_mprintf ( sql_format1, i, i%10,
>> "datadatadatadatadatadatadatadatadatadatadata" );
>>        ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
>>        ret = sqlite3_step ( p_stmt );
>>        sqlite3_free ( sql );
>>        ret = sqlite3_finalize ( p_stmt );
>>
>>        sql = sqlite3_mprintf ( sql_format2, i, i%10,
>> "datadatadatadatadatadatadatadatadatadatadata" );
>>        ret = sqli

Re: [sqlite] blob c/c++ sample code

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Wayne Anderson wrote:
> I'm unclear about several things, but chief among them is how to set  
> the initial size of a blob. 

If you are using SQL then use:

  INSERT into foo values(zeroblob(12345))

If you are using bindings with a statement like:

  INSERT into foo values(?)

Then use sqlite3_bind_zeroblob to set the size.  This effectively makes
the value be nulls/zero for whatever size you set.  Then use the
incremental blob api to read/write the contents of the blob as you deem fit.

> The documentation indicates that you need  
> to use the "UPDATE SQL Command" to change the size of a blob but I  
> have no idea how to do this from within a C/C++ program.

As you saw in the documentation the blob api does not allow for changing
the size of the blob.  You use the UPDATE command with zeroblobs as
shown in the insert stuff above to create a new blob of whatever size
you want.

Aside: You can do an append abusing || which returns a string and cast
it back to blob, but it is very hacky.  For example this will increase
the size by 2048 bytes:

  UPDATE foo SET x=CAST( x || zeroblob(2048) AS blob) WHERE 

> Can anyone point me to some sample code?

Pretty much only the page you already read:

  http://www.sqlite.org/c3ref/blob_open.html

The functions are regular open/read/write/close with the proviso that
you can't change the blob size.  Also note what happens if the database
is altered while you have blobs open.  You use zeroblobs to efficiently
allocate the space for blobs.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpWi+YACgkQmOOfHg372QRm4wCg2R6DNGpcwLiBVA2e/PNKXfRA
/n4AoJy8+G8jkij5ComIzZ0g+6m5KWmZ
=Chnh
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] blob c/c++ sample code

[Wayne Anderson]

Hello,

I'm converting an old application file format to use SQLite as a back  
end.  For several sections of the file data I will need to use blobs.   
I'm unclear about several things, but chief among them is how to set  
the initial size of a blob.  The documentation indicates that you need  
to use the "UPDATE SQL Command" to change the size of a blob but I  
have no idea how to do this from within a C/C++ program.

Can anyone point me to some sample code?


Thanks,

Wayne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange crashes

[Woltan]

Heyas,
I have a strange problem, which I hope you can help me with. Here we go:

I am building a C# wrapper for SQLite. I just want to make a simple query
like: SELECT * FROM TableA and this is what I do:
1. sqlite3_open
2. sqlite3_prepare_v2
3. sqlite3_column_name (to get the names of the columns of the query)

and I dont get no further. This is the point where SQLite crashes. The same
thing happens when I try to call sqlite3_sql.  However,
sqlite3_column_count, sqlite3_step and sqlite3_column_type seem to be
working just fine (if those were the commands at point 3).

To make this problem even a little harder:
I have to projects, that access the very same wrapper!! One project is a
simple form application, and the other is a Visual Studio Add-In Project.
When I call my wrapper with my from application everything works exactly how
it is supposed to. Only the Add-In project crashes as described above!

Does any of you have an idea, or guess what might be going wrong?
Any tipp, suggestion..., is also deeply appreciated!!

thx in advance
cherio Woltan
(sorry for my bad english!^^)
-- 
View this message in context: 
http://www.nabble.com/Strange-crashes-tp24417739p24417739.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Ken]

Batching the orders and writing more data as one transaction will certainly 
yield better throughput, but at the risk of some data loss until the data is 
committed to disk. It sounds like you are building some type of OLTP/ 
Transaction logging system. 

Another good idea here is to also implement or at least think about some form 
of an archiving system. For instance not only would you have one db per thread 
(hashed) but maybe daily you switch to a brand new database file set. Maybe a 
naming convention such as MMDDYY_HashID.db would also be useful?



--- On Thu, 7/9/09, Rizzuto, Raymond  wrote:

> From: Rizzuto, Raymond 
> Subject: Re: [sqlite] multi-thread access to a db
> To: "General Discussion of SQLite Database" 
> Date: Thursday, July 9, 2009, 12:28 PM
> Right now I index off the order
> id.  I can look into options for indexing - you are
> correct that it is more likely that I'll need to read an
> order I recently wrote than one that is older. 
> However, since reading is ~.2% of the accesses to the db,
> all db work accounts for 2% of the cpu usage, it may not be
> worth optimizing in that area.
> 
> In my system, order codes are unique, and orders go to a
> specific thread based on a hash of that id.  Therefore,
> the only thing I need to do is create a unique db file,
> probably based on the thread id, and each thread's logic for
> writing/reading is unchanged - just which db is different.
> 
> Currently I archive orders individually, at the time I
> determine the order shouldn't be needed.  I could add
> logic to do that in a batch in the future, but that would
> require some extensive changes elsewhere in the logic, so
> I'll try some of the other suggestions first.
> 
> -Original Message-
> From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
> Sent: Thursday, July 09, 2009 1:08 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] multi-thread access to a db
> 
> 
> On 9 Jul 2009, at 2:58pm, Rizzuto, Raymond wrote:
> 
> > I have 4 servers, all with 4 cores.  This is to
> handle a volume of
> > 10-20 million orders per day.
> >
> > Most of the work load (~90%) is unrelated to the
> database.  In fact,
> > I added the database just to allow me to offload
> orders out of
> > memory when they look done so that the app doesn't run
> out of
> > memory.  It is a 32-bit app, so it typically dies
> when it hits ~2.5
> > gig.  Moving to a 64-bit app was not an option.
> 
> Okay, you're way ahead of most of my recommendations and
> obviously
> understand what you're doing.
> 
> > In approx .2% of the orders, I guess wrong, and have
> to read the
> > order back in from the db to process additional
> activity.  I could
> > remove indices to improve write performance, however
> the hit on read
> > performance might outweigh the gain.  Since the
> processing is
> > supposed to be in near-realtime, the delay in that
> case might be
> > excessive.  Still, it may be worth trying that.
> 
> Since it takes very little extra coding to test the effect
> on speed it
> might be worth experimenting with unusual index
> methods.  If you read
> the database only for two operations, both of which need
> all records
> to do with a particular order, it might be worth
> experimenting with
> having no primary key, just one index on the 'order number'
> column.
> One DBMS I used to use was famously faster if you used
> 'DESC' in
> indices, since records you were normally looking for were
> more likely
> to be recent records than extremely old ones, and the
> search algorithm
> worked faster that way.
> 
> > I am using begin/commit around the writes since each
> order can
> > require between 1 and 5 writes.
> 
> If you're writing orders in big batches, and if your
> previous
> statements about crash-recovery are true, then it might be
> worth
> putting begin/commit just around each batch of orders
> instead of each
> individual order.  You might want to take it even
> further: by analogy
> with a non-SQL DBMS, I once wrote a logging program which
> did a COMMIT
> only just before a SELECT was needed, or when the logging
> program was
> quit.  However, I don't know how SQLite acts if you
> have thousands of
> uncommitted changes: it might get slower if you have that
> many.
> 
> > Ken made a suggestion that I create separate db's for
> each thread.
> > Since the threads don't share data, that approach
> would work for me,
> > and eliminate all contention.  Each db would have
> the same schema,
> > of course.
> 
> If you don't share data, that might be good.  You'd
> need to work out a
> a system for order codes, of course, so that you can work
> out which
> dataset a particular order is in.
> 
> Simon.
> 
> 
> IMPORTANT: The information contained in this email and/or
> its attachments is confidential. If you are not the intended
> recipient, please notify the sender immediately by reply and
> immediately delete this message and all its attachments. Any
> review, use, reproduction, disclosure o

Re: [sqlite] SQL Syntax

[Rick Ratchford]

Cheers!
 
Rick Ratchford
ProfitMax Trading Inc. 
http://www.amazingaccuracy.com
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Thursday, July 09, 2009 1:15 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] SQL Syntax
#>
#>Rick Ratchford 
#>wrote:
#>> select
#>>(select min(Year) ...) as firstFullYear,
#>>(select max(Year) ...) as lastFullYear;
#>>
#>> If I go with the condensed version you illustrate above, I 
#>assume that 
#>> I would then need to use the LIMIT clause, right? When I 
#>tried it, I 
#>> get a bunch of rows returned, all with the same information.
#>>
#>> 1988  2008
#>> 1988  2008
#>> 1988  2008
#>> 1988  2008
#>> 1988  2008
#>> 1988  2008
#>> ...
#>>
#>> Here is the SQL used:
#>>
#>> Select (Select min(Year) From TmpTable Where Month=1 and Day<8) as 
#>> FirstYear,
#>>   (Select max(Year) From TmpTable Where Month=12 and Day>24) as 
#>> LastYear FROM TmpTable"
#>
#>Do you see a FROM clause in my example?
#>
#>Igor Tandetnik 
#>


select
   (select min(Year) ...) as firstFullYear,
   (select max(Year) ...) as lastFullYear;

When I saw the '...', I assumed this was just a partial example and that I
was to fill in the rest. Because I'm still a real newbie at this, I didn't
realize that a 'select' does not always need to be followed by a table name.
I feel like a real dummy now. :-&

Thanks again.

Rick




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Noah Hart]

>How do you plan to statically link your new library into multiple
projects? 
>Are you planning to make a copy of the code files for each C# project
that uses Sqlite? 

I use the "Add as Link". 

Again my goal was not to create a reusable library, but rather as a
programming challenge to learn C#


>I don't think you'll be able to do this for assemblies written in any
other language, 
>e.g. VB.NET. In fact, people using these languages are basically locked
out of your library, 
>at least without a wrapper to make it a DLL (I think).

Correct, those who want to use it in other languages, can use existing
dlls, which work just fine.  
For example, the  SQLite ADO.NET Provider from sqlite.phxsoftware.com is
an excellent solution

>Also, this approach (making copies of the code) introduces parallel
maintenance issues, esp. 
True

>There is also the "Add as Link" feature, which is similar to #include
in C++. 
I had to recreate TCL for C# as well in order to run the test harness;
So I use the Add as Link for both the test harness as well as the shell
application


>I don't mean to defy your disclaimer; these are topics of some
practical importance to me. 
>I deal with C++ / Sqlite apps daily that may eventually need to be
(rapidly) transitioned to C#. 
>My growing sentiment is that .NET does not really lend itself to
this... I guess I want "#include" from C++.

You can call SQLite from C# by using existing wrappers.  Again, my goal
was to learn OOP using something I could use.

>Another area I miss "#include" is in declaring simple constants; 

YUP -- this was a real challenge in the porting.

Regards,

Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax

[Igor Tandetnik]

Rick Ratchford 
wrote:
> select
>(select min(Year) ...) as firstFullYear,
>(select max(Year) ...) as lastFullYear;
>
> If I go with the condensed version you illustrate above, I assume
> that I would then need to use the LIMIT clause, right? When I tried
> it, I get a bunch of rows returned, all with the same information.
>
> 1988  2008
> 1988  2008
> 1988  2008
> 1988  2008
> 1988  2008
> 1988  2008
> ...
>
> Here is the SQL used:
>
> Select (Select min(Year) From TmpTable Where Month=1 and Day<8) as
> FirstYear,
>   (Select max(Year) From TmpTable Where Month=12 and Day>24) as
> LastYear FROM TmpTable"

Do you see a FROM clause in my example?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[D. Richard Hipp]

On Jul 9, 2009, at 12:09 PM, Noah Hart wrote:

>
> BACKGROUND:
> In order to learn C#, I have spent the last year converting the  
> source code
> of SQLite3 from C to C#. As of version 3.6.16, it is now ready to  
> release in
> the wild. I don't want to self-host CVS or some other repository, so  
> I am
> trying to decide where to post the code.
>
> My goals for this are simple. I just want to allow people to  
> download the
> source code, submit feedback, bug reports, etc.
>
> I have looked at sourceforge, googlecode, codeproject and a few  
> others.
> However, while they all look fine, I not sure what works well in the  
> real
> world.
>
> Any suggestions from your personal experience would be appreciated.

If you can convince a few prominent contributors to this mailing list  
(Igor Tandetnik, Roger Binns, members of the SQLite.org staff, etc.)  
to say this is a good project, then I will create a subdomain under  
sqlite.org (ex: csharp.sqlite.org) and set you up with a website with  
a fossil configuration management system on which to host your  
project.  See http://www.fossil-scm.org/ for more information on  
fossil.  Fossil is self-hosting, btw.

The above offer applies to any open-source project related to SQLite.


>
> DISCLAIMER:
> This port was done simply to learn C#, and then embed SQLite3 into a  
> C#
> application without the need for a dll.  Please no criticism for  
> doing this,
> or a discussion of if it was advisable to port SQLite3 to C# in the  
> first
> place.
>
> Also, I know this is off-topic, but I am really interested in your
> suggestions.  If you want to flame me, please reply directly, and  
> not to the
> list.
>
> Regards,
>
> Noah Hart
>
> -- 
> View this message in context: 
> http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fetch whole result set to memory

[Igor Tandetnik]

Eberhard, Markus (external)
 wrote:
> I'm using SQLite in my application and I would like to fetch the whole
> result set of a select statement to memory.
> I can't use sqlite3_get_table since it doesn't support BLOBs as far
> as I know.
> Currently I'm using function sqlite3_step to itterate through the
> result set; that function calls winRead (->ReadFile) which consumes
> between 60% and 70% of the application runtime (thousands of winRead
> calls). That is why I'm searching for a possibility to fetch the
> whole result set of a select statement to memory. Is there any
> possibility to do that?

Let me get this straight. The data is originally in a file on disk. You 
want it in memory. But you don't want the application to make any 
ReadFile calls. How precisely do you expect the bits to get from here to 
there?

sqlite3_get_table is implemented simply by looping with sqlite3_step - 
look at its source code. You can do the same, and save each row in some 
in-memory data structure. I don't see though how you expect any of this 
to reduce disk activity.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Beau Wilkinson]

I wholeheartedly agree about the importance of source control, even though my 
specific advice didn't really involve that.

To the OP in particular:
How do you plan to statically link your new library into multiple projects? Are 
you planning to make a copy of the code files for each C# project that uses 
Sqlite? I don't think you'll be able to do this for assemblies written in any 
other language, e.g. VB.NET. In fact, people using these languages are 
basically locked out of your library, at least without a wrapper to make it a 
DLL (I think).

Also, this approach (making copies of the code) introduces parallel maintenance 
issues, esp. if you end up with multiple copies of the library on your own 
computer. I guess the existence of parallel copies is acceptable - even 
desirable - under some circumstances, I just wonder if you've considered these 
issues. There is also the "Add as Link" feature, which is similar to #include 
in C++. I have never worked through all of the issues re. source control and 
"Add as Link," although I suspect this can be made to work.

I don't mean to defy your disclaimer; these are topics of some practical 
importance to me. I deal with C++ / Sqlite apps daily that may eventually need 
to be (rapidly) transitioned to C#. My growing sentiment is that .NET does not 
really lend itself to this... I guess I want "#include" from C++.

Another area I miss "#include" is in declaring simple constants; should two 
different assemblies that, say, both need to know that WAKEUP_STRING means 
"~~~WTFU~~~" really have to use some kind of DLL or IPC mechanism at runtime to 
ascertain this fact? Or should they have parallel copies of, say, Contants.cs? 
Or should we use "Add as Link" and kludge around source control? I wonder if 
Microsoft really thought these things through, especially since it is there 
source control tool that so greatly mishandles "Add as Link."

Maybe you or someone else can dispel my confusion on these points. Someone once 
suggested to me that the best answer is to select "~~~WTFU~~~" (or whatever) 
from a "constants" table in a database. That seems like an arbitrary and 
unwelcome requirement, although if this really must be done, Sqlite might be 
the way to go.




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Roger Binns [rog...@rogerbinns.com]
Sent: Thursday, July 09, 2009 12:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Pros and cons of various online code sharing sites

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Noah Hart wrote:
> I have looked at sourceforge, googlecode, codeproject and a few others.
> However, while they all look fine, I not sure what works well in the real
> world.

TL;DR: Being familiar with DVCS is an important developer skill these
days. Concentrate on that.  I'd recommend Google Code, BitBucket and
then SF.

I can't speak for codeproject but can for the others.  Google code only
allows a small subset of licenses (a good thing) but doesn't include
public domain which is what SQLite itself is.  Google's mailing lists
are the best.  SF has horrendous mailing lists and web interface.  SF
has over the last year or so added every bell and whistle imaginable
(wikis, bug trackers, trac etc).

What I would recommend is that you also take this as an opportunity to
learn how to use a DVCS.  The main players are git, mercurial and bzr
and you'll get adherents coming out of the woodwork for each, but they
are all far more similar than they are different.  Unless you love all
things Ubuntu, I'd recommend starting with mercurial and once
comfortable with it try out git to get a different perspective.  SF and
GoogleCode both support mercurial, and there is a dedicated mercurial
based hoster at http://www.bitbucket.org

 http://hgbook.red-bean.com/

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpWKQIACgkQmOOfHg372QRgMQCbBGWsCD0t2GwotjeWaPJOzcTA
LykAniJas2Yy/iemeVyuma6kxRwrfui2
=kExD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Noah Hart wrote:
> I have looked at sourceforge, googlecode, codeproject and a few others.
> However, while they all look fine, I not sure what works well in the real
> world.

TL;DR: Being familiar with DVCS is an important developer skill these
days. Concentrate on that.  I'd recommend Google Code, BitBucket and
then SF.

I can't speak for codeproject but can for the others.  Google code only
allows a small subset of licenses (a good thing) but doesn't include
public domain which is what SQLite itself is.  Google's mailing lists
are the best.  SF has horrendous mailing lists and web interface.  SF
has over the last year or so added every bell and whistle imaginable
(wikis, bug trackers, trac etc).

What I would recommend is that you also take this as an opportunity to
learn how to use a DVCS.  The main players are git, mercurial and bzr
and you'll get adherents coming out of the woodwork for each, but they
are all far more similar than they are different.  Unless you love all
things Ubuntu, I'd recommend starting with mercurial and once
comfortable with it try out git to get a different perspective.  SF and
GoogleCode both support mercurial, and there is a dedicated mercurial
based hoster at http://www.bitbucket.org

 http://hgbook.red-bean.com/

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpWKQIACgkQmOOfHg372QRgMQCbBGWsCD0t2GwotjeWaPJOzcTA
LykAniJas2Yy/iemeVyuma6kxRwrfui2
=kExD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Rizzuto, Raymond]

Right now I index off the order id.  I can look into options for indexing - you 
are correct that it is more likely that I'll need to read an order I recently 
wrote than one that is older.  However, since reading is ~.2% of the accesses 
to the db, all db work accounts for 2% of the cpu usage, it may not be worth 
optimizing in that area.

In my system, order codes are unique, and orders go to a specific thread based 
on a hash of that id.  Therefore, the only thing I need to do is create a 
unique db file, probably based on the thread id, and each thread's logic for 
writing/reading is unchanged - just which db is different.

Currently I archive orders individually, at the time I determine the order 
shouldn't be needed.  I could add logic to do that in a batch in the future, 
but that would require some extensive changes elsewhere in the logic, so I'll 
try some of the other suggestions first.

-Original Message-
From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
Sent: Thursday, July 09, 2009 1:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] multi-thread access to a db


On 9 Jul 2009, at 2:58pm, Rizzuto, Raymond wrote:

> I have 4 servers, all with 4 cores.  This is to handle a volume of
> 10-20 million orders per day.
>
> Most of the work load (~90%) is unrelated to the database.  In fact,
> I added the database just to allow me to offload orders out of
> memory when they look done so that the app doesn't run out of
> memory.  It is a 32-bit app, so it typically dies when it hits ~2.5
> gig.  Moving to a 64-bit app was not an option.

Okay, you're way ahead of most of my recommendations and obviously
understand what you're doing.

> In approx .2% of the orders, I guess wrong, and have to read the
> order back in from the db to process additional activity.  I could
> remove indices to improve write performance, however the hit on read
> performance might outweigh the gain.  Since the processing is
> supposed to be in near-realtime, the delay in that case might be
> excessive.  Still, it may be worth trying that.

Since it takes very little extra coding to test the effect on speed it
might be worth experimenting with unusual index methods.  If you read
the database only for two operations, both of which need all records
to do with a particular order, it might be worth experimenting with
having no primary key, just one index on the 'order number' column.
One DBMS I used to use was famously faster if you used 'DESC' in
indices, since records you were normally looking for were more likely
to be recent records than extremely old ones, and the search algorithm
worked faster that way.

> I am using begin/commit around the writes since each order can
> require between 1 and 5 writes.

If you're writing orders in big batches, and if your previous
statements about crash-recovery are true, then it might be worth
putting begin/commit just around each batch of orders instead of each
individual order.  You might want to take it even further: by analogy
with a non-SQL DBMS, I once wrote a logging program which did a COMMIT
only just before a SELECT was needed, or when the logging program was
quit.  However, I don't know how SQLite acts if you have thousands of
uncommitted changes: it might get slower if you have that many.

> Ken made a suggestion that I create separate db's for each thread.
> Since the threads don't share data, that approach would work for me,
> and eliminate all contention.  Each db would have the same schema,
> of course.

If you don't share data, that might be good.  You'd need to work out a
a system for order codes, of course, so that you can work out which
dataset a particular order is in.

Simon.


IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Viewer Tool

[Simon Slavin]

On 8 Jul 2009, at 7:20pm, TSGames wrote:

> I have developed a gui-based tool to create, edit and view SQLite  
> databases.

Just a note that this is a Windows 2000/XP/Vista/7 product only.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Simon Slavin]

On 9 Jul 2009, at 2:58pm, Rizzuto, Raymond wrote:

> I have 4 servers, all with 4 cores.  This is to handle a volume of  
> 10-20 million orders per day.
>
> Most of the work load (~90%) is unrelated to the database.  In fact,  
> I added the database just to allow me to offload orders out of  
> memory when they look done so that the app doesn't run out of  
> memory.  It is a 32-bit app, so it typically dies when it hits ~2.5  
> gig.  Moving to a 64-bit app was not an option.

Okay, you're way ahead of most of my recommendations and obviously  
understand what you're doing.

> In approx .2% of the orders, I guess wrong, and have to read the  
> order back in from the db to process additional activity.  I could  
> remove indices to improve write performance, however the hit on read  
> performance might outweigh the gain.  Since the processing is  
> supposed to be in near-realtime, the delay in that case might be  
> excessive.  Still, it may be worth trying that.

Since it takes very little extra coding to test the effect on speed it  
might be worth experimenting with unusual index methods.  If you read  
the database only for two operations, both of which need all records  
to do with a particular order, it might be worth experimenting with  
having no primary key, just one index on the 'order number' column.   
One DBMS I used to use was famously faster if you used 'DESC' in  
indices, since records you were normally looking for were more likely  
to be recent records than extremely old ones, and the search algorithm  
worked faster that way.

> I am using begin/commit around the writes since each order can  
> require between 1 and 5 writes.

If you're writing orders in big batches, and if your previous  
statements about crash-recovery are true, then it might be worth  
putting begin/commit just around each batch of orders instead of each  
individual order.  You might want to take it even further: by analogy  
with a non-SQL DBMS, I once wrote a logging program which did a COMMIT  
only just before a SELECT was needed, or when the logging program was  
quit.  However, I don't know how SQLite acts if you have thousands of  
uncommitted changes: it might get slower if you have that many.

> Ken made a suggestion that I create separate db's for each thread.   
> Since the threads don't share data, that approach would work for me,  
> and eliminate all contention.  Each db would have the same schema,  
> of course.

If you don't share data, that might be good.  You'd need to work out a  
a system for order codes, of course, so that you can work out which  
dataset a particular order is in.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Rizzuto, Raymond]

I did some measurements and calculations, and writing to the db accounts for 2% 
of the processing in my system.  Of course, that low percentage is due to the 
high transaction rate I got with asynch+exclusive.

With 5 threads, I compute that contention would occur 20% of the time.  That 
isn't too horrendous, but I think splitting the db to a per thread db makes 
sense so that I can scale to more threads.  We'll probably migrate to machines 
with 8 cores, and increase the number of threads, which will make the 
contention unacceptable.

-Original Message-
From: Rizzuto, Raymond
Sent: Thursday, July 09, 2009 12:44 PM
To: 'kennethinbox-sql...@yahoo.com'; 'General Discussion of SQLite Database'
Subject: RE: [sqlite] multi-thread access to a db

Each thread has its own data.  I.e. a thread only ever needs to read data that 
it wrote.

I considered using the asynch io module described at 
http://www.sqlite.org/asyncvfs.html so that I could have a worker thread, but 
was dissuaded by the author, who suggested the asynch pragma instead.

The worker thread that decides to move an order from memory to db might in the 
very next instance find it needs to restore the order to memory.  There would 
need to be some interlock to ensure that the data was not deleted till it was 
stored, or that it could be retrieved from the db thread's work queue.  I 
believe the asynch io moduel supported that.

At this point, I am relatively happy with the performance I get when I have 
both asynch and exclusive selected.  In a simple (non-multithreaded) test, I 
was able to write 10,000 records of 1k length each in 1 second.

My main concern is to make sure that I code my multithreaded application to 
work correctly with sqlite in the asynch+exclusive mode, and that I don't do 
anything to degrade the performance from what I saw in my simple test.

Ray

-Original Message-
From: Ken [mailto:kennethinbox-sql...@yahoo.com]
Sent: Thursday, July 09, 2009 10:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] multi-thread access to a db


Additional considerations:

1. Does the re-reading of data occur cross thread? If so you'll need some way 
to identify the DB that contains the data.

2. Consider using either a disk array or multiple disk drives, one for each db 
file. You probably should do some load testing at volume to determine optimal 
configurations for you write patterns.

3. You indicate that the I/O is minimal. Why not create one thread that handles 
the Database work load. All the other threads could pass the work to the 
DbWorker thread. This would also eliminate DB contention, but would cause 
contention at the OS Mutex/Semaphore layer, which should be faster than disk 
based contention.

Implementation of course could be done via a Shared Memory segment (if unix 
based) and some locking mechanisms.

For a really slick high performance LL implemenation consider using an Unrolled 
Linked List. They are incredibly fast and provide 3-5 times faster performance 
than a simple LL, especially on multicore cpu's with large L1,L2 cache lines.

HTH

--- On Thu, 7/9/09, Rizzuto, Raymond  wrote:

> From: Rizzuto, Raymond 
> Subject: Re: [sqlite] multi-thread access to a db
> To: "General Discussion of SQLite Database" 
> Date: Thursday, July 9, 2009, 8:58 AM
> I have 4 servers, all with 4
> cores.  This is to handle a volume of 10-20 million
> orders per day.
>
> Most of the work load (~90%) is unrelated to the
> database.  In fact, I added the database just to allow
> me to offload orders out of memory when they look done so
> that the app doesn't run out of memory.  It is a 32-bit
> app, so it typically dies when it hits ~2.5 gig.
> Moving to a 64-bit app was not an option.
>
> In approx .2% of the orders, I guess wrong, and have to
> read the order back in from the db to process additional
> activity.  I could remove indices to improve write
> performance, however the hit on read performance might
> outweigh the gain.  Since the processing is supposed to
> be in near-realtime, the delay in that case might be
> excessive.  Still, it may be worth trying that.
>
> I am using begin/commit around the writes since each order
> can require between 1 and 5 writes.
>
> Ken made a suggestion that I create separate db's for each
> thread.  Since the threads don't share data, that
> approach would work for me, and eliminate all
> contention.  Each db would have the same schema, of
> course.
>
> Ray
>
> -Original Message-
> From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
> Sent: Wednesday, July 08, 2009 5:12 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] multi-thread access to a db
>
>
> On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote:
>
> > If I remove the locking_mode=exclusive, I don't get
> those errors.
> >
> > I'd appreciate any advice on how I can get the best
> performance
> > using multiple threads in my application, given that:
> >
> >
> > 1.  I need maximum per

Re: [sqlite] multi-thread access to a db

[Rizzuto, Raymond]

Each thread has its own data.  I.e. a thread only ever needs to read data that 
it wrote.

I considered using the asynch io module described at 
http://www.sqlite.org/asyncvfs.html so that I could have a worker thread, but 
was dissuaded by the author, who suggested the asynch pragma instead.

The worker thread that decides to move an order from memory to db might in the 
very next instance find it needs to restore the order to memory.  There would 
need to be some interlock to ensure that the data was not deleted till it was 
stored, or that it could be retrieved from the db thread's work queue.  I 
believe the asynch io moduel supported that.

At this point, I am relatively happy with the performance I get when I have 
both asynch and exclusive selected.  In a simple (non-multithreaded) test, I 
was able to write 10,000 records of 1k length each in 1 second.

My main concern is to make sure that I code my multithreaded application to 
work correctly with sqlite in the asynch+exclusive mode, and that I don't do 
anything to degrade the performance from what I saw in my simple test.

Ray

-Original Message-
From: Ken [mailto:kennethinbox-sql...@yahoo.com]
Sent: Thursday, July 09, 2009 10:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] multi-thread access to a db


Additional considerations:

1. Does the re-reading of data occur cross thread? If so you'll need some way 
to identify the DB that contains the data.

2. Consider using either a disk array or multiple disk drives, one for each db 
file. You probably should do some load testing at volume to determine optimal 
configurations for you write patterns.

3. You indicate that the I/O is minimal. Why not create one thread that handles 
the Database work load. All the other threads could pass the work to the 
DbWorker thread. This would also eliminate DB contention, but would cause 
contention at the OS Mutex/Semaphore layer, which should be faster than disk 
based contention.

Implementation of course could be done via a Shared Memory segment (if unix 
based) and some locking mechanisms.

For a really slick high performance LL implemenation consider using an Unrolled 
Linked List. They are incredibly fast and provide 3-5 times faster performance 
than a simple LL, especially on multicore cpu's with large L1,L2 cache lines.

HTH

--- On Thu, 7/9/09, Rizzuto, Raymond  wrote:

> From: Rizzuto, Raymond 
> Subject: Re: [sqlite] multi-thread access to a db
> To: "General Discussion of SQLite Database" 
> Date: Thursday, July 9, 2009, 8:58 AM
> I have 4 servers, all with 4
> cores.  This is to handle a volume of 10-20 million
> orders per day.
>
> Most of the work load (~90%) is unrelated to the
> database.  In fact, I added the database just to allow
> me to offload orders out of memory when they look done so
> that the app doesn't run out of memory.  It is a 32-bit
> app, so it typically dies when it hits ~2.5 gig.
> Moving to a 64-bit app was not an option.
>
> In approx .2% of the orders, I guess wrong, and have to
> read the order back in from the db to process additional
> activity.  I could remove indices to improve write
> performance, however the hit on read performance might
> outweigh the gain.  Since the processing is supposed to
> be in near-realtime, the delay in that case might be
> excessive.  Still, it may be worth trying that.
>
> I am using begin/commit around the writes since each order
> can require between 1 and 5 writes.
>
> Ken made a suggestion that I create separate db's for each
> thread.  Since the threads don't share data, that
> approach would work for me, and eliminate all
> contention.  Each db would have the same schema, of
> course.
>
> Ray
>
> -Original Message-
> From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
> Sent: Wednesday, July 08, 2009 5:12 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] multi-thread access to a db
>
>
> On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote:
>
> > If I remove the locking_mode=exclusive, I don't get
> those errors.
> >
> > I'd appreciate any advice on how I can get the best
> performance
> > using multiple threads in my application, given that:
> >
> >
> > 1.  I need maximum performance.
>
> Spend at least five grand on a fast water-cooled box.
> Use a version
> of Unix/Linux compiled without support for anything you
> don't need,
> like printing.  Create your database file on a RAM
> disk.  Write your
> application as a command-line app, and don't run the GUI.
>
> > That is also why I need multiple threads
>
> May not help if they're all constantly accessing the
> database.  In
> fact contention for access can slow the process down: you
> have seven
> threads, five of which are perpetually blocked.
> There's no one-size-
> fits-all solution to fast database access, it depends on
> the patterns
> of when data is available for writing, and how important
> the order the
> data was available is when you read.  Sometimes you
> p

Re: [sqlite] Pros and cons of various online code sharing sites

[Beau Wilkinson]

By the way, Noah, I think share your feelings about DLL-free static linking in 
.NET. It ought to be easier to statically link to libraries in .NET, e.g. to 
create a truly standalone .EXE. This oversight is one of the main reasons I now 
avoid .NET.

In particular, C# doesn't have "#include" or anything like it, except that 
Visual Studio does provide an "Add as Link" option for existing files which 
statically links to them. The "link" gets converted into a project-specific 
copy if you add the project to SourceSafe, though. This happens silently, even 
though it introduces a very undesirable parallel maintenance burden!

-
Beau Wilkinson
Software Development Engineer, DP
MARINE TECHNOLOGIES, LLC
985-612-1313 (office)
x52913 (ECO phone)
985-705-5203 (cell)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Noah Hart [n...@lipmantpa.com]
Sent: Thursday, July 09, 2009 11:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite]  Pros and cons of various online code sharing sites

BACKGROUND:
In order to learn C#, I have spent the last year converting the source code
of SQLite3 from C to C#. As of version 3.6.16, it is now ready to release in
the wild. I don't want to self-host CVS or some other repository, so I am
trying to decide where to post the code.

My goals for this are simple. I just want to allow people to download the
source code, submit feedback, bug reports, etc.

I have looked at sourceforge, googlecode, codeproject and a few others.
However, while they all look fine, I not sure what works well in the real
world.

Any suggestions from your personal experience would be appreciated.

DISCLAIMER:
This port was done simply to learn C#, and then embed SQLite3 into a C#
application without the need for a dll.  Please no criticism for doing this,
or a discussion of if it was advisable to port SQLite3 to C# in the first
place.

Also, I know this is off-topic, but I am really interested in your
suggestions.  If you want to flame me, please reply directly, and not to the
list.

Regards,

Noah Hart

--
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax

[Rick Ratchford]

Rick Ratchford wrote:
> Okay, this worked, but I have NO IDEA why.
>
>SQLString = "SELECT min(Year) FROM TmpTable " & _
>"WHERE Month=1 UNION " & _
>"SELECT max(Year) FROM TmpTable " & _
>"WHERE Month = 12 LIMIT 2"

Read about aggregate functions (min() is one of those) in your favorite SQL
textbook, then drop the LIMIT clause. You may also want to use UNION ALL
instead of UNION: otherwise, if you just have one full year's worth of data,
you'll get a single row as UNION removes duplicates.

In fact, it may be more convenient (definitely easier to reuse as a fragment
in more complicated queries) to return this result as one row with two
columns:

select
(select min(Year) ...) as firstFullYear,
(select max(Year) ...) as lastFullYear;

Igor Tandetnik 



Igor,

If I go with the condensed version you illustrate above, I assume that I
would then need to use the LIMIT clause, right? When I tried it, I get a
bunch of rows returned, all with the same information.

1988  2008
1988  2008
1988  2008
1988  2008
1988  2008
1988  2008
...

Here is the SQL used:

Select (Select min(Year) From TmpTable Where Month=1 and Day<8) as
FirstYear,
   (Select max(Year) From TmpTable Where Month=12 and Day>24) as
LastYear FROM TmpTable"


LIMIT 1 ??

Thanks.
Rick



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Beau Wilkinson]

In my personal experience Code Project is the most useful, especially if you 
work heavily with C#.

I have worked with SourceForge and my personal opinion is that it's a bit too 
UNIX-oriented for my taste. I love UNIX, and use (for example) Cygwin and MinGW 
very extensively for development work. But for mundane chores like browsing the 
Web, learning about a project, downloading and decompressing its code, etc. the 
reality of 2009 is that I'm working in Windows and CodeProject seems to respect 
that more than SourceForge.

And I will respect your disclaimer, but I am having to grab my arm a la Dr. 
Strangelove to do so. What's done is done.
-
Beau Wilkinson
Software Development Engineer, DP
MARINE TECHNOLOGIES, LLC
985-612-1313 (office)
x52913 (ECO phone)
985-705-5203 (cell)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Noah Hart [n...@lipmantpa.com]
Sent: Thursday, July 09, 2009 11:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite]  Pros and cons of various online code sharing sites

BACKGROUND:
In order to learn C#, I have spent the last year converting the source code
of SQLite3 from C to C#. As of version 3.6.16, it is now ready to release in
the wild. I don't want to self-host CVS or some other repository, so I am
trying to decide where to post the code.

My goals for this are simple. I just want to allow people to download the
source code, submit feedback, bug reports, etc.

I have looked at sourceforge, googlecode, codeproject and a few others.
However, while they all look fine, I not sure what works well in the real
world.

Any suggestions from your personal experience would be appreciated.

DISCLAIMER:
This port was done simply to learn C#, and then embed SQLite3 into a C#
application without the need for a dll.  Please no criticism for doing this,
or a discussion of if it was advisable to port SQLite3 to C# in the first
place.

Also, I know this is off-topic, but I am really interested in your
suggestions.  If you want to flame me, please reply directly, and not to the
list.

Regards,

Noah Hart

--
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pros and cons of various online code sharing sites

[Noah Hart]

BACKGROUND:
In order to learn C#, I have spent the last year converting the source code
of SQLite3 from C to C#. As of version 3.6.16, it is now ready to release in
the wild. I don't want to self-host CVS or some other repository, so I am
trying to decide where to post the code.

My goals for this are simple. I just want to allow people to download the
source code, submit feedback, bug reports, etc.

I have looked at sourceforge, googlecode, codeproject and a few others.
However, while they all look fine, I not sure what works well in the real
world.

Any suggestions from your personal experience would be appreciated.

DISCLAIMER:  
This port was done simply to learn C#, and then embed SQLite3 into a C#
application without the need for a dll.  Please no criticism for doing this,
or a discussion of if it was advisable to port SQLite3 to C# in the first
place.

Also, I know this is off-topic, but I am really interested in your
suggestions.  If you want to flame me, please reply directly, and not to the
list.

Regards,

Noah Hart

-- 
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fetch whole result set to memory

[Eberhard, Markus (external)]

Hi,
I'm using SQLite in my application and I would like to fetch the whole
result set of a select statement to memory. 
I can't use sqlite3_get_table since it doesn't support BLOBs as far as I
know.
Currently I'm using function sqlite3_step to itterate through the result
set; that function calls winRead (->ReadFile) which consumes between 60%
and 70% of the application runtime (thousands of winRead calls). That is
why I'm searching for a possibility to fetch the whole result set of a
select statement to memory. Is there any possibility to do that?
 
Thanks in advance,
Markus

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?

[Pavel Ivanov]

Why do you re-post your code as if it's another question not related
to previous one (along with the answer)?

> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?

If SQLite is compiled with SQLITE_THREADSAFE = 1 or 2 then API is
thread-safe (with some limitations when it's 2), if SQLITE_THREADSAFE
= 0 then API is not thread-safe. But in both cases I have a doubt
about signal-safety and also API is not re-entrant for sure except for
some specially designed functions.

Pavel

On Thu, Jul 9, 2009 at 3:52 AM, liubin liu<7101...@sina.com> wrote:
>
> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
>
>
> 
>
>
> #include       // for printf()
> #include      // for signal()
> #include      // for alarm()
> #include      // for system()
> #include     // for pthread_create()
> #include     // for sqlite3_***
>
>
>
> sqlite3 *db = NULL;
>
> int timer_handle_mark1 = 0;
> int timer_handle_mark2 = 9;
>
>
>
> pthread_t trd1;
>
> void trd1_task ( void );
>
>
>
> int sqlite3_helper_create_db (void);
>
> int sqlite3_helper_insert_data (void);
>
> // psf - prepare, step, finalize
> int sqlite3_helper_get_data_psf_from_tb1 ( int id );
>
> void timer_handler()
> {
>    int ret = -1;
>    int i = 9;
>
>    char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
>    char *query_string = NULL;
>
>
>    sqlite3_stmt *p_stmt = NULL;
>
>    timer_handle_mark2 = 0;
>    query_string = sqlite3_mprintf ( query_format2, i%500 );
>    timer_handle_mark1++;
>
>    timer_handle_mark2 = 1;
>    ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
>    timer_handle_mark1++;
>
>    timer_handle_mark2 = 2;
>    ret = sqlite3_step ( p_stmt );
>    timer_handle_mark1++;
>
>    //if ( SQLITE_ROW == ret )
>    //    printf ( "# IN timer_handler(), id: %d, length: %d\n",
> sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
>
>    timer_handle_mark2 = 3;
>    sqlite3_finalize ( p_stmt );
>    timer_handle_mark1++;
>
>
>    alarm(1);
> }
>
> int inittimer()
> {
>    signal ( SIGALRM, timer_handler );
>    alarm(1);
>    return 0;
> }
>
> void trd1_task ( void )
> {
>    sleep (30);
>    printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>    sleep (50);
>    printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
> }
>
>
>
> int main ( void )
> {
>    int ret = -1;
>    int i = 0;
>
>    ret = pthread_create ( &trd1, 0, (void *)trd1_task, 0 );
>
>    ret = sqlite3_open ( "testsignal.db", &db );
>
>    sqlite3_helper_create_db ();
>
>    sqlite3_helper_insert_data ();
>
>    ret = inittimer();
>
>    for ( i=0; ; i++ )
>        ret = sqlite3_helper_get_data_psf_from_tb1 ( i );
>
>    ret = sqlite3_close ( db );
>
>    system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );
>
>    return 0;
> }
>
>
> int sqlite3_helper_create_db (void)
> {
>    int ret = -1;
>    char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
> INTEGER, data CHAR(50));";
>    char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
> INTEGER, data CHAR(50));";
>
>    ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
>    //printf ( "ret: %d\n", ret );
>    ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
>    //printf ( "ret: %d\n", ret );
>    if ( SQLITE_OK == ret )
>        printf ( "# IN main(), create db file, SUCCESS!\n" );
>
>
>    return 0;
> }
>
>
> int sqlite3_helper_insert_data (void)
> {
>    int ret = -1;
>    int i = 0;
>
>    sqlite3_stmt *p_stmt = NULL;
>
>
>    char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
> VALUES (%d, %d, %Q);";
>    char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
> VALUES (%d, %d, %Q);";
>    char *sql = NULL;
>
>
>    ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
>    for ( i=0; i<500; i++ )
>    {
>        sql = sqlite3_mprintf ( sql_format1, i, i%10,
> "datadatadatadatadatadatadatadatadatadatadata" );
>        ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
>        ret = sqlite3_step ( p_stmt );
>        sqlite3_free ( sql );
>        ret = sqlite3_finalize ( p_stmt );
>
>        sql = sqlite3_mprintf ( sql_format2, i, i%10,
> "datadatadatadatadatadatadatadatadatadatadata" );
>        ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
>        ret = sqlite3_step ( p_stmt );
>        sqlite3_free ( sql );
>        ret = sqlite3_finalize ( p_stmt );
>    }
>    ret = sqlite3_exec ( db, "COMMIT", NULL, NULL, NULL );
>    if ( SQLITE_OK == ret )
>        printf ( "# IN main(), insert data, SUCCESS!\n" );
>
>    return 0;
> }
>
>
> int sqlite3_helper_get_data_gt_from_tb1 ( int id )
> {
>    int ret = -1;
>
>
>    char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
>    char *query_string = NULL;
>
>    char **resultp = NULL;
>    int row = 

Re: [sqlite] New Book Available

[Robert Citek]

On Thu, Jul 9, 2009 at 8:50 AM,  wrote:
> On Thu, 9 Jul 2009, Rich Shepard wrote:
>
>>   Rick van der Laans, who wrote the excellent "Introduction to SQL, 4th Ed."
>> (and eariler editions, of course) has just had his new book specific to
>> SQLite published. It is another resource for those who want a detailed
>> explanation of how to get the most from this great tool.
>
> What is the title? I am not finding a new book by van der
> Laans book on Google or Amazon.

Took a bit of googling but eventually found this:

http://www.r20.nl/SQLGuidetoSQLite_V1_1.htm

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Ken]

Additional considerations:

1. Does the re-reading of data occur cross thread? If so you'll need some way 
to identify the DB that contains the data.

2. Consider using either a disk array or multiple disk drives, one for each db 
file. You probably should do some load testing at volume to determine optimal 
configurations for you write patterns. 

3. You indicate that the I/O is minimal. Why not create one thread that handles 
the Database work load. All the other threads could pass the work to the 
DbWorker thread. This would also eliminate DB contention, but would cause 
contention at the OS Mutex/Semaphore layer, which should be faster than disk 
based contention. 

Implementation of course could be done via a Shared Memory segment (if unix 
based) and some locking mechanisms.

For a really slick high performance LL implemenation consider using an Unrolled 
Linked List. They are incredibly fast and provide 3-5 times faster performance 
than a simple LL, especially on multicore cpu's with large L1,L2 cache lines.

HTH

--- On Thu, 7/9/09, Rizzuto, Raymond  wrote:

> From: Rizzuto, Raymond 
> Subject: Re: [sqlite] multi-thread access to a db
> To: "General Discussion of SQLite Database" 
> Date: Thursday, July 9, 2009, 8:58 AM
> I have 4 servers, all with 4
> cores.  This is to handle a volume of 10-20 million
> orders per day.
> 
> Most of the work load (~90%) is unrelated to the
> database.  In fact, I added the database just to allow
> me to offload orders out of memory when they look done so
> that the app doesn't run out of memory.  It is a 32-bit
> app, so it typically dies when it hits ~2.5 gig. 
> Moving to a 64-bit app was not an option.
> 
> In approx .2% of the orders, I guess wrong, and have to
> read the order back in from the db to process additional
> activity.  I could remove indices to improve write
> performance, however the hit on read performance might
> outweigh the gain.  Since the processing is supposed to
> be in near-realtime, the delay in that case might be
> excessive.  Still, it may be worth trying that.
> 
> I am using begin/commit around the writes since each order
> can require between 1 and 5 writes.
> 
> Ken made a suggestion that I create separate db's for each
> thread.  Since the threads don't share data, that
> approach would work for me, and eliminate all
> contention.  Each db would have the same schema, of
> course.
> 
> Ray
> 
> -Original Message-
> From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
> Sent: Wednesday, July 08, 2009 5:12 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] multi-thread access to a db
> 
> 
> On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote:
> 
> > If I remove the locking_mode=exclusive, I don't get
> those errors.
> >
> > I'd appreciate any advice on how I can get the best
> performance
> > using multiple threads in my application, given that:
> >
> >
> > 1.  I need maximum performance.
> 
> Spend at least five grand on a fast water-cooled box. 
> Use a version
> of Unix/Linux compiled without support for anything you
> don't need,
> like printing.  Create your database file on a RAM
> disk.  Write your
> application as a command-line app, and don't run the GUI.
> 
> > That is also why I need multiple threads
> 
> May not help if they're all constantly accessing the
> database.  In
> fact contention for access can slow the process down: you
> have seven
> threads, five of which are perpetually blocked. 
> There's no one-size-
> fits-all solution to fast database access, it depends on
> the patterns
> of when data is available for writing, and how important
> the order the
> data was available is when you read.  Sometimes you
> pile up all your
> data to be written into a text file, and another process
> (on a
> different computer ?!) works through the text file and does
> the writing.
> 
> > 2.  All threads need to write to the same db
> > 3.  No other application needs access to the db
> > 4.  I don't care about durability, just fast
> insert times since
> > reads are much less frequent.
> 
> Use BEGIN TRANSACTION and COMMIT properly.  This may
> be more important
> than multi-threading.  It has a huge result.
> 
> If reads are /really/ rare, it might be worth removing all
> indices on
> your database, and only creating an index just before you
> need to
> read, or even just executing the SELECT without any
> indices.
> 
> Simon.
> 
> 
> IMPORTANT: The information contained in this email and/or
> its attachments is confidential. If you are not the intended
> recipient, please notify the sender immediately by reply and
> immediately delete this message and all its attachments. Any
> review, use, reproduction, disclosure or dissemination of
> this message or any attachment by an unintended recipient is
> strictly prohibited. Neither this message nor any attachment
> is intended as or should be construed as an offer,
> solicitation or recommendation to buy or sell any security
> or other financial inst

Re: [sqlite] SQLITE_OMIT_AUTOVACUUM seems to need small change inbtree.c

[Yan Bertrand]

Hello Pavel & the community,

Thank you for your quick answer.
Actually, it could have been related but it is not.
My problem is on a different set of functions that, indeed, are not found when 
SQLITE_OMIT_AUTOVACUUM is defined. The file I attached earlier solves the 
problem. I don't know if there is a way that the community would like to see if 
the change is worth integrating in some CVS branch?

FYI: I am re-doing it on the latest CVS snapshot I just got earlier today since 
I needed to re-generate parse.c and others. I found out that lemon did not 
exclude lines related to omitted functions for triggers & VTabs in my case, so 
I am also making changes for that. Let me know if you think that these would 
deserve inclusion into offical code, or bear not enough added value.

Best regards,

Yan


-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Pavel Ivanov
Envoyé : jeudi 9 juillet 2009 02:22
À : General Discussion of SQLite Database
Objet : Re: [sqlite] SQLITE_OMIT_AUTOVACUUM seems to need small change inbtree.c

Is this related to your problem?
http://www.sqlite.org/cvstrac/tktview?tn=3940

Pavel

On Wed, Jul 8, 2009 at 4:06 AM, Yan Bertrand wrote:
>                Hello,
>
>
>
> I am compiling SQLite with options to reduce the size, and with hopes to
> reduce the duration of some operations on portable devices. This
> includes the use of the compilation switch SQLITE_OMIT_AUTOVACUUM.
>
> I noticed that btree.c will not compile withmy current combination of
> switches. I changed it to compile and thought that it could be
> interesting for someone to have a look, review and approve it you want
> to keep it for the future ?
>
>
>
> Attached :
>
> -          Btree.c modified to compile
>
> -          Config.h with my current config
>
> -          Vtab.c with lines inverted for the include of the header that
> leads to config.h, and the check for the compilation directive defined
> in config.h  :-)
>
>
>
>
>
> I am new to SQLite and compiling it for the second week. So feel free to
> point me at my mistakes (if any).
>
> Best regards,
>
>
>
>                Yan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Rizzuto, Raymond]

I have 4 servers, all with 4 cores.  This is to handle a volume of 10-20 
million orders per day.

Most of the work load (~90%) is unrelated to the database.  In fact, I added 
the database just to allow me to offload orders out of memory when they look 
done so that the app doesn't run out of memory.  It is a 32-bit app, so it 
typically dies when it hits ~2.5 gig.  Moving to a 64-bit app was not an option.

In approx .2% of the orders, I guess wrong, and have to read the order back in 
from the db to process additional activity.  I could remove indices to improve 
write performance, however the hit on read performance might outweigh the gain. 
 Since the processing is supposed to be in near-realtime, the delay in that 
case might be excessive.  Still, it may be worth trying that.

I am using begin/commit around the writes since each order can require between 
1 and 5 writes.

Ken made a suggestion that I create separate db's for each thread.  Since the 
threads don't share data, that approach would work for me, and eliminate all 
contention.  Each db would have the same schema, of course.

Ray

-Original Message-
From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
Sent: Wednesday, July 08, 2009 5:12 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] multi-thread access to a db


On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote:

> If I remove the locking_mode=exclusive, I don't get those errors.
>
> I'd appreciate any advice on how I can get the best performance
> using multiple threads in my application, given that:
>
>
> 1.  I need maximum performance.

Spend at least five grand on a fast water-cooled box.  Use a version
of Unix/Linux compiled without support for anything you don't need,
like printing.  Create your database file on a RAM disk.  Write your
application as a command-line app, and don't run the GUI.

> That is also why I need multiple threads

May not help if they're all constantly accessing the database.  In
fact contention for access can slow the process down: you have seven
threads, five of which are perpetually blocked.  There's no one-size-
fits-all solution to fast database access, it depends on the patterns
of when data is available for writing, and how important the order the
data was available is when you read.  Sometimes you pile up all your
data to be written into a text file, and another process (on a
different computer ?!) works through the text file and does the writing.

> 2.  All threads need to write to the same db
> 3.  No other application needs access to the db
> 4.  I don't care about durability, just fast insert times since
> reads are much less frequent.

Use BEGIN TRANSACTION and COMMIT properly.  This may be more important
than multi-threading.  It has a huge result.

If reads are /really/ rare, it might be worth removing all indices on
your database, and only creating an index just before you need to
read, or even just executing the SELECT without any indices.

Simon.


IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New Book Available

[cmartin]

On Thu, 9 Jul 2009, Rich Shepard wrote:

>   Rick van der Laans, who wrote the excellent "Introduction to SQL, 4th Ed."
> (and eariler editions, of course) has just had his new book specific to
> SQLite published. It is another resource for those who want a detailed
> explanation of how to get the most from this great tool.

What is the title? I am not finding a new book by van der 
Laans book on Google or Amazon.

Chris

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE is not working in pthread

[Pramoda M. A]

Hi All,

I have used pthread_create(fedora 10)  to insert data into database. But it is 
not working. Only sqlite3_open will execute first time.
Not other things are executed in the thread. What should I do?

Thanks & Regards
Pramoda.M.A
CREST | KPIT Cummins Infosystems Limited | Bengaluru | Cell: +91 91640 57663

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi-thread access to a db

[Rizzuto, Raymond]

Ken,

Yes, I know contention is possible.  My application spends most of its time 
doing non-db operations - it is likely that locking contention isn't currently 
a big factor, but that will change as I increase the # of threads.  Since each 
thread reads and writes its own data, I can definitely use your suggestion of 
multiple databases.

In my testing of performance of 3.6.x, I found that asynch + exclusive mode was 
60x faster than just asynch.  I'm not sure if that is representative, or just 
specific to my environment, but that is a key advantage for my environment.

I did change my app last night to share the db connection between threads, and 
have per-thread prepared statements, and that seemed to work fairly well.  I 
need to do more testing, however.

I think mysql, postgress or oracle would be overkill for this application.  I 
was considering using a flat file initially, but sqlite saves me a lot of 
coding to achieve the same results.

Thanks for the suggestions.

Ray

-Original Message-
From: Ken [mailto:kennethinbox-sql...@yahoo.com]
Sent: Wednesday, July 08, 2009 6:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] multi-thread access to a db


Ray,

Using multiple threads you will have locking contention on the database. Only 
one thread is allowed to write at a time. If you need concurrent writing then 
create multiple databases or maybe look into a different DB platform like 
mysql, postgress or oracle.



--- On Wed, 7/8/09, Rizzuto, Raymond  wrote:

> From: Rizzuto, Raymond 
> Subject: Re: [sqlite] multi-thread access to a db
> To: "sqlite-users@sqlite.org" 
> Date: Wednesday, July 8, 2009, 3:28 PM
> If I remove the
> locking_mode=exclusive, I don't get those errors.
>
> I'd appreciate any advice on how I can get the best
> performance using multiple threads in my application, given
> that:
>
>
>  1.  I need maximum performance.  That is also
> why I need multiple threads
>  2.  All threads need to write to the same db
>  3.  No other application needs access to the db
>  4.  I don't care about durability, just fast insert
> times since reads are much less frequent.
>
> Ray
>
> 
> From: Rizzuto, Raymond
> Sent: Wednesday, July 08, 2009 3:27 PM
> To: 'sqlite-users@sqlite.org'
> Subject: multi-thread access to a db
>
> I have an application where I have 7  threads.
> Each thread opens its own db connection object, but the
> connections are to the same db.  I am seeing sporadic
> insert failures when a thread attempts to insert into the
> db.  sqlite3_errmsg returns this message:
>
> database is locked
>
> I am using sqlite3 version 3.6.1.  I use the following
> two pragmas to get the best insert performance (the db is
> used exclusively by this application, and I don't need to
> have the DB recover after an os crash or power fail):
>
> sqlite3_exec(result->db,
> "pragma synchronous=off;", 0, 0, &zErrMsg);
> sqlite3_exec(result->db,
> "pragma locking_mode=exclusive;", 0, 0, &zErrMsg);
>
> I am using threading mode "multi-thread".
>
> Does mode=exclusive mean that the first thread that opens
> and writes to the db locks out all other threads?
>
> Ray
>
>
> 
> Ray Rizzuto
> raymond.rizz...@sig.com
> Susquehanna International Group
> (610)747-2336 (W)
> (215)776-3780 (C)
>
>
>
> 
> IMPORTANT: The information contained in this email and/or
> its attachments is confidential. If you are not the intended
> recipient, please notify the sender immediately by reply and
> immediately delete this message and all its attachments. Any
> review, use, reproduction, disclosure or dissemination of
> this message or any attachment by an unintended recipient is
> strictly prohibited. Neither this message nor any attachment
> is intended as or should be construed as an offer,
> solicitation or recommendation to buy or sell any security
> or other financial instrument. Neither the sender, his or
> her employer nor any of their respective affiliates makes
> any warranties as to the completeness or accuracy of any of
> the information contained herein or that this message or any
> of its attachments is free of viruses.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her

[sqlite] New Book Available

[Rich Shepard]

   Rick van der Laans, who wrote the excellent "Introduction to SQL, 4th Ed."
(and eariler editions, of course) has just had his new book specific to
SQLite published. It is another resource for those who want a detailed
explanation of how to get the most from this great tool.

   I have no vested interest in the book, but I did review and comment on a
couple of chapters.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax

[Rick Ratchford]

Rick Ratchford wrote:
> Okay, this worked, but I have NO IDEA why.
>
>SQLString = "SELECT min(Year) FROM TmpTable " & _
>"WHERE Month=1 UNION " & _
>"SELECT max(Year) FROM TmpTable " & _
>"WHERE Month = 12 LIMIT 2"

Read about aggregate functions (min() is one of those) in your favorite SQL
textbook, then drop the LIMIT clause. You may also want to use UNION ALL
instead of UNION: otherwise, if you just have one full year's worth of data,
you'll get a single row as UNION removes duplicates.

In fact, it may be more convenient (definitely easier to reuse as a fragment
in more complicated queries) to return this result as one row with two
columns:

select
(select min(Year) ...) as firstFullYear,
(select max(Year) ...) as lastFullYear;

>Igor Tandetnik 
-
SQLString =  "SELECT min(Year) FROM TmpTable " & _
 "WHERE Month=1 And Day<8 " & _
 "UNION ALL " & _
 "SELECT max(Year) FROM TmpTable " & _
 "WHERE Month = 12 And Day>24 "


This should limit it to looking for something in the first week and last
week.

David

-

 Noobie here 

I think the min and max functions by design return only one value.

However, I'm afraid this query might not work in the general case.

For example, if your data starts on january 20, 1988, then this query will
think 1988 is a full year. Ditto if the data ends early in december.

So it seems to me that it works for you by luck, because your data set
doesn't start in january, nor ends in december.

Unless of course if I am totally wrong, which happens often enough.

Jean-Denis Muys

-

In one message, I'd like to thank you all for your help (Igor, David,
Jean-Denis, ...).

Igor, that's a good precaution to use UNION ALL, although in case of my code
this would never happen since it has a minimum 5 years of data requirement
even to run the procedure. Thanks for that pointer as well as the idea to
condense by reply by having both years appear in one row. I've not reached
the point in my 'book' where you group 'select' inside 'select' using
parenthesis. Yours was actually the first time I've seen it. Thanks. :)

David, thanks for your input on the addition of Day <>. :)

Jean-Denis, newbie or not, thanks for your input. I did note this and had
originally placed a Day < 5 for the beginning year and Day > 28 for the last
year. But with all the tweeking I was doing to this, some things dropped,
some added... I'll use David's 'first week, last week' numbers for the
final.

Thanks guys.

Rick







___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error is coming in Linux while openings Databse

[Pramoda M. A]

If I remove sqlite3_get_table API, it is working fine.  Now segfault error is 
coming.
I am running in root only.
Data is inserting properly, but unable to query the the database.

Thanks & Regards
Pramoda.M.A
CREST | KPIT Cummins Infosystems Limited | Bengaluru | Cell: +91 91640 57663

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Martin.Engelschalk
Sent: Thursday, July 09, 2009 4:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Error is coming in Linux while openings Databse

Hi,

Does the user under which the application runs have write permission on 
the Directory the database resides in?
Are you sure that the segfault occurs inside sqlite and not in your own 
code?

Martin

Pramoda M. A schrieb:
> Hi All,
>
>I am working on Fedora 10. Using C interfaces, I am successfully opening 
> database. No error while inserting data
> Into database. But after insertion, if I try to get data it is giving 
> segmentation fault error.
> When I try to open using in-built sqlite3 commands, "Select * from File", is 
> it error as
>
> " SQL error: file is encrypted or is not a database"
>
> Please anybody help.
>
> Thanks & Regards
> Pramoda.M.A
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax

[David Bicking]

On Thu, 2009-07-09 at 10:47 +0200, Jean-Denis Muys wrote:
> On 7/9/09 6:21 , "Rick Ratchford"  wrote:
> 
> >  
> > Okay, this worked, but I have NO IDEA why.
> > 
> > SQLString = "SELECT min(Year) FROM TmpTable " & _
> > "WHERE Month=1 UNION " & _
> > "SELECT max(Year) FROM TmpTable " & _
> > "WHERE Month = 12 LIMIT 2"
> > 
> > 
> > While this returned the correct answers:
> > 
> > 1988
> > 2008
> > 
> > 

> 
> However, I'm afraid this query might not work in the general case.
> 
> For example, if your data starts on january 20, 1988, then this query will
> think 1988 is a full year. Ditto if the data ends early in december.
> 
> So it seems to me that it works for you by luck, because your data set
> doesn't start in january, nor ends in december.
> 

SQLString =  "SELECT min(Year) FROM TmpTable " & _
 "WHERE Month=1 And Day<8 " & _
 "UNION ALL " & _
 "SELECT max(Year) FROM TmpTable " & _
 "WHERE Month = 12 And Day>24 "


This should limit it to looking for something in the first week and last week.

David


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error is coming in Linux while openings Databse

[Martin.Engelschalk]

Hi,

Does the user under which the application runs have write permission on 
the Directory the database resides in?
Are you sure that the segfault occurs inside sqlite and not in your own 
code?

Martin

Pramoda M. A schrieb:
> Hi All,
>
>I am working on Fedora 10. Using C interfaces, I am successfully opening 
> database. No error while inserting data
> Into database. But after insertion, if I try to get data it is giving 
> segmentation fault error.
> When I try to open using in-built sqlite3 commands, "Select * from File", is 
> it error as
>
> " SQL error: file is encrypted or is not a database"
>
> Please anybody help.
>
> Thanks & Regards
> Pramoda.M.A
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error is coming in Linux while openings Databse

[Pramoda M. A]

Hi All,

   I am working on Fedora 10. Using C interfaces, I am successfully opening 
database. No error while inserting data
Into database. But after insertion, if I try to get data it is giving 
segmentation fault error.
When I try to open using in-built sqlite3 commands, "Select * from File", is it 
error as

" SQL error: file is encrypted or is not a database"

Please anybody help.

Thanks & Regards
Pramoda.M.A

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax

[Igor Tandetnik]

Rick Ratchford wrote:
> Okay, this worked, but I have NO IDEA why.
>
>SQLString = "SELECT min(Year) FROM TmpTable " & _
>"WHERE Month=1 UNION " & _
>"SELECT max(Year) FROM TmpTable " & _
>"WHERE Month = 12 LIMIT 2"

Read about aggregate functions (min() is one of those) in your favorite 
SQL textbook, then drop the LIMIT clause. You may also want to use UNION 
ALL instead of UNION: otherwise, if you just have one full year's worth 
of data, you'll get a single row as UNION removes duplicates.

In fact, it may be more convenient (definitely easier to reuse as a 
fragment in more complicated queries) to return this result as one row 
with two columns:

select
(select min(Year) ...) as firstFullYear,
(select max(Year) ...) as lastFullYear;

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what is most effective way to temporarily disable triggers?

[Martin.Engelschalk]

Hi,

if you are talking about a feature request: Oracle supports sql syntax 
to enable or disable a certain trigger (as opposed to all triggers as 
you suggested):

ALTER TRIGGER  DISABLE
or
ALTER TRIGGER  ENABLE

This would be nice.

Martin

Michal Seliga schrieb:
> hi
>
> attached is patch which will make temporary disable of triggers possible. i
> tried it in my application with current data (many inserts in to various table
> with many triggers on them, which are not meant to be run while importing 
> data).
> it works and it changed running time from 62 seconds to 4, so i guess its 
> worth it
>
> i added new pragma DISABLE_TRIGGERS which can be set to 0 (default, everything
> works) or 1 (function which returns triggers always returns empty list so none
> will be called)
>
> honestly, i didn't spent too much time with learning sqlite sources, i just 
> made
> quick hack which works for me, i hope i diidn't break anything. so i decided 
> to
> share it. maybe one day this feature can get to official version too
>
>
>
>   
> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what is most effective way to temporarily disable triggers?

[Michal Seliga]

hi

attached is patch which will make temporary disable of triggers possible. i
tried it in my application with current data (many inserts in to various table
with many triggers on them, which are not meant to be run while importing data).
it works and it changed running time from 62 seconds to 4, so i guess its worth 
it

i added new pragma DISABLE_TRIGGERS which can be set to 0 (default, everything
works) or 1 (function which returns triggers always returns empty list so none
will be called)

honestly, i didn't spent too much time with learning sqlite sources, i just made
quick hack which works for me, i hope i diidn't break anything. so i decided to
share it. maybe one day this feature can get to official version too



diff -r -u sqlite-3.6.16.orig/src/pragma.c sqlite-3.6.16/src/pragma.c
--- sqlite-3.6.16.orig/src/pragma.c 2009-06-25 13:45:58.0 +0200
+++ sqlite-3.6.16/src/pragma.c  2009-07-09 12:16:06.31250 +0200
@@ -1388,6 +1388,10 @@
   }else
 #endif
 
+  if( sqlite3StrICmp(zLeft, "DISABLE_TRIGGERS")==0 ){
+  db->isDisabledTriggers=(u8)atoi(zRight);
+  }
+  else
  
   {/* Empty ELSE clause */}
 
diff -r -u sqlite-3.6.16.orig/src/sqliteInt.h sqlite-3.6.16/src/sqliteInt.h
--- sqlite-3.6.16.orig/src/sqliteInt.h  2009-06-26 17:14:56.0 +0200
+++ sqlite-3.6.16/src/sqliteInt.h   2009-07-09 12:16:10.46875 +0200
@@ -869,6 +869,7 @@
   void (*xUnlockNotify)(void **, int);  /* Unlock notify callback */
   sqlite3 *pNextBlocked;/* Next in list of all blocked connections */
 #endif
+  u8 isDisabledTriggers;/* True if triggers should be disabled */
 };
 
 /*
diff -r -u sqlite-3.6.16.orig/src/trigger.c sqlite-3.6.16/src/trigger.c
--- sqlite-3.6.16.orig/src/trigger.c2009-06-25 13:35:52.0 +0200
+++ sqlite-3.6.16/src/trigger.c 2009-07-09 12:17:11.15625 +0200
@@ -50,6 +50,9 @@
   Schema * const pTmpSchema = pParse->db->aDb[1].pSchema;
   Trigger *pList = 0;  /* List of triggers to return */
 
+  if(pParse->db->isDisabledTriggers)
+ return (Trigger *)0;
+
   if( pTmpSchema!=pTab->pSchema ){
 HashElem *p;
 for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what is most effective way to temporarily disable triggers?

[Michal Seliga]

hi

attached is patch which will make temporary disable of triggers possible. i
tried it in my application with current data (many inserts in to various table
with many triggers on them, which are not meant to be run while importing data).
it works and it changed running time from 62 seconds to 4, so i guess its worth 
it

i added new pragma DISABLE_TRIGGERS which can be set to 0 (default, everything
works) or 1 (function which returns triggers always returns empty list so none
will be called)

honestly, i didn't spent too much time with learning sqlite sources, i just made
quick hack which works for me, i hope i diidn't break anything. so i decided to
share it. maybe one day this feature can get to official version too


diff -r -u sqlite-3.6.16.orig/src/pragma.c sqlite-3.6.16/src/pragma.c
--- sqlite-3.6.16.orig/src/pragma.c 2009-06-25 13:45:58.0 +0200
+++ sqlite-3.6.16/src/pragma.c  2009-07-09 12:16:06.31250 +0200
@@ -1388,6 +1388,10 @@
   }else
 #endif
 
+  if( sqlite3StrICmp(zLeft, "DISABLE_TRIGGERS")==0 ){
+  db->isDisabledTriggers=(u8)atoi(zRight);
+  }
+  else
  
   {/* Empty ELSE clause */}
 
diff -r -u sqlite-3.6.16.orig/src/sqliteInt.h sqlite-3.6.16/src/sqliteInt.h
--- sqlite-3.6.16.orig/src/sqliteInt.h  2009-06-26 17:14:56.0 +0200
+++ sqlite-3.6.16/src/sqliteInt.h   2009-07-09 12:16:10.46875 +0200
@@ -869,6 +869,7 @@
   void (*xUnlockNotify)(void **, int);  /* Unlock notify callback */
   sqlite3 *pNextBlocked;/* Next in list of all blocked connections */
 #endif
+  u8 isDisabledTriggers;/* True if triggers should be disabled */
 };
 
 /*
diff -r -u sqlite-3.6.16.orig/src/trigger.c sqlite-3.6.16/src/trigger.c
--- sqlite-3.6.16.orig/src/trigger.c2009-06-25 13:35:52.0 +0200
+++ sqlite-3.6.16/src/trigger.c 2009-07-09 12:17:11.15625 +0200
@@ -50,6 +50,9 @@
   Schema * const pTmpSchema = pParse->db->aDb[1].pSchema;
   Trigger *pList = 0;  /* List of triggers to return */
 
+  if(pParse->db->isDisabledTriggers)
+ return (Trigger *)0;
+
   if( pTmpSchema!=pTab->pSchema ){
 HashElem *p;
 for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Low-Cost data migration and ETL tools

[Tguru]

Bombarded by Talend ads?

As Lawrence says, Talend Open Studio is an option. It can do the job easily
and less expensive than proprietary solutions that cost a lot of money.

Some companies have budget problems and are having to reduce their cost of
operation. 

You can have a look at the open source called Talend Open Studio: it is
user-friendly but also has advanced features intended for technical users
(java debugger, code injection…). It can perform data migration and ETL as
you wrote in your first post. 

The website is
http://www.talend.com/solutions-data-integration/data-migration.php to
download the open source program. They have a forum and documentation you
can read. Tell us what you think about the software. 

For an ETL benchmark:
http://blogs.sun.com/aja/entry/talend_s_new_data_processing




lawrence.chitty wrote:
> 
> Rstat wrote:
>> Hi, Im building a database for my company. We are a rather small size
>> book
>> company with a lot of references and still growing. 
>>
>> We have a Mysql database here and are trying to find some good tools to
>> use
>> it at its best. Basically we are just starting up the database after
>> dealing
>> with Excel: we had a size problem… So im trying to find a program that
>> will
>> allow us to do two different things: the migration of our data from the
>> old
>> system to the new one and a specialized software to perform ETL (Extract,
>> transform and load) on our database. 
>>
>> About the price of the tools, if we were one year ago, the accounting
>> department would have been pretty relaxed about this. But today, we have
>> some budget restrictions and therefore need a low cost tool. So could you
>> give me some advice on a good data migration and etl tool for a low cost? 
>>
>> Thanks for your help.
>>   
> 
> 
> For some reason, I am forever being bombarded with adverts for Talend 
> (www.talend.com). It looks to be a very capable  ETL tool with an 
> opensource version, although having done nothing more than briefly 
> played with it, I cannot vouch for it.
> 
> Regards
> 
> Lawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Low-Cost-data-migration-and-ETL-tools-tp24387902p24407376.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax

[Jean-Denis Muys]

On 7/9/09 6:21 , "Rick Ratchford"  wrote:

>  
> Okay, this worked, but I have NO IDEA why.
> 
> SQLString = "SELECT min(Year) FROM TmpTable " & _
> "WHERE Month=1 UNION " & _
> "SELECT max(Year) FROM TmpTable " & _
> "WHERE Month = 12 LIMIT 2"
> 
> 
> While this returned the correct answers:
> 
> 1988
> 2008
> 
> What I don't understand is why it didn't simply return:
> 
> 1988
> 1988
> 
> Since there is at least 15 or more days in Month=1 (Jan).
> 
> Anyone?
> 

 Noobie here 

I think the min and max functions by design return only one value.

However, I'm afraid this query might not work in the general case.

For example, if your data starts on january 20, 1988, then this query will
think 1988 is a full year. Ditto if the data ends early in december.

So it seems to me that it works for you by luck, because your data set
doesn't start in january, nor ends in december.

Unless of course if I am totally wrong, which happens often enough.

Jean-Denis Muys

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?

[liubin liu]

does it mean that the sqlite3's C-api isn't reentrant or thread-safety?





#include   // for printf()
#include  // for signal()
#include  // for alarm()
#include  // for system()
#include // for pthread_create()
#include // for sqlite3_***



sqlite3 *db = NULL;

int timer_handle_mark1 = 0;
int timer_handle_mark2 = 9;



pthread_t trd1;

void trd1_task ( void );



int sqlite3_helper_create_db (void);

int sqlite3_helper_insert_data (void);

// psf - prepare, step, finalize
int sqlite3_helper_get_data_psf_from_tb1 ( int id );

void timer_handler()
{
int ret = -1;
int i = 9;

char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
char *query_string = NULL;


sqlite3_stmt *p_stmt = NULL;

timer_handle_mark2 = 0;
query_string = sqlite3_mprintf ( query_format2, i%500 );
timer_handle_mark1++;

timer_handle_mark2 = 1;
ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
timer_handle_mark1++;

timer_handle_mark2 = 2;
ret = sqlite3_step ( p_stmt );
timer_handle_mark1++;

//if ( SQLITE_ROW == ret )
//printf ( "# IN timer_handler(), id: %d, length: %d\n", 
sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );

timer_handle_mark2 = 3;
sqlite3_finalize ( p_stmt );
timer_handle_mark1++;


alarm(1);
}

int inittimer()
{
signal ( SIGALRM, timer_handler );
alarm(1);
return 0;
}

void trd1_task ( void )
{
sleep (30);
printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
sleep (50);
printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
}



int main ( void )
{
int ret = -1;
int i = 0;

ret = pthread_create ( &trd1, 0, (void *)trd1_task, 0 );

ret = sqlite3_open ( "testsignal.db", &db );

sqlite3_helper_create_db ();

sqlite3_helper_insert_data ();

ret = inittimer();

for ( i=0; ; i++ )
ret = sqlite3_helper_get_data_psf_from_tb1 ( i );

ret = sqlite3_close ( db );

system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );

return 0;
}


int sqlite3_helper_create_db (void)
{
int ret = -1;
char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
INTEGER, data CHAR(50));";
char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
INTEGER, data CHAR(50));";

ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
//printf ( "ret: %d\n", ret );
ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
//printf ( "ret: %d\n", ret );
if ( SQLITE_OK == ret )
printf ( "# IN main(), create db file, SUCCESS!\n" );


return 0;
}


int sqlite3_helper_insert_data (void)
{
int ret = -1;
int i = 0;

sqlite3_stmt *p_stmt = NULL;


char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
VALUES (%d, %d, %Q);";
char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
VALUES (%d, %d, %Q);";
char *sql = NULL;


ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
for ( i=0; i<500; i++ )
{
sql = sqlite3_mprintf ( sql_format1, i, i%10,
"datadatadatadatadatadatadatadatadatadatadata" );
ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
ret = sqlite3_step ( p_stmt );
sqlite3_free ( sql );
ret = sqlite3_finalize ( p_stmt );

sql = sqlite3_mprintf ( sql_format2, i, i%10,
"datadatadatadatadatadatadatadatadatadatadata" );
ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
ret = sqlite3_step ( p_stmt );
sqlite3_free ( sql );
ret = sqlite3_finalize ( p_stmt );
}
ret = sqlite3_exec ( db, "COMMIT", NULL, NULL, NULL );
if ( SQLITE_OK == ret )
printf ( "# IN main(), insert data, SUCCESS!\n" );

return 0;
}


int sqlite3_helper_get_data_gt_from_tb1 ( int id )
{
int ret = -1;


char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
char *query_string = NULL;

char **resultp = NULL;
int row = 0;
int column = 0;
char *errmsg = NULL;
char **tmp_str = NULL;

query_string = sqlite3_mprintf ( query_format1, id%500 );
ret = sqlite3_get_table ( db, query_string, &resultp, &row, &column,
&errmsg );
if ( SQLITE_OK == ret )
{
//printf ( "sqlite3_get_table() success!\n" );
tmp_str = resultp + column;
printf ( "# IN main(), id: %d, length: %d\n", atoi(tmp_str[0]),
atoi(tmp_str[1]) );
}
sqlite3_free ( query_string );
sqlite3_free_table ( resultp );

return 0;
}


int sqlite3_helper_get_data_psf_from_tb1 ( int id )
{
int ret = -1;

sqlite3_stmt *p_stmt = NULL;


char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
char *query_string = NULL;

query_string = sqlite3_mprintf ( query_format1, id%500 );