Re: [sqlite] Compressing the DBs?

[Bill KING]

Gussimulator wrote:
> Hello there,
>
> This is what I mean by repetitive data:
>
> Tables:
> E:\DirectX90c\
> E:\DirectX90c\Feb2006_MDX1_x86_Archive.cab\
> E:\DirectX90c\Feb2006_d3dx9_29_x64.cab\
> E:\DirectX90c\Feb2006_xact_x64.cab\
> E:\DirectX90c\Feb2006_MDX1_x86.cab\
> E:\DirectX90c\Feb2006_xact_x86.cab\
>
> And so on, As you can see, the string E:\DirectX90c\ repeats all the
> time in this example. (Also does "Feb2006_" on almost every table).
>
> It's just an example of the type of repetitive data I have to deal
> with, they are normally paths. Since theres directories within
> directories, the paths repeat.
>
> What would be an ideal aproach for this situation?, I would like to
> save space, but I wouldnt like to waste a big amount of processing
> power to do so.
>
> One must keep in mind that my system must perform "well" on various
> situations (which I cant predict, at least not all of them), for this
> reason I cant have a very elaborated database scheme. Sometimes saving
> a few KBs could mean wasting a few tons of cycles, and I can't deal
> with that. I'd rather have those extra KBs and deal with a responsive
> application, than saving a few KBs and falling asleep at the keyboard
> (don't worry, it's a multi-threaded environment, however it's
> important to keep it optimized, I'm just over-sizing the problem a
> little).
>
>
> I'd like to take the right 'path' here...
> Thanks.
>
>
>
>
>
>
> - Original Message - From: "Darren Duncan"
> <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, July 06, 2006 12:04 AM
> Subject: Re: [sqlite] Compressing the DBs?
>
>
>> At 6:04 PM -0300 7/5/06, Gussimulator wrote:
>>> Now, since theres a lot of repetitive data, I thought that
>>> compressing the database would be a good idea, since, we all know..
>>> One of the first principles of data compression is getting rid of
>>> repetitive data, so... I was wondering if this is possible with
>>> SQLite or it would be quite a pain to implement a compression scheme
>>> by myself?.. I have worked with many compression libraries before so
>>> that wouldnt be an issue, the issue however, would be to implement
>>> any of the libraries into SQLite...
>>
>> First things first, what do you mean by "repetitive"?
>>
>> Do you mean that there are many copies of the same data?
>>
>> Perhaps a better approach is to normalize the database and just store
>> single copies of things.
>>
>> If you have tables with duplicate rows, then add a 'quantity' column
>> and reduce to one copy of the actual data.
>>
>> If some columns are unique and some are repeated, perhaps try
>> splitting the tables into more tables that are related.
>>
>> This, really, is what you should be doing first, and may very well be
>> the only step you need.
>>
>> If you can't do that, then please explain in what way the data is
>> repetitive?
>>
>> -- Darren Duncan 
>
>
We came across this with our filesystem metainfo system, what we ended
up doing was creating a sub-table called "location". with this location
it can be used either via join in the sql statement, or via a cached
internal structure to recreate the path of a file. (Easy enough with a
map<> or a hash<> style bucket class). No major overhead costs, but a
definate savings in space. (Each directory becomes an entry in the
location table, so a file is then stored as a location key value +
filename).

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Compressing the DBs?

[Gussimulator]

Hello there,

This is what I mean by repetitive data:

Tables:
E:\DirectX90c\
E:\DirectX90c\Feb2006_MDX1_x86_Archive.cab\
E:\DirectX90c\Feb2006_d3dx9_29_x64.cab\
E:\DirectX90c\Feb2006_xact_x64.cab\
E:\DirectX90c\Feb2006_MDX1_x86.cab\
E:\DirectX90c\Feb2006_xact_x86.cab\

And so on, As you can see, the string E:\DirectX90c\ repeats all the time in 
this example. (Also does "Feb2006_" on almost every table).


It's just an example of the type of repetitive data I have to deal with, 
they are normally paths. Since theres directories within directories, the 
paths repeat.


What would be an ideal aproach for this situation?, I would like to save 
space, but I wouldnt like to waste a big amount of processing power to do 
so.


One must keep in mind that my system must perform "well" on various 
situations (which I cant predict, at least not all of them), for this reason 
I cant have a very elaborated database scheme. Sometimes saving a few KBs 
could mean wasting a few tons of cycles, and I can't deal with that. I'd 
rather have those extra KBs and deal with a responsive application, than 
saving a few KBs and falling asleep at the keyboard (don't worry, it's a 
multi-threaded environment, however it's important to keep it optimized, I'm 
just over-sizing the problem a little).



I'd like to take the right 'path' here...
Thanks.






- Original Message - 
From: "Darren Duncan" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, July 06, 2006 12:04 AM
Subject: Re: [sqlite] Compressing the DBs?



At 6:04 PM -0300 7/5/06, Gussimulator wrote:
Now, since theres a lot of repetitive data, I thought that compressing the 
database would be a good idea, since, we all know.. One of the first 
principles of data compression is getting rid of repetitive data, so... I 
was wondering if this is possible with SQLite or it would be quite a pain 
to implement a compression scheme by myself?.. I have worked with many 
compression libraries before so that wouldnt be an issue, the issue 
however, would be to implement any of the libraries into SQLite...


First things first, what do you mean by "repetitive"?

Do you mean that there are many copies of the same data?

Perhaps a better approach is to normalize the database and just store 
single copies of things.


If you have tables with duplicate rows, then add a 'quantity' column and 
reduce to one copy of the actual data.


If some columns are unique and some are repeated, perhaps try splitting 
the tables into more tables that are related.


This, really, is what you should be doing first, and may very well be the 
only step you need.


If you can't do that, then please explain in what way the data is 
repetitive?


-- Darren Duncan 

Re: [sqlite] Compressing the DBs?

[Darren Duncan]

At 6:04 PM -0300 7/5/06, Gussimulator wrote:
Now, since theres a lot of repetitive data, I thought that 
compressing the database would be a good idea, since, we all know.. 
One of the first principles of data compression is getting rid of 
repetitive data, so... I was wondering if this is possible with 
SQLite or it would be quite a pain to implement a compression scheme 
by myself?.. I have worked with many compression libraries before so 
that wouldnt be an issue, the issue however, would be to implement 
any of the libraries into SQLite...


First things first, what do you mean by "repetitive"?

Do you mean that there are many copies of the same data?

Perhaps a better approach is to normalize the database and just store 
single copies of things.


If you have tables with duplicate rows, then add a 'quantity' column 
and reduce to one copy of the actual data.


If some columns are unique and some are repeated, perhaps try 
splitting the tables into more tables that are related.


This, really, is what you should be doing first, and may very well be 
the only step you need.


If you can't do that, then please explain in what way the data is repetitive?

-- Darren Duncan

Re: [sqlite] Multiple Users

[John Stanton]

Ann don't cry into your beer, there is always next time!  My team also 
got knocked out by Italy.


Your approach makes perfect sense to me.  Perhaps you can achieve it be 
defining a database wrapper which encapsulates the DBMS APIs and 
provides a standard interface to your applications.  You might need to 
add some deadlock detection and synchronization in the Sqlite wrapper.


My CS Professor used to say "There is no problem in Computer Science 
which cannot be solved by yet another level of abstraction" (I hope your 
English is good enough for that, I cannot put it into German).


[EMAIL PROTECTED] wrote:

 Original-Nachricht 
Datum: Wed, 05 Jul 2006 13:01:12 -0500
Von: John Stanton <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users



First, sad about the football, but someone has to win.



Its only football... and so hard it is... the italian players 
are the better players. But our coach has done a greatly work.

We can be content with this result.


My point that since Sqlite is embedded its only locking method is the 
fcntl type file lock.  Any other level of locking has to be supplied by 
your application.  It cannot perform row and table locks like a DB 
server.



Thats not my intention. I dont want to blow up a Single-File-DB like
SQLite to a Server-Based DBMS like MS SQL-Server. 

But I work with various DB, and I like to do handle all at the 
same way. It is be much easier, to access and repeat my own 
knowledge, if I support a frontend and all frontend looks similarly

at the Database-Connect-Level.

And... why should I don't handle a SQLite-DB as a Backend like a Server-Based 
DBMS? Its imho primary the same.

If I allow more than one User to "connect" to a DB, I have to
manage the access of all this users, particularly the concurrent Edit 
of one and the same Record. The Problem exist not during the Dialog-

Edit. The Problem occurs after read Data and stay on Screen, at the
End-Editing, then, if changes written to Disk. 

Other Users is allowed to fetch this Record too and present it in his 
own View - but only as Read-Only. I do it at the same Way like SAP. 
All User can fetch "this" record and present it in Dialog-View, but 
only the first reader can edit and store his Changes to DB.


Greetings, Anne

Re: [sqlite] Syntax Errors with various strings?

[Gussimulator]

Thanks Gerry,
My strings are normal filenames with their paths, so as you can see there 
arent many characters that would ruin my day, however you're right about the 
' char.


I'll make my own quote(); routine and then I'll compare with the SQLite one 
by doing a small benchmark, see whats best for my case. I guess mine will 
win since I'll be able to make a macro in ASM. Instead of a DLL call, etc. 
Just by having the proc call, we lose cycles.








