Re: [sqlite] Sharing an SQLite database (using whole-file locking) over OpenAFS (Andrew File System)

[Simon Slavin]

On 13 Apr 2010, at 5:19am, Max Vlasov wrote:

> On Tue, Apr 13, 2010 at 3:46 AM, Simon Slavin  wrote:
> 
>> 
>> Does SQLite actually use byte-range locking ?  I thought it always locked
>> the entire file.
>> 
>> 
> Simon, I think it's about the way sqlite implements different locks, a quote
> from os.h
> 
> ** A SHARED_LOCK is obtained by locking a single randomly-chosen
> ** byte out of a specific range of bytes. ...

Oh, I see.  That's called semaphore locking.  I never realised SQLite used it.  
Thanks for the info.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sharing an SQLite database (using whole-file locking) over OpenAFS (Andrew File System)

[Max Vlasov]

On Tue, Apr 13, 2010 at 3:46 AM, Simon Slavin  wrote:

>
> Does SQLite actually use byte-range locking ?  I thought it always locked
> the entire file.
>
>
Simon, I think it's about the way sqlite implements different locks, a quote
from os.h

** A SHARED_LOCK is obtained by locking a single randomly-chosen
** byte out of a specific range of bytes. ...
** An EXCLUSIVE_LOCK is obtained by locking all bytes in the range.
** There can only be one writer.  A RESERVED_LOCK is obtained by locking
** a single byte of the file that is designated as the reserved lock byte.
** A PENDING_LOCK is obtained by locking a designated byte different from
** the RESERVED_LOCK byte.

It was a note about windows, but there's also a phrase "The same locking
strategy and byte ranges are used for Unix."

Max Vlasov,
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sharing an SQLite database (using whole-file locking) over OpenAFS (Andrew File System)

[Simon Slavin]

On 13 Apr 2010, at 12:28am, Brandon Simmons wrote:

> On the OpenAFS mailing list, I was told that this might be problematic
> because of the lack of support for byte-range locking in OpenAFS, but
> that...

Does SQLite actually use byte-range locking ?  I thought it always locked the 
entire file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sharing an SQLite database (using whole-file locking) over OpenAFS (Andrew File System)

[Brandon Simmons]

To begin, I have read the FAQ and understand that SQLite wasn't
designed to be used over a network, accept all responsibility for data
loss, zombie armageddon, etc...

That said I am trying to determine if a bunch of sqlite DBs can be
successfully shared over the network using AFS (as an alternative to
NFS). We have a web app which works not unlike a traditional
multi-user unix environment, except that users work through a web
interface. Each user has their own sqlite database, and writes are
relatively infrequent to any single database.

On the OpenAFS mailing list, I was told that this might be problematic
because of the lack of support for byte-range locking in OpenAFS, but
that...

 > However, my understanding that shared r/w access to sqlite
through AFS probably does work,
 > provided you ensure sqlite uses the correct locking style (cf.
sqlite's os_unix.c):
 >
 > #define SQLITE_WHOLE_FILE_LOCKING  0x0001   /* Use whole-file locking */
 >
 > This feature is apparently due to Adam Megacz, who posted
briefly about it in 2006.
 > See http://marc.info/?l=sqlite-users=116742195016159=2 .

(to quote Matt W. Benjamin).

I haven't found very much information on "whole-file locking" from
google, but did find this ancient thread:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg17305.html

...but I'm not quite sure what to do with that. What is your take on
this, and how would I go about enabling "whole-file locking"? Thanks
for any insight.

Sincerely,
Brandon Simmons
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] New book about SQLite

[A.J.Millan]

Perhaps some may find interesting email I just received from Amazon.

As someone who has purchased or rated The Definitive Guide to SQLite by Mike 
Owens, you might like to know that Inside Symbian SQL: A Mobile Developer's 
Guide to SQLite will be released on April 26, 2010.

Product Description
This is the definitive guide for Symbian C++ developers looking to use 
Symbian SQL in applications or system software.

Since Symbian SQL and SQLite are relatively new additions to the Symbian 
platform, Inside Symbian SQL begins with an introduction to database theory 
and concepts, including a Structured Query Language (SQL) tutorial.

Inside Symbian SQL also provides a detailed overview of the Symbian SQL 
APIs. From the outset, you will "get your hands dirty" writing Symbian SQL 
code. The book includes snippets and examples that application developers 
can immediately put to use to get started quickly.

