Re: [sqlite] db vs shell

2008-08-10 Thread Vitali Lovich
Careful when using time.  The bash built-in called time times 1 shell 
statement (including pipes).  The binary in /usr/bin/time only times the 
command given - it does not span pipes.

[EMAIL PROTECTED] wrote:
> On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote:
>   
>> Are you sure time ignores everything after the pipe?
>> 
>
> Seems to depend on shell version - when I tested it here it definitely
> ignored everything after. Yours seems to do the right thing, which makes
> your sqlite issue an interesting find indeed.
>
> Cheers, Peter
> ___
> 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] Two different DLLs linked statically to Sqlite and loaded in the same process

2008-03-04 Thread Vitali Lovich
 From what I remember, an attempt at database compatibility is attempted 
across major revision numbers (i.e. all SQLite v2 revisions can read 
SQLite v2 databases, all SQLite v3 revisions can read SQLite v3 
databases), but I can't remember.  I don't believe it's generally 
recommended to be doing what you are doing - is there any reason why 
they both can't be dynamically linked against a sqlite DLL, and then 
supply only the one version?

A good way to test would be to grab the versions being used by the DLLs 
off of the website, and write your own little test harness that 
simulates the situation and try to use 1 DLL version to write the 
database, and another to read & modify it, and finally read it back 
using the 2nd.

If you open a database read-only, then no corruption should occur since 
SQLite will not have the permissions on the file handle.

Kalipto wrote:
> Hello,
>
> For some particular reasons, one process loads two different DLLs, each 
> one linked with a static library of Sqlite. This two DLLs access the 
> same database. I know that there is no problem if you access to the 
> database from 2 different processes. But I was wondering if there would 
> be a problem in this situation. Maybe the two libraries would improperly 
> share some objects like mutex in the same process environment ?
>
> Another question is, does it makes a problem that the 2 versions of the 
> static Sqlite libraries are different (not the same release number) ?
>
> I ask all these questions because sometimes (even if it is rare) the 
> database gets corrupted. So I was wondering if it could be an 
> explanation of this problem or if I must look into another direction 
> (maybe the program would badly write in the database structure and 
> corrupt it).
>
> Last question: if I open a database in read-only mode, does it guarantee 
> that the database will not be corrupted.
>
> Thanks for your answers.
>
> Kal
> ___
> 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] Compiling sources for Coldfire embedded platform

2008-03-04 Thread Vitali Lovich
Actually, the correct way would be to do make CC=compilername, although 
for most situations it may produce the same effect.  Also, SQLite uses 
autotools, so it's just a matter of doing CC=compilername ./configure.

You can find more info using ./configure --help.  Also, for 
cross-compilation, you may want to look at the --target= & --host= 
command-line parameters to configure.  Using configure ensures that 
everything will use the environment you want it to.

Stephen Oberholtzer wrote:
> On Fri, Feb 29, 2008 at 11:01 PM,  <[EMAIL PROTECTED]> wrote:
>   
>>  I've downloaded and built the sources for the standard Intel Linux
>>  platform but I want to run this on Linux on a Coldfire (MCF5484) platform.
>>  How do I modify the compiler the build uses (short of just modifying the
>>  Makefile)? I've looked at the Makefile.in but there's nothing obvious
>>  about how to change it.
>>
>>  Didn't see any docs on this. Any help appreciated.
>>
>> 
>
> I don't know that specifically, but the usual method for that stuff is
>
> CC=compilername make
>
>   

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


Re: [sqlite] installation of sqlite in linux

2008-03-04 Thread Vitali Lovich
debz wrote:
> I have downloaded tclsqlite-3.5.6.so.gz(with tcl binding , i need tcl).I dont
> know how to install.plz help.
>   
>   
You should be able to either put it into a common system library path 
(i.e. /usr/lib) or set the environment variable LD_LIBRARY_PATH before 
invoking the program that will be using the library.  As for how to make 
use of the TCL binding, you'd probably have to look 
http://www.sqlite.org/tclsqlite.html or at some TCL reference books.

PS: You may find it easier to install sqlite through your package 
manager.  On you Ubuntu it's sudo apt-get install libsqlite3-0 
libsqlite3-dev libsqlite3-tcl.  On Gentoo, it'd probably be something 
along the lines of sudo emerge libsqlite3-tcl.  Usually you can 
tab-complete the package name.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.

