I'd like to add my support to this idea - I'm sure many of us
deploy our
code to staging/production servers and would appreciate some utility
tools to help streamline this process. A utility to upgrade/downgrade
database schemas would be particularly useful. I'm not so sure about
scripts to help with subversion deployment - this area might be too
specific to each individual setup? I have a php script that traverses
the current directory plus matching subdirectories applying raw SQL
upgrade scripts to the chosen database. The syntax is:
dbupgrade.php [options] database_name [server_type]
[options] are things like -u user_name and -p password for the
database
connection. The server_type allows extra scripts to be run on
particular
types of server (e.g. 'production', 'staging').
It takes a snapshot of the database first (using mysqldump - so it is
MySQL specific currently) allowing the user to run a 'rollback'
script
if something goes horribly wrong. It uses Zend_Db to connect to the
database server and applies the SQL commands using the exec()
method of
the PDO object. The only requirement is that the database must have a
_version table with only one field (version) that contains the
current
version number of the database. Upgrade scripts are named
upgradeXXX.sql
where XXX is the version number. Only upgrade scripts with a value of
XXX greater than the current version are applied. It would be easy to
implement downgrade scripts as well. Upgrade scripts are stored in
a sql
directory like this:
sql/
database1/
upgrade1.sql
upgrade2.sql
production/
upgrade3.sql
staging/
database2/
upgrade1.sql
The dbupgrade script looks for subdirectories of sql that match the
database_name. It applies all upgrade scripts named >
_version.version.
It will also apply any upgrade scripts found in a subdirectory that
matches server_type. I've found this arrangement gives me enough
flexibility for my server setup. I also make use of Zend_Config to
look
for default database connection settings. However, this requires some
kind of naming convention to be generally useful.
It would be nice maybe if the _version table stored the version
history
(by introducing a timestamp field).
Tony
On 8 Jun 2007, at 07:03, oetting wrote:
This is something which RubyOnRails nicely manages with
"migrations".
Each change to the database is versioned in a separate file
which is
basically some ruby code to be executed. It knows about hooks
like "up"
and "down" and within this hooks it e.g. creates the new columns
with
default values or removes the column.
Year i know, it is rather cool. ZF is missing a database schema
abstraction
layer for this to be possible in the same way. There is a
proposal for
this.
An alternative implementation could be done by automating the
execution of
raw SQL commands. The developer would specify files like this:
# UP
ALTER TABLE foo ADD COLUMN bar int(11);
# DOWN
ALTER TABLE foo DROP COLUMN bar;
A script would then automate execution series of these migration
files. This
would not require any schema abstraction. I have made a proof of
concept
implementation of this, and would like to contribute in this area as
well,
if anyone finds it interesting.
The "idea" for a flexible deployment tool is "good", but actually I
think it has nothing to do with the Zend Framework in any way.
Whenever
you would create such a tool you wouldn't want it specific for
ZF, do
you?
Perhaps it could utillize ZF components but it should not be
specific
to any
apecific project layout.
Jacob
--
View this message in context:
http://www.nabble.com/Deployment-tool--
tf3886709s16154.html#a11020982
Sent from the Zend Framework mailing list archive at Nabble.com.
