When can we expect to move away from db.xml?
And with a bit of a tweak (https://www.concrete5.org/community/forums/customizing_c5/adding-ind... ) they can me turned on (which I am not about to do as that is just going to come back and bite me)
But can anyone tell me how to defined compound keys with dbn.xml? The C5 doc (http://www.concrete5.org/documentation/how-tos/developers/creating-... ) says
but I can not see how anywhere.
Can I assume that the <sql> tag is also ignored?
For now I will just run my alter tables manually, but, with the move away from ADOdb and towards Doctrine, is there plans to support something like the schema.yml format or @ORM ?
Edit: Of course, if you want to have multiple compound keys in single table or compound keys that do not involve the primary key, then that's not currently possible as far as I can tell.
<field name="ID" type="I"> <key /> <unsigned /> </field> <field name="bID" type="I"> <key /> <unsigned /> </field> </table>
the table that was created only had one key on the last field defined with a key tag.
Did I do it wrong? Is there another way or parameter I needed to add?
mysql> SHOW INDEX FROM c57_dev.myTestPackage; +---------------+-----------+---------+-------------+------------+----------+------------+-----------+ | Table | Non_unique| Key_name| Seq_in_index| Column_name| Collation| Cardinality| Index_type| +---------------+-----------+---------+-------------+------------+----------+------------+-----------+ | mytestpackage | 0| PRIMARY | 1| ID | A | 0| BTREE | | mytestpackage | 0| PRIMARY | 2| bID | A | 0| BTREE | +---------------+-----------+---------+-------------+------------+----------+------------+-----------+ 2 rows in set (0.00 sec)
(I removed a few columns because the complete printout got linewrapped in an ugly way.)
Edit: Here's the db.xml I used:
<?xml version="1.0"?> <schema version="0.3"> <table name="myTestPackage"> <field name="ID" type="I"> <key /> <unsigned /> </field> <field name="bID" type="I"> <key /> <unsigned /> </field> </table> </schema>
was... I was not aware that I had to drop the table completely before
rebuilding (by installing a block, for example). When uninstalling a block
the table remains (not unreasonable), but on reinstall of same block, it
only made some alters to the table (most likely the ones that were not in
any conflict). So if I already has a primary key defined, it did not make
the alters to create the different primary key. So the schema I was
looking at was always the old one :(
I ended up with a bunch of sql statements that I need anyway because I need
FK's and those, as you pointed out, are not supported in the db.xml.
If I remove the AUTO_INCREMENT property, keys can once again be modified via db.xml.
If I try to remove a key from bID column -- or add one to it if it hasn't got one -- while ID has the AUTO_INCREMENT property, I get this exception:
'An exception occurred while executing 'ALTER TABLE mytestpackage DROP PRIMARY KEY': SQLSTATE: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key'
You will get this error even if the same update removes the AUTO_INCREMENT, so it seems that changing keys requires two updates: one to remove the AUTO_INCREMENT and another one to change the keys and reset the AUTO_INCREMENT.
As a side note, the update page doesn't display the exception (at least for me), so it's not obvious the update fails. The only indication is the lack of "Update succeeded" message.
I do agree it would be good to move away from db.xml files, since they're a holdover from a system we don't even use any more. But I'm not sure what the alternative is. I'm not aware of a portable format other than db.xml – at least not one that isn't bound up completely with Doctrine ORM. We're moving certain things to Doctrine ORM and we want it to be a first-class citizen in concrete5 – but it's gonna be a long time before we could move everything into it, and that may not happen at all. Is anyone aware of a similar portable schema definition ala AXMLS that is actively maintained and compatible with Doctrine DBAL?
As file formats go, XML appears to be quite widely used for database schema descriptions.
As for the ADODB style structure, does it somehow restrict developers from creating complete schema descriptions? I'm not talking about the code that parses it, but the actual XML structure.
There doesn't seem to be any widely accepted standard for XML (or any other) files describing DB schemas, so unless the move is to raw SQL (which has its own drawbacks) I'm not sure what the tangible advantages would be.
Easy to share, parse, validate, read and easily supported without having to waste the hours and hours of learning about XML/DTD, which has zero use outside of XML
On the other hand, if you want something that is easy to share, parse, validate and support, then the choice is SQL. Stick to the standard and it works with every relational database without having to waste any time either learning what you shouldn't already know or writing adapter classes to convert one syntax to another.
(Also, if the XML file is well-formed you don't need a DTD, in which case XML is really not much more difficult than HTML.)
I'm not sure if there is a best solution. Personally, I just want something that is quick to write, does what is needed and stays out of my way. The current solution fulfills this criteria but so would JSON or YAML. In the end, the time I spend writing the database schema is just a fraction of the total time spent on a block or package, so unless the format is really convoluted it makes no real difference.
The more important question, I think, is, what is the best solution for C5. Building my schema in c5 is not a simple task. First I build my db.xml... and given the size and complexity of the tables, XML is just fine. But then I have to manually write dozens of "ALTER TABLE..." commands to add my FK's, unique (non-pri) indexes, and then write a bunch of test sql scripts to validate and test the table... It's a royal pain in the butt, and worse, completely outside he framework.
If C5 plans to stick with MySQL, then it should go to SQL. If it plans to supports other backends, it should move to JSON or YML, or even better, embrace some sort of ORM, if not Doctrine, then maybe, RedBeansPHP, phpDataMapper, Propel, Axon ORM, Sado Library... or a few others floating around.
Andrew, you say ". I'm not aware of a portable format other than db.xml – at least not one that isn't bound up completely with Doctrine ORM.", and it soudns liek you are Docrtine ORM is something you woudl prefer to stay away from. Is that true, and dos it apply to all ORMs, even ones based on Doctrine like Spot?
I guess my questions is, if an ORM gives you at least the same support you have for your existing store, plus add a boatload of of other options, where is the downside?
What other features are you missing? Are they really that difficult to implement with db.xml?
I'd still don't understand what's really wrong with it. I'm not championing for the db.xml, but the only real argument I have heard against it is that XML format is difficult to read. The fact that it doesn't support this or that is just a matter of implementation, that is, changing the format doesn't change anything in that regard.
Going with the ORM route is of course one option with many advantages (and disadvantages), but I'm not sure if it actually solves the problem as much as changes it.
About the SQL element that you asked about in the first post: I'm not convinced that a schema description is the right place for custom SQL queries.
That said, I think creating a declarative XML format that is more specifically wrapped around Doctrine's schema object is probably the way to go here. A format that has support for things that our current format doesn't have, and one that matches the Doctrine naming conventions for types, field lengths, etc...
However, I believe as with most migration libraries, Doctrine requires you to explicitly say what changes going "up" and "down" from one version to the next... in other words, it can't be handled as simply and declaratively as the current db.xml way. C5 will need to pass in the package version that is being upgraded FROM along with the new version that is being updated TO in the package controller's upgrade() method so it can run the proper migrations (or the C5 built-in system will need a way to know which migrations are associated with which versions so it can run the proper migrations itself).
I'm not a huge fan of the db.xml system (moreso because of its feature limitations than the XML syntax), but the way it automatically handles schema migrations in a declarative way is really nice and not something I've found in other migration libraries.
When I've looked for php migration libraries, the one that seems to be the most well-regarded and feature-complete (and easy to use) is Phinx:http://phinx.org/
All this being said... yeah it might not be worth the trouble to ditch the db.xml format :-/
Maybe the answer is as simple as an updated version of the db.xml format that adds foreign keys in some way, and uses doctrine's naming conventions for things like types and lengths, rather than cryptic fields from AXMLS (e.g. type="string" instead of type="C")