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.
[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(B/$B(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
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/
[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
