Tip: TMS Security System template

It would be nice if you could add all tables needed for TMS Security System with one simple command (nice for cross-selling) :)

That is a good suggestion. We'll consider this for a future update.

I would provide standard data structures as templates, but unfortunately, the data modeller can not support them or diagram them.

Dalton, what do you mean by "template" when you say data modeler can not support them?

Hi Wagner,


I use standard templates for a great deal of my code.   I have domains, triggers, views, tables etc.

Now, lets forget the lack of support for database triggers (on connect, on transaction etc) and just look at standard items.

When I define a domain, it may depend upon the existence of a procedure, table or view.   
So, before I can define a domain, the procedure, table or view, must exist in the database.
Those objects may in turn depend upon the domain being defined.

In order to script this sort of dependency, there is a specific order of create and alter statements that must be followed and the datamodeller does not follow that procedure, so the scripts that are generated all have to be edited by hand before they can run.

In addition, there is no way of diagramming the various dependencies as the data modeler has no method of representing other types of database objects within the diagram.

Here is a perfect example.

I have a firebird database.   It has a series of views defined within it.   It has some local UI preference tables and a single table that details how the database is to connect to the rest of the data cloud.

When you perform a select upon a data table, it gets it's data from a stored procedure, that stored procedure looks up where the data is that the user is requesting and connects to a remote database that holds the actual data.   The insert/update/delete triggers on the view also connect to stored procedures that perform the appropriate action on the remote database.    There are also rules that define what to do if the remote database becomes unavailable, rules on connecting to another database and replaying any transactions that did not get replicated before the disconnection, including rules that perform reconciliation of transactions that may occur due to the database being/operating  offline for a period of time.

Now, the aspects of security, logging, replication etc all being handled inside the database stored procedure language and triggers, are all standard templates of code that are effectively just a library that is common across multiple databases and can be manipulated/generated by the system.

On top of that, standard data structures for accounting, communications, security etc are all possible as templates, but due to the amount of dependent code that is required to have them properly separate the OLTP and OLAP functionality are not possible to adequately diagram/document with the data modeler as it currently stands.

Is that clear?  I can go into more detail if you wish.

We tried to build the whole script to not have objects being created before other objects which they depend on.

I agree that there might be some problems with this algorithm, in this case I would appreciate if you could point me out some exact examples (a bug report).
For example, a domain is always created before a table, because a table might have some fields that belongs to that domain.

Hi Wagner,


The best solution is to create the objects without bodies than alter them later - I can provide exact syntax for this for you.

For example

create domain "IAMADOMAIN" as integer;
alter domain "IAMADOMAIN" set default 0;
alter domain "IAMADOMAIN" add constraint check (value >0 and value <1000);

or
set term ^^;
create procedure "IAMAPROCEDURE" ("INPUTONE" "IAMADOMAIN", "INPUTTWO" "IAMADOMAIN")
returns ("OUTPUTONE" "IAMADOMAIN")
as
begin
/placeholder text/
end
^^
set term ;^^
commit;
set term ^^;
< ="-" ="text/; =utf-8">
create procedure "IAMAPROCEDURE" ("INPUTONE" "IAMADOMAIN", "INPUTTWO" "IAMADOMAIN")
returns ("OUTPUTONE" "IAMADOMAIN")
as
declare variable "MYVARIABLE" "IAMADOMAIN";
< ="-" ="text/; =utf-8">
begin
  {Procedure body}
end
^^

Etc....

Now the order of declaration would be

create database statements
alter database statements
create roles
create users
create filter statements
create function statements
create schema statements
create domains
create procedures
create tables
insert data into the various tables
create views
create triggers
insert comments
alter domains
alter procedures
alter users (adding to roles etc)
alter grants
create database triggers
insert comments on the database triggers

There are a few other steps but the above would cover 99% of the needs.

I wish your data modeler source was part of the subscription as I would add the features/bug fixes myself.
I have bought the data modeler and the vcl source subscription.   Even if you put in a license condition to restrict resale, it would be worth my while as your product is close to what I need, but still missing so many features.

best regards

Dalton
< ="-" ="text/; =utf-8">< ="-" ="text/; =utf-8">< ="-" ="text/; =utf-8">< ="-" ="text/; =utf-8">< ="-" ="text/; =utf-8">

Thanks for the info, we will investigate further on this. Although I wonder if we couldn't have a specified order and then just create the whole objects, not without bodies.

About source code, it's something we are also considering, but not decided yet. Nevertheless, it won't apply to VCL Subscription because those are only related to Delphi VCL components.

Hi Wagner, 


The problem is that you can easily get circular references in the design - procedure A needs procedure B which needs Procedure C which in turn needs procedure A.   No amount of analysis would determine a correct creation order.
But, if you create the domains/procedures without constraints/bodies then on a second round, populate them, you do not care about creation order as it all takes care of itself.   It is easier to implement and can not be broken by complex designs.

As for the source, I agree with you in terms of the subscription, BUT as the code base would benefit from/require most of the components in the VCL subscription, it would be a benefit if the source was made available to those who have purchased the datamodeler AND the vcl subscription.

I just wish you had a single global license for your products.   If I buy the subscription, I get one set of components, If I buy the component pack, I get a different set (notably the intraweb components).    

Just a thought.

best regards

Dalton