Hi Louis,

This is exactly what I have done! (so it is not all that strange)

I used this technique to implement all the new features I needed for our
system.

I thought I was on a fools errand when I began this development cycle
(it is alot of work) but it is nice to know that I am not the only crazy
developer out there.

> This brings me to my request. At the moment, all these DDL manipulation is
> implemented in a client software module. It would be great to be able to
> write SPs to :

I ran up against the same wall you did, but I refused to put the DDL
into the client application.

I had the user populate 'definition' tables with the information that
would create the required metadata.

This would be done with a combination of views, triggers and stored
procedures.

I then had the final SP fire a trigger that would alert a 'BOT' that
would read the tables and implement the DDL statements.

This kept the user side code lite by putting this job in a dedicated
client.
This also made it easier to maintain any new metadata structures by
making all the DDL rules controlled by SP code. The bot simply runs a
SP, that returns a dataset, which is one DDL statement per row. Very
easy to write and no bother to maintain.

I would love for your suggestion to be implemented, it would remove the
need for the bot, but, in the meantime, you may want to try splitting
your code like we did.

(I also put triggers on all the system tables so that only the bot can
alter metadata)

best regards

Dalton

> When the user creates a table, the database application engine then
> automatically creates all the SPs to wrap the particular table with ADD,
> SET, UPDATE and VIEW SPs. As the user edits the table, the SPs change. We
> have also defined a number of other 'datatypes'. The user can define a
> column type to be a lookup (similar to MS Access) to a column in another
> table. In the database this implemented as a FK, but the VIEW SP returns
> the FK value as well as the looked up column value. It essestially
> 'flattens' the two tables with a join. Tables may contain N lookup column
> to a depth of M. We have also created datatypes that automatically maintain
> history in a separate table, 'compound' datatypes, etc. It is important to
> note that all of these 'extra' datatypes are implemented under the hood.
> The user only needs to selects a column type as e.g. lookup into column X
> of table T, define the column as a history column, and Bob's Your Uncle...!
> All the tables to implement the lookup, history, etc. are created and the
> SP updated to present the table as you defined it.
>
> This may sound like a lot of 'unnecessary' work, and believe me, it has
> involved a lot of intricate programming. The needs of the customers that we
> have been servicing change frequently due to the nature of their business
> and we have found it worthwhile to develop this generic backup-end and
> front-end.
>
> This brings me to my request. At the moment, all these DDL manipulation is
> implemented in a client software module. It would be great to be able to
> write SPs to :
>
> CREATE_TABLE(TableName,PKColumnName,PKColumnType);
> ADD_COLUMN(TableName,ColumnName,ColumnType);
> etc...
>
> This will create an 'API' within the database itself. You could do this in
> a middle tier, but I believe it would be better in the DB. From here the
> requirement for DDL in SPs
>
> I realise that what we are doing may be very elaborate and that very few
> people have the need for this, but even MS SQL Server supports this. I
> considered moving to SQL Server, but all the people I talk to still
> maintain IB is better. IB is also my 1st choice, having used it for the
> last 4 years.
>