Alexey Pechnikov wrote on 15/12/2009 15:15:42: >> 3. ENFORCE constraint for table columns, for example: CREATE TABLE t(i >> enforce integer, j enforce text). This optional constraint enforces type >> checking so that an entry must match the column type (i.e. integer, real, >> numeric, text, blob). Note: this can be done using a CHECK constraint,
>> but here is done internally using some additional custom VDBE opcodes to >> give greater efficiency. > > Great! Is the error message informative enough? For a table such as CREATE TABLE t (value enforce text); doing INSERT INTO t VALUES(100) will return "t.value must be type 'text'" as an error. >> 4. Optional READONLY option for the ATTACH DATABASE command: the attached >> database can then be opened "readonly" even when the main database is not. > > It's useful in most common situations. Dos this prevent creating no-exists > files for attached databases? Is the error message informative enough? Trying to attach a non-existent database as "readonly" will return the error "unable to open database file". >> Shortly I hope also to have completed a patch to allow a limited form of >> "stored procedures" where the sqlite_prepare function can be used to >> create a VDBE program with support for loops, rudimentary support for >> if/then/else style constructions and generation of output rows that can be >> then executed as and when. > > Do you have any description? I did see similar for SQLite version 2 only. Yes, I also heard of something under v2 also but it was easier for me to start from scratch since otherwise I would have just had to work out how much the changes between v2 and v3.6 might cause problems! As a brief description, this patch will provide a way of generating mini VDBE programs. I've added the following SQL-like instructions: FOR var IN (a, b, c) LOOP ... END LOOP FOR var BETWEEN a AND b LOOP ... END LOOP WHILE expr LOOP ... END LOOP A statement such as EXIT or EXIT WHEN expr can then be used within the loop to exit at any point. A simple LOOP ... END LOOP will loop indefinitely. At the moment using something like... CASE [expr] WHEN expr THEN ... WHEN expr THEN ... ELSE ... END is sufficing for select/case and if/then/else style constructs and kept things simple. SELECT INTO vars FROM ... can be used to read from tables into variables (at the moment this is the only way to create and modify variables, although I hope to change this depending on the complexity to do so) and SELECT ... can be used to output values to the outside world! It should also be possible to use the parameter tokens (e.g. ?) and sqlite3_bind command, but I've got some bugs in it at the moment. Also, at the moment I'm generating the VDBE code through a rather back-handed way since I'm having problems going through sqlite3_prepare which likes to split SQL up by semi-colons! So, all in all there's still quite some work to do before this is ready. At the moment there is no support for something like "CREATE STORED PROCEDURE" or whatever, although I can't see a reason why this couldn't be done at a later date but this is not a priority for me presently. So, by extension it is not possible to execute any of this via the sqlite shell, or via a trigger, etc. It can only be used at present through the C api, etc. By the way, if you have any suggestions of other things to add in, I'll happily hear them! > Please to publish patches for original source tree. The amalgamation can be > easy builded from it. If you don't have web server please send me to email > and I will place the pathes on my server. Yes, I'll be happy with that. What I'll do, though, is split them up into the individual patches rather than post them to you as one big patch -- then people can pick-and-choose what they want. I should have some time tomorrow to do that, but if you don't hear from me, then feel free to pester me!!! Cheers Will _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