2007-10-17 Thread Vitali Lovich
Well, rough calculations indicate that the situation that sqlite 
preallocates about an additional 3 rows every time a new ROWID is 
assigned (on the fairly common use case that ROWID is always 
monotonically increasing).  Assuming that this corner case is not 
accounted for, it could be that when assigning randomly or in decreasing 
order, the additional rows are kept around but unassigned, resulting in 
unused space.


Without actually looking into the internals or profiling the code, this 
is just a guess and I would recommend that someone better familiar with 
the internals comment on this topic.


Scott Hess wrote:

Could it be that you're seeing the btree optimization around in-order
insertion? From btree.c:

#ifndef SQLITE_OMIT_QUICKBALANCE
  /*
  ** A special case:  If a new entry has just been inserted into a
  ** table (that is, a btree with integer keys and all data at the leaves)
  ** and the new entry is the right-most entry in the tree (it has the
  ** largest key) then use the special balance_quick() routine for
  ** balancing.  balance_quick() is much faster and results in a tighter
  ** packing of data in the common case.
  */

-scott


On 10/16/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
  

Dear all,

I'll reframe the question again, If ROWID(hidden column/b-tree key/internal
to all table) is changed manually, means I'll insert some unique values in
random order / in descending order (say from 1 to 1), the memory
occupied increases more. why? I observed entire table is getting sorted
based on ROWID.

My results were, for 1 records if it is normally inserted (ie., not
modifying ROWID) it takes 500KB. The same thing if I modify the ROWID it
consumes 1.5MB. why?

Is that all the columns are indexed or only ROWID is indexed?

Memory measurement is done by sqlite3GenericMalloc using
SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each allocation
size gives total memory allocation.

Thanks.


On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:


My question is how you're measuring the memory useage?  Are you
accounting for the space overhead of the various bookkeeping sqlite
needs (i.e. master table)?  The way you're creating you're table implies
you're not using autoincrement for the integer field - are you
accounting for the extra internal row id column?

Joe Wilson wrote:
  

It could be malloc fragmentation.

Which sqlite version, operating system, and malloc implementation are


you using?
  

--- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote:



Say I have 3 columns in one Table, with one INTEGER, two TEXT columns,
  

If
  

ROWID is manually inserted and made descending for 1 records from
  

1
  

to 1, (or even if random number for ROWID - both these cases), the
  

memory
  

occupied is more. Why is this so?

  







  

Be a better Globetrotter. Get better travel answers from someone who


knows. Yahoo! Answers - Check it out.
  

http://answers.yahoo.com/dir/?link=list=396545469




-
  

To unsubscribe, send email to [EMAIL PROTECTED]



-
  




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Compact statement for table to table copy

2007-10-16 Thread Vitali Lovich
Well, you don't have to list the columns if you're not changing the 
ordering.


INSERT INTO table1 SELECT * FROM table2;

Rich Rattanni wrote:

I have two tables in a database with exactly the same schema
(idNum PRIMARY KEY, data1, data2)

I want to copy all the records in table 2 to table 1, currently I am
using the following statement:
INSERT INTO table1 (data1, data2) SELECT data1, data2 FROM table2.

Now this is just a simplified illustration, in my case I am copying
about 10 columns over.  I was wondering if there was a compact way to
write the SQL statement, that copied the  data over from one table to
the other ignoring the primary key fields.  I suspect there is not,
but I figured it wouldnt hurt to ask.

--
Thanks,
Richard Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.

2007-10-16 Thread Vitali Lovich
My question is how you're measuring the memory useage?  Are you 
accounting for the space overhead of the various bookkeeping sqlite 
needs (i.e. master table)?  The way you're creating you're table implies 
you're not using autoincrement for the integer field - are you 
accounting for the extra internal row id column?


Joe Wilson wrote:

It could be malloc fragmentation.

Which sqlite version, operating system, and malloc implementation are you using?

--- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote:
  

Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, If
ROWID is manually inserted and made descending for 1 records from 1
to 1, (or even if random number for ROWID - both these cases), the memory
occupied is more. Why is this so?





   


Be a better Globetrotter. Get better travel answers from someone who knows. 
Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list=396545469

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Vitali Lovich

