Creating table structure undepended site

**website** · May 21, 2003 12:02

Hello

I am wondering how I could make a site so that I am not depended on some specific table names/structure, that is that the columns don't neccisarily have to be called something specific and I can easily adjust my site to differant table/column names.

I do run all querys throught an object so I am not depended on some specific database and I wanted to take it a step further.
I was just wondering what is the best way to do this?
I had thought of doing querys like this:

PHP Code:


$q =& $db->query('SELECT {table1.col1} as name1, {table2.col2} as name2 FROM {table1}, {table2}');

replacing table1/col1 etc with some convinient names that are saved in my system, then I store what the actual field name is and do preg_replace through all my querys to place the appropriate names, still, this method must be a little bit slow with all those preg_replace calls, or what ?

If anyone is doing/knows how to something like this please share it, I would appreciate it very much!

Thanks in advance!

edit: isn't it independed ? I don't know what is wrong with me today ; )

**Dr Livingston** · May 21, 2003 12:10

Never seen this done before....

SQL Templates anyone ?

The only thing that I can think of at the moment as to why you'd want to do this is to share table resources (ie data) between different tables/databases that all (must) have the same design and relationships...

There is bound to be another way of doing this w/out using the idea you've just described I'd think ?

But at the moment I can't think of method of templating SQL queries....

Sorry

**website** · May 21, 2003 12:14

Yeah I know, this isn't so good solution but this must exist, I mean, this is so useful!

I plan to place my web'system' on two websites that already exist and already have differant table structure. This would be the key, I just don't know of a good way to do this...

**Phil.Roberts** · May 21, 2003 13:13

PHP Code:


define('NEWS_TABLE', 'news');



$q =& $db->query('SELECT title, article, date FROM ' . NEWS_TABLE . ' ORDER BY date');

Perhaps you could also maybe work some kind of tag parsing mechanism into your query() method? Then you could implement an SQL templates system if you wanted.

**website** · May 21, 2003 13:36

my scripts would drown in constants

No I would like to have the parsing logic, as you said, in the query method (or before I execute the query which is not done there). Any idea how to do so other then the way I pointed out in the first post ?

**HarryF** · May 21, 2003 14:53

Quick link - have a read up on DB_DataObject (part of PEAR): http://devel.akbkhome.com/peardoc2/p...ataobject.html

**been** · May 22, 2003 08:29

I use the SQL 'AS' keyword to abstract from db column names.
as for

SQL Templates anyone ?

This

PHP Code:


$sqlTemplate = 'SELECT %s FROM %s WHERE %s';

$sql = sprintf( $sqlTemplate, $sqlFrom, $dbTable, $sqlWhere);

has been working fine for me, but maybe there are problems with it I haven't thought of ;-)

**anode** · May 22, 2003 08:34

Originally Posted by HarryF

Quick link - have a read up on DB_DataObject (part of PEAR): http://devel.akbkhome.com/peardoc2/p...ataobject.html

Along similar lines, the phrasebook pattern.

**website** · May 23, 2003 07:01

well been, I would rather not need to include more variables all over my classes, rather do it like anode and HarryF are suggesting...

About that method, I don't fully understand, I read the phrasebook and some pages of your link Harry but could you guys help me with it. I thought that was bit limiting as it is so hard to make complicated querys with joins etc, could you give me example of classes and usage when doing the following:

PHP Code:


$db->query('SELECT User.id, User.name, Post.title, Post.content FROM User, Post WHERE User.id = Post.User_id AND Post.id = 1');

on the following tables

Code:

User:
id
name

Post:
id
title
content
User_id (references User.id)

Could you show me how to do this query with your methods ?

I would appreciate it extreamly much !

**Dr Livingston** · May 23, 2003 08:46

Anode - have you looked at this article ?http://www.evolt.org/article/Effecti...149/index.htmlYet to look at it myself though....

**mkrz** · May 23, 2003 08:56

Oh dear. Just had a look at the article Dr Livingston mentioned and I completely agree with tapir's 2nd comment. This approach isn't very adviseable. I think a DAO solves this problem in a much more flexible and scaleable way.
website, you might want to check out the DAO pattern. It might offer what you are looking for: a nice way to abstract away the database implementation details. I cannot praise the advantages of this pattern enough.

**been** · May 23, 2003 09:10

well been, I would rather not need to include more variables all over my classes, rather do it like anode and HarryF are suggesting...

I was not suggesting you should include your variables all over the place, I was just showing a different kind of 'templating'...
In the link HarryF posted I saw a line re-appearing all over the place: 'global $db'
With the risk of being called a total ripoff-guy: 'GLOBALS ARE EVIL' (btw, HarryF, didn't you write an article about that? ;-) )

No matter how you look at it, you're still going to have to store the table name, field names etc.. somewhere before you can use them in your apps.
Now, I don't know, but it's my opinion these storage containers are going to be variables ;-)

