Add functionality to alter/update existing database schema

[jburns131]

I'd like to be able to have a database/table schema defined in some type of config file.

Then I'd like to use Sql_Schema to compare the existing database schema with the new schema in my config file.

I would then like to be able to use the comparison to build the database queries needed to update the database schema to my new schema and pass it to something similar to __toString(), which can be passed to PDO or ExtendedPdo.

I am prepared to create my own library to do this, but wanted to know if this would fit in the domain of what Sql_Schema does. Or should this go into another library, like Sql_SchemaManager?

What do you think?

I'd be happy to send PR's if you'd like.

[pmjones]

wanted to know if this would fit in the domain of what Sql_Schema does

I believe it does, yes. PRs definitely welcome on this.

[harikt]

👍

[jburns131]

I'll fork and see what I can come up with :-)

[jburns131]

My vision is to be able to compare and alter anything from a single table to every table in the database.

The biggest issue I see right now is that other than 'primary key' the library does no introspection regarding indexes. I'm only familiar with MySQL, so that would entail INDEX, UNIQUE and FOREIGN KEY indexes. I'm not sure what, or if, there are equivalents for the other database systems.

Maybe I should include a use case for this feature:

• My software has a module/plugin system. I want to have the option where a module will have a schema file that describes to latest schema of the database. The software back end will use the feature we are talking about here to compare the current database/table schema with the new schema file in the module and update the database/table as needed
• Some modules may have more than one table, with multiple indexes, namely foreign keys

I'm not sure if I'm looking at the trees for the forest here. Right this second is looks like I'm going to have to research each DBMS to find out how indexes or their equivilent work, and then update each *Schema.php file to include methods that will query for index information.

Does this sound right to you folks?

This looks like it's not going to be a simple task. If anyone has any input I'd love to hear it :-)

[pmjones]

I think the simpler this starts, the better. I have begun work on a "migration" branch: https://github.com/auraphp/Aura.SqlSchema/tree/migration

[pmjones]

@jburns131 Any progress on this?

[jburns131]

@pmjones Sorry for the delayed response.

I hope everyone has been having a great summer :-)

I have been taking a break from all dev related activities (including email) to spend some time with my wife and little ones during the summer vacation.

We're making some changes to get ready for the coming school year. That includes budgeting my time to include coding activities without threats that have to do with my laptop and a public pool :-p

I joke obviously :-) It's been wonderful getting out with the family or staying in working on crafts and probably having more fun playing games on our Wi U than my girls :-D

And I was feeling some burnout last spring. Taking a break was just what we all needed. Good times!

That being said, I've got the itch again lol.

I'm looking forward to updating my dev environment, grokking some docs and pouring over AuraPhp code to become more familiar with the current code base/structure.

I've got some catching up to do, but I plan on taking another look at this soon.

@pmjones Don't be surprised if I try to catch you on irc to touch base, get some feedback or pick your brain ;-)

[pavarnos]

Hi. Any progress on this? I have been using my own library for this for many years https://github.com/pavarnos/schema which gives me a nice declarative way of describing the database and syncing schemas which is also useful for a custom report writer, generating documentation. Was thinking of migrating it to use Aura but:

• Aura.SqlSchema does not support table and column comments
• the migration branch here is using up()/down(). Mine shows a diff between the php declarations (ideal) and the queried ddl (actual). These could potentially work together by using the diff to generate a class stub for the next version migration...

[harikt]

@pavarnos the migration was not released in 2.x . May be @pmjones can give you much more details for I am weak in this subject .

[pmjones]

Little if any progress here. There is the beginning of a migration branch but it has been unattended for quite a while. @pavarnos you may want to look at that, and see if it begins to suit your needs. As my attention is spread thin, I'd appreciate any efforts on bringing it to fruition.

[pavarnos]

was thinking more of something like DBAL schema comparator http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-representation.html where you can take a parsed schema from the database plus an ideal schema from code and return SQL to update the database.
You could use this to generate the up()/down() automatically if you wanted... but for small or inhouse projects (eg single install) this is not needed.

Doctrine's schema comparator is unusable for me because

• enum support is a big hassle (i need to register a custom Type class for every enum. I have dozens)
• i want to be able to tag columns and add other metadata (eg richer types than those directly supported by the DBMS like a markdown column type) so i can automatically populate forms, handle archiving / export / custom query builders / auto generate reports etc all from the schema definition + a small amount of sugar. DBAL makes this awkward with the way they add columns to tables and tables to schemas: any extensions would be brittle.

i think this would be hard in Aura too: need a richer and extensible Table and Column class.

Is this a useful direction for Aura.SqlSchema, or too far out of scope?

[pavarnos]

... what i like about the abstract (code) method of declaration is that its easy to create portions of a mock database for testing eg call $customerOrderRepository->getSchema()->toSqlite() to create an empty table in memory, then $customerOrderRepository->save($testData) to populate it ready for testing.

[harikt]

@pavarnos ok, so what you mean is easy to switch between the database vendors for testing.

I like the direction. As migration is not released you can always push the idea 👍 . I don't know what may be the reason @pmjones went with the other direction for we already have Aura.SqlQuery which can do something similar for queries with multiple vendors.

You can always push / send a PR to make the direction :-) .

[harikt]

@pavarnos If you are interested in bringing the features from
https://github.com/pavarnos/schema#declare-your-schema-programis to aura, we can also consider adding you to our organization. ( Nothing to bribe you :-) )

Thank you

[pavarnos]

Yes, if i was going to build that again I'd probably base it on Aura components but steal a lot of ideas from DBAL to mix in with what I already have. Lets see what @pmjones says: it is a big change in direction.