I'm making a database abstraction layer and I need to write methods for my database object. I'd like to create a useful class, but I don't want to overload it with features that most people won't use. I know which ones I use, but I'm curious to know which SQL features you use most to build your queries (regardless of what type of SQL database you're running). What would absolutely appall you if I left it out?

I'm still building it, but some of my current methods are: left_join, where, order_by, etc. In your replies, if you could please post what you commonly use and then also tell me what database you work with, it would help me a lot. Also, if you have any general suggestions for methods, that would be useful too. Thank you.

All the basics, like what you've mentioned. (Select, Update, Insert, Delete; Where, From, Joins, Having, Order By, Top N, all the usual basic stuff you'll find in MS Access.) Custom aggregates are amazingly valuable, but I don't know how you might hope to implement them.

I doubt I could do much meaningful SQL work without subqueries, or, failing that, CTEs. The S(tructured) in SQL comes from the concept of a subquery.

Check constraints and join hints are pretty big. Locking hints to a lesser degree.

Ranking and partitioning functions are an absolute must have.

Error shielding and logging, plus good client statistics and (but this is less often than the others) access to the query plan.

Hope this helps. I don't know how I would go about trying to model these into a class, but if I were going to use that class in a web project, these are the things I would immediately look for in it.

Thank you, sir, exactly the type of reply I was looking for. I'm definitely going to have my work cut out for me, but I'll just start on the basics and work my way from there. As just a quick follow up, do you use all join types, or just a few?

Wow. Going over your list and as I dig deeper, I'm starting to discover why MySQL has been maligned by a lot of people. As far as I can tell, it doesn't support CTEs or check constraints and ranking functions have to be emulated. However, from a market perspective, because I hope to offer alternatives to Wordpress, Joomla, and Drupal, I can't rule MySQL out.

I think my best option right now is to make a simple base database object with features that are shared across databases (even if their syntax is slightly different) and then make it easily extensible. Many of the operations for a CMS of this sort are going to be simplistic CRUD functions and having simple base DBObject will ensure module portability for that kind of use. However, if someone wants or needs to take advantage of a database specific feature, they can extend the base object. The new modules that are dependent on the extension won't be portable, but the extension class shouldn't break the old modules either as long as they don't rewrite any existing methods. I'm fairly certain I can figure out a way to easily change which class the table-specific classes extend.

My problem today is figuring out how to write methods for building subqueries and top N. I don't think it's going to be very easy.

Yesterday I was able to get the class working for building basic select statements using methods for selecting field names, assigning aliases, left joins, where clauses, order by, limit, and offset. I also have it so that it can return a single row accessible through magic methods, a multiple row result set that can be accessed by a simple foreach loop, a number for getting a count, and a debug option that prints the built query. I haven't tried inserting, updating or deleting yet. Once I finish a prototype, I'm going to do some performance testing and see how much overhead it adds and if I can improve it at all. I'll probably also see if I can simplify or improve the methods at that time as well.

I use both join types. Inner and outer.

A left join and a right join are the same thing, you just list the tables in different order. A cross or Cartesian join can be achieved with inner or outer, it's really more about the join condition or where clause. I tend to never use full outer joins. If I want that functionality I combine a left and a right join via the union operator. (This gives me a little more control.)

But one thing that's important to note when we're talking about joins, is that I always, in every case, use correlation names with joins. Here's an example of what I mean

PHP Code:

Select
   O.Name, C.Name
From
   sysObjects O Join
   sysColumns C
      On O.ID = C.ID 


Thanks again.

Originally Posted by Learning Newbie

But one thing that's important to note when we're talking about joins, is that I always, in every case, use correlation names with joins.

I think I found a decent way to do that, except I just named my method 'alias'. I'm going to allow for the database tables to be prefixed upon installation, so the actual table name would be something like VM_users. The class takes "users" as the parameter, prepends the prefix for the table name as "VM_users", but then automatically sets "users" as the alias unless an alternate alias is used, in which case the alternate is used. That way, it lets me use the table name without having to know what the prefix is. I'm sure it's been done thousands of times, but it was kind of cool to get it working on my own.

As a code example:

PHP Code:

$users = new users();
$users->left_join("userInfo", "users.user_id = ui.user_id", "ui");
$users->where("users.gender = 'M'");
$users->limit(10);
$users->offset(5);
$users->order_by("ui.DOB", "ASC");
$users->select("debug"); 


will produce the following query for MySQL:

SELECT * FROM VM_users AS users LEFT JOIN VM_userInfo AS ui ON user.user_id = ui.user_id WHERE users.gender = 'M' ORDER BY ui.DOB ASC LIMIT 5, 10

And this code:

PHP Code:

$users = new users();
$users->alias('u');
$users->left_join("userInfo", "u.user_id = userInfo.user_id");
$users->where("u.gender = 'M'");
$users->limit(10);
$users->offset(5);
$users->order_by("userInfo.DOB", "ASC");
$users->select("debug"); 


produces:

SELECT * FROM VM_users AS u LEFT JOIN VM_userInfo AS userInfo ON u.user_id = userInfo.user_id WHERE u.gender = 'M' ORDER BY userInfo.DOB ASC LIMIT 5, 10

And this:

PHP Code:

$users = new users();
$users->select("multiple"); 


would return a result set for this query:

SELECT * FROM VM_users AS users

Getting the result set would be done like:

PHP Code:

$users = new users();
foreach ($users->select("multiple") as $row) {
     echo $row['firstName'].' '.$row['lastName'];
} 


It's still simplistic, but it works. Multiple left joins, wheres, and order bys are also allowed and specific fields can also be specified by using the select_fields() method.