- Original Message - 
From: "Gerry Snyder" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, July 05, 2006 5:13 PM
Subject: Re: [sqlite] Syntax Errors with various strings?



Gussimulator wrote:

Thanks Christian, I'll give it a shot later.
Just because using quotation marks (did a quick macro that adds them to 
my string) did the job, doesnt mean its a better solution than this one.


In particular, if your strings include the  '  character, you will need to 
double it (make it  ''  ) somehow, and the quote() function will take care 
of this, as well as enclosing the string in quotation marks.


Gerry 

Re: [sqlite] Compressing the DBs?

[Mikey C]

What platform are you using?

If you are using NTFS filesystem you can just mark the file for compression
and the OS takes care of it transparently.


-- 
View this message in context: 
http://www.nabble.com/Compressing-the-DBs--tf1897195.html#a5190175
Sent from the SQLite forum at Nabble.com.

[sqlite] Compressing the DBs?

[Gussimulator]

I've been using SQLite for a very short period of time and so far Its doing a 
great job for my application (single user, quite a big amount of data though).

Now, since theres a lot of repetitive data, I thought that compressing the 
database would be a good idea, since, we all know.. One of the first principles 
of data compression is getting rid of repetitive data, so... I was wondering if 
this is possible with SQLite or it would be quite a pain to implement a 
compression scheme by myself?.. I have worked with many compression libraries 
before so that wouldnt be an issue, the issue however, would be to implement 
any of the libraries into SQLite...

So, before I waste my time with something that perhaps has been done already, I 
decided to ask here. Is there any compression scheme I can use? or I'm doomed 
to implement this by myself? (any tips?).

Thanks a lot. 

PS: Sorry if I didnt read the manual enough.

Re: [sqlite] sqlite3_free()

[jason . ctr . alburger]

Thanks you for your help!

Jason Alburger
HID/NAS/LAN Engineer
L3/ATO-E En Route Peripheral Systems Support
609-485-7225


   
 [EMAIL PROTECTED] 
   
 07/05/2006 03:34   To 
 PMsqlite-users@sqlite.org 
cc 
   
 Please respond to Subject 
 [EMAIL PROTECTED] Re: [sqlite] sqlite3_free() 
  te.org   
   
   
   
   
   




[EMAIL PROTECTED] wrote:
> A few quick questions...
>
> If an error is returned by sqlite3_exec( ) , I believe the error message
is
> written in the (char **errmsg) provided in the 5 parameter of the
function
> call.
>
>   1. Is this the same error text that can be obtained by
> sqlite3_errmsg( ) ?

Maybe or maybe not.  The error message returned by sqlite3_exec()
might be more detailed.  Depends on the error.

>   2. If this is the same error text that can be obtained by
> sqlite3_errmsg( ), what happens if I call sqlite3_free( ) before
> sqlite3_errmsg( )?
>

By "the same" I mean the same sequence of bytes.  The error message
from sqlite3_exec() is always obtained from a malloc-like memory
allocator and must be freed using sqlite3_free().  The error message
returned by sqlite3_errmsg() is always a constant, static string.
The error messages might say the same thing, but they are distinct
strings.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Syntax Errors with various strings?

[Gerry Snyder]

Gussimulator wrote:

Thanks Christian, I'll give it a shot later.
Just because using quotation marks (did a quick macro that adds them 
to my string) did the job, doesnt mean its a better solution than this 
one.


In particular, if your strings include the  '  character, you will need 
to double it (make it  ''  ) somehow, and the quote() function will take 
care of this, as well as enclosing the string in quotation marks.


Gerry

Re: [sqlite] Syntax Errors with various strings?

[Gussimulator]

Thanks Christian, I'll give it a shot later.
Just because using quotation marks (did a quick macro that adds them to my 
string) did the job, doesnt mean its a better solution than this one.





- Original Message - 
From: "Christian Nassau" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, July 05, 2006 7:18 AM
Subject: Re: [sqlite] Syntax Errors with various strings?



Have you tried using the quote() function?

From http://www.sqlite.org/lang_expr.html

quote(X) This routine returns a string which is the value of its
argument suitable for inclusion into another SQL statement. Strings are
surrounded by single-quotes with escapes on interior quotes as needed.
BLOBs are encoded as hexadecimal literals. The current implementation of
VACUUM uses this function. The function is also useful when writing
triggers to implement undo/redo functionality.


Gussimulator wrote:
Hi, When I have strings with "-", "!",  "\" or similar characters I get a 
syntax error.. Now, I thought this would happen with a few of this chars 
so I made 2 routines in my program, one that converts each of this chars 
into a flag string, which then, by the other routine can be reverted to 
the original characters to obtain the string in its original form, so I 
can later work with it by my side.


