[sqlite] Why are strings in hexadecimal notation always blobs?
Hello, it seems that "Lothar" is stored as a TEXT value but when i store X'4C6F74686172' it is a BLOB. What is the reason for it? The dynamic typing should look at the data content and not the literal form. So i would consider this a bug. For convenience i tried to always use hex quotes no matter what data is inside. But this is bad for a GUI Frontend i've written which is displaying data differently if 'sqlite3_column_type' returns 4. -- Best regards, Lothar Scholz mailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Why are strings in hexadecimal notation always blobs?
Hello Igor, Friday, January 18, 2008, 8:09:02 PM, you wrote: IT> Lothar Scholz IT> wrote: >> it seems that "Lothar" is stored as a TEXT value but when i store >> X'4C6F74686172' it is a BLOB. >> What is the reason for it? IT> Same reason 1 is an integer literal but '1' is a string literal. IT> X'4C6F74686172' is a blob literal. Sorry as far as i understand the dynamics of datatypes they should depend on the bytes that are passed but not on the literal that is used for notation inside a textual SQL statement. Another question, how would you realiable represent contrl characters in the range 1-31 in a string? It is not really good to add them as plain code in text files and SQLite does not have C like backslash quoting. Especially the automatic %R%N->%N conversions might be a huge problem. And i don't think we should restrict the TEXT data type to anything more then non zero bytes. IT> What do you mean, data content? How is it supposed to know that a IT> particular sequence of bytes is supposed to represent a string, without IT> the help of mind-reading hardware? After all, you don't expect the IT> number 48 to be magically interpreted as a string '0'. You don't, right? Well if it looks like a number it is a number. If it does not look like a number it is either a TEXT or if it contains zero (or maybe non text control characters others then usually defined \f \v \r \n) it is a BLOB. This would make sense for me. IT> Use parameterized queries and approprite sqlite3_bind_* calls. This way IT> you unambiguously control the type of the value. I have to use for portability reasons UTF-8 sql text files. So i can't use a programming langauge API. -- Best regards, Lothar Scholzmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is there a difference between NULL und zero byte length BLOB/TEXT?
Hello, The last question for today: Is there a difference between NULL und zero byte length BLOB/TEXT? Need this information for my SQLite GUI Frontend. -- Best regards, Lothar Scholz mailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Why are strings in hexadecimal notation always blobs?
Hello Igor, Saturday, January 19, 2008, 12:02:15 AM, you wrote: IT> You misunderstand the dynamics of datatypes then. Yes maybe. With the current implementation i really do not understand the point anyway neither with my understanding nor with yours. IT> Wait a minute. Didn't you just say that you _want_ text strings to be IT> able to contain control characters? So what's left for the BLOB then? IT> Suppose I want to insert, say, a bitmap image into the database - as a IT> BLOB, naturally. You are saying that, if it doesn't just happen to IT> contain at least one zero byte, it will have to go in as a string. So if IT> it has a black pixel, it's a BLOB. If it doesn't have any black pixels, IT> it's a string. Does this really make sense to you? Yes the only reason left for a BLOB would be a containing zero byte and any illegal UTF8 sequence of bytes. For me it looks like the introduction of the current logic is just for backward compatibility that embedded 0 characters are not allowed in a string. IMHO it would be cleaner if we conceptionally only have BLOBS and check for other datatypes on demand maybe with some caching. Exactly what TCL is doing it when it assumes everything is a string and (since version 7.X )we got the cached integer or double values. But well i can code around this like usual but i will bring up the topic again if there is a discussion about a 4.0 release. -- Best regards, Lothar Scholzmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is the precise definition of an identifier?
Hello, I have to write an sqlite syntax highligher for an editor and at the moment i use the following token BNF syntax. ident := '_' | letter ( letter | '_' | digit )* Is this correct? I would appreciate an additional section in the documentation that defines the few missing low level productions. -- Best regards, Lothar Scholz mailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: What is the precise definition of an identifier?
Hello Igor, Saturday, January 19, 2008, 1:26:26 AM, you wrote: IT> In addition to that, in SQL any string enclosed in double quotes is also IT> an identifier. E.g. these are valid statements: IT> Further, SQLite also interprets strings enclosed in square brackets as IT> identifiers (for compatibility with MS Access, I believe): Okay thanks. Do [ and " literals have any escaping for example "" in the same way the single quote string literal is doing it. And just to ask it before i miss something: The double single quotes '' is the only escaped character in SQLite string literals. Correct? -- Best regards, Lothar Scholzmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] No way to dump a database with large BLOB's ?
Hello, Dumping a database with images i run into the 1 million byte per SQL statement limit. I thought that the usual way to backup a database is the sqlite.exe and dump and eval method. Shouldn't this limit be dynamic instead of hard wired into a compile constant? -- Best regards, Lothar Scholz mailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Why does the FAQ example use 2 copies for adding/deleting a column
Hello, The example for question 11 in the FAQ has this code for dropping an existing table column 'c' BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 SELECT a,b FROM t1_backup; DROP TABLE t1_backup; COMMIT; This is copying the whole table twice. Is there any reason why it shouldn't be: BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; ALTER TABLE t1_backup RENAME TO t1; COMMIT; Or is this just that ALTER TABLE RENAME did not exist when the FAQ was created? -- Best regards, Lothar Scholz mailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: [unclassified] [sqlite] Re: Why does the FAQ example use 2 copies for adding/deleting a column
Hello Igor, Tuesday, January 29, 2008, 9:06:56 AM, you wrote: IT> Lothar Scholz IT> wrote: >> This is copying the whole table twice. Is there any reason why it >> shouldn't be: >> >> BEGIN TRANSACTION; >> CREATE TEMPORARY TABLE t1_backup(a,b); >> INSERT INTO t1_backup SELECT a,b FROM t1; >> DROP TABLE t1; >> ALTER TABLE t1_backup RENAME TO t1; >> COMMIT; IT> t1_backup is a temporary table. You want a permanent table in the main IT> database. ALTER TABLE cannot magically move a table from temp database IT> to main - the two databases are physically in two different files. Oopps, you are right but this wasn't really my point, so please substitute it with: CREATE TABLE t1_backup(a,b) The code is also very dangerous because if there are any problems, for example running out of space, then all the data is lost. Transactions do not rewind drop table statements and other schema manipulations. -- Best regards, Lothar Scholzmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite database on a certain high-performance "SSD"
Hello Mark, Tuesday, September 22, 2009, 3:53:48 AM, you wrote: M> I've currently got a loaner high-performance flash-based "SSD" (let's M> just say it doesn't connect to any disk controllers) that I'm testing M> for performance. I've run my application against it, and I believe that M> I should see numbers MUCH higher than I do. When I run my test app on a M> normal SATA 7200 RPM disk, I get a certain performance, and on the "SSD" M> I get about 1/10th that speed. On an array of SAS disks I get numbers M> that are about 5x faster than my SATA disk, so my software itself isn't M> (I believe) the bottleneck. M> I'm wondering if anyone has any tips for "optimizing" for this sort of M> storage solution. Throw it into the trash bin and buy a new one which has a 3rd generation controller and at least 64MB fast cache. The old JMicron controller that many low cost SSD still use was developed for Flash USB sticks. With modern SSD like the latest Samsung should give you at least the same performance as the SATA. If it gets better depends on file size and cache. Are you sure that the SAS RAID Controller is not keeping everything in the controller cache? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?
It is driving me crazy. I'm working on a web spider where a table holds the downloaded webpage. It seems that a select "SELECT count(*) WHERE NOT text IS NULL" requires that the complete text column is loaded. With a stored LOB this results in crazy performance. Is this optimized in later versions of SQLite (i'm using a 3.4.x which is about 2 years old). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?
Hello Artur, Sunday, February 28, 2010, 12:44:00 AM, you wrote: AR> "Select count(id) from table where text!='' or text!=NULL" is the same, AR> right? Using text!='' obviously needs to load the text column. I just tried it nevertheless and aborted after 5 min (brute force reread of the whole file would be by the way much much faster then the 5min for a 10 GB file). AR> As i see you want to count all rows, right? I don't know if it is faster AR> to only count one column instead of *, but I using it the one-column-count AR> way. Perhaps this helps too? I never thought about this and always considered it syntax-sugar for the count function. And it seems i'm right, no change either. So it's maybe time to say byebye to SQLite and move on to a real database system. -- Best regards, Lothar Scholzmailto:sch...@scriptolutions.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
