Re: [sqlite] Sqlite3.exe command line tool - winXP
On 7 Dec 2009, at 7:05am, dave lilley wrote: sqlite3 path to the DB dbfile I get put to the command line. How many bytes long is this file 'dbfile' ? typed in the following and below is the results. sqlite .show echo: off explain: on headers: on mode: explain nullvalue: output: stdout separator: | width: 4 13 4 4 4 13 2 13 sqlite .schema ?table? accounts; sqlite .schema ?accounts?; sqlite So I don't know if it's me not understanding the syntax or not. I have 5 tables one of them *is* accounts. Type '.schema' without any quotes or question-marks. Do you get any response to that ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] char,ascii function in sqlite
On 7 Dec 2009, at 5:56am, greensparker wrote: i want to check the first character should not be an special character. [by before insert trigger] how to do it? i thought of using ascii function but i didnt find in sqlite. substr(X,Y,Z) will get you the first character. I'm not sure what you mean by 'special', but the core functions are here: http://www.sqlite.org/lang_corefunc.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_exec() returns SQLITE_OK but Database showsdifferent result
souvik.da...@wipro.com wrote: I am accessing the same database from two different processes. From one of the process , I am able to create tables in runtime but when I am trying to create a table from another process in runtime on the same DB I am not able to create so. The strange part is that I find sqlite3_exec() is returning retCode as SQLITE_OK. But then , when I am going and checking the DB, I am not able to see the table. There are two common causes for this. 1) You are opening a different file than the one you think you are opening. E.g. you are using a relative path to the file, and the workding directory is not what you expect it to be. 2) You are starting an explicit transaction (see BEGIN) and forgetting to commit it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL selecting in two related tables?
I had responded to Jean-Denis Muys as follows: select M.title from Magazine as M join ( select distinct issn, issues from subscription ) as SubscriptionVariants on SubscriptionVariants.issn = M.issn order by M.title, SubscriptionVariants.issues But I neglected to add the issues column to the outer select; it should have read: select M.title, SubscriptionVariants.issues from Magazine as M join ( select distinct issn, issues from subscription ) as SubscriptionVariants on SubscriptionVariants.issn = M.issn order by M.title, SubscriptionVariants.issues Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_exec() returns SQLITE_OK but Database showsdifferent result
1) You are opening a different file than the one you think you are opening. E.g. you are using a relative path to the file, and the workding directory is not what you expect it to be. 2) You are starting an explicit transaction (see BEGIN) and forgetting to commit it. 3) You are starting to execute some SELECT statement and don't finish it (do not execute sqlite3_reset or sqlite3_finalize). It prevents SQLite from committing anything no matter if you issue COMMIT explicitly or rely on auto-committing. Pavel On Mon, Dec 7, 2009 at 7:33 AM, Igor Tandetnik itandet...@mvps.org wrote: souvik.da...@wipro.com wrote: I am accessing the same database from two different processes. From one of the process , I am able to create tables in runtime but when I am trying to create a table from another process in runtime on the same DB I am not able to create so. The strange part is that I find sqlite3_exec() is returning retCode as SQLITE_OK. But then , when I am going and checking the DB, I am not able to see the table. There are two common causes for this. 1) You are opening a different file than the one you think you are opening. E.g. you are using a relative path to the file, and the workding directory is not what you expect it to be. 2) You are starting an explicit transaction (see BEGIN) and forgetting to commit it. Igor Tandetnik ___ 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] char,ascii function in sqlite
As Simon said use substr() to get first character and use cast(X'FF' as text) to convert some hexadecimal character code to symbol (in my example the code is FF = 255). But there's no way to convert character into its code, so you cannot do any arithmetics with it though you probably don't need it because comparison seems to be enough and it's perfectly good with text data type. Pavel On Mon, Dec 7, 2009 at 5:24 AM, Simon Slavin slav...@bigfraud.org wrote: On 7 Dec 2009, at 5:56am, greensparker wrote: i want to check the first character should not be an special character. [by before insert trigger] how to do it? i thought of using ascii function but i didnt find in sqlite. substr(X,Y,Z) will get you the first character. I'm not sure what you mean by 'special', but the core functions are here: http://www.sqlite.org/lang_corefunc.html Simon. ___ 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] sqlite3_exec() returns SQLITE_OK but Databaseshowsdifferent result
Hi Pavel, Thanks a lot. You are right. I missed out a sqlite3_finalize() and that prevented the commit. Regards, Souvik -Original Message- From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov Sent: Mon 12/7/2009 6:15 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite3_exec() returns SQLITE_OK but Databaseshowsdifferent result 1) You are opening a different file than the one you think you are opening. E.g. you are using a relative path to the file, and the workding directory is not what you expect it to be. 2) You are starting an explicit transaction (see BEGIN) and forgetting to commit it. 3) You are starting to execute some SELECT statement and don't finish it (do not execute sqlite3_reset or sqlite3_finalize). It prevents SQLite from committing anything no matter if you issue COMMIT explicitly or rely on auto-committing. Pavel On Mon, Dec 7, 2009 at 7:33 AM, Igor Tandetnik itandet...@mvps.org wrote: souvik.da...@wipro.com wrote: I am accessing the same database from two different processes. From one of the process , I am able to create tables in runtime but when I am trying to create a table from another process in runtime on the same DB I am not able to create so. The strange part is that I find sqlite3_exec() is returning retCode as SQLITE_OK. But then , when I am going and checking the DB, I am not able to see the table. There are two common causes for this. 1) You are opening a different file than the one you think you are opening. E.g. you are using a relative path to the file, and the workding directory is not what you expect it to be. 2) You are starting an explicit transaction (see BEGIN) and forgetting to commit it. Igor Tandetnik ___ 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 Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache size tuning
Maybe 'pragma cache_size'? Pavel On Fri, Dec 4, 2009 at 10:05 PM, Richard Klein richard.kl...@schange.com wrote: Does SQLite provide any tools to help the developer tune the database cache size? Thanks, - Richard Klein ___ 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] char,ascii function in sqlite
hi , im using sqlite3 in debain i want to check the first character should not be an special character. [by before insert trigger] how to do it? i thought of using ascii function but i didnt find in sqlite. No such function is part of the SQLite core. But I wrote an extension offering that function, and a lot more. Drop me a mail if you're interessed in the C source. Cheers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Putting images into SQLite.
Hello Bruce, Monday, December 7, 2009, 1:20:10 AM, you wrote: BR On Dec 6, 2009, at 6:17 PM, Jean-Christophe Deschamps wrote: I was simply replying to the OP's actual question: From what I read, it is necessary to have a programmatic interface to put images into a database. True? So, no, it isn't _necessary_ (but recommended). BR What is programatically BR How, in any meaningful way, is this different than running a BR shell command (program of an extremely brief size)? BR ___ BR sqlite-users mailing list BR sqlite-users@sqlite.org BR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Does the shell command have an interface for specifying on disk objects to insert as blobs? Might be a neat addition. INSERT INTO BLA.'file:/tmp/bbies.jpg'... I do it programmatically. I wrote an image interface class for inserting, deleting and searching on hashes, filenames and meta-data. Before the parameterized inserts were the norm, I used to encode my blobs before insert. Using the newer interface, it's pointless to encode. I crank the page size up to max for my image databases too. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Effect of VACUUM when very low on disk space
Hi all, I'm currently writing some code that attempts to keep an SqLite database file below a certain file size (the embedded PC it is running on has a wonderful side effect that when the disk runs out of disk space, it blue-screens Windows and you can't boot the device after that - how helpful!). When the file exceeds a certain size, I DELETE a specific number of records, then VACUUM the file to get the size back below required limits. This works fine, however what happens to the VACUUM command if there is insufficient disk space for SqLite to write out the cleaned up copy of the database? I assume it will fail, but the documentation doesn't specifically say how much disk space is required during a VACUUM operation. The newly vacuumed file's size should end up being equal to or less than the existing file's size, so I assume I'll need at least the current database's size of disk space free, but will it ever require more space than that to perform the VACUUM (e.g. from other temporary files)? Also, is there any SqLite command I can use to get the database file's size? I'm currently using the Win32 API call GetFileSizeEx() to get the file's size which works fine, but does SqLite itself know the database file's size when it has the database open? It would seem more gracefully coded if I didn't have to open a separate handle to the database file just to get the file size out. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Putting images into SQLite.
What is programatically How, in any meaningful way, is this different than running a shell command (program of an extremely brief size)? I took the OP's phrasing to mean that he needed a way to do it with e.g. command-line available programs, but in any case without having to use a programming language to develop an ad hoc code to do it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Putting images into SQLite.
On Mon, Dec 7, 2009 at 9:15 AM, Jean-Christophe Deschamps j...@q-e-d.org wrote: What is programatically How, in any meaningful way, is this different than running a shell command (program of an extremely brief size)? I took the OP's phrasing to mean that he needed a way to do it with e.g. command-line available programs, but in any case without having to use a programming language to develop an ad hoc code to do it. Yes, that seems like a reasonable interpretation of the OP's question, one I also understood. One thing I don't understand though, Jean-Christophe, even though one can enter base64 encoded images into the db via the sqlite shell, how does one create the base64 encoded images? One would need a way to do that on the shell command line no? Not to mention, how inconvenient it would be to do for any meaningful image, as you can see from your own 2x1 pixels image example. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, Wisconsin, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG: The byte order mark problem in fields with ICU collation
Hello! The SQLite in the field with ICU collation does store the BOM in the start of string. This bahaviour produce some difficulty resolving problems. select length(name),x.name, hex(x.name) from (select distinct name from const_telephony_direction where delete_date IS NULL) as x; ... 17|sovintel-konmark|EFBBBF736F76696E74656C2D6B6F6E6D61726B ... The hex sequence EFBBBF is UTF-8 byte order mark. The right value is 16|sovintel-konmark|736F76696E74656C2D6B6F6E6D61726B .schema const_telephony_direction CREATE TABLE const_telephony_direction ( ... name text collate russian not null, -- группа направлений ... ); pragma collation_list; 0|russian 1|NOCASE 2|RTRIM 3|BINARY 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] Putting images into SQLite.
Hi Puneet, Yes, that seems like a reasonable interpretation of the OP's question, one I also understood. One thing I don't understand though, Jean-Christophe, even though one can enter base64 encoded images into the db via the sqlite shell, how does one create the base64 encoded images? One would need a way to do that on the shell command line no? Not to mention, how inconvenient it would be to do for any meaningful image, as you can see from your own 2x1 pixels image example. But the hex isn't base64 encoded at all: it's merely a hex byte after byte linear dump, something that is obvious to get without any dependancy on SQLite or the environment. BTW, having images stored direct in hex blobs without any convoluted encoding allows smart database managers to display the images directly in resultset grid, which I find very convenient. I didn't mean to enforce this as a convenient way to handle routine operation, except if the particular situation demands it. I fully agree with your remark that it isn't very practical, but I've slowly discovered that SQLite is so flexible that it shows up in many environments where one wouldn't expect a database layer to simply exist in the first place. I've no clue as why the OP asked that and what his actual constraints really are, but if he needs a text only, command-line only, no-specific-program way to have his job done, then SQLite is still his friend and not a stumbling block. That's all of SQLite glory to offer workable solutions to incredingly strange situations! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Putting images into SQLite.
On Mon, Dec 7, 2009 at 9:55 AM, Jean-Christophe Deschamps j...@q-e-d.org wrote: Hi Puneet, Yes, that seems like a reasonable interpretation of the OP's question, one I also understood. One thing I don't understand though, Jean-Christophe, even though one can enter base64 encoded images into the db via the sqlite shell, how does one create the base64 encoded images? One would need a way to do that on the shell command line no? Not to mention, how inconvenient it would be to do for any meaningful image, as you can see from your own 2x1 pixels image example. But the hex isn't base64 encoded at all: it's merely a hex byte after byte linear dump, something that is obvious to get without any dependancy on SQLite or the environment. I still don't understand your strategy above. Where am I, the user, supposed to get the hex byte after byte linear dump from? Imagine, I have a photograph of you called jcd.jpg sitting on my hard disk at ~/pics/jcd.jpg and I want to insert it as a blob into CREATE TABLE pics (id INTEGER PRIMARY KEY, pic BLOB); via the sqlite3 shell. Could you please give me a step by step on how to do that? Not via a small program written in a programming language, but only via the sqlite3 shell, as both you and I agree that was the implied intent of the OP's question. BTW, having images stored direct in hex blobs without any convoluted encoding allows smart database managers to display the images directly in resultset grid, which I find very convenient. I didn't mean to enforce this as a convenient way to handle routine operation, except if the particular situation demands it. I fully agree with your remark that it isn't very practical, but I've slowly discovered that SQLite is so flexible that it shows up in many environments where one wouldn't expect a database layer to simply exist in the first place. I've no clue as why the OP asked that and what his actual constraints really are, but if he needs a text only, command-line only, no-specific-program way to have his job done, then SQLite is still his friend and not a stumbling block. That's all of SQLite glory to offer workable solutions to incredingly strange situations! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, Wisconsin, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3.exe command line tool - winXP
My mistake - I meant: sqlite .schema if sqlite is telling you that the table doesn't exist, the .schema command should tell you what tables exist. RW 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 dave lilley Sent: Monday, December 07, 2009 2:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite3.exe command line tool - winXP Well I've tried the following sqlite3 path to the DB dbfile I get put to the command line. typed in the following and below is the results. sqlite .show echo: off explain: on headers: on mode: explain nullvalue: output: stdout separator: | width: 4 13 4 4 4 13 2 13 sqlite .schema ?table? accounts; sqlite .schema ?accounts?; sqlite So I don't know if it's me not understanding the syntax or not. I have 5 tables one of them *is* accounts. thanks for your reply. 2009/12/7 Wilson, Ronald rwils...@harris.com what tables do you have? sqlite schema; from my mobile 434.851.1612 On Dec 5, 2009, at 11:31 PM, dave lilley dgl...@gmail.com wrote: I have been trying to (without much joy) get a list of rows from a table within an sqlite3 DB. I've been starting from the CMD prompt sqite3 DB filename here dropping into sqlite3 cmd prompt i type in sqlite select * from accounts; SQL error: no such table: accounts sqlite Now I've used SQLite Database manager 3.1 to create the tables (at least 5 in the one db) and have been able to access the tables until i started to entry text for a blob field and now the DB manager falls over! I am using ruby 1.8.6 as my dev language an suppose i should put it to that community BUT my issue of not being able to generate a list of data from an sql statement when i know the names of the tables does belong here. any help would be appreciated. dave. ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Murmurhash2 function as new extension
Hello!
See
http://mobigroup.ru/files/sqlite-ext/murmurhash/
and a few words on russian
http://geomapx.blogspot.com/2009/12/murmurhash-20.html
The examples:
select murmurhash(1,2,3);
1074609160
sqlite select murmurhash('hello',99);
3350841100
sqlite select murmurhash('hi!',1);
2372833641
sqlite select murmurhash('q',0);
2030783509
sqlite select murmurhash('test');
1026673864
sqlite select murmurhash();
P.S. The inet extension is updated some times ago. I recommend
for all users to get the new version:
http://mobigroup.ru/files/sqlite-ext/md5/
The tests set is extended, see ipv4-ext.sql
The versioning extension for tables history logging and versioning/replication
will be in production soon. I did change the base concept for total
simplification
and more usability.
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] Putting images into SQLite.
Could you please give me a step by step on how to do that? Not via a small program written in a programming language, but only via the sqlite3 shell, as both you and I agree that was the implied intent of the OP's question. Sorry, I'm no vxWorks, Unix, Linux, MacOS, Windows, AS400, Symbian, you_name_it ... guru. I just can't cite you a portable combination of command-line tools to do that. I took the question differently, don't read me backwards. I didn't think of manual input on individual sqlite3 command line, but rather building some SQL input file. That one has to use a complex combination of dump, grep, awk, sed, emacs, whatever_tool_exists, to have lines like: insert into pics(id, pic) values ($id$, x'$pic$'); built and replacing $id$ by the required id and $pic$ by a hex dump doesn't seem unfeasible. I took the question to mean: Is it necessary to write a dedicated SQLite program to insert pics or other binary data into an SQLite base. My answer was that no, suitable combination of command-line style tools can do it, with a short example of what needs to be done. That a particular environment doesn't offer a ready to use facility and one has to make a generic utility corresponding to the pseudo-code: open argument filename as binary foreach byte b fprintf to stdout b as %02x close files is not in my view SQLite development. No SQLite header needed for that, no SQLite API or wrapper. If ever such utility doesn't readily exist and can't be simulated on a particular platform is something else. That utility can be used on any base and does only depend on the OS used. Look, to come up with a working example, here's what I did: Local $h = FileOpen(D:\2009-12-07_024519.bmp, 16) Local $v = FileRead($h) FileClose($h) ConsoleWrite(x' Hex($v) ' @lf) Then I copied the console output and used it inside my favorite SQLite manager thusly: insert into t (d) values (x'424D3E003600280002000100010018000800C40EC40E0001'); I wrote the four line utility above in [Windows] AutoIt just because I was lazy enough to question myself about what available command-line utility or combination thereof could produce the same result quickly, DOS, Windows or MinGW or else. Now, just by curiosity, I just tried to dig out a _really_ dusty dump.exe dated ... 24/10/1987 from the brave DOS time and it produces: D:\dump test.bmp H 42 4D 3E 00 00 00 00 00 00 00 36 00 00 00 28 00 'BM...6...(.' 0010H 00 00 02 00 00 00 01 00 00 00 01 00 18 00 00 00 '' 0020H 00 00 08 00 00 00 C4 0E 00 00 C4 0E 00 00 00 00 '..D...D.' 0030H 00 00 00 00 00 01 FF FF FF FF FF FF 00 00 '..' Name and contents differ because I dropped the initial test file from yesterday. Nevertheless, I believe it's easy to use a less prehistoric dump (or equivalent) then grep (or equivalent) the output to produce what's needed. That subsequent regexp should be considered programmatic or not is up to the reader. But then, even entering _anything_ on any shell of any OS can be considered programmatic, whatever that means. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Putting images into SQLite.
As others have said, there are lots of ways to store the image data directly in the DB with BLOBs, encoding, etc. Alternatively, you could store the pics separate from the DB and just store the path to the pic file in the DB. -Shane On Sun, Dec 6, 2009 at 8:35 PM, Ted Rolle, Jr. ster...@gmail.com wrote: From what I read, it is necessary to have a programmatic interface to put images into a database. True? --- 3.14159265358979323846264338327950 Let the spirit of pi spread 2884197169399375105820974944592307 all around the world! 8164062862089986280348253421170679 http://pi314.at PI VOBISCUM! ___ 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] Effect of VACUUM when very low on disk space
On 7 Dec 2009, at 2:27pm, Nick Shaw wrote: When the file exceeds a certain size, I DELETE a specific number of records, then VACUUM the file to get the size back below required limits. This works fine, however what happens to the VACUUM command if there is insufficient disk space for SqLite to write out the cleaned up copy of the database? I assume it will fail, but the documentation doesn't specifically say how much disk space is required during a VACUUM operation. The newly vacuumed file's size should end up being equal to or less than the existing file's size, so I assume I'll need at least the current database's size of disk space free, but will it ever require more space than that to perform the VACUUM (e.g. from other temporary files)? Because there is no documentation about how these things work, even if we answered your question, the answer might change in a future version. For example, VACUUM might go from rewrite-in-place to writing a fresh copy of the entire file. It should definitely do this if it notices database corruption while VACUUMing. In similar situations to yours I have instead created a huge pointless file to take up disk space pointlessly. The routine that recovers data deletes this huge file (giving it guaranteed free space to work) then does the recovery, then creates a huge dummy file again. This technique means that not only will your automated system have a chance to work but you will also be able to free up enough space to do emergency recovery stuff manually. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Alexey Pechnikov pechni...@mobigroup.ru wrote: The SQLite in the field with ICU collation does store the BOM in the start of string. What do you mean by SQLite stores the BOM? Are you saying that you are passing in a string without the BOM, and SQLite spontaneoulsy manufactures one? Double-check your application - I suspect you'll find that you are actually passing strings with BOM to SQLite to begin with, and it just stores them faithfully. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.21
SQLite version 3.6.21 is now available on the SQLite website: http://www.sqlite.org/ SQLite version 3.6.21 is a monthly maintenance release of SQLite. Upgrading from prior versions is optional. Version 3.6.21 features an enhancement to the sqltie3_trace() interface such that the values of bound parameters are inserted into the SQL output emitted by sqlite3_trace(), making application diagnostics easier. Version 3.6.21 also features general performance improvements and a rework of the FTS3 full-text search extension for improved robustness and performance. SQLite version 3.6.21, as all versions of SQLite since 3.6.17, has been tested to 100% MC/DC and branch coverage using multiple independently developed test harnesses. As always, please let us know if you encounter any difficulties with this or any other SQLite release. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite bug report - large databases only - 'database or disk is full'
SQLite bug report
Summary:
--
error message:
Error: near line 2: database or disk is full
It happens with plenty of disk space available and with 'unlimited' database
size.
It does not happen on all systems. It does not happen on small databases.
Details:
--
The error occurs on
- windows server 2003 SP2 64bit, windows server 2009 64bit
- windows vista, windows 7 rc1, windows 7 - all 64-bit
The error does _not_ occur on
- windows server 2003 SP2 32bit, windows xp sp3 32 bit
SQLite3 versions affected: all the ones released in 2009, possibly all the
earlier ones, too.
The bug was 'distilled' with the reproduction described below.
Reproduction
--
We (1) create a table in the database, (2) create an input file; (3) import the
input file into the table mulltiple times.
Using the files attached below the error did occur during the third/fourth
import, at database size around 22-28gb.
Additional information and observations
1. This is not a new bug - it's been around for the last year, probably two -
but it was difficult to spot and isolate.
2. The problem is 'touchy' - with different random seed in the generated table
the problem may occur at 45gb db size instead of 20-30gb. When I tried to use,
as input data, the same line repeated millions of times - the problem did not
occur at all - tests were aborted by me at 160-260 gb database size.
3.The problem does _not_ depend _purely_ on the data being imported. The
reproduction script loads the same data set multiple times and fails on the
third/fourth time - so it is the database size which triggers it.
4. When the input file is smaller (1gb instead of 8gb), the problem still
occurs in the 20-30gb database size range - so there is nothing magical about
the number of imports.
5. The test database here is created with pragma page_size = 32768. The same
error message occurs for other page sizes and at various cache sizes.
6. In some other tests (not using to the scripts here, with different data) the
import was good (about 20gb size) but an attempt to create an index on the
imported data resulted in the same error diagnostic - after about 30mins of
running.
7. This is not an SQLITE3 problem - when using .NET wrapper for SQLite or using
any of the admin tools - the problems occurs in the same area, even though
messages are not reported the same way by these tools [and I am getting
sometimes 'database image in malformed' after such tools].
Scripts to reproduce the bug
--
++
== awk program to produce the table - save as: t1b.w
BEGIN {
srand(13) # assure all tests have the same data
for (i=0;iCOUNT;i++) {
str = +i , strftime(%Y-%m-%d %H:%M:%S) , strftime(%Y-%m-%d %H:%M:%S)
, rand()*10 , rand()*100 , rand()*1000 , rand()*1 ,
rand()*100 , rand()*10 , rand()*100 , rand()*1000 , rand()*1
, rand()*10 , rand()*100 , rand() , rand()*100 , rand()*1000
, rand()
print str
} # END FOR
} # END BEGIN
++
== sql script to prepare database - save as: prepdb.sql
pragma page_size=32768;
pragma default_cache_size=1;
CREATE TABLE [large] (
ID integer NOT NULL,
SDATETIME datetime NOT NULL,
PREVSDATETIME datetime,
var1 real,
var2 real,
var3 real,
var4 real,
var5 real,
var6 real,
var7 real,
var8 real,
var9 real,
var10 real,
var11 real,
var12 real,
var13 real,
var14 real,
var15 real
);
++
input to sqlite3 - import data file: inpb.sql
.separator ,
.import inpb.txt large
++
== dos cmd script to run the test, using the files above
echo -- prepare database
del tstb.db
rem prepare database with table schema
c:\apps\sqlite3 tstb.db prepdb.sql
time /T
echo -- prepare input file with sufficient number of csv records [par
COUNT], 50m records give about 8gb data
c:\apps\gawk -f t1b.w -v COUNT=5000 inpb.txt
echo - load table with data sets the first 10 times
time /T
for %%i in (1,2,3,4,5,6,7,8,9,10) do c:\apps\sqlite3 tstb.db inpb.sql
time /T
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Hello! On Monday 07 December 2009 22:29:47 Igor Tandetnik wrote: What do you mean by SQLite stores the BOM? Are you saying that you are passing in a string without the BOM, and SQLite spontaneoulsy manufactures one? Double-check your application - I suspect you'll find that you are actually passing strings with BOM to SQLite to begin with, and it just stores them faithfully. Yes, the BOM is on the original string. But with ICU collation we can see that 17 symbols string is equal to 16 symbols string. I think this result is not right. May be automatically dropping the BOM for ICU collated fields is more correct way. 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
[sqlite] Recipe to safely move/rename a database?
I have a scenario where I want to move 99+% of the records from one database to another, initially empty but for a set of table definitions (in practice, copied from a template file). On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably long (it involves about 30MB of data). What I would rather do is: 1) move the current database file from its current location to the archive location, 2) create a new current database (from the same template I use now for the archive) and 3) copy back, from archive to current, the rows that should *not* be archived (deleting them from the archive afterward). Clearly, I'll need to create a lock on the current database before moving it, but I can foresee complications related to the behind-the curtain filesystem operations being performed. If someone has worked out all the pitfalls of this scenario, I'd appreciate a recipe. Thanks, Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Alexey Pechnikov pechni...@mobigroup.ru wrote: Yes, the BOM is on the original string. But with ICU collation we can see that 17 symbols string is equal to 16 symbols string. I think this result is not right. What's the basis for this belief? It's not at all uncommon for two Unicode strings of different length (in codepoints) to collate equal - for example, they could be canonically equivalent but in different normalization forms, or contain weightless characters such as a zero-width non-breaking space (U+FEFF), also known as BOM. May be automatically dropping the BOM for ICU collated fields is more correct way. Why don't you do just that in your application? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recipe to safely move/rename a database?
On 7 Dec 2009, at 9:58pm, Chris Eich wrote: On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably long (it involves about 30MB of data). Do you have a transaction around all the INSERT commands ? This will speed it up many fold. Another thing which will increase speed is to DROP all indexes (apart from PRIMARY KEY) before the INSERT commands, and recreate them afterwards. There's no reason why you shouldn't pursue the technique you mentioned, but I thought I'd give you an alternative you might prefer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Hello! On Tuesday 08 December 2009 01:07:54 Igor Tandetnik wrote: Alexey Pechnikov pechni...@mobigroup.ru wrote: Yes, the BOM is on the original string. But with ICU collation we can see that 17 symbols string is equal to 16 symbols string. I think this result is not right. What's the basis for this belief? It's not at all uncommon for two Unicode strings of different length (in codepoints) to collate equal - for example, they could be canonically equivalent but in different normalization forms, or contain weightless characters such as a zero-width non-breaking space (U+FEFF), also known as BOM. The normalization is now performed by any string operation. But more fast and useful to do it once at data store. May be automatically dropping the BOM for ICU collated fields is more correct way. Why don't you do just that in your application? Yes, I fix it in my application, but this problem can be produced in any application. 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] Recipe to safely move/rename a database?
Simon Slavin wrote: On 7 Dec 2009, at 9:58pm, Chris Eich wrote: On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably long (it involves about 30MB of data). Do you have a transaction around all the INSERT commands ? This will speed it up many fold. there aren't multiple insert commands. only a single insert command was mentioned. it would be a single transaction already. i have no advice, i'm just pointing out that this advice seems to be based on the false assumption that multiple transactions are involved (because this is the most common reason why things slow down). Another thing which will increase speed is to DROP all indexes (apart from PRIMARY KEY) before the INSERT commands, and recreate them afterwards. There's no reason why you shouldn't pursue the technique you mentioned, but I thought I'd give you an alternative you might prefer. Simon. cheers, raf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Alexey Pechnikov pechni...@mobigroup.ru wrote: The normalization is now performed by any string operation. But more fast and useful to do it once at data store. So, which normalization form should the data store choose for me? And what if I need a different one? I'd rather the database store my data exactly the way I put it in. I really don't want it to decide for me what my data should look like. May be automatically dropping the BOM for ICU collated fields is more correct way. Why don't you do just that in your application? Yes, I fix it in my application, but this problem can be produced in any application. One person's problem is another's feature. If that other application doesn't want BOM in its strings, it should strip it, just like yours now does. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Hello! On Tuesday 08 December 2009 01:35:49 Igor Tandetnik wrote: So, which normalization form should the data store choose for me? And what if I need a different one? I'd rather the database store my data exactly the way I put it in. I really don't want it to decide for me what my data should look like. I think the custom collation is the user solution for data processing. Or may be a collation is only data _visualization_ mechanism? 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] The byte order mark problem in fields with ICUcollation
Alexey Pechnikov pechni...@mobigroup.ru wrote: On Tuesday 08 December 2009 01:35:49 Igor Tandetnik wrote: So, which normalization form should the data store choose for me? And what if I need a different one? I'd rather the database store my data exactly the way I put it in. I really don't want it to decide for me what my data should look like. I think the custom collation is the user solution for data processing. Perhaps. Any piece of software could be thought of as a solution for data processing - after all, all computers do is shuffle bits around. I'm not sure what this general statement adds to the discussion at hand. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
On Mon, Dec 07, 2009 at 05:35:49PM -0500, Igor Tandetnik wrote: Alexey Pechnikov pechni...@mobigroup.ru wrote: The normalization is now performed by any string operation. But more fast and useful to do it once at data store. So, which normalization form should the data store choose for me? And what if I need a different one? I'd rather the database store my data exactly the way I put it in. I really don't want it to decide for me what my data should look like. I believe the right thing to do is to normalize strings when creating index entries, but to leave the table data unnormalized. You'd have to make the equality operator also normalize though. That way you can have a unique text column and it will accept acute; only one way, composed or decomposed, but not both. I.e., normalization-insensitive matching, normalization-preserving. Provides the best user experience. If multiple systems' input methods produce text in different normalization forms, or even unnormalized, users will still find their data -- no surprises. And given that whatever systems the users are using likely can display the strings produced by their input modes, preserving those strings unmodified gives you the highest likelihood that the strings returned will display properly. (This is what Solaris implements for NFSv4, CIFS and local ZFS filesystem access, for example. ZFS hashes directories, and it normalizes filenames prior to hashing, both on create and lookup, but the directory entries are left unnormalized.) To do this right requires support in SQLite3, even if it's provided by an extension. I don't recall if user-defined collation functions provide everything you need to support this. May be automatically dropping the BOM for ICU collated fields is more correct way. Why don't you do just that in your application? Yes, I fix it in my application, but this problem can be produced in any application. One person's problem is another's feature. If that other application doesn't want BOM in its strings, it should strip it, just like yours now does. +1 Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The byte order mark problem in fields with ICUcollation
Nicolas Williams nicolas.willi...@sun.com wrote: I believe the right thing to do is to normalize strings when creating index entries, but to leave the table data unnormalized. You'd have to make the equality operator also normalize though. I believe that's precisely what ICU collations do. Two canonically equivalent strings compare equal, whichever way the database happens to store them. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SOLVED] How do I properly import numbers from CSV?
On Sun, Dec 06, 2009 at 11:26:42PM -0500, Walter Dnes wrote I still don't understand why 2009, *WITHOUT QUOTES* would be forced to text, i.e. ' 2009', when imported into a field that is declared as integer in the create statement. There are actually 2 solutions... 1) Use tab-delimited if possible (not really CSV) 2) Get rid of leading/trailing spaces if you're using comma-separated input. E.g. this row results in text fields being imported... 2231,615HMAK, 2005, 3, 28, 8.0, , -1.0, , 3.5 ...while this one results in mostly numeric fields being imported... 2231,615HMAK,2005,3,28,8.0, ,-1.0, ,3.5 As an added bonus, the script that got rid of unnecessary spaces also knocked down the 11,143,911,240 byte CSV file to only 5,382,671,854 bytes. This allowed me to import the file in 3 sub-2-gigabyte pieces versus 6 pieces that the original required. -- Walter Dnes waltd...@waltdnes.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
