I finally got around to looking at the DDT and I have some questions/comments:
1. I see that you can now install a package and set the db name at runtime. It mentions a new update to do this. My downloads area only has one from 11/20/2006. Does the 11/20/2006 release have this functionality?
2. Is this new feature explained in the new help? If not, how does it work? I see that there is now dynamic naming, e.g. $DbName$. Where can it be used and what else is available?
3. I would strongly encourage you to improve your support migration of existing dbs into DDT. While it makes sense to move business logic into the BOs, thus avoiding the need of triggers, there are also strong reasons to keep the triggers (at least initially). First, in my case, I am migrating an application to SF. The db has 91 tables, 100 views, 156 sprocs, 16 UDF, 56 triggers plus assorted constraints. Importing this db will mean that MOST of the work is by hand and I can't even deal with the triggers, UDFs or non-index/default constraints within the DDT. It does make sense that I could get rid of most/all of the triggers and UDFs eventually, but I don't have time now. I need to do this gradually. The second reason is that not all apps will have their own reporting. There could be an Enterprise reporting tool that is required to be used and it might not be .NET and/or the developer might have NO control of building reports in it (both my case). In this case, sprocs and views are definitely required. So, I'd like to strongly suggest that you include include UDFs, triggers and other types of constraints as items defined by the DDT and that you import ALL of these SQL objects when importing a database, and that this be done sooner rather than later.
4. The comment area for sprocs is quite small and I'm sure it is designed for use within the DDT so users can find a sproc. However, if the sproc is a non-trivial, there will input/output parameters and business logic to describe. If I put the comments in the pre-create/environment code area, will it correctly build the sproc? E.g. if I put
/* MyProc
Description....
IN:
@id - description
OUT:
@status - description
*/
in the pre-create area, will this be inserted before the sproc, so I would get something like:
/* MyProc
Description....
IN:
@id - description
OUT:
@status - description
*/
Create Procedure MyProc
@id int,
@status int OUTPUT
AS
-- sproc code here
GO
5. Related to 4., how would I set comments that would be associated with a view in the DDT. I want something like:
/* Viewname
Description
*/
Create View viewname
AS
Anyway to do this? (This is so users of a view don't have to have the DDT to understand what the view is intended to do, especially helpful with report views used by other reporting tools).
6. How does the DDT handle objects that exist in a db but aren't in the DDT? I.e. lets say I have table1, table 2 defined in the DDT, but not table3. However, table3 does exist in the database. When I deploy the package, what happens to table3? (what about views, sprocs etc.)?
7. How does DDT handle data migration issues? I.e. I have a table, table1. In a later version of the db, it is replaced with table2 and table3. The data in table1 needs to be moved into table2 and table3 (according to some SQL statements). Just to spice things up, I can't set FK or indexes on table2 or table3 until the migration is done. So, if I were scripting it, I would create table2 and table3, run the sql migration scripts, set indexes/fk on table2 and table3, then delete table1. How is this sort thing handled (if at all)?
8. I saw mention of using the DbEngine.dll to run scripts, which is apparently how triggers/udfs are deployed. How does this work?
9. Is there anyway to just deploy data? I.e. maybe I don't mess with the DDT for schema related stuff, just to deploy data. How would that work?
10. I realize that the version in DDT is just for DDT use, but I like to have schema version info stored in the database. Is there any way to access this and plug it into a table, when installing a package? That way, a quick glance at the appropriate table lets me know which version a database is on.
OK, that enough for now