I realize database abstraction is more of an ORM type of thing, but as I’m working on a pretty big application, I realized that I’m building all my DB Forge stuff with MySQL in mind and things like, say Oracle (which is completely foreign to me) use different field types that I may not know about. So I can test everything on MySQL and it could work fine, but if someone tries to install the system on an Oracle database, problems would ensue and I wouldn’t have the slightest clue how to fix all the syntax problems. So I started hunting and found Creole, which is a database abstraction tool. Now, I’m not saying Creole should become part of CI. Far from it in fact. But what I think would be awesome to see would be some kind of database abstraction layer to the Forge.
The basic idea I had (though I haven’t put any code down yet) is an array of basic types.
In the Forge stuff, you create a type for the fields of a table you’re creating. But in my case, say I’m building an application MySQL in mind. I’d put the type as INT, but in reality, Oracle uses INTEGER. Since Oracle doesn’t have INT (at least as far as I can tell from my research), there are gonna be major problems since it can’t find the field type. From here, the Forge would throw the type over to an abstraction class which would then take my type and search the array for it. Bingo, it finds it and we build another array that’ll set the key as the type.
From there, when the Forge goes to do its thing, it’s using the generic INTEGER instead of a database specific type like INT. Since the database driver is set and the database library has been loaded already, it can translate INTEGER to whatever the database driver in question would be looking for (which would require a second array, most likely in each database driver directory). And suddenly, the Forge allows you, without any knowledge of, say, Oracle database types, to develop an application that can be installed on any of the CI-supported databases.
This is an excellent idea AgentPhoenix. It sounds like you’re working on something similar (or will be). Please share your code, I’d be very interested.
This is incredibly basic right now and not vetted for efficiency and the like, but I at least wanted to put something out there. I’d love to hear suggestions.
There are a few things that need to be worked out (obviously). First off, you have things like LONGTEXT in MySQL that have equivalents like LONG and CLOB in Oracle. I think the preferred option in Oracle is CLOB since LONG is deprecated and isn’t searchable. So the question at this point is figuring out how to handle those kinds of situations. If I’m dumping into a MySQL database, I’m going to want it to stay LONGTEXT, but some kind of substitution has to happen for the other database types.
Also, the other thing I’m stumped on is how to integrate it with the Forge so that it’s a seamless process and you don’t have to run your arrays through another function before going into the Forge.
function change_type($field_data = '', $db_driver = '') { foreach ($field_data as $a => $b) { foreach ($this->field_types as $key => $value) { if (in_array($b['type'], $value)) { $field_data[$a]['type'] = $this->fields_db[$db_driver][$key]; } } }
return $field_data; }
This basically takes an array (can be a single array or a multi-dimensional array) and substitutes the type key for its database equivalent (sort of). Still very early, so suggestions would be awesome.
Alright, I put everything down on paper and got some stuff sorted out in my head, so here’s kinda how I see something like this panning out.
First, adding a new config variable to the database config file.
$db['default']['forgestyle'] = 'mysql';
This variable essentially tells CI what database style you’ll be doing your Forge coding in. If you’re most comfortable with MySQL field types, you would set that and define all your field types in MySQL terms, then CI would translate it to the proper field type if your database driver is different from the Forge style variable.
Next, each driver would get a new file: [driver]_types.php. The types file would have a multi-dimensional array with all the field types for that driver. The below example would be the mysql_types.php file. (MySQL is my preferred database, so I’m sure some of the associations are wrong, but this is just to show people where I’m moving with this.)
There’s a pretty simple method that goes through and does all the translations now. (I’ve been testing this outside of CI just to get things working, so the references to $this->style and $this->driver would be changed in the end.)
function change_type($data = '') { /* continue only if the style and driver are different */ if ($this->driver != $this->style) { foreach ($data as $a => $b) { foreach ($this->db_fields[$this->style] as $key => $value) { if ($b['type'] == strtoupper($key) || $b['type'] == strtolower($key)) { $data[$a]['type'] = strtoupper($value[$this->driver]); } } } }
return $data; }
Still not sure where this should go. My first thought was create a DB_abstraction.php file in the database directory, but I’m not sure if that’s the best way to do it or if the proper Forge methods should be updated to include this type of a method. As with anything like this, I’m probably too close to the code to see potential issues, so someone please step up and let me know where I’m going off track or where things could be improved!
This looks very cool. Please keep building. I’m hoping others will chime in here, as I don’t really have the ability to help much with this on a code level at the moment.
Anyone out there a whiz with databases other than MySQL? I could use some help with figuring out types for the abstraction as well as constraints for fields in the other database types.
Still working on this. I think I’ve managed to cobble together data types and column constraints for Oracle, PostgreSQL, and MS SQL. I have SQLite installed on my desktop at home and will be able to do those, but does anyone know about ODBC data types and column constraints. I’m utterly confused by everything I’m seeing in a Google search.
Thanks, m4rw3r! The big thing left to figure out is the best way to integrate this with DB Forge. Obviously it can’t be extended, but I’m not sure what the best avenue for getting working with the Forge is. From a quick glance, there are only 3 methods that it has to play with: add_fields, add_column, and modify_column. Any ideas?
Maybe create it as a new library? Then it can be created from scratch, with no concern of the original.
Some other functionality can also be implemented, like some nice method chaining (take a look at my short try at abstracting the forge here, but I think I need something more coherent to build it on).
Yeah, but if it’s gonna be something that will (hopefully) find its way into the CI core, then it’ll need to be PHP4 compliant as well.
The goal really is just to provide some basic conversion from whatever database syntax you’re used to using to whatever driver you’re using. (Obviously if the style you’re most familiar with and the driver are the same, it doesn’t do anything.) Right now, type and constraint are the only two (that I can see) that will need to be touched before a field is added or modified through the Forge.
I’ve got some nice solutions for IgnitedRecord with chaining.
They work like this:
// $this->user is an IR object $this->user->belongs_to('group')->col('uid'); // col is short for column // PHP 4: $this->user->belongs_to('group', array('col' => 'uid')); // or $rel_prop =& $this->user->belongs_to('group'); $rel_prop->col('uid'); // but the other one is nicer in PHP 4
So I thought we could do something like this:
// PHP 4: $a_table =& $this->forge->new_table('users'); // maybe allow a few more options to be added via a second parameter $a_table->create_column('id', // maybe short it to column array('type' => 'int', 'options' => array('auto_increment' => true, 'unsigned' => true), 'primary' => true)); $a_table->create(); // same with PHP 5: $this->forge->new_table('users') ->create_column('id') ->type('int') ->options(array('auto_increment' => true, 'unsigned' => true)) ->primary(true) ->end() ->create();
I can explain how i works, if you want.
I think some more abstraction would be nice, because I feel like I don’t have enough control when modifying tables.
I think I’m using the wrong term here. I’m not looking to create some type of ORM thing for the Forge, the goal here is to create a mechanism for translating data type and columnar constraints from one type of database syntax to another. It should integrate seamlessly with the Forge as it is now. If the EL guys want to make a bunch of changes to parameters and the like, awesome, but that’s not my goal with this.
For instance, you know MySQL but if you were hired to write something that used Oracle, you’d have to do all kinds of research on data types and everything. What I want this to do is take in to account that you’re coding with MySQL syntax and translate data types and column constraints into something Oracle can use.
