Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Writing Nearly Codeless Apps: Part 4 Expand / Collapse
Author
Message
Posted Wednesday, October 27, 2010 9:42 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:01 PM
Points: 76, Visits: 232
Phil Factor (10/27/2010)

I'm all in favor of relieving the need for repetitive coding in SQL, and I've published some articles on the subject, but I'm convinced, after many years of using these sorts of techniques, that there are limits to what can be achieved this way.



I'll be bold enough, and not for the first time, to predict that SSD will finally be understood to be the tool which supports BCNF/5NF (and not just a tool to speed boot). With such "complete" schemas, code generation from same is fully feasible. After all, as Date and Pascal have asserted, "a row *is* a business rule". Celko has also published "Thinking in Sets", which explains the use of auxiliary tables. Again, with such on SSD, performance is no longer an issue (modulo that one could get similar performance on rust with hundreds of short stroked parts).
Post #1011661
Posted Wednesday, October 27, 2010 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
RobertYoung (10/27/2010)

The long standing issue with schema changes (code generated or otherwise) is what to do with existing table data. With code generation, there really does need to be a hard and fast rule: if Sally Business Analyst wants a Foo column added to the Bar table she has to either identify the default value or accept that the column is Nullable. Sally, most often, hasn't a clue what the answer is, either way. Generation doesn't create the problem, but when coders are in charge, the problem keeps getting fobbed off into byzantine code loops. Generation demands an up front answer. The needy BA's of the world don't like having to be explicit.

RAP does not presume to actually alter your tables for you, and in fact other than providing a preprocessor to help enforce the consistency of primary keys and status fields, it is not involved in the process of schema generation. The kind of capability you describe transends even modern schema design tools, which generally do nothing for you other than generate the new DDL after you've changed the schema.

In order to preserve your data and make the kinds of new-field-initialization decisions you describe requires intimate knowledge of the purpose of the fields. Until such time as we have schema design tools that understand the intent (and not just the pattern) of your design, this task will need to be done by hand.

Similarly, neither RAP nor any other design tool that I know of would have the ability to modify your business rules and your UI in accordance with your schema changes. These sorts of changes could be made only by a comprehensive application design tool that truly understands what you are trying to do. Such a thing could sit on top of RAP, but it is well beyond the scope of RAP.

What RAP does do is to relieve you of the nightmare of trying to track down all the places in the database and the data layer that are impacted by your schema change. It does this by simply and comprehensively regenerating all the code it originally generated when you created the app.
Post #1011683
Posted Wednesday, October 27, 2010 10:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
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.
Post #1011734
Posted Wednesday, October 27, 2010 10:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
Boban Stojanovski-455883 (10/27/2010)
Totally agree with Bob.
What I also didn't like on generated code from RAP is the code style how procedures are defined.
I use my codesmith templates for this job, and not just for creating stored procedures.

Boban: Please see my response to "Bob the Mushroom" above. -Dave
Post #1011736
Posted Wednesday, October 27, 2010 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152

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.
Post #1011759
Posted Wednesday, October 27, 2010 11:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
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.
Post #1011773
Posted Wednesday, October 27, 2010 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 6,043, Visits: 8,324
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1011781
Posted Wednesday, October 27, 2010 11:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:01 PM
Points: 76, Visits: 232
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?
Post #1011792
Posted Wednesday, October 27, 2010 12:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1011801
Posted Wednesday, October 27, 2010 1:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
Hugo Kornelis (10/27/2010)
[quote]David Ziffer (10/27/2010)

[quote]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.
Post #1011834
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse