DB class. Design generic class to manipulate DB data
Moderator: General Moderators
DB class. Design generic class to manipulate DB data
Hi,
Using php5.
I notice in my coding style that my sql queries are going all over the place.
I think it is about time to make things more structured when it comes to DB.
I want to ask you guys how you proceed when it comes to mangaing DB data.
1. Basicly it is about edit,delete,updated.
2. Do you make one generic class and extend it depending on table to manipulate.
3. How would you handle if data is across multiple tables.
4. What if you want to order by or whatever by some data (that is piled up from several tables.).
5. Is there any specific naming scheme used for table,database names that helps this process.
6. Are you always using full names in sql queries.
I have worked a bit with
http://pear.php.net/manual/en/package.d ... object.php
but not sure if this concept is the way to go.
Would like to hear your experience concerning this.
Using php5.
I notice in my coding style that my sql queries are going all over the place.
I think it is about time to make things more structured when it comes to DB.
I want to ask you guys how you proceed when it comes to mangaing DB data.
1. Basicly it is about edit,delete,updated.
2. Do you make one generic class and extend it depending on table to manipulate.
3. How would you handle if data is across multiple tables.
4. What if you want to order by or whatever by some data (that is piled up from several tables.).
5. Is there any specific naming scheme used for table,database names that helps this process.
6. Are you always using full names in sql queries.
I have worked a bit with
http://pear.php.net/manual/en/package.d ... object.php
but not sure if this concept is the way to go.
Would like to hear your experience concerning this.
My POV is that relational databases are altogether outdated for web development purposes. Heck, they weren't ment for that in the first place. All those abstraction layers and ORMs are simply trying to mask the fact.
For implementation details you can look up ActiveRecord information on the web. Although, it is simpler to download Rails and browse through it's code.
I believe this is the idea behind "model" in MVC. However, on many occasions creating model is too big of a hassle. And it definitely incures some overhead.2. Do you make one generic class and extend it depending on table to manipulate.
For implementation details you can look up ActiveRecord information on the web. Although, it is simpler to download Rails and browse through it's code.
Could you elaborate a bit on that POV? I still haven't seen a better model for datathan the relational model for data. So meaby i learn something new.Gambler wrote:My POV is that relational databases are altogether outdated for web development purposes.
(If you were saying that most SQL dbms have deficiencies i can only agree. But i don't consider an SQL database a good representation of a relational database.)
- Imho most of the abstraction layers exist because there has never been a good standard for communication with sql databases. Imagine we needed abstraction layers in browsers so we could surf using ora-http and ms-httpGambler wrote:
Heck, they weren't ment for that in the first place. All those abstraction layers and ORMs are simply trying to mask the fact.
- Imho ORMs exist because people don't like to repeat the mapping work between OOP and SQL time after time..
(Not saying anything about the quality of those mappings.. Especially not the auto-generated ones by an ORM).
Afaik, the relational model was ment to be a model for data. I don't see why or how this is related to problems that are related to SQL.
I agree that there is no such thing as the perfect architecture and that it depends from case to case.Gambler wrote:I believe this is the idea behind "model" in MVC. However, on many occasions creating model is too big of a hassle. And it definitely incures some overhead.2. Do you make one generic class and extend it depending on table to manipulate.
No matter how you implement your software, you will need to add the rules somewhere. I find it hard to understand how it would incure overhead if you decide to group those rules, because you have to write them anyway.
I believe Martin Fowler has done a great job explaining ActiveRecord and other models in his Patterns of Enterprise Application Architecture. Just don't forget that in most situations the truth is somewhere in between models (trying to get best of both worlds).Gambler wrote:
For implementation details you can look up ActiveRecord information on the web. Although, it is simpler to download Rails and browse through it's code.
Re: DB class. Design generic class to manipulate DB data
So you need something to generate change-read-update and delete (crud) queries? How about a SQLGenerator?jmut wrote: I want to ask you guys how you proceed when it comes to mangaing DB data.
1. Basicly it is about edit,delete,updated.
Btw, if you search the web for CRUD you'll find more resources
Why would i extend from that table? Most of my tables do use different data and have different constraints. But that doesn't change the behaviour of my SQL table.jmut wrote: 2. Do you make one generic class and extend it depending on table to manipulate.
You could have a look at ADO.NET if you were looking for a possible implementation how you could represent that structure with OOP. In my experience that is a lot of overhead because i only want to generate queries, and for that task my SQLGenerator is more appropriatejmut wrote: 3. How would you handle if data is across multiple tables.
Easy, my SQLGenerator accepts one or more pairs of (column name - asc | desc) that are used for the ORDER BY clause.jmut wrote: 4. What if you want to order by or whatever by some data (that is piled up from several tables.).
A decent SQLGenerator can handle all sorts of naming schemes. I remember that i had a couple of problems when i generated joins (because it required my generator to prepend the column name to the choosen columns in the SELECT clause).jmut wrote: 5. Is there any specific naming scheme used for table,database names that helps this process.
No.jmut wrote: 6. Are you always using full names in sql queries.
Actually, the only one who can really judge is youjmut wrote: I have worked a bit with http://pear.php.net/manual/en/package.d ... object.php
but not sure if this concept is the way to go.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
No idea what you're talking about I'm afraidGambler wrote:My POV is that relational databases are altogether outdated for web development purposes. Heck, they weren't ment for that in the first place. All those abstraction layers and ORMs are simply trying to mask the fact.
It's not the idea I use in MVC. I simply have a wrapper for each database that contains the things I need. Taking it down to each table is a bit OTT in my opinion but each to their ownGambler wrote:I believe this is the idea behind "model" in MVC. However, on many occasions creating model is too big of a hassle. And it definitely incures some overhead.2. Do you make one generic class and extend it depending on table to manipulate.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Responding to the original poster -- the first thing you want to do is create Model / Presentation layer separation. Building Model object to encapsulate your SQL is the solution to your problem. Within those classes, how you access data is a huge topic and as much personal preference as technical. Take a look at: Table Data Gateway (144), Row Data Gateway (152), Active Record (160), Data Mapper (165).
(#10850)
Through experementation I developed my own theoretical "model". But it's not strictly a data model, more like a bit of wishful thinking on the topic. The main idea is to move as much logic to application as possible. Thus, the "database" I would like to have would be mostly a set of tools for storage, manipulation and indexing rather than a stand-alone application. Some ideas I managed to implement:I still haven't seen a better model for datathan the relational model for data.
1) Preservation and use of native types.
2) No schemas.
3) Purely functional/OO interface (no SQL).
If this would be a real database written in a low-level language I would go further:
- User-controlled indexing.
- Ability to store node pointers inside attributes.
- No separate client and server (akin to SQLite).
- Reduced operation overhead. Ideally, you could use DB interface the way you would use DOM without making your application too slow. Retrival of individual nodes should not be too costly to put inside a loop.
- Run-time caching control.
You still haven't explained why the relational model is outdated for the web. Now i'm even more curious because it appears to me that your "model" is nothing more than a hierarchical model (tree-like structure). The same hierarchical model that people have left behind and replaced with implementations of the network model and the relational model.Gambler wrote: Through experementation I developed my own theoretical "model". But it's not strictly a data model, more like a bit of wishful thinking on the topic.
- In which context do you consider it to be native? MySQL data types? PHP data types? Operating system data types?Gambler wrote: The main idea is to move as much logic to application as possible. Thus, the "database" I would like to have would be mostly a set of tools for storage, manipulation and indexing rather than a stand-alone application. Some ideas I managed to implement:
1) Preservation and use of native types.
2) No schemas.
- How do you preserve consistency and safeguard constraints if you don't have a schema of your data?
You say that this is not a real database. How would you define it then?Gambler wrote: If this would be a real database written in a low-level language I would go further:
Imho indexing is simply a way to tell your dbms how you will use your data (and thus give it some hints in order to improve performance). I consider it as an implementation matter instead of a conceptual one.Gambler wrote: - User-controlled indexing.
This gives me the impression that you would evolve from a hierarchical model to a network model. (A couple of decades ago it took a lot longer before people made that step. This gives me the impression that there will be a day that you evolve to a true relational model..)Gambler wrote: - Ability to store node pointers inside attributes.
Again, an implementation detail.Gambler wrote: - No separate client and server (akin to SQLite).
I agree that a dbms should have acceptable performance. If i look at most available dbms out there today i can only conclude that a seperate server and client perform better.Gambler wrote: - Reduced operation overhead. Ideally, you could use DB interface the way you would use DOM without making your application too slow. Retrival of individual nodes should not be too costly to put inside a loop.
Again, an implementation detail.Gambler wrote: - Run-time caching control.
Do you mean I should prove it? I don't think I have to. I said that it is my point of view, and this is truly so. Providing reasons and examples that would explain why it is the case would be lengthy and off-topic. If you're interested in discussion of various database models applied to web development, then I recommend you to create a separate thread, in which we will continue this conversation.You still haven't explained why the relational model is outdated for the web.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
No you don't have to prove itGambler wrote:Do you mean I should prove it? I don't think I have to. I said that it is my point of view, and this is truly so. Providing reasons and examples that would explain why it is the case would be lengthy and off-topic. If you're interested in discussion of various database models applied to web development, then I recommend you to create a separate thread, in which we will continue this conversation.You still haven't explained why the relational model is outdated for the web.
As for what you describe Gambler with the whole database model being directly integrated to the application and all OO it's already in development, though not publicised massively thus yet.