A.J.Millan



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Executing SELECT and INSERT from C under Linux

[Erik Kvernstad]

Thank you, Simon.

Erik

On Mon, Apr 12, 2010 at 3:19 PM, Simon Davies
 wrote:
> On 12 April 2010 14:01, Erik Kvernstad  wrote:
>> I am writing a C program under Linux to perform operations on an
>> sqlite-created (manually) database.  I have successfully compiled and
>> linked with the sqlite3 library and my program opens and closes the Db
>> successfully.
>> However, it is unclear to me how I in a simple way may perform
>> operations like SELECT, INSERT etc. on the database from the C
>> program.
>>
>> Grateful for hints.
>
> second question in
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq gives some suggestions
>
>>
>> Erik Kvernstad
>
> Regards,
> 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] Executing SELECT and INSERT from C under Linux

[Erik Kvernstad]

Thank you so much for this information.

Erik

On Mon, Apr 12, 2010 at 3:46 PM, Nick Shaw  wrote:
> I assume you're using the C API calls to open/close the database?  If
> so, it's just another API call to execute some simple SQL:
> sqlite3_exec().  If you need something more advanced, like prepare,
> bind, step etc, then see the appropriate APIs for those.  All listed in
> http://www.sqlite.org/c3ref/funclist.html.
>
> Thanks,
> Nick.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Erik Kvernstad
> Sent: 12 April 2010 14:02
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Executing SELECT and INSERT from C under Linux
>
> I am writing a C program under Linux to perform operations on an
> sqlite-created (manually) database.  I have successfully compiled and
> linked with the sqlite3 library and my program opens and closes the Db
> successfully.
> However, it is unclear to me how I in a simple way may perform
> operations like SELECT, INSERT etc. on the database from the C
> program.
>
> Grateful for hints.
>
> Erik Kvernstad
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reduce database file size

[Edzard Pasma]

If the subject is transferring data from a web server to a central  
database, I like to refer to a feature of an open source package that  
I published myself. It is only a side-side feature, resulting from  
the general design, but it was added with just this in mind. This  
feature, 'Connecting over the internet', is described in http:// 
packages.python.org/sqmediumlite/#5.4 (this is only for Python users).

Op 12-apr-2010, om 6:57 heeft Roger Binns het volgende geschreven:

> Nikolaus Rath wrote:
>> I see. I gues I got confused by the help that refers to the  
>> parameter as
>> 'cmd':
>
> The help shows that a list of TABLE is taken in the same syntax as
> SQLite's shell uses.  Also note that the help is formatted for
> documenting interactive usage rather than API usage.
>
>> That works, thanks! But why is it *that* slow?
>
> Because it is written in Python and SQLite's is in C.  I've made no
> effort to optimise the Python - please send me a sample database and
> I'll see what can be done.
>
> Additionally the Python supports more functionality which does have an
> effect on speed.  For example you can choose what encoding is used.
>
> Roger
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] strange error on .import: cannot find file

[Johannes Dröge]

Hi there,

while working ok on an up-to-date 64bit Ubuntu machine, on this 32bit Debian 5 
as well as 
on an older 64 bit Debian 4 it produces a strange error:

sqlite3 test.db '.import "data.dmp" mytable'
Error: cannot open "data.dmp"

with test.db and data.dmp in the working directory.

I tried it with different file names, absolute paths, anything. File and folder 
permissions are ok, e. g. 

> head data.dmp

returns the first lines and proves its readability.

This drives me crazy, anybody can help?

MfG Johannes D.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and GLOB

[Shane Harrelson]

On Mon, Apr 12, 2010 at 9:14 AM, Igor Tandetnik  wrote:

> Mike Goins wrote:
> > sqlite> explain query plan SELECT tb_file_key, basename, extension,
> > path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b' AND
> >  path GLOB '*';
> > 0|0|TABLE tb_file WITH INDEX fullpath_idx
> >
> >
> > The last one with the leading wildcard doesn't look like it should use
> > the index.
>
> SQLite can use the index to satisfy conditions on basename and extension,
> then do a full scan of what's left.
> --
> Igor Tandetnik
>
>

SQLite will not try to optimize a GLOB (to use an index) if it doesn't
contain wild cards -- although it probably should.
This optimization has been added to our list for a possible future
enhancement.

As a workaround, as has been noted, you could simply use the  foo = 'b'
instead of foo GLOB 'b'.

-Shane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] strange error on .import: cannot find file

[Johannes Dröge]

Hi there,

while working ok on an up-to-date 64bit Ubuntu machine, on this 32bit Debian 5 
as well as 
on an older 64 bit Debian 4 it produces a strange error:

sqlite3 test.db '.import "data.dmp" mytable'
Error: cannot open "data.dmp"

with test.db and data.dmp in the working directory.

I tried it with different file names, absolute paths, anything. File and folder 
permissions are ok, e. g. 

> head data.dmp

returns the first lines and proves its readability.

This drives me crazy, anybody can help?

MfG Johannes D.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Executing SELECT and INSERT from C under Linux

[Nick Shaw]

I assume you're using the C API calls to open/close the database?  If
so, it's just another API call to execute some simple SQL:
sqlite3_exec().  If you need something more advanced, like prepare,
bind, step etc, then see the appropriate APIs for those.  All listed in
http://www.sqlite.org/c3ref/funclist.html.

Thanks,
Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Erik Kvernstad
Sent: 12 April 2010 14:02
To: sqlite-users@sqlite.org
Subject: [sqlite] Executing SELECT and INSERT from C under Linux

I am writing a C program under Linux to perform operations on an
sqlite-created (manually) database.  I have successfully compiled and
linked with the sqlite3 library and my program opens and closes the Db
successfully.
However, it is unclear to me how I in a simple way may perform
operations like SELECT, INSERT etc. on the database from the C
program.

Grateful for hints.

Erik Kvernstad
___
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] Executing SELECT and INSERT from C under Linux

[Simon Davies]

On 12 April 2010 14:01, Erik Kvernstad  wrote:
> I am writing a C program under Linux to perform operations on an
> sqlite-created (manually) database.  I have successfully compiled and
> linked with the sqlite3 library and my program opens and closes the Db
> successfully.
> However, it is unclear to me how I in a simple way may perform
> operations like SELECT, INSERT etc. on the database from the C
> program.
>
> Grateful for hints.

second question in
http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq gives some suggestions

>
> Erik Kvernstad

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and GLOB

[Igor Tandetnik]

Mike Goins wrote:
> sqlite> explain query plan SELECT tb_file_key, basename, extension,
> path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b' AND
>  path GLOB '*';
> 0|0|TABLE tb_file WITH INDEX fullpath_idx
> 
> 
> The last one with the leading wildcard doesn't look like it should use
> the index.

SQLite can use the index to satisfy conditions on basename and extension, then 
do a full scan of what's left.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and GLOB

[Mike Goins]

>  I don't know why "basename GLOB 'a' " doesn't get optimized. There is no 
> reason not to, and the documentation suggests that it should. Could be a bug.

I wondering the same and wanted to make sure I was not missing something silly.

>  Note that "basename GLOB 'a' " is equivalent to "basename='a' ". Can you 
> rewrite your statements replacing GLOBs with equality tests where possible? 
> Then the index is indeed used.

I was trying to avoid using multiple queries, but there isn't any
reason I can't.  Just means more string testing and more prepared
statements.


Went back to an older version we have in production and it uses the
index regardless if the asterisk is found or even if just asterisk:

SQLite version 3.5.1
Enter ".help" for instructions
sqlite> explain query plan SELECT tb_file_key, basename, extension,
path FROM tb_file WHERE basename GLOB 'a*' AND  extension GLOB 'b' AND
 path GLOB 'c';
0|0|TABLE tb_file WITH INDEX fullpath_idx
sqlite> explain query plan SELECT tb_file_key, basename, extension,
path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b*' AND
 path GLOB 'c';
0|0|TABLE tb_file WITH INDEX fullpath_idx
sqlite> explain query plan SELECT tb_file_key, basename, extension,
path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b' AND
 path GLOB 'c';
0|0|TABLE tb_file WITH INDEX fullpath_idx
sqlite> explain query plan SELECT tb_file_key, basename, extension,
path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b' AND
 path GLOB '*';
0|0|TABLE tb_file WITH INDEX fullpath_idx


The last one with the leading wildcard doesn't look like it should use
the index.



