Subsonic DAL fails compilation on columns which are keywords

Question

I'm using Subsonic 3 (Active Record), VS2010, Framework 3.5 against a MySQL database. Someone named a column "string." I S@*t you not.

OK, I've named some database objects some dumb names (Like naming a SQL Server table "User") before but... c'mon! And I cannot change the table because of dependencies in the app!

OK I feel a little better now that I've vented a little thanks for listening

So, of course, in the generated code I get errors all over the place from lines like:

private string string {}

I don't see this as a priority bug for Subsonic unless others are having similar issues with other column names. Any plans to do anything about this?

Thanks

Paul

p.s. I will try to talk the other developers into changing, or allowing me to change the column name, I know that is the real solution, but Subsonic could gracefully handle illegal names, maybe something like the way .netTiers handles it (add _safeName to the name, so it would become string_safeName).

Answer 1

You can fix this yourself very easily.

Open up the SQLServer.ttinclude file. It's a T4 template file that SubSonic uses to generate your code.

Almost halfway down, on line 155, you will find the LoadColumns function:

List<Column> LoadColumns(Table tbl){
    var result=new List<Column>();
    var cmd=GetCommand(COLUMN_SQL);
    cmd.Parameters.AddWithValue("@tableName",tbl.Name);

    using(IDataReader rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)){
        while(rdr.Read()){
            Column col=new Column();
            col.Name=rdr["ColumnName"].ToString();
            col.CleanName=CleanUp(col.Name);
            col.DataType=rdr["DataType"].ToString();
            col.SysType=GetSysType(col.DataType);
            col.DbType=GetDbType(col.DataType);
            col.AutoIncrement=rdr["IsIdentity"].ToString()=="1";
            col.IsNullable=rdr["IsNullable"].ToString()=="YES";
            int.TryParse(rdr["MaxLength"].ToString(),out col.MaxLength);

            result.Add(col);
        }

    }

    return result;
}

Simply add logic here in this function when it assigns the name to change from the column name in your database, "string", to something a little more sane.

List<Column> LoadColumns(Table tbl){
    var result=new List<Column>();
    var cmd=GetCommand(COLUMN_SQL);
    cmd.Parameters.AddWithValue("@tableName",tbl.Name);

    using(IDataReader rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)){
        while(rdr.Read()){
            Column col=new Column();

            var rawName = rdr["ColumnName"].ToString();
            if (rawName.Equals("string")) {
                col.Name="StringColumn";
            } else {
                col.Name=rawName;
            }

            col.CleanName=CleanUp(col.Name);
            col.DataType=rdr["DataType"].ToString();
            col.SysType=GetSysType(col.DataType);
            col.DbType=GetDbType(col.DataType);
            col.AutoIncrement=rdr["IsIdentity"].ToString()=="1";
            col.IsNullable=rdr["IsNullable"].ToString()=="YES";
            int.TryParse(rdr["MaxLength"].ToString(),out col.MaxLength);

            result.Add(col);
        }

    }

    return result;
}

Also, were you to put some time into it and make it handle all keywords (say, through lookup in a static dictionary and change the names by appending a common suffix or dictionary substitution), then you could submit back a patch to the project and contribute instead of trying to talk the other developers into doing it.

Answer 2

I perhaps used the wrong language. Before I went ahead and made modifications, I wanted to make sure this was not all ready on some priority list somewhere or being worked on. Here's the way I went with it.

In Settings.ttinclude:

string[] reservedWords = new string[]{"abstract", "as", "base", "bool", "break", "byte", "case", "catch", "char", "checked", 
            "class", "const", "continue", "decimal", "default", "delegate", "do", "double", "else", "enum", "event", "explicit", 
            "extern", "finally", "fixed", "float", "for", "foreach", "goto", "if", "implicit", "in", "int", "interface", "internal", 
            "is", "lock", "long", "namespace", "new", "null", "object", "operator", "out", "override", "params", "private", 
            "protected", "public", "readonly", "ref", "return", "sbyte", "sealed", "short", "sizeof", "stackalloc", "static", "string", 
            "struct", "switch", "this", "throw", "try", "typeof", "unit", "ulong", "unchecked", "unsafe", "ushort", "using", "virtual", 
            "void", "volatile", "while", "FALSE", "TRUE", "yield", "by", "descending", "from", "group", "into", "orderby", "select", 
            "var", "where" };

 string CleanUp(string tableName){
    string result=tableName;

    //strip blanks
    result=result.Replace(" ","");

    if(reservedWords.Contains(result)){
        result += "_SafeName";
    }
    //put your logic here...

    return result;
}

The CleanUp function already fixes table and column names, so I put my logic there. I hope I got all the reserved words...

This is also on a smaller project where the database is MySQL.

So this hack/patch is only for c#/mysql, but very easy to move to other DBs/languages.