Re: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
Re: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
- Subject: Re: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
- From: OC <email@hidden>
- Date: Tue, 13 Jan 2015 16:26:21 +0100
Paul,
On 13. 1. 2015, at 5:33, Paul Hoadley <email@hidden> wrote:
> On 13 Jan 2015, at 12:06 pm, OC <email@hidden> wrote:
>> 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";
>
> It's really only conceptual. A "version" is just a "change to the model which I'm going to need to reflect in the database at the next deployment".
Well right, but far as I understand, the version numbers are important to select the appropriate upgrade code, are they not?
I have nothing like that. I've got one "upgrade" code, which is always used the very same way -- adding (potentially also removing/renaming) columns based on the current differences betwixt the model and the real DB schema. Nothing more, nothing less.
> ... I prefer using the migrations API in code.
If you mean e.g. adding columns through migrationTable.newXXXColumn(name,allowsNull) etc., that's what does not work for me. It consistently generates "alter table FOO null BAR TYPE" instead of "alter table FOO add column BAR TYPE".
>> (c) I have to write those scripts.
> Entity Modeler can generate SQL for you from a model.
But how would Modeller help me?!? I need to change the database from my application, live, runtime. Changing the database manually does not help at all. Besides, my static model does *not* contain attributes for those colums which need to be added/removed/updated; those are added also dynamically at runtime.
And moreover, far as I know, Modeller can't add/remove columns; all SQL it can generate are statements to create/drop _tables_. Which is precisely what I do not need here.
> ... You need to know what you're doing.
I believe I know that pretty well. My problems -- at the moment at least -- are
(a) sometimes (so far in one sole case, which is luckily harmless, but I fear future) the EOAdaptorChannel describeModelWithTableNames API does not read the current schema properly (returning an empty entity for a table which is far from empty)
Is there another, more reliable API which reads in the current schema? I've tried the migration API, and far as I can say, it does not read the current schema at all -- correct me if I am wrong, but it seems to me the migration API assumes the programmer already knows the current schema. That's not my case, I need to read it from the database.
(b) I did not find a way to switch my isolation level and locking discipline. I can set them up using the JDBC connexion URL all right, but then they stick, and commands like
ERXJDBCUtilities.executeUpdateScript(channel,'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, LOCKING PESSIMISTIC;')
simply do not work at all. That's a problem, for my application runs read-committed/optimistic, but to change the schema FrontBase demands the appropriate transactions to be run serializable/pessimistic.
Would migration solve this? Given my JDBC connexion URL specifies read-committed/optimistic, would migration automatically use serializable/pessimistic transaction for schema changes?
(c) which reminds me, meantime I've bumped into another weird thing which I alas had not time to pursue in detail yet: if I connect serializable/pessimistic, I can change schema all right, but it seems some fetches (some of them only, nor all) do not work for some reason?!? I have added a code which reads in some tables and checks consistence; as noted above, alas I haven't been able yet to find the precise culprit, but I know that
- if I connect read-committed/optimistic, the code works properly, does not throw, fetches properly, logs the found inconsistencies and ends without a glitch;
- if I connect serializable/pessimistic (making no other change anywhere, just changing the JDBC connexion URL), I'm getting "can't open channel" exceptions like this:
===
04:03:31.518 WARN An exception occurred while trying to open a channel: Syntax error 108. Expected .at com.frontbase.jdbc.FBJErrorMetaData.errorMessageAtIndex(FBJErrorMetaData.java:162)
at com.frontbase.jdbc.FBJErrorMetaData.getExceptionChain(FBJErrorMetaData.java:194)
at com.frontbase.jdbc.FBJConnection.checkMetaData(FBJConnection.java:1205)
at com.frontbase.jdbc.FBJConnection.initTransaction(FBJConnection.java:376)
at com.frontbase.jdbc.FBJConnection.<init>(FBJConnection.java:162)
at com.frontbase.jdbc.FBJDriver.connect(FBJDriver.java:55)
at java.sql.DriverManager.getConnection(DriverManager.java:579)
at java.sql.DriverManager.getConnection(DriverManager.java:190)
at com.webobjects.jdbcadaptor.JDBCContext.connect(JDBCContext.java:236)
at com.webobjects.jdbcadaptor.JDBCContext._tryConnect(JDBCContext.java:362)
at com.webobjects.jdbcadaptor.JDBCContext._channelWillOpen(JDBCContext.java:505)
at com.webobjects.jdbcadaptor.JDBCChannel.openChannel(JDBCChannel.java:111)
at com.webobjects.eoaccess.EODatabaseContext._openChannelWithLoginPanel(EODatabaseContext.java:1907)
at com.webobjects.eoaccess.EODatabaseContext._obtainOpenChannel(EODatabaseContext.java:1966)
at com.webobjects.eoaccess.EODatabaseContext._objectsWithFetchSpecificationEditingContext(EODatabaseContext.java:3054)
at er.extensions.eof.ERXDatabaseContext._objectsWithFetchSpecificationEditingContext(ERXDatabaseContext.java:68)
at com.webobjects.eoaccess.EODatabaseContext.objectsWithFetchSpecification(EODatabaseContext.java:3195)
at com.webobjects.eocontrol.EOObjectStoreCoordinator.objectsWithFetchSpecification(EOObjectStoreCoordinator.java:488)
at com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4069)
at er.extensions.eof.ERXEC.objectsWithFetchSpecification(ERXEC.java:1308)
at com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4444)
at com.webobjects.eocontrol.EOEditingContext$objectsWithFetchSpecification.call(Unknown Source)
...
===
Weird. Later I read the DB contents all right, even in serializable/pessimistic mode.
>> (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.
>
> I agree that migrations is not going to help you with that. That sounds like a pretty unique use case. Out of curiosity, are you able to describe in greater depth what you're doing here? (That is, the actual nature of the application, or the problem you're solving. I understand your technical explanation, I just can't picture a use case for it.) It sounds very interesting.
Well the gist is that some of my EOs have (along with normal ones) a set of user-defined attributes: the user can open an appripriate editor and set up something like "my auction will, along with the attributes which the application itself defines, also three VARCHAR attributes named 'foo', 'bar', and 'bax'; they will have four DECIMAL attributes named so-and-so, etc etc.". These things are stored in the database, and the application then allows to use the dynamic attributes the very same way one can use static ones (i.e., display them in forms/tables, edit their contents, filter by them, etc.)
Originally, I have solved this through a BLOB, which contains a serialized NSDictionary, which contains all the dynamic attributes by-name.
That works reasonably well, but we bumped into grave efficiency problem filtering such tables: to fetch only items whose attribute 'foo' matches 'John*', we can't do SQL SELECT; instead we have to fetch the whole table, decode all the BLOBs, and filter by the results. That proved a show-stopper.
Thus, now I have to implement all those dynamic attributes as table columns, so that I can (e.g.) use fetch qualifiers for them.
Thanks and all the best,
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