Re: [sqlite] Re: Using a table as a negative filter.
Thanks Igor! This solves a big question for me :-) Ran On 3/29/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > Ran <[EMAIL PROTECTED]> wrote: > > For example: > > > > create temp table A(col1 varchar, col2 varchar, col3 varchar, col4 > > varchar); > > > > insert into A values('a', 'A', '1', 'n'); > > insert into A values('a', 'a', '2', 'e'); > > insert into A values('b', 'B', '3', 'n'); > > insert into A values('a', 'A', '4', 'n'); > > insert into A values('b', 'b', '5', 'e'); > > insert into A values('c', 'c', '6', 'n'); > > > > create temp table B(col1 varchar, col2 varchar, col3 varchar); > > > > insert into B values('a', '1', 'a'); > > insert into B values('b', '2', 'b'); > > insert into B values('d', '3', 'd'); > > insert into B values('e', '3', 'e'); > > > > So, in order to get all the rows of table A where col1 and col2 are > > equal to any of the col1 and col3 of the rows of table B, one can > > write: > > > > select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = > > B.col1 and A.col2 = B.col3; > > > > Which returns: > > > > a|a|2|e > > b|b|5|e > > > > However, how could I get the other rows - so those rows in table A > > which do NOT match to the rows of table B (using the same columns)? > > select * from A where not exists > (select * from B where A.col1 = B.col1 and A.col2 = B.col3); > > or > > select A.* from A left join B on (A.col1 = B.col1 and A.col2 = B.col3) > where B.col1 is null; > > Igor Tandetnik >
Re: [sqlite] Re: Db copy
Iulian Popescu wrote: Thank you very much, but this will require to modify the library as far as I understand. I would rather not do that, is that any other less intrusive way? Iulian, I wasn't suggesting that you modify the library. It is an open source program. I was suggesting that you "appropriate" some of the source that is used to implement functions similar to what you are trying to do, and incorporate it into your application. The functions in shell.c use the library APIs in the same way as your application. Copy the useful stuff out and modify it to do exactly what you need to do in your own application. It seemed like a good place to start to me. HTH Dennis Cote
RE: [sqlite] Re: Db copy
Thank you very much, but this will require to modify the library as far as I understand. I would rather not do that, is that any other less intrusive way? Iulian. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 6:50 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Db copy Iulian Popescu wrote: >I'm really sorry - I forgot to mention that both databases are in memory. > > > > Iulian, In that case you will have to copy the contents. The easiest way to do that is probably to modify the code the sqlite shell uses to do a database dump. Instead of writing the generated SQL out to a file like the dump command does, execute the generated SQL statements to create the same tables and records in the second attached database. The shell command is implemented in a few functions in shell.c. I believe there is some similar code in the vacuum functions that copy the entire database to a new file without converting everything into SQL text. This might be faster. You can look at the code in vacuum.c HTH Dennis Cote
Re: [sqlite] Re: Db copy
Iulian Popescu wrote: I'm really sorry - I forgot to mention that both databases are in memory. Iulian, In that case you will have to copy the contents. The easiest way to do that is probably to modify the code the sqlite shell uses to do a database dump. Instead of writing the generated SQL out to a file like the dump command does, execute the generated SQL statements to create the same tables and records in the second attached database. The shell command is implemented in a few functions in shell.c. I believe there is some similar code in the vacuum functions that copy the entire database to a new file without converting everything into SQL text. This might be faster. You can look at the code in vacuum.c HTH Dennis Cote
RE: [sqlite] Re: Db copy
I'm really sorry - I forgot to mention that both databases are in memory. Iulian. -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 5:32 PM To: SQLite Subject: [sqlite] Re: Db copy Iulian Popescu wrote: > Does anybody have an idea what would be the easiest way to completely > copy an attached database to another empty attached database? Making a copy of the underlying file. Igor Tandetnik
[sqlite] improving query performance
I have a sqlite database with about 3 GB of data, most of which is stored in a data table with about 75 million records, having three columns (EntryId INTEGER, PropertyId INTEGER, Value NUMERIC) and PRIMARY KEY(EntryId, PropertyId). This table is not indexed, to allow faster updates. The problem is that it takes over an hour to access all Values, for a specified PropertyId, when the value is obtained for each EntryId separately (using "SELECT Value FROM Data WHERE PropertyId=? AND EntryId=?", bound to the specified PropertyId and EntryId) and the EntryId values for successive database queries are in essentially random order (taken from an external list of entries that has been sorted by property values). This same query (getting the property value for each EntryId, separately) only takes about 7 minutes when the EntryId values for successive database queries are in the same ascending order as the data orginally inserted into the table. I assume that this has to do with better pager caching of successive records in the database, whereas random access may re-read the same page multiple times (due to the limited cache). My question is whether it should be faster to A) create an index for the table before the query, query the value (for the specified PropertyId) for each EntryId (in essentially random order, from external list of entries), and delete the index after the queries (for each EntryId) are done or B) issue a single "SELECT EntryId, Value FROM Data WHERE PropertyId=?" query (bound to the specified PropertyId) and step through the results, using something like a hash table lookup to map the EntryId values (returned from the query) back to an index into the external list of entries. The values extracted from the database are to be copied into an entry property data structure, having the same order as the external list of entries.
Re: [sqlite] Db copy
Why not do it through the underlying OS? If the destination is empty -alex On Mar 29, 2006, at 1:10 PM, Iulian Popescu wrote: Hello, Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Thank you, Iulian.
Re: [sqlite] Db copy
Iulian Popescu wrote: Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Iulian, I think it would be easiest to copy the file associated with the attached database instead of the trying to copy the database contents. After copying the file, you can attach to the new file and you will have a complete copy of the original database. HTH Dennis Cote
[sqlite] Re: Db copy
Iulian Popescu wrote: Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Making a copy of the underlying file. Igor Tandetnik
[sqlite] Db copy
Hello, Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Thank you, Iulian.
Re: [sqlite] Installing SQLite on a Shared Platform
you are absolutely right on the debian version numbering, of course. the GNU lib is 2.2.5 and brilliant idea aabout the live cd. i'll get a copy of knoppix (debian based i recall) and get the lib from there. thanks so much! Justin -- View this message in context: http://www.nabble.com/Installing-SQLite-on-a-Shared-Platform-t1362538.html#a3659555 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Installing SQLite on a Shared Platform
jpadie wrote: > 2. the linux servers are debian 2.4.28 (so they say). i read that 2.4 was > not a stable release so i am assuming they mean 2.2.x in any event With Debian 2.4.28 I think of a Debian release with kernel version 2.4.28. This means it is a Debian distribution with a self-compiled kernel. No information about Debian release. You might get some information about Debian release with cat /etc/debian_version or cat /etc/issue but the content need not to be the truth. > from this i would guess it would also use glibc2.2 and not 2.3 as you > point out the debian woody (3) uses the earlier library. The command ldd --version tells you the GNU libc version. If I remember correct, phpinfo() has a line with libc version, too. >so next thing to try is to find sqlite.so compiled with glibc.so. i have > no way of compiling things on my systems and googling does not reveal > anything. Do any of you know a source of precompiled .so files? Why you don't use one of those Linux Live CDs? You get libraries, you can decompress binary packages, pick the right files and you can compile.
[sqlite] Re: Using a table as a negative filter.
Ran <[EMAIL PROTECTED]> wrote: For example: create temp table A(col1 varchar, col2 varchar, col3 varchar, col4 varchar); insert into A values('a', 'A', '1', 'n'); insert into A values('a', 'a', '2', 'e'); insert into A values('b', 'B', '3', 'n'); insert into A values('a', 'A', '4', 'n'); insert into A values('b', 'b', '5', 'e'); insert into A values('c', 'c', '6', 'n'); create temp table B(col1 varchar, col2 varchar, col3 varchar); insert into B values('a', '1', 'a'); insert into B values('b', '2', 'b'); insert into B values('d', '3', 'd'); insert into B values('e', '3', 'e'); So, in order to get all the rows of table A where col1 and col2 are equal to any of the col1 and col3 of the rows of table B, one can write: select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and A.col2 = B.col3; Which returns: a|a|2|e b|b|5|e However, how could I get the other rows - so those rows in table A which do NOT match to the rows of table B (using the same columns)? select * from A where not exists (select * from B where A.col1 = B.col1 and A.col2 = B.col3); or select A.* from A left join B on (A.col1 = B.col1 and A.col2 = B.col3) where B.col1 is null; Igor Tandetnik
[sqlite] Using a table as a negative filter.
Hi all, I would like to ask if anyone can suggest how to filter out rows of one table by using negation over several columns of rows of other table. For example: create temp table A(col1 varchar, col2 varchar, col3 varchar, col4 varchar); insert into A values('a', 'A', '1', 'n'); insert into A values('a', 'a', '2', 'e'); insert into A values('b', 'B', '3', 'n'); insert into A values('a', 'A', '4', 'n'); insert into A values('b', 'b', '5', 'e'); insert into A values('c', 'c', '6', 'n'); create temp table B(col1 varchar, col2 varchar, col3 varchar); insert into B values('a', '1', 'a'); insert into B values('b', '2', 'b'); insert into B values('d', '3', 'd'); insert into B values('e', '3', 'e'); So, in order to get all the rows of table A where col1 and col2 are equal to any of the col1 and col3 of the rows of table B, one can write: select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and A.col2 = B.col3; Which returns: a|a|2|e b|b|5|e However, how could I get the other rows - so those rows in table A which do NOT match to the rows of table B (using the same columns)? Just to change the equal signs to non-equal won't work because the tables are joined - so many rows that are not wanted are returned. Of course I could write: select A.col1, A.col2, A.col3, A.col4 from A except select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and A.col2 = B.col3; Which gives correctly: a|A|1|n a|A|4|n b|B|3|n c|c|6|n But I wonder if there is a better way (so which perform better) to get those rows. Any suggestions? Thanks a lot, Ran
RE: [sqlite] sqlite3_step crash?
Thanks, guys. I knew this wasn't going to be enough, I was mostly looking for suggestions as to how I should pursue the problem and it looks like I'll have to resort to writing a rusty C sample given that I haven't touched C/C++ in years. I guess I was still hoping for an "oh yea, we have a bug 123 logged and its been solved post 3.3.4 release" :) Cheers! -Boris -- +1.604.689.0322 DeepCove Labs Ltd. 4th floor 595 Howe Street Vancouver, Canada V6C 2T5 [EMAIL PROTECTED] CONFIDENTIALITY NOTICE This email is intended only for the persons named in the message header. Unless otherwise indicated, it contains information that is private and confidential. If you have received it in error, please notify the sender and delete the entire message including any attachments. Thank you. -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 6:40 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3_step crash? On Wed, 29 Mar 2006 [EMAIL PROTECTED] wrote: >"Boris Popov" <[EMAIL PROTECTED]> wrote: >> >> Does this help at all? >> > >No. A stack trace, especially one without line number >information, is of no help whatsoever. I beg to differ. Any stack trace that includes malloc or free is almost certainly heap corruption. What the OP should do is: - Check for buffer overflows. - Check for multiple free()s of the same memory block. - Run the software under some memory checker. Not sure what to recommend on Windows. The problem is almost certainly in the OP's code. >D. Richard Hipp <[EMAIL PROTECTED]> Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] Installing SQLite on a Shared Platform
Thanks for the reply. I should have included this in my original post: 1. 1&1 won't turn on sqlite. they say it's a security issue (which is, of course, nonsense). I suspect it is a pricing issue as it might cause people who would otherwise upgrade to a multiple mysql package to remain at the cheaper level and use sqlite dbs. 2. the linux servers are debian 2.4.28 (so they say). i read that 2.4 was not a stable release so i am assuming they mean 2.2.x in any event from this i would guess it would also use glibc2.2 and not 2.3 as you point out the debian woody (3) uses the earlier library. so next thing to try is to find sqlite.so compiled with glibc.so. i have no way of compiling things on my systems and googling does not reveal anything. Do any of you know a source of precompiled .so files? Many thanks for your assistance Justin -- View this message in context: http://www.nabble.com/Installing-SQLite-on-a-Shared-Platform-t1362538.html#a3654138 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] quote() and constraints
On 3/29/06, jt <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm implementing a log procedure with triggers on my tables (following > some ideas found in http://www.sqlite.org/cvstrac/wiki?p=UndoRedo). > As I have more than one table, I'm generating the relevant SQL to do the > job. > I use the quote() function to quote each value of each row. > > The problem is that " SELECT quote('foo')!='foo' ": when I tried to > load the log table in another database, I get "SQL error; constraint > failed" on every row that has a CHECK constraint. > The quick solution is to add quote() around each value in the CHECK > statement. > > Is there another way to do it? > Why is " SELECT quote('foo')!='foo' "? quote('foo') returns 'foo', including the ' '. The 'foo' on your right-hand side doesn't include the quotes. - Pam
Re: [sqlite] Sqlite CLDC
Hi, I found this site but unfortunately it is intended for the J2SE/J2EE but not for the J2ME (Java 2 Mobile Edition). Me I work under J2ME with configuration CLDC. But the latter does not comprise all APIs java which one finds under J2EE. From: Dennis Cote <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite CLDC Date: Wed, 29 Mar 2006 08:22:18 -0700 EL AMRANI Jalal wrote: Hi, I am a new developper in the J2ME technology, I want to connect to a file SQLITE database through JDBC by using the CLDC configuration. I know that the java.sql API does not exist but i make this question for our programmers for many explation. And I shall be aware of that it dosn't exist any PURE Java JDBC driver in J2ME for SQLite. And as I know one uses JNI to invoke non-java libraies. I want that you help me please if it is possible to have a JDBC for SQLITE DATABASE. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Google is your friend. Try searching for "SQLite JDBC". Among other things it will lead you to http://www.ch-werner.de/javasqlite/overview-summary.html HTH Dennis Cote _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] [SOLVED] Re: [sqlite] LIKE and GLOB bug with numbers?
Tito Ciuro wrote: Due to application requirements, I must rely on LIKE and GLOB in order to match data, matching data that contains some value (sensitive or insensitive match). Now, it seems to me that using either LIKE or GLOB will force a row scan anyhow, since it can't use the index, correct? So your solution would force me to perform the row scan for 250K rows when matching values. That is 10 times more data to scan through. Tito, I'm glad to see you are looking at this. First, the attribute value table is storing the same string data in my proposal and your value table. In my case there is some overhead for the additional rowids (3 bytes for 250K rows), and the FileId column (2 bytes for 25K files), but in your case you have the overhead of the field separator commas (13 bytes in your example with 14 attributes). My table has 250K short rows, and yours has 25K long rows. There are 10 times as many rows in my table, but your rows are about 10 times a large. There may be a small difference in the total amount of data, but it is not going to be significant one way or the other. The real heart of the problem is your requirement to match data using LIKE due to "application requirements". Can you expand on that statement so that I can understand the details of your requirements? From your examples, what you are doing is using LIKE to do equality tests for a subfield in your large value column. With the values separated into separate rows there is no need to use LIKE to do that. WHERE CMValues LIKE '%2004-12-16 10:11:35 -0800%' would become WHERE Attribute.Value = '2004-12-16 10:11:35 -0800' If you need case insensitive searches you simply convert the value string to the same case as your test string in the equality test. Or if you are unsure of the case of the test string, you can convert them both. WHERE upper(Attribute.Value) = 'ABIWORD.PROFILE' or WHERE upper(Attribute.Value) = upper(:match_value) Now, these comparisons will scan all the values, since they don't specify which attribute you want to check. This may be what you want if you are searching for a date and you don't care if it matches the create date or the modification date, but generally that is not what you want to do (Why look at file size or album name data when looking for a date?). Usually you will want to check the value of a particular attribute as shown below. WHERE AttributeDefinition.Name = ' Modification Date' AND Attribute.Value = :mod_date This query will use the AttributeValue index to reduce the rows scanned to only those that hold values of this attribute. In this case each file has a mod date so it will search 25K rows. For a query that is looking for a attribute like 'Album Name' which only exists for your 1K music files, it will only scan those 1K rows checking for album name matches. In some cases you may want to look in a set of attributes, which you can do as shown below. WHERE AttributeDefinition.Name IN ( ' Modification Date', 'Create Date', 'Backup Date') AND Attribute.Value = :date The possibilities are, quite literally, endless. If you have some sample queries that you need to make please tell me what they are so we can talk about more concrete examples. HTH Dennis Cote
Re: [sqlite] Installing SQLite on a Shared Platform
On Wed, 29 Mar 2006, jpadie wrote: > >Hi there > >my host (1 and 1) have compiled php5 without sqlite. but I have a couple of >php applications that would benefit from SQLite (for portability). You could raise a ticket with 1 and 1 to install sqlite. > >The server is a linux box and I do not have root, SSH or telnet access >(although i have found using php i can eval and exec commands (eg. apt-get) >outside of the document root: opens up possibilities...) What version and distro of Linux? Do you know? > >I do not operate any linux boxes elsewhere so have no way of compiling and >uploading source code. > >Now the issue: > >I am trying to find a way to "install" and use SQLite even though it is not >compiled in to php5. PDO and PDO_SQLite are installed. > >i can dl() extensions (.so) but when I try to do this with the SQLite >extension 2.8.17 or later i get the message: >-- >Warning: dl() [function.dl]: Unable to load dynamic library >'/homepages/6/OCCLUDED/htdocs/pear/extensions/sqlite-2.8.17.so' - >/lib/libc.so.6: version `GLIBC_2.3' not found This means that sqlite-2.8.17.so is compiled against glibc 2.3, which is included with most modern distros. Debian Woody uses glibc 2.2, as does RH 7.x and derived products (RH AS 1.x) RH 8.x was the first glibc 2.3 based RH distribution, I believe. Not sure about the Suse distros. It could be that: - The Linux box it is installed on is using glibc < 2.3, in which case you'll have to get hold of library binaries compiled on an old distro with suitable glibc. - PHP is running in a chroot environment, such that the system glibc is not available. The fact you can run external binaries makes this unlikely. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] quote() and constraints
Hi, I'm implementing a log procedure with triggers on my tables (following some ideas found in http://www.sqlite.org/cvstrac/wiki?p=UndoRedo). As I have more than one table, I'm generating the relevant SQL to do the job. I use the quote() function to quote each value of each row. The problem is that " SELECT quote('foo')!='foo' ": when I tried to load the log table in another database, I get "SQL error; constraint failed" on every row that has a CHECK constraint. The quick solution is to add quote() around each value in the CHECK statement. Is there another way to do it? Why is " SELECT quote('foo')!='foo' "? config: sqlite 3.3.4 -- jt
Re: [sqlite] Sqlite CLDC
EL AMRANI Jalal wrote: Hi, I am a new developper in the J2ME technology, I want to connect to a file SQLITE database through JDBC by using the CLDC configuration. I know that the java.sql API does not exist but i make this question for our programmers for many explation. And I shall be aware of that it dosn't exist any PURE Java JDBC driver in J2ME for SQLite. And as I know one uses JNI to invoke non-java libraies. I want that you help me please if it is possible to have a JDBC for SQLITE DATABASE. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Google is your friend. Try searching for "SQLite JDBC". Among other things it will lead you to http://www.ch-werner.de/javasqlite/overview-summary.html HTH Dennis Cote
[sqlite] Installing SQLite on a Shared Platform
Hi there my host (1 and 1) have compiled php5 without sqlite. but I have a couple of php applications that would benefit from SQLite (for portability). The server is a linux box and I do not have root, SSH or telnet access (although i have found using php i can eval and exec commands (eg. apt-get) outside of the document root: opens up possibilities...) I do not operate any linux boxes elsewhere so have no way of compiling and uploading source code. Now the issue: I am trying to find a way to "install" and use SQLite even though it is not compiled in to php5. PDO and PDO_SQLite are installed. i can dl() extensions (.so) but when I try to do this with the SQLite extension 2.8.17 or later i get the message: -- Warning: dl() [function.dl]: Unable to load dynamic library '/homepages/6/OCCLUDED/htdocs/pear/extensions/sqlite-2.8.17.so' - /lib/libc.so.6: version `GLIBC_2.3' not found -- I have googled for this error but not come up with anything helpful. If anyone has any workarounds or suggestions i'd be very grateful thanks Justin -- View this message in context: http://www.nabble.com/Installing-SQLite-on-a-Shared-Platform-t1362538.html#a3651203 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] sqlite3_step crash?
On Wed, 29 Mar 2006 [EMAIL PROTECTED] wrote: >"Boris Popov" <[EMAIL PROTECTED]> wrote: >> >> Does this help at all? >> > >No. A stack trace, especially one without line number >information, is of no help whatsoever. I beg to differ. Any stack trace that includes malloc or free is almost certainly heap corruption. What the OP should do is: - Check for buffer overflows. - Check for multiple free()s of the same memory block. - Run the software under some memory checker. Not sure what to recommend on Windows. The problem is almost certainly in the OP's code. >D. Richard Hipp <[EMAIL PROTECTED]> Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Sqlite CLDC
Hi, I am a new developper in the J2ME technology, I want to connect to a file SQLITE database through JDBC by using the CLDC configuration. I know that the java.sql API does not exist but i make this question for our programmers for many explation. And I shall be aware of that it dosn't exist any PURE Java JDBC driver in J2ME for SQLite. And as I know one uses JNI to invoke non-java libraies. I want that you help me please if it is possible to have a JDBC for SQLITE DATABASE. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] sqlite3_step crash?
"Boris Popov" <[EMAIL PROTECTED]> wrote: > > Does this help at all? > No. A stack trace, especially one without line number information, is of no help whatsoever. If you think the problem is in SQLite and not in your own code, then you should do as much of the following as you can: (1) Explain why you think the problem is in SQLite. (2) Tell us exactly what version of SQLite you are using and how you obtained it (did you compile it yourself or use a precompiled download.) (3) Provide us with your database schema and the query you were executing at the time of failure. (4) Provide a sample database. (5) Provide copies of your source code (6) Come up with a reproducible test case that demonstrations the problem using the command-line shell, sqlite3.exe -- D. Richard Hipp <[EMAIL PROTECTED]>