October 27, 2010 at 11:24 am
wbrianwhite (10/27/2010)
As mundane as this might seem, these three routines provide some dazzling capabilities:
Not really. A history trigger is a far better choice. The stored proc can only ensure that the archive table has values in synch if that stored proc is the only means used to alter the data. What if someone does it another way? A trigger will fire no matter what means you use to update the data.
I have long considered using triggers instead of the "output" clause in the stored procs, precisely because of this situation here. Of course if you have developers who don't understand the database structure you're pretty much screwed no matter what you do, because of course such people could go around modifying the archive tables directly (without making corresponding changes to the current data), and then even your triggers won't save you.
The one problem that I haven't solved yet with triggers is how to populate the AuditUserId field on a deletion. With insertions and deletions the AuditUserId is being written to the "current" record and so a trigger would have access to it. On a deletion, nothing is being written, and so the trigger would not have access to the ID of the user who is doing the deletion.
October 27, 2010 at 11:37 am
David Ziffer (10/27/2010)
wbrianwhite (10/27/2010)
As mundane as this might seem, these three routines provide some dazzling capabilities:
Not really. A history trigger is a far better choice. The stored proc can only ensure that the archive table has values in synch if that stored proc is the only means used to alter the data. What if someone does it another way? A trigger will fire no matter what means you use to update the data.
I have long considered using triggers instead of the "output" clause in the stored procs, precisely because of this situation here. Of course if you have developers who don't understand the database structure you're pretty much screwed no matter what you do, because of course such people could go around modifying the archive tables directly (without making corresponding changes to the current data), and then even your triggers won't save you.
I agree with wbrianwhite - triggers are better. If you give noone permission to modify the contents of the archive tables, then your developers won't mess up either. The trigger will still be able to add to the archive table, becuase of ownership chaining.
The one problem that I haven't solved yet with triggers is how to populate the AuditUserId field on a deletion. With insertions and deletions the AuditUserId is being written to the "current" record and so a trigger would have access to it. On a deletion, nothing is being written, and so the trigger would not have access to the ID of the user who is doing the deletion.
USER_NAME() returns the name of the database user; SUSER_SNAME() returns the corresponding login.
October 27, 2010 at 11:49 am
Hugo Kornelis (10/27/2010)
USER_NAME() returns the name of the database user; SUSER_SNAME() returns the corresponding login.
Does SS manage to do that when the "client" is a webserver (IIS) which pools connections, especially ones which use one or a few trusted (database) logins?
October 27, 2010 at 12:04 pm
i don't have as much gray whiskers as others, but in my experience the problem isn't reinventing the wheel but adding to it over the years. so and so customer wants some feature so you add a few tables or columns. the business grows into another product area means more tables added. etc. another app comes along that needs a new database but it also needs data from the core databases that have been around for years so again you don't need to reinvent the wheel. just add a spoke here and there from time to time.
the problem is not the SQL most times it's the application code. over the years the core classes will grow to the point where a lot of functionality is concentrated in a few core C# or Java classes and you have to test against a lot of apps every time you make code changes
October 27, 2010 at 1:14 pm
Hugo Kornelis (10/27/2010)
David Ziffer (10/27/2010)
USER_NAME() returns the name of the database user; SUSER_SNAME() returns the corresponding login.
Aha. This would work beautifully if RAP were using the SQL Server user name or login as its notion of a user. But it's actually using its own user table (TBadmUser) on the assumption that you will want a more granular way of designating users. For example the "anonymous" user for everyone who uses a web site, which is what SQL Server would see for all persons using the web sitein a typical public site setup, might not be too informative if you want to keep track of every individual using the site.
This user-table, by the way, is the only data that RAP requires your application to store; aside from this there are no requirements that you implement any particular tables.
October 27, 2010 at 1:23 pm
alen teplitsky (10/27/2010)
i don't have as much gray whiskers as others, but in my experience the problem isn't reinventing the wheel but adding to it over the years. so and so customer wants some feature so you add a few tables or columns. the business grows into another product area means more tables added. etc. another app comes along that needs a new database but it also needs data from the core databases that have been around for years so again you don't need to reinvent the wheel. just add a spoke here and there from time to time.the problem is not the SQL most times it's the application code. over the years the core classes will grow to the point where a lot of functionality is concentrated in a few core C# or Java classes and you have to test against a lot of apps every time you make code changes
The point of such endeavors, and David's is by no means the first (the earliest I'm aware of were COBOL/VSAM generators in the early 70's), is to replace data integrity in code with data integrity in data. After all, your code, ultimately, just implements a bunch of data compares; code logic boils down to data logic in a pretty pink dress. With all logic in data, extension with a code generator is a matter of adding tables and columns, perhaps with triggers/functions/procs. Writing a generator which extracts logic in database code is non-trivial compared to one which just implements DRI on the client. To paraphrase Shakespeare, "first thing we do, is kill all the client coders".
Now, whether any organization would refactor its databases is another question; one which always amuses me, given that coders, virtually en masse, preach "refactor your code". I guess it's a matter of whose ox is getting gored.
October 27, 2010 at 2:30 pm
RobertYoung (10/27/2010)
alen teplitsky (10/27/2010)
Now, whether any organization would refactor its databases is another question; one which always amuses me, given that coders, virtually en masse, preach "refactor your code". I guess it's a matter of whose ox is getting gored.
Hmmm I thought I'd put in a comment here because the thinking on this blog is going to the same place that it always tends to go when I present RAP to people.
If an organization has to be dragged kicking and screaming to refactor its databases, or if developers are grudgingly adopting database object naming conventions in order to get the code-generating features of RAP or something like RAP, then they're going to fail because they are missng the point.
The point is not to have a code generator. Nor is auditing the point. Nor are the twenty-or-so other side benefits RAP.
The point is to impose a discipline which makes all sorts of things possible that weren't possible before. The code generator is just a pleasant side-effect of the discipline - it's one of the new possibilities. RAP's simple ORM is also another side-effect. There are other side-effects that I haven't even explored yet - things that can and will be implemented easily and simply because some discipline was imposed on the database design.
Some of the posts here complain about RAP's naming convention, suggesting that developers should have essentially no systematic regularity to their object names. Well that is fine but unfortunately other people might not be able to figure out your database objects by their names, and you might as well toss out the notion of automatic code generation because it simply isn't going to work. The naming convention is not some unnecessary blight imposed by a control freak (me). Rather it's a discipline that makes tons of things possible. If people can't stand the discipline, then they should feel free to remain mired at our current level of productivity forever.
If an organization does not get the idea that the discipline is the starting point and that the discipline makes possible all the benefits that flow from it, then we are back at square one. Once again I use the automobile analogy. Most of the people working for Ford in 1913 didn't understand the point of all this ridiculous standardization because they were forever thinking at the component level, not the system level. People thought Ford was a fool for imposing all sorts of requirements that had never been imposed before. Why use the same screw every time in the same hole? Why not just use whatever is on hand? Why bother specify the design of a car down to the umpteenth level when hand-improvisation has been working just fine for decades? Why make the car only in black? And on and on.
All these arguments are just lovely until the day you face some serious competition.
October 27, 2010 at 4:23 pm
To repeat what others have said, this has been done over and over.
The thing that makes me most nervous though is how it turns the user
interface into a big table maintenance exercise for the database
developers; and it's hard to think of any reasonable user story
that comes out looking like maintaining tables. CRUD-driven apps
are in my experience a UI disaster.
October 27, 2010 at 5:56 pm
doofledorfer (10/27/2010)
CRUD-driven apps are in my experience a UI disaster.
The display of the UI isn't a function of CRUD-drivenness. All apps are CRUD. Most UI's today are rehashes of character graphics tools like Professional BASIC (and the 3270 long before). The widgets are still pixelated analogs of, well analog, car radio interfaces and Chinese restaurants. Radio sets, check boxes, lists, menus; there are even Dashboards with pixelated knobs and dials, for crying out loud. The sad fact is, there isn't much that's new in business/PC based software. Some of the devices from Apple have implemented new-ish gadgets, but those devices (yet) aren't integrated into business software. The iPad is likely to be the first; although tablets, per se, have been used in warehouse/distribution VAR systems for decades.
Some object that generated CRUD means: BCNF datastores with limited data per interaction, rather than the flood of hundreds of "fields" which replicate the un-normalized messes so beloved by java/C#/VB coders (and COBOL/VSAM forebears; this genealogy mostly unknown to contemporary coders). Phones/Pads will demand more parsimonious data structures in order for applications to succeed; no keyboard and such.
What form the UI takes is dependent on the generator, only; not the schema. It could be VB or html/js/AJAX or iPad or foobar, for all that it matters. BCNF datastores offer *more* flexibility in the display of data than the un-normalized alternatives; since the datastore is by definition fully disaggregated, the UI has the option of aggregating back to whatever level of mess the users demand. They will never know the difference, only that shipping inconsistent data is well nigh impossible.
We need to keep a number of distinctions in mind: the discipline of RAP, the discipline of BCNF/5NF, data logic rather than code logic, server-side rather than client-side control, the UI. There are synergies and antagonisms at work here.
October 27, 2010 at 7:01 pm
doofledorfer (10/27/2010)
To repeat what others have said, this has been done over and over.The thing that makes me most nervous though is how it turns the user
interface into a big table maintenance exercise for the database
developers; and it's hard to think of any reasonable user story
that comes out looking like maintaining tables. CRUD-driven apps
are in my experience a UI disaster.
doofledorfer: RAP does not derive a UI from the underlying schema. It generates code only as far up as the data layer.
The business rules, as will be covered in a future article, are hand-written, as is the UI. However the business rules use a strict set of templates that reduce unnecessary variation in the business layer. The implementer is free to transform the data any way he/she wants in the business rules layer. Therefore RAP places absolutely no restrictions on the nature or even the content of the UI. In fact, RAP doesn't even presume that a human-readable UI even exists (you could just as well be building a web service). Although RAP comes with a demo that has a UI that mimics the underlying table structure, this is in no way a requirement.
The benefit that RAP delivers in its upper levels is that it provides a prototype for a strict MVC pattern. Basically, all business logic and data transformation is done in the business rules layer. Implementers stricly avoid the encoding of any business logic at all in the UI. This makes RAP applications non-UI dependent, i.e. no matter what UI or set of UIs you decide to implement, they get their data from precisely the same underlying business rule structure. Once again, the same themes: consistency, commonality, and miminal code via systematic design.
October 28, 2010 at 1:40 am
David Ziffer (10/27/2010)
Bob the Mushroom (10/27/2010)
Hi David,How configurable are the object names? I know that it's initially unlikely anyone will bother to look into the database if it's getting auto-generated, but auto-generation isn't necessarily an excuse for failing to meet coding guidelines. Anyone who works with me and creates a stored procedure called SPTBadmUser_Delete is going to get it in the neck pretty quickly - exactly the same as any developer who creates a DataSet variable using a ds- prefix. YMMV, but in my opinion UserDelete gets the point across perfectly succinctly, possibly more rapidly for a human reader. I don't need the name to reflect the fact that it's a stored procedure that acts on a table, just the object being modified and the action being performed on it.
Right now RAP is pretty tied to its object name conventions. RAP is delivered in source form so of course if you were inclined you could change its conventions. But as for getting rid of the naming conventions entirely, I wish you luck.
For example, what is "UserDelete"? It makes perfect sense if you already know what it is, but does everyone know what it is? My schema might contain a table called "UserDelete". Or "UserDelete" might be a stored procedure. Probably it isn't a function (because functions can't modify data) but what about "UserFetch"? Is that a function or a stored procedure? Or is that a table too?
Systematic code generation requires totally unambiguous naming conventions. If you try to create a code generator without using a RAP-like system of being totally unambigouous, I predict you'll end up with users who are generating identical names for different objects. In RAP, the odds of doing this are precisely - zero.
Long time ago, maybe five years ;), I've used prefixes for my objects like usp_ or p_, but in nowadays I never had any problem with UserDelete. If you have some naming rules then you will not have to define table UserDelete, at least i will not have. So totally disagree with you. To name a procedure with prefix 'SPTBadm' is something that deserves more than what Bob mentioned. Glad that you didn't say anything about my comments for code style.
You get us wrong, your article have great idea behind, and was pleasure to read it.
October 28, 2010 at 6:01 am
RobertYoung (10/27/2010)
doofledorfer (10/27/2010)
CRUD-driven apps are in my experience a UI disaster.The display of the UI isn't a function of CRUD-drivenness. All apps are CRUD. Most UI's today are rehashes of character graphics tools like Professional BASIC (and the 3270 long before). The widgets are still pixelated analogs of, well analog, car radio interfaces and Chinese restaurants. Radio sets, check boxes, lists, menus; there are even Dashboards with pixelated knobs and dials, for crying out loud. The sad fact is, there isn't much that's new in business/PC based software. Some of the devices from Apple have implemented new-ish gadgets, but those devices (yet) aren't integrated into business software. The iPad is likely to be the first; although tablets, per se, have been used in warehouse/distribution VAR systems for decades.
Some object that generated CRUD means: BCNF datastores with limited data per interaction, rather than the flood of hundreds of "fields" which replicate the un-normalized messes so beloved by java/C#/VB coders (and COBOL/VSAM forebears; this genealogy mostly unknown to contemporary coders). Phones/Pads will demand more parsimonious data structures in order for applications to succeed; no keyboard and such.
What form the UI takes is dependent on the generator, only; not the schema. It could be VB or html/js/AJAX or iPad or foobar, for all that it matters. BCNF datastores offer *more* flexibility in the display of data than the un-normalized alternatives; since the datastore is by definition fully disaggregated, the UI has the option of aggregating back to whatever level of mess the users demand. They will never know the difference, only that shipping inconsistent data is well nigh impossible.
We need to keep a number of distinctions in mind: the discipline of RAP, the discipline of BCNF/5NF, data logic rather than code logic, server-side rather than client-side control, the UI. There are synergies and antagonisms at work here.
I had listed this in a previous post. We use NetTiers with CodeSmith. It is an incredible generator for an ORM. Our team focuses on the design of the DB, business rules, and UI. The generator gives us the plumbing, but we write all of the other parts ourselves. It will generate a web UI, mainly for testing or getting some test data into the database. It is a well thought out ORM.
If we need to change the DB design, we write the SQL code to make the change and put it in the deployment document. We then regenerate to get the changes, and refactor our code where appropriate. Usually, we have a domain library with POCO's and the repository pattern to act as a facade to the ORM. It works well, and we use it in enterprise projects without a problem. In fact, it has saved countless hours of monotonous coding.
October 29, 2010 at 5:56 am
I would not touch this design with a 10-foot pole.
October 29, 2010 at 7:32 am
careusa2003 (10/29/2010)
I would not touch this design with a 10-foot pole.
If you are going to insult a design of which you know little about, at least elaborate. I would love to know what it is that you find so horrible about the design.
October 29, 2010 at 7:50 am
ron.buchanan.us (10/29/2010)
careusa2003 (10/29/2010)
I would not touch this design with a 10-foot pole.If you are going to insult a design of which you know little about, at least elaborate. I would love to know what it is that you find so horrible about the design.
The only vector which makes sense: s/he's a client-side coder who lives by the mantra "we prefer to enforce constraints in our application code". Known lots o such folks. They make messes worse than a puppy. :w00t:
Viewing 15 posts - 16 through 30 (of 59 total)
You must be logged in to reply to this topic. Login to reply