Re: [sqlite] create index before or after many inserts?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/15/2010 09:33 PM, Jay A. Kreibich wrote: Contiguous, yes, but the pages may not be in any logical order. The internal node pages will get shuffled as the tree is built, meaning you might still have a significant number of seeks. Seeks nearer to other pages will be quicker (for spinning media) and most operating systems do a fair amount of read ahead. For example if SQLite asks for a particular page the OS may read the next 256kb too. If that additional data was only index pages then chances are far more likely for cache hits. About the only scenario where having the index pages and data pages interleaved is beneficial is a query that needs some columns from the index and some (unindexed) from the data and most of the rows are relevant to the query. The data pages and index pages will be closer then, but even this scenario would require very carefully contriving your data and indices. The advice still stands - create the indices after, not before. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwYaFUACgkQmOOfHg372QQ3lQCfdHcDOrgftEepPICEhzXuSTqk qa0AoJBiPbcitaqwZYQulr/1bAvls5+B =qs6R -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BLOB Support
Hi All, I would like to know more about this BLOB support in SQLite. Some of my queries are: 1. One of my colleague suggested that using BLOB support for storing images in the DB is a good idea, whereas storing AVCHD data(huge size) as blobs is not a good idea. I need a bit more clarification on this statement. 2. I just want to know how does this BLOB support help in increasing the performance? 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? -- * Thanks Regards SEN* / / ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
On 16 Jun 2010, at 7:58am, Navaneeth Sen B wrote: 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? What tool are you expecting to use to store the file inside the DB ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
Hi Simon, I am using SQLite. Thanks Sen ** On 6/16/2010 12:40 PM, Simon Slavin wrote: On 16 Jun 2010, at 7:58am, Navaneeth Sen B wrote: 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? What tool are you expecting to use to store the file inside the DB ? 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] create index before or after many inserts?
On 16 Jun 2010, at 5:04am, Eric Smith wrote: Simon Slavin wrote: The standard assumption about SQLite is that it's faster to do your INSERTs first, then create the indices. How much of a difference this makes depends on a lot of things. On what things does it depend? Nothing magic, just all the normal things: How many indices do you have ? How many columns in each ? How many rows in each ? Are the indexed fields 'clumpy' or not ? How fast are your CPU and storage system ? How much of three different types of cache do you have ? Is your database storage system optimized for read-ahead or write-ahead storage ? The only way to find out is to try it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create index before or after many inserts?
On 16 Jun 2010, at 5:33am, Jay A. Kreibich wrote: On Tue, Jun 15, 2010 at 08:58:22PM -0700, Roger Binns scratched on the wall: On 06/15/2010 07:59 PM, Simon Slavin wrote: The standard assumption about SQLite is that it's faster to do your INSERTs first, then create the indices. If the index is created first then the data in the table and the pages making up the index will be interleaved. That is likely to lead to more and further seeks on accessing the index. Creating the index afterwards will result in a contiguous sequence of pages (assuming no existing free pages). Contiguous, yes, but the pages may not be in any logical order. The internal node pages will get shuffled as the tree is built, meaning you might still have a significant number of seeks. Actually, I always figured that the CREATE INDEX routine was better tuned for creating many entries in an index at once, than writing them one by one. Each index has its own set of pages, right ? Imagine a table with three indices. Inserting a thousand rows would normally do something like mess with index 1; mess with index 2; mess with index 3; mess with index 1; mess with index 2 ... 1000 times There would be far less shuffling involved if all the index 1 work was done in one chunk, then all the index 2 work, etc.. Of course it's possible that SQLite is clever about transactions and if all the INSERT commands are in one transaction it does all the index-updating together. That would be neat. I haven't read the source code. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
On 16 Jun 2010, at 8:14am, Navaneeth Sen B wrote: I am using SQLite. Thanks Sen ** On 6/16/2010 12:40 PM, Simon Slavin wrote: On 16 Jun 2010, at 7:58am, Navaneeth Sen B wrote: 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? What tool are you expecting to use to store the file inside the DB ? SQLite does not store files inside its database. Therefore the first part of your question never happens. Your question doesn't mean anything. Simon. PS: when quoting someone else's post, please trim it to just the piece you need for your own text, and add your own text below the old text. English is read from top to bottom. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking issue on NFS filesystem
int sqlite3_open_v2( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb, /* OUT: SQLite db handle */ int flags, /* Flags */ const char *zVfs/* Name of VFS module to use */ ); With the last argument unix-dotfile. Does it work? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way for create a sqlite blob field with python?
Hi
2010/6/15 Roger Binns rog...@rogerbinns.com
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 06/15/2010 01:46 PM, Fabio Spadaro wrote:
I am developing an interface to Python 's SQLite and would it be
you can store files in the sqlite database. To do this
obviously need to create a blob field but how to store image files?
If you are using Python 2 then use the buffer type. If using Python 3 then
use bytes.
Py2:
cursor.execute(insert into foo(image) values(?),
(buffer(open(.../foo.jpg, rb).read()), ))
Py3:
cursor.execute(insert into foo(image) values(?),
(open(.../foo.jpg, rb).read(), ))
If you use APSW then you can also use the incremental blob API:
http://apsw.googlecode.com/svn/publish/blob.html
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkwX7yAACgkQmOOfHg372QSIkQCfej/1Zw7htLxZtVFGsIsxb91m
YnkAniPp7aScNJITD3xYOmH4MC9e4Asx
=M6pK
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I created the table:
CREATE TABLE blobber(file BLOB)
I inserted 2 records:
1. gestdb.cur.execute(insert into blobber(file) values(?),
(buffer(open('..\conf\img\file1.gif','rb').read()), ))
2. gestdb.cur.execute(insert into blobber(file) values(?),
(buffer(open('..\conf\img\file2.gif','rb').read()), ))
But the next time I select a single line instead of two:
fetchall [(read-write buffer ptr 0x02CC2A68, size 1311 at
0x02CC2A30,)]
What's wrong.
--
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
for some reason, I remember you asking the same question not too long ago, and getting a bunch of answers. I recall chipping in with an answer myself. DIdn't any of those answers help? On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B navanee...@tataelxsi.co.in wrote: Hi All, I would like to know more about this BLOB support in SQLite. Some of my queries are: 1. One of my colleague suggested that using BLOB support for storing images in the DB is a good idea, whereas storing AVCHD data(huge size) as blobs is not a good idea. I need a bit more clarification on this statement. What is the clarification you need? I remember writing that (in my wisdom), it is better to store large binary objects such as big video, audio or image files in the file system, and store the metadata for them in the db. If you have many, many small items, storing them directly as blobs in the sqlite should be very quick and helpful, but other than that, storing them in the file system may be better. Did you experiment with one or the other? 2. I just want to know how does this BLOB support help in increasing the performance? Don't know. Only you can tell, based on your usage scenario and your performance expectations. 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? What do you mean by What is the difference produced? Which difference and produced from what? If stored correctly, you will have the same item in the db as would have been in the file system, so which difference are you talking about? If you are talking about the mechanism itself, well, we went through that earlier and above as well... the db does all the homework for you regarding where to store the files, even what to call them, if you implement that, etc. But, of course, you can't access those files directly if they are in the db. You have to get to them via the db only. -- * Thanks Regards SEN* / / ___ 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 === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] notify all processes of database modification
That is, other than polling, . Yep, that trick. (Why does everyone dismiss polling...don't we have any embedded programmers here? Hey Windows CE guy, don't raise your hand...) Roger summed it up, and labeled it correctly (crappy). But if it works sufficiently for him, why not explore it? Arno: If you are still reading this, please let us know what solution you eventually took. I like to see closure on these threads, and perhaps we will learn something from you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
Do some testing to find out if it suits the application you develop. But just in general .. file systems are build to handle files, databases are for handle data. I don't think BLOB in SQlite will increasing the performance compared to store the files in the file system. Some SQlite APIs do not support BLOB very good. Also something to consider if you will store large files. /Andreas On Wed, Jun 16, 2010 at 1:31 PM, P Kishor punk.k...@gmail.com wrote: for some reason, I remember you asking the same question not too long ago, and getting a bunch of answers. I recall chipping in with an answer myself. DIdn't any of those answers help? On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B navanee...@tataelxsi.co.in wrote: Hi All, I would like to know more about this BLOB support in SQLite. Some of my queries are: 1. One of my colleague suggested that using BLOB support for storing images in the DB is a good idea, whereas storing AVCHD data(huge size) as blobs is not a good idea. I need a bit more clarification on this statement. What is the clarification you need? I remember writing that (in my wisdom), it is better to store large binary objects such as big video, audio or image files in the file system, and store the metadata for them in the db. If you have many, many small items, storing them directly as blobs in the sqlite should be very quick and helpful, but other than that, storing them in the file system may be better. Did you experiment with one or the other? 2. I just want to know how does this BLOB support help in increasing the performance? Don't know. Only you can tell, based on your usage scenario and your performance expectations. 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? What do you mean by What is the difference produced? Which difference and produced from what? If stored correctly, you will have the same item in the db as would have been in the file system, so which difference are you talking about? If you are talking about the mechanism itself, well, we went through that earlier and above as well... the db does all the homework for you regarding where to store the files, even what to call them, if you implement that, etc. But, of course, you can't access those files directly if they are in the db. You have to get to them via the db only. -- * Thanks Regards SEN* / / ___ 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 === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Andreas Henningsson Vanligt sunt förnuft är inte särkilt vanligt. -- Voltaire ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson andreas.hennings...@gmail.com wrote: Do some testing to find out if it suits the application you develop. But just in general .. file systems are build to handle files, databases are for handle data. Well, at the risk of being pedantic, you say files I say data (sung to the tune of you say poe-tay-toe, I say poe-tah-toe). What is within the files is data after all. When storing a blob of, say, an image, you are not storing the file; instead, you are storing those bits-and-bytes that are reconstructed as an image by your image viewer. To do so, you open your image file, you read in the contents, and write them to the db blob column. Your file is gone... it is no longer relevant. Now what you have is the data in your db. But yes, my sense is (no firm, scientific tests backing this claim, mind you), that storing very large binary objects in a db doesn't seem efficient. Storing them on the file system while storing their metadata in the db seems a lot more efficient. On the other hand, a case could be made for storing blobs in the db when you have many, many small binary objects, as in the case of image thumbnails. Especially if the blobs are smaller than a page size, the db would likely be extremely efficient. I don't think BLOB in SQlite will increasing the performance compared to store the files in the file system. Some SQlite APIs do not support BLOB very good. Also something to consider if you will store large files. /Andreas On Wed, Jun 16, 2010 at 1:31 PM, P Kishor punk.k...@gmail.com wrote: for some reason, I remember you asking the same question not too long ago, and getting a bunch of answers. I recall chipping in with an answer myself. DIdn't any of those answers help? On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B navanee...@tataelxsi.co.in wrote: Hi All, I would like to know more about this BLOB support in SQLite. Some of my queries are: 1. One of my colleague suggested that using BLOB support for storing images in the DB is a good idea, whereas storing AVCHD data(huge size) as blobs is not a good idea. I need a bit more clarification on this statement. What is the clarification you need? I remember writing that (in my wisdom), it is better to store large binary objects such as big video, audio or image files in the file system, and store the metadata for them in the db. If you have many, many small items, storing them directly as blobs in the sqlite should be very quick and helpful, but other than that, storing them in the file system may be better. Did you experiment with one or the other? 2. I just want to know how does this BLOB support help in increasing the performance? Don't know. Only you can tell, based on your usage scenario and your performance expectations. 3. What is the difference produced in storing the file inside DB(not in blob format) and storing the same file in BLOB format in the DB? What do you mean by What is the difference produced? Which difference and produced from what? If stored correctly, you will have the same item in the db as would have been in the file system, so which difference are you talking about? If you are talking about the mechanism itself, well, we went through that earlier and above as well... the db does all the homework for you regarding where to store the files, even what to call them, if you implement that, etc. But, of course, you can't access those files directly if they are in the db. You have to get to them via the db only. -- * Thanks Regards SEN* / / -- 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 === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way for create a sqlite blob field with python?
Hi,
2010/6/16 Fabio Spadaro fabiolinos...@gmail.com
Hi
2010/6/15 Roger Binns rog...@rogerbinns.com
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 06/15/2010 01:46 PM, Fabio Spadaro wrote:
I am developing an interface to Python 's SQLite and would it be
you can store files in the sqlite database. To do this
obviously need to create a blob field but how to store image files?
If you are using Python 2 then use the buffer type. If using Python 3
then
use bytes.
Py2:
cursor.execute(insert into foo(image) values(?),
(buffer(open(.../foo.jpg, rb).read()), ))
Py3:
cursor.execute(insert into foo(image) values(?),
(open(.../foo.jpg, rb).read(), ))
If you use APSW then you can also use the incremental blob API:
http://apsw.googlecode.com/svn/publish/blob.html
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkwX7yAACgkQmOOfHg372QSIkQCfej/1Zw7htLxZtVFGsIsxb91m
YnkAniPp7aScNJITD3xYOmH4MC9e4Asx
=M6pK
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I created the table:
CREATE TABLE blobber(file BLOB)
I inserted 2 records:
1. gestdb.cur.execute(insert into blobber(file) values(?),
(buffer(open('..\conf\img\file1.gif','rb').read()), ))
2. gestdb.cur.execute(insert into blobber(file) values(?),
(buffer(open('..\conf\img\file2.gif','rb').read()), ))
But the next time I select a single line instead of two:
fetchall [(read-write buffer ptr 0x02CC2A68, size 1311 at
0x02CC2A30,)]
What's wrong.
--
Fabio Spadaro
www.fabiospadaro.com
Sorry, I did not commit.
--
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding Database Is Locked Error
Thank you all for your responses. This discussion has grown a lot more than I thought it would. Like I said in my original question, my system is made up of several programs that communicate by sending messages to each other. I have a utility program that can send messages to the programs for testing and debugging purposes. I have updated my utility program and the program that accesses the database so that I can send arbitrary SQL queries or update statements to the database program to execute. Now I don't need to use the command line SQLite tool and the locked database problem is no longer an issue. The discussion of transactions and interleaving queries with updates was really good information. I am (mis)using transactions not so much to group a unit of work that must succeed together, but more to improve the I/O performance by causing any updates to be written to disk at the end of the transaction. Writing each change individually to disk was taking too much time and wrapping things in a transaction reduced that time considerably. I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a problem in my case, since now there really is just one program accessing the database. I am probably interleaving queries and updates in at least one part of my program, but it doesn't seem to be causing any problems. I'll let you know if I get the 1:00 am hotline support call. Swapnil, thanks for the link. I'm running on SCO OpenServer so I'm not sure your product would work for me but I'll take a look. I was really looking for code examples that demonstrate best practices. Thanks again for all the comments, suggestions and good information. Shawn Hi Shawn, demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? If you pre-select and then modify, you have to be aware enough to realize you MUST wrap the whole process in a manual transaction, and you still need to know how to deal with all the locking and busy issues that come with that. One way to assure use of best-practices in dealing with SQLite nitty-gritties of transactions, locking and busy errors would be to use a tool like StepSqlite PL/SQL compiler (https://www.metatranz.com/stepsqlite https://www.metatranz.com/stepsqlite ). It automates SQLite best-practices (as recommended by Jay, Pavel and Simon in this thread) by wrapping things in transactions, handling BUSY errors and retrying after delay and others like preparing all SQL statements only once etc. SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on writing your code instead of having to learn to deal with SQLite idiosyncrasies right at the beginning. Full disclosure: I am the creator of StepSqlite. -Swapnil Kashikar support @ metatranz . com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
On Wed, Jun 16, 2010 at 8:34 AM, P Kishor punk.k...@gmail.com wrote: On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson andreas.hennings...@gmail.com wrote: Do some testing to find out if it suits the application you develop. But just in general .. file systems are build to handle files, databases are for handle data. Well, at the risk of being pedantic, you say files I say data (sung to the tune of you say poe-tay-toe, I say poe-tah-toe). I am in total agreement ;) But yes, my sense is (no firm, scientific tests backing this claim, mind you), that storing very large binary objects in a db doesn't seem efficient. Storing them on the file system while storing their metadata in the db seems a lot more efficient. On the other hand, a case could be made for storing blobs in the db when you have many, many small binary objects, as in the case of image thumbnails. Especially if the blobs are smaller than a page size, the db would likely be extremely efficient. A lot also has to do with the requirements: My software is an event image viewing system, where each event is seen as a single document and all the data associated with the document is contained within the event folder. Currently only the metadata is stored in the database, all the images are stored in folders that are within the event folder. I am guessing, as is others, that storing the large images in the SQLite DB would be less efficient then how I am storing it now. One side effect, though is the requirement to backup the event folder. It takes a LOT longer to copy 5000 4k~8k files then it would be to copy one 20M ~ 40M database file. Because of this, in time I want to move all the thumbnails into one SQLite file, or maybe have one SQLite file per current folder holding images. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding Database Is Locked Error
I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a problem in my case, since now there really is just one program accessing the database. Did you forget the message this thread was started from? The sqlite3 command line utility is a second program, so this could be a problem. Pavel On Wed, Jun 16, 2010 at 9:33 AM, Odekirk, Shawn shawn.odek...@intelligrated.com wrote: Thank you all for your responses. This discussion has grown a lot more than I thought it would. Like I said in my original question, my system is made up of several programs that communicate by sending messages to each other. I have a utility program that can send messages to the programs for testing and debugging purposes. I have updated my utility program and the program that accesses the database so that I can send arbitrary SQL queries or update statements to the database program to execute. Now I don't need to use the command line SQLite tool and the locked database problem is no longer an issue. The discussion of transactions and interleaving queries with updates was really good information. I am (mis)using transactions not so much to group a unit of work that must succeed together, but more to improve the I/O performance by causing any updates to be written to disk at the end of the transaction. Writing each change individually to disk was taking too much time and wrapping things in a transaction reduced that time considerably. I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a problem in my case, since now there really is just one program accessing the database. I am probably interleaving queries and updates in at least one part of my program, but it doesn't seem to be causing any problems. I'll let you know if I get the 1:00 am hotline support call. Swapnil, thanks for the link. I'm running on SCO OpenServer so I'm not sure your product would work for me but I'll take a look. I was really looking for code examples that demonstrate best practices. Thanks again for all the comments, suggestions and good information. Shawn Hi Shawn, demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? If you pre-select and then modify, you have to be aware enough to realize you MUST wrap the whole process in a manual transaction, and you still need to know how to deal with all the locking and busy issues that come with that. One way to assure use of best-practices in dealing with SQLite nitty-gritties of transactions, locking and busy errors would be to use a tool like StepSqlite PL/SQL compiler (https://www.metatranz.com/stepsqlite https://www.metatranz.com/stepsqlite ). It automates SQLite best-practices (as recommended by Jay, Pavel and Simon in this thread) by wrapping things in transactions, handling BUSY errors and retrying after delay and others like preparing all SQL statements only once etc. SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on writing your code instead of having to learn to deal with SQLite idiosyncrasies right at the beginning. Full disclosure: I am the creator of StepSqlite. -Swapnil Kashikar support @ metatranz . com ___ 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] SQLite and Qt
On Tue, Jun 15, 2010 at 7:11 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: Hi, What is the error? did you make a sample project that you could share? The error was between the keyboard and the chair... Once the SQLite DLL was moved into the Qt bin directory, all worked as expected. Thus the answer to my question would seem to be: Qt will simply use the SQLite3.lib you give it, if it is to link to a DLL, you need to make sure Qt can find the DLL. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] issue with v3.6 documentation
Morning all, I recently updated my copy of SQLITE to the v3.6.x line. Everything appeared to be working just fine and I had no problems until I went to the documentation to look up the syntax for an SQL command. Then I noticed that the syntax pages have all changed. No longer is the syntax for a command physically on the page, it appears to be present in an image of some kind. While I'm sure the syntax diagram images look great to the sighted folks, blind folks can't see them at all. So, the documentation pages, that used to be useful and contain the actual syntax of the sql commands, are now useless to me. Is there a way to obtain an older copy of the sqlite documentation that has the syntax of commands physically on the pages? Thanks Dan G ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way for create a sqlite blob field with python?
Hi, 2010/6/15 Roger Binns rog...@rogerbinns.com -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/15/2010 01:46 PM, Fabio Spadaro wrote: I am developing an interface to Python 's SQLite and would it be you can store files in the sqlite database. To do this obviously need to create a blob field but how to store image files? If you are using Python 2 then use the buffer type. If using Python 3 then use bytes. Py2: cursor.execute(insert into foo(image) values(?), (buffer(open(.../foo.jpg, rb).read()), )) Py3: cursor.execute(insert into foo(image) values(?), (open(.../foo.jpg, rb).read(), )) If you use APSW then you can also use the incremental blob API: http://apsw.googlecode.com/svn/publish/blob.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwX7yAACgkQmOOfHg372QSIkQCfej/1Zw7htLxZtVFGsIsxb91m YnkAniPp7aScNJITD3xYOmH4MC9e4Asx =M6pK -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Another question. If I wanted to convert the buffer to a file how should I do? -- Fabio Spadaro www.fabiospadaro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory request
Hi, A little question for you... how is the minumun space required for sqlite3 library ?? I try to compile my application with sqlite3.c file, but I have some kind of problem of insufficient memory when my application do sqlite3_inizialize Can you help me ?? thank you so much Bye Airpalaz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions
I scanned through the bug list on the website and didn't see one that was
related to this, so please forgive me if this has already been answered.
We have created a function that takes a string and returns a substring that is
split on a given character (I'll provide the source below), called splitstr.
In sqlite 3.0.8 we used this function and found that comparing the output to a
given string would use the case-sensitivity of the given string. Meaning that
if we used select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo'; and
col1 of table foo was defined as text collate nocase then the comparisons
would be case insensitive. This was the behavior in 3.0.8. It is no longer
the behavior in 3.6.23.
Is this working as designed and the 3.0.8 behavior was incorrect? Or is this a
valid bug?
To reproduce this I create the following table:
create table foo( key integer primary key, value text collate nocase );
insert into foo VALUES( NULL, 'f...@bar' );
insert into foo VALUES( NULL, 'f...@bar' );
insert into foo VALUES( NULL, 'f...@bar' );
insert into foo VALUES( NULL, 'f...@bar' );
select * from foo where splitstr( value, '@', 1 ) = 'foo';
4|f...@bar
However, in 3.0.8 I would get all records.
Splitstr() is a very simple function:
static void splitstrFunc( sqlite3_context *context, int argc, sqlite3_value
**argv ){ const char *z = NULL; char *temp = NULL; const char *p1 =
NULL; intp2 = 0; assert( argc==3 ); z = sqlite3_value_text(argv[0]);
if( z==0 ) return; p1 = sqlite3_value_text(argv[1]); if( p1 == 0 )
return; p2 = sqlite3_value_int(argv[2]); if( (temp = strchr( z, p1[0] )) ==
NULL ) return; *temp = '\0'; temp++; if( p2 == 2 ) z = temp; else
if( p2 != 1 ) /* must specify either first or second */ return;
sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:
(FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),
Thanks for your help
Jeff Webb
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
Hello Sam, I store multiple gigs of image files, some as large as 2-3 megs in Sqlite DB's. For pretty much the same reason, the convenience of having them in one package. For my requirements, extracting the images from the DB, and displaying them isn't a bottleneck. It's fast enough. Search speed was improved by not storing the meta-data in the same table as the blob data. If I wanted to improve performance even more, I'd keep the meta-data in a different DB file. Essentially a DB for blobs and a DB for meta-data. It seems that seeking over the blobs to get to the meta-data in the combined DB is somewhat slow. C SC A lot also has to do with the requirements: My software is an event SC image viewing system, where each event is seen as a single document SC and all the data associated with the document is contained within SC the event folder. Currently only the metadata is stored in the SC database, all the images are stored in folders that are within the SC event folder. I am guessing, as is others, that storing the large SC images in the SQLite DB would be less efficient then how I am storing SC it now. One side effect, though is the requirement to backup the SC event folder. It takes a LOT longer to copy 5000 4k~8k files then SC it would be to copy one 20M ~ 40M database file. Because of this, in SC time I want to move all the thumbnails into one SQLite file, or maybe SC have one SQLite file per current folder holding images. SC Sam SC ___ SC sqlite-users mailing list SC sqlite-users@sqlite.org SC 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] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions
Is this working as designed and the 3.0.8 behavior was incorrect? Or is this
a valid bug?
I'm not developer of SQLite and don't know what did they intend to do.
But applying common sense I'd say that behavior of 3.0.8 was incorrect
and it's fixed in 3.6.23. To get the same result as you used to you
can write your query like this:
select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo' collate nocase;
Here your intention would be explicit and independent of column definition.
Pavel
On Tue, Jun 15, 2010 at 6:46 PM, Jeff Webb jeff.w...@quest.com wrote:
I scanned through the bug list on the website and didn't see one that was
related to this, so please forgive me if this has already been answered.
We have created a function that takes a string and returns a substring that
is split on a given character (I'll provide the source below), called
splitstr. In sqlite 3.0.8 we used this function and found that comparing the
output to a given string would use the case-sensitivity of the given string.
Meaning that if we used select * from foo where splitstr(foo.col1, '@', 1 )
= 'foo'; and col1 of table foo was defined as text collate nocase then the
comparisons would be case insensitive. This was the behavior in 3.0.8. It
is no longer the behavior in 3.6.23.
Is this working as designed and the 3.0.8 behavior was incorrect? Or is this
a valid bug?
To reproduce this I create the following table:
create table foo( key integer primary key, value text collate nocase );
insert into foo VALUES( NULL, 'f...@bar' );
insert into foo VALUES( NULL, 'f...@bar' );
insert into foo VALUES( NULL, 'f...@bar' );
insert into foo VALUES( NULL, 'f...@bar' );
select * from foo where splitstr( value, '@', 1 ) = 'foo';
4|f...@bar
However, in 3.0.8 I would get all records.
Splitstr() is a very simple function:
static void splitstrFunc( sqlite3_context *context, int argc,
sqlite3_value **argv ){ const char *z = NULL; char *temp = NULL;
const char *p1 = NULL; int p2 = 0; assert( argc==3 ); z =
sqlite3_value_text(argv[0]); if( z==0 ) return; p1 =
sqlite3_value_text(argv[1]); if( p1 == 0 ) return; p2 =
sqlite3_value_int(argv[2]); if( (temp = strchr( z, p1[0] )) == NULL )
return; *temp = '\0'; temp++; if( p2 == 2 ) z = temp; else if( p2
!= 1 ) /* must specify either first or second */ return;
sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:
(FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),
Thanks for your help
Jeff Webb
___
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] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions
On Tue, Jun 15, 2010 at 03:46:17PM -0700, Jeff Webb scratched on the wall: Is this working as designed and the 3.0.8 behavior was incorrect? It sounds like it. Collations are typically not promoted through operations. Having one promoted from a function parameter to the result seems incorrect. I'm can't offer a definitive answer, however. select * from foo where splitstr( value, '@', 1 ) = 'foo'; 4|f...@bar As a workaround, try: SELECT * FROM foo WHERE splitstr( value, '@', 1 ) COLLATE NOCASE = 'foo'; -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
On Wed, Jun 16, 2010 at 10:04 AM, Teg t...@djii.com wrote: Hello Sam, I store multiple gigs of image files, some as large as 2-3 megs in Sqlite DB's. For pretty much the same reason, the convenience of having them in one package. For my requirements, extracting the images from the DB, and displaying them isn't a bottleneck. It's fast enough. Search speed was improved by not storing the meta-data in the same table as the blob data. If I wanted to improve performance even more, I'd keep the meta-data in a different DB file. Essentially a DB for blobs and a DB for meta-data. It seems that seeking over the blobs to get to the meta-data in the combined DB is somewhat slow. Teg, Thank you for the info! I had always intended to store the metadata in a separate DB then the blobs. There will always only be one metatdata DB, the only question is whether there is one big blob DB or multiple blob DB's which are broken into logical groupings. The system already has the concept of groups the images, so conceptually the later is very logical. Of course the advantage to one big blob DB is not having to figure out which DB to open to get the images. A few customers will load upwards of 100,000 to 150,000 images in one events. How well does SQLite handle large blob DB's? There are two images that I want to store, a thumbnail (4K~8K) and a large image (80K~160K). That would result in one DB between 9Gig and 25Gig. Now that I do the math, breaking them into logical groups that normally don't have more then 2000 images and normally only a few hundred, seems like a much more practical approach;) Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] issue with v3.6 documentation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Clone the latest fossil source at: http://www.sqlite.org/docsrc and open art/syntax/all-bnf.html. I had the same problem a while back, and someone kindly created that file. You might be able to access it from the website, but the anonymous login is protected by a very silly ascii art captcha. If you have problems cloning, just mail me offlist and I'll attach it. I also have the docs for sqlite 3.6.3, which I believe was the last version to put the BNF in the pages themselves. dansli...@g-ware.com wrote: Morning all, I recently updated my copy of SQLITE to the v3.6.x line. Everything appeared to be working just fine and I had no problems until I went to the documentation to look up the syntax for an SQL command. Then I noticed that the syntax pages have all changed. No longer is the syntax for a command physically on the page, it appears to be present in an image of some kind. While I'm sure the syntax diagram images look great to the sighted folks, blind folks can't see them at all. So, the documentation pages, that used to be useful and contain the actual syntax of the sql commands, are now useless to me. Is there a way to obtain an older copy of the sqlite documentation that has the syntax of commands physically on the pages? Thanks Dan G ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - -- Tyler Spivey - PGP Key ID: 0xae742aaf -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQIcBAEBCgAGBQJMGOSnAAoJEPb0SlyudCqvj54QAImJUiGWcrrP1DWAqDUYODf0 1IOS4EOPS4cZDSu8/skBwRzDcA7eFt6XaDaqfLzGqERAmwJdAee+bWbldNI2u1B6 dBSCM5L2arG1z+vOTZbB8pDIWDP5e9kdN01Ko84oEpMDICbkNqDNa25xWrotVC1f /LL1xXbu6nmtrGsoZxCiVwCE94GoIDbzwtsCo+dr7vHNVMNeVKNiX72KEaH9R6wm 4fBlYCXUmkjlvim1wzhlYJBGw1WgVYVuy6H2PHeDBKEMhAIzMnRMc0QUxutt+lqe cCQq38KXRZZU8sr7D0qNZAzrwqvEX4Wks43PNWE8PgbzJzNzrxHQaozYtooAHjUl HJzyI5F/vMYvaIvkBHrZoFLX7ULhurwjVTDsTm0VK1rz7LzzhuU1tJmlUF1u9R3k tEI5CmELI4XFjDVcDynCKbvH4bd6C+n2jWymGJlIw2LM0rVMo18u+titI8OPxVJM G+GiQdVMEZafas3Fc7plnKJz0fU7RE716KFTFMe9q35m9RmrMb2dWoSH5vPMNb1/ raTRYQzqS5oaWaQUdxrifnR7r/S5M6l7XYLBTkGTyivTnXHDNVsZvDpFTd2zyCEv MyqBF67Mb32XEz28gus0x+uJ4z5MBciX9vto0WEJ0NeGoOv4jObZ/gS9TwiP4XmY F86xOoqpxWAizCNertaw =Zb46 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding Database Is Locked Error
I'm running on SCO OpenServer so I'm not sure your product would work for me but I'll take a look StepSqlite compiler's Linux target generates shared objects (.so) that should work on virtually all Unix-like systems thanks to ELF format. As an aside, further in SQLite exploration you may find it convenient to use SQLite loadable extensions (http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions). StepSqlite makes it a snap to generate a loadable extension; a simple radio button choice to generate either a Loadable Extension or a Regular C++ library from *same* PL/SQL source. -Swapnil Kashikar support @ metatranz . com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and Qt
On Wed, Jun 16, 2010 at 3:42 PM, Sam Carleton scarle...@miltonstreet.com The error was between the keyboard and the chair... Once the SQLite DLL was moved into the Qt bin directory, all worked as expected. :-) Thus the answer to my question would seem to be: Qt will simply use the SQLite3.lib you give it, if it is to link to a DLL, you need to make sure Qt can find the DLL. sure, I am happy that your problem is solved. Best regards, Sylvain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way for create a sqlite blob field with python?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/16/2010 04:14 AM, Fabio Spadaro wrote: But the next time I select a single line instead of two: fetchall [(read-write buffer ptr 0x02CC2A68, size 1311 at 0x02CC2A30,)] What's wrong. Errr, nothing. Blobs are returned as buffers too. (This way you can distinguish them from str.) Buffers act just like str. You can get an individual byte - buf[7] - or a range - buf[7:93] etc. You can convert them to str - str(buf). Another question. If I wanted to convert the buffer to a file how should I do? Just use it as is: open(filename, wb).write(buf) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwZAVoACgkQmOOfHg372QTj4ACgwuDNdpMcFQEP901Sein+98Qy UaQAn1BwlSP1cN1xriY95XWekyg9sdjs =KSFO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way for create a sqlite blob field with python?
Hi, 2010/6/16 Roger Binns rog...@rogerbinns.com -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/16/2010 04:14 AM, Fabio Spadaro wrote: But the next time I select a single line instead of two: fetchall [(read-write buffer ptr 0x02CC2A68, size 1311 at 0x02CC2A30,)] What's wrong. Errr, nothing. Blobs are returned as buffers too. (This way you can distinguish them from str.) Buffers act just like str. You can get an individual byte - buf[7] - or a range - buf[7:93] etc. You can convert them to str - str(buf). Another question. If I wanted to convert the buffer to a file how should I do? Just use it as is: open(filename, wb).write(buf) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwZAVoACgkQmOOfHg372QTj4ACgwuDNdpMcFQEP901Sein+98Qy UaQAn1BwlSP1cN1xriY95XWekyg9sdjs =KSFO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users You can also determine the path and file name from the blob or should I create a column attached with this information stored? -- Fabio Spadaro www.fabiospadaro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way for create a sqlite blob field with python?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/16/2010 11:35 AM, Fabio Spadaro wrote: You can also determine the path and file name from the blob or should I create a column attached with this information stored? You are overthinking this. A blob is just a collection of bytes in the same way a string is collection of characters. Any meaning you give to them is up to you and your application. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwZPGMACgkQmOOfHg372QRK3wCgqzLmFWuRuA9Jje0uJ3ly9AvD W+cAniao1Fiv0JecrUwo0Lk2u6nNyqRd =ooO+ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