Now, I've found myself with syntax errors even on strings that didnt had 
any strange characters, So.. I'm wondering, what can I do to prevent 
this? Does SQLite provide a "format" routine or is there anything I can 
do to prevent the syntax errors... like this ones? (since my data wont 
get into the db if theres an error, of course, thats why Im concerned).


I'm really worried about this, hence I subscribed on the list (first 
message!).  I hope someone can help me out on this one, thanks.


And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find 
myself with this type of problem.






--
---
Christian Nassau
Software Developer
---
Swissrisk
Holzhausenstrasse 44,
60322 Frankfurt, Germany

tele: +49 69 50952-266
fax:  +49 69 50952-299

www.swissrisk.com
--- 

Re: Re: [sqlite] Multiple Users

[Anne . Kirchhellen]

 Original-Nachricht 
Datum: Wed, 05 Jul 2006 19:30:04 +0100
Von: Nikki Locke <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

> We seem to have a misunderstanding over the definition of "optomistic 
> locking".

Yes, maybe...  ;-)

> To me, "optimistic" locking means the application doesn't do any locking 
> when a record is read. All it does it to retain the timestamp of the
> record when it was read.

I'm oriented to the definitons from OLEDB
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstlocktypeenum.asp

Citation:
adLockOptimistic
Indicates optimistic locking, record by record. 
The provider uses optimistic locking, locking records only when you 
call the Update method.

adLockPessimistic   
Indicates pessimistic locking, record by record. The provider 
does what is necessary to ensure successful editing of the 
records, usually by locking records at the data source 
immediately after editing.

> When the application wants to write edited records back to the database,
> it does something like...

Likewise

First, if I want to fetch a Record, I allways store a timestamp in 
combination with the Records-ID and further needed parameters. Then 
I search for a older Timestamp with exactly equal parameters. If I 
dont found anyone, I'm the first User with authorisation to change 
this record. After this any other User can only store a Timestamp it 
be younger as mine. All Edit-Controls on View are Write-Protected in 
this case. The Statusline shows a Message, which User is owner of 
this Record.

My Problems are now solved. SQLite performs a physical locking at
the DB-File during writing to Disk. Any other Frontend-Instances
do wait, until the Writing is ready. The Writer-Frontend release
the DB after the succesfully end of the Write-Process. I dont have 
to do anything... SQlite is a clever DB  ;-)

Greetings, Anne


-- 


Echte DSL-Flatrate dauerhaft für 0,- Euro*!
"Feel free" mit GMX DSL! http://www.gmx.net/de/go/dsl

Re: [sqlite] Syntax Errors with various strings? & ...Ampersand

[Gussimulator]

Enclosing the strings with quotation marks did the job.
Thanks a lot.



- Original Message - 
From: "C.Peachment" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, July 05, 2006 6:39 AM
Subject: Re: [sqlite] Syntax Errors with various strings? & ...Ampersand



The solution I have adopted to both of these issues is to:

1. ensure all strings are enclosed by matching quotation marks, and

2. use the question mark substitution form of prepared statement
with subsequent bind of parameters. This can only be done from a
programming language and not the command line interface.


On Wed, 05 Jul 2006 10:44:31 +0200, Roger wrote:


I have a company name as follows:



Chemistry & chemicals



I have plenty of those in my database which come with ampersands, now
when i do a query i get nothing.



How best can i write the query using a string with an ampersand as part
of it.



I am developing in a PHP/SQlite environment.



On Wed, 5 Jul 2006 06:07:46 -0300, Gussimulator wrote:

Hi, When I have strings with "-", "!",  "\" or similar characters I get a 
syntax error.. Now, I thought this would happen with a few of this chars 
so I
made 2 routines in my program, one that converts each of this chars into a 
flag string, which then, by the other routine can be reverted to the
original characters to obtain the string in its original form, so I can 
later work with it by my side.


Now, I've found myself with syntax errors even on strings that didnt had 
any strange characters, So.. I'm wondering, what can I do to prevent this?
Does SQLite provide a "format" routine or is there anything I can do to 
prevent the syntax errors... like this ones? (since my data wont get into 
the

db if theres an error, of course, thats why Im concerned).

I'm really worried about this, hence I subscribed on the list (first 
message!).  I hope someone can help me out on this one, thanks.


And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find 
myself with this type of problem.

Re: [sqlite] sqlite3_free()

[drh]

[EMAIL PROTECTED] wrote:
> A few quick questions...
> 
> If an error is returned by sqlite3_exec( ) , I believe the error message is
> written in the (char **errmsg) provided in the 5 parameter of the function
> call.
> 
>   1. Is this the same error text that can be obtained by
> sqlite3_errmsg( ) ?

