Paul, Chuck, Theodore, Timothy, and others,
On 13. 1. 2015, at 1:02, Paul Hoadley <pa...@logicsquad.net> 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 (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
