Dear netTiers Community,
Thank you for providing netTiers. It has saved me many hours of development time and it is a joy to work with. Attached is a new CodeSmith template (not related to netTiers directly) which can generate SQL database scripts from a single xml file. If the community wishes to include it n netTiers and wrap it in a Creative Commons license... that's fine.
kind regards,
-Casey Plummer
---------------------------------------------------------
DatabaseGen
Written by Casey
Plummer
Email: plummer [AT]
mailbag.com
Summary
DatabaseGen is a
CodeSmith template that will read an xml file which defines a SQL database, and
generates SQL scripts to populate the database with tables, columns, relationships, views, and
indices. It was built to run as a precursor
to netTiers (www.nettiers.com), and so it does not generate any stored
procedures.
Requirements
- Microsoft SQL 2005
- CodeSmith 3.2 or
higher
- Visual Studio 2005
- .NET Framework 2.0
- NAnt and NAntContrib
(binaries are already included to make it easier)
Installation
- Extract
the zip file into a folder of your choice.
- Open
the DatabaseGen.sln solution file.
- Fix
the CodeSmith dll references by pointing to your local installation of
CodeSmith (or from GAC).
- Build
the solution to verify success.
- Open
and run the DatabaseGen.Demo\Schema\CreateDatabase.sql file to create the
Demo database. You may need to adjust local paths.
- Open
the DatabaseGen.Demo\Build.xml and verify that the paths to the tool
executables are correct (SQL, Visual Studio, and CodeSmith).
- Open
a command prompt and run DatabaseGen.Demo\ GenerateSchema.bat
- Verify
that the database is now populated with new schema objects.
Usage
DatabaseXmlSchema.xsd
is a schema of a schema. To be clear, it defines the schema of an xml file
which then defines the schema for an SQL database. It was developed by referencing the Microsoft
SQL help files. In some cases, elements are defined which were never
implemented in the CodeSmith template and are just ignored.
Schema.xml is a
schema that defines a SQL database. This
is the file where you define all your tables, views, etc… for your application. This package includes an example file which
demonstrates almost all of the supported functionality.
Build.xml is a NAnt
build script, which greatly automates the generation process.
Features
- Creates
tables, columns, relationships, indices, views.
- Supports
data types, column lengths, identity columns, auto increment, increment
seed, default values, unique constraints, descriptions, formulas, schema
binding views.
- Supports
a “Common” table definition which has columns that should automatically be
included in each table.
- Supports
“Common” views to create a particular type of view for all tables.
- There
is a special FormatInfo record to define the naming standard formats for
your database.
Notes
When viewing the
Schema.xml file, right click and “View DataGrid” to make it much easier to
edit. Also, if you do NOT see a little
[+] box next to each row, the Schema.xml file has become dissociated with the
DatabaseXmlSchema.xsd file. To
re-associate, switch back the Xml view, and in the Properties windows, select
“Schemas” and browse to the DatabaseXmlSchema.xsd file.
Unfortunately, right
now if the Schema.xml file has a mistake it can be very hard to validate and
find what needs to be fixed. I would suggest creating a Schema.xml file
incrementally. Make small changes, and
regenerate to make sure it still works.
Repeat.
The usage of this
template is best when you are starting a brand new database schema. Otherwise, if you are going to retrofit this
into an existing application, I would highly recommend that you use RedGate’s
SqlCompare to make sure that no schema differences are lost.
I wrote this to solve
my database needs for several of my applications. I believe it is fairly
generic and can be applied to almost any database design, but your ideas on
this may vary. Please give me feedback.