Maybe or maybe not.  The error message returned by sqlite3_exec()
might be more detailed.  Depends on the error.

>   2. If this is the same error text that can be obtained by
> sqlite3_errmsg( ), what happens if I call sqlite3_free( ) before
> sqlite3_errmsg( )?
> 

By "the same" I mean the same sequence of bytes.  The error message
from sqlite3_exec() is always obtained from a malloc-like memory
allocator and must be freed using sqlite3_free().  The error message
returned by sqlite3_errmsg() is always a constant, static string.
The error messages might say the same thing, but they are distinct
strings.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: Re: [sqlite] Multiple Users

[Anne . Kirchhellen]

 Original-Nachricht 
Datum: Wed, 05 Jul 2006 13:01:12 -0500
Von: John Stanton <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

> First, sad about the football, but someone has to win.

Its only football... and so hard it is... the italian players 
are the better players. But our coach has done a greatly work.
We can be content with this result.

> My point that since Sqlite is embedded its only locking method is the 
> fcntl type file lock.  Any other level of locking has to be supplied by 
> your application.  It cannot perform row and table locks like a DB 
> server.

Thats not my intention. I dont want to blow up a Single-File-DB like
SQLite to a Server-Based DBMS like MS SQL-Server. 

But I work with various DB, and I like to do handle all at the 
same way. It is be much easier, to access and repeat my own 
knowledge, if I support a frontend and all frontend looks similarly
at the Database-Connect-Level.

And... why should I don't handle a SQLite-DB as a Backend like a Server-Based 
DBMS? Its imho primary the same.

If I allow more than one User to "connect" to a DB, I have to
manage the access of all this users, particularly the concurrent Edit 
of one and the same Record. The Problem exist not during the Dialog-
Edit. The Problem occurs after read Data and stay on Screen, at the
End-Editing, then, if changes written to Disk. 

Other Users is allowed to fetch this Record too and present it in his 
own View - but only as Read-Only. I do it at the same Way like SAP. 
All User can fetch "this" record and present it in Dialog-View, but 
only the first reader can edit and store his Changes to DB.

Greetings, Anne
-- 


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

[sqlite] sqlite3_free()

[jason . ctr . alburger]

A few quick questions...

If an error is returned by sqlite3_exec( ) , I believe the error message is
written in the (char **errmsg) provided in the 5 parameter of the function
call.

  1. Is this the same error text that can be obtained by
sqlite3_errmsg( ) ?
  2. If this is the same error text that can be obtained by
sqlite3_errmsg( ), what happens if I call sqlite3_free( ) before
sqlite3_errmsg( )?

Jason Alburger
HID/NAS/LAN Engineer
L3/ATO-E En Route Peripheral Systems Support
609-485-7225

Re: [sqlite] Multiple Users

[John Stanton]

First, sad about the football, but someone has to win.

My point that since Sqlite is embedded its only locking method is the 
fcntl type file lock.  Any other level of locking has to be supplied by 
your application.  It cannot perform row and table locks like a DB server.


The extreme simplicity in use of Sqlite is because it uses one file and 
no server.  It is not possible to emulate Oracle with one file and no 
server.


[EMAIL PROTECTED] wrote:

 Original-Nachricht 
Datum: Tue, 04 Jul 2006 19:13:56 -0500
Von: John Stanton <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

Hi John

I think, I understand what you will to say 



Sqlite is a single file shared between users just like a word processing 
file shared between multiple users.  It is not a DBMS server and just 
uses the regular fcntl type file locks.



but... I want try to ask again...  ;-)

SQLite handles the File-Lockings as a physical Locking during the 
writings to File and Disk. (Is it so?) I do my own logical Locking 
during the Edit of a Record and then reflect to the Busy-State if the 
changes will be saved to disk. 
The different point of views are intended from a logical Lock during 
Edit and a physical Lock during storing the Data to Disk. A logical 
Locking can hold it for a long time, several minutes, to a hour ... so 
long the user need to hold this Record for Editing. But there is during

editing no affect to DB. This will done very well in my Logical-Locking-
Concept. The physical File-Locking needs only milliseconds, even to write 
changes to Disk, if DB Ready and not Busy. 

Now then...if the User-Edit endet, the changes will be saved to File 
and written to Disk, and that is controlled bei File- Locking through
SQLite, so that multiple Diskwritings to one DB do not end in a 
collision.


Is that accurately described?

Greeting from Germany
Anne

Re: Re: [sqlite] Multiple Users

[Anne . Kirchhellen]

 Original-Nachricht 
