Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

This sounds pretty interesting, and frankly very reminiscent of the project that originally got me interested in perl. I was working on an order entry system in VB with a Sybase database as the back end. I was designing the OE class model as VB COM objects and didnt want to have to do all the DB work by hand as well. Enter Perl, parsing VB class modules and automatically generating a DB, as well as modifying the class model to add DB interaction. I completed my model, but not long after the project was killed (turned out it was just one of like 10 other similar projects in the company, and eventually they were all put on hold so that a better plan could be developed :-) I was happy as it meant I didnt have to work in VB anymore, and it was the last project where I have had to do more the maintenance work on VB. A later project was in pure perl and involved essentially the reverse, using a DB design tool that produced XML we developed a XSLT stylesheet that generated DB aware classes from the model. That one was live for a year, fulfilled its purpose and was shut down when it is was no longer needed. (It was the only interim solution that ive seen that actually was interim :-)

The reason I say all this is because my conclusions drawn from them is that tools like this rapidly become unsuitable for serious work. They artificially tie your hands to a bunch of presuppositions that sound good at the time, but dont grow well. So im very suspicious of tools and frameworks that work like this. I tend to view them as wasted efforts that while sounding good in theory dont actually work out as useful as they should. Dont get me wrong, Im not saying this is a wasted effort. At least one respondant has replied very positively, and even if only they and you derive benefit from this tool then you've achieved something positive. Im just trying to point out a few things that I observed in the projects I mention.

With regard to naming conventions, I think a system like this becomes very dependent on the power and expressivness of naming conventions (this assumes that as you said you dont want to use more robust structural discovery techniques). You touch on the problem yourself with the naming convention you mention. Primary keys are represented by the table name, and foreign keys are represented by the table name refered to. Personally I have never designed or used a DB that conformed to either of these assumptions. :-)

First off, my table names are often long. Second off, how do you represent a table that has two references to another table this way? For instance a DB that represents a family relationships. A "person" table may have two fields "mother" and "father" and then perhaps we may have a second table "guardians" that contains two references, one to the guardian one to the gardee (hmm, not sure the word for that :-), How do we represent either of these structures using your naming convention? How do you represent composite primary keys? How do you represent complex integrity constriants? Now as one respondant already pointed out, we dont need to _always_ use your code, but if its to be really useful I think you need to resolve some of these problems. The double reference one is very common in my experience.

Naturally I have some ideas on the matter. :-) One thing that we did was use a more complex naming scheme. In our scheme every table had an associated prefix, normally generated from the first letter in each word in the table name, or lacking multiple words for truly base table names some other easily remembers abbreviation (such as cs or cust for customer). Then we used suffixes to represent foreign key or primary key status. So a table customer_relationship that has an identity primary key would have that column called "cr_id_pk". If there is also a type table called "customer_relationship_type" then the reference to it in customer_relationship would be called "cr_crt_id_fk". The automatic tools can then use some rudimentary rules to figure out whats going on.

In the case I mentioned earlier of the family trees, we might have a table "people" (I chose this now, as it abbrreviates nicely) whose prefix is ppl. Now we might have fields "ppl_id_pk", "ppl_father_ppl_id_fk", "ppl_mother_ppl_id_fk", etc. When the discovery mechanism runs it identifys the fields ending in fk, pulls off the tables own prefix, and then keeps removing words from the front until it matches a primary key it knows about. This approach covers cases where you have composite primary keys, multiple references to the same table, foreign keys that are part of a composite primary key (its not hard to work out a way to handle this using the suffix pk).

Does all of the above start sounding nasty? It should. And its the reason why I steer clear of stuff like this nowadays. Anyway, I hope you find some of this useful to your efforts, while I am a cynic, Im not totally negative. :-)


---
demerphq

<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...

In reply to Re: Module RFC: Yet another object-persistence interface by demerphq
in thread Module RFC: Yet another object-persistence interface by blokhead

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others lurking in the Monastery: (6)
As of 2024-03-28 22:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found