Re: [sqlite] updating records problem
I believe you are wrong. The rowid is the primary key in the SQLite B-tree representation. It is always unique and allows you not to use your own primary key. But if you have a column of type INTEGER PRIMARY KEY, the values in this column become rowid. Otherwise a new autoincremented column is created. Jakub Jay Sprenkle wrote: rowid won't work in a multiuser database. It's just the row number within the result set you retrieved. This sort of problem is the reason why people use a unique id field. The database automatically assigns you a number that won't change and isn't shown to the user (they can't change it either). On 5/17/05, Will Leshner <[EMAIL PROTECTED]> wrote: The problem is that the 'name' and 'age' fields are not sufficiently unique to identify the very row the user wanted to update. Instead every row that has matching names and ages are going to be updated.
Re: [sqlite] updating records problem
On May 17, 2005, at 8:11 PM, Ken & Deb Allen wrote: By ensuring that each record receives a unique identifier and including that in all queries, the main issue is resolved. Right, but the problem is that the user didn't use the unique identifier in the query and now I'm faced with the prospect of trying to muck with the user's query to add the unique identifier myself. I was trying to find out if maybe there was some other way that I hadn't considered.
Re: [sqlite] updating records problem
All database tables should be defined with a unique primary key.
Ideally this should consist of one or more integer columns; tect
columns can be used but they are universally less efficient. If the
data being stored does not contain a natural unique identifier, then
one should be added; you can simply name the column "CustomerID" or
"PKey" and set it to be an integer. By ensuring that each record
receives a unique identifier and including that in all queries, the
main issue is resolved.
Of course one problem remains, and that is detecting whether some other
user has changed the record since you saved it. To achieve this, you
define another integer column and store another value in that field,
but this value is updated each time the record is saved. This can be a
simple sequential value that rolls around to zero (or one) again after
reaching some maximum value. The name of the field should reflect its
purpose, so you could name it something like "EditVersion" or
"ChangeFlag". The client does not need to access this field, or the
primary key field, but they can be used to ensure that the record being
updated matches the current record (you could read the current record
first, or use the fields to validate the update).
For example, to continue your example, the SELECT statement would be
modified to include the "CustomerID" and "ChangeFlag" columns, and the
RecordSet.Update() method would be modified to issue a SQL statement
like "UPDATE Customers SET Name = xxx, Age = yyy WHERE CustomerID =
AND ChangeFlag = fff", and the code would check to ensure that
exactly one record was modified.
-ken
On 17-May-05, at 4:46 PM, Will Leshner wrote:
I develop a database wrapper for SQLite and I have an interesting
problem that I'm curious how other people solve. Basically, the users
of my wrapper have the option of editing database records indirectly
through the wrapper, rather than directly, using UPDATE. The database
wrapper is a set of classes and one of those classes is a RecordSet.
So, when the user asks for a RecordSet, with the intention of editing
one or more records, she might do this (where rs is a RecordSet):
rs = db.SQLSelect("SELECT name, age FROM customers")
Now, the user can edit a record in the RecordSet like this:
rs.Edit
rs.Field("name") = "Frank"
rs.Field("age") = 10
rs.Update
What the wrapper does, when it sees the Update, is create SQL and feed
it to SQLite:
UPDATE cusomers SET name='Frank', age=10 WHERE name=
AND age=;
The problem is that the 'name' and 'age' fields are not sufficiently
unique to identify the very row the user wanted to update. Instead
every row that has matching names and ages are going to be updated.
To solve this problem, I've been telling users to explicitly add
'rowid' as one of their columns when they issue SQL to select records
to edit:
rs = db.SQLSelect("SELECT rowid, name, age FROM customers")
This works ok, but I'd really like to get rid of this limitation. I've
considered ways of possibly inserting 'rowid' manually to the user's
SQL, but the idea of modifying the user's SQL kind of leaves a bad
taste in my mouth.
I know that PHP also uses SQLite and I'm wondering if it has the same
problem and how it may have solved it.
Thanks for any help.
RE: [sqlite] Sqlite3explorer can't open my DB
ok, fine. what baffled me in the first place was the error message: > 4:malformed database schema - near "to": syntax error this is an sqlite error, not an sqlite3Explorer error, so I wonder whether at some point even sqlite would gag at this field type definition...
[sqlite] autoCommit
I see there is an autoCommit flag in the sqlite3 structure. That is, of course, an opaque structure and technically I shouldn't be looking at it. But I wonder how evil it would be to expose that flag so that I can use it to detect whether or not SQLite is currently in a transaction. Thanks.
Re: [sqlite] Relationship between 2 tables
I use triggers to do this. Quoting Jay Sprenkle <[EMAIL PROTECTED]>: > On 5/17/05, Svetlik Slavomir <[EMAIL PROTECTED]> wrote: > > Hi, I am new in this forum, but not in SQLite3. I successfully used > > SQLite3.dll with Rapid-Q (very good programming language - clone of > > Q-Basic), basic commands like CREATE, INSERT, SELECT, UPDATE etc. works > > fine, thanks! > > My newbie question: If I create two tables (parent and child), I know > > set relationship between this tables only by temporarely commands SELECT > > and JOIN (when I read data) - this way I used to this time. But - is > > there any other solution (inside SQLite), how to SET this relationship > > PERMANENT and than it works automatically (for example by deleting > > parent record, where are deleted child records too (like in MS Access)) > > or in future GET this relationship for better understanding my or > > strange tables? > > Sorry, there's no referential integrity enforcement in Sqlite either. >
Re: [sqlite] updating records problem
On May 17, 2005, at 2:07 PM, Jay Sprenkle wrote: rowid won't work in a multiuser database. It's just the row number within the result set you retrieved. This sort of problem is the reason why people use a unique id field. The database automatically assigns you a number that won't change and isn't shown to the user (they can't change it either). Good point. Right now I'm just trying to solve the single-user part of the problem. I'd like to users not to have to worry about unique ids and such when they go through the wrapper to edit database records.
RE: [sqlite] Sqlite3explorer can't open my DB
Mr. Cariotoglou, I had two tables with unusual schemas. These schemas were acceptable to SQLite but I perhaps I should not have expected Sqlite3Explorer to allow them. The schemas look like the following example: CREATE TABLE dataset_version ( version_id smallint not null , name varchar(32) not null , version_date datetime year to the second not null , ship varchar(9), lcm varchar(5), creator varchar(32) ); The data type for version date was intended as a comment. I changed it to read: version_date datetime not null , and my problem went away. BTW, I really like Sqlite3Explorer. Thank you for a very good product. Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell) -Original Message- From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 17, 2005 4:56 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Sqlite3explorer can't open my DB Could you please let me know what the problem was ? I am the author of sqlite3Explorer, and perhaps I could fix it. > -Original Message- > From: Downey, Shawn [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 17, 2005 11:45 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Sqlite3explorer can't open my DB > > Thanks anyway. I solved this myself. > > Sqlite3Explorer is more particular about the table schema > syntax than Sqlite. > > Shawn M. Downey > MPR Associates > 632 Plank Road, Suite 110 > Clifton Park, NY 12065 > 518-371-3983 x3 (work) > 860-508-5015 (cell) > > > -Original Message- > From: Downey, Shawn > Sent: Tuesday, May 17, 2005 12:28 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Sqlite3explorer can't open my DB > > Version 1.4 of Sqlite3explorer could not open my database. > The error message is: > > > > 4:malformed database schema - near "to": syntax error > > > > An older version of Sqlite3explorer worked fine on this > database. I am using sqlite.dll version 3.2.1. Does anyone > else have problems with Sqlite3explorer? > > > > Shawn M. Downey > > MPR Associates > > 632 Plank Road, Suite 110 > > Clifton Park, NY 12065 > > 518-371-3983 x3 (work) > > 860-508-5015 (cell) > > > > > >
Re: [sqlite] updating records problem
rowid won't work in a multiuser database. It's just the row number within the result set you retrieved. This sort of problem is the reason why people use a unique id field. The database automatically assigns you a number that won't change and isn't shown to the user (they can't change it either). On 5/17/05, Will Leshner <[EMAIL PROTECTED]> wrote: > The problem is that the 'name' and 'age' fields are not sufficiently > unique to identify the very row the user wanted to update. Instead > every row that has matching names and ages are going to be updated. >
RE: [sqlite] Sqlite3explorer can't open my DB
Could you please let me know what the problem was ? I am the author of sqlite3Explorer, and perhaps I could fix it. > -Original Message- > From: Downey, Shawn [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 17, 2005 11:45 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Sqlite3explorer can't open my DB > > Thanks anyway. I solved this myself. > > Sqlite3Explorer is more particular about the table schema > syntax than Sqlite. > > Shawn M. Downey > MPR Associates > 632 Plank Road, Suite 110 > Clifton Park, NY 12065 > 518-371-3983 x3 (work) > 860-508-5015 (cell) > > > -Original Message- > From: Downey, Shawn > Sent: Tuesday, May 17, 2005 12:28 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Sqlite3explorer can't open my DB > > Version 1.4 of Sqlite3explorer could not open my database. > The error message is: > > > > 4:malformed database schema - near "to": syntax error > > > > An older version of Sqlite3explorer worked fine on this > database. I am using sqlite.dll version 3.2.1. Does anyone > else have problems with Sqlite3explorer? > > > > Shawn M. Downey > > MPR Associates > > 632 Plank Road, Suite 110 > > Clifton Park, NY 12065 > > 518-371-3983 x3 (work) > > 860-508-5015 (cell) > > > > > >
[sqlite] updating records problem
I develop a database wrapper for SQLite and I have an interesting
problem that I'm curious how other people solve. Basically, the users
of my wrapper have the option of editing database records indirectly
through the wrapper, rather than directly, using UPDATE. The database
wrapper is a set of classes and one of those classes is a RecordSet.
So, when the user asks for a RecordSet, with the intention of editing
one or more records, she might do this (where rs is a RecordSet):
rs = db.SQLSelect("SELECT name, age FROM customers")
Now, the user can edit a record in the RecordSet like this:
rs.Edit
rs.Field("name") = "Frank"
rs.Field("age") = 10
rs.Update
What the wrapper does, when it sees the Update, is create SQL and
feed it to SQLite:
UPDATE cusomers SET name='Frank', age=10 WHERE name=
AND age=;
The problem is that the 'name' and 'age' fields are not sufficiently
unique to identify the very row the user wanted to update. Instead
every row that has matching names and ages are going to be updated.
To solve this problem, I've been telling users to explicitly add
'rowid' as one of their columns when they issue SQL to select records
to edit:
rs = db.SQLSelect("SELECT rowid, name, age FROM customers")
This works ok, but I'd really like to get rid of this limitation.
I've considered ways of possibly inserting 'rowid' manually to the
user's SQL, but the idea of modifying the user's SQL kind of leaves a
bad taste in my mouth.
I know that PHP also uses SQLite and I'm wondering if it has the same
problem and how it may have solved it.
Thanks for any help.
RE: [sqlite] Sqlite3explorer can't open my DB
Thanks anyway. I solved this myself. Sqlite3Explorer is more particular about the table schema syntax than Sqlite. Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell) -Original Message- From: Downey, Shawn Sent: Tuesday, May 17, 2005 12:28 PM To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite3explorer can't open my DB Version 1.4 of Sqlite3explorer could not open my database. The error message is: 4:malformed database schema - near "to": syntax error An older version of Sqlite3explorer worked fine on this database. I am using sqlite.dll version 3.2.1. Does anyone else have problems with Sqlite3explorer? Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell)
RE: [sqlite] Can I refer to a column alias in same SQL Select statement?
I think that you can put the aggregates directly into the SELECT clause rather than referring to them by alias, i.e. select city, sum(Weight)/count(id) as AvgWeight -Tom > -Original Message- > From: de f [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 17, 2005 1:55 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Can I refer to a column alias in same SQL > Select statement? > > Is there any way to do the following without using subqueries or > repeating the formulas? > > select city, count(id) as TotalNum, sum(Weight) as TotalWeight, > TotalNum/TotalWeight as AvgWeight... > > > Get your own "800" number > Voicemail, fax, email, and a lot more > http://www.ureach.com/reg/tag >
[sqlite] Can I refer to a column alias in same SQL Select statement?
Is there any way to do the following without using subqueries or repeating the formulas? select city, count(id) as TotalNum, sum(Weight) as TotalWeight, TotalNum/TotalWeight as AvgWeight... Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag
RE: [sqlite] all of tables of a DB
Thanks for help --- "Downey, Shawn" <[EMAIL PROTECTED]> a écrit: > > SELECT name FROM sqlite_master WHERE type = 'table'; > > Shawn M. Downey > MPR Associates > 632 Plank Road, Suite 110 > Clifton Park, NY 12065 > 518-371-3983 x3 (work) > 860-508-5015 (cell) > > > -Original Message- > From: majed chatti [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 17, 2005 8:00 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] all of tables of a DB > > Houw can I get all of tables of a data base > > I think its same think like > > >select * from sysobjects; > > but it dose not work > > > > > > > _ > > Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de > stockage pour vos mails, photos et vidéos ! > Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com > _ Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de stockage pour vos mails, photos et vidéos ! Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com
[sqlite] Sqlite3explorer can't open my DB
Version 1.4 of Sqlite3explorer could not open my database. The error message is: 4:malformed database schema - near "to": syntax error An older version of Sqlite3explorer worked fine on this database. I am using sqlite.dll version 3.2.1. Does anyone else have problems with Sqlite3explorer? Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell)
Re: [sqlite] Database locked after crash
On Tue, 17 May 2005, Jaap Krabbendam wrote:
I have been simulating a crash during a transaction. After BEGIN, at some
point I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a
crash.
After that, I can see that a -journal file is present. If I restart my
executable, it seems that the changes of the transaction are made undone
(which is as expected). The journal file however is not removed.
Furthermore, if I try to do the same operation again (BEGIN + some changes),
I get an SQL_BUSY error code on the first record change (UPDATE/SET).
run fuser on the db and see who has it open. this cannot happen unless:
- some other process holds the lock (eg. you are using fastcgi or mod_ruby
and some other process is locking the db)
- your db in on nfs and you setup is fubar. btw. i've never seen a
__correct__ nfs setup. when incorrect locks can get hung on the server
side.
- there is a kernel bug.
I have the feeling that the OS still has a lock on the database. Any ideas on
how to prevent this or on how to recover from this situation?
again - unless there is a kernel bug (which i doubt as we are using sqlite
on
many of our linux systems running 100,000's of transactions, even on nfs, with
zero issues in 3 years) the most likely explaination is that another process
does, in fact, hold the lock.
I am using the following setup:
-sqlite-3.2.1
-linux/i686/2.6.9-1.667smp
-application using posix threads. Only one thread is accessing the database.
threads and fcntl based locks do not work as you might expect. your process
will go into uninterruptable sleep on the call to fcntl if it blocks and this
stops all threads. what do you mean 'only one posix thread' since ruby
threads are green and not posix?? you mean you have another application using
posixthreads in addition to your rails app? if so that's certainly the
process holding the lock.
if your db is not on nfs this code will show you how to tell which process
holds the lock:
jib:~ > cat a.rb
#
# http://raa.ruby-lang.org/project/posixlock/
# http://www.codeforpeople.com/lib/ruby/posixlock/
#
require 'posixlock'
path = ARGV.shift || __FILE__
File::chmod 0700, path
f = open path, 'r+'
if fork
ret = f.lockf File::F_LOCK, 0
pid = Process::pid
puts "parent <#{ pid }> holds lock on <#{ f.path }>"
sleep 2
else
sleep 1
ret = f.lockf File::F_TEST, 0
ppid = ret
pid = Process::pid
puts "child <#{ pid }> cannot lock <#{ f.path }> because pid <#{ ppid }> holds
lock"
exit
end
jib:~ > ruby a.rb
parent <23833> holds lock on
child <23834> cannot lock because pid <23833> holds lock
so a simple script like
require 'posixlock'
path = open ARGV.shift 'r+'
ret = f.lockf File::F_TEST, 0
unless ret.zero?
puts "process <#{ ret }> holds lock on <#{ path }>"
else
puts "lock on <#{ path }> available "
end
man fcntl will explain all this further.
kind regards.
-a
--
===
| email :: ara [dot] t [dot] howard [at] noaa [dot] gov
| phone :: 303.497.6469
| renunciation is not getting rid of the things of this world, but accepting
| that they pass away. --aitken roshi
===
[sqlite] add stdev() and rr() [sqlite-3.2.1]
This is$B!!(BYutaka nakamura in Japan.
(B
(Bdiff file sqlite-3.2.1's func.c.
(B
(Badd stdev() and rr() .
(B
(B. stdev() mean stddev lile MS Excel.
(B
(B. rr() mean RiskReturn( mean 1/CV =$B&L(B/$B&R(B)
(B
(Bpahaps RiskReturn calculate speed fastest SQL in the world.
(B
(Bbecouse loop time about harf and rr() direct calcrate SQL is noting.
(B
(BBye!
(B
(B
(B854,878d853
(B< static void stdevStep(sqlite3_context *context, int argc, sqlite3_value
(B**argv){
(B< StdDevCtx *p;
(B< if( argc<1 ) return;
(B< p = sqlite3_aggregate_context(context, sizeof(*p));
(B< if( p && SQLITE_NULL!=sqlite3_value_type(argv[0]) ){
(B< p->sum += sqlite3_value_double(argv[0]);
(B< p->sum2 += pow(sqlite3_value_double(argv[0]),2);
(B< p->cnt++;
(B< }
(B< }
(B< static void stdevFinalize(sqlite3_context *context){
(B< StdDevCtx *p;
(B< p = sqlite3_aggregate_context(context, sizeof(*p));
(B< if( p && p->cnt>0 ){
(B< sqlite3_result_double(context,sqrt(((double)p->cnt*p->sum2 -
(Bpow(p->sum,2))/((double)p->cnt*((double)p->cnt -1.0;
(B< }
(B< }
(B< static void rrFinalize(sqlite3_context *context){
(B< StdDevCtx *p;
(B< p = sqlite3_aggregate_context(context, sizeof(*p));
(B< if( p && p->cnt>0 ){
(B<
(Bsqlite3_result_double(context,(p->sum/(double)p->cnt)/sqrt(((double)p->cnt*p
(B->sum2 - pow(p->sum,2))/((double)p->cnt*((double)p->cnt -1.0;
(B< }
(B< }
(B<
(B1022,1023d996
(B< { "stdev", 1, 0, 0, stdevStep,stdevFinalize },
(B< { "rr", 1, 0, 0, stdevStep, rrFinalize},
Re: [sqlite] Database locked after crash
I do have some code here. It is clear that the problem is related to using
threads. I did not see the problem having just the main program. Note that it
is my objective to test a crash, hence the exit(0) from my_thread. The same
phenomenon is seen when replacing exit(0) by while(1) sleep(100) and then
hitting ^C
If you start the program with an argument, a new mydb file will be created
and the program will issue an exit(0). If you then start the program again
without any arguments,
my_thread will end up in while(1) sleep(100). You can also test with
sqlite3 client to see that the db is locked.
Jaap Krabbendam.
>Can you post code?
>
>--- Jaap Krabbendam <[EMAIL PROTECTED]> wrote:
>
>>
>> Hi,
>>
>> I have been simulating a crash during a transaction. After BEGIN, at some
point
>> I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash.
>>
>> After that, I can see that a -journal file is present. If I restart my
>> executable, it seems that the changes of the transaction are made undone
>> (which is as expected). The journal file however is not removed.
>> Furthermore, if I try to do the same operation again (BEGIN + some changes),
>> I get an SQL_BUSY error code on the first record change (UPDATE/SET).
>>
>> I have the feeling that the OS still has a lock on the database. Any ideas
>> on
>> how to prevent this or on how to recover from this situation?
>>
>> I am using the following setup:
>> -sqlite-3.2.1
>> -linux/i686/2.6.9-1.667smp
>> -application using posix threads. Only one thread is accessing the database.
>>
>> Thanks,
>> J.J. Krabbendam
>>
>>
>
>
>
>
>__
>Do you Yahoo!?
>Yahoo! Small Business - Try our new resources site!
>http://smallbusiness.yahoo.com/resources/
#include
#include
#include
#include
#include
void *my_thread(void *);
char *file = "mydb";
bool create;
int main(int argc, char *argv[])
{
int ret;
create = argc > 1;
pthread_t thread;
ret = pthread_create(&thread, 0, my_thread, 0);
if (ret != 0)
perror("pthread_create");
while (1)
sleep(100);
}
void *my_thread(void *)
{
int ret = 0;
sqlite3 *dbref;
if (create)
unlink(file);
ret = sqlite3_open(file, &dbref);
if (ret != 0)
goto exit;
if (create)
{
ret = sqlite3_exec(dbref, "CREATE TABLE tab (col1 INTEGER)", 0,0,0);
if (ret != 0)
goto exit;
ret = sqlite3_exec(dbref, "INSERT INTO tab (col1) VALUES (3)", 0,0,0);
if (ret != 0)
goto exit;
}
ret = sqlite3_exec(dbref, "BEGIN", 0,0,0);
if (ret != 0)
goto exit;
ret = sqlite3_exec(dbref, "UPDATE tab SET col1=6", 0,0,0);
if (ret != 0)
goto exit;
printf("Return value1: %d\n",ret);
while (1)
sleep(100);
exit(0);
exit:
printf("Return value2: %d\n",ret);
sqlite3_close(dbref);
while (1)
sleep(100);
}
Re: [sqlite] Porting help please :-)
Thanks for responding Gé There are the usual options of buffered and unbuffered file i/o in AmigaOS. A given file can be opened either in a shared mode, or an exclusive mode by a process, but it can also be locked first and then opened. It cannot be locked individually for a given pthread running in that process. __For coarse grain locking there is Lock( filename, mode )__ Lock( ) allows you to lock a given file by name, and you get a lock descriptor. That lock descriptor can either be SHARED_LOCK ( ACCESS_READ ) or EXCLUSIVE_LOCK ( ACCESS_WRITE ), indicating more of the intention of the descriptor rather than causing any limitation on the outcome of a Read or Write operation on any resulting file descriptor. In any case the lock descriptor has to be held while its in use. You can then access the file descriptor from that lock descriptor safe in the knowledge that it is yours. The problem being that it locks the whole file, rather than individual records/ranges inside it. This is how I currently implement it to prevent two different processes both running on the same database from overwriting each other. It works to a fashion, but its not sophisticated like I think it should be - and reading your reply, it could be. __For fine grain lock there is LockRecord( ... ) and LockRecords( ... )__ LockRecord( ... ) allows you to lock a given range to yourself ( which I didn't actually realise I had as an option to be honest until I went back to the developer documentation for the OS just now ) in either EXCLUSIVE or SHARED mode. LockRecords( ... ) allows you to lock multiple ranges to yourself at once ( kind of like a lockset ). Both of those have optional timeouts, and IMMED suboptions to either wait for a lock or return straight away if its already locked. Now Im not totally sure whether or not the lock record option treats individual threads as different potential lock holders, thats something Ill try in a minute ( and it isn't clear from the documentation either ). But it certainly locks it system wide. So following your reply, it indicates that I should follow the Win32 implementation pattern? Then if I can confirm if threads are treated as individual lockholders or not I can use the AmigaOS pthread.library to multi-thread it. If not, it stays single threaded but at least potentially atomic system wide - which should be enough to make it a decent enough port. Dave.
Re: [sqlite] Porting help please :-)
David Pitcher wrote: > The claim that it works over a networked drive has made me > start to think that operating system specific locking mechanisms are totally > the wrong way to go, and that I have misunderstood the code. OS locking mechanisms are supposed to work over a network. >[...] or should I just sit down with a pen, ignore the calls to the Windows API in > the win32 one and try and work it out from the unix implementation? The NT branch of the Win32 implementation does the same thing as the Unix one. It does not have to work around the silly Posix lock semantics so it's simpler. I'd start there. Gé
Re: [sqlite] Porting help please :-)
Dave, The os_unix.c locking code is extremely convoluted because of the rather insane Posix locking semantics, which tend to make writing threaded code excessively complicated, e.g. if two threads are accessing the same file and one thread closes its file handle the other thread loses all its locks. I don't know anything about AmigaOS, but if two threads can open the same file using two file handles, and can independently lock and unlock ranges in the file then I'd take a closer look at os_win.c to determine the logic. The semantics are not all that hard. For Windows there are two case: o Pre-NT locks: you can only have write locks o NT and later: you can have read and write locks Sqlite implements a randomized method of simulating read locks for Pre-NT operating systems (Win95/Win98/WinME). It's explained well in os_win.c An explanation on how AmigaOS locking works would be useful. Gé David Pitcher wrote: > there > > Ive just recently ported ( in the nastiest dirtiest way so far ) sqlite onto > another platform - that is AmigaOS version 4.0 ( > powerpc > ):http://www.os4depot.net/index.php?function=showfile&file=library/misc/sqlite3.tar.gz > > However I had to put a health warning on it because in order to get it to > work in the limited spare time I have had so far I had to remove all the > locking code in os_amiga.c ( ripped straight from the os_unix.c file with a > few changes to stop a few clashes in naming of #defined' variables with OS > provided ones ) else any operation would just fail saying the database was > locked ( which yes probably is something wrong in the C runtime library > emulation ). > > So in order to restore sane locking I wrote an AmigaOS specific version of > the os_amiga.c file, but using the Operating System locks built in. Now > looking at the windows implementation it does seem to call operating system > methods for locking files exclusive or shared, but specifically on a section > of the file. The claim that it works over a networked drive has made me > start to think that operating system specific locking mechanisms are totally > the wrong way to go, and that I have misunderstood the code. > > Excuse me if this sounds terribly 'newbie'. > > So, a question, should I NOT be using operating system specific locks ( > indeed my attempts to get it to work once I used OS functions to lock the > file failed to work in a sqlite friendly way ) and instead be just setting > various offsets in the file to certain bitpatterns to indicate to potential > sharers what the state is? My implementation is the only one that attempts > to store a lock descriptor in struct OsFile also. > > I have the feeling Ive missed a porting document or something. Anybody can > set me straight as to whether or not I should be modifying byte offsets in > the file, using OS provided locking systems ( and their semantics ) or > should I just sit down with a pen, ignore the calls to the Windows API in > the win32 one and try and work it out from the unix implementation? > > Regards > > Dave. >
Re: [sqlite] Database locked after crash
I'll try to get the essentials in a short program (and hope that it still reproduces the problem). I'll get back on this later. Jaap Krabbendam > >Can you post code? > >--- Jaap Krabbendam <[EMAIL PROTECTED]> wrote: > >> >> Hi, >> >> I have been simulating a crash during a transaction. After BEGIN, at some point >> I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash. >> >> After that, I can see that a -journal file is present. If I restart my >> executable, it seems that the changes of the transaction are made undone >> (which is as expected). The journal file however is not removed. >> Furthermore, if I try to do the same operation again (BEGIN + some changes), >> I get an SQL_BUSY error code on the first record change (UPDATE/SET). >> >> I have the feeling that the OS still has a lock on the database. Any ideas >> on >> how to prevent this or on how to recover from this situation? >> >> I am using the following setup: >> -sqlite-3.2.1 >> -linux/i686/2.6.9-1.667smp >> -application using posix threads. Only one thread is accessing the database. >> >> Thanks, >> J.J. Krabbendam >> >> > > > > >__ >Do you Yahoo!? >Yahoo! Small Business - Try our new resources site! >http://smallbusiness.yahoo.com/resources/
Re: [sqlite] Database locked after crash
Can you post code? --- Jaap Krabbendam <[EMAIL PROTECTED]> wrote: > > Hi, > > I have been simulating a crash during a transaction. After BEGIN, at some > point > I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash. > > After that, I can see that a -journal file is present. If I restart my > executable, it seems that the changes of the transaction are made undone > (which is as expected). The journal file however is not removed. > Furthermore, if I try to do the same operation again (BEGIN + some changes), > I get an SQL_BUSY error code on the first record change (UPDATE/SET). > > I have the feeling that the OS still has a lock on the database. Any ideas on > how to prevent this or on how to recover from this situation? > > I am using the following setup: > -sqlite-3.2.1 > -linux/i686/2.6.9-1.667smp > -application using posix threads. Only one thread is accessing the database. > > Thanks, > J.J. Krabbendam > > __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
Re: [sqlite] Relationship between 2 tables
On 5/17/05, Svetlik Slavomir <[EMAIL PROTECTED]> wrote: > Hi, I am new in this forum, but not in SQLite3. I successfully used > SQLite3.dll with Rapid-Q (very good programming language - clone of > Q-Basic), basic commands like CREATE, INSERT, SELECT, UPDATE etc. works > fine, thanks! > My newbie question: If I create two tables (parent and child), I know > set relationship between this tables only by temporarely commands SELECT > and JOIN (when I read data) - this way I used to this time. But - is > there any other solution (inside SQLite), how to SET this relationship > PERMANENT and than it works automatically (for example by deleting > parent record, where are deleted child records too (like in MS Access)) > or in future GET this relationship for better understanding my or > strange tables? Sorry, there's no referential integrity enforcement in Sqlite either.
[sqlite] Database locked after crash
Hi, I have been simulating a crash during a transaction. After BEGIN, at some point I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash. After that, I can see that a -journal file is present. If I restart my executable, it seems that the changes of the transaction are made undone (which is as expected). The journal file however is not removed. Furthermore, if I try to do the same operation again (BEGIN + some changes), I get an SQL_BUSY error code on the first record change (UPDATE/SET). I have the feeling that the OS still has a lock on the database. Any ideas on how to prevent this or on how to recover from this situation? I am using the following setup: -sqlite-3.2.1 -linux/i686/2.6.9-1.667smp -application using posix threads. Only one thread is accessing the database. Thanks, J.J. Krabbendam
RE: [sqlite] all of tables of a DB
SELECT name FROM sqlite_master WHERE type = 'table'; Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell) -Original Message- From: majed chatti [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 17, 2005 8:00 AM To: sqlite-users@sqlite.org Subject: [sqlite] all of tables of a DB Houw can I get all of tables of a data base I think its same think like >select * from sysobjects; but it dose not work _ Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de stockage pour vos mails, photos et vidéos ! Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com
[sqlite] all of tables of a DB
Houw can I get all of tables of a data base I think its same think like >select * from sysobjects; but it dose not work _ Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de stockage pour vos mails, photos et vidéos ! Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com
Re: [sqlite] Relationship between 2 tables
Hi, i think you are talking about foreign key constraints: these are not enforced by sqlite (http://www.sqlite.org/omitted.html). If you want the database to delete child records when the parent record is deleted, you have to write a trigger (http://www.sqlite.org/lang_createtrigger.html). Martin Svetlik Slavomir schrieb: Hi, I am new in this forum, but not in SQLite3. I successfully used SQLite3.dll with Rapid-Q (very good programming language - clone of Q-Basic), basic commands like CREATE, INSERT, SELECT, UPDATE etc. works fine, thanks! My newbie question: If I create two tables (parent and child), I know set relationship between this tables only by temporarely commands SELECT and JOIN (when I read data) - this way I used to this time. But - is there any other solution (inside SQLite), how to SET this relationship PERMANENT and than it works automatically (for example by deleting parent record, where are deleted child records too (like in MS Access)) or in future GET this relationship for better understanding my or strange tables? Thanx for answer. Slavo (Slovakia)
[sqlite] Relationship between 2 tables
Hi, I am new in this forum, but not in SQLite3. I successfully used SQLite3.dll with Rapid-Q (very good programming language - clone of Q-Basic), basic commands like CREATE, INSERT, SELECT, UPDATE etc. works fine, thanks! My newbie question: If I create two tables (parent and child), I know set relationship between this tables only by temporarely commands SELECT and JOIN (when I read data) - this way I used to this time. But - is there any other solution (inside SQLite), how to SET this relationship PERMANENT and than it works automatically (for example by deleting parent record, where are deleted child records too (like in MS Access)) or in future GET this relationship for better understanding my or strange tables? Thanx for answer. Slavo (Slovakia)
[sqlite] Porting help please :-)
there Ive just recently ported ( in the nastiest dirtiest way so far ) sqlite onto another platform - that is AmigaOS version 4.0 ( powerpc ):http://www.os4depot.net/index.php?function=showfile&file=library/misc/sqlite3.tar.gz However I had to put a health warning on it because in order to get it to work in the limited spare time I have had so far I had to remove all the locking code in os_amiga.c ( ripped straight from the os_unix.c file with a few changes to stop a few clashes in naming of #defined' variables with OS provided ones ) else any operation would just fail saying the database was locked ( which yes probably is something wrong in the C runtime library emulation ). So in order to restore sane locking I wrote an AmigaOS specific version of the os_amiga.c file, but using the Operating System locks built in. Now looking at the windows implementation it does seem to call operating system methods for locking files exclusive or shared, but specifically on a section of the file. The claim that it works over a networked drive has made me start to think that operating system specific locking mechanisms are totally the wrong way to go, and that I have misunderstood the code. Excuse me if this sounds terribly 'newbie'. So, a question, should I NOT be using operating system specific locks ( indeed my attempts to get it to work once I used OS functions to lock the file failed to work in a sqlite friendly way ) and instead be just setting various offsets in the file to certain bitpatterns to indicate to potential sharers what the state is? My implementation is the only one that attempts to store a lock descriptor in struct OsFile also. I have the feeling Ive missed a porting document or something. Anybody can set me straight as to whether or not I should be modifying byte offsets in the file, using OS provided locking systems ( and their semantics ) or should I just sit down with a pen, ignore the calls to the Windows API in the win32 one and try and work it out from the unix implementation? Regards Dave.