Datum: Wed, 05 Jul 2006 07:32:47 -0600
Von: Dennis Cote <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

> Anne,
> 
> Yes, ...

A simple "Yes"   :-)))

>...your description is correct

Hi Dennis
Many thanks to you too. That brings me lightness ... 

Greeting from Germany, Anne
-- 


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

Re: Re: [sqlite] Multiple Users

[Anne . Kirchhellen]

Hi Nikki

Many thanks for your answer. Its really helpful.
First time it seems to me, that I understand the problem.
In the past it happens, that the message in some Postings sometimes
lives behind a Fog  :-))) Thinks dont will be get clearly.

Its sometimes not so easy, to put it on the same line, what in the
posting is written, and what the poster did mean. Sometimes it would 
be easier, if the answer to a question contains only a Yes or a No. 
In this case, I try to perform my question, that is a one of this possible... 
to hope, I can leave my helplessness...

 Original-Nachricht 
Datum: Wed, 05 Jul 2006 12:05:07 +0100
Von: Nikki Locke <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

> ...the way SQLite works during a database update is that the 
> entire database file is locked, and remains locked until the update is 
> complete. If you use transactions, the entire database file is locked for
> the entire transaction (*).

It seems to me, that is (looked from the viewpoint "Frontend") another
form of optimistic locking. I only have to look at the DB's busystate, 
until my changes are succesfully written to DB. 

> Provided you code handles the errors (by retrying the update until it 
> succeeds), it should work fine. 

Yep, it seems, that would solve my problems... 

Best greeetings from Germany ;-)
Anne
-- 


Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

Re: [sqlite] Syntax Errors with various strings?

[Dennis Cote]

Gussimulator wrote:

Hi, When I have strings with "-", "!",  "\" or similar characters I get a 
syntax error.. Now, I thought this would happen with a few of this chars so I made 2 routines in my program, 
one that converts each of this chars into a flag string, which then, by the other routine can be reverted to 
the original characters to obtain the string in its original form, so I can later work with it by my side.

  
When do you get these syntax errors and how are the strings being passed 
to SQLite? Are you using the correct single quote character (i.e. the ' 
not the ") as a string delimiter?


Dennis Cote

Re: [sqlite] Multiple Users

[Dennis Cote]

[EMAIL PROTECTED] wrote:


but... I want try to ask again...  ;-)

SQLite handles the File-Lockings as a physical Locking during the 
writings to File and Disk. (Is it so?) I do my own logical Locking 
during the Edit of a Record and then reflect to the Busy-State if the 
changes will be saved to disk. 
The different point of views are intended from a logical Lock during 
Edit and a physical Lock during storing the Data to Disk. A logical 
Locking can hold it for a long time, several minutes, to a hour ... so 
long the user need to hold this Record for Editing. But there is during

editing no affect to DB. This will done very well in my Logical-Locking-
Concept. The physical File-Locking needs only milliseconds, even to write 
changes to Disk, if DB Ready and not Busy. 

Now then...if the User-Edit endet, the changes will be saved to File 
and written to Disk, and that is controlled bei File- Locking through
SQLite, so that multiple Diskwritings to one DB do not end in a 
collision.


Is that accurately described?

  

Anne,

Yes, your description is correct. SQLite does what you are calling the 
physical locking, and your application will have to do the logical 
locking itself. SQLite will help with the logical locking by providing 
physical locking that will prevent two users from updating the database 
to acquire the same logical lock. SQLite's physical locking will ensure 
that only one application instance can write to the database to either 
acquire or release a logical lock. As long as all your applications use 
and respect your logical locks, then this scheme should work well.


HTH
Dennis Cote

Re: Re: [sqlite] Multiple Users

[Anne . Kirchhellen]

 Original-Nachricht 
Datum: Wed, 5 Jul 2006 13:19:30 +0200
Von: "Olaf Beckman Lapré" <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

Hi Olaf

> Why not use optimistic locking (timestamp based pseudo locking)? It's
> ussually sufficient.

Thats my way in OLEDB. I store the Users ID, the Workstation-ID, 
the Current-Process-ID, a initial Timestamp and a Heartbeat-Timestamp 
to see, that this Record-Lock is alive. And last, I store the ID of 
the Record to be locked and his appended Records in other Tables. 
That works very fine and prevent logical conflicts on the Level 
"Data-Objects". 

But this do not avoid a physical write-conflict to Disk if multiple 
Users write there changes to DB at the same moment. SQLite dont support 
optimistic locking. It seems to me, with SQLite I can do my Pseudo-
Locking as a Concept too, similarly to OLEDB, but in reflection to 
the DB's Busy-State. 

*hmmm* But if I think a time about it... isn't it so, that 
SQLite allways perform a optimistic locking? Mikkis Posting makes
me meditative ... it seems to me, it contains all the needed answers 
to me.

