Community
Participate
Working Groups
The portal schema checker currently has two parts: one part for the initial creation of a table and another part for the refactoring of a table. I'd like to have just one piece of code: one that could both create a table and refactor a table, all driven by the table dump (plus perhaps a bit more information about primary keys, etc).
Here a two things I think we need to add to this scheme to make it work: 1) a set of queries that run before refactoring a table Example Usage: When you want to rename a row. The code would just drop on row and create another. 2) a set of quries to run after refactoring Example Usage: Setting up keys, triggers.
Been working on this bug over the past few days. First on over view of the scheme I have settled on. Overview: First step is the check to see if the last schemas matches, if so stop here. If not run the 'before' queries associated with the last schema. CREATE TABLE - If a table doesn't exists it is created. (this is working) DROP FIELDS - Any field that don't exist in latest schema but are not in the table drop (this is working) - Also must drop keys associated with fields. (working) ADD FIELDS Any field that does exist in latest schema but not in the table add to table. Also add keys. After creating/dropping/adding run the last 'after' queries. Finally recheck the database table matches the latest schema and report any issues. Edge cases: - If all fields are to be dropped last field wont drop so you must drop the table and recreate table from newest schema. Not easy to just add then drop because of primary keys. - Must turn off (and back on) foreign key constraints while refactoring.
You probably want to ADD FIELDS then do some queries and then DROP FIELDS. This is needed is order to rename a field, i.e., ADD COLUMN B UPDATE B = A DROP COLUMN A If you do: DROP, ADD, UPDATE (as you suggest) then you'll lose the values of A.
Instead of add/update/drop why don't we use something like this: ALTER TABLE t1 CHANGE a b INTEGER; The case this deals with nicely is when the field you want to change is the primary key. With the add/update/drop scheme you have to drop the key and re-add the primary key. At least that is what I had in mind. But now that I think about it the code should probably deal with both cases.
This code is working and checked in to the Phoenix repository as part of the database management code. It should be a drop-in replacement for the one in the Portal, however every table schema will need to be re-made to use the 'SHOW CREATE TABLE' statement. All table history will be lost when doing so.
*** Bug 239358 has been marked as a duplicate of this bug. ***
*** Bug 248214 has been marked as a duplicate of this bug. ***
We are no longer making significant changes in the existing portal. Marking as WONTFIX.
