Versioning DB Scripts Structure with TortoiseSVN

Question

It just occured to me today as I had setup TortoiseSVN. How to manage and track DB scripts.

In TFS, you can just add a script to a changeset. Then add the changeset to whatever task.

I've used both TFS and Subversion (Tortoise) and don't recall in the past what the best way was to manage this. Do you create folders by project name and date or do you create a folder called Scripts and just name the filename the name of the project?

we dont' really have a task management system at this new place (because there was only one coder in the past).

Answer 1

The SVN "red bean" book has some good suggestions on how to set up repositories.

Your project setup is your choice, of course.

Answer 2

The way our company does it is like this:

[repository-for-app]
+ [trunk]
  + [database-name]
    + [build-scripts]
      - [release_date_1]
      - [...] (etc)
    - [data]
    + [metadata]
      - [procedures]
      - [roles]
      - [tables]
      - [triggers]
      - [views]
      - [...] (etc)
- [tags]
- [branches]

So, for example, inside [procedures] there would be one .sql file per CREATE OR ALTER PROCEDURE statement.

The [build-scripts] folder is for actual SQL patch scripts that are applied to bring one version of a database up to the next version.

Answer 3

Here is what I do using CVS:

Create a folder called "db", under it create another folder called "upg" (for Upgrades). This folder contains one folder for each version, such as "v100", "v110", "v120", etc.

In each of the version folders are the SQL scripts to create the database initially ("v100"), and to patch/upgrade the database structure in subsequent versions ("v110", etc.).

Since there is often a dependency between the scripts (ie. you must create the tables before you load data, etc.), each script is named using a number prefix. For example:

v100
  01_create_the_tables.sql
  02_create_some_views.sql
  03_load_some_config_data.sql
  04_add_a_column_or_something.sql
v110
  01_create_some_new_table_and_migrate_data.sql
  02_drop_that_unused_index.sql
  03_set_up_fulltext_search.sql
  04_grant_some_privileges.sql

And so on.

This system has worked well for me the last 10 years or so, with one codebase being deployed to several different customers, all with multiple test, acceptance and production databases. Having the version numbers for folders and sequence numbers for the scripts makes it easy to determine which version a given database is, and which scripts to apply to bring it up to whatever version is required by the client application (ie. the user interface).