> Using the latest binary, sqlite3-3.6.23.1.bin.gz
>
> sqlite> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
> INTEGER default 0 );
> sqlite> CREATE INDEX fullpath_idx on tb_file (basename, extension, path);
> sqlite> INSERT INTO tb_file (basename, extension, path) VALUES ('a', 'b', 
> 'c');
> sqlite> select * from tb_file;
> tb_f  basename       exte  path  dele
>   -      
> 1     a              b     c     0
>
> sqlite> explain query plan SELECT tb_file_key, basename, extension,
> path FROM tb_file WHERE basename GLOB 'a*' AND  extension GLOB 'b' AND
>  path GLOB 'c';
> orde  from           deta
>   -  
> 0     0              TABLE tb_file WITH INDEX fullpath_idx
> sqlite> explain query plan SELECT tb_file_key, basename, extension,
> path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b*' AND
>  path GLOB 'c';
> orde  from           deta
>   -  
> 0     0              TABLE tb_file USING PRIMARY KEY ORDER BY
>
>
> The first select uses the index since the the glob character is picked
> up, while the second does not.   Is this expected?   Does it matter?
>
> Thanks
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Executing SELECT and INSERT from C under Linux

[Erik Kvernstad]

I am writing a C program under Linux to perform operations on an
sqlite-created (manually) database.  I have successfully compiled and
linked with the sqlite3 library and my program opens and closes the Db
successfully.
However, it is unclear to me how I in a simple way may perform
operations like SELECT, INSERT etc. on the database from the C
program.

Grateful for hints.

Erik Kvernstad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] encryption

[g...@greschenz.de]

you can try
   http://greschenz.dyndns.org/downloads/sqlite_crypt.zip
bye, gg
 

Simon Slavin  hat am 12. April 2010 um 12:56 geschrieben:

>
> On 12 Apr 2010, at 5:59am, Steve Bywaters wrote:
>
> > I am currently using a proprietary ISAM database for an application.
> > These files are .TPS/Topspeed format files, for Clarion, which have built-in
> > encryption via a 'password'.
> >
> > Since sensitive setup and sales data is contained in these, I would want to
> > maintain that level of security.
> > Any way I can do that with a SQLite db?
> > Both for access (ODBC) and for content (should not be human-readable via
> > query).
>
> The main architect behind SQLite runs a company that maintains a non-free
> encryption add-on to SQLite.  You use all the SQLite commands just as you
> would with an unencrypted database, but you pass in a password when, or just
> after, you open the file.  Without the password you get nothing.  And reading
> the file from the disk just gets you gibberish.  For further details see
>
> 
>
> 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] How SQLite solves these issues

[Simon Slavin]

On 12 Apr 2010, at 11:47am, Navaneeth Sen B wrote:

> Hi Simon,
>> These two are not problems, depending on how 'simultaneous' you want to be 
>> (milliseconds ?). SQLite handles multi-user locking if your CE platform and 
>> operating system does.  By the way, I don't know what 'CE' means.
> 
> Does that mean it does not support this kind of concurrency?
> Can you please explain how SQLite handles multi-user locking?
> I have read somewhere in the SQLite site that it does not support High 
> concurrency, like when you are reading a part of the database, you cant 
> write to the other part of the DB.

SQLite does not lock at the record level.  When you commit a change to the 
database the entire database is locked for a short time.  But it is a very 
short time.  If you are only reading or writing 3000 records at a time, and you 
have appropriate indexes, then this is unlikely to be a problem for you.  But 
you should probably test an SQLite solution to get an idea how long these 
updates and queries will take for your platform.

And thanks for your explanation of 'CE'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How SQLite solves these issues

[Hamish Allan]

On Mon, Apr 12, 2010 at 11:33 AM, Navaneeth Sen B
 wrote:

> Thanks Hamish,
>
> But can you give me more clarity for the sentence in the quoted text.
>> However, it doesn't store files, it stores data. If you need to query
>> (meta)data from a particular file format, you'll have to extract it
>> yourself.
>>

What I mean is that if you want to, say, order the results of a query
by shutter speed, you'd have to extract the shutter speeds from the
AVCHD files yourself and put them into a "shutterspeed" column in your
table. SQLite doesn't know anything about AVCHD files (or any others
AFAIK).

Best wishes,
Hamish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] encryption

[Simon Slavin]

On 12 Apr 2010, at 5:59am, Steve Bywaters wrote:

> I am currently using a proprietary ISAM database for an application.
> These files are .TPS/Topspeed format files, for Clarion, which have built-in 
> encryption via a 'password'.
> 
> Since sensitive setup and sales data is contained in these, I would want to 
> maintain that level of security.
> Any way I can do that with a SQLite db?
> Both for access (ODBC) and for content (should not be human-readable via 
> query).

The main architect behind SQLite runs a company that maintains a non-free 
encryption add-on to SQLite.  You use all the SQLite commands just as you would 
with an unencrypted database, but you pass in a password when, or just after, 
you open the file.  Without the password you get nothing.  And reading the file 
from the disk just gets you gibberish.  For further details see



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How SQLite solves these issues

[Navaneeth Sen B]

Hi Simon,
> These two are not problems, depending on how 'simultaneous' you want to be 
> (milliseconds ?).  SQLite handles multi-user locking if your CE platform and 
> operating system does.  By the way, I don't know what 'CE' means.
>
>
Does that mean it does not support this kind of concurrency?
Can you please explain how SQLite handles multi-user locking?
I have read somewhere in the SQLite site that it does not support High 
concurrency, like when you are reading a part of the database, you cant 
write to the other part of the DB.

Regards,
Sen



On 4/12/2010 3:59 PM, Simon Slavin wrote:
> On 12 Apr 2010, at 10:58am, Navaneeth Sen B wrote:
>
>
>> Currently I am working in a project where we are developing a CE
>> product, which is using a DB which was developed by one of our teams.
>> I am facing some of the below mentioned issues with the current DB:
>>
>> * Listing of more than 3000 .jpeg files will produce a system hang
>>  
> SQLite can certainly return more than 3000 records without problems.  But I'm 
> not sure why you're asking about SQLite but talking about .jpeg files.  A 
> file is a file.  It lives in a directory on a disk, not in a database.  If 
> you want to keep picture information in a database, it's just another BLOB 
> field, not a file at all.
>
>
>> * Cannot simultaneously update and list the contents in the table
>> * Sorting can be done only by using a maximum of two fields
>>  
> These two are not problems, depending on how 'simultaneous' you want to be 
> (milliseconds ?).  SQLite handles multi-user locking if your CE platform and 
> operating system does.  By the way, I don't know what 'CE' means.
>
>
>> [snip]
>>
>> I would also like to know how SQLite stores AVCHD files in the DB.
>> As you know AVCHD files have a peculiar directory structure(association
>> with .cpi files), how is it handled in SQLite?
>>  
> Same comment as about the .jpeg files: it doesn't, and nor does any other SQL 
> database system.  No database system stores files.  That's what a file system 
> does.  A database could store the bytes that made up a file, but as you point 
> out AVCHD 'files' are not files at all, they're collections of files 
> (directories, but more like 'bundles' if you're used to Mac OS X), so you'd 
> need to invent a convention for explaining which bytes came from which file.
>
> I think you need to decide how you want to handle these files.  Either keep 
> the files on disk and just store the path/filename in a database field (in 
> which case they're just short text fields and trivial to handle) or read the 
> bytes out of the files and store them in BLOB fields (which will take a lot 
> of programming and your database will be huge).  Either way, the database is 
> still not storing a file, it's storing text or a BLOB.
>
> 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] Question about lemon

[Andy Gibbs]

Hi Allan,

> Instead of adding the definition
>
> cmd ::= set_item.
>
> have you thought about defining the acceptable alternatives to "SET"?
> For instance,
>
> set ::= SET.
> set ::= .
> cmd ::= set set_list.

Thank you for the suggestion.  I have just tried this and unfortunately, it 
doesn't work in my scenario, but still causes a parsing error in the same 
place.


Hi Ron,

> Have you tried implementing your parser in BISON or any other parser
> generator?  Many of the same lessons apply and I bet the BISON users
> list would be more helpful than the sqlite users list.

I haven't tried Bison: Lemon seemed so much easier to use and understand, 
and has been otherwise perfect for the task in hand.  However, if I have 
some time, I will look into trying it in Bison instead.

If I find an answer I'll post it here!...

Thanks.

Andy


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] encryption

[Steve Bywaters]

I am currently using a proprietary ISAM database for an application.
These files are .TPS/Topspeed format files, for Clarion, which have built-in 
encryption via a 'password'.

