I will do.
I have deployed the Version 1.9.9 of SQLite Control in 64 bit here:
SQLite Control
This now has the ability of creating Xojo code to handle the databases.
If anyone has been following the SQL Formatting thread then you might be interested to know that I have now incorporated within SQLite Control the latest formatter.
In addition, the app now handles the complete modification of a table structure. The only available instructions in SQLite are table rename and adding a column. SQLite Control now has the ability of changing the field types, adding columns, renaming columns, setting/unsetting default values etc.
Latest version here:
SQLite Control
As always, all comments/questions/complaints etc. are gratefully received. Please note that I take no responsibility for your database!
Question…
What do you do about situations where your complete “ALTER TABLE” method causes INDEX, VIEW or TRIGGER to become invalid?
Especially Index and Trigger, if you drop or rename a column that is used in either of them, they cannot be reapplied (since I assume you are creating a NEW TABLE, moving the data, dropping the original table (which would disconnect the INDEX and TRIGGER), and renaming the “new table” to the original name
These are all questions I am trying to resolve for MY manager 
- drop the index/trigger and tell the user… “Hey figure out how to put them back yourself”
- analyze the SQL that created them and determine if they really are AFFECTED, and if not just reapply
- go the distance, and analyze the SQL and see if it can be programmaticly modified to remain valid (which is can’t if you dropped columns)
Dave
That’s a very good question.
At the moment the app does not undertake an integrity check. I will look at these issues tomorrow and report back.
Simon.
Ok, the results are in.
If I remove a field in a table then:
Index
If the field is referenced in an Index then the Index is removed by SQLite.
Trigger
If the field is referenced in a Trigger then the Trigger is removed by SQLite.
View
If the field is referenced in a View then the View is not changed by SQLite. If you operate a select from the view then you get an SQLite error message advising you that the field does not exist.
The Index and/or Trigger will be removed regardless… assuming you are building a “temp” table as the destination to be renamed.
- Assume Alter Table FRED
- create a new table “TEMP” with the altered structure
- copy data from FRED to TEMP based on reordered and/or renamed columns
- DROP FRED (all INDEX and TRIGGER are dropped automatically, regardless of field references)
- rename TEMP to FRED … but now the new “FRED” has neither INDEX or TRIGGERS that previously existed.
- So now what do you do?
- — tell the user? too bad? so sad?
- — attempt to automagicially recreate them?
the first is “easy”, the second is more difficult, but seems to be the “right thing to do” (or attempt)
here is the process that SQLite themselves documented
-
If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.
-
Start a transaction.
-
Remember the format of all indexes and triggers associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name=‘X’.
-
Use CREATE TABLE to construct a new table “new_X” that is in the desired revised format of table X. Make sure that the name “new_X” does not collide with any existing table name, of course.
-
Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT … FROM X.
-
Drop the old table X: DROP TABLE X.
-
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
-
Use CREATE INDEX and CREATE TRIGGER to reconstruct indexes and triggers associated with table X. Perhaps use the old format of the triggers and indexes saved from step 3 above as a guide, making changes as appropriate for the alteration.
-
If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.
-
If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.
-
Commit the transaction started in step 2.
-
If foreign keys constraints were originally enabled, reenable them now.
The procedure above is completely general and will work even if the schema change causes the information stored in the table to change. So the full procedure above is appropriate for dropping a column, changing the order of columns, adding or removing a UNIQUE constraint or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints, or changing the datatype for a column
SQLiteMAN just silently drops them…
SQLiteMANAGER (SQLabs) leaves them, and then things bomb due to integrity issues.
I’d rather the user KNEW what was going to happen before it did
Yes, I have read the recommended action from the SQLite documentation. In essence this is what you would do if you were hand coding the SQL.
The difficulty is in automating that process. I don’t think that the Indexes would be that much of a problem as the SQL for those is really easy. The issue, I think, is with the triggers. I will think about this over the next couple of days as I have some real work to do in the meantime!
This is fascinating to me and is occupying the majority of my time. I know that I will solve it, but when and how is difficult right now!
My app already parses INDEX values, so I could tell what index would either need a name changed, or column dropped
my Trigger Create doesn’t work quite yes, but I think something similar would be very doable.
If a column name is changed, then take the Index definition alter the column name in it, and re-execute… if columns(dropped) that is a bit different.
But this also brings up renaming the TABLE itself… Indexes are fine there, but a TRIGGER or VIEW might be affected…
These are all things I had not considered before… and I want my process to be as transparent to the user as possible
FYI… if you RENAME a table (doing nothing else to it)… SQLite automatically fixes all Index references for you… however Views and Triggers that contain the ORIGINAL name have to be dealt with in another manner
I have the INDEX part covered.
i have now done the basic part of TRIGGER after modifying the table but I have just seen an error where the trigger is based upon a filename that has now changed. I will fix that tomorrow. At that point I think I have all the INDEX and TRIGGER issues solved.
I will then move on to looking at any Views.
Thanks for the heads up, Dave, much appreciated.
Simon… would have emailed this to you, but my email isn’t working 
Your app doesn’t allow creation of Triggers on VIEWS (it only lists tables in the drop down)…
But “Instead OF” and “Update OF” can only be used on a View, (and not on a table)
I cannot make this example (from the SQLite Website) work, in your app (because it is a view, my App and SQLite Man both complain (error new “END”)
CREATE TABLE customer(
cust_id INTEGER PRIMARY KEY,
cust_name TEXT,
cust_addr TEXT
);
CREATE VIEW customer_address AS
SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng
INSTEAD OF UPDATE OF cust_addr ON customer_address
BEGIN
UPDATE customer SET cust_addr=NEW.cust_addr
WHERE cust_id=NEW.cust_id;
END;creating the table and the view work, but all the test platforms have barfed on the Trigger
with either “Near ‘END’” or “Near cust_addr”
and you really need to use TEXTAREA for the BEGIN/END query area, and give the user the chance to FIX an issue… instead it just erases everything, and closes the dialog, forcing you to start from scratch again.
This doesn’t seem to work anywhere either (another web example)
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
not sure why creating a trigger is so difficult
strange… now all of a sudden it works in my app … go figure
Simon… you listbox (to display table contents) does not handle text fields that contain EndofLine (it just shows the first line)…
but then mine does the same thing … need to write a listbox subclass with varying rowheights I guess… dang
just curious how you can do that with a listbox subclass ? don’t you need a canvas ?
[quote=325695:@Dave S]Simon… would have emailed this to you, but my email isn’t working 
Your app doesn’t allow creation of Triggers on VIEWS (it only lists tables in the drop down)…
But “Instead OF” and “Update OF” can only be used on a View, (and not on a table)
I cannot make this example (from the SQLite Website) work, in your app (because it is a view, my App and SQLite Man both complain (error new “END”)
CREATE TABLE customer(
cust_id INTEGER PRIMARY KEY,
cust_name TEXT,
cust_addr TEXT
);
CREATE VIEW customer_address AS
SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng
INSTEAD OF UPDATE OF cust_addr ON customer_address
BEGIN
UPDATE customer SET cust_addr=NEW.cust_addr
WHERE cust_id=NEW.cust_id;
END;creating the table and the view work, but all the test platforms have barfed on the Trigger
with either “Near ‘END’” or “Near cust_addr”
and you really need to use TEXTAREA for the BEGIN/END query area, and give the user the chance to FIX an issue… instead it just erases everything, and closes the dialog, forcing you to start from scratch again.
This doesn’t seem to work anywhere either (another web example)
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
not sure why creating a trigger is so difficult :([/quote]
All of this works in my app.
[quote=325718:@Dave S]Simon… you listbox (to display table contents) does not handle text fields that contain EndofLine (it just shows the first line)…
but then mine does the same thing … need to write a listbox subclass with varying rowheights I guess… dang[/quote]
Yes. This is a limitation of the listbox. If you export the table to an Excel file then the whole contents are there. I decided to not change anything for my app as it is not an application for handling a specific database but a generic app that displays what it can for a field/column. I do not consider that an unreasonable compromise for a generic db manager.
Going back to the TRIGGER issue. I didn’t realise that a trigger can be created for a view and your post made me re-read the SQLite documentation on the syntax. Of course, you are right, but I don’t think that I will implement this at present as the syntax can be used in the SQL tab to hand-code that type of trigger (that works, by the way).
Also, I think I will change the CREATE TRIGGER dialog to allow the editing of the individual lines within the BEGIN … END block. The implementation is a personal preference that I appreciate will not be liked by all but I implemented it this way as I wanted a visualisation of the fact that there can be many individual statements within the BEGIN/END block.
Thanks for all your comments, they help me greatly.
Simon.
I have now incorporated the trigger changes. The latest version can be downloaded here:
SQLite Control
[quote=325718:@Dave S]Simon… you listbox (to display table contents) does not handle text fields that contain EndofLine (it just shows the first line)…
but then mine does the same thing … need to write a listbox subclass with varying rowheights I guess… dang[/quote]
I started to write a listbox replacement, then realized that the amount of work exceeded the project I would use it in… so I came up with a “compromise”, that you are welcome to use as well.
If the contents of a listbox cell includes EndofLine characters, then the cell shows only the first line (with … on the end), and a CellHelpTag shows the whole string if the user mouse moves over the cell
[quote=325801:@Dave S]I started to write a listbox replacement, then realized that the amount of work exceeded the project I would use it in… so I came up with a “compromise”, that you are welcome to use as well.
If the contents of a listbox cell includes EndofLine characters, then the cell shows only the first line (with … on the end), and a CellHelpTag shows the whole string if the user mouse moves over the cell[/quote]
Yes, Dave, that would be good. My display listbox is a subclass of the listbox anyway so I would love to see your code for the listbox that you use. Then I can incorporate your changes in mine.
Simon.