Link McGaughey wrote:
The design is stable until the business model changes.  Which is the
case with R&D projects as they are constantly changing.  The
applications we are writing are things that do not exist in any
capacity in the market.  Which makes "stable" a relative term when
comparing it to a banking or warehousing situation.  Also, keep in
mind that the applications we are writing are quite large with a large
user base and we have a very small team (over half are junior
developers).  In an R&D capacity, faster turn around is key, so having
the ability to lower maintenance code changes is vital.

Some possible maintenance issues:

  - changing all the sql to get the new column.
  - having different names for your aliases from query to query become
confusing.

Also, I'm not sure what you mean by a "hash array".  I am guessing you
are getting a hash or hash reference.  I too am using the
fetchrow_hashref, however it drops the table prefix on the return
column names, which in turn clobbers column names that are the same
across tables.  Thus the reason for this thread.
I was really just looking for any future planning done on the DBI side
of things to aid with getting back full information.
As I said before sloppily designed SQL and Stored procedures cannot be saved by a third party tool.

In you case I would go with stored procedures that return the same pattern of result set the same each time, that way if the model is changed
(but the basic design is not) the SP will return the same expected values.

This can be done without SP by wrapping your SQL in some sort of perl object (bean for lack of a better word) that has a set of poperties that are set by your SQL.

That way if you model changes you do no need to change you base design.

  Either a flag to
pass into the function call, or new functions which return the table
names prefixed as you would see when running the SQL directly from a
console.
Not all consoles do this

Golden and sqlplus for example when given this SQL

select CONTRACT.ID, CUSTOMER.ID from CONTRACT, CUSTOMER where CUSTOMER.id=CONTRACT.CUSTOMER_ID

simply return a Set like this

id|id
1| 2
2| 3
..| ..

In your case it you will most likely have to 'Define' what the column name should be first.

 It seems that other languages, such as Java, do not seem to
have is issue, both ways of retrieval are possible.
By the way Java as a language has nothing to do with it. Java uses JDBC, ODBC or alike to read the database It would be the implementation of that 'DB Layer' that would be putting the table heading there for you not Java itself.

So to sum up if your DB is subject to change you should abstract it in a more stable form SP, PerlObjects etc. So you porgrammer can go on with thier work whiout havieng to worry about changes to the DB.



If the
fetchrow_hashref were changed to house both types of keys (those
without table prefixes and those with), in the way Java and other
languages work, then this would be a mute point.

-Link

On Tue, Aug 11, 2009 at 1:25 AM, Jared Still<[email protected]> wrote:
On Thu, 2009-08-06 at 10:23 -0400, Link McGaughey wrote:

...aliasing each of the column names, but this becomes a maintenance
issue when you have alter the selected columns every time there is a
change to the table.
Personally, I would work on stabilizing the design.

Tables frequently being changed would indicate to me that whoever is
designing the tables is not yet sure what to do with them.

I'm curious as to just what the maintenance issues are?

Normally I would expect to see all SQL statements packaged together
somehow, and each SQL statement appears only once.

Personally, I use a hash array, version them according to
database version.  This simplifies changes a bit.


SELECT *
FROM
   table1 t1
   JOIN table2 t2 ON (t1.column_id = t2.column_id)
WHERE
   ...

In general, using 'select *' is not a good practice.

One good reason is that it is a resource hog.  It takes
a fair amount more resources to do 'select *' rather than
just selecting just the needed columns, especially when a
small percentage of the columns are actually used.

Yes, I know you didn't ask, but thought you would like to know.
It's not too hard to test this yourself.
I've done so on Oracle, but not on MySQL.

And no, I don't care if 'select *' is easier to code.  :)




Reply via email to