Database Schema Versioning

Database schema is an important part of your source code and you would like to release it along with your application especially if it is a web application. The other reason to version your database is for continuous integration. Every time a team member makes some change in the code the build server picks it up and rebuilds the whole application again. If that change contains a database update, you would want that to happen on the build server automatically. Here comes your database script runner tool.

I have been looking for tools like that over the internet but could not find an open source script runner. The one which does the job is not open source and the one which is open source does not do the job right. So I decided to write my own.

This is a class library which takes in a connection string, a file format string and the path to the scripts directory. Connection string is off course the address of your database and the file format is an handy feature so you can use any name format for your sql scripts. For instance: a file name “my_%.sql” will read all files of the format my_<sequence number>.sql where sequence number is a 4 digit integer.

Here is the code. I use it as a library and run it at the start up on one of my services but you can create a console application and reference the assembly in it to use it as a standalone tool. I might create that console application later and will share it here if I do.

    public class SchemaUpdater
    {
        private readonly string _connectionString;
        private readonly string _nameformat;
        private readonly string _scriptdir;

        public SchemaUpdater(string connectionstring, string format, string scriptpath)
        {
            _connectionString = connectionstring;
            _nameformat = format;
            _scriptdir = scriptpath;
        }

        public void Update()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                var scriptseparator = new[] {"\nGO"};

                // Make sure we have a schema versions table
                var scriptfile = string.Format("{0}\\{1}", _scriptdir, "versions-table.sql");
                var transaction = connection.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    Array.ForEach(File.ReadAllText(scriptfile).Split(scriptseparator, StringSplitOptions.RemoveEmptyEntries),
                        (sql => new SqlCommand(sql, connection, transaction).ExecuteNonQuery()));
                    transaction.Commit();
                }
                catch(Exception)
                {
                    transaction.Rollback();
                    throw;
                }

                // Now run the baseline
                scriptfile = string.Format("{0}\\{1}", _scriptdir, "base.sql");
                transaction = connection.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    Array.ForEach(File.ReadAllText(scriptfile).Split(scriptseparator, StringSplitOptions.RemoveEmptyEntries),
                        (sql => new SqlCommand(sql, connection, transaction).ExecuteNonQuery()));
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }

                // Now run all the files
                var command = new SqlCommand("SELECT Version FROM SchemaVersion", connection);
                var version = command.ExecuteScalar();
                command.Dispose();

                var start = version == null ? 0 : Convert.ToInt32(version)+1;

                var filename = _nameformat.Replace("%", start.ToString("0000"));
                scriptfile = string.Format("{0}\\{1}", _scriptdir, filename);

                transaction = connection.BeginTransaction(IsolationLevel.Serializable);

                try
                {
                    while (File.Exists(scriptfile))
                    {
                        Array.ForEach(File.ReadAllText(scriptfile).Split(scriptseparator, StringSplitOptions.RemoveEmptyEntries),
                            (sql => new SqlCommand(sql, connection, transaction).ExecuteNonQuery()));

                        start++;
                        filename = _nameformat.Replace("%", start.ToString("0000"));
                        scriptfile = string.Format("{0}\\{1}", _scriptdir, filename);
                    }

                    new SqlCommand(string.Format("Update SchemaVersion SET Version={0}", start-1), connection, transaction).ExecuteNonQuery();

                    transaction.Commit();
                }
                catch(Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }

This is a small piece of code but it works for me, also takes care of the GO separator in the sql scripts and runs all commands in a transaction. It uses my scripts “version-tables.sql” which creates a new version tables in the database if it does not exist and “base.sql” which contain any sql statements which you want to run to create a baseline schema.

Advertisements

Oracle: Drop Database Command

 

Oracle introduced a new command in 10g to drop a database. Before that there was a set of procedures to follow in order to get rid of a database which involved deleting all data files from the file system, deleting control files, the parameter files etc. Now after this command everything is deleted by the RDBMS and there is no chance of forgetting any file to delete.

The command is simple, “DROP DATABASE”.

And the procedure to use this command is:

sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive restrict
ORACLE instance started.

SQL> exit

rman target /

drop database;

The database has to be started in exclusive restrict mode.

If you are running a RAC you will have to close down all instances and mount the database exclusively on one node only. The parameter file will need to be changed and the parameter “cluster_database” should be set to false. My suggestion is to create another parameter file from spfile and mount the database using that file.

CREATE PFILE=’/home/oracle/anotherpfile.ora’ from SPFILE;

The file can be modified and all RAC/node specific parameters can be removed.

 

Technorati Tags: ,,,,

 

Share this post : Social! del.icio.us it! digg it! Facebook it! live it! reddit! technorati! yahoo!

Layoff tracker website for IT & Software markets

Recession phobia is spreading very fast and strange rather funny ideas have started to sprinkle. Just have a look at this website:

http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9126955

These people have developed a database of IT companies who have layed off staff due to bad sales and bearish profit forecasts. They keep their database updated and its searchable as well with all statistics, remarks and features. The highest figure I have seen is from HP which is 24600 but overall I see IT is among the least effected industry. To further classify, most of the people among the layed off staff is from sales and marketing departments.

 

 

Share this post : digg it! Facebook it! live it! reddit! technorati! yahoo!