Category: Uncategorized

Further schema comparison

~ chriseyre2000 ~ Leave a comment

The following is an extension of the previous post.

This is useful when dealing with sql server 2005 and custom schema’s.

(That is the prefix before database objects, not the structure of the database).


select tableName=SO.name, schemaName = SS.Name

from sysobjects SO

join sys.schemas SS ON SO.uid = SS.[schema_id]

where type = ‘U’

order by SO.name

Comparing Database Schema in SQL Server and Sybase

~ chriseyre2000 ~ Leave a comment
A common development problem is to keep the schema of two databases in step.

This lists the tables:

    select tableName=name from sysobjects where type = ‘U’ order by name

This lists the fields per table:

    select tableName = so.name,
       colName = sc.name,
       colLength = sc.length,
       colOrder = sc.colid,
       dataType = st.name 
  from sysobjects so
 inner join syscolumns sc on sc.id = so.id
 inner join systypes st on st.usertype = sc.usertype
 where so.type = ‘U’
 order by so.name, sc.colid
   
Between the two you have the tools needed to compare two database schemas.
The only restriction is the database access.

This does not compare contents – that is a different problem.

Making TFS into a useable VCS

~ chriseyre2000 ~ Leave a comment
I have just started using TFS as a version control system at work.

So far I have found a few flaws:

  • It does not support keyword expansion so you can automatically keep the version number in the header.This is a pain as I have a database versioning scheme that relies on this.
  • The UI in Visual Studio is rather limited compared to most other VCS clients.  It is heavily geared around editing everything via Visual Studio.
I do have solution to the UI limitation: Use TortoiseSVN plus SVNBridge.  This allows you to use the popular and free SVN client with the TFS backend.

This allows a more natural means of working (folders don’t need to be added to the repository first) plus you get an indicator of what you have changed or added.

A good read

~ chriseyre2000 ~ Leave a comment
I have just finished reading “Applying Domain-Driven Design and Patterns” by Jimmy Nilsson.

This is the closest that I have seen anyone come to the approach that I have discussed here.
One of the big differences that he aims to have a model that can be saved at any time.
The approach that I had was to have a model that could take what ever input the user gave (irrespective of the ability to save).  The problem comes when you are missing a mandatory field or if the user supplies XXX to a numeric field.

He opted to use an object relational mapper where I used code generation to create a distinct data access layer hierarchy.  He covers the context problem for business rules – but insists that they are part of the model.  My take is that the business rules do form part of the model but must be attached to the entities.

What to do when your tool tries to do too much

~ chriseyre2000 ~ Leave a comment
I have started using SSIS to propagate 40 reference tables from one system to another.
SSIS reminds me of Crystal Reports – it works well for the simple cases and does have tools to allow complicated options.  However the complex parts become very fragile.

For crystal reports I eventually took to putting as much of the logic as possible into a view or a sproc so that crystal only requires a simple select plus a few formatting options.  If you treat Crystal as a presentation layer you get two benefits – far less debugging plus a possibility of moving to a better option when one becomes availbale.

SSIS is similar.  I was having a lot of trouble coping with moving unicode data around.  The best solution that I could come up with was to alter the select commands to cast the data to the right type.  SSIS can perform automatic name matching if the column names only differ by case.

My transformation goes:

Select from Sybase -> Staging Table
Call Proc that updates/inserts the real table from the Staging Table.

How much is SSIS actually buying here?  A custom app could do all this and I could define the mapping in a seperate table (which I can’t do with SSIS).  In addition the error handling in SSIS is a bit of a joke.  Eventually it comes down to using the profiler if a problem is detected.