Thursday, August 30, 2007

Mono and ProMesh.NET

I follow the Mono Blog aggregator, and Miguel de Icaza mentioned that a user had tested ProMesh.NET on Mono, and it ran without any changes! That in in itself was cool, but when I took a look at ProMesh.Net, it looked like a well designed framework, perhaps comparable to MonoRail. The framework is ORM agnostic, and the biggest weakness might be it's view templates, but they have a great tutorial, so judge for yourself!

Labels: ,

Tuesday, August 28, 2007

My take on Enums in SQL Server

I am a registered user on SQL Server Central, and I read a good article there on "Enums in SQL Server". I thought that I would quickly present my own humble solution to this problem, although I do not mean to imply that my way is better - you can decide for yourself.

First, the problem to be solved is to represent a limited set of choices, where new choices would typically require a developer's intervention to implement the business logic. An example would be a list of task priorities in a to-do list.

CREATE TABLE Priorities(
ID tinyint not null identity(1,1) PRIMARY KEY,
Code char(3) not null unique,
Priority varchar(20) not null
) ;

INSERT INTO Priorities (Code, Priority) VALUES ('911', 'Emergency') ;
INSERT INTO Priorities (Code, Priority) VALUES ('HIG', 'High') ;
INSERT INTO Priorities (Code, Priority) VALUES ('NOR', 'Normal') ;
INSERT INTO Priorities (Code, Priority) VALUES ('LOW', 'Low') ;
INSERT INTO Priorities (Code, Priority) VALUES ('ATA', 'As Time Allows') ;

The best feature of this lookup table style is that it allows you to choose a key size that is appropriate for the number of data elements, and tiny int is 1 byte. I feel confident that an integer key comparisons will always be as fast as or faster than a char(x) string, since we never have to worry about collation sequences. We do not use the description field or the primary key in our business logic, we use the "Code" field exclusively. This allows us to change the descriptions based on user dictates (and it will happen!), minimize storage requirements in our task table where it matters, and if we ever need to archive the data, just convert Priorities.ID to Priorities.Code. It also means that our business logic doesn't care what data type is used for the primary key, minimizing code disturbances if/when we move from tinyint to smallint.

Having presented my take on the problem, the approach taken in the Enum article has its advantages. Archiving is dead simple - just copy the lookup key. And there is no need to force the code list to be unique since it is a primary key and this property is guaranteed. Also, debugging is simpler, as the developers will not usually have to translate the codes to determine their meaning.

Either way, this avoids a classic problem in programming - mixing usages. I have always felt that we should not let a user assign primary keys to tables, as they are not likely to make unique values, and they tend to want to change their mind, which makes for some miserable modified key update code that ripples through your database, touching related tables that otherwise would not care that (for example) a user has changed their name.

Labels:

Monday, August 20, 2007

Auditing a SQL Server database

I do not believe that I have covered this before, but the Active Record Code Generator can also generate audit records for your database tables. The template that I wrote (SqlAuditTrigger.vm) adds records to an AuditChanges table, with a related AuditFields table containing the table and field name. I think that this is a fairly compact solution, but I also added code to the trigger to ignore SA (system administrator) changes. This may not be appropriate for your application, but it is easily removed. I also have a "TODO" item to detect related lookup tables (really ENUMs) and translate IDs to Names, so that changes to the FavoriteColor_ID field would look at the FavoriteColor table, and instead of recording FavoriteColor_ID changed from 2 to 5, it would say changed from "Ruby Red" to "Passionate Purple". The reason that I did not actually implement it is that I do not have enough information. My code generation schema does not list all of the fields in related tables, so I can not tell a MasterCustomerList from a FavoriteColor or ServiceType table.

Today on SqlServerCentral.com, there was an article describing using XML and XSLT to generate triggers and his implementation used one audit table for each real table.

As with any problem, there are many solutions, and the best solution is the one that meets your needs and is maintainable. Don't forget to adjust the security and access level for your audit tables (read-only or deny access to regular users).

Labels: ,

Friday, August 17, 2007

I love Bouncy Castle!