Of course, I wouldn't include these variables all over my classes!
I'd use a class to fetch the queries (which are stored in sprintf()-compatible 'template' format (for example in a text file)) and pass them to the query() method of my db-connection-object.
I'd simply use an identifier for the queries.
I guess you could call it a Dao...

All queries would be stored in 1 section of a configuration file, if queries change, I have to modify 1 specific section of the configuration file, if the table names or field names change, I change them in the same configuration file (could be another one btw ;-) ) I don't have to change anything in the rest of my app code....

I've come to learn (and am convinced) that 'template view' not the way to go for application layout, why should it be any different for queries ?

**website** · May 23, 2003 09:48

well, yes, I have it similar but I use no globals, as I do it now, I pass an object call $db into the classes that need it.
But if you store all querys in one config file, do you just call it like $db->execQuery(1) ?
1 would be the number of the query...
Well, that obviously places everything regarding the sql in one place but is that really the best way ?

Anyway, I've got to check out sprintf() function and these DAO

(some links maybe)

**been** · May 23, 2003 10:19

Well, if it's the best way, I really don't know.
But, considering a website:

It has different pages, some of them are maybe static, some of them are dynamic and use a db.
The pages that use a db have certain amount of queries that are executed, for example:

Code:

SELECT title, posts FROM forum WHERE date > 20030506;
SELECT title, content, author FROM forum_posts WHERE post_id = 25;

etc...

What I'm getting at: each page has a limited set of queries to perform (depending on the action requested by the user). A system may change, queries may change, but all in all, it will be limited So I would say, yes store them in 1 place, let 1 layer of your app access this queries, perform them and return the results...

If you look at the Dao pattern from Sun blueprint catalog they use pretty much the same system...

**Dr Livingston** · May 23, 2003 10:37

mkrz - that bad huh ? Yet to read it myself; only passing it on since there is a discussion about it.

been - I agree with mkrz - use HarryF's Dao for this.

I cannot understand as to why you want to pass more overhead on your server simply to read in some query variables ?

Nice idea and all but pointless IMHO - In HarryF's example, you'd only every need to change one class and that's it - in this case, it'd be the query it's self and nothing more.

Honestly, this is the better method. Have a look and you'll see for yourself....

**HarryF** · May 23, 2003 11:06

In the link HarryF posted I saw a line re-appearing all over the place: 'global $db'
With the risk of being called a total ripoff-guy: 'GLOBALS ARE EVIL' (btw, HarryF, didn't you write an article about that? ;-) )

It was magic quotes but agree; in general probably a bad idea. Don't think DataObject ties you to doing it this way though.

One other approach (seems like everyone in PHP is thinking along the same lines right now) is what John Lim, author of adodb has just come up with;

http://php.weblogs.com/discuss/msgReader$2508 - this is more aimed at database abstraction but I guess it could be used for this problem.

**Richi** · May 26, 2003 15:50

Originally Posted by website

Hello

I am wondering how I could make a site so that I am not depended on some specific table names/structure, that is that the columns don't neccisarily have to be called something specific and I can easily adjust my site to differant table/column names.

I do run all querys throught an object so I am not depended on some specific database and I wanted to take it a step further.
I was just wondering what is the best way to do this?
I had thought of doing querys like this:

PHP Code:


$q =& $db->query('SELECT {table1.col1} as name1, {table2.col2} as name2 FROM {table1}, {table2}');

replacing table1/col1 etc with some convinient names that are saved in my system, then I store what the actual field name is and do preg_replace through all my querys to place the appropriate names, still, this method must be a little bit slow with all those preg_replace calls, or what ?

If anyone is doing/knows how to something like this please share it, I would appreciate it very much!

Thanks in advance!

edit: isn't it independed ? I don't know what is wrong with me today ; )

I think the best tutorial for this Ez Publish 3, you can build forms from admin interface, the reading for this for going by query base on the node id.

I think the idea is easy, that the data is stored in the DB base on the datatype, which is predefined by some php lines.

there are 2 table where the data is stored,
1. contentobject , include the the id,data, typeid
2. contentobject_attribute: include the typeid, typename.

so the query will ask the data from contentobject, and will format it base on contentobject_attribute, such input,textarea,enum .. ect ..

Best thing just download it from ez.no , and look t the DB design and the php code !

this what i think, didn`t test it, what you think ?

User Tag List

Thread: Creating table structure undepended site

Thread Tools

Display

Creating table structure independed site

Bookmarks

Bookmarks

Posting Permissions