Some ideas about table creation and migrations

[Mark Drew]

Hi there,
I have been thinking about adding Amazon Simple DB support to reactor,
of course, before doing this, I think it would be useful to be able to
add Tables (Domains in ASDB talk) to a project.

Before I get too sidetracked into the specifics, I thought I would
suggest the following use scenario and you can tell me what you think:

The following creates a table, and adds columns to it.. Once you have
added all you want, you can just save it:

reactor.CreateTable("Post")
.addColumn("name", "type", "size", "extra")
.addColumn("", "", "")
.addColumn("", "", "")
.addColumn("", "", "")
.add("")
.save();
(notice the add method, its a shortcut to addColumn in the Table object)

Of course, you would probably want to delete columns too:
reactor.CreateTable("Post")
.removeColumn("name")
.removeColumn("name")
.removeColumn("name")
.remove("name")
.save();

Since in development you would want to track all these changes, each
save method would create a "migration" (see Ruby on Rails for more on
this).. each migration would give Reactor a "version" and we would
have ways of going up and down those versions with a migration file.
These XML files would be part of your generated project and might be
XML files in the following format:

<migration version="1">
<up>
<table name="Post">
<add name="something" type="varchar" size="255" />
<add name="anotheritem" type="varchar" size="255" />
<add name="anotheritem" type="varchar" size="255" />
</table>

</up>
<down>
<table name="Post">
<drop />
</table>
</down>
</migration>

Another part of the API would of course be the migration control so doing:

reactor.migrate();
Would create the latest version of the database

reactor.migrate("to"); would create the database up to a certain version

reactor.migrate("to", "from") (or rather from => to) would upgrade or
downgrade a database from one version to another

I have put his email under the RFC's section of the reactor project,
but I would love to get your feedback on these ideas

http://trac.reactorframework.org/wiki/CreateTablesAndMigrationsRFC

--
Mark Drew

Adobe Community Expert
Blog: http://www.markdrew.co.uk/blog/
LinkedIn: http://www.linkedin.com/in/mdrew

[Dutch Rapley]

looks good, but I will make a suggestion on the XML

instead of

<migration version="1">
       <up>
               <table name="Post">
                       <add name="something" type="varchar" size="255" />
                       <add name="anotheritem" type="varchar" size="255" />
                       <add name="anotheritem" type="varchar" size="255" />
               </table>

       </up>
       <down>
               <table name="Post">
                       <drop />
               </table>
       </down>
</migration>

I'd recommend adding an "action" attribute to the xml:

<migration version="1">
       <up>
               <table action="create" name="Post">
                       <column action="add" name="something" type="varchar" size="255" />
                       <column action="add" name="anotheritem" type="varchar" size="255" />
                       <column action="add" name="anotheritem" type="varchar" size="255" />
               </table>
       </up>
       <down>
               <table action="drop" name="Post"/>
       </down>
</migration>

For tables, we can do things like CREATE, DROP, and ALTER. For columns, our actions would be ADD, RENAME, DROP, MODIFY

-Dutch

[Tom Chiverton]

2008/12/2 Mark Drew <mark...@gmail.com>:

> reactor.CreateTable("Post")
> .addColumn("name", "type", "size", "extra")
> .addColumn("", "", "")
> .addColumn("", "", "")
> .addColumn("", "", "")
> .add("")
> .save();
> (notice the add method, its a shortcut to addColumn in the Table object)

I'd really like to be able to add new objects to the reactor.xml and
when it starts up, the correct CreateTable().... is run, creating a
new version.
This would require type hints on the <field> tag of course.

--
Tom