Bye, Anne

-- 


Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

Re: [sqlite] Querying a value with an ampersand

[Jay Sprenkle]

On 7/5/06, Roger <[EMAIL PROTECTED]> wrote:

I have a company name as follows:

Chemistry & chemicals

I have plenty of those in my database which come with ampersands, now
when i do a query i get nothing.

How best can i write the query using a string with an ampersand as part
of it.


select * from mytable
where company = 'Chemistry & chemicals'
does not work?

Re: [sqlite] Multiple Users

[Olaf Beckman Lapré]

Why not use optimistic locking (timestamp based pseudo locking)? It's
ussually sufficient.

Olaf
- Original Message - 
From: "Nikki Locke" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, July 05, 2006 1:05 PM
Subject: Re: [sqlite] Multiple Users


> > In order to this, the next question ;-) Is a physcial Locking to the
> > DB allways necessary, if more the one User (writer) connect to a DB
> > and a Table? That means, is it insufficient, if I handle only a
> > logical Locking in the Application instead of physcial Locking?
>
> As I understand it (mostly from reading this list, and a few side trips to
the
> documentation), the way SQLite works during a database update is that the
> entire database file is locked, and remains locked until the update is
> complete. If you use transactions, the entire database file is locked for
the
> entire transaction (*).
>
> Now, if there are multiple processes trying to update the same database
file,
> only one can update it at a given moment. Attempts to do updates, or start
a
> transaction (*) will fail with an error indicating the database is locked.
> This applies even if the two processes are updating different tables.
>
> Provided you code handles the errors (by retrying the update until it
> succeeds), it should work fine. However, if there are lots of processes
doing
> large updates to the same database file, they may be waiting for each
other a
> lot.
>
> Contrast this with a full database server (like SQL server, MySql, etc.),
> which has much finer grained locking, and can let two processes update
> different tables, or even different rows on the same table, at the same
time.
>
> (*) Well, depending on the type of the transaction, it may not be locked
when
> you BEGIN the transaction, it may wait until the first attempt to update
the
> database.
>
> -- 
> Nikki Locke, Trumphurst Ltd.  PC & Unix consultancy & programming
> http://www.trumphurst.com/
>
>
>

Re: [sqlite] Multiple Users

[Nikki Locke]

> In order to this, the next question ;-) Is a physcial Locking to the 
> DB allways necessary, if more the one User (writer) connect to a DB 
> and a Table? That means, is it insufficient, if I handle only a 
> logical Locking in the Application instead of physcial Locking? 

As I understand it (mostly from reading this list, and a few side trips to the 
documentation), the way SQLite works during a database update is that the 
entire database file is locked, and remains locked until the update is 
complete. If you use transactions, the entire database file is locked for the 
entire transaction (*).

Now, if there are multiple processes trying to update the same database file, 
only one can update it at a given moment. Attempts to do updates, or start a 
transaction (*) will fail with an error indicating the database is locked. 
This applies even if the two processes are updating different tables.

Provided you code handles the errors (by retrying the update until it 
succeeds), it should work fine. However, if there are lots of processes doing 
large updates to the same database file, they may be waiting for each other a 
lot.

Contrast this with a full database server (like SQL server, MySql, etc.), 
which has much finer grained locking, and can let two processes update 
different tables, or even different rows on the same table, at the same time.

(*) Well, depending on the type of the transaction, it may not be locked when 
you BEGIN the transaction, it may wait until the first attempt to update the 
database.

-- 
Nikki Locke, Trumphurst Ltd.  PC & Unix consultancy & programming
http://www.trumphurst.com/

Re: [sqlite] Syntax Errors with various strings?

[Christian Nassau]

Have you tried using the quote() function?

>From http://www.sqlite.org/lang_expr.html

quote(X)This routine returns a string which is the value of its
argument suitable for inclusion into another SQL statement. Strings are
surrounded by single-quotes with escapes on interior quotes as needed.
BLOBs are encoded as hexadecimal literals. The current implementation of
VACUUM uses this function. The function is also useful when writing
triggers to implement undo/redo functionality.


Gussimulator wrote:
> Hi, When I have strings with "-", "!",  "\" or similar characters I get a 
> syntax error.. Now, I thought this would happen with a few of this chars so I 
> made 2 routines in my program, one that converts each of this chars into a 
> flag string, which then, by the other routine can be reverted to the original 
> characters to obtain the string in its original form, so I can later work 
> with it by my side.
> 
> Now, I've found myself with syntax errors even on strings that didnt had any 
> strange characters, So.. I'm wondering, what can I do to prevent this? Does 
> SQLite provide a "format" routine or is there anything I can do to prevent 
> the syntax errors... like this ones? (since my data wont get into the db if 
> theres an error, of course, thats why Im concerned).
> 
> I'm really worried about this, hence I subscribed on the list (first 
> message!).  I hope someone can help me out on this one, thanks.
> 
> And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find myself 
> with this type of problem.
> 
> 


