Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>We have a system where the database is nominally the master-inside our source control system, we maintain a sequence of "schema change" scripts (.sql files), each of which is responsible for idempotently rolling back the change and then applying it. Each script is just numbered, so we have 000.sql (which creates the database and sets up standard objects), 001.sql etc. </p> <p>During development, a developer writes a schema change script and runs it against the development database. Each change is required to add a row into a <code>dba.change_info</code> table, containing the change number and a brief description. In order to roll back a change, one can just run the first section of it. For SQL Server, the idempotence of the rollback section is handled by examining sysobjects etc before issuing DROP commands- similar to "drop ... if exists" constructs. Schema changes may need to do migration of data if a model is being changed rather than simply being added, and also are used to maintain reference data.</p> <p>During the release process, a DBA (we're a small company, so this is a role taken on by one of the developers anyway) applies the schema changes for the release to the production database between stopping the old version of the applications and starting the updated ones.</p> <p>This is all quite a manual process, but satisfies requirements such as migrating data from one model to another: e.g. expanding a boolean flag to a set of options, or converting a many-to-one association to a many-to-many. This typically isn't something that can be generated with simple schema-comparison tools anyway. It also allows for role separation- although in practice we all have full access to production, there is enough decoupling there so that the "DBA" can read and review the .sql files to be applied in production.</p> <p>In theory, at least, a complete database (containing only reference data) could be built by simply running all schema changes in order for 000.sql onwards. In practice we don't regularly do this, but rather copy our production database to dev and then apply the change scripts before running regression tests prior to a release. This serves to test the change scripts themselves, but is only practical with a medium size production database.</p>
    singulars
    1. This table or related slice is empty.
    1. PODatabase structure and source control - best practice
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USaraqnid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSounds thorough anyway! I think I'd rather be able to recreate the current production database from a set of 'master' scripts than rely on a lot of change update scripts - I can see how this could get complicated...
      singulars
      1. PO
      1. USPaddy
    2. CO@Paddy as mentioned, recreating the database from scratch simply isn't something we've got a need to do; although that's based on us being able to simply copy the prod database to dev and apply the outstanding changes. by contrast, having to evolve models and migrate data is something we have to do a lot, and you'll always need to write some sort of script to do that if you're making any non-trivial changes.
      singulars
      1. PO
      1. USaraqnid
    3. COTrue. We don't have the option of getting our hands on a copy of the production database(s), so this might not be for us.
      singulars
      1. PO
      1. USPaddy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload