Re: [sqlite] Getting the rowid after an insert
On 26 Nov 2009, at 5:54am, Edward Diener wrote: > I have a table with an integer primary key as the first type. My > understanding is that this is an alias for the rowid. When I insert a > row in this table using _sqlite3_prepare and then sqlite3_step I need to > retrieve the rowid for the row I have just inserted. Is there an SQL > statement I can use, or a way, to do that ? http://www.sqlite.org/c3ref/last_insert_rowid.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting the rowid after an insert
I have a table with an integer primary key as the first type. My understanding is that this is an alias for the rowid. When I insert a row in this table using _sqlite3_prepare and then sqlite3_step I need to retrieve the rowid for the row I have just inserted. Is there an SQL statement I can use, or a way, to do that ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *
My application includes a main process and some other processes. I open the database in other process, but at end I will close the database in main process. The problem happens while I close the database. The main process is blocked. And I could see the journal file is still there, so I guess there are still some transactions. How resolve the problem? Thanks in advance! -- View this message in context: http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-close-the-sqlite3-*-tp26523551p26523551.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: file is encrypted or is not a database
Schrum, Allan wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Dmitri Priimak >> Sent: Wednesday, November 25, 2009 11:39 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Error: file is encrypted or is not a database >> >> Simon Slavin wrote: >> >>> On 25 Nov 2009, at 6:19pm, Dmitri Priimak wrote: >>> >>> >>> Simon Slavin wrote: > On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: > > > >> 000 6166 6c69 6465 7420 206f 706f 6e65 6420 >> 010 7461 6261 7361 2065 7274 6e61 6173 7463 >> 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 >> 030 6520 636e 7972 7470 6465 6f20 2072 7369 >> 040 6e20 746f 6120 6420 7461 6261 7361 2065 >> 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 >> 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 >> >> > That is the text > > aflidet oponed tabasa ertnaastcoi n62: f li esie cnyrtpdeo rsin > >> toa d tabasa ehOm .yW eaflidet oorllabkct ar >> > You are writing text over your database file. > > > I do not remember doing it. >>> Okay, rather than you yourself, I mean something in your computer. >>> >> Whatever that text is, it's not a SQLite database file and it's not >> even a slight modification of the database file format. It's just >> something that looks very like corrupted text. The word 'cnyrtpdeo' >> looks a bit like 'crypto' and 'oponed' looks like 'opened' and I think >> it's corrupted text. >> Yes. You are right. :( >> >> -- >> Dmitri Priimak >> > > Byte swap problems. The message is: > > "failed to open database transaction 26 = file is encrypted or is not a > database Oh my. We failed to rollback tra" > Yes. I can see it now. Something is indeed writing into this file. -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: file is encrypted or is not a database
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dmitri Priimak > Sent: Wednesday, November 25, 2009 11:39 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Error: file is encrypted or is not a database > > Simon Slavin wrote: > > On 25 Nov 2009, at 6:19pm, Dmitri Priimak wrote: > > > > > >> Simon Slavin wrote: > >> > >>> On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: > >>> > >>> > 000 6166 6c69 6465 7420 206f 706f 6e65 6420 > 010 7461 6261 7361 2065 7274 6e61 6173 7463 > 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 > 030 6520 636e 7972 7470 6465 6f20 2072 7369 > 040 6e20 746f 6120 6420 7461 6261 7361 2065 > 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 > 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 > > >>> That is the text > >>> > >>> aflidet oponed tabasa ertnaastcoi n62: f li esie cnyrtpdeo rsin > toa d tabasa ehOm .yW eaflidet oorllabkct ar > >>> > >>> You are writing text over your database file. > >>> > >>> > >> I do not remember doing it. > >> > > > > Okay, rather than you yourself, I mean something in your computer. > Whatever that text is, it's not a SQLite database file and it's not > even a slight modification of the database file format. It's just > something that looks very like corrupted text. The word 'cnyrtpdeo' > looks a bit like 'crypto' and 'oponed' looks like 'opened' and I think > it's corrupted text. > Yes. You are right. :( > > -- > Dmitri Priimak Byte swap problems. The message is: "failed to open database transaction 26 = file is encrypted or is not a database Oh my. We failed to rollback tra" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] advantages of Not using shared cache mode
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nicolas Rivera wrote: > In trying to understand shared cache mode, I would like to know why one > would Not use it. It is not useful unless you open the same database multiple times concurrently within the same process. Then it only saves you time and I/O if the queries in different connections are doing substantially the same thing as they would access the same database pages. If for example the queries were accessing different tables then there would be no cache sharing - - you may as well have used two different connections. Your operating system also comes with caching functionality so the SQLite cache may not buy you that much any way. Finally shared cache mode does not behave the same with busy handlers - they are not called and you to have implement the same functionality in your own code. http://www.sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f In my documentation I specifically advise against shared cache mode: http://apsw.googlecode.com/svn/publish/tips.html#shared-cache-mode Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksNkPYACgkQmOOfHg372QT4lgCgjbflFeTOT1OqBm8iC2AdpuxL LtEAoOW9LRvNfbXOX6twCwkKsFy0Vx70 =94qg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: file is encrypted or is not a database
Simon Slavin wrote: > On 25 Nov 2009, at 6:19pm, Dmitri Priimak wrote: > > >> Simon Slavin wrote: >> >>> On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: >>> >>> 000 6166 6c69 6465 7420 206f 706f 6e65 6420 010 7461 6261 7361 2065 7274 6e61 6173 7463 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 030 6520 636e 7972 7470 6465 6f20 2072 7369 040 6e20 746f 6120 6420 7461 6261 7361 2065 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 >>> That is the text >>> >>> aflidet oponed tabasa ertnaastcoi n62: f li esie cnyrtpdeo rsin toa d >>> tabasa ehOm .yW eaflidet oorllabkct ar >>> >>> You are writing text over your database file. >>> >>> >> I do not remember doing it. >> > > Okay, rather than you yourself, I mean something in your computer. Whatever > that text is, it's not a SQLite database file and it's not even a slight > modification of the database file format. It's just something that looks > very like corrupted text. The word 'cnyrtpdeo' looks a bit like 'crypto' and > 'oponed' looks like 'opened' and I think it's corrupted text. Yes. You are right. :( -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: file is encrypted or is not a database
On 25 Nov 2009, at 6:19pm, Dmitri Priimak wrote: > Simon Slavin wrote: >> On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: >> >>> 000 6166 6c69 6465 7420 206f 706f 6e65 6420 >>> 010 7461 6261 7361 2065 7274 6e61 6173 7463 >>> 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 >>> 030 6520 636e 7972 7470 6465 6f20 2072 7369 >>> 040 6e20 746f 6120 6420 7461 6261 7361 2065 >>> 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 >>> 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 >> >> That is the text >> >> aflidet oponed tabasa ertnaastcoi n62: f li esie cnyrtpdeo rsin toa d >> tabasa ehOm .yW eaflidet oorllabkct ar >> >> You are writing text over your database file. >> > I do not remember doing it. Okay, rather than you yourself, I mean something in your computer. Whatever that text is, it's not a SQLite database file and it's not even a slight modification of the database file format. It's just something that looks very like corrupted text. The word 'cnyrtpdeo' looks a bit like 'crypto' and 'oponed' looks like 'opened' and I think it's corrupted text. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: file is encrypted or is not a database
Simon Slavin wrote: > On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: > > >> 000 6166 6c69 6465 7420 206f 706f 6e65 6420 >> 010 7461 6261 7361 2065 7274 6e61 6173 7463 >> 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 >> 030 6520 636e 7972 7470 6465 6f20 2072 7369 >> 040 6e20 746f 6120 6420 7461 6261 7361 2065 >> 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 >> 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 >> > > That is the text > > aflidet oponed tabasa ertnaastcoi n62: f li esie cnyrtpdeo rsin toa d > tabasa ehOm .yW eaflidet oorllabkct ar > > You are writing text over your database file. > I do not remember doing it. -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: file is encrypted or is not a database
On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: > 000 6166 6c69 6465 7420 206f 706f 6e65 6420 > 010 7461 6261 7361 2065 7274 6e61 6173 7463 > 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 > 030 6520 636e 7972 7470 6465 6f20 2072 7369 > 040 6e20 746f 6120 6420 7461 6261 7361 2065 > 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 > 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 That is the text aflidet oponed tabasa ertnaastcoi n62: f li esie cnyrtpdeo rsin toa d tabasa ehOm .yW eaflidet oorllabkct ar You are writing text over your database file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: file is encrypted or is not a database
Hi. I noticed strange problem with some of my sqlite databases. It does not affect all of them. I have a db file, which I modify from the crontab using sqlite3 cli. Every once in a while, file goes bad. In the sense that when I connect to it using sqlite3 cli and do any select I get error message "Error: file is encrypted or is not a database" When I do a hex dump of db file, I get 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0e03 13b2 020 6600 9079 0200 1800 0100 030 0100 040 * while it is good and once it goes bad I get 000 6166 6c69 6465 7420 206f 706f 6e65 6420 010 7461 6261 7361 2065 7274 6e61 6173 7463 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 030 6520 636e 7972 7470 6465 6f20 2072 7369 040 6e20 746f 6120 6420 7461 6261 7361 2065 050 684f 6d20 2e79 5720 2065 6166 6c69 6465 060 7420 206f 6f72 6c6c 6162 6b63 7420 6172 Any explanation for this? I do not believe I have sqlite v2 sitting anywhere on that computer. -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fsync/fdatasync problem on UBIFS
Dear list, I'm using SQLite 3.6.20 on an ARM Linux device which uses the UBIFS filesystem (on OneNAND flash). When I perform a database update, and cut the power a few seconds later, the changes are rolled back when the device restarts. This is because after the restart the journal file has re-appeared in the filesystem. If I perform a manual sync command in the shell before cutting the power, everything works fine. Does anyone else have experience with running SQLite on UBIFS? Before switching to UBIFS we were using our application on a JFFS2 filesystem without any problems. Thanks, Ronny Dierckx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trigger on update bug in 3.6.20?
On 11/25/09 17:35 , "Vasu Nori" wrote: >> I have to disagree. failing safely is better than failing silently. > thanks for the discussion. (except I don't see the c++ analogy :) ) My eyes, my time, and my bandwidth would appreciate it if you could avoid quoting five full screens of irrelevant history for a one sentence comment. (and I apologize for also wasting bandwidth with this small reminder). Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to deal with non 'normilize-able' tables
What you are saying is you are holding information about items which have
different characteristics. To represent these as relations you would have a
product entity then you would have an attribute entity that would be like
(product_id,attribute_id,attribute_name,attribute_value) eg:
screwdriver1210, 1, handle, wood
screwdriver1210, 2, point, crossdrive
screwdriver1210, 3, weight, 180
hammer0899, 1,weight, 3.35
hammer0899, 2 head, steel
etc
You then join from product to this table and pick up the listed features.
You can of course use attribute_ids that are standardised eg 56 is always
weight etc
That's one way. Alternatively if there aren't too many different attributes
you can collapse ('denormalise') these into a bunch of fields in the product
table. I would recommend leaving your model normalised until you are forced
to compromise.
--
View this message in context:
http://old.nabble.com/How-to-deal-with-non-%27normilize-able%27-tables-tp26495733p26516107.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trigger on update bug in 3.6.20?
On Wed, Nov 25, 2009 at 4:43 AM, Pavel Ivanov wrote: > Try to look at things not from the point of view of your application > but from the point of view of the SQLite itself. > > > 1. backward compatibility. It worked before upto 3.6.16. so, probably > it > > should work the same now. > > It was undefined behavior up to 3.6.16, it is undefined behavior now. > It's not a problem of SQLite that two undefined behaviors are not the > same and somebody relied on one of them or another. > > > 2. maybe an error should be thrown instead of silently failing (like > > oracle does, as you mentioned..). this will alert the apps using the > > triggers incorrectly but have been working with versions <= 3.6.16. > > It's not "silently failing". It silently works and works correctly, > although "correctly" is not correct from you app's POV. > > > in both before and after triggers, don't allow modifications to the > origin > > tables. (origin table = the table this trigger is on) > > throw error at execution time of the trigger, if this condition occurs. > > This will break a lot of applications (including yours). > So looking at all arguments your suggestions are not viable. It's like > suggesting to change C++ standard to make expressions like j = ++i + > ++i to work in some deterministic way. If you dared not to notice > "undefined behavior" warning in the documentation and relied on > something your particular version of compiler did then compiler is not > at fault when it changed behavior in another version. > > I have to disagree. failing safely is better than failing silently. thanks for the discussion. (except I don't see the c++ analogy :) ) > > Pavel > > On Tue, Nov 24, 2009 at 8:27 PM, Vasu Nori wrote: > > On Tue, Nov 24, 2009 at 12:28 PM, Pavel Ivanov > wrote: > > > >> > I am not what you mean by Oracle's Before triggers have different > >> concept? > >> > care to explain? > >> > >> Sure. When Oracle calls your before update trigger it provides you old > >> values of the row and storage for new values of the row. You can > >> change whatever you like in this storage and be sure that it will make > >> its way into the table. You don't change these values with update > >> statement, you use simple assignment as to some pl/sql variable, don't > >> you? And in this way you can even change values that were provided in > >> update statement which could lead to somewhat "strange" behavior from > >> the point of view of the user not knowing about trigger existence. > >> Also in Oracle you cannot change other rows in the same table trigger > >> was called on at all. > > > > > >> In SQLite on the other hand you can change whatever table and whatever > >> rows you like (except the same rows before update trigger was called > >> on) but you have to issue update statement for that which will work > >> independently from the statement caused trigger to work in the first > >> place. Because of this very concept limitation you've got undefined > >> behavior if you change the same row... > >> > >> > This specific trigger and the conditions under which it operates do > not > >> > cause any such problems. > >> > > >> > but it is not... > >> > >> You're wrong. Just look closer on the statement drh pointed to: your > >> BEFORE UPDATE trigger modifies a row that should have been updated by > >> UPDATE statement which called the trigger (you're updating the row > >> with the same _id). So you're hitting exactly this explained undefined > >> behavior. > >> > >> > > thanks for the email. you are right. it is the same row. but my point is > a > > bit different. > > let me outline 2 different approaches to this. > > > > 1. backward compatibility. It worked before upto 3.6.16. so, probably > it > > should work the same now. > > 2. maybe an error should be thrown instead of silently failing (like > > oracle does, as you mentioned..). this will alert the apps using the > > triggers incorrectly but have been working with versions <= 3.6.16. > > > > in both before and after triggers, don't allow modifications to the > origin > > tables. (origin table = the table this trigger is on) > > throw error at execution time of the trigger, if this condition occurs. > > > > If there is enough encouragement on one or the other, I could do a > patch.. > > > > and thanks once again for your responses. sqlite is great and I would > like > > to help make it better by bringing issues to the forum.. > > > >> > >> Pavel > >> > >> On Tue, Nov 24, 2009 at 3:06 PM, Vasu Nori wrote: > >> > On Tue, Nov 24, 2009 at 11:25 AM, Pavel Ivanov > >> wrote: > >> > > >> >> > yes it is risky, in general. but in this specific case, I expect no > >> >> > problems. > >> >> > just as a reference, this works just fine on mysql and oracle.. > >> >> > and used to work quite well until sqlite 3.6.16. > >> >> > >> >> I don't know anything about MySQL but Oracle has completely different > >> >> concept of BEFORE UPDATE triggers, so you cannot compare it with
[sqlite] advantages of Not using shared cache mode
Hi, In trying to understand shared cache mode, I would like to know why one would Not use it. According to http://www.hwaci.com/sw/sqlite/sharedcache.html, shared cache mode "can significantly reduce the quantity of memory and IO required by the system." In http://www.mail-archive.com/sqlite-users@sqlite.org/msg26239.html, Dr. Hipp mentions some threading restrictions related to shared cache mode, but all of them seem to have been dropped in version 3.5.0. For example, whereas shared cache was previously enabled/disabled for only one thread, now it is for an entire process (http://www.hwaci.com/sw/sqlite/c3ref/enable_shared_cache.html). Even the virtual table restriction was removed in version 3.6.17 (http://www.hwaci.com/sw/sqlite/sharedcache.html). So if the threading restrictions no longer exist, and if shared cache mode is better performing in terms of memory usage and I/O, why is it not the default? What is the advantage of not using it? Thanks, Nicolas Rivera ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple threads with shared cache mode
So then only one write transaction at a time is allowed per database. Which means there is no advantage, in terms of concurrency, with using shared cache mode. Right? > On 11/24/2009 4:17 PM, Pavel Ivanov wrote: > > Indeed, it's weird. And I've just realized that if we have two > simultaneous write transactions they both have to write their own > journal whenever they wish to write something to disk. SQLite database > cannot have two different journal files, so it should serialize > transactions whenever they want to actually write something to the > file. Maybe that's what was meant in the doc? I can't say, hopefully > somebody with more knowledge can explain this. > > Pavel > > On Tue, Nov 24, 2009 at 3:46 PM, presta wrote: > >> >>> No, it's one write transaction per table. >>> >> Wierd, according to the doc : "At most one connection to a single shared >> cache may open a write transaction at any one time. This may co-exist with >> any number of read transactions" >> >> >> >> -- >> View this message in context: >> http://old.nabble.com/multiple-threads-with-shared-cache-mode-tp26500974p26502966.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key constraint as column constraint?
Sry, was a bit confused You are right :-) Of course FOREIGN KEY makes no sense in a column const. ... Pavel Ivanov schrieb: > According to http://www.sqlite.org/lang_createtable.html you can > mention foreign-key-clause (starting with REFERENCES) as > column-constraint. Why it doesn't work for you? > > Pavel > > On Wed, Nov 25, 2009 at 10:33 AM, Jan wrote: >> Hi, >> >> I am testing the new fk support in my db. Currently I have *column >> constraints* for fk that were parsed by genfkey to create triggers. >> >> Simply adding FOREIGN KEY (column) to the column constr. seems not to >> work. But moving everything to the end of the table definition as a >> table constraint works. >> >> As far as I understand the docu is correct here (it is not possible tu >> use FOREIGN KEY in column constr.). But CHECK constraints are. Why is >> there a difference? >> >> Jan >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key constraint as column constraint?
According to http://www.sqlite.org/lang_createtable.html you can mention foreign-key-clause (starting with REFERENCES) as column-constraint. Why it doesn't work for you? Pavel On Wed, Nov 25, 2009 at 10:33 AM, Jan wrote: > Hi, > > I am testing the new fk support in my db. Currently I have *column > constraints* for fk that were parsed by genfkey to create triggers. > > Simply adding FOREIGN KEY (column) to the column constr. seems not to > work. But moving everything to the end of the table definition as a > table constraint works. > > As far as I understand the docu is correct here (it is not possible tu > use FOREIGN KEY in column constr.). But CHECK constraints are. Why is > there a difference? > > Jan > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign Key constraint as column constraint?
Hi, I am testing the new fk support in my db. Currently I have *column constraints* for fk that were parsed by genfkey to create triggers. Simply adding FOREIGN KEY (column) to the column constr. seems not to work. But moving everything to the end of the table definition as a table constraint works. As far as I understand the docu is correct here (it is not possible tu use FOREIGN KEY in column constr.). But CHECK constraints are. Why is there a difference? Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
> The same is true of FOREIGN KEY, by the way (I checked), but that's a bit > more obvious since breaking FOREIGN KEY will always result in a database the > programmer would consider corrupt. You're not quite right. You're talking about immediate foreign keys. There're deferred foreign keys too. See section 4.2 here: http://www.sqlite.org/foreignkeys.html. Pavel On Wed, Nov 25, 2009 at 9:13 AM, Simon Slavin wrote: > > On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote: > >>> I couldn't find the answer documented anywhere, so I will have to assume >>> that it may change in future versions. Unless the requirement for depth >>> first is somewhere in the SQL specification. >> >> I believe it should be. Triggers should be executed before the >> statement causing them to fire is considered successful. Otherwise all >> triggers checking some constraints and prohibiting incorrect data (and >> thus raise(...) function) are useless. > > Ahha. That makes sense: it would have to RAISE an error and back out the > other transactions anyway, so it may as well do the TRIGGERs first. Okay, on > that basis I can write my code assuming that it will always behave > depth-first. > > The same is true of FOREIGN KEY, by the way (I checked), but that's a bit > more obvious since breaking FOREIGN KEY will always result in a database the > programmer would consider corrupt. I wrote some code in another DBMS once > that betrayed that it did some really nasty things when one FOREIGN KEY > triggered another one. > > Thanks for the help. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote: >> I couldn't find the answer documented anywhere, so I will have to assume >> that it may change in future versions. Unless the requirement for depth >> first is somewhere in the SQL specification. > > I believe it should be. Triggers should be executed before the > statement causing them to fire is considered successful. Otherwise all > triggers checking some constraints and prohibiting incorrect data (and > thus raise(...) function) are useless. Ahha. That makes sense: it would have to RAISE an error and back out the other transactions anyway, so it may as well do the TRIGGERs first. Okay, on that basis I can write my code assuming that it will always behave depth-first. The same is true of FOREIGN KEY, by the way (I checked), but that's a bit more obvious since breaking FOREIGN KEY will always result in a database the programmer would consider corrupt. I wrote some code in another DBMS once that betrayed that it did some really nasty things when one FOREIGN KEY triggered another one. Thanks for the help. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
> I couldn't find the answer documented anywhere, so I will have to assume that > it may change in future versions. Unless the requirement for depth first is > somewhere in the SQL specification. I believe it should be. Triggers should be executed before the statement causing them to fire is considered successful. Otherwise all triggers checking some constraints and prohibiting incorrect data (and thus raise(...) function) are useless. Pavel On Wed, Nov 25, 2009 at 8:59 AM, Simon Slavin wrote: > > On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote: > >> Does this answers question? > > I think it does for the current version: depth first. Thanks. > > I couldn't find the answer documented anywhere, so I will have to assume that > it may change in future versions. Unless the requirement for depth first is > somewhere in the SQL specification. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote: > Does this answers question? I think it does for the current version: depth first. Thanks. I couldn't find the answer documented anywhere, so I will have to assume that it may change in future versions. Unless the requirement for depth first is somewhere in the SQL specification. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
Does this answers question?
sqlite> create table log (t);
sqlite> create table t1 (a);
sqlite> create table t2 (a);
sqlite> create trigger tt1 after update on t1 begin
...> insert into t2 values (new.a);
...> insert into log values ("update of t1, a="||new.a);
...> end;
sqlite> create trigger ttt1 after insert on t1 begin
...> insert into log values ("insert into t1, a="||new.a);
...> end;
sqlite> create trigger ttt2 after insert on t2 begin
...> insert into log values ("insert into t2, a="||new.a);
...> end;
sqlite> insert into t1 values (1);
sqlite> insert into t1 values (2);
sqlite> insert into t1 values (3);
sqlite> insert into t1 values (4);
sqlite> insert into t1 values (5);
sqlite> update t1 set a = 6;
sqlite> select rowid, t from log;
1|insert into t1, a=1
2|insert into t1, a=2
3|insert into t1, a=3
4|insert into t1, a=4
5|insert into t1, a=5
6|insert into t2, a=6
7|update of t1, a=6
8|insert into t2, a=6
9|update of t1, a=6
10|insert into t2, a=6
11|update of t1, a=6
12|insert into t2, a=6
13|update of t1, a=6
14|insert into t2, a=6
15|update of t1, a=6
Pavel
On Tue, Nov 24, 2009 at 3:20 PM, Simon Slavin wrote:
> This new implementation of recursive TRIGGERs. Is it depth-first or
> width-first ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running out of space
On 25 Nov 2009, at 12:26pm, Antti Nietosvaara wrote: > Simon Slavin wrote: >> I assume your database file is on your boot volume. What operating system >> are you using ? >> > > Actually the database is alone in its own partition. Ah. That's better in some ways. But I think you're still better off taking the standard measures one takesf any partition. Ignore the fact that it's a SQLite database. Use some sort of standard monitoring tool (there are many) to email/SMS you when the amount of free space on that partition drops below a certain number of bytes. When that happens do human-type things to free some space, e.g. SQLite's VACUUM operation, or deleting old records or whatever. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trigger on update bug in 3.6.20?
Try to look at things not from the point of view of your application but from the point of view of the SQLite itself. > 1. backward compatibility. It worked before upto 3.6.16. so, probably it > should work the same now. It was undefined behavior up to 3.6.16, it is undefined behavior now. It's not a problem of SQLite that two undefined behaviors are not the same and somebody relied on one of them or another. > 2. maybe an error should be thrown instead of silently failing (like > oracle does, as you mentioned..). this will alert the apps using the > triggers incorrectly but have been working with versions <= 3.6.16. It's not "silently failing". It silently works and works correctly, although "correctly" is not correct from you app's POV. > in both before and after triggers, don't allow modifications to the origin > tables. (origin table = the table this trigger is on) > throw error at execution time of the trigger, if this condition occurs. This will break a lot of applications (including yours). So looking at all arguments your suggestions are not viable. It's like suggesting to change C++ standard to make expressions like j = ++i + ++i to work in some deterministic way. If you dared not to notice "undefined behavior" warning in the documentation and relied on something your particular version of compiler did then compiler is not at fault when it changed behavior in another version. Pavel On Tue, Nov 24, 2009 at 8:27 PM, Vasu Nori wrote: > On Tue, Nov 24, 2009 at 12:28 PM, Pavel Ivanov wrote: > >> > I am not what you mean by Oracle's Before triggers have different >> concept? >> > care to explain? >> >> Sure. When Oracle calls your before update trigger it provides you old >> values of the row and storage for new values of the row. You can >> change whatever you like in this storage and be sure that it will make >> its way into the table. You don't change these values with update >> statement, you use simple assignment as to some pl/sql variable, don't >> you? And in this way you can even change values that were provided in >> update statement which could lead to somewhat "strange" behavior from >> the point of view of the user not knowing about trigger existence. >> Also in Oracle you cannot change other rows in the same table trigger >> was called on at all. > > >> In SQLite on the other hand you can change whatever table and whatever >> rows you like (except the same rows before update trigger was called >> on) but you have to issue update statement for that which will work >> independently from the statement caused trigger to work in the first >> place. Because of this very concept limitation you've got undefined >> behavior if you change the same row... >> >> > This specific trigger and the conditions under which it operates do not >> > cause any such problems. >> > >> > but it is not... >> >> You're wrong. Just look closer on the statement drh pointed to: your >> BEFORE UPDATE trigger modifies a row that should have been updated by >> UPDATE statement which called the trigger (you're updating the row >> with the same _id). So you're hitting exactly this explained undefined >> behavior. >> >> > thanks for the email. you are right. it is the same row. but my point is a > bit different. > let me outline 2 different approaches to this. > > 1. backward compatibility. It worked before upto 3.6.16. so, probably it > should work the same now. > 2. maybe an error should be thrown instead of silently failing (like > oracle does, as you mentioned..). this will alert the apps using the > triggers incorrectly but have been working with versions <= 3.6.16. > > in both before and after triggers, don't allow modifications to the origin > tables. (origin table = the table this trigger is on) > throw error at execution time of the trigger, if this condition occurs. > > If there is enough encouragement on one or the other, I could do a patch.. > > and thanks once again for your responses. sqlite is great and I would like > to help make it better by bringing issues to the forum.. > >> >> Pavel >> >> On Tue, Nov 24, 2009 at 3:06 PM, Vasu Nori wrote: >> > On Tue, Nov 24, 2009 at 11:25 AM, Pavel Ivanov >> wrote: >> > >> >> > yes it is risky, in general. but in this specific case, I expect no >> >> > problems. >> >> > just as a reference, this works just fine on mysql and oracle.. >> >> > and used to work quite well until sqlite 3.6.16. >> >> >> >> I don't know anything about MySQL but Oracle has completely different >> >> concept of BEFORE UPDATE triggers, so you cannot compare it with >> >> SQLite in here. >> >> >> > I am not what you mean by Oracle's Before triggers have different >> concept? >> > care to explain? >> > >> > >> >> And I wouldn't look at this issue as on a regression but as on some >> >> undocumented feature that you relied upon and it was changed without a >> >> warning. I guess now SQLite works like this: >> >> 1) you issue update to change d; >> >> 2) SQLite finds row
Re: [sqlite] Running out of space
Simon Slavin wrote: > I assume your database file is on your boot volume. What operating system > are you using ? > Actually the database is alone in its own partition. I'm currently trying to avoid the problem by assigning "big enough" partition for the db calculated from the estimated data size. However, it would be nice to somehow handle this in the application just in case the estimations are wrong. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running out of space
On 11/25/09 10:50 , "Simon Slavin" wrote: > The message is that if you are short of > space it is already too late for any software to cope with the problem. > I disagree. It all depends on where you set the threshold for "short of space". To give you a trivial example, if I set the threshold to 2GB on *my* machine for *my* application, then I will never be at risk. But the correct threshold depends on several factors: - you host platform, mainly its OS, but also any services running on it. - your application usage patterns, which I cannot speculate on. - the internal needs of SQLite during space scavenging, which are knowable though I don't know them. They themselves might depend on your application usage patterns. The threshold might be difficult to determine, and might not even be a constant, but a function of your current data set size. For example, setting the threshold to the triple of your current database size might be enough - or totally overkill. Here is how I would tackle this issue, through experimenting. I would start by determining what kind of function of the dataset size the threshold it. Whatever it is, it can be approximated by an affine function for small dataset sizes: T(N) = a+b*N Where T is the threshold, and N is the dataset size. We first need an upper bound for the constant a. Note that all we need is an upper bound. More generally speaking, you can always write: T(N) = T(0)+T'(N) where T'(0)=0, where a=T(0). 1- To assess a=T(0) first build a small -but not empty- dataset, and let your system and your application run*. Then artificially deplete the available disk space to zero, for example by storing on it a dummy file. Then run your scavenging procedure. If it runs OK, then you can use a = 0 (though I'd still use some value >0). But it's possible (likely?) it will fail because it hasn't got enough disk space. So release some increasing disk space until your scavenging procedure succeeds (you can use a dichotomy procedure for example). When it succeeds, the initial free disk space you had to set aside can be used as T0, an upper bound for T(0). (*) this procedure assumes your application is the only one depleting disk space. If that's not the case, then you need to take the other consumers into consideration. 2- Of course experimenting with a small data set is not good enough since you want to handle a situation that occurs with large data sets by definition! So repeat step 1 with a series of datasets of increasing sizes: N1, N2, N3, N4... (I would typically double the size at each step). Using N1, you will get an upper bound T1 for T(N1). Using N2, you will get an upper bound T2 for T(N2). And so on. Eventually, you'll have a set of points for a working T(N). You can fit a function to that set of points: - T1 will let you determine whether the threshold can be constant or whether it needs to have some multiple of the dataset size. - T2 will let you determine whether an affine function is enough to represent T(N) or whether you need something more sophisticated. - T3 and the others will let you see whether a low-order polynomial function is enough or whether you need to go to an exponential function. I refuse to consider the possibility that an exponential function would not even be enough :-) Of course how far you want to work through all this depends on how certain you want to be of your system resilience under low storage space conditions. This is how I would do it. Did I miss anything? Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running out of space
On 25 Nov 2009, at 9:40am, Antti Nietosvaara wrote: > I have an application which keeps an index of data in an SQLite > database. I'm trying to figure out the best way to handle the possible > scenario of database filling out the entire hard disk. I could just > delete some of the oldest rows, but I wonder if it's possible that even > delete statements fail due to insufficient disk space for journal file. > Is there a robust way to handle these situations? I assume your database file is on your boot volume. What operating system are you using ? For most operating systems if you get close to running out of space your program has probably crashed long before it will notice there are zero bytes free and start freeing up disk space. The message is that if you are short of space it is already too late for any software to cope with the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running out of space
Deleting data may not free enough space in the database file to allow new records to be added [the new records may contain more data]. You could continually delete old records until an INSERT succeeded (indicating enough space now)? Otherwise, I'd say you'll just have to monitor the hard disk space and ensure you have enough free. Even if you detect the disk becoming full, you then have to decide on what to do: delete old records, or raise some kind of alert to the user? Don't forget you can always VACUUM the database (providing there's sufficient disk space for SqLite to rewrite the vacuumed DB file out). If you do that regularly, as well as deleting unwanted records, you shouldn't run out of disk space. (Unless of course you really DO need all the data - in which case your disk needs to be bigger!) Thanks, Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Antti Nietosvaara Sent: 25 November 2009 09:41 To: sqlite-users@sqlite.org Subject: [sqlite] Running out of space Hello, I have an application which keeps an index of data in an SQLite database. I'm trying to figure out the best way to handle the possible scenario of database filling out the entire hard disk. I could just delete some of the oldest rows, but I wonder if it's possible that even delete statements fail due to insufficient disk space for journal file. Is there a robust way to handle these situations? Thanks - Antti ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Running out of space
Hello, I have an application which keeps an index of data in an SQLite database. I'm trying to figure out the best way to handle the possible scenario of database filling out the entire hard disk. I could just delete some of the oldest rows, but I wonder if it's possible that even delete statements fail due to insufficient disk space for journal file. Is there a robust way to handle these situations? Thanks - Antti ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
