[sqlite] Understanding Foreign Key Contraints
I have a fairly complex web based application which helps manage the process of running an American Football results picking competition through the season. I am in the process of porting it to Sqlite ready for the start of next season. I just discovered a bug in my handling of a constraint violation that has made me want to rethink my strategy in this area. My original strategy was to completely cascade deletes, but the bug I discovered showed me a place in the user interface where that approach could be too dangerous, as it wasn't obvious that there would be side effects at the user interface level. So I want to replan my strategy. But given the complexity of the situation I want to make sure I don't make any mistakes. There is one particular pattern that occurs in several places, where what might happen is ambiguous (at least to me), and I would like this mailing lists view of what will happen and what is the right thing to do to make it so. [Note the application is web based with Ajax calls. Every single page request or ajax call opens the database and does a "PRAGMA foreign_keys = ON" as its first function] Let me list my key entities in this pattern At the top level there are three "Team" with primary key tid (which is a three character string - but that is probably irrelevant) "Participant" with primary key uid and "Competition" with primary key cid. There are then some secondary entities, for this example I need two "Registration" (user registers for a competition) which has primary key (cid,uid). Its foreign key constraints are defined as cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE, -- Competition ID uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE ON DELETE CASCADE, --User ID "Team_in_competition" with primary key (cid,tid). Its foreign key constraints are defined as cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- Competition ID tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON DELETE CASCADE, --TeamID And then a derived entity from the other two "Playoff_pick" with primary key (cid,uid,tid). Its the foreign key constrains on this one which is rather tricky I want to arrange my constraints so that. Deleting Competition or Participant Deletes everything below it Deleting Team_in_competition fails with a constrain violation when there is a playoff_pick that refers to it I am hoping that I can define the constraints so. FOREIGN KEY (cid,uid) REFERENCES registration(cid,uid) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (cid,tid) REFERENCES team_in_competition(cid,tid) What I am hoping is that if I delete the "Competition" (or "Participant") , then it deletes the "Registration" which in turn deletes the "Playoff_pick" immediately, but that because the deleting of "Team_in_competition" is deferred until commit time, by that time the commit happens there is no "Playoff_pick" to prevent the "Team_in_competition" from being deleted. Have I understood this right? -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange issue with sqlite 3.7.9
On 05/07/12 01:05, Richard Hipp wrote: On Wed, Jul 4, 2012 at 3:05 PM, Alan Chandlerwrote: The commit referenced by that page: http://www.sqlite.org/src/info/b23ae131874bc5c621f0 went into 3.7.9. So the problem was probably introduced in 3.7.9, not 3.7.10. Indeed - I just tried the test case in that ticket and in fact demonstrated that the bug is in 3.7.9 Does that mean that the problem is fixed by http://www.sqlite.org/src/info/0dc4cb9355 and does not exist in recent releases of SQLite? Or are you saying that this is a new problem that needs to be addressed. If the latter, I'm going to need you to send me a database again so that I can reproduce the problem, because I did keep the one you sent last time. No, its the same problem and fixed in later releases. The only issue is that the main web site lists the bug as starting in 3.7.10, when in fact it starts in 3.7.9. This is unfortunate because the latest Ubuntu LTS release (12.04) uses 3.7.9, and so my application broke again. (I have reported this to Ubuntu and they have at least acknowledged the bug). The test case in this ticket http://www.sqlite.org/src/info/b7c8682cc1 demonstrates the problem in 3.7.9 -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange issue with sqlite 3.7.9
On 04/07/12 18:52, Dan Kennedy wrote: On 07/04/2012 08:26 PM, Alan Chandler wrote: Due to hardware problems with my Debian Stable server, I have just upgraded to Ubuntu-Server 12.04. I have installed sqlite3 and when I ask it the version (with .version) it replies SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e Which exactly complies with the sqlite web site for this release. Sometime ago I reported a problem with 3.7.10 referenced here http://www.sqlite.org/src/info/b7c8682cc1 The commit referenced by that page: http://www.sqlite.org/src/info/b23ae131874bc5c621f0 went into 3.7.9. So the problem was probably introduced in 3.7.9, not 3.7.10. Indeed - I just tried the test case in that ticket and in fact demonstrated that the bug is in 3.7.9 -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange issue with sqlite 3.7.9
Due to hardware problems with my Debian Stable server, I have just upgraded to Ubuntu-Server 12.04. I have installed sqlite3 and when I ask it the version (with .version) it replies SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e Which exactly complies with the sqlite web site for this release. Sometime ago I reported a problem with 3.7.10 referenced here http://www.sqlite.org/src/info/b7c8682cc1 Using the same database (which I would prefer not to publish because its full of personal financial information - I already gave Richard Hipp a randomised copy in respect of the last bug) I have experienced a problem shown below. I am not sure this is the same problem as before (its very similar) but that was reported as being introduced with changes introduced in 3.7.10 select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain = 'Hartley' AND ((t.src = a.name and t.srccode = c.id) ) GROUP BY c.id; Produces output, where as select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain = 'Hartley' AND ((t.src = a.name and t.srccode = c.id) OR (t.dst = a.name and t.dstcode = c.id )) GROUP BY c.id; does not even though ONLY added an OR clause within a bracketed AND clause Just to confuse the issue dfxaction (but not the other tables) is a view - defined as below (and I think this is where the similarity to the other bug comes in). If I replace that with xaction (the real table its based on) then the second select above does produce expected output. CREATE VIEW dfxaction AS SELECT t.id,t.date,t.version, src, srccode, dst, dstcode,t.description, rno, repeat, CASE WHEN t.currency = 'GBP' THEN t.amount WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN t.srcamount WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN t.dstamount ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER) END AS dfamount FROM xaction AS t LEFT JOIN account AS sa ON t.src = sa.name LEFT JOIN account AS da ON t.dst = da.name LEFT JOIN currency ON t.currency != 'GBP' AND (t.srcamount IS NULL OR sa.currency != 'GBP') AND (t.dstamount IS NULL OR da.currency != 'GBP') AND t.currency = currency.name; ALSO just to confirm - I repeated the same experiment on sqlite3 version 3.7.13 (Debian unstable version) and the second query performs perfectly I am mentioning this here because the earlier bug was supposed to have been caused by a change made by 3.7.10, whereas this is 3.7.9 and given its the version of choice in ubuntu it might be better to clarify whether there is a problem there or not. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10
On 09/03/12 00:29, Richard Hipp wrote: On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandlerwrote: The complete database schema is here https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sql<https://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql> My database is full of private financial data so I would rather not just post it publically. If you really need the data I could mail it to you privately (its only 366kb big) Can you scrub the data (replace numbers with values from random(), and all strings with random text?) If not, email the database directly to me. I have done some randomisation and sent it to you privately -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10
On 08/03/12 23:32, Richard Hipp wrote: On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandlerwrote: I have been running a financial management application application I wrote for a number of years. My "production" version runs on Debian stable system as a result is running sqlite v3.7.3. My personal development machine is running Debian unstable and as a result has sqlite 3.7.10. Earlier this week I discovered a bug in a rarely used part of the application, so took a copy of the production database and ran it on my development machine. I quickly found that and fixed it, but another major element of the application appeared to give some strange results. I have spend some down tracking down what caused the problem, and it seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql. It seems to me that the later release gets things wrong - but it might be that the newer version has some sort of PRAGMA that I am not using right. So I would like to ask here where I am going wrong. The basic issue is around a view on a table called "xaction" - the transactions processed. It has optional "source" and "destination" accounts (must be at least one or the other but can also have both) and optional "codes" that relate to classes of transaction as they appear in the account. I put a view on top of this which normalises the currency for use in my accounts. The schema for the view is ... CREATE VIEW dfxaction AS SELECT t.id,t.date,t.version, src, srccode, dst, dstcode,t.description, rno, repeat, CASE WHEN t.currency = 'GBP' THEN t.amount WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN t.srcamount WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN t.dstamount ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER) END AS dfamount FROM xaction AS t LEFT JOIN account AS sa ON t.src = sa.name LEFT JOIN account AS da ON t.dst = da.name LEFT JOIN currency ON t.currency != 'GBP' AND (t.srcamount IS NULL OR sa.currency != 'GBP') AND (t.dstamount IS NULL OR da.currency != 'GBP') AND t.currency = currency.name; The query that results in differences between the two versions of sqlite (I have cut this down to the minimum I could find showed the difference). SELECT c.id AS id, c.type AS type, c.description AS description, t.* FROM dfxaction AS t, code AS c WHERE t.date BETWEEN 129384 AND 1325375999 AND ((t.src IS NOT NULL AND t.srccode = c.id) OR t.dstcode = c.id ) ORDER BY c.description COLLATE NOCASE ASC; The little part "t.src IS NOT NULL AND" seems to be the trigger for cutting down the records to almost none from the full amount because when I remove it I got more like the correct number of records. Most of the records excluded by putting the clause in DO NOT have t.src of NULL. AND OF COURSE ON sqlite 3.7.3 I get all the records I expect. The other important aspect. If I don't use a view, but instead replace "FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10 delivers all the records I would expect. So I am completely perplexed as to why there are changes between behaviour. Can anyone help me understand. Maybe the change at http://www.sqlite.org/src/info/b23ae13187 broke something. Can you get us a complete schema with enough data to actually run a test case that shows the problem? The complete database schema is here https://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql My database is full of private financial data so I would rather not just post it publically. If you really need the data I could mail it to you privately (its only 366kb big) -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10
On 08/03/12 22:44, Alan Chandler wrote: I have been running a financial management application application I wrote for a number of years. My "production" version runs on Debian stable system as a result is running sqlite v3.7.3. My personal development machine is running Debian unstable and as a result has sqlite 3.7.10. Earlier this week I discovered a bug in a rarely used part of the application, so took a copy of the production database and ran it on my development machine. I quickly found that and fixed it, but another major element of the application appeared to give some strange results. I have spend some down tracking down what caused the problem, and it seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql. It seems to me that the later release gets things wrong - but it might be that the newer version has some sort of PRAGMA that I am not using right. So I would like to ask here where I am going wrong. Things have now got stranger. I just saw the post on the e-mail list for sqlitestudio and thought that looks interesting, so I have now downloaded it. It seems to be using sqlite 3.7.8 It works correctly, and the very same sql using sqlite manager in Mozilla goes wrong (this is linked to sqlite 3.7.10) -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange difference between sqlite 3.7.3 and 3.7.10
I have been running a financial management application application I wrote for a number of years. My "production" version runs on Debian stable system as a result is running sqlite v3.7.3. My personal development machine is running Debian unstable and as a result has sqlite 3.7.10. Earlier this week I discovered a bug in a rarely used part of the application, so took a copy of the production database and ran it on my development machine. I quickly found that and fixed it, but another major element of the application appeared to give some strange results. I have spend some down tracking down what caused the problem, and it seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql. It seems to me that the later release gets things wrong - but it might be that the newer version has some sort of PRAGMA that I am not using right. So I would like to ask here where I am going wrong. The basic issue is around a view on a table called "xaction" - the transactions processed. It has optional "source" and "destination" accounts (must be at least one or the other but can also have both) and optional "codes" that relate to classes of transaction as they appear in the account. I put a view on top of this which normalises the currency for use in my accounts. The schema for the view is ... CREATE VIEW dfxaction AS SELECT t.id,t.date,t.version, src, srccode, dst, dstcode,t.description, rno, repeat, CASE WHEN t.currency = 'GBP' THEN t.amount WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN t.srcamount WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN t.dstamount ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER) END AS dfamount FROM xaction AS t LEFT JOIN account AS sa ON t.src = sa.name LEFT JOIN account AS da ON t.dst = da.name LEFT JOIN currency ON t.currency != 'GBP' AND (t.srcamount IS NULL OR sa.currency != 'GBP') AND (t.dstamount IS NULL OR da.currency != 'GBP') AND t.currency = currency.name; The query that results in differences between the two versions of sqlite (I have cut this down to the minimum I could find showed the difference). SELECT c.id AS id, c.type AS type, c.description AS description, t.* FROM dfxaction AS t, code AS c WHERE t.date BETWEEN 129384 AND 1325375999 AND ((t.src IS NOT NULL AND t.srccode = c.id) OR t.dstcode = c.id ) ORDER BY c.description COLLATE NOCASE ASC; The little part "t.src IS NOT NULL AND" seems to be the trigger for cutting down the records to almost none from the full amount because when I remove it I got more like the correct number of records. Most of the records excluded by putting the clause in DO NOT have t.src of NULL. AND OF COURSE ON sqlite 3.7.3 I get all the records I expect. The other important aspect. If I don't use a view, but instead replace "FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10 delivers all the records I would expect. So I am completely perplexed as to why there are changes between behaviour. Can anyone help me understand. Thanks -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detach says database is locked
On 21/10/10 00:35, Igor Tandetnik wrote: > Alan Chandler wrote: >> Further to my other post related to attaching to databases with PHP PDO, >> I have now managed to ATTACH OK >> >> However, when I come to DETACH, I am getting a Database is locked error >> when I try and execute it. >> >> The only thing happening to that database in between ATTACH and DETACH >> is a single row SELECT > > Make sure you reset or finalize the statement (not sure how it's done in PHP). I believe closeCursor(); does the job. If not, I unset the variable. However, I have figured out the problem - which is really sad since I can't do what I hoped - which is loop round a set of rows from a higher level select statement ATTACHing and DETACHing to a database in turn . Because the top level select loop is its own transaction, you can't detach from the database which you attached in the inner part of the loop since at that moment you are in a transaction. I think my way out of the problem is to pull out all the rows into a single array, then close the transaction and interate over the array members. Fortunately in the case its just a menu - so there probably won't be too many items. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Detach says database is locked
Further to my other post related to attaching to databases with PHP PDO, I have now managed to ATTACH OK However, when I come to DETACH, I am getting a Database is locked error when I try and execute it. The only thing happening to that database in between ATTACH and DETACH is a single row SELECT I don't really understand why I can't DETACH. Can anyone give me any ideas what this could be. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange position as a result of ATTACH
I am testing an application written in PHP, so all sqlite access is
through PDO.
In a particular situation I am scanning a directory for filenames with a
'.db' extension and attaching to each database in turn to do something
with it. I am using a prepared statement ($astmt) to do the attach,
binding it with each filename that matches the database type and
executing the prepared statement.
Thus ...
$db=new PDO('sqlite:'.DATA_DIR.'football.ini');
...
$astmt = $db->prepare("ATTACH ? AS comp");
$fns = scandir(DATA_DIR);
foreach ($fns as $filename) {
if(filetype(DATA_DIR.$filename) == 'file') {
$split = splitFIlename($filename);
if($split[1] == 'db') {
// found a database file
$astmt->bindValue(1,DATA_DIR.$filename);
$astmt->execute(); //ATTACH
...
$astmt->closeCursor();
$db->exec("DETACH comp;");
}
}
}
The first time round the loop seems to work fine, but the second time
round the loop, the $astmt->execute(); trying to attach to the file
fails with SQLITE_SCHEMA
Reading the docs, it appears SQLITE_SCHEMA means I need to recompile the
prepared statement each time round the loop.
WHY?
(I tried it and it works - but is inefficient).
PS = I suppose it may be a php bug that it is using the v1 version of
prepare
--
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] moving from mysql
On 16/10/10 21:07, Chris Percol wrote: > Hi there, > > I am thinking of moving from mysql to sqlite3 and want to know if there are > any gotchas? > > My current situation is a web based school booking system using a mysql > database with four tables. The busiest of the tables may have 6000 rows > added each year (I tend to empty this table at the end of the year). The > other tables store a little information, maybe 100 rows between them. > > There is approx 100 users creating bookings throughout the week, not a lot > of concurrents but I guess the possibility is there. > > From what I read on the sqlite site sqlite could handle this like a breeze. > I just wanted to ask for any thoughts regarding performance or limitations > before I make the move. > I don't know what language your web application is in, but I have just completed a port from Postgres of a financial accounting and planning application I wrote several years ago for myself. This was using PHP. From a performance perspective, it is really fast. I open a transaction and the head of most web pages, and keep it open through out the page even if I am only using selects, and every page I have comes up instantenously. Even one which calculates the complete profit and loss for my business, including calculating depreciation for capital purcheses made part way through a year finishes fast than you can notice. Riding on the success of that I am part way through porting an American Football results picking competition I think its important for a multiuser application to use WAL mode and I think this minimises the potential for locking conflicts from each of users. The benefit of a web application is that each page request opens the database, does its stuff and closes it again. There is therefore a reasonably large probability that all connections to the database are closed in a reasonably short space of time. In WAL mode, the last database close encorporates the WAL back into the main database, so there is relatively little time when its not encorporated back in. For me, the biggest problem is having sufficiently recent versions of all the code and libraries to support WAL mode. At least one host (Bluehost) that I use can't handle that. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
On 14/10/10 17:26, Kavita Raghunathan wrote: > I found an example: > Select * from order by ID where limit 1 offset n-1 > > Will retreive the nth row. > In this case order by ID is probably not necessary as its already ordered > with a few missing rows. It might happen to be ordered - but that is an implementation detail. Unless you use the ORDER BY clause the database may deliver the records in any order it wishes. It is not forced to use the ID order. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is the SYNTAX diagram wrong
I seem to be doing plenty of SELECT * FROM a LEFT JOIN b WHERE ...; but looking at the syntax diagrams at http://www.sqlite.org/lang_select.html it looks like I have to follow LEFT with OUTER. Shouldn't the diagram allow OUTER to be bypassed? -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Just want to double check on index need
On 14/10/10 17:28, Dan Kennedy wrote: > > On Oct 14, 2010, at 10:43 PM, Alan Chandler wrote: >> CREATE TABLE div_winner_pick ( ... >> PRIMARY KEY (cid,confid,divid,uid) >> ); ... >> >> CREATE INDEX div_win_pick_uid_cid_idx ON div_winner_pick (uid,cid); ... > It should be clearer. Basically the index would be redundant > if it contains the same columns in the same order as the primary > key. Or a prefix thereof. i.e. the following indexes would be > all be redundant (pure overhead for no benefit): > > CREATE INDEX x ON div_pick_winner(cid); > CREATE INDEX x ON div_pick_winner(cid,confid); > CREATE INDEX x ON div_pick_winner(cid,confid,divid); > CREATE INDEX x ON div_pick_winner(cid,confid,divid,uid); > > Your index is not redundant though. This is interesting - what if I changed the primary key to be PRIMARY KEY (uid,cid,confid,divid) Is that an optimisation that is useful to make? -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Just want to double check on index need
I am porting an application (American Football Results Picking
Competition) over from a Postgres databaseo to SQLite which involves
some fairly intense queries. I am doing this partially to do some
performance comparisons although I have some other reasons too.
I just want to make sure that I am setting up the indexes to some of the
tables correctly.
Here is an example of a representative type of table
CREATE TABLE div_winner_pick (
cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE
ON DELETE CASCADE, -- Competition ID
confid character(3) NOT NULL REFERENCES conference(confid) ON
UPDATE CASCADE ON DELETE CASCADE, --Conference ID
divid character(1) NOT NULL REFERENCES division(divid) ON UPDATE
CASCADE ON DELETE CASCADE, --Division ID
uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE
ON DELETE CASCADE, --User ID
tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON
DELETE CASCADE, --Team who will win division
submit_time bigint DEFAULT (strftime('%s','now')) NOT NULL, --Time
of submission
PRIMARY KEY (cid,confid,divid,uid)
);
where the Primary key references several columns
For this particular table - in my Postgres definition I created the
following two indexes
CREATE INDEX div_win_pick_uid_idx ON div_winner_pick (uid);
CREATE INDEX div_win_pick_cid_idx ON div_winner_pick (cid);
i.e. Two of the 4 fields that make up the primary key.
and I was anticipating doing the same - or something similar - I am not
yet convinced I don't need to do
CREATE INDEX div_win_pick_uid_cid_idx ON div_winner_pick (uid,cid);
However, I came across the following text on the SQLite Web Site as part
of the explanation of the CREATE TABLE command
"INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
constraints are implemented by creating an index in the database (in the
same way as a "CREATE UNIQUE INDEX" statement would). Such an index is
used like any other index in the database to optimize queries. As a
result, there often no advantage (but significant overhead) in creating
an index on a set of columns that are already collectively subject to a
UNIQUE or PRIMARY KEY constraint."
I just wanted to check that lack of advantage (and overhead) applies
purely to an index across all columns of the primary key and that if I
need the index across a lesser number of columns (because I am querying
for all records that match where I can define the values "cid" and "uid"
in the example above) it is still and advantage to create it separately.
--
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
On 04/09/10 12:31, Mike Zang wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > I don't think this applies to you, but I had to build an application where time for the user has to be reasonably accurate (an American Football picking competition, where the deadline was 5 minutes before each match) My users are worldwide. I realised that on the server end, I could carry the date/time around as a UNIX timestamp (ie seconds from 1970 UTC) and then use javascript on the client end (in a browser) to locally display stuff as (after multiplying by 1000). As a result, I almost always think about that approach as my first choice when writing a new app. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameters in views preparation
On 26/08/10 20:29, Alan Chandler wrote: > On 26/08/10 17:38, Simon Slavin wrote: > >> So someone can check it out. Try it with a VIEW that definitely doesn't >> exist, or use >> >> CREATE VIEW IF NOT EXISTS ... >> > > As far as I can work it out, the statement then prepares OK - but seems > then to execute as a no op. Since having completed that script and then > checking the schema with the command line sqlite3 utility, the view no > longer exists. > > This seems completely wrong. Surely it should do this check at > *execute* time not at *prepare* time. > Life is just too short. The benefit of a prepared statement is lost when you can't have parameters and you are only using it once anyway, so whilst there is a slight lengthening of the time when the database is Locked, its just easier to move the whole thing into a PDO::exec function (which in SQLITE terms I think prepares and then executes immediately). I did this, and my code now works fine. There might be a discussion about the difference between semantic and syntactic validation of prepared statements, but I am not expert enough in SQL to know what the perceived wisdom in this area is. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameters in views preparation
On 26/08/10 17:38, Simon Slavin wrote: > So someone can check it out. Try it with a VIEW that definitely doesn't > exist, or use > > CREATE VIEW IF NOT EXISTS ... > As far as I can work it out, the statement then prepares OK - but seems then to execute as a no op. Since having completed that script and then checking the schema with the command line sqlite3 utility, the view no longer exists. This seems completely wrong. Surely it should do this check at *execute* time not at *prepare* time. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameters in views preparation
On 26/08/10 13:38, Simon Slavin wrote:
>
> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>
>> This time it reported that the view it would have created failed because
>> the table (view) already existed.
>
> I'm sorry to ask this, but can you check for us whether a VIEW by that name
> really does exist ? Don't forget, VIEWs get saved in the file, they're not
> part of the attachment.
Yes it does - this VIEW outputs the transactions in the default currency
(rather than the currency of the transaction) and the whole objective of
my this particular php script is to update this view when the default
currency changes.
a...@kanga:~/dev/money/db[master]$ sqlite3 money.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema dfxaction
CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da .currency = 'GBP' THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS
INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;
sqlite>
--
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameters in views preparation
On 26/08/10 12:20, Pavel Ivanov wrote: > Yes, "validation" happens only at the time of execution. So you are > apparently doing something wrong and you better show your code. > easiest is to provide links to a copy. I've added a .txt extension to all the files to stop them being executed by the web server The key php file I am talking about is this one http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/updatedefcur.php.txt It is trying to update the view (dfxaction) which provides transaction amounts in the default currency because the default currency is changing.. You will see it requires db.inc which is here http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/db.inc.txt This initializes the global variable $db by opening the sqlite database. The whole database schema is loaded on initial initialization by reading this file The dfxaction view is created almost at the end of this file. http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/database.sql.txt (someone asked further down the thread whether the view really exists. Yes it does and its not temporary). -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameters in views preparation
On 26/08/10 10:22, Alan Chandler wrote: > I am using PHP PDO to access sqlite and have reached a peculiar error > situation. Basically its saying I can't have parameters in a CREATE > VIEW sql statement when I am preparing it. The plot thickens I replaced all the parameter placeholders with a quoted version of the parameter and undertook the prepare statement again. This time it reported that the view it would have created failed because the table (view) already existed. Is it correct to undertake the semantic validation at prepare time? In my code, I delete the view before attempting to recreate it by executing the prepared statement. Isn't that the time to validate whether there are semantic problems with the statement? The reason I was pre-preparing the statement was to limit the time I have to lock the database with a "BEGIN IMMEDIATE" (and the later "COMMIT"). -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Parameters in views preparation
I am using PHP PDO to access sqlite and have reached a peculiar error
situation. Basically its saying I can't have parameters in a CREATE
VIEW sql statement when I am preparing it.
I can't find any reference to this restriction or the error message in
the SQLite documentation, so I am wondering is it a SQLite restriction
or something PDO is forcing upon me?
This is the prepare statement followed by a var dump to check the error
code.
$vstmt = $db->prepare("
CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = ? THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = ? THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da .currency = ? THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS
INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != ? AND
(t.srcamount IS NULL OR sa.currency != ? ) AND
(t.dstamount IS NULL OR da.currency != ?) AND
t.currency = currency.name;
");
var_dump($db->errorInfo());
and this gives the following error info
array(3) {
[0]=>
string(5) "HY000"
[1]=>
int(1)
[2]=>
string(35) "parameters are not allowed in views"
}
All the parameters are to be bound to the same value, the 3 letter name
of a default currency which will be passed in via a $_POST variable for
this particular Ajax called routine.
I could just use PHP to merge in the variable into the string, but I
have been using prepare and bindValue to prevent SQL injection attacks
so I am a little nervous of breaking this rule.
Is there a particular reason for this restriction (if it is indeed an
SQLite one).
--
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for project: Chat
On 24/08/10 17:21, Artur Reilin wrote: > I thinking about coding a chat with sqlite. You know the most hosts not > allow you to use chats or something that way, because they write and read > to often data from mysql database. > > So my thought is to make a chat which use forums software mysql tables for > user data, but writes and read chat text from sqlite database. As the chat > content get pruned after some time, the database can't grow big. So the > insert and selects should be fast. > I have already made a chat that uses SQLite. Its available to clone from my git repository (licenced under the GPL). See http://www.chandlerfamily.org.uk/content/software To clone the repository you need git clone git://www.chandlerfamily.org.uk/mbchat.git This originally started life as an mysql database version, but using it on the web site I wrote it for (http://www.melindasbackups.com) it ran out of steam when 20 people were using it (as it had to poll the database every 2 seconds per user to get a sensible performance). I went through some intermediate stage of using named pipes and sqlite before landing on the current version that forks a chat server for the time that anyone is in chat. I have also added optional security controls so that its possible to encrypt chat messages. It has multiple rooms which are added via adding them to the database, and various permissions and colours for users. (users see some rooms as public, others are moderated, and others they see only when they are members of a specific group - we used them as committee rooms). There is a useable demo here http://chat.hartley-consultants.com/ which is stand alone. The code can also link to an SMF forum and derive username from the login identity of the user on the forum. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PHP5-FPM] Sqlite3 or pdo_sqlite?
On 24/07/10 15:41, Alan Chandler wrote: > On 24/07/10 14:18, J. King wrote: > >> PDO_sqlite3 also does have the advantage of being available by default >> since PHP 5.0.0, whereas sqlite3 is only available by default since PHP >> 5.3.0. I'm aware of no other advantages to using PDO, and from what I've >> read it's on the slow side. >> > > Right now its the only php library that calls (or enables a call) to > sqlite_busy_timeout. > > There is a patch which will make it into php 5.3.3 when it is released > "very shortly" that does provide for that call. > > This makes the sqlite3 pretty bad for applications (such as the typical > web site) where there might be some locking issues. > > I meant that right now PDO:: is the only library that enables the call. Sqlite3 will get it shortly -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PHP5-FPM] Sqlite3 or pdo_sqlite?
On 24/07/10 14:18, J. King wrote: > PDO_sqlite3 also does have the advantage of being available by default > since PHP 5.0.0, whereas sqlite3 is only available by default since PHP > 5.3.0. I'm aware of no other advantages to using PDO, and from what I've > read it's on the slow side. > Right now its the only php library that calls (or enables a call) to sqlite_busy_timeout. There is a patch which will make it into php 5.3.3 when it is released "very shortly" that does provide for that call. This makes the sqlite3 pretty bad for applications (such as the typical web site) where there might be some locking issues. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
On 22/07/10 23:38, peterwinson1 wrote: > > Hello, > > I have a some what complex question about UPDATE. I have the following > table > > table1 (KEY, COL1) > > 0, 1 > 1, 2 > 2, 3 > 3, 4 > > What I would like to do is to UPDATE COL1 by subtracting the COL1 value > where KEY = 0 from the COL1 value of the current row so that the result > would be. > > 0, 0 > 1, 1 > 2, 2 > 3, 3 > > Can this be done in SQL? It does not have to be one UPDATE/SELECT > statement. > > Thank you > pw > > UPDATE table1 SET COL1 = (COL1 - (SELECT COL1 FROM table1 WHERE key = 0)); -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Couple of questions about WAL
On 22/07/10 17:14, Dan Kennedy wrote: > > When in WAL mode, clients use file-locks to implement a kind of > robust (crash-proof) reference counting for each database file. > When a client disconnects, if it is the only client connected to > that database file, it automatically runs a checkpoint and > then deletes the *-wal file. > Aah - this is an important point which I didn't get from the docs. I thought the wal was only cleared after the 1000 page watermark had been reached. > > So one thing to bear in mind when using WAL mode is always to > call sqlite3_close() on all database handles before process > exit. Otherwise you may leave a big *-wal file in the file-system > which will need to be traversed by the next client that connects > to the database. Thanks - that is an important point. I don't do that at the moment. I wrote >> 2) If the WAL is not synced back to the database (because the 1000 >> pages >> have yet to be created), is it still in non volatile storage, synced >> at >> the end of the last commit. In other words, if the server happens to >> get shut down and rebooted, will the WAL still be there and continue >> to >> function from the last committed transaction. (And, given question 1 >> the WAL index will just be rebuilt). > > Yes and yes. Assuming you are using synchronous=FULL. If using > synchronous=NORMAL then the contents of the WAL may or may not > have made it to persistent media when the crash occured. In this > case the checksums in the WAL are used to recover as many > transactions as possible. I am not so worries about a system crash killing a transaction in the middle as an otherwise quiescent system being shut down normally. However, as you say, the last closed database connection clears the WAL anyway, so I don't think this matters >> 4) Is the escalation of locks process process still in place. In >> particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN >> EXCLUSIVE the same? (My current approach is that for web access that >> does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, >> for >> one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an >> application level check (via SELECT) on a version field on the >> database >> to ensure its the same as when I originally read it, ROLLBACK if it >> isn't, but if it is proceed with the INSERTS and UPDATES and finally >> COMMIT). > > In WAL mode, "BEGIN IMMEDIATE" and "BEGIN EXCLUSIVE" do the same > thing - open a write transaction. In both cases readers are not > affected (different from rollback mode - in rollback mode a > "BEGIN EXCLUSIVE" would lock out all readers). I have a worry about consistency of view rather than whether or not a reader is locked out. If I do a BEGIN, SELECT1 and at that point a writer does BEGIN IMMEDIATE, SELECT3, UPDATE, COMMIT, and then I continue with SELECT2 COMMIT, will SELECT1 and SELECT2 have a consistent view of the database unaffected by the UPDATE in the middle. In other words, is the Readers view of how far up the WAL it is allowed to look get controlled by the BEGIN ... COMMIT bracket rather than just the individual SELECTS it is performing. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Couple of questions about WAL
I have been reading about WAL, and there are a few questions I would like to ask. 1) I am slightly confused about readers building the WAL index. It says way down the page quote: Using an ordinary disk file to provide shared memory has the disadvantage that it might actually do unnecessary disk I/O by writing the shared memory to disk. However, the developers do not think this is a major concern since the wal-index rarely exceeds 32 KiB in size and is never synced. Furthermore, the wal-index backing file is deleted when the last database connection disconnects, which often prevents any real disk I/O from ever happening. /quote In a situation where I have a web application (php based) in essence each request makes a database connection, does a couple of queries, and exits (thereby closing the connection). Unless my site gets really loaded (which in the application I am thinking of porting over to SQLite doesn't happen) it is quite frequent that there are no open database connections. Does this mean that every web access has to rebuild the index just to perform a query? is this a long process? 2) If the WAL is not synced back to the database (because the 1000 pages have yet to be created), is it still in non volatile storage, synced at the end of the last commit. In other words, if the server happens to get shut down and rebooted, will the WAL still be there and continue to function from the last committed transaction. (And, given question 1 the WAL index will just be rebuilt). 3) When the 1000 page (or perhaps a smaller number that might be more appropriate in my case) watermark is passed, does the writer (if not interrupted) completely clear the WAL. 4) Is the escalation of locks process process still in place. In particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN EXCLUSIVE the same? (My current approach is that for web access that does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, for one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an application level check (via SELECT) on a version field on the database to ensure its the same as when I originally read it, ROLLBACK if it isn't, but if it is proceed with the INSERTS and UPDATES and finally COMMIT). -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding how to use locking optimally
driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval. Requires int <http://uk.php.net/manual/en/language.types.integer.php> . which sort of implies that it does). I presume many people have experience of the sort of application pattern I am describing. What approach to managing locking do you take? -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
