DDL and foreign keys
Kerr
DDL updates across many MS SQL and Oracle deployments. I was testing
the addition of a foreign key to an already existing table by using
the getDiff method, and it doesn't appear that the foreign key is
generated if the table already exists in my 'old' schema. If the
table is not detected in the 'old' schema, the FK is created fine as
long as it exists as part of the create table object. I have thus far
been unable to create an FK using its own individual create statement
in any scenario. I am also puzzled about date/time/timestamp
nomenclature and how it relates to the various datatypes in the source
DBMS. I'm going to do more research on this, but is there a data type
map available outside of having to decipher mssql.cfc, oracle.cfc,
etc.?
* The create constraint example at http://www.datafaucet.com/ddl.cfm
does not contain a refcolumns property, and as I looked through your
code I am pretty confident this is a required item.
* Is there support for indices? Trigger support is there, and those
can often be mimiced easily with queries and CF logic. Indices,
however, are an integral part of database design and the inability to
incorporate them into a DF schema makes the DDL piece of DF an
incomplete solution IMHO.
Thanks for your time!
Kerr
Isaac Dealey
> Hi, I've been testing the DDL area of DataFaucet as I need a way to
> do DDL updates across many MS SQL and Oracle deployments. I was
> testing the addition of a foreign key to an already existing table by
> using the getDiff method, and it doesn't appear that the foreign key
> is generated if the table already exists in my 'old' schema. If the
> table is not detected in the 'old' schema, the FK is created fine as
> long as it exists as part of the create table object.
Right... I was about to say "I think that was an oversight on my part"
but then I remembered that there was a complication in the way that I
wrote the SchemaExport.cfc (which is fairly new) because I used the
implicit syntax for foreign keys instead of creating them separately --
so in the export XML it just says references="othertable.othercolumn",
which was easier when exporting from <cfproperty> tags because it
eliminates the need to generate a random-ish name for the constraint.
The problem of course is the one you're running into, where when you do
the diff it's only looking for new columns, not columns that already
exist, so if you added a constraint on an existing column, it doesn't
include the new constraint in the diff, only if it's a new column.
Wasn't sure how to handle the discrepancy in the diff XSL. I'll have
another look at it tomorrow and see if something leaps out at me.
The schema diff is still pretty new though, so I hope it will become
more robust and we'll be able to eliminate this limitation.
> I have thus far been unable to create an FK using its own
> individual create statement in any scenario.
Hmmm... you're talking about this one right?
<create type="constraint" table="mytable"
name="mytable_ref" foreignkey="pk,uid" reftable="othertable" />
I'll try and test this tomorrow and make sure it's still working... I'm
not sure why it would have stopped working, I don't recall any recent
changes. Otherwise feel free to send me the code you've got to
in...@datafaucet.com directly (or you can post to the list if you prefer)
and I'll see if I can reproduce the issue.
The DDL page of the documentation could probably be better too -- I
figured DDL would be used less often, so I was focused more on the
documentation for the rest of the suite.
> I am also puzzled about date/time/timestamp
> nomenclature and how it relates to the various datatypes in the source
> DBMS.
In theory you're always supposed to use "timestamp" when using
DataFaucet because that's the standard. The agent for SQL Server will
then convert that to "datetime" for you. I don't really work with the
separate date/time datatypes (oracle has thoes right?) but in theory
they should work if you db supports them. I just stay away from them in
my code for cross-platform compatibility. I wrote the SQL Server agent
to convert them both to datetime because I was under the impression that
SQL Server didn't support them and that would be the closest alternative.
Although I could have been mistaken. :)
> I'm going to do more research on this, but is there a data type
> map available outside of having to decipher mssql.cfc, oracle.cfc,
> etc.?
The data types are actually structures defined in
/system/agent/format.cfc -- in sturctures called this.type and
this.dbtype -
this.dbtype is for DDL which converts a number of numeric data types to
"numeric(p,s)" because numeric is widely supported while float may not
be or may not be well implemented (as is the case in Oracle where the
implementation of float is problematic). Individual agents can then
override those, like I'm pretty sure sql server overrides the various
int types so that they stay smallint, bigint, etc. instead of being
converted to numeric.
this.type is used for cfqueryparam tags, so that's why there are two
different structs
And yeah, I know the agents aren't really documented right now either.
In my defense, they're not the primary API for daily coding. :)
> * The create constraint example at http://www.datafaucet.com/ddl.cfm
> does not contain a refcolumns property, and as I looked through your
> code I am pretty confident this is a required item.
Well it defaults to the value of the foreignkey argument in the
createConstraintEntity() method -- so in theory it shouldn't be required
because it assumes the primary key column has the same name as the
foreign key column.
> * Is there support for indices? Trigger support is there, and those
> can often be mimiced easily with queries and CF logic. Indices,
> however, are an integral part of database design and the inability to
> incorporate them into a DF schema makes the DDL piece of DF an
> incomplete solution IMHO.
No, indexes is something I haven't gotten around to. But then, I've not
had to manage any farms of databases like you have. I've mostly worked
for companies that just had a small handful of databases and we used
the SQL profiler to work up indexes when they got sluggish. In your case
having index support in the tool makes perfect sense. If you're
interested in working up the addition, I'd love to add your addition to
the project. :)
> Thanks for your time!
Welcome! Thanks for joining us. :)
--
[ ike ] founder - DataFaucet ORM
phone: 781.769.0723