Originally Posted by VirtuosiMedia

That way, it lets me use the table name without having to know what the prefix is. I'm sure it's been done thousands of times, but it was kind of cool to get it working on my own.

What's the value of prefixing your table names, if you refer to them without knowing what the prefix is? Sounds like an added layer of complexity, with no payoff whatsoever?

Why would that have been implemented so many times? I don't see what it gets anyone. Remember all the cheers of millions of programmers when Hungarian notation got the final nail in its coffin.

Anyway, rant aside, what I mean when I say I alias table names during joins, is that I give them usually 1 and sometimes 2 or even 3 character aliases. In a lot of schemata queries, O is sysObjects, I is sysIndexes, C is sysColumns, et cetera.

I've actually seen the ability to change the prefix on multiple scripts that I've used, usually during installation. I doubt there are any security benefits, but some people like the option so they can avoid potential naming conflicts if they have other scripts on their server. That said, it definitely isn't the greatest thing since sliced bread.

Interesting blog post, thanks for pointing that out. I notice the one comment it has is a trackback from a highly intelligent blogger from Down Under. I don't know if you read any of Neerav's stuff, but I respect the guy a lot. That he's linking to it shows some level of interest.

But my thought is that a person who can gain access to your system through all the security procedures you have in place, is probably going to be able to query the information schema and get a list of your tables.

In MySQL, is it one database per server? That might explain why the table prefixes aren't so common in my neck of the woods - we tend to separate stuff out, everything into its own db. So there can be two User tables, say one in HR and one in Finance. It's funny that a number of our people still use the prefix "tbl" on every table, tho. They'll tell you it's "so you can see it's a table, and not a view, when you write a query". I say if you don't know what it is, you shouldn't be writing production queries against it!

But I don't want to hijack your thread off topic. The point with table aliases, especially for joins, is that the names you give them are temporary, scoped only to that query. If we use sysObjects and sysIndexes as an example (tables present in every SQL Server database), there are some common fields like ID and Name that are present in both tables. In the select list, you could write

Select sysObjects.Name, sysIndexes.Name [blah blah] Where sysObjects.ID > 100

Or

Select O.Name, I.Name [blah blah] Where O.ID > 100

You have to specify which table the ambiguous columns come from, and it's so much more readable to work on a query where they're aliased down to a few short letters. There's a concept called "natural join" where instead of saying on x.y = z.y it assumes any fields with the same name in both table are the join condition. Which implies joined tables will tend to have at least 1 field like this, often more. Which means having to supply lots of "disambiguator" table name or alias dot field name code. So that's why the short lived kind that doesn't actually need to change your database schema, are still useful.

I'm not really sure if you can have more than one database per server in MySQL. I'll have to look into it, though. On the whole prefix thing, the main reason I'm including it isn't really security (I agree with you on that one), but because of the naming issue and because it seems to be a common feature that I've encountered across several PHP scripts I've used including Joomla (and I think Drupal does it too).

I'm totally following you on the aliases. I had actually provided examples above in the code, but you might have missed them because I edited them in without knowing you had posted. I don't require aliases as a parameter, but they're optional. Because of the whole prefix thing, a table named 'VM_users' will be aliased to 'users' by default (my joins will do the same type of thing). However, if you want to shorten it even further, say to 'u', you can use the method alias('u') if it's the main table, or enter 'u' as a parameter if it's part of a join. The 'u' will then replace 'users' as the alias. For MySQL, setting an alias is done by using AS after the table name.

This may be of interest to you. It probably won't be ready for a week or two yet, but as part of my work on this, I'm researching rudimentary SQL implementations for DB2, MS SQL, MySQL, Oracle, PostgreSQL, and SQLite. I'm compiling basic syntax examples for each and I'm going to put them all together in a blog post. I'm not going to go into super complex examples, but I'll cover the basics. It'll be a lot of work, but hopefully it'll help me understand the differences better so that I can build a DBObject that will make sense. However, if you know of a similar resource that exists already, please share. It would definitely save me some work.

Originally Posted by Learning Newbie

we tend to separate stuff out, everything into its own db. So there can be two User tables, say one in HR and one in Finance.

Select sysObjects.Name, sysIndexes.Name [blah blah] Where sysObjects.ID > 100

Or

Select O.Name, I.Name [blah blah] Where O.ID > 100

As I'm exploring database creation, a few questions crop up related to MS SQL. I'm guessing that in the x.y format, the x is the database and the y is table name...is that right? Do you use separate connections for each, or can they be handled through the same connection? And if they're separate, do you put them all in a single config file? I'm just trying to figure out how I would run an installation script and extend my DBObject for MS SQL as I'm used to a single connection through a single config file with MySQL.

SELECT name,dob,marital_status,siblings,spouse,address,fo rmer_address,phone_numbers,former_phone_numbers,em ail_addresses from ssn_database;

I think I may have to take SQLite off the list as it doesn't support RIGHT JOIN, FULL OUTER JOIN, and a few other features. There are a few security concerns I have about it too because it stores the entire database on a single file on the server, which, depending on where it's placed, is web-accessible. I saw a few workarounds, but I don't know how comfortable I am shipping with them. Documentation is also woefully sparse.

I'm still just working on the MySQL object, but I've added methods for left, right, inner, and full joins and I'm going to add the cross joins as well. I think I also figured out how to add subqueries, but the Top N queries are more problematic because some of the DBs seem to support them and others emulate them using subqueries.

Hopefully some will find this an interesting journey. If nothing else, I'm learning a lot.

I found a very useful ebook that gives general SQL syntax and then some database-specific examples.