[sqlite] Which is faster raw file I/O or sqlite BLOB
Hi, I am planning to use BLOB to store file contents. So can you please suggest, which is faster 'raw file I/O' or 'sqlite BLOB'. Thank you in advance. -H ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cross-database time function.
Hello! On Tuesday 29 December 2009 04:21:07 Simon Slavin wrote: I agree that this is often an acceptable alternative. But * it's hard to decipher if you're reading the data by eye SQLite internal juliandays format is not human readable too. * the system does not deal with leap seconds correctly It's not the problem becouse the format precision is 1 second. * the system terminates in 2038 (if you use Unix's old 32-bit standard) * one day you may need to read the data on a non-unix platform In cross-platform Tcl: tclsh8.5 [~]clock format 1000 Wed Nov 16 12:46:40 MSK 5138 Nevertheless, if your data starts off as a Unix epoch, it can be fast and convenient to just store it without having to do any conversion. As example, Cisco devices and some Unix daemons produce datetime in this format. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which is faster raw file I/O or sqlite BLOB
It depends on numerous facts. I'd say if size of all your files is measured in megabytes and you're not dealing with thousands of files in the same directory then raw file I/O will be faster. If size of files is measured mostly in tens or hundreds of bytes and you need thousands and millions of them then SQLite will be faster. Pavel On Tue, Dec 29, 2009 at 3:55 AM, _h_ hiralsmaill...@gmail.com wrote: Hi, I am planning to use BLOB to store file contents. So can you please suggest, which is faster 'raw file I/O' or 'sqlite BLOB'. Thank you in advance. -H ___ 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] Using incremental BLOB I/O when processing result set
Its a shame that a BLOB handle is not returned as a result of a query rather than the BLOB itself. Let me correct you. It's not a shame, it's database specifics and it has its good points. For me personally I'd hate if SQLite returned me some abstract handle if I requested blob value. Note: if you select blob value for example from MS SQL it's also loaded as a whole into servers memory - you just don't care about that. But depending on client implementation the blob value can be also loaded in full into client's memory as well (despite your usage of SQLGetData() function), so this behavior is not something SQLite-specific. Also note: for latest versions of MS SQL Microsoft strongly recommends to not use text and image datatypes (real LOB types) but use varchar(max) and varbinary(max) instead. These types can obtain any value LOBs could get but they also will never return to you any handle in case you've selected them - only the whole value as a bunch. So again as you see this behavior is not SQLite-specific. And the last note: I've never heard of any database engine developer who cares about making it possible to use his DBMS with any other DBMS in a database independent manner. If somebody needs that independence usually he writes database-specific drivers which have independent API and internally handle all cases differently. Pavel On Tue, Dec 29, 2009 at 2:52 AM, Mark Hessling m...@rexx.org wrote: Well that is very disappointing :-( Not being able to extract a portion of a BLOB from a result set without having the complete BLOB in memory makes it impractical to use BLOBs in SQLite in a database independent manner. Its a shame that a BLOB handle is not returned as a result of a query rather than the BLOB itself. Thanks for the clarification anyway. Cheers, Mark On Mon, 2009-12-28 at 21:17 -0500, Igor Tandetnik wrote: Zaher Dirkey wrote: If i want to extract BLOB to a file that mean it is must the whale blob be loaded to memory before can save it? No, not with BLOB I/O. But you have to be careful: don't mention the BLOB field in your SELECT statement, retrieve the ROWID instead. Then use it in sqlite3_blob_open call. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- * Mark Hessling, m...@rexx.org http://www.rexx.org/ * Author of THE, a Free XEDIT/KEDIT editor, Rexx/SQL, Rexx/CURL, etc. * Maintainer of Regina Rexx interpreter and Rexx/Tk * Use Rexx? join the Rexx Language Association: http://www.rexxla.org/ ___ 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] SQLitedb problem
Hello, Thanks you for your reply. Ok i will explain my sqlite's problem with more details. The problem concern the address book sqlitedb file from my iPhone. There is some data (contacts) from this file don't appear on my Iphone. And i verfy that by using a sqlite application browser : SQLite Database Browser.It's the same results. But when i open the adressbook.sqlitedb file with notepad or pspad (the sqlitedb file is not an ascii file but we can read the ascii data stored in this file) i can find these contacts. So i saw that they are not removed. I think that these data are ignored. So how data can be ignored on a sqlitedn file? How can i fix this problem so i can view them on my iphone or SQLite Database Browser. Othman. On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov paiva...@gmail.com wrote: What's the problem with the answers already given? Pavel On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I wait for your help. Please can you answer to me ASAP. Thanks, Othman. On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I have a problem with a sqlitedb file. So there is some data in this sqlitedb file (on notepad or pspad we can found easily these data) but they don't appear on a sqlite application browser (i use SQLite Database Browser 2.0 b1)...it seems that these data are ignored. Then how can i fix my problem? Thanks, Othman ___ 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] SQLitedb problem
Thanks, for your remark. I paid no attention to the mailing list. In fact, I wanted to respond to the list. But, i doesn't receive on my inbox answers from the mailing list. Is there any answers? Othman. On Mon, Dec 28, 2009 at 6:05 PM, Pavel Ivanov paiva...@gmail.com wrote: 1. Don't reply directly to me. If you write your question to the mailing list all further discussion should take place in the list. 2. Do you realize that if you write your question to the mailing list then all members of the list will answer to the list, not directly to your e-mail? Yes, I wrote directly to you because I've realized that you don't read this list at all (why do you write to it then?). 3. Prove me wrong: did you read all the answers given you in the mailing list? Pavel On Mon, Dec 28, 2009 at 12:58 PM, Othman Guessous guessous.oth...@gmail.com wrote: Hello Pavel, Thanks you for your reply. Ok i will explain my sqlite's problem with more details. The problem concern the address book sqlitedb file from my iPhone. There is some data (contacts) from this file don't appear on my Iphone. And i verfy that by using a sqlite application browser : SQLite Database Browser.It's the same results. But when i open the adressbook.sqlitedb file with notepad or pspad (the sqlitedb file is not an ascii file but we can read the ascii data stored in this file) i can find these contacts. So i saw that they are not removed. I think that these data are ignored. So how data can be ignored on a sqlitedn file? How can i fix this problem so i can view them on my iphone or SQLite Database Browser. I thank you in advance for your return. Othman. On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov paiva...@gmail.com wrote: What's the problem with the answers already given? Pavel On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I wait for your help. Please can you answer to me ASAP. Thanks, Othman. On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I have a problem with a sqlitedb file. So there is some data in this sqlitedb file (on notepad or pspad we can found easily these data) but they don't appear on a sqlite application browser (i use SQLite Database Browser 2.0 b1)...it seems that these data are ignored. Then how can i fix my problem? Thanks, Othman ___ 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] SQLitedb problem
I understand now. Thank you Pavel for the link. But i don't understand why i don't receive the answers to my gmail inbox. However, I joined the mailing list. So i'll check my registration. I thank everyone for the responses. I will read them with attention. Othman. On Mon, Dec 28, 2009 at 6:23 PM, Pavel Ivanov paiva...@gmail.com wrote: http://www.mail-archive.com/sqlite-users@sqlite.org/msg49299.html Pavel On Mon, Dec 28, 2009 at 1:15 PM, Othman Guessous guessous.oth...@gmail.com wrote: Thanks, for your remark. I paid no attention to the mailing list. In fact, I wanted to respond to the list. But, i doesn't receive on my inbox answers from the mailing list. Is there any answers? Othman. On Mon, Dec 28, 2009 at 6:05 PM, Pavel Ivanov paiva...@gmail.com wrote: 1. Don't reply directly to me. If you write your question to the mailing list all further discussion should take place in the list. 2. Do you realize that if you write your question to the mailing list then all members of the list will answer to the list, not directly to your e-mail? Yes, I wrote directly to you because I've realized that you don't read this list at all (why do you write to it then?). 3. Prove me wrong: did you read all the answers given you in the mailing list? Pavel On Mon, Dec 28, 2009 at 12:58 PM, Othman Guessous guessous.oth...@gmail.com wrote: Hello Pavel, Thanks you for your reply. Ok i will explain my sqlite's problem with more details. The problem concern the address book sqlitedb file from my iPhone. There is some data (contacts) from this file don't appear on my Iphone. And i verfy that by using a sqlite application browser : SQLite Database Browser.It's the same results. But when i open the adressbook.sqlitedb file with notepad or pspad (the sqlitedb file is not an ascii file but we can read the ascii data stored in this file) i can find these contacts. So i saw that they are not removed. I think that these data are ignored. So how data can be ignored on a sqlitedn file? How can i fix this problem so i can view them on my iphone or SQLite Database Browser. I thank you in advance for your return. Othman. On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov paiva...@gmail.com wrote: What's the problem with the answers already given? Pavel On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I wait for your help. Please can you answer to me ASAP. Thanks, Othman. On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I have a problem with a sqlitedb file. So there is some data in this sqlitedb file (on notepad or pspad we can found easily these data) but they don't appear on a sqlite application browser (i use SQLite Database Browser 2.0 b1)...it seems that these data are ignored. Then how can i fix my problem? Thanks, Othman ___ 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] SQLitedb problem
Because the data was deleted. If the data was deleted it doesn't mean, that the data is not more in the database. It just not overwritten or not cleaned by an vacuum command. Is that so hard to believe? Artur -- Am 28.12.2009, 19:09 Uhr, schrieb Othman Guessous guessous.oth...@gmail.com: Hello, Thanks you for your reply. Ok i will explain my sqlite's problem with more details. The problem concern the address book sqlitedb file from my iPhone. There is some data (contacts) from this file don't appear on my Iphone. And i verfy that by using a sqlite application browser : SQLite Database Browser.It's the same results. But when i open the adressbook.sqlitedb file with notepad or pspad (the sqlitedb file is not an ascii file but we can read the ascii data stored in this file) i can find these contacts. So i saw that they are not removed. I think that these data are ignored. So how data can be ignored on a sqlitedn file? How can i fix this problem so i can view them on my iphone or SQLite Database Browser. Othman. On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov paiva...@gmail.com wrote: What's the problem with the answers already given? Pavel On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I wait for your help. Please can you answer to me ASAP. Thanks, Othman. On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous guessous.oth...@gmail.com wrote: Hello, I have a problem with a sqlitedb file. So there is some data in this sqlitedb file (on notepad or pspad we can found easily these data) but they don't appear on a sqlite application browser (i use SQLite Database Browser 2.0 b1)...it seems that these data are ignored. Then how can i fix my problem? Thanks, Othman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Requirements for index-aware INSERT SELECT
The code I use can calculate data flow for sql queries (summing xRead iAmt in VFS) and I noticed that many variations of INSERT SELECT led to very big data flow (multiplication of the db size). I thought that such queries can be optimized if both tables are indexed accordingly and finally the following query INSERT OR IGNORE INTO Table (Field) SELECT SomeOtherField FROM OtherTable ORDER BY SomeOtherField produced significant reduce in data flow. (Field Is indexed in Table). I don't think the difference is related to some caching since the variant without ORDER BY shows 50 MB data transfer for 17 MB base while adding ORDER BY reduces it to 1 MB (1:50 ratio so far). But my other query uses more complex schema (with LEFT JOIN and several fields (although indexed together)). The problem is I could not optimize the query to reduce the data flow in this case. Are there any specific requirements for the inserts like the first one? Couldn not to find information about this in the documentation. Thanks Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Archive Search Engine
It would be very helpful if there was a search engine for the sqlite-user mailing list archive. My question might have already been answered, but currently using the archive I have to manually look through the threads month by month. It is like finding a needle in a haystack! Bill _ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Archive Search Engine
But there is a search engine on: http://www.mail-archive.com/sqlite-users%40sqlite.org/info.html It is right at the top. Adam On Tue, Dec 29, 2009 at 12:02 PM, Bill Marvin bill_mar...@hotmail.comwrote: It would be very helpful if there was a search engine for the sqlite-user mailing list archive. My question might have already been answered, but currently using the archive I have to manually look through the threads month by month. It is like finding a needle in a haystack! Bill _ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG Report -- schema.test does not check for authorization in build
Test schema-13.1 fails with
Error: {authorization not available in this build}
Test needs to be bracket with
ifcapable auth {
do_test schema-13.1 {
set S [sqlite3_prepare_v2 db SELECT * FROM sqlite_master -1 dummy]
db function hello hello
db function hello {}
db auth auth
db auth fails because tclsqlite.c has
#ifdef SQLITE_OMIT_AUTHORIZATION
Tcl_AppendResult(interp, authorization not available in this
build, 0);
return TCL_ERROR;
#else
Regards,
Noah Hart
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] undefined reference to `readline'
Hi everybody, sqlite 3.6.16 on linux debian. Using the amalgamation src, I compile sqlite using configure --enable-readline. But using the shell, I haven' t the history function. The config.log file says: sqlite-3.6.16/conftest.c:37: undefined reference to `readline' collect2: ld returned 1 exit status. So it's clear, the linker does not find the readline function , but I don't understand why. Any idea ? Thanks to everybody Angelo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] selective result columns
Is it possible to run an SQL query in sqlite that displays columns only on a condition? For example: select col1, (case when 1==2 then col2) from myTable In this case, I only want to show one column (col1) Thanks -- View this message in context: http://old.nabble.com/selective-result-columns-tp26958131p26958131.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
On 29 Dec 2009, at 6:29pm, nomorecaddy wrote: Is it possible to run an SQL query in sqlite that displays columns only on a condition? For example: select col1, (case when 1==2 then col2) from myTable In this case, I only want to show one column (col1) I'm not sure I understand your question, but I'll guess. A SELECT command must return the same number of columns in each row. You can use a conditional function to change what appears in each row and your CASE is fine, but you need to correct the syntax: http://sqlite.awardspace.us/syntax/sqlitepg09.htm Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
Thanks, I'm looking for a SQL query that returns a variable number of columns. Many of my columns contain NULL data, and I want to avoid showing the column altogether in that case. Simon Slavin-3 wrote: On 29 Dec 2009, at 6:29pm, nomorecaddy wrote: Is it possible to run an SQL query in sqlite that displays columns only on a condition? For example: select col1, (case when 1==2 then col2) from myTable In this case, I only want to show one column (col1) I'm not sure I understand your question, but I'll guess. A SELECT command must return the same number of columns in each row. You can use a conditional function to change what appears in each row and your CASE is fine, but you need to correct the syntax: http://sqlite.awardspace.us/syntax/sqlitepg09.htm Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/selective-result-columns-tp26958131p26959565.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG Report on sqlite 3.6.20 Error in SQL parser between sqlite3.3.4 and sqlite3.6.20
Hello,
I detect this problem because a program using sqlite command line works
on sqlite.3.3.4 but
it does not anymore using sqlite3.6.20
PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in
previous version sqlite3.3.4 OK)
OR Error in SQL parser between
sqlite3.3.4 and sqlite3.6.20
TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes
HOW TO REPRODUCE IT:
Execute following batch on both versions of sqlite
sqlite3 Fails3.6.20.sql
when using 3.6.20 we get the unjustified error
Best regards,
Alejandro
Fails3.6.20.sql--
BEGIN TRANSACTION;
CREATE TABLE basica(
x,
y,
suma
);
INSERT INTO basica VALUES('Austria','1996-03',5904.0);
INSERT INTO basica VALUES('Austria','1996-04',21904.0);
INSERT INTO basica VALUES('Germany','1996-03',10545.0);
INSERT INTO basica VALUES('Germany','1996-04',13687.0);
INSERT INTO basica VALUES('USA','1996-03',21814.0);
INSERT INTO basica VALUES('USA','1996-04',13108.0);
CREATE TABLE groupLimX(
x,
sumaXs
);
INSERT INTO groupLimX VALUES('USA',305843.0);
INSERT INTO groupLimX VALUES('Germany',258820.0);
INSERT INTO groupLimX VALUES('Austria',140668.0);
CREATE TABLE groupLimY(
y,
sumaYs
);
INSERT INTO groupLimY VALUES('1996-04',113818.0);
INSERT INTO groupLimY VALUES('1996-03',102947.0);
COMMIT;
.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);
/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4
SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING
(y, x);
---
BEGIN TRANSACTION;
CREATE TABLE basica(
x,
y,
suma
);
INSERT INTO basica VALUES('Austria','1996-03',5904.0);
INSERT INTO basica VALUES('Austria','1996-04',21904.0);
INSERT INTO basica VALUES('Germany','1996-03',10545.0);
INSERT INTO basica VALUES('Germany','1996-04',13687.0);
INSERT INTO basica VALUES('USA','1996-03',21814.0);
INSERT INTO basica VALUES('USA','1996-04',13108.0);
CREATE TABLE groupLimX(
x,
sumaXs
);
INSERT INTO groupLimX VALUES('USA',305843.0);
INSERT INTO groupLimX VALUES('Germany',258820.0);
INSERT INTO groupLimX VALUES('Austria',140668.0);
CREATE TABLE groupLimY(
y,
sumaYs
);
INSERT INTO groupLimY VALUES('1996-04',113818.0);
INSERT INTO groupLimY VALUES('1996-03',102947.0);
COMMIT;
.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);
/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4 SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING (y, x);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] undefined reference to `readline'
Hi, Am 29.12.09 19:28, schrieb Angelo: sqlite-3.6.16/conftest.c:37: undefined reference to `readline' collect2: ld returned 1 exit status. So it's clear, the linker does not find the readline function , but I don't understand why. Do you have the readline library including headers installed? Lutz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
On 29 Dec 2009, at 8:34pm, nomorecaddy wrote: I'm looking for a SQL query that returns a variable number of columns. Many of my columns contain NULL data, and I want to avoid showing the column altogether in that case. That is something that must be handled by your software. There is no way in SQL to return an answer to a SELECT which has a different number of columns in different records. You could make SQL return columns with NULL in and your software could automatically recognise them and know not to print them. Remember that SQL is a database engine. It's job is to supply data. It's your software's job to understand what needs to be done with it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible error when using overloaded name oid inside a trigger
There appears to be a problem with the use of an explicit column named oid from inside an 'instead of insert' trigger on a view. This seems to have been introduced in version 3.6.18 as the results differ from those of version 3.6.17. SQLite version 3.6.21 behaves the same as: SQLite version 3.6.18 sqlite sqlite create temp table foo (oid integer, nid integer); sqlite sqlite create temp view vxdata as select * from foo; sqlite sqlite create temp table log(a integer, b integer); sqlite sqlite create temp trigger tr instead of insert on vxdata ...for each row begin ... insert into log values(new.oid, new.nid); ...end; sqlite sqlite insert into vxdata(oid, nid) select 100, 200; sqlite insert into foo(oid, nid) select 100, 200; sqlite select * from log; -1|200 sqlite select * from foo; 100|200 sqlite .exit SQLite version 3.6.17 sqlite sqlite create temp table foo (oid integer, nid integer); sqlite sqlite create temp view vxdata as select * from foo; sqlite sqlite create temp table log(a integer, b integer); sqlite sqlite create temp trigger tr instead of insert on vxdata ...for each row begin ... insert into log values(new.oid, new.nid); ...end; sqlite sqlite insert into vxdata(oid, nid) select 100, 200; sqlite insert into foo(oid, nid) select 100, 200; sqlite select * from log; 100|200 sqlite select * from foo; 100|200 sqlite .exit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
I don't have access to that level of software, so that's the problem. Thanks for your response - I like the power of select case, and was hoping that case could be applied in other areas as well. Simon Slavin-3 wrote: On 29 Dec 2009, at 8:34pm, nomorecaddy wrote: I'm looking for a SQL query that returns a variable number of columns. Many of my columns contain NULL data, and I want to avoid showing the column altogether in that case. That is something that must be handled by your software. There is no way in SQL to return an answer to a SELECT which has a different number of columns in different records. You could make SQL return columns with NULL in and your software could automatically recognise them and know not to print them. Remember that SQL is a database engine. It's job is to supply data. It's your software's job to understand what needs to be done with it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/selective-result-columns-tp26958131p26960765.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
I'm not clear. Were you able to use Simon's syntax link: http://sqlite.awardspace.us/syntax/sqlitepg09.htm To correct your syntax? SQL works with sets, and will always return the same number of columns, but you *can* use sql to force a NULL, an empty string, or a space to be returned for a column value. Does that not meet your need? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG Report on sqlite 3.6.20 Error in SQL parser between sqlite3.3.4 and sqlite3.6.20
I get the same error in 3.6.18, so probably the same solution applies in
3.6.20. I got the query to work with a sub-select.
SQLite version 3.6.18
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite BEGIN TRANSACTION;
sqlite CREATE TABLE basica(
... x,
... y,
... suma
... );
sqlite INSERT INTO basica VALUES('Austria','1996-03',5904.0);
sqlite INSERT INTO basica VALUES('Austria','1996-04',21904.0);
sqlite INSERT INTO basica VALUES('Germany','1996-03',10545.0);
sqlite INSERT INTO basica VALUES('Germany','1996-04',13687.0);
sqlite INSERT INTO basica VALUES('USA','1996-03',21814.0);
sqlite INSERT INTO basica VALUES('USA','1996-04',13108.0);
sqlite
sqlite CREATE TABLE groupLimX(
... x,
... sumaXs
... );
sqlite INSERT INTO groupLimX VALUES('USA',305843.0);
sqlite INSERT INTO groupLimX VALUES('Germany',258820.0);
sqlite INSERT INTO groupLimX VALUES('Austria',140668.0);
sqlite
sqlite CREATE TABLE groupLimY(
... y,
... sumaYs
... );
sqlite INSERT INTO groupLimY VALUES('1996-04',113818.0);
sqlite INSERT INTO groupLimY VALUES('1996-03',102947.0);
sqlite COMMIT;
sqlite
sqlite .header on
sqlite SELECT * FROM groupLimY INNER JOIN groupLimX;
y|sumaYs|x|sumaXs
1996-04|113818.0|USA|305843.0
1996-04|113818.0|Germany|258820.0
1996-04|113818.0|Austria|140668.0
1996-03|102947.0|USA|305843.0
1996-03|102947.0|Germany|258820.0
1996-03|102947.0|Austria|140668.0
sqlite CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
sqlite SELECT * FROM mia LEFT JOIN basica USING (y, x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING
(y,x);
SQL error: cannot join using column y - column not present in both tables
sqlite SELECT * FROM (select x, sumaXs, y, sumaYs from groupLimY INNER JOIN
groupLimX) LEFT JOIN basica USING (y,x);
x|sumaXs|y|sumaYs|suma
USA|305843.0|1996-04|113818.0|13108.0
Germany|258820.0|1996-04|113818.0|13687.0
Austria|140668.0|1996-04|113818.0|21904.0
USA|305843.0|1996-03|102947.0|21814.0
Germany|258820.0|1996-03|102947.0|10545.0
Austria|140668.0|1996-03|102947.0|5904.0
sqlite
sqlite SELECT * FROM (select * from groupLimY INNER JOIN groupLimX) LEFT JOIN
basica USING (y,x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite
Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com
HARRIS CORPORATION | RF Communications Division
assuredcommunications(tm)
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of javaj1...@elxala.com
Sent: Tuesday, December 29, 2009 8:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] BUG Report on sqlite 3.6.20 Error in SQL parser between
sqlite3.3.4 and sqlite3.6.20
Hello,
I detect this problem because a program using sqlite command line works
on sqlite.3.3.4 but
it does not anymore using sqlite3.6.20
PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in
previous version sqlite3.3.4 OK)
OR Error in SQL parser between
sqlite3.3.4 and sqlite3.6.20
TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes
HOW TO REPRODUCE IT:
Execute following batch on both versions of sqlite
sqlite3 Fails3.6.20.sql
when using 3.6.20 we get the unjustified error
Best regards,
Alejandro
Fails3.6.20.sql--
BEGIN TRANSACTION;
CREATE TABLE basica(
x,
y,
suma
);
INSERT INTO basica VALUES('Austria','1996-03',5904.0);
INSERT INTO basica VALUES('Austria','1996-04',21904.0);
INSERT INTO basica VALUES('Germany','1996-03',10545.0);
INSERT INTO basica VALUES('Germany','1996-04',13687.0);
INSERT INTO basica VALUES('USA','1996-03',21814.0);
INSERT INTO basica VALUES('USA','1996-04',13108.0);
CREATE TABLE groupLimX(
x,
sumaXs
);
INSERT INTO groupLimX VALUES('USA',305843.0);
INSERT INTO groupLimX VALUES('Germany',258820.0);
INSERT INTO groupLimX VALUES('Austria',140668.0);
CREATE TABLE groupLimY(
y,
sumaYs
);
INSERT INTO groupLimY VALUES('1996-04',113818.0);
INSERT INTO groupLimY VALUES('1996-03',102947.0);
COMMIT;
.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);
/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4
SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica
[sqlite] .dump of utf16 database
I was trying some .dump round-trips like sqlite3 old.db .dump | sqlite3 new.db and noticed if old.db is utf16, ie. pragma encoding=utf16, then new.db doesn't get that but instead is utf8. Is that intentional? Would slipping a pragma into the .dump output preserve the db coding? (I don't want the .dump text output to be utf16, and this was only an experiment to see what came out. Perhaps it doesn't matter to anything.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts3 issue with tokenizing of content during a query
Hi - I'm using sqlite 3.6.21 with this patchhttp://www.sqlite.org/src/ci/6cbbae849990d99b7ffe252b642d6be49d0c7235, which I found in this forum a few weeks ago. I'm also using a custom tokenizer which I wrote. My scenario is this: I am storing XHTML in the database, and I want to FTS-enable this content. I only want to index the text contained within the XHTML elements, not the element names or attributes. (e.g. dont-index this=or thisindex this/...) My tokenizer skips over element names and attributes, then delegates the element textual content to the Porter tokenizer. On return from the Porter tokenizer, I correct the token offset and length values to be the actual offsets within the document (Porter tokenizer doesn't ever see the whole document, just a string within a tag). I didn't want to ship my tokenizer with my app for two reasons. 1 - I wrote it using an API not available to my client app, 2 - it doesn't make sense because on the client the user will be entering search terms that aren't surrounded by xml tags, which is what my tokenizer expects. Instead, my client registers a tokenizer with the same name as my custom tokenizer, but in fact it is registering a copy of the porter tokenizer. I expected this to work fine - and it appeared to, until I discovered that it was pulling out text in some of the xml attributes - which shouldn't be indexed. It turns out that FTS3 is re-tokenizing the content (not just the search term) on the client (using my copy of the Porter tokenizer) and returning those results. I don't understand why - is this a bug or is this normal behavior? I expected the fts index to retain all of the token offsets/sizes such that they wouldn't have to be recomputed on the client. My workaround is to port my tokenizer so that it runs on the client, and to wrap search terms in dummy xml tags dummylike this/dummy. But I feel I shouldn't have to do this... Any feedback appreciated... Nick Hodapp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump of utf16 database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kevin Ryde wrote: Perhaps it doesn't matter to anything. The only effect is what happens behind the scenes. If you primarily use the - -16 interfaces to bind and retrieve text then the database also being utf16 means you avoid SQLite doing a conversion (assuming the byte orders also match). Some SQLite internals like query parsing are UTF8 only anyway (ie the prepare-16 functions convert to UTF8 and call the UTF8 variants). There may also be size differences in your database depending on the distribution of code points in your text data and the size of strings in proportion to other data and SQLite's metadata. You can dress your immediate issue by doing this: (echo 'pragma encoding=UTF-16;' ; sqlite3 old.db .dump ) | sqlite3 new.db Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAks6wFQACgkQmOOfHg372QSioQCbBjDWAb1mSDKW4G3yOD1Igdz0 n2EAn19xGp4/HwJfj2Mgwqt8Sh9gjfCL =v0qa -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Archive Search Engine
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bill Marvin wrote: It would be very helpful if there was a search engine for the sqlite-user mailing list archive. Gmane does a pretty good job and gives you other ways of reading the list (eg NNTP/news, RSS): http://gmane.org/info.php?group=gmane.comp.db.sqlite.general To do a search, click on Searching on the left and put gmane.comp.db.sqlite.general in the group field. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAks6wVEACgkQmOOfHg372QSF3wCfTRCm7bhoV9pFxGgFVwyEQLhV 7DAAnRmYeHHVaxrQ9lx8ZcGxaQ9hT/l5 =P74A -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Archive Search Engine
On 30 Dec 2009, at 2:56am, Roger Binns wrote: Bill Marvin wrote: It would be very helpful if there was a search engine for the sqlite-user mailing list archive. Gmane does a pretty good job and gives you other ways of reading the list (eg NNTP/news, RSS): http://gmane.org/info.php?group=gmane.comp.db.sqlite.general To do a search, click on Searching on the left and put gmane.comp.db.sqlite.general in the group field. Another way is to just do a web search for whatever you want. I use this a lot. Sometimes it comes up with a post to this list, but other times someone I've never heard of came up with a good solution and posted it to their blog or something. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fts3 issue with tokenizing of content during a query
On Dec 30, 2009, at 6:25 AM, Nick Hodapp wrote: Hi - I'm using sqlite 3.6.21 with this patchhttp://www.sqlite.org/src/ci/6cbbae849990d99b7ffe252b642d6be49d0c7235 , which I found in this forum a few weeks ago. I'm also using a custom tokenizer which I wrote. My scenario is this: I am storing XHTML in the database, and I want to FTS-enable this content. I only want to index the text contained within the XHTML elements, not the element names or attributes. (e.g. dont- index this=or thisindex this/...) My tokenizer skips over element names and attributes, then delegates the element textual content to the Porter tokenizer. On return from the Porter tokenizer, I correct the token offset and length values to be the actual offsets within the document (Porter tokenizer doesn't ever see the whole document, just a string within a tag). I didn't want to ship my tokenizer with my app for two reasons. 1 - I wrote it using an API not available to my client app, 2 - it doesn't make sense because on the client the user will be entering search terms that aren't surrounded by xml tags, which is what my tokenizer expects. Instead, my client registers a tokenizer with the same name as my custom tokenizer, but in fact it is registering a copy of the porter tokenizer. I expected this to work fine - and it appeared to, until I discovered that it was pulling out text in some of the xml attributes - which shouldn't be indexed. It turns out that FTS3 is re-tokenizing the content (not just the search term) on the client (using my copy of the Porter tokenizer) and returning those results. I don't understand why - is this a bug or is this normal behavior? It runs the tokenizer on returned documents as part of the snippet() or offsets() function. The full-text index doesn't actually store the byte offsets returned by the tokenizer xNext() call, just the token number. So you have to re-tokenize to figure out the byte offsets required by snippet() or offsets(). Dan. I expected the fts index to retain all of the token offsets/sizes such that they wouldn't have to be recomputed on the client. My workaround is to port my tokenizer so that it runs on the client, and to wrap search terms in dummy xml tags dummylike this/dummy. But I feel I shouldn't have to do this... Any feedback appreciated... Nick Hodapp ___ 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] BUG Report on sqlite 3.6.20 Error in SQL parser between sqlite3.3.4 and sqlite3.6.20
On Dec 30, 2009, at 4:52 AM, Wilson, Ronald wrote:
I get the same error in 3.6.18, so probably the same solution
applies in 3.6.20. I got the query to work with a sub-select.
Changed between 3.6.6 and 3.6.7 from the looks of things.
SQLite version 3.6.18
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite BEGIN TRANSACTION;
sqlite CREATE TABLE basica(
... x,
... y,
... suma
... );
sqlite INSERT INTO basica VALUES('Austria','1996-03',5904.0);
sqlite INSERT INTO basica VALUES('Austria','1996-04',21904.0);
sqlite INSERT INTO basica VALUES('Germany','1996-03',10545.0);
sqlite INSERT INTO basica VALUES('Germany','1996-04',13687.0);
sqlite INSERT INTO basica VALUES('USA','1996-03',21814.0);
sqlite INSERT INTO basica VALUES('USA','1996-04',13108.0);
sqlite
sqlite CREATE TABLE groupLimX(
... x,
... sumaXs
... );
sqlite INSERT INTO groupLimX VALUES('USA',305843.0);
sqlite INSERT INTO groupLimX VALUES('Germany',258820.0);
sqlite INSERT INTO groupLimX VALUES('Austria',140668.0);
sqlite
sqlite CREATE TABLE groupLimY(
... y,
... sumaYs
... );
sqlite INSERT INTO groupLimY VALUES('1996-04',113818.0);
sqlite INSERT INTO groupLimY VALUES('1996-03',102947.0);
sqlite COMMIT;
sqlite
sqlite .header on
sqlite SELECT * FROM groupLimY INNER JOIN groupLimX;
y|sumaYs|x|sumaXs
1996-04|113818.0|USA|305843.0
1996-04|113818.0|Germany|258820.0
1996-04|113818.0|Austria|140668.0
1996-03|102947.0|USA|305843.0
1996-03|102947.0|Germany|258820.0
1996-03|102947.0|Austria|140668.0
sqlite CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN
groupLimX;
sqlite SELECT * FROM mia LEFT JOIN basica USING (y, x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN
basica USING (y,x);
SQL error: cannot join using column y - column not present in both
tables
sqlite SELECT * FROM (select x, sumaXs, y, sumaYs from groupLimY
INNER JOIN groupLimX) LEFT JOIN basica USING (y,x);
x|sumaXs|y|sumaYs|suma
USA|305843.0|1996-04|113818.0|13108.0
Germany|258820.0|1996-04|113818.0|13687.0
Austria|140668.0|1996-04|113818.0|21904.0
USA|305843.0|1996-03|102947.0|21814.0
Germany|258820.0|1996-03|102947.0|10545.0
Austria|140668.0|1996-03|102947.0|5904.0
sqlite
sqlite SELECT * FROM (select * from groupLimY INNER JOIN groupLimX)
LEFT JOIN basica USING (y,x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite
Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com
HARRIS CORPORATION | RF Communications Division
assuredcommunications(tm)
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of javaj1...@elxala.com
Sent: Tuesday, December 29, 2009 8:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] BUG Report on sqlite 3.6.20 Error in SQL parser
between
sqlite3.3.4 and sqlite3.6.20
Hello,
I detect this problem because a program using sqlite command line
works
on sqlite.3.3.4 but
it does not anymore using sqlite3.6.20
PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in
previous version sqlite3.3.4 OK)
OR Error in SQL parser between
sqlite3.3.4 and sqlite3.6.20
TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes
HOW TO REPRODUCE IT:
Execute following batch on both versions of sqlite
sqlite3 Fails3.6.20.sql
when using 3.6.20 we get the unjustified error
Best regards,
Alejandro
Fails3.6.20.sql--
BEGIN TRANSACTION;
CREATE TABLE basica(
x,
y,
suma
);
INSERT INTO basica VALUES('Austria','1996-03',5904.0);
INSERT INTO basica VALUES('Austria','1996-04',21904.0);
INSERT INTO basica VALUES('Germany','1996-03',10545.0);
INSERT INTO basica VALUES('Germany','1996-04',13687.0);
INSERT INTO basica VALUES('USA','1996-03',21814.0);
INSERT INTO basica VALUES('USA','1996-04',13108.0);
CREATE TABLE groupLimX(
x,
sumaXs
);
INSERT INTO groupLimX VALUES('USA',305843.0);
INSERT INTO groupLimX VALUES('Germany',258820.0);
INSERT INTO groupLimX VALUES('Austria',140668.0);
CREATE TABLE groupLimY(
y,
sumaYs
);
INSERT INTO groupLimY VALUES('1996-04',113818.0);
INSERT INTO groupLimY VALUES('1996-03',102947.0);
COMMIT;
.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT
