Thank you for your response Simon. On Sat, Mar 2, 2013 at 3:51 PM, Simon Slavin <slav...@bigfraud.org> wrote: > SQLite isn't at its root a DDL. It's a C API, fully documented here: > > <http://www.sqlite.org/capi3ref.html> > > That's SQLite and that's its full documentation. As designed, to add SQLite > facilities to your programming project you simply include the C source code > (.c and .h files) that implements those commands into your project. That's > the 'amalgamation source' you see us talking about on this list.
I am familiar with the C API. I have successfully built the amalgamation with Embarcadero CBuilder, translated the C API to Object Pascal and linked the .obj file that CBuilder output to Delphi programs with success, This way I don't need to distribute the SQLite dll, the C source is directly linked in Delphi. And I have written a thin Object Pascal object layer on top of the C API which allows me to use SQLite in Delphi programs. > Any DDL you see is someone trying to make some or all of those things > accessible as a DDL. People who make DDLs for SQLite can put as many or as > few of these things into their DDL as they like, along with as many things > they made up themselves as they like. There is more than one DDL and for all > we know they might all be different to one-another. If you want to know > about a particular DDL, go find the documentation for that DDL. But... if I understand correctly adding some DDL extensions to SQLite would mean modifying its SQL parser so that it can parse e.g. the expression "ALTER TABLE tab DROP COLUMN col" and its engine in order to be able to execute it. Wouldn't that mean modifying the source? I would prefer not to do that for various reasons, the main one being that I would have to learn C (I can read it but never coded with it) and then make myself very familiar with the code of SQLite itself in order to have it execute the introduced DDL, which is far beyond my scope. On the other hand, if you mean executing the "introduced" DDL in the context of my own application, read ahead. > Having said that, the GUI admin tools and DDLs which implement something like > adding/removing constraints usually doesn't include new low-level C code to > fiddle with the database like the source code does. They normally does it by > using various API calls in a way that looks like you're doing just one call. > For instance, to add a new constraint to an existing table ... > > ALTER TABLE myTable ADD CONSTRAINT (capacity > 0) <-- imaginary command for > discussion only > > they do this: > > 1. Get the old table definition as a text string. > 2. Add the constraint to the definition. > 3. Create a new table with this new definition. > 4. Copy all the data in the old table into the new table. > 5. Drop the old table. > 6. Rename the new table with the old table's name. > > You can do all the above yourself using just SQLite commands which are > already in the API. All they've done is specify one command in the DDL which > does them all for just one call. > > (There are complications which mean that you cannot do exactly the above and > expect everything to work perfectly. I'm using it just as a demonstration.) I am already doing that but it is very difficult to do with FKs. Even if you disable FK enforcement for the duration of the above sequence of actions one may end up with invalid FKs, for instance using renamed or dropped columns. Even worse the consequences will not be evident until the first time the FK is enforced as part of a DML statement, where you get a pretty generic error (constraint failed). This means that the database schema is in an inconsistent state. So in order to do a structural change properly one would have to: -Disable FK enforcement to avoid losing detail records when the table is dropped. -Find FKs that are affected by the coming change. -Perform the change doing the sequence you illustrated. -Repeat the sequence for any tables linked with FKs to fix or drop them since it is impossible to alter a table's FK without recreating the table. -Reenable FKs. Ok, it can be done but it is tedious at best, hence my original question. I thought that some tool writer had a better solution they wouldn't mind sharing. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
