Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
- Subject: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
- From: OC <email@hidden>
- Date: Tue, 13 Jan 2015 02:36:43 +0100
Paul, Chuck, Theodore, Timothy, and others,
On 13. 1. 2015, at 1:02, Paul Hoadley <email@hidden> wrote:
>> But after first going through I am none the smarter and it does not seem to me it would help. Perhaps I am completely missing the point, but seems to me this is intended to a completely different task than the one of mine?
>
> It might be just solving the same problem differently. What you've described as your workflow seems fairly dynamic (reading the database, adding properties at runtime, adding and removing columns, and so on), and migrations as implemented in er.extensions.migration are not at all dynamic: you manually keep the database in sync with changes to the model using Java code that is run (if required) at application startup.
Now I have re-readed the docs a couple of times, and it seems to me
(a) I have to mark different schemas as "versions";
(b) I have to write basic Java code for each supported "version", which code (could do about anything, but normally) just loads and executes a script containing all the CREATE TABLE/ADD COLUMN/whatever SQL stuff needed;
(c) I have to write those scripts.
Then, WOnder migrations support keeps track of DB versions for me and makes sure appropriate Java upgrade method of appropriate class for a given model gets called (which normally means the appropriate script performed), based on the current and desired DB version.
If my perception is right, this is definitely what I don't want here. I don't need to manage different versions for lots of models. I would have anyway to generate those SQL scripts of (c) programmatically, or else, to write a code which scans my model and calls appropriate add-columns in (b).
Which is exactly what I am struggling with anyway, and perhaps I'm just blind, but I can't see much migrations would help me with. They would make sure appropriate code is called for given version -- but I have only _one_ code for _one_ version. And I would have to write the code -- just as I have to now.
>> Well... this version of my app _does_ do that at startup only, but future versions definitely should be able to re-read the data -- (a) -- and re-do the synchronization of model -- (b) -- and database -- (c) -- essentially at any time (of course, locking completely out all other instances when it is being done).
>
> I really don't know if Wonder migrations are going to solve your particular use case, but it's certainly worth a look. If you need the two-way synchronisation you describe (that is, from database back to model as well), then migrations are not going to help you. It's strictly one way: model to database via Java.
Just again, my task is
(i) to determine the desired schema -- it is partially defined by static model, partially defined by stuff stored in the database itself. So, the first thing I need is to read some tables, and based on their contents to create my schema dynamically. The thing is considerably more complex, but as a simplified example, presume there's a T_SCHEMA table with C_TNAME, C_CNAME and C_CTYPE columns; my code would read its contents, and for each row, it would know in the dynamic scheme, the table whose name is stored in C_TNAME should contain a column whose name is stored in C_CNAME and type in C_CTYPE.
(ii) having read in the data and having constructed the desired schema, I update my model appropriately. This part is comparatively easy -- given the simplified example above, I just go through all the C_TNAMEs, for each of them find appropriate entity in my model (I do not create tables dynamically, only columns, which mean I always find the entity). Then I go through all the C_CNAMEs with the same C_TNAME, and for each of them add appropriate EOAttribute. This (after some struggle with the new EOAttribute(entity) gotcha) works quite well.
(iii) having created the desired schema in my model, I need to sync the model to the database.
Note: this is one model, not a network of models. (Well I have got two of them in fact, but that's unimportant technicality.) There are no static versions; there are just static tables and static columns (defined in the model as read in from eomodeld), plust dynamic attributes defined by contents of the database itself -- those were added to the model programmatically at (ii).
And those programmatically added attributes now need to be synchronized to the database as columns.
The task is, therefore, as follows:
(a) I have to read in the current database schema;
(b) I have to go through all the dynamically added attributes, and check whether they do have appropriate column in the database
- if they do, all right (presumably this dynamic attribute has been added long ago and already synced to DB), nothing to do;
- if they do not (this very attribute has been added lately, after last sync) -- I have to create appropriate column.
For simplicity let's forget the remaining cases (extra column in DB -- whose attribute was removed after last sync --, or improper columnt -- whose type has been changed).
Conceptually, this is easy enough. Nevertheless, I've bumped into some technical problems, help with which I would surely appreciate.
(a) to read the current schema, it seems I could use either the ERXMigration stuff or the EOAdaptorChannel describeModelWithTableNames method. The former did not seem to me to read the current schema properly: e.g., there does not seem to be a method to check whether a column exists or not; the table.existingColumnNamed("FOO") method _ALWAYS_ returns an ERXMigrationColumn, even if there is no FOO column in the database at all!
Therefore, to read the current schema in I am using the EOAdaptorChannel describeModelWithTableNames method. It essentially works, although I've bumped into one really weird case of a table for which no attributes at all are read in -- although there _are_ columns in there. It's difficult to find the culprit without having access to EOAdaptorChannel sources; definitely, "show table DB_RECORD_GROUPING;" in sql92 reads the schema properly, whilst EOAdaptorChannel.describeModelWithTableNames returns a completely empty entity for the table. If anybody sees a possible culprit or at least a way to find it, I'd be grateful -- for the moment I have worked around this by ignoring empty entities, which luckily for this particular one does not harm (but in future with another one might).
The problem with improperly read table schema aside, this part I have finished and works properly: my code finds existing columns, and would create those which do not exist. Since the ERXMigrationTable.newXXXColumn stuff does not work properly (generating 'null' instead of 'add column' in the SQL script), I simply am generating the SQL scripts programmatically and sending them to the database directly through ERXJDBCUtilities.executeUpdateScript.
Here comes my second problem I would appreciate help with: normally, my application runs "isolation=read_committed/locking=optimistic" (as set up through JDBC connection URL). Of course, trying to ALTER TABLE I get an exception that another mode is needed.
My first attempt to solve this was
===
ERXJDBCUtilities.executeUpdateScript(channel,'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, LOCKING PESSIMISTIC;')
... the complete db sync stuff here ...
ERXJDBCUtilities.executeUpdateScript(ach,'SET TRANSACTION ISOLATION LEVEL READ COMMITTED, LOCKING OPTIMISTIC;')
===
Oops, does not work. For some triple-weird reason which I so far was not able to find any explanation in docs (and to be frank I had not time to dive into sources yet), ERXJDBCUtilities just logs out that it "skips" the SET commands, and it does just that. Therefore, at the moment, I have to run with modified JDBC connexion URL, which is wrong.
How does one temporarily switch the isolation level and locking?
Far as I can say, at the moment, I've got just those two hurdles:
- how to reliably read in complete current schema (without the problem that some table's columns are "invisible")
- how to temporarily switch the transaction settings?
Well I haven't tried to rename or drop columns yet, but that will wait :)
Thanks a big lot,
OC
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden