Original Post
Introducing Diddle, my very hackish data definition language for people who don't like to type a lot.
Diddle is about leverage. I took all the repetitive malarky that I go through with defining database layouts and applied DRY to the extreme. With Diddle, you can define a table as easily as writing its name and listing its columns. Primary key constraints are very easy to setup, and foreign key references are automatically included by referencing the foreign table. Backup versions of every table are created, with triggers on all edits, providing a full log of edits over time. And stored procedures are automatically generated getting and setting data for all tables. With Diddle, I can make massive late-stage design changes to my database and not have to suffer the consequences of screwing up my change tracking, foreign key references, or stored procedures.
It's pretty hackish right now (okay, the code is flipping ugly as hell), but it gets the job done and maybe someone could use something like it. But basically, this 24-line-long Diddle script
Gets turned into this 496-line-long SQL script
Diddle is about leverage. I took all the repetitive malarky that I go through with defining database layouts and applied DRY to the extreme. With Diddle, you can define a table as easily as writing its name and listing its columns. Primary key constraints are very easy to setup, and foreign key references are automatically included by referencing the foreign table. Backup versions of every table are created, with triggers on all edits, providing a full log of edits over time. And stored procedures are automatically generated getting and setting data for all tables. With Diddle, I can make massive late-stage design changes to my database and not have to suffer the consequences of screwing up my change tracking, foreign key references, or stored procedures.
It's pretty hackish right now (okay, the code is flipping ugly as hell), but it gets the job done and maybe someone could use something like it. But basically, this 24-line-long Diddle script
#Persons PersonID Guid pk FirstName string LastName string ?BirthDate DateTime#Activity [SPK] Description string fk Persons#AddressTypes -- Home, Business, etc. [LCODE]#Address [SPK] fk Persons fk AddressTypes [TSPAN] Street string City string State string ZIP stringGets turned into this 496-line-long SQL script
if not exists(select * from information_schema.schemata where schema_name = 'dbo')begin create schema dboendgoif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_Persons_dbo_aspnet_Users')begin alter table dbo.Persons drop constraint FK_dbo_Persons_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_Activity_dbo_Persons')begin alter table dbo.Activity drop constraint FK_dbo_Activity_dbo_Personsendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_Activity_dbo_aspnet_Users')begin alter table dbo.Activity drop constraint FK_dbo_Activity_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_AddressTypes_dbo_aspnet_Users')begin alter table dbo.AddressTypes drop constraint FK_dbo_AddressTypes_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_Address_dbo_Persons')begin alter table dbo.Address drop constraint FK_dbo_Address_dbo_Personsendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_Address_dbo_AddressTypes')begin alter table dbo.Address drop constraint FK_dbo_Address_dbo_AddressTypesendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_dbo_Address_dbo_aspnet_Users')begin alter table dbo.Address drop constraint FK_dbo_Address_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_audit_dbo_Persons_dbo_aspnet_Users')begin alter table audit_dbo.Persons drop constraint FK_audit_dbo_Persons_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_audit_dbo_Activity_dbo_aspnet_Users')begin alter table audit_dbo.Activity drop constraint FK_audit_dbo_Activity_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_audit_dbo_AddressTypes_dbo_aspnet_Users')begin alter table audit_dbo.AddressTypes drop constraint FK_audit_dbo_AddressTypes_dbo_aspnet_Usersendif exists(select * from information_schema.referential_constraints where constraint_name = 'FK_audit_dbo_Address_dbo_aspnet_Users')begin alter table audit_dbo.Address drop constraint FK_audit_dbo_Address_dbo_aspnet_Usersendif exists(select * from information_schema.tables where table_name = 'Persons' and table_schema = 'dbo')begin drop table dbo.Personsendcreate table dbo.Persons( PersonID uniqueidentifier not null, FirstName nvarchar(max) not null, LastName nvarchar(max) not null, BirthDate DateTime, sys_CreatorUserId uniqueidentifier not null, constraint PK_dbo_Persons primary key(PersonID))if exists(select * from information_schema.tables where table_name = 'Activity' and table_schema = 'dbo')begin drop table dbo.Activityendcreate table dbo.Activity( ActivityID int not null identity(1, 1), Description nvarchar(max) not null, PersonsPersonID uniqueidentifier not null, sys_CreatorUserId uniqueidentifier not null, constraint PK_dbo_Activity primary key(ActivityID))if exists(select * from information_schema.tables where table_name = 'AddressTypes' and table_schema = 'dbo')begin drop table dbo.AddressTypesendcreate table dbo.AddressTypes( -- Home, Business, etc., AddressTypesID int not null identity(1, 1), AddressTypesName nvarchar(max) not null, sys_CreatorUserId uniqueidentifier not null, constraint PK_dbo_AddressTypes primary key(AddressTypesID))if exists(select * from information_schema.tables where table_name = 'Address' and table_schema = 'dbo')begin drop table dbo.Addressendcreate table dbo.Address( AddressID int not null identity(1, 1), sys_StartDate DateTime not null default(getdate()), sys_EndDate DateTime not null, Street nvarchar(max) not null, City nvarchar(max) not null, State nvarchar(max) not null, ZIP nvarchar(max) not null, PersonsPersonID uniqueidentifier not null, AddressTypesAddressTypesID int not null, sys_CreatorUserId uniqueidentifier not null, constraint PK_dbo_Address primary key(AddressID))if exists(select * from information_schema.tables where table_name = 'Persons' and table_schema = 'audit_dbo')begin drop table audit_dbo.Personsendcreate table audit_dbo.Persons( PersonID uniqueidentifier not null, FirstName nvarchar(max) not null, LastName nvarchar(max) not null, BirthDate DateTime, sys_CreatorUserId uniqueidentifier not null, sys_Operation nvarchar(max) not null, sys_DateCreated DateTime not null default(getdate()), constraint PK_audit_dbo_Persons primary key(PersonID))if exists(select * from information_schema.tables where table_name = 'Activity' and table_schema = 'audit_dbo')begin drop table audit_dbo.Activityendcreate table audit_dbo.Activity( ActivityID int not null identity(1, 1), Description nvarchar(max) not null, PersonsPersonID uniqueidentifier not null, sys_CreatorUserId uniqueidentifier not null, sys_Operation nvarchar(max) not null, sys_DateCreated DateTime not null default(getdate()), constraint PK_audit_dbo_Activity primary key(ActivityID))if exists(select * from information_schema.tables where table_name = 'AddressTypes' and table_schema = 'audit_dbo')begin drop table audit_dbo.AddressTypesendcreate table audit_dbo.AddressTypes( AddressTypesID int not null identity(1, 1), AddressTypesName nvarchar(max) not null, sys_CreatorUserId uniqueidentifier not null, sys_Operation nvarchar(max) not null, sys_DateCreated DateTime not null default(getdate()), constraint PK_audit_dbo_AddressTypes primary key(AddressTypesID))if exists(select * from information_schema.tables where table_name = 'Address' and table_schema = 'audit_dbo')begin drop table audit_dbo.Addressendcreate table audit_dbo.Address( AddressID int not null identity(1, 1), sys_StartDate DateTime not null default(getdate()), sys_EndDate DateTime not null, Street nvarchar(max) not null, City nvarchar(max) not null, State nvarchar(max) not null, ZIP nvarchar(max) not null, PersonsPersonID uniqueidentifier not null, AddressTypesAddressTypesID int not null, sys_CreatorUserId uniqueidentifier not null, sys_Operation nvarchar(max) not null, sys_DateCreated DateTime not null default(getdate()), constraint PK_audit_dbo_Address primary key(AddressID))alter table dbo.Persons add constraint FK_dbo_Persons_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table dbo.Activity add constraint FK_dbo_Activity_dbo_Persons foreign key(PersonsPersonID) references dbo.Persons(PersonID)alter table dbo.Activity add constraint FK_dbo_Activity_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table dbo.AddressTypes add constraint FK_dbo_AddressTypes_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table dbo.Address add constraint FK_dbo_Address_dbo_Persons foreign key(PersonsPersonID) references dbo.Persons(PersonID)alter table dbo.Address add constraint FK_dbo_Address_dbo_AddressTypes foreign key(AddressTypesAddressTypesID) references dbo.AddressTypes(AddressTypesID)alter table dbo.Address add constraint FK_dbo_Address_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table audit_dbo.Persons add constraint FK_audit_dbo_Persons_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table audit_dbo.Activity add constraint FK_audit_dbo_Activity_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table audit_dbo.AddressTypes add constraint FK_audit_dbo_AddressTypes_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)alter table audit_dbo.Address add constraint FK_audit_dbo_Address_dbo_aspnet_Users foreign key(sys_CreatorUserId) references dbo.aspnet_Users(UserId)gocreate trigger dbo.Persons_Tracker on dbo.Persons after insert, update, delete as insert into audit_dbo.Persons (PersonID, FirstName, LastName, BirthDate, sys_CreatorUserId, sys_Operation) select PersonID, FirstName, LastName, BirthDate, sys_CreatorUserId, 'Deleted' from deleted; insert into audit_dbo.Persons (PersonID, FirstName, LastName, BirthDate, sys_CreatorUserId, sys_Operation) select PersonID, FirstName, LastName, BirthDate, sys_CreatorUserId, 'Inserted' from inserted;goif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Get_Persons')begin drop procedure dbo.Get_Personsendgocreate procedure dbo.Get_Persons @PersonID uniqueidentifier = nullas begin set nocount on; select * from dbo.Persons where (@PersonID is null or @PersonID = PersonID)endgoif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Set_Persons')begin drop procedure dbo.Set_Personsendgocreate procedure dbo.Set_Persons @PersonID uniqueidentifier, @FirstName nvarchar(max), @LastName nvarchar(max), @BirthDate DateTime, @sys_CreatorUserId uniqueidentifieras begin set nocount on; if exists(select * from dbo.Persons where @PersonID = PersonID) begin update dbo.Persons set FirstName = @FirstName, LastName = @LastName, BirthDate = @BirthDate, sys_CreatorUserId = @sys_CreatorUserId where @PersonID = PersonID end else begin insert into dbo.Persons( PersonID, FirstName, LastName, BirthDate, sys_CreatorUserId ) values( @PersonID, @FirstName, @LastName, @BirthDate, @sys_CreatorUserId ) endendgocreate trigger dbo.Activity_Tracker on dbo.Activity after insert, update, delete as insert into audit_dbo.Activity (ActivityID, Description, PersonsPersonID, sys_CreatorUserId, sys_Operation) select ActivityID, Description, PersonsPersonID, sys_CreatorUserId, 'Deleted' from deleted; insert into audit_dbo.Activity (ActivityID, Description, PersonsPersonID, sys_CreatorUserId, sys_Operation) select ActivityID, Description, PersonsPersonID, sys_CreatorUserId, 'Inserted' from inserted;goif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Get_Activity')begin drop procedure dbo.Get_Activityendgocreate procedure dbo.Get_Activity @ActivityID int = nullas begin set nocount on; select * from dbo.Activity where (@ActivityID is null or @ActivityID = ActivityID)endgoif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Set_Activity')begin drop procedure dbo.Set_Activityendgocreate procedure dbo.Set_Activity @ActivityID int = null output, @Description nvarchar(max), @PersonsPersonID uniqueidentifier, @sys_CreatorUserId uniqueidentifieras begin set nocount on; if exists(select * from dbo.Activity where @ActivityID = ActivityID) begin update dbo.Activity set Description = @Description, PersonsPersonID = @PersonsPersonID, sys_CreatorUserId = @sys_CreatorUserId where @ActivityID = ActivityID end else begin insert into dbo.Activity( Description, PersonsPersonID, sys_CreatorUserId ) values( @Description, @PersonsPersonID, @sys_CreatorUserId ) select @ActivityID = @@IDENTITY endendgocreate trigger dbo.AddressTypes_Tracker on dbo.AddressTypes after insert, update, delete as insert into audit_dbo.AddressTypes (AddressTypesID, AddressTypesName, sys_CreatorUserId, sys_Operation) select AddressTypesID, AddressTypesName, sys_CreatorUserId, 'Deleted' from deleted; insert into audit_dbo.AddressTypes (AddressTypesID, AddressTypesName, sys_CreatorUserId, sys_Operation) select AddressTypesID, AddressTypesName, sys_CreatorUserId, 'Inserted' from inserted;goif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Get_AddressTypes')begin drop procedure dbo.Get_AddressTypesendgocreate procedure dbo.Get_AddressTypes @AddressTypesID int = nullas begin set nocount on; select * from dbo.AddressTypes where (@AddressTypesID is null or @AddressTypesID = AddressTypesID)endgoif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Set_AddressTypes')begin drop procedure dbo.Set_AddressTypesendgocreate procedure dbo.Set_AddressTypes @AddressTypesID int = null output, @AddressTypesName nvarchar(max), @sys_CreatorUserId uniqueidentifieras begin set nocount on; if exists(select * from dbo.AddressTypes where @AddressTypesID = AddressTypesID) begin update dbo.AddressTypes set AddressTypesName = @AddressTypesName, sys_CreatorUserId = @sys_CreatorUserId where @AddressTypesID = AddressTypesID end else begin insert into dbo.AddressTypes( AddressTypesName, sys_CreatorUserId ) values( @AddressTypesName, @sys_CreatorUserId ) select @AddressTypesID = @@IDENTITY endendgocreate trigger dbo.Address_Tracker on dbo.Address after insert, update, delete as insert into audit_dbo.Address (AddressID, sys_StartDate, sys_EndDate, Street, City, State, ZIP, PersonsPersonID, AddressTypesAddressTypesID, sys_CreatorUserId, sys_Operation) select AddressID, sys_StartDate, sys_EndDate, Street, City, State, ZIP, PersonsPersonID, AddressTypesAddressTypesID, sys_CreatorUserId, 'Deleted' from deleted; insert into audit_dbo.Address (AddressID, sys_StartDate, sys_EndDate, Street, City, State, ZIP, PersonsPersonID, AddressTypesAddressTypesID, sys_CreatorUserId, sys_Operation) select AddressID, sys_StartDate, sys_EndDate, Street, City, State, ZIP, PersonsPersonID, AddressTypesAddressTypesID, sys_CreatorUserId, 'Inserted' from inserted;goif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Get_Address')begin drop procedure dbo.Get_Addressendgocreate procedure dbo.Get_Address @AddressID int = nullas begin set nocount on; select * from dbo.Address where (@AddressID is null or @AddressID = AddressID)endgoif exists(select * from information_schema.routines where routine_schema = 'dbo' and routine_name = 'Set_Address')begin drop procedure dbo.Set_Addressendgocreate procedure dbo.Set_Address @AddressID int = null output, @sys_StartDate DateTime, @sys_EndDate DateTime, @Street nvarchar(max), @City nvarchar(max), @State nvarchar(max), @ZIP nvarchar(max), @PersonsPersonID uniqueidentifier, @AddressTypesAddressTypesID int, @sys_CreatorUserId uniqueidentifieras begin set nocount on; if exists(select * from dbo.Address where @AddressID = AddressID) begin update dbo.Address set sys_StartDate = @sys_StartDate, sys_EndDate = @sys_EndDate, Street = @Street, City = @City, State = @State, ZIP = @ZIP, PersonsPersonID = @PersonsPersonID, AddressTypesAddressTypesID = @AddressTypesAddressTypesID, sys_CreatorUserId = @sys_CreatorUserId where @AddressID = AddressID end else begin insert into dbo.Address( sys_StartDate, sys_EndDate, Street, City, State, ZIP, PersonsPersonID, AddressTypesAddressTypesID, sys_CreatorUserId ) values( @sys_StartDate, @sys_EndDate, @Street, @City, @State, @ZIP, @PersonsPersonID, @AddressTypesAddressTypesID, @sys_CreatorUserId ) select @AddressID = @@IDENTITY endend