Re: [sqlite] is there any way for create a sqlite blob field with python?

[Roger Binns]

-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

Re: [sqlite] is there any way for create a sqlite blob field with python?

[Fabio Spadaro]

Hi,

2010/6/16 Roger Binns 

> -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 [( > 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?

[Roger Binns]

-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 [( 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] SQLite and Qt

[Sylvain Pointeau]

On Wed, Jun 16, 2010 at 3:42 PM, Sam Carleton 
>
> 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] Avoiding Database Is Locked Error

[sub sk79]

> 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] issue with v3.6 documentation

[Tyler Spivey]

-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] BLOB Support

[Sam Carleton]

On Wed, Jun 16, 2010 at 10:04 AM, Teg  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] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Jay A. Kreibich]

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] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Pavel Ivanov]

> 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  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] BLOB Support

[Teg]

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

[sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Jeff Webb]

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

[sqlite] Memory request

[airpa...@libero.it]

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

Re: [sqlite] is there any way for create a sqlite blob field with python?

[Fabio Spadaro]

Hi,

2010/6/15 Roger Binns 

> -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] issue with v3.6 documentation

[danslists]

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] SQLite and Qt

[Sam Carleton]

On Tue, Jun 15, 2010 at 7:11 AM, Sylvain Pointeau
 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

Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

> 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
 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
>  ).
>>
>> 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] BLOB Support

[Sam Carleton]

On Wed, Jun 16, 2010 at 8:34 AM, P Kishor  wrote:
> On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson
>  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

[Odekirk, Shawn]

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
 ).
> 
> 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] is there any way for create a sqlite blob field with python?

[Fabio Spadaro]

Hi,

2010/6/16 Fabio Spadaro 

>  Hi
>
> 2010/6/15 Roger Binns 
>
> -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 [( 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] BLOB Support

[P Kishor]

On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson
 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  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
>>  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] BLOB Support

[Andreas Henningsson]

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  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
>  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] notify all processes of database modification

[Rich Rattanni]

> 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

[P Kishor]

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
 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] is there any way for create a sqlite blob field with python?

[Fabio Spadaro]

 Hi

2010/6/15 Roger Binns 

> -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 [(,)]

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] Locking issue on NFS filesystem

[Sylvain Pointeau]

> 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] BLOB Support

[Simon Slavin]

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] create index before or after many inserts?

[Simon Slavin]

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] create index before or after many inserts?

[Simon Slavin]

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] BLOB Support

[Navaneeth Sen B]

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] BLOB Support

[Simon Slavin]

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] BLOB Support

[Navaneeth Sen B]

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] create index before or after many inserts?

[Roger Binns]

-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