-- 
---
Christian Nassau
Software Developer
---
Swissrisk
Holzhausenstrasse 44,
60322 Frankfurt, Germany

tele: +49 69 50952-266
fax:  +49 69 50952-299

www.swissrisk.com
---

Re: [sqlite] Syntax Errors with various strings? & ...Ampersand

[C.Peachment]

The solution I have adopted to both of these issues is to:

1. ensure all strings are enclosed by matching quotation marks, and

2. use the question mark substitution form of prepared statement
with subsequent bind of parameters. This can only be done from a
programming language and not the command line interface.


On Wed, 05 Jul 2006 10:44:31 +0200, Roger wrote:

>I have a company name as follows:

>Chemistry & chemicals

>I have plenty of those in my database which come with ampersands, now
>when i do a query i get nothing.

>How best can i write the query using a string with an ampersand as part
>of it.

>I am developing in a PHP/SQlite environment.


On Wed, 5 Jul 2006 06:07:46 -0300, Gussimulator wrote:

>Hi, When I have strings with "-", "!",  "\" or similar characters I get a 
>syntax error.. Now, I thought this would happen with a few of this chars so I 
made 2 routines in my program, one that converts each of this chars into a flag 
string, which then, by the other routine can be reverted to the 
original characters to obtain the string in its original form, so I can later 
work with it by my side.

>Now, I've found myself with syntax errors even on strings that didnt had any 
>strange characters, So.. I'm wondering, what can I do to prevent this? 
Does SQLite provide a "format" routine or is there anything I can do to prevent 
the syntax errors... like this ones? (since my data wont get into the 
db if theres an error, of course, thats why Im concerned).

>I'm really worried about this, hence I subscribed on the list (first 
>message!).  I hope someone can help me out on this one, thanks.

>And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find myself 
>with this type of problem.

[sqlite] Syntax Errors with various strings?

[Gussimulator]

Hi, When I have strings with "-", "!",  "\" or similar characters I get a 
syntax error.. Now, I thought this would happen with a few of this chars so I 
made 2 routines in my program, one that converts each of this chars into a flag 
string, which then, by the other routine can be reverted to the original 
characters to obtain the string in its original form, so I can later work with 
it by my side.

Now, I've found myself with syntax errors even on strings that didnt had any 
strange characters, So.. I'm wondering, what can I do to prevent this? Does 
SQLite provide a "format" routine or is there anything I can do to prevent the 
syntax errors... like this ones? (since my data wont get into the db if theres 
an error, of course, thats why Im concerned).

I'm really worried about this, hence I subscribed on the list (first message!). 
 I hope someone can help me out on this one, thanks.

And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find myself 
with this type of problem.

[sqlite] Querying a value with an ampersand

[Roger]

I have a company name as follows:

Chemistry & chemicals

I have plenty of those in my database which come with ampersands, now
when i do a query i get nothing.

How best can i write the query using a string with an ampersand as part
of it.

I am developing in a PHP/SQlite environment.

Re: Re: [sqlite] Multiple Users

[Anne . Kirchhellen]

 Original-Nachricht 
Datum: Tue, 04 Jul 2006 19:13:56 -0500
Von: John Stanton <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Multiple Users

Hi John

I think, I understand what you will to say 

> Sqlite is a single file shared between users just like a word processing 
> file shared between multiple users.  It is not a DBMS server and just 
> uses the regular fcntl type file locks.

but... I want try to ask again...  ;-)

SQLite handles the File-Lockings as a physical Locking during the 
writings to File and Disk. (Is it so?) I do my own logical Locking 
during the Edit of a Record and then reflect to the Busy-State if the 
changes will be saved to disk. 
The different point of views are intended from a logical Lock during 
Edit and a physical Lock during storing the Data to Disk. A logical 
Locking can hold it for a long time, several minutes, to a hour ... so 
long the user need to hold this Record for Editing. But there is during
editing no affect to DB. This will done very well in my Logical-Locking-
Concept. The physical File-Locking needs only milliseconds, even to write 
changes to Disk, if DB Ready and not Busy. 

Now then...if the User-Edit endet, the changes will be saved to File 
and written to Disk, and that is controlled bei File- Locking through
SQLite, so that multiple Diskwritings to one DB do not end in a 
collision.

Is that accurately described?

Greeting from Germany
Anne


-- 


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail