Advancement : 'Generate Enums from Lookup Tables'

[GoogleCodeExporter]

For anyone interested in the below request from JC and Mike. I'm about to
work on a solution for this as I'd like it to. Technically it will be
different to how JC asked for it ( Ie config section ) , but will work the
same way , with more options which I'll outline in a week or so when I've
built it.

******************************
Transferred from http://forums.subsonicproject.com/forums/t/2448.aspx

# Posted by jcgrubbs on Saturday, January 12, 2008

One of the things that I do frequently in my apps (and I'm pretty sure
others do as well) is build lookup tables in my DB and then create
associated enums to make it easier to specify things at design time.  I
typically just add "None" as the first item in the enum and then I can map
the enum position directly to the ID of the item in the db.  What would be
really really cool is for SubSonic to do this enum creation for you with
SubCommander.  So for instance, you might add something to the App.Config
file's SubSonic config section like such:

<enumTables>
    <add tableName="Lookup1" enumColumn="ItemName" />
    <add tableName="Lookup2" enumColumn="Item2Name"  /> 
</enumTables> 

 And then SubCommander would give you a file similar to the AllStructs.cs
file with an enum for each item specified in the config section.  This way
as your DB changes during development you can easily update these enums in
your app.

Anyway, I'd attempt to add this functionality to my local version of the
SubSonic code, except I wouldn't even know where to begin.  So if anyone
want's to do this or point me in the right direction I'd appreciate it. 

Thanks,
JC
grubbs.jc@gmail.com

# Posted by mikebol on Thursday, February 05, 2009

 I'd like the same feature.  I really like this feature in NetTiers.  Sad
to see this was requested over a year ago though.  I guess there's no hope
for me.

Mike

**********************************************

Original issue reported on code.google.com by cdmdotn...@gmail.com on 30 Apr 2009 at 8:16

[GoogleCodeExporter]

Firstly note that this is erronious if you happen to be using MySQL for you're 
DB
server as MySQL supports enums in the database and in post
http://code.google.com/p/subsonicproject/issues/detail?id=79 I mention that I've
already built this functionality - so this is really just for the other DBs 
like MSSQL

While I looked at initally building the enums from as simple a solution as RC 
had IE:
two tables ( tbNumberOfWheels , tbColor ) and each will be translated into an 
enum
and reading relationships to build the enum datatype and then writing back the
appriopriate values
I quickly realised this would fail for people like me who use multi purpose 
tables IE
one table defined something like ( ID, NAME, TYPE ) values similar to ( 1 , 
'blue' ,
'colour' ) , ( 2 , 'red' , 'colour' ) , ( 3 , 'two weehls' , 'numberOfWheels' ) 

So I've come up with a more usable solution which requires two additional tables

Table1 ( defines the enum )
Columns being :
Name , Table, ValueColumn (Value to save ) , DisplayColumn (enum display) ,
RestrictionColumn , RestrictionValue ( values usable in an in statement : IE ' 
1,2,5' )
so for the above multi purpose table I would have two records / rows
( 'Colour' , 'categories' , 'ID' , 'NAME' , 'TYPE' , '"colour"' ) , (
'NumberOfWheels' , 'categories' , 'ID' , 'NAME' , 'TYPE' , '"numberOfWheels"' )
but could also be defines as ( 'Colour' , 'categories' , 'ID' , 'NAME' , 'TYPE' 
,
'"lightColour","darkColour"' ) which would make an enum using both dark and 
light
colours from the categories table

Table2 ( specifies when a defined enum is used )
Columns being : 
Table , Column , Enum ( the ID value of the corrosponding enum from Table1 )
so i might have values like
( 'Vehiclles' 'numberOfWheels' , 2 ) - defines that the 'numberOfWheels' column 
of
the Table 'Vehicles' is to use the second enum which happens to be defined 
above as
NumberOfWheels
or 
( 'Clothing' 'Colour' , 1 ) - defines that the 'Colour' column of the Table
'Clothing' is to use the first enum which happens to be defined above as Colour

I do plan that the table names and maybe columns as define above can be set in 
the
config section for SubSonic so it's not a fixed table name and fixed column 
names ,
but that would come after it works.

As I mentioned above this is erronious if you happen to be using MySQL for 
you're DB
server as MySQL supports enums in the database and in post
http://code.google.com/p/subsonicproject/issues/detail?id=79 I mention that I've
already built this functionality - so this is really just for the other DBs 
like MSSQL

Some comments and feedback would be great.

Original comment by cdmdotn...@gmail.com on 30 Apr 2009 at 8:47

[GoogleCodeExporter]

I would like to use the same functionality too ... Wander if it has been 
implemented 
already ...

Original comment by yordan.g...@gmail.com on 23 Jun 2009 at 12:08

[GoogleCodeExporter]

Actually I created already one, which is not however based on subsonic. It 
works so 
that a command line executable retrieves the config values from web.config file 
of 
the web project (gets the file path from command line ) pointing it to the db 
table 
( sql server 2008 - ):

CREATE TABLE [dbo].[EnumGenerator](
    [EnumGeneratorId] [int] IDENTITY(1,1) NOT NULL,
    [EnumName] [varchar](50) NOT NULL,
    [ColNameInt] [varchar](50) NOT NULL,
    [ColNameVal] [varchar](50) NOT NULL,
    [TableName] [varchar](50) NOT NULL,
    [ColWhereMeta] [varchar](50) NOT NULL,
    [ColWhereVal] [varchar](50) NOT NULL,
    [StrSqlCommand] [varchar](200) NULL,
    [AssemblyName] [varchar](50) NOT NULL,
    [AssemblyDirPath] [varchar](300) NOT NULL,
    [TargetPath] [varchar](max) NULL,
 CONSTRAINT [PK_EnumGenerator] PRIMARY KEY CLUSTERED 
(
    [EnumGeneratorId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

than it just generates the dll's and copies them to the bin folder of the web 
project.

Here is the Web.Configs: stuff:
    <appSettings>
        <add key="ConnectionStringTemplate" value="Data Source={0:G};Initial 
Catalog={1:G};Persist Security Info=False;User ID={2:G};Password=
{3:G};MultipleActiveResultSets=True"/>
        <add key="GenAppConnectionString" value="Data 
Source=hostName;initial catalog=OCMS_DEV;User 
ID=OCMS_DEV_usrOcms;Password=1a1dc91c907325c69271ddf0c944bc72;persist security 
info=False;"/>
        <!-- the default database we are going to access-->
        <add key="DefaultDb" value="ocms_dev"/>
        <!-- the hosts name and instance as used in the connection string 
for accessing the db-->
        <add key="DbHostNameAndInstance" value="hostName"/>
        <add key="DefaultLoginName" value="ocms_dev_usr"/>
        <add key="DefaultLoginPass" value="pass"/>
        <!-- the type of RDBMS we are going to utilize-->
        <add key="DefaultRDBMS" value="SqlServer2008"/>
    </appSettings>

If something does not work (needs to be reconfigured because of the environment 
... 
drop me e-mail ...) Feel free to delete this attachment if it does not server 
the 
purposes of this project ...

Original comment by yordan.g...@gmail.com on 26 Jun 2009 at 11:37

Attachments:

• GenAppGenerator.zip