SQLite3 constraints

[solnic]

Would anyone be interested in SQLite3 support for constraints? I have some code written using triggers which I could clean up and submit if there was sufficient interest.

---

Created by Simon Harris - 2009-09-21 14:02:32 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1053

[solnic]

Yes, I would really like to see something like that. Ideally I’d like to see it if it didn’t result in any failing specs.

The current idea with dm-constraints is we set up software level constraints, that if working perfectly should catch all attempts to destroy a resource when it has dependents. The DB level constraints are more of a safety net, as well as communicating intent, and also providing a backup for if (when) the DB becomes an integration point.

by Dan Kubb (dkubb)

[solnic]

I’d just like to say that I would love to see this functionality merged into dm-constraints. I believe newer versions of SQLite 3 even support FK constraints too.

If some of this functionality included CHECK constraints I would be doubly excited to see it merged in :)

by Dan Kubb (dkubb)

[r3n4ud]

@solnic and @dkubb : any news or plans on the sqlite3 constraints support? I would really to get this functionality working since I would like to port an ugly sqlite3 generation scripts to datamapper but I really need the db-level constraints since the db is used in an un-ruby ecosystem…

I could help to get the things done.

[dkubb]

@nibua-r if you wanted to take a stab at it, there's a good chance it might represent a few minutes to make the code changes, and perhaps an hour or two of testing for someone sufficiently motivated.

The basic approach would be to modify the SQLite adapter within dm-constraints so it returns true for versions of SQlite3 that provide FK constraint support. See dm-migrations for the methods we have built-in for getting the DB version.

[r3n4ud]

@dkubb I have delayed my response to take (a little) time to check the code and I'm sorry for that.
I'll report here as soon as I can take some time to get a first version.

[r3n4ud]

@dkubb Wow, I forgot about that issue… Now, the need is back again!
After a quick glance on the dm-constraints source code (i.e. DataObjectsAdapter), all I could say is that the basic approach would fail. SQLite doesn't support ALTER TABLE … ADD CONSTRAINT (SQL Features That SQLite Does Not Implement) and as a consequence, it is not possible to add the constraints after the table creation, thus to use the actual methods.

Is there a way to append the relationship constraints to the CREATE TABLE statement?

cc @solnic @snusnu