Two approaches - use sqlite, or use OS code.

Use proper filesystem synchronization as appropriate for the given OS &
filesystem, where you guarantee that your db copy is the only one that
holds an exclusive lock.  Then do the file copy and release the lock.

The better approach, IMHO would be to create a new database with the
same schema, attach it to the existing db instance, and copy over the
data using transactions, then detach it.  This way, you've got access to
the original database and the database copy has a consistent,
thread-safe view of the data in the original.  Additionally, my guess
would be the copy would be vacuumed, which if you're using the copy for
backup purposes is great since it saves on disk space.

Cyrus Durgin wrote:

Maybe it would help to state my use case: without this functionality, what
is the proper way to copy a database using the C API without introducing a
race condition?

On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:
  

-Original Message-
From: Cyrus Durgin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 09, 2007 5:02 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to get file handle from sqlite3 object?

i'm wondering if there's a "standard" way to get an open file
handle from an
sqlite3 pointer using the C API.  anyone know?

  

There's no public way to get this, nor should there be.  The internal
implementation of the database should be kept separate from the logical
API
to access it.  Such a function would muddy the water between
implementation
and interface and hamper the ability to change the implementation without
changing the interface.

Not all filesystems would be able to return one, nor could it guarantee
that
the database is in fit state for someone to fiddle with its internal
handle.
Furthermore, it could not be guaranteed that once a caller obtained the
handle that the caller might then do something damaging to it or alter its
state.

Such a function definitely falls into the BAD IDEA category, IMO.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]

-






  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-09 Thread Vitali Lovich
Can you please clarify why this would be needed?  Sqlite databases are 
opened by name, thereby you can use standard OS or stdlib functions to 
open the same file with a different handle.


Cyrus Durgin wrote:

i'm wondering if there's a "standard" way to get an open file handle from an
sqlite3 pointer using the C API.  anyone know?

  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] any way to SELECT the 100th-104th row of a table?

2007-10-07 Thread Vitali Lovich
The only way I can think of doing it without modifying the table (i.e. 
adding a rowid column that's updated on every delete) is to select all 
the rows on the table

and then increment count for every step that returns a row.

Adam Megacz wrote:

Hello.  This is probably a stupid question, but...

Is there any way to include some phrase in a SELECT clause that will
match only the Nth-Mth rows of a table, for some values of N and M?

Note that ROWID isn't what I'm looking for -- if you delete rows from
a table the ROWID no longer matches the "row number".

Thanks,

  - a

  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Conditional table select

2007-05-06 Thread Vitali Lovich



Dan Kennedy wrote:

On Fri, 2007-05-04 at 18:22 -0400, Vitali Lovich wrote:
Multiple tables contain a primary key KEY.  If Table1 contains a 
matching KEY, then I want that row from Table1.  Only if it isn't in 
Table1, then look in Table2 if it is there.  If not in Table2 go on to 
Table3, etc etc.


How about this:

  SELECT * FROM tbl1 WHERE key = $key 
  UNION ALL 
  SELECT * FROM tbl2 WHERE key = $key

  LIMIT 1;

Although technically, using LIMIT without ORDER BY is a bad thing.
  

The solution I came up with is:

SELECT coalesce(
(SELECT field1 FROM tbl1 WHERE key = $key),
(SELECT field1 FROM tbl2 WHERE key = $key),
(SELECT field1 FROM tbl3 WHERE key = $key))
, field2 FROM tbl1 WHERE key = $key;

The only problem with this though is that I can only select that 1 field
- if I want another, I have to do more select statements.  However, if
coalesce works the way I think it does, then it'll do early evaluation
and stop at the first non null parameter.  Also, I'm hoping that SQLite
realizes that it can retrieve field2 on its first evaluation of select.
Even if it can't though, I'm only expecting tbl1 to have at most maybe
10 entries.

Thoughts, suggestions?

Thanks



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Conditional table select

2007-05-04 Thread Vitali Lovich

Hi,

I was wondering what would be the optimal way to select 1 matching row 
from multiple tables.  Here is the scenario.


Multiple tables contain a primary key KEY.  If Table1 contains a 
matching KEY, then I want that row from Table1.  Only if it isn't in 
Table1, then look in Table2 if it is there.  If not in Table2 go on to 
Table3, etc etc.


Is there a way to do this using SQL, or should I just break this up into 
multiple queries and have the logic in C - this is for an embedded 
system, so I want to use the least amount of memory & CPU (memory is 
more important though).


Thanks

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] shared-cache mode and firefox

