use Perl

• Databases and Data Logic (Score:1)

  by n1vux (1492) on 2006.02.17 16:20 (#46187) Homepage Journal

  Short answer -- If you have DBAs who earn their big bucks, do it their way. If not, ask why not; and do it so that your next project will be easier. Data logc belongs in the DB. Logic specific to a specific use of the data belongs in the specific application.

  Business logic in the application:
  Stored procedures and triggers are typically hidden. DB Neutral

  Logic that is data centric belongs with the Data, so that it's programming language neutral. I'm more likely to have programs in two different languages accessing the same data than to change vendors. Just because I'm using Java for the GUI I should have to use Java DAO's for the batch processing ?? I don't think so, says the Perl guy.

  Stored Procs may hide the SQL from the programmers, but embedding the SQL in the 3GL hides the SQL from the DBAs who may be able to optimize the SQL. Same problem as embedding Perl in HTML or vice versa -- a content management system that lets you keep HTML templates so web designers can design 'em without breaking their template-ness and without breaking the embedded Perl (or Python with it's magic whitespace *shudder*) is easier for big projects. Same with big DB projects, you want to have the SQL where the experts can get at it without breaking the Perl or Java.

  Business logic in the database:

  If the DB has a rich enough Data Dictionary and Relational Integrity, use it. From Day 1 ... it's hard to turn RI on after you've got any data at all. As much of the data semantics as will fit in the DB is better off there, because it's done once, and in a tool designed for it: a DataBase.

  How often have you ported an app from one database engine to another (e.g., MySQL to Oracle)?

  We've moved a number of apps from MS SQL Serer to Sybase, and from Sybase and MS SQL Server to DB2. I expect no difficulty moving a small DB from MS Access to MySQL when I get the Tuits. Moving to or from ORACLE can be hard; if you need Oracle's richness, use it and love it. If you don't need Oracle's richness, be very very careful about using features that will keep you captive. Moving between the less-extended SQL DBs is easier.

  (ex-Oracle users keep asking IBM when they'll add HINTS to DB2. The answer always is that the DB2 optimizer doesn't need your hints, would only be thrown off by them; and if you want a feeble optimizer that needs to be micro-managed by hints so you feel important, you can go back to using Oracle. DB2 admins have better things to do, like optimizing the IOs to increase throughput, or adding an index where even the optimizer can't do better than a tablescan.)

  Coding those non-portable Triggers, Stored Procs by hand will require adjustment on a later port. If that's what it takes, it's worth it. But using tools is better.

  If using professional DBA tools, those are all auto-generated from the design drawing, porting is a snap. I haven't fully checked the capabilities of the FL/OSS DBA design tools, I don't know how much of the Trigger/StoredProc/RI feature set they support yet. I may need to check it out soon, since I need to port some data from MySQL to MySQL. It will be a hard port because the PHP WebSite schema changed between versions ... different problem

  How often have you had completely separate apps writing to the same data store (using completely different code bases)?

  All the time! That's life in the big city. Big organizations have legacy systems whose databases need modern GUIs. There are the GUI/On-Line apps for the end-users and the Batch / Real-Time apps that communicate with other applications that provide bulk-updates.

  Do you depend on non-portable database features?

  Since I learned SEQUEL back when it had vowels in the name, before ANSI/ISO, I usually code in a very portable subset of SQL :-). The first rule of optimization is DON'T. The second rule is for experts only: DON'T OPTIMIZE YET. If I need to use a non-portable feature to do something, I will. Sadly, Stored Procs are still not quite portable ... but the SQL inside them can be. I use Stored Procs on projects where we have real DBAs. Tools that generate good queries on the fly are good too.

  Have you experienced programmers damaging your data because the logic wasn't in the database layer?

  Sure. If you haven't, you haven't worked on a big project. Even if you have a DB Access Object Layer in the application, someone will code direct to the DB. (Those wiley perl hackers, avoiding calling the JavaDAO!) This is why Stored Procs and Relational Integrity and Triggers will save you on a BIG project. On a small project, one that Catalyst can handle on it's own, all that is of course overkill. But Relational Integrity rules.

  ASIDE#1 -- The best database I ever used was WANG PACE. It claimed to be relational, but had some pretty OO features before there was a name for that other than "Post-Relational": It was really class/method oriented without the OO Jargon. The rich Data Dictionary cataloged Types for columns by type names, so natural joins could match the FirstName, LastName fields by type even if the column names were different. Types, columns, tables could all have stored procs (exits, methods) registered for and triggered on events. These "exit procs" could be coded in your choice of SQL, C/C++, or COBOL -- interoperably. Relational integrity was a given; a checkbox at on table-def says to reject add w/o parent or auto-create parent; another says to drop parent if last child deleted or not. With RI having Foreign Key declarations, it was trivial to generate default CRUD screens (Create, Retrieve, Update, Delete) with drill-detail, multiple lines of detail, etc. All on TERMCAP-style green-screens in the late 80's. It's taken 20 years for the industry to catch up, *sigh*

  ASIDE#2 -- My Second favorite DB was IBM QBE, Query By Example, which iirc also had RI/FK. You built your query by sketching examples of records you wanted on T-diagrams of the table schemata on a 3270 green-screen, using constants in a column, variables to link joins, and a condition box to constrain relations. Elegant -- but really for on-line ad-hoc query, not for programming.

  --
  Bill
  # I had a sig when sigs were cool
  use Sig;

  Reply to This