[sqlite] System.Data.SQLite Release
Just wondering when the next release of System.Data.SQLite will be available. There's a bug, that was already reported and fixed, in the current release that badly breaks NHibernate / ActiveRecord. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Reserved Words Bug
Thanks for fixing that so quickly. Looking forward to a new release. Patrick Earl On Sat, Jul 9, 2011 at 2:39 PM, Joe Mistachkin sql...@mistachkin.com wrote: Patrick Earl wrote: System.Resources.MissingManifestResourceException was unhandled Message=Could not find any resources appropriate for the specified culture or the neutral culture. Make sure System.Data.SQLite.SR.resources was correctly embedded or linked into assembly System.Data.SQLite at compile time, or that all the satellite assemblies required are loadable and fully signed. This issue appears to be caused by an incorrect resource name in the mixed-mode assembly compiled with VS 2010. The following line in the project file SQLite.Interop.2010.vcxproj is incorrect: LogicalName$(IntDir)System.Data.SQLite.%(Filename).resources/LogicalName It should read: LogicalNameSystem.Data.SQLite.%(Filename).resources/LogicalName This issue has been fixed in: http://system.data.sqlite.org/index.html/ci/55f56ce508 Thanks for pointing out this problem. -- Joe Mistachkin ___ 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] System.Data.SQLite Reserved Words Bug
First I wanted to say that I was so excited to see the 1.0.74 release
with .NET 4, zip files, and SQLite 3.7.7. I've been waiting for .NET
4 support for a long while. Thanks so much. :)
Unfortunately, I was unable to upgrade from 1.0.66 because of the
following problem.
Using this code produces the following exception:
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
SQLiteConnection conn = new SQLiteConnection(Data Source=test.db);
conn.Open();
conn.GetSchema(ReservedWords);
}
}
}
/*
System.Resources.MissingManifestResourceException was unhandled
Message=Could not find any resources appropriate for the specified
culture or the neutral culture. Make sure
System.Data.SQLite.SR.resources was correctly embedded or linked
into assembly System.Data.SQLite at compile time, or that all the
satellite assemblies required are loadable and fully signed.
Source=mscorlib
StackTrace:
at
System.Resources.ManifestBasedResourceGroveler.HandleResourceStreamMissing(String
fileName)
at
System.Resources.ManifestBasedResourceGroveler.GrovelForResourceSet(CultureInfo
culture, Dictionary`2 localResourceSets, Boolean tryParents, Boolean
createIfNotExists, StackCrawlMark stackMark)
at System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo
requestedCulture, Boolean createIfNotExists, Boolean tryParents,
StackCrawlMark stackMark)
at System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo
culture, Boolean createIfNotExists, Boolean tryParents)
at System.Resources.ResourceManager.GetString(String name,
CultureInfo culture)
at System.Data.SQLite.SR.get_Keywords() in
c:\dev\sqlite\dotnet\System.Data.SQLite\SR.Designer.cs:line 87
at System.Data.SQLite.SQLiteConnection.Schema_ReservedWords()
in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line
1239
at System.Data.SQLite.SQLiteConnection.GetSchema(String
collectionName, String[] restrictionValues) in
c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line 1223
at System.Data.SQLite.SQLiteConnection.GetSchema(String
collectionName) in
c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line 1176
at ConsoleApplication4.Program.Main(String[] args) in
c:\temp\projects\ConsoleApplication4\Program.cs:line 15
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly,
String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state, Boolean
ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
*/
Thanks for your help with this.
Patrick Earl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
On Wed, Jun 1, 2011 at 12:32 AM, Roger Binns rog...@rogerbinns.com wrote: It does seem to me that this specific example is wrong. Selects return zero or more rows yet the equality wants exactly one value. I would expect an error rather an implied LIMIT 1 should there be anything other than exactly one row. In some cases you'd only know by executing that subquery how many rows it returns, rather than at prepare time. This is my general feeling as well, but I'm ignoring this since I'm okay with erroneous queries generating ambiguous results. So, in summary, the problem has been with us for 6 years and nobody has cared. And fixing it reduces the battery life on your cellphone by some small amount. Are you *sure* this is something that needs to change? I think it is important to correct, especially as there is no workaround. The fact that there is no straight-forward work-around is the most problematic part. One possible work-around would be to build a tokenizer that re-extracts all the SQLite parameters so their values can be verified. Another possibility is to completely ignore missing named parameters, leading to the inability to detect errors for the user. Understanding the query would be more of a leap, so I don't think it's a reasonable solution to attempt to remove the extra parameter. From these, the best solution seems to be re-tokenizing the sql in the provider. Is there another work-around? It seems that implementing this at the database level would be the most efficient approach. Obviously re-tokenizing all the SQL would be expensive. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
So, to move forward on this bug a decision needs to be made at what level it will be handled. 1. At the SQLite level. 2. At the provider level (ex. System.Data.SQLite, Python providers, etc.) 3. At the user level (ex. NHibernate, Entity Framework, User Applications, etc.) Doing it in #3 would involve figuring out which parameters would be removed and not including those, a very difficult option. Doing it in #2 would involve adding something that does manual parameter parsing and validation (such as the parameter tokenizer). Doing it in #1 would involve things that I don't understand, though it would simultaneously correct the problems with all providers mentioned on this thread. I'm not clear on who is maintaining System.Data.SQLite, but I would certainly be happy to see some progress towards the resolution of this issue. Since I don't believe #3 is a feasible option (nor even the correct place to abstract away the SQLite oddity), the solution should be #1 or #2. For #2 there is a performance hit on every query performed using such a provider (.NET, Python, etc.). The queries need to be parsed by the provider to determine validity. If the SQLite syntax changes, these providers need to be updated. For #1, it sounds like there is a minor performance penalty, but perhaps it can be implemented in a way where the effects are minimal. #1 has the advantage that it may be possible to avoid any sort of significant performance hit unless an optimized parameter is encountered. #2 doesn't have this luxury since it doesn't know when a parameter might be optimized out. What would the SQLite team suggest to help progress the fix for this? If it's at the System.Data.SQLite level, I would be willing to help contribute a fix. If that were the case, I would hope that the SQLite syntax could be parsed by a regex for performance reasons. Patrick Earl On Wed, Jun 1, 2011 at 10:36 AM, Stephan Beal sgb...@googlemail.com wrote: On Wed, Jun 1, 2011 at 6:24 PM, Patrick Earl pate...@patearl.net wrote: From these, the best solution seems to be re-tokenizing the sql in the provider. Is there another work-around? It seems that implementing this at the database level would be the most efficient approach. Obviously re-tokenizing all the SQL would be expensive. Actually... if you just want to tokenize the SQL for the parameters, as opposed to checking the validity of the SQL itself, it is not all that difficult to do. i recently did just that to add named parameter support to the MySQL back-end of a db access abstraction API. MySQLv5 doesn't support named parameters natively, so i wrote a relatively simple tokenizer which can fish them out and another routine which converts named parameters to question marks so that we can feed the data to mysql (but can also remember enough info to map the named param positions to qmark positions): http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/b5ba5aa115c73b63179456d1fed7846a11878c05 Search that file for: cpdo_find_next_named_param cpdo_named_params_to_qmarks The code for those functions is public domain and should be generic enough to extract for your own use (almost without modification - i think only the return code symbols would need to be replaced). The docs are in the header file: http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/6749b977687afa081f6b5b3e7fc3b19cd62bb70a That code has worked for me very well so far, and i haven't yet had any mis-parsing except on code which was itself not legal SQL (i.e. it doesn't seem to create any new problems where there isn't already a problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ 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] Bug in sqlite3_bind_parameter_name
That's awesome. Thanks so much. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Two Requests for System.Data.SQLite
1. Please release a zipped version of the x86 and x64 binaries so we don't have to install the package, copy the files, then uninstall it. 2. Please release a version built against .NET 4 so the legacy runtime flag can be avoided. Thanks for considering these things. They would certainly make my life easier. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
The generated limit parameter does have a value of 1, so it's a valid query. It's SQLite that has taken this valid query with a valid parameter value of 1 and has exposed its internal implementation details by removing it and causing additional work-arounds in parameter binding. It's possible the work-arounds aren't major... I haven't had time to look at the picture in depth. Patrick Earl On Tue, May 31, 2011 at 12:47 PM, Jan Hudec b...@ucw.cz wrote: On Tue, May 31, 2011 at 08:00:40 -0400, Richard Hipp wrote: On Mon, May 30, 2011 at 11:27 PM, Patrick Earl pate...@patearl.net wrote: SELECT this_.studentId as studentId143_0_, this_.Name as Name143_0_, this_.address_city as address3_143_0_, this_.address_state as address4_143_0_, this_.preferredCourseCode as preferre5_143_0_ FROM Student this_ WHERE this_.Name = ( SELECT this_0_.Name as y0_ FROM Student this_0_ WHERE this_0_.studentId = @p0 ORDER BY this_0_.Name asc limit @p1) [...] The LIMIT in a scalar subquery is always ignored. A scalar subquery operates with a LIMIT of 1 regardless of any LIMIT that you might specify. It's quite obvious that any other limit in a scalar subquery does not make sense, not only in SQLite, but in any SQL database, since only one value will ever be used. Which leads me to wonder what causes it to be generated (you don't want to tell me the weird names are invented manually, right?) and whether - it should have not generated the limit, or - it should have used in instead of = (thus making it list query which can meaningfuly have limit). -- Jan 'Bulb' Hudec b...@ucw.cz ___ 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] Bug in sqlite3_bind_parameter_name
I just had an amusing realization... if SQLite itself won't change,
then the problem falls on the shoulders of System.Data.SQLite, which
is incidentally also handled by this mailing list. It's ultimately
this code that is causing the problem because of the case mentioned
here:
private void BindParameter(int index, SQLiteParameter param)
{
if (param == null)
throw new SQLiteException((int)SQLiteErrorCode.Error,
Insufficient parameters supplied to the command);
This exception is being thrown because SQLite is eating one of the
parameters as Richard mentioned.
I haven't analyzed the consequences of changing or disabling the check
in some way. If somebody beats me to it, I'd be happy about that too.
:)
Patrick Earl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in sqlite3_bind_parameter_name
Greetings! I have the following query: SELECT this_.studentId as studentId143_0_, this_.Name as Name143_0_, this_.address_city as address3_143_0_, this_.address_state as address4_143_0_, this_.preferredCourseCode as preferre5_143_0_ FROM Student this_ WHERE this_.Name = (SELECT this_0_.Name as y0_ FROM Student this_0_ WHERE this_0_.studentId = @p0 ORDER BY this_0_.Name asc limit @p1) sqlite3_bind_parameter_count returns 2. sqlite3_bind_parameter_name(1) returns @p0 sqlite3_bind_parameter_name(2) returns , not @p1 Perhaps this is related to being a limit in a subquery. Though nobody may care, this is currently preventing the NHibernate test suite from passing. Appreciate your help with this. :) Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lack of decimal support
Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). I would like to encourage the developers to consider adding support for base-10 numbers. This is clearly a very pertinent issue, as even this month there was another thread regarding decimal support. Thanks for your consideration. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite NHibernate
Greetings. I'm a committer for NHibernate who has been working on improving the support for SQLite. I've been able to get most of the over 3000 tests passing on SQLite. Kudos to Richard and the team for producing such an impressive little database. I wanted to share with you the main limitations I found on this journey in the hopes that some day they will no longer be limitations. They are ordered by my view on their importance. 1. Support for a base-10 numeric data type. 2. Support for altering tables (especially the removal or addition of foreign keys). Granted, tables can be updated by turning off foreign key constraints, copying all data, manually checking foreign key consistency, and then turning on foreign key constraints again. Not having the ability to alter tables ultimately leads to a great of complexity in any system that has to deal with updating database schemas. 3. FULL OUTER JOIN support. There are work-arounds, but implementing those as part of NHibernate proved quite complicated, so I opted to wait unless there seems to be extreme demand for it. 4. Some sort of locate function to get the index of substring within another string. I couldn't even find any way to emulate this (aside from user defined functions). 5. Support for operations like = all (subquery), = some (subquery), and = any (subquery). 6. Better support for distributed transactions. I don't pretend to be an expert here, but it seems plausible that SQLite could participate in a transaction across multiple databases. Perhaps implementing two phase commit would help with this. Thanks for your consideration. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of decimal support
I've found the decimal numbers to be most generally useful in narrow ranges. For reference, here are a couple notes on how other databases implement them: MSSQL stores up to 38 digits in 17 bytes, with a specific precision. http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx PostgreSQL is more flexible and supports up to 1000 digits. http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL In order to get a jump on the implementation, I would suggest that it might be possible to use C routines from the PostgreSQL project or some appropriately licensed library. Perhaps an author from a numeric library would be willing to donate their work to the SQLite project. Patrick Earl On Sat, Mar 26, 2011 at 7:43 PM, Simon Slavin slav...@bigfraud.org wrote: On 27 Mar 2011, at 2:39am, Patrick Earl wrote: Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). I would like to encourage the developers to consider adding support for base-10 numbers. This is clearly a very pertinent issue, as even this month there was another thread regarding decimal support. Intersting idea. You will need to develop your own C routines to do calculations with decimals. Do you feel they should be implemented at a fixed length or would you want to be able to use decimal strings of arbitrary lengths ? 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] Lack of decimal support
That is true, but then when you are formulating generic queries within a place such as an ORM like NHibernate, you would need to figure out when to translate the user's 100 into 1. As well, if you multiplied numbers, you'd need to re-scale the result. For example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one wanted to get excessively complicated, they could implement a series of user functions that perform decimal operations using strings and then reformulate queries to replace + with decimal_add(x,y). That said, it'd be so much nicer if there was just native support for base-10 numbers. :) Patrick Earl On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare list@barefeetware.com wrote: On 27/03/2011, at 12:39 PM, Patrick Earl wrote: Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). Can you store all money amounts as integers, as the cents value? That is exact, searchable etc. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ 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] Lack of decimal support
You're right, it doesn't make sens to multiply dollars, but if you're in a context where you don't have significant understanding of the user's query, how do you determine if 1.05 is $1.05 or 105%? I understand that one can custom-code everything for SQLite and get reasonable results in some cases, but please understand that I'm looking for solutions that don't require the framework to understand the user's intentions any more than I want to work with base-10 numbers up to a certain precision/scale. Patrick Earl On Sat, Mar 26, 2011 at 8:43 PM, Gerry Snyder mesmerizer...@gmail.com wrote: Do money values really get multiplied together? What is the meaning of square cents as a unit? Gerry On 3/26/11, Patrick Earl pate...@patearl.net wrote: That is true, but then when you are formulating generic queries within a place such as an ORM like NHibernate, you would need to figure out when to translate the user's 100 into 1. As well, if you multiplied numbers, you'd need to re-scale the result. For example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one wanted to get excessively complicated, they could implement a series of user functions that perform decimal operations using strings and then reformulate queries to replace + with decimal_add(x,y). That said, it'd be so much nicer if there was just native support for base-10 numbers. :) Patrick Earl On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare list@barefeetware.com wrote: On 27/03/2011, at 12:39 PM, Patrick Earl wrote: Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). Can you store all money amounts as integers, as the cents value? That is exact, searchable etc. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ 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 -- Sent from my mobile device ___ 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] Lack of decimal support
If you use a view to return a double, you've lost the exact value you were trying to save by storing the decimal as a text value. If you continue to work with it as an integer, it's exact, but that requires continual awareness of the number of decimal places at any point in time. In essence, you have to build significant numeric infrastructure into your program to emulate the missing numeric infrastructure in SQLite. Patrick Earl On Sat, Mar 26, 2011 at 9:52 PM, BareFeetWare list@barefeetware.com wrote: On 27/03/2011, at 2:09 PM, Patrick Earl wrote: if you're in a context where you don't have significant understanding of the user's query, how do you determine if 1.05 is $1.05 or 105%? Can you give us a bit more background and an example of this? How is the interface for the query represented to the user and what can they enter there to create a query? You can probably do this fairly easily via views which display data in a particular format for the user to see or create a query. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ 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] Lack of decimal support
On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan dar...@darrenduncan.net wrote: You could store your exact precision numbers as a pair of integers representing a numerator/denominator ratio and then have math operators that work on these pairs like they were one number. You would then know at the end how to move the radix point since that was kept track of along with the number. -- Darren Duncan If you did this, you wouldn't be able to compare numbers in the database without resorting to division. If you just specified how many fixed decimal places there were, you could zero-pad strings if you only needed to perform comparison operations. Obviously you'd need to create custom operations, as you suggest, for other math operators. If SQLite can't decide on a base-10 format itself, perhaps the answer lies in enhancing the API to allow for custom type storage and operators. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
Okay, I tried the strategy discussed previously but I'm still having problems. Either I'm not seeing something, or there's a bug in the foreign constraint support. Take a look at the following two execution snippets: sqlite sqlite commit transaction; sqlite sqlite begin transaction; sqlite sqlite DROP TABLE ParkingLotLevel; sqlite DROP TABLE Car; sqlite DROP TABLE ParkingLot; sqlite sqlite Commit transaction; Error: foreign key constraint failed And now, we switch Car and ParkingLotLevel... sqlite sqlite commit transaction; sqlite sqlite begin transaction; sqlite sqlite DROP TABLE Car; sqlite DROP TABLE ParkingLotLevel; sqlite DROP TABLE ParkingLot; sqlite sqlite Commit transaction; sqlite No error! Since the constraints are deferred, the order of the table drops shouldn't matter, but it clearly does. I've included a full failing example at the following link. Beware that it's quite long and full of boring automated code. The interesting part is right at the end and corresponds with what I wrote above. http://patearl.net/files/broken4.txt This problem (or some variation thereof) has been making me crazy for the past three days. So nice to finally have a reasonable looking test case. :) Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign constraints and table recreation
Greetings all. I've been having much trouble with foreign constraints and updating the table schema. I used to just recreate the table and rename, but now that I wish to enable foreign keys on my connections, it seems that I can no longer do this. The following statements produce a foreign constraint violation, which seems to contradict the current documentation, which says that dropping the table with deferred constraints will only produce a violation if not corrected by the end of the transaction. PRAGMA foreign_keys = ON; CREATE TABLE ParkingLot (Id int NOT NULL PRIMARY KEY); CREATE TABLE Car (Id int NOT NULL PRIMARY KEY, ParkingLotId int NOT NULL REFERENCES ParkingLot (Id) DEFERRABLE INITIALLY DEFERRED); INSERT INTO ParkingLot (Id) VALUES (1); INSERT INTO Car (Id, ParkingLotId) VALUES (2, 1); BEGIN TRANSACTION; CREATE TABLE ParkingLotTemp (Id int NOT NULL PRIMARY KEY); INSERT INTO ParkingLotTemp (Id) SELECT Id FROM ParkingLot; DROP TABLE ParkingLot; ALTER TABLE ParkingLotTemp RENAME TO ParkingLot; COMMIT TRANSACTION; Even though at the end of the transaction you can select and find the appropriate rows in the car and parking lot tables, committing the transaction causes a foreign constraint violation. I used to be able to do all my DDL inside of transactions to ensure sanity was preserved, but I can't figure out how to make that work any longer. It seems I'd have to drop out of a transaction, turn off the foreign keys, do the DDL, then turn the foreign keys back on. I'm not sure if the above behavior was by design or not, but it's not even ideal using deferred constraints and drop/rename to put the table back. The following potential features may also help contribute to a solution. 1. Allow ALTER TABLE to add/remove foreign constraints. 2. Allow foreign constraints to be deferred for the duration of a transaction. (ie. SET CONSTRAINTS) 3. Allow foreign keys to be disabled / enabled within transactions using pragma. 4. Allow tables to be renamed without causing foreign references to that table to rename. You could then rename your original table, create a new one in its place, and put all the original data back. In any case, I'm looking forward to some sort of improvement to the situation. Maybe I'm missing something, but I've spent my weekend banging my head against this one. Best regards, Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
Thanks Simon. I believe you're correct in that I can recreate all dependant tables. I had attempted this trick earlier, but was doing so in the context of immediate mode constraints, and that made the re-insertion of data and dropping of tables exceptionally complicated in some cases (such as circular references between tables). So to summarize, the strategy for modifying a table with foreign constraints enabled is to: 1. Find all direct and indirect dependants of the table being modified. 2. Create temporary tables for all of these. 3. Copy the data from the main tables into these temporary tables. 3a. If no circular dependencies, do a topological sort on the tables to get the correct insertion order. 3b. If circular dependencies, either use deferred constraints or come up with a sophisticated algorithm to reinsert the original data (needs to take into account not null columns with circular references present). 4. Drop all the original tables, again with similar steps to 3a and 3b. 5. Rename all the temporary tables to their original names. I will try this algorithm today and report back if I fail. Since I don't have time to imagine an algorithm to delete/insert/update rows in an order that doesn't break constraints, I've ended up using deferred constraints (undesirable in my case) just to support table modification. It would be great if the kind of complexity above was somehow encapsulated in the database engine, instead of having users work around it with non-trivial steps. As a side note, the above algorithm isn't likely to be particularly performant on databases with significant data present. In the general case of multiple individual modifications to tables (such as in the context of a database change manangement framework), the amount of work being done by the DB to modify the table is quite time consuming. Thanks for your help with this. Patrick Earl On Mon, May 10, 2010 at 5:18 AM, Simon Slavin slav...@bigfraud.org wrote: On 10 May 2010, at 7:34am, Patrick Earl wrote: PRAGMA foreign_keys = ON; CREATE TABLE ParkingLot (Id int NOT NULL PRIMARY KEY); CREATE TABLE Car (Id int NOT NULL PRIMARY KEY, ParkingLotId int NOT NULL REFERENCES ParkingLot (Id) DEFERRABLE INITIALLY DEFERRED); INSERT INTO ParkingLot (Id) VALUES (1); INSERT INTO Car (Id, ParkingLotId) VALUES (2, 1); BEGIN TRANSACTION; CREATE TABLE ParkingLotTemp (Id int NOT NULL PRIMARY KEY); INSERT INTO ParkingLotTemp (Id) SELECT Id FROM ParkingLot; DROP TABLE ParkingLot; ALTER TABLE ParkingLotTemp RENAME TO ParkingLot; COMMIT TRANSACTION; Even though at the end of the transaction you can select and find the appropriate rows in the car and parking lot tables, committing the transaction causes a foreign constraint violation. I'm not sure how you expected this to work. You declare ParkingLot as a parent table of Car, but then you DROP TABLE ParkingLot, leaving Car an orphan. The only legitimate way to do this is to DROP TABLE Car first, or to remove the foreign key constraint from it (which SQLite doesn't let you do). The fact that you rename another table 'ParkingLot' later has nothing to do with your constraint: the constraint is linked to the table, not to the table's name. If you're going to make a temporary copy of ParkingLot, then make a temporary copy of Car too: CREATE TABLE CarTemp (Id int NOT NULL PRIMARY KEY, ParkingLotId int NOT NULL REFERENCES ParkingLotTemp (Id) DEFERRABLE INITIALLY DEFERRED); INSERT INTO CarTemp (Id) SELECT Id FROM Car; Then you can drop both original tables and rename both 'temp' tables. However, I don't see why you're doing any of this rather than just adding and removing rows from each table as you need. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
On Mon, May 10, 2010 at 10:58 AM, Simon Slavin slav...@bigfraud.org wrote: It should not be possible to have circular dependencies. Because you somehow got the data in in the first place, and /that/ wouldn't have been possible had you had circular dependencies. Part of normalising your data structure includes making sure that you haven't duplicated data. I meant foreign keys that cause tables to have circular relationships. For example, a customer might have a list of credit cards (the credit cards table has a customer id) and the customer has a default credit card (the customer table has a credit card id). I realize you could make a third table to store the default credit card relationship, but as you observed, I'm looking at the general case. I do note that you appear to be trying to solve an extremely general case, as if you, the programmer, have no idea why your schema is the way it is. I have to warn you that if you're going to solve the general case, you are going to run into situations which are not solvable without considering individual rows of a table. Indeed, hence why it's so complicated without using deferred constraints. Unfortunately, enabling deferred constraints leads to later detection of errors during typical development. With immediate constraints, even using null in fields temporarily might not solve the issue, since there may be not-null constraints to deal with. In any case, suffice to say that it is indeed quite complicated. Getting back to one of the points that started this conversation, the complex nature of operations needed to transactionally modify tables with foreign key integrity preservation suggests to me that this would be something the database engine could provide a helping hand with. It wouldn't necessarily need to be full support for alter table, other options presented previously would also help. But this gets back to an earlier point of mine: why go through any of this performance at all ? Why do you need to create temporary copies of databases only to originally delete and replace the originals ? The simplified example I provided had no changes to the tables, but in the real scenario, at least one of the tables will need some sort of modification. Thanks for the detailed replies. I appreciate your insight. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
