How to define a way-of-organizing-your-code strategy for creating a more maintanable codebase when it comes to SQL refactoring

[dennis-fedco]

I am refactoring entire collective scattered SQL from my legacy codebase, and into separate classes, and looking for some structure to put it into.

Right now I have folders effectively called

• `DataFromDB` - contains classes that accepts whatever parameters are given, and returns pure data back to the user
• `DAO` - Data Access Object, which takes that raw data from DB and makes sense out of it and prepares it for consumption by the model/business logic layer objects.

That is:

    - src (folder)
      |- DAO (folder)
        | - ProductADAO (classes - take data in, return consumable objects out)
        | - ProductBDAO
        | - ...
        | - ProductZDAO
      |- DataFromDB (folder)
        | - ProductAData (classes - contain methods to query pure result sets from DB)
        | - ProductBData
        | - ...
        | - ProductZData

Whenever I need to make a new SQL or refactor an old one I do this:

1. What is this SQL doing?  Is it operating on `SomeObjectX`?  If yes, find/create class called `ObjectX`, and add a method to it, extracting pure data from DB, put it into `DataFromDB` folder.
2. Write the `DAO` object if needed to transform data into a consumable object.
3. Use the object as is in my code.

Does this look like a good strategy?  Is there a better one?

 

My problem with this one is that before (now) all the SQL is tightly coupled and is included into the multiple business classes.  Using the above strategy will mean I am to be creating many many classes, a lot of classes, most likely one for every few SQL statements. 

 

The pros is that it seems like I will achieve a level of code modularity that I wanted.

[ignace]

A class per table would be much better then having a class per query. This however means you will need to map the fields to the appropriate classes when doing joins.

 

Doctrine has a class that can do this for you.

http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html#resultsetmappingbuilder

 

This means that you will map the fields from the result to their appropriate objects incl. hierarchy.

Edited December 30, 2014 by ignace

[dennis-fedco]

How strongly are you advocating this approach and Doctrine approach?  My background is that currently I have 1100+ SQL statements in my code, and an average SQL merges 2-4 tables like so:
 

SELECT * FROM a.order
LEFT JOIN a.item ON a.order.id = a.item.a_order_id
LEFT JOIN b.part ON a.item.b_part_id = b.part.id
WHERE b.part.rp_id = $id
GROUP BY a.order.id

Essentially whatever method I choose that requires any change to the way I do SQL now, will require an exercise of eventually changing those 1100+ statements to use that new way.  Or if I abandon it at any time I will have some bare SQL statements in my code and some Doctrinified statements in my code, and future developers will need to learn both technologies to maintain that.

 

Right now what I do is simply refactor things into their own classes where I don't have to think hard on SQL at all as I just move it.  My focus is currently on refactoring + feature adding.  and I do the refactoring as I go along whenever I come across a section of code I am working on.

 

Having worked with Doctrine a little bit, I came to realize that if I will start using it, majority of my work will become "re-encoding existing SQL into Doctrine", and "learning ways of Doctrine when I come to a particular SQL corner cases and statements that do not easily translate directly to language of Doctrine".  And doing so will take up quite a few of my brain power cycles and take away from things like adding features.  Doctrine is a paradigm shift from a structural query language to object oriented one.  I would love to use it but I am not sure it is the time.

 

Also seeing how my table are mostly joins of multiple tables, using a class-per-table may not suit me that well (at least in terms of workload).

I keep myself open to suggestions and strategies.  I do want to avoid unreasonable amounts of work if at all possible.

• Doing nothing is one way as things work now.  And that is having SQL everywhere in the code - view, modely, controller, etc.
• Doing my described strategy approach is basically moving directly-SQL-related code out into separate namespaces while inserting "wiring" to connect it back to the caller functions.  (wiring, as in decoupling previously directly connected components and connecting them via functions/class declarations) and that is what I consider to be the minimal amount of work needed to make codebase "better" (more loosely coupled, more maintainable, more flexible perhaps).  This is reasonably safe as it leaves SQL as SQL, as in structurally there is no fundamental change.
• Using Doctrine looks like rehauling a lot of things at their core level and using an approach (class-per-table) that I am not yet sure fits, especially looking at the way I have SQL written (lots of multi-table joins).  Plus, learning Doctrine, as I don't have that great of a grasp on it at the moment.

Edited December 30, 2014 by dennis-fedco