2006-12-06 Thread Vitali Lovich

http://www.sqlite.org/sharedcache.html

It's controlled at runtime by the function

int sqlite3_enable_shared_cache(int);

Thus that behaviour is probably controlled by Firefox (assuming it uses a 
version of sqlite with cache support compiled in).  You'd have to ask them.


Jay Sprenkle wrote:

Good evening,

I'd like to make a request for the next version of sqlite.
It's a big change and will probably get shot down, but you won't know
until you ask.

Firefox is now using sqlite. They use shared-cache mode because they 
want it

to work over networked drives and they don't want to pay for the
latency involved.
I personally wouldn't have made this choice since it trades away
something I think
that could be useful to a large number of people to support something
that I believe
almost nobody will use. It's not my decision though. If shared-cache 
mode was

something that could be turned off at run time it would allow everyone
to get what
they want. I believe this would be a complete refactoring of the code 
though.


Thanks for your time Dr. Hipp, and for sharing Sqlite with all of us!

Jay



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

2006-12-01 Thread Vitali Lovich

Try instead of "SELECT * FROM table WHERE name LIKE ?" as your sql query,
"SELECT * FROM table WHERE name LIKE :comparison"


Thomas Zangl wrote:

Vitali Lovich schrieb:

Regarding your code snippet:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
sqlite3_bind_text(prepared_statement, 0,search , search , 
SQLITE_STATIC);


First I'm not sure what language you're using - it seems Perl-like.

Anyways, the documentation for 
http://www.sqlite.org/capi3ref.html#sqlite3_bind_text gives the 4th 
param as the number of bytes (not chars) while you're passing the 
original string.  Since I'm assuming it's Perl, it won't generate an 
error on the type mismatch.  You generally want to pass -1 for the 
fourth parameter (from what I understand, -1 is always safe for 
sqlite3_bind_text).  Also, take care in using SQLITE_STATIC and make 
sure that the string you pass remains on the heap (i.e. delete isn't 
called, not sure if this is possible in Perl) or the stack (i.e. 
local variable in scope) when you execute the statement.

Its C :-)

Anyway, I tried your suggestion and free the char* after 
sqlite3_finalize. Does not help.

  char* sql_parameter_search = '%test%'
rc = sqlite3_bind_text(prepared_statement, 1,
sql_parameter_search, strlen(sql_parameter_search), 
SQLITE_STATIC);

sql_check_result(rc);
logDebug("Added search = %s", sql_parameter_search);

the result is:

Added search = %test%
my_sqlite_logger-SQLITE said: (0) SELECT * FROM table WHERE name LIKE ?


So - no variable substitution done?

Somebody with a working LIKE example?

TIA,

Thomas

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

2006-11-30 Thread Vitali Lovich

Regarding your code snippet:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
sqlite3_bind_text(prepared_statement, 0,search , search , SQLITE_STATIC);

First I'm not sure what language you're using - it seems Perl-like.

Anyways, the documentation for 
http://www.sqlite.org/capi3ref.html#sqlite3_bind_text gives the 4th 
param as the number of bytes (not chars) while you're passing the 
original string.  Since I'm assuming it's Perl, it won't generate an 
error on the type mismatch.  You generally want to pass -1 for the 
fourth parameter (from what I understand, -1 is always safe for 
sqlite3_bind_text).  Also, take care in using SQLITE_STATIC and make 
sure that the string you pass remains on the heap (i.e. delete isn't 
called, not sure if this is possible in Perl) or the stack (i.e. local 
variable in scope) when you execute the statement.


Hope this helps.

PS.  As for the trace, I can't help you as I've never used it (try 
looking up the API reference).  What I do though for my program is save 
to the log the string I pass to prepare.  Then I print the values that 
in the order they are bound.


Thomas Zangl wrote:

Igor Tandetnik schrieb:

Hi!

Compare with this program:

string userInput;
string sql = "update UserPrefs set innocuousPref=? where userid=123;";
sqlite3_stmt* stmt;
sqlite3_prepare(db, sql.c_str(), -1, , 0);
sqlite3_bind_text(stmt, 1, userInput.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

Well - ok ;-) I am quite sure that my version is safe but anyway I 
tried sqlite3_prepare. Now I have some problems debugging it.


I tried to use

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);

but I get the input of the prepare call - no ? replaced by the actual 
values.


Any ideas how to debug this? I have doubts that my SQL statement is 
bound correctly.


What I try todo is something like this:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
qlite3_bind_text(prepared_statement, 0,search , search , SQLITE_STATIC);

Any ideas?

TIA,
Thomas


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] number problem with 3.2.8

2006-10-25 Thread Vitali Lovich
I'm fairly certain that it's because when you're saying ring_time > 
'10', it's asking sqlite to take all strings that are greater than the 
string '10'.  So all the numbers you presented are greater than 1.  What 
you want is "where ring_time > 10".  Notice the lack of quotes which 
means treat 10 as a number instead of a string.




Lloyd Thomas wrote:

I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be having a problem 
doing queries where with '>' to search a number.
for instance if I do the following

select  ring_time fron calls where ring_time > '10';
I get the following results
3
6
3
6
3
6
2
3
3
3
2
etc.
Why? 
This row is varchar. Is 3.2.8 not able to work with numbers stored as varchar? 

  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Escaping wildcards when using LIKE & UTF-16

2006-10-16 Thread Vitali Lovich
Sorry.. Pressed send before completing the explanation.  Most of the 
time is spent actually just creating the db representation of the 
library, which is a fairly significant amount of time - it's inserting 
at about 3-5 mp3 files per second into the DB, but that's only a 
profiled release build.  Right now, as I think I mentioned, sqlite is 
only taking a small fraction of the time and I want it to stay that way.


Griggs, Donald wrote:

Re: "..a possible optimization I'm thinking of ..."

Is it simple to place, say, 10 SQL Bind operations in series, then see if
this time is even perceptible?

Since this is a human interface, is it not likely that any efforts to avoid
the binds will go unnoticed because the execution time is so small?

(Maybe with an embedded processor, things are just vastly slower than what I
usually work with.)









[These off-the-cuff opinions are mine, and not those of my company, of
course]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Escaping wildcards when using LIKE & UTF-16

2006-10-16 Thread Vitali Lovich
This actually isn't really something that really has a user interface 
(at least not at the moment).


sourceforge.net/projects/networkmedia for the little blurb - basically 
I'm working on a way of synchronizing disjoint music collections - i.e. 
music collection on laptop, music collection on main computer.  Laptop & 
main computer get new different new songs & some songs have their id3 
tags updated.  Ideally, this program will be able to merge the two to 
create a single music library with all unique songs (songs with 
different audio data are different songs, but songs where just the 
metadata is different are the same and just merge the metadata to get 
the most recent info).


Griggs, Donald wrote:

Re: "..a possible optimization I'm thinking of ..."

Is it simple to place, say, 10 SQL Bind operations in series, then see if
this time is even perceptible?

Since this is a human interface, is it not likely that any efforts to avoid
the binds will go unnoticed because the execution time is so small?

(Maybe with an embedded processor, things are just vastly slower than what I
usually work with.)









[These off-the-cuff opinions are mine, and not those of my company, of
course]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Escaping wildcards when using LIKE & UTF-16

2006-10-16 Thread Vitali Lovich
Thanks for the helpful reply - it does seem like it'll probably be be 
better and easier to compile a new statement every time - a possible 
optimization I'm thinking of making is that I'll cache the fields 
selected in the previous query - that way I'll only need to recompile 
the query every time the fields needed changes (which for my purposes 
could potentially be never or rarely).  It also allows me to use the = 
operator to ensure that the id3 tags match exactly.


Dennis Cote wrote:

Vitali Lovich wrote:
Yeah, I know I can do that, and that's my backup option.  I just 
wasn't sure how much performance suffers in this situation because 
this has to keep re-compiling the SQL statements (whereas right now, 
all my SQL statements are only prepared once at runtime and then the 
appropriate values are simply bound).  I was hoping that I could 
bypass figuring out the precise performance hit if I could figure out 
a clever way of doing this (right now sqlite only accounts for about 
16% of the time spent).



Vitali,

Now I see why you are trying to do this. It is an interesting 
optimization idea, but it will only work if the time to execute all 
the wildcard comparisons is less that the time it takes to compile the 
more specific query.


To get an idea how long it takes sqlite's like function to do a 
wildcard match I ran the following test. I created a database with 8M 
rows containing the string "1234567890". I then timed the execution of 
the following two queries.


   select count(*) from t;
   select count(*) from t where a like '%'; 
The only difference is that sqlite will execute a call to the like 
function for each row. Each comparison will succeed and the same 
result is returned. The first query took 1.59 seconds, and the second 
took 6.29 seconds. The difference, 4.7 seconds, is the time it took to 
execute the like comparisons. This gives an execution time of about 
560 ns per wildcard like comparison.


If your table has 2000 songs and each song has 6 ID3 tags, and you are 
trying to match only one of them, you will be executing 10K (5 * 2000) 
unnecessary wildcard like comparisons. This should take about 5.6ms. 
So if sqlite takes less time than this to compile your query it would 
be faster to use a specific query that only looked at the one column 
you are trying to match. You will have to write some test code to time 
preparing your queries.


Regardless of which way you build your queries, you will still have to 
escape any wildcard and escape characters in the user supplied (or 
selected) strings that you are using with the like operator for the 
columns that you are trying to match.


HTH
Dennis Cote 
- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Escaping wildcards when using LIKE & UTF-16

2006-10-13 Thread Vitali Lovich
Yeah, I know I can do that, and that's my backup option.  I just wasn't 
sure how much performance suffers in this situation because this has to 
keep re-compiling the SQL statements (whereas right now, all my SQL 
statements are only prepared once at runtime and then the appropriate 
values are simply bound).  I was hoping that I could bypass figuring out 
the precise performance hit if I could figure out a clever way of doing 
this (right now sqlite only accounts for about 16% of the time spent).


Thanks anyways

Dennis Cote wrote:

Vitali Lovich wrote:
Maybe there's something else I can do to get around it for my 
purposes.  What I have for instance is a table representing ID3 
tags.  What I want to do is select from the table any existing tags 
that match a variable number of fields - for instance, I can say I 
want to match using title & artist only or I using all the fields 
such as title, artist, album etc...  The LIKE comparison allowed me 
to use the % wildcard to bind to fields I don't care about - is there 
any other way using some other comparison operator (going under the 
restriction that I don't want to modify the values I'm using to query).



Vitali,

If I understand you correctly, you have a table with columns like this:

   CREATE TABLE id3_tags (
   title   text,
   artist  text,
   album   text,
   ...
   );

And you want to do queries that match one or more of these columns.

I would suggest that you build your SQL query based on the columns the 
user selected to match. Then you can bind the user supplied text to 
match only those columns and you don't even look at the columns that 
are not supposed to match (instead of matching everything with a 
wildcard). Something like this:


   //build the query
   int columns = 0;
   string sql = "SELECT * from id3_tags ";

   if (user_selected_title) {
   sql += columns++ ? "AND " : "WHERE ";
   sql += "title = :title ";
   }
   if (user_selected_artist) {
   sql += columns++ ? "AND " : "WHERE ";
   sql += "artist = :artist ";
   }
   if (user_selected_album) {
   sql += columns++ ? "AND " : "WHERE ";
   sql += "album = :album ";
   }
   ... // repeat for all columns

   // prepare the query
   sqlite3_stmt* query;
   int rc = sqlite3_prepare(db, sql.c_str(), -1, , NULL);

   // bind parameters to the query
   if (user_selected_title) {
   int idx = sqlite3_bind_parameter_index(query, ":title");
   sqlite3_bind_text(query, idx, users_title, -1, SQLITE_STATIC);
   }
   if (user_selected_artist) {
   int idx = sqlite3_bind_parameter_index(query, ":artist");
   sqlite3_bind_text(query, idx, users_artist, -1, SQLITE_STATIC);
   }
   if (user_selected_album) {
   int idx = sqlite3_bind_parameter_index(query, ":album");
   sqlite3_bind_text(query, idx, users_album, -1, SQLITE_STATIC);
   }
   ... // repeat for all columns

   // execute the query
   do {
   rc = sqlite3_step(query);
 if (rc == SQLITE_ROW) {
   // use sqlite3_column_* to retrieve results
   }
   } while rc != SQLITE_DONE

HTH
Dennis Cote


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Escaping wildcards when using LIKE & UTF-16

2006-10-13 Thread Vitali Lovich
Maybe there's something else I can do to get around it for my purposes.  
What I have for instance is a table representing ID3 tags.  What I want 
to do is select from the table any existing tags that match a variable 
number of fields - for instance, I can say I want to match using title & 
artist only or I using all the fields such as title, artist, album 
etc...  The LIKE comparison allowed me to use the % wildcard to bind to 
fields I don't care about - is there any other way using some other 
comparison operator (going under the restriction that I don't want to 
modify the values I'm using to query).


Dennis Cote wrote:

Vitali Lovich wrote:

I have a statement along the lines of:
"SELECT * FROM table WHERE value LIKE :abc"

I want to bind a UTF-16 string to abc - however, it can potentially 
contain % or _ - does sqlite provide any functionality for escaping 
these characters (I know I have to use the escape clause, but I still 
have to escape the individual characters in the string replacing 
:abc) or do I have to write my own routine that works with the wider 
string?




Vitali,

No, there is no API in sqlite to escape the wildcard and escape 
characters in a LIKE search string. You will have to do it yourself.


HTH
Dennis Cote


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Escaping wildcards when using LIKE & UTF-16

2006-10-12 Thread Vitali Lovich

I have a statement along the lines of:
"SELECT * FROM table WHERE value LIKE :abc"

I want to bind a UTF-16 string to abc - however, it can potentially 
contain % or _ - does sqlite provide any functionality for escaping 
these characters (I know I have to use the escape clause, but I still 
have to escape the individual characters in the string replacing :abc) 
or do I have to write my own routine that works with the wider string?


Thanks

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] fast Java solution?

2006-07-17 Thread Vitali Lovich
My suggestion to speed up the step call backs would be to have the JNI 
code do the actual step routine and cache up the pertinent information 
into an array.


Then after it's complete or the cache limit is reached (set the cache 
limit to a sufficient size such that the time spent processing that 
information in Java outweighs your JNI communication time, at least by a 
factor of 10 I think) return the cache to Java for processing.


Other than that, you will be limited by the JNI overhead.  
http://www.excelsior-usa.com/ has a product 
thttp://www.excelsior-usa.com/xfunction.html which lets you call native 
libraries without writing any kind of JNI code.  The reason I'm pointing 
it out is that they may have some optimizations that you might not have 
though of.  Anyways, it's not free, but there is an evaluation version 
that should let you decide whether or no it fits your requirements.


Brannon King wrote:

So who here has the _fast_ Java solution for accessing SQLite3?

Here's what I've done so far. I took the wrapper from wiki page labeled
"Java wrapper incl. JDBC driver for SQLite.
¤http://www.ch-werner.de/javasqlite;. I then fixed the calloc calls so that
the params were in the right order, fixed the finalize calls that should
have been reset calls, added some bind functions, built some step functions
without the column name/type overhead, and compiled it with my 3.3.6 code
from last week. So the step function(s) for that library take a Java
callback function. Alas, that seems to be too slow. The overhead of the
Java-to-C then the C-to-Java call all in each step function is just too much
overhead in JNI calls. Anyone else seen that issue?

Is there some standard tool that will generate a JNI dll from the
sqlite.dll? And if so, will that do step functions without the callback
overhead?

I suppose I'll dig in and make a custom JNI interface so that most of my
code is done in C, but just thought I'd ask around first... Thanks for your
time. The other Java wrappers posted seem to wrap too much or too little or
not be compatible with version 3. 
_

Brannon King
¯



  


[sqlite] RE: Problem executing sqlite3_prepare command

2006-07-11 Thread Vitali Lovich
Never mind.  I just realized (from rereading a previous newsgroup 
posting) that the wildcards can only be used to replace values within 
expressions (and not the table or column names).




[sqlite] Problem executing sqlite3_prepare command

2006-07-11 Thread Vitali Lovich
I'm trying to call the sqlite3_prepare command with the string 'CREATE 
TABLE $table ( $columns );'.  It fails and the errmsg functions returns 
'near "$table": syntax error'.  I've tried using ? as well and I get the 
same message.  What am I missing?


Thanks