Since sensitive setup and sales data is contained in these, I would want to 
maintain that level of security.
Any way I can do that with a SQLite db?
Both for access (ODBC) and for content (should not be human-readable via query).

Steve
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How SQLite solves these issues

[Navaneeth Sen B]

Thanks Simon,
> SQLite can certainly return more than 3000 records without problems.  But I'm 
> not sure why you're asking about SQLite but talking about .jpeg files.  A 
> file is a file.  It lives in a directory on a disk, not in a database.  If 
> you want to keep picture information in a database, it's just another BLOB 
> field, not a file at all.
>
I am sorry, That was a mistake.
Actually my DB is dealing with jpeg and avchd files.
I meant "data"

CE means Consumer Electronic :-)

Regards,
Sen

On 4/12/2010 3:59 PM, Simon Slavin wrote:
> On 12 Apr 2010, at 10:58am, Navaneeth Sen B wrote:
>
>
>> Currently I am working in a project where we are developing a CE
>> product, which is using a DB which was developed by one of our teams.
>> I am facing some of the below mentioned issues with the current DB:
>>
>> * Listing of more than 3000 .jpeg files will produce a system hang
>>  
>
>
>> * Cannot simultaneously update and list the contents in the table
>> * Sorting can be done only by using a maximum of two fields
>>  
> These two are not problems, depending on how 'simultaneous' you want to be 
> (milliseconds ?).  SQLite handles multi-user locking if your CE platform and 
> operating system does.  By the way, I don't know what 'CE' means.
>
>
>> [snip]
>>
>> I would also like to know how SQLite stores AVCHD files in the DB.
>> As you know AVCHD files have a peculiar directory structure(association
>> with .cpi files), how is it handled in SQLite?
>>  
> Same comment as about the .jpeg files: it doesn't, and nor does any other SQL 
> database system.  No database system stores files.  That's what a file system 
> does.  A database could store the bytes that made up a file, but as you point 
> out AVCHD 'files' are not files at all, they're collections of files 
> (directories, but more like 'bundles' if you're used to Mac OS X), so you'd 
> need to invent a convention for explaining which bytes came from which file.
>
> I think you need to decide how you want to handle these files.  Either keep 
> the files on disk and just store the path/filename in a database field (in 
> which case they're just short text fields and trivial to handle) or read the 
> bytes out of the files and store them in BLOB fields (which will take a lot 
> of programming and your database will be huge).  Either way, the database is 
> still not storing a file, it's storing text or a BLOB.
>
> 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] How SQLite solves these issues

[Navaneeth Sen B]

Thanks Hamish,

But can you give me more clarity for the sentence in the quoted text.
> However, it doesn't store files, it stores data. If you need to query
> (meta)data from a particular file format, you'll have to extract it
> yourself.
>
Regards,
Sen

_

On 4/12/2010 3:44 PM, Hamish Allan wrote:
> Hi,
>
> SQLite will outperform the DB you describe in every aspect.
>
> However, it doesn't store files, it stores data. If you need to query
> (meta)data from a particular file format, you'll have to extract it
> yourself.
>
> Best wishes,
> Hamish
>
> On Mon, Apr 12, 2010 at 10:58 AM, Navaneeth Sen B
>   wrote:
>
>> Hi all,
>>
>> Currently I am working in a project where we are developing a CE
>> product, which is using a DB which was developed by one of our teams.
>> I am facing some of the below mentioned issues with the current DB:
>>
>> * Listing of more than 3000 .jpeg files will produce a system hang
>> * Cannot simultaneously update and list the contents in the table
>> * Sorting can be done only by using a maximum of two fields
>>
>> So right now we are thinking about moving to SQLite as it has proved its
>> features in many CE devices.
>> But still I would like to get more clarity in the above issues and I
>> would like to know how SQLite solves the above issues.
>>
>> I would also like to know how SQLite stores AVCHD files in the DB.
>> As you know AVCHD files have a peculiar directory structure(association
>> with .cpi files), how is it handled in SQLite?
>>
>> Regards,
>> Sen
>> /
>> ///
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How SQLite solves these issues

[Simon Slavin]

On 12 Apr 2010, at 10:58am, Navaneeth Sen B wrote:

> Currently I am working in a project where we are developing a CE 
> product, which is using a DB which was developed by one of our teams.
> I am facing some of the below mentioned issues with the current DB:
> 
>* Listing of more than 3000 .jpeg files will produce a system hang

SQLite can certainly return more than 3000 records without problems.  But I'm 
not sure why you're asking about SQLite but talking about .jpeg files.  A file 
is a file.  It lives in a directory on a disk, not in a database.  If you want 
to keep picture information in a database, it's just another BLOB field, not a 
file at all.

>* Cannot simultaneously update and list the contents in the table
>* Sorting can be done only by using a maximum of two fields

These two are not problems, depending on how 'simultaneous' you want to be 
(milliseconds ?).  SQLite handles multi-user locking if your CE platform and 
operating system does.  By the way, I don't know what 'CE' means.

> [snip]
> 
> I would also like to know how SQLite stores AVCHD files in the DB.
> As you know AVCHD files have a peculiar directory structure(association 
> with .cpi files), how is it handled in SQLite?

Same comment as about the .jpeg files: it doesn't, and nor does any other SQL 
database system.  No database system stores files.  That's what a file system 
does.  A database could store the bytes that made up a file, but as you point 
out AVCHD 'files' are not files at all, they're collections of files 
(directories, but more like 'bundles' if you're used to Mac OS X), so you'd 
need to invent a convention for explaining which bytes came from which file.

I think you need to decide how you want to handle these files.  Either keep the 
files on disk and just store the path/filename in a database field (in which 
case they're just short text fields and trivial to handle) or read the bytes 
out of the files and store them in BLOB fields (which will take a lot of 
programming and your database will be huge).  Either way, the database is still 
not storing a file, it's storing text or a BLOB.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How SQLite solves these issues

[Hamish Allan]

Hi,

SQLite will outperform the DB you describe in every aspect.

However, it doesn't store files, it stores data. If you need to query
(meta)data from a particular file format, you'll have to extract it
yourself.

Best wishes,
Hamish

On Mon, Apr 12, 2010 at 10:58 AM, Navaneeth Sen B
 wrote:
> Hi all,
>
> Currently I am working in a project where we are developing a CE
> product, which is using a DB which was developed by one of our teams.
> I am facing some of the below mentioned issues with the current DB:
>
>    * Listing of more than 3000 .jpeg files will produce a system hang
>    * Cannot simultaneously update and list the contents in the table
>    * Sorting can be done only by using a maximum of two fields
>
> So right now we are thinking about moving to SQLite as it has proved its
> features in many CE devices.
> But still I would like to get more clarity in the above issues and I
> would like to know how SQLite solves the above issues.
>
> I would also like to know how SQLite stores AVCHD files in the DB.
> As you know AVCHD files have a peculiar directory structure(association
> with .cpi files), how is it handled in SQLite?
>
> Regards,
> Sen
> /
> ///
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How SQLite solves these issues

[Navaneeth Sen B]

Hi all,

Currently I am working in a project where we are developing a CE 
product, which is using a DB which was developed by one of our teams.
I am facing some of the below mentioned issues with the current DB:

* Listing of more than 3000 .jpeg files will produce a system hang
* Cannot simultaneously update and list the contents in the table
* Sorting can be done only by using a maximum of two fields

So right now we are thinking about moving to SQLite as it has proved its 
features in many CE devices.
But still I would like to get more clarity in the above issues and I 
would like to know how SQLite solves the above issues.

I would also like to know how SQLite stores AVCHD files in the DB.
As you know AVCHD files have a peculiar directory structure(association 
with .cpi files), how is it handled in SQLite?

Regards,
Sen
/
///


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for Backup

[Simon Slavin]

On 12 Apr 2010, at 6:46am, Navaneeth Sen B wrote:

> I just want to know whether SQLite support automatic backup of the DB 
> contents.
> Or is there any other DBs which support this feature?

What do you mean by 'automatic backup' ?  I don't think this term has a 
commonly used definition.

I can tell you the following: If a database isn't in use you can just copy the 
once file and get the entire database.  SQLite does supply an API for copying a 
database which is in use, but you can't just copy the files.  SQLite does not 
support undo once a transaction is committed.  Committed transactions are 
flushed to disk very quickly.  SQLite supports ACID about as well as the 
underlying OS and file system can.

If you'd like to give a definition of 'automatic backup' we may be able to help 
more.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users