Today, I needed to send some private data to another company, and I could not get FTP with SSL to authenticate over our firewall, so I had to sent an encrypted file over FTP. The other company has a GPG public key, so I looked around for a bit on the web (yes, I googled c# gpg encrypt open source) and found a message that mentioned Bouncy Castle on the 4th or 5th page (why was it so far down the list???). I then googled Bouncy Castle to finally find that they have a wonderful c# implementation of GPG encryption (and many others), and their test code includes an example that reads a file and writes an encrypted file. This made integration VERY simple. I did move their test folder into their test project, so I would not be deploying all of the test code to production.

Labels: ,

Monday, August 13, 2007

What happened to VS2005 Data Pro Power Tools?

I was reading a blog on MSDN, with a link to some cool Power Toys for Visual Studio 2005 Team Edition for Database Professionals. So today, I had some time to install the VS2005 TE Service Release 1 so I could grab the power toys, but the microsoft link is dead! The developer went on a vacation (literally), so it might be a while until this gets fixed.

Oh, by the way, O’Reilly Hacking Visual Studio mentions a cool documentation tool called GhostDoc. It looks slick!

Update: Here is the link, thanks to Michael!

Saturday, August 11, 2007

NHibernate hbm xml template

Friday, I added an NHibernate mapping.hbm.xml template. I mainly wanted to show that the code generator can be used for other purposes than just Castle ActiveRecord, and NHibernate is an easy target. Jim R. has requested that I add HasMany (aka ManyToOne) mapping properties and attributes. I think this is going to push me to refactor the schema extraction code.

Some of my future goals for the application are:

1. Full Model Generation (list of children, whether directly or indirectly through a many-to-many table)
2. User controllable plural-to-singular mapping.
3. Multi-database, using the Database Provider Factory.
4. Refactor code generation into a library (as I do with all projects)
5. Command-line code generation (supply at least a template and a list of tables)
6. Templates for other ORM tools (pure NHibernate, Rocky Lhotka's CSLA Business Objects, etc.)

Labels: ,

Wednesday, August 08, 2007

Improved Model generation in Code Generator

I have improved the Inequality test in the model template, as requested by JimR. This was another case where it worked well for me, only because I followed the convention that primary keys are named ID, and foreign keys are named SingularOfTable_ID. I know that I should really push the Inequality test into the template, but for now, I have corrected it in the DbFieldInfo class.

Labels: ,

Monday, August 06, 2007

Features of the Code Generator

I just updated my code generator to optionally generate validation attributes. This simple change includes App.config file entries for all check boxes, and a new checkbox for "Validation" - aka validation attibute generation. While I was making this change, I realized that I really need to pass a CodeGenerationContext object to the DbTable, DbField and ModelGenerator classes. The requester can populate the context, and pass it to the code generator.

Anyway, enough about the code, let's talk about the templates. I made a simple template this weekend to generate a DataGridView column array, suitable for databinding. I'm sure my new template will need some tweaks to handle Foreign Keys better (it currently just displays them as TextBox). Let's look at a template.

##FILENAME:PR_${table.GetClassName()}_Insert.sql
##
## Generate a SQL stored procedure to insert a record into the
## specified table and return the newly created primary key
##
## FUTURE: The generated procedure can support journalling, etc.
##

#set ($procname = "PR_${table.GetClassName()}_Insert")

#set ($prefix = '')
#set ($lbrace = '[')
#set ($rbrace = ']')
#set ($paramtypes = '')
#set ($nfields = '')
#set ($params = '')
#foreach($f in $fields)
#if (!$f.IsPrimaryKey())
#set ($nfields = "$nfields$prefix$lbrace${f.Column_Name}$rbrace")
#set ($paramtypes = "$paramtypes$prefix@p${f.Column_Name} ${f.GetSqlType()}")
#set ($params = "$params$prefix@p${f.Column_Name}")
#set ($prefix = ', ')
#end
#end

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[$procname]')
AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[$procname];
END;

## we are executing the stored procedure as a string, so double up on any quotes.
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[$procname]
($paramtypes)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].$lbrace${table}$rbrace ($nfields)
VALUES ($params) ;
SELECT @@IDENTITY AS ID ;
END;
'
GO

The lines with ## are comments, but line 1 is actually a second template, and will be used to generate the output filename. Below the comment section is a block of variable assignments that set a field list $nfields, parameter list with data types $paramtypes, and un-typed parameter list $params. Once we have these variables, it is quite easy to write our SQL stored procedure. You might notice that the stored procedure is preceded by a conditional DROP PROCEDURE statement, and the new CREATE PROCEDURE is executed as a string. Have you noticed that we have not mentioned "Active Record" yet? If a person knows NVelocity well enough, they can create a template to do some amazing things. The only requirement is that the template can derive it's functions, properties, method calls, etc. from the basic table name, primary key, foreign key, SQL type and .Net data type information.

If you are looking for inspiration, try writing an active record partial that contains a custom Find(string field1, string field2, etc.) with NULL values ignored in the internal Criterion list. For extra credit, break out the criterion array building into a Find_Query(...) method that can also be used to retrieve a count instead of finding the records.

Thursday, August 02, 2007

ActiveRecord Code Generator

Thanks to some recent interest in the community, I have made a few more changes to ActiveRecord Code Generator. First, I added a browse button and a dialog, using some code supplied by Jim R. I don't care for the folder browser dialog, but it is built in to .Net 2, so I use it. Second, I fixed a few bugs that crop up when a table does not match my arbitrary table and field naming scheme. This project was written quickly as a pre-requisite to a large project, and it shows, but it is steadily improving.

Evening Update: I added a POCO (Plain Old C# Object) template as a starting point for an NHibernate cs and xbm template set.

Labels: ,