When can we expect to move away from db.xml?
Permalink
I know that FK are not supported in the C5 implementation of the ADOdb XML schema (defined here ->http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema)...
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 ?
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 ?
I used your other excellent as a startig point... but my experience is otherwise. When I did this ->
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?
<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?
That's strange. I created a bare bones test package with identical field definitions, and I got this:
(I removed a few columns because the complete printout got linewrapped in an ugly way.)
Edit: Here's the db.xml I used:
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>
clearly you are correct... which lead me to finding the problem. which
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.
Thanks
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.
Thanks
After some testing it would appear that once you have a column set as KEY and AUTO_INCREMENT then modifying the keys becomes impossible.
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:
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.
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[42000]: 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.
Yes, the sql tag is ignored (heh, I honestly had forgotten or never knew that tag was supported in the first place.)
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?
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?
I'm not for or against db.xml, but is there something inherently wrong with it?
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.
http://stackoverflow.com/questions/17489082/standard-file-formats-t...
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.
http://stackoverflow.com/questions/17489082/standard-file-formats-t...
XML is the worst of all the alternatives. Even the LISP schema option is more readable! But with regards to what the best options is, why not the obvious choice, JSON ? Seehttp://json-schema.org
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
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
I think the readability of XML is a matter of opinion, but if readability is really the issue then the "obvious" choice is YAML. Structured data doesn't get much more readable than that.
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.
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.
There are literally millions of comments, debates and articles about which is better. And, like most solutions, there are pro, cons and personal preferences. Mine happen to be JSON, YAML, etc, etc, XML.
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?
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?
Now correct me if I'm wrong, but aren't the limitations of db.xml less about the file format and more about the CODE that reads the db.xml? For example, like I demonstrated in another post, foreign key support is trivial to add.
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.
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.
I like Doctrine ORM, but I'm not sure every aspect of the core will run its data layer through it, which is why I don't think a solution based solely on the Doctrine and its ORM aspect will work.
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...
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...
Doctrine has a "migrations" system. It's fairly verbose, but if you're going to move away from the db.xml files, it needs to be replaced with something that handles automatic schema updates and since you're already standardizing on Doctrine then that should probably be the solution.
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.
-Jordan
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.
-Jordan
Eh, actually upon further inspection I'm not sure Doctrine's migration functionality will work in the context of C5 because it seems to only be usable as a command-line tool (not something that is called within a web request, like when you're installing or upgrading an addon from the C5 dashboard):
http://docs.doctrine-project.org/projects/doctrine-migrations/en/la...
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 :-/
http://docs.doctrine-project.org/projects/doctrine-migrations/en/la...
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 :-/
We actually use Doctrine Migrations as the backbone of our 5.7 upgrade system. So it will work and it is something that could work from a package – we just haven't made it work at the package level yet. We did have to do a bit of customization for it and hunt around the API (since it isn't the typical way of using it) but it does work and we've been pretty happy with it so far. There's no UI or support for doing any rollbacks using it currently – but that's just because we haven't built the UI or the down scripts, not because it couldn't theoretically be possible.
Yeah I really like the declarative database definitions too. Doctrine can handle all that stuff really nice (with their Comparator classes that we use when upgrading db.xml files.
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")
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")
You are free to use this as a starting point:
http://www.concrete5.org/community/forums/customizing_c5/adding-ind...
http://www.concrete5.org/community/forums/customizing_c5/adding-ind...
https://www.concrete5.org/community/forums/customizing_c5/adding-ind...
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.