Re: [sqlite] db vs shell
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
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
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
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] Compact statement for table to table copy
Sorry - then I don't really know a syntactically shorter way On 10/17/07, Rich Rattanni <[EMAIL PROTECTED]> wrote: > > I am not changing the ordering, but i do not want to copy the primary > key field since it is auto increment (I neglected too mention that > before, sorry). > On 10/16/07, Vitali Lovich <[EMAIL PROTECTED]> wrote: > > 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] > > > - > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
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&sid=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
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.
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&sid=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?
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?
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?
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] Bug: Database connection closed on different thread
I'm confused with your statement about threads. With multi-core multi-cpu machines becoming more prevalent on the desktop, seems to me that multi-threaded apps would provide better performance (obviously for specific problem sets). Also, don't apps that properly separate UI and background work benefit from being multi-threaded (i.e. UI remains responsive despite processing) even on single-cpu machines? Granted, writing correct multi-threaded apps may be more difficult, but I would disagree that they're evil - it just requires a higher level of programmer and better understanding of the architecture. [EMAIL PROTECTED] wrote: patters <[EMAIL PROTECTED]> wrote: Greetings, We ran into a bug that's not documented behavior: closing a connection that was created in a different thread (where thread A calls sqlite3_open[16], "hands off" the connection to thread B, and thread B calls sqlite3_close). The documentation (http://www.sqlite.org/faq.html#q6) says that any functions can be used with a connection as long as it's not being used by another thread. With SQLITE_ENABLE_MEMORY_MANAGEMENT defined, The documentation says that when SQLITE_ENABLE_MEMORY_MANAGEMENT is defined then all operations against a database connection must occur in the same thread in which the database connection was originally opened. This constraint exists for all versions of SQLite before and including 3.4.2. The constraint is removed in version 3.5.0. And as is my custom, I must also warn you that your best remedy is to not use threads in the first place since threads are evil and programs that use threads are almost always buggy and slow. If you feel compelled to use threads in spite of this warning, then upgrading to SQLite version 3.5.0 will probably work out better for you than trying to use version 3.4.2 or earlier. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
I think you need to clarify your needs a bit, cause it seems somewhat confusing. So you have a wireless network, where all the nodes connect wirelessly to it. What is the database being used for? Is each node accessing and updating a local copy of some database (i.e. what other nodes it can see), or is it accessing a centrally managed database which all the nodes are accessing/updating. Additionally, what exactly are you trying to simulate? Are you trying to test how efficiently databases work over some kind of network (as a previous reply pointed out, this would be pointless with sqlite since sqlite is in-process and local) or are you trying to test out some kind of third algorithm where you just need to use some kind of database to store data. nadiap wrote: Hello, i am a newbie and i would like to ask if it is possible to use sqlite in order to simulate a wireless network where each node will have each own database. I mean, can i attach each node to a database? What shall i do? I am sorry if my question seems naive, but i am just learning Please help me. Thank you - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Conditional table select
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
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
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?
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?
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, &stmt, 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
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
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
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
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
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, &query, 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
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
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?
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
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
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
