DyDL - Part 2: What is MagicDB?
MagicDB is the codename for the Dynamic Data Layer that I built to help me with my much larger project, my fabled “Capstone” for my Masters of Science Degree. First a couple technical details:
- MagicDB currently runs on PHP 5.x.
- MagicDB currently only interfaces with MySQL.
- MagicDB is built using an Object-Oriented paradigm.
- MagicDB uses PHP’s mysqli engine.
Understanding the technical bits will explain why some decisions have to be made. PHP is used pragmatically for myself, since that is the language I used to build my capstone with. It’s free. That helps. MySQL is also free, and one of the most common companions with PHP. The mysqli engine is used primarily because it provides the support for prepared statements. Using a prepared statement allows us to curb SQL injection attacks, which addresses one of our main concerns with security.
MagicDB has only a couple rules that need to be followed.
- Data Objects (PHP) must follow specific naming requirements.
- Data Objects (PHP) and Data Tables (MySQL) must follow specific structural requirements.
Naming Requirements are actually pretty simple. All data objects used with MagicDB need to follow the pattern [Prefix]TableName. The prefix can be whatever the developer wants. For my capstone project (codename: TWiG), I used the prefix “TWiGdb_”. This means for a table in my database named “Entry”, I have a corresponding data object with the class name of TWiGdb_Entry.
Structural Requirements are a little more in-depth. Originally, the structures were required to be identical. This meant that for every column in the database, the data object should have a column for that as well. I improved upon this by adding a column to every database table (but not the data object) called “exist”. This column is a flag to show if the row was removed from the database, it has a default value of true. This allows us to “remove” data, without deleting it (although functionality is built to actually DELETE data), in case we require that data later.
Another structural requirement is that the first column in the data object MUST be the primary key for the table. In the table, the first column must be the primary key as well. This allows us to issue UPDATE commands easily. A constructor with all the attributes as parameters is also required. Order of the parameters and attributes must also match across the board.
The final requirement is that each attribute of the data object MUST be using the same exact name as the corresponding column in the table.
For example the table User would be described like this:
The corresponding data object would look like this:
Once we can build the structure of the database, and the corresponding data objects, we can begin to discuss how the DyDL works. MagicDB itself really has a simple interface to use.
In the next few posts we’ll discuss each of these methods, and talk about some of the private methods that power the implementation.
So… it is an object to database mapping layer? What is the intent, to allow application developers to use a database backend without having to know SQL? And last question - why do you call it a dynamic database layer?
Yes, at the topical level its a object to database mapping layer. However, The intent is not to hide SQL from the user (although for most purposes it does), it is an attempt to try to make it easier to accept/manipulate data.
The data layer is dynamic in a few senses, most of which will be coming out in the next few parts… but as a heads up..
PHP mysqli does not easily allow for dynamic binding of prepared statements - this system will allow for that. All processes in MagicDB are not injectable, by using true prepared statements, not escape characters.
The get() method (part 4) uses a little reflection to return data as requested.. nothing too fancy here, but hopefully more in the works later in life.
(the following features are subject to change, as I am only a few weeks ahead of these posts)
Advanced queries (which are essentially query pass-throughs) will also bind dynamically under the hood. This allows the end user to not need to worry about the fiddily bits of mysqli. Also, since you can’t fetch_assoc_array as a prepared statement, the advanced pass through method does the work of making that happen for you. (I’ll be talking about the advanced passthrough prolly around part 6 or 7).
The other dynamic feature, which is less of a run-time feature, but just a handy built in tool will be the object generation wizard. The idea behind this is, you don’t need to write the objects yourself, but just point the wizard at the database and the objects will be generated automagically. Still have to hash out exactly how it’ll work.. but my initial test code worked nicely.
Hope the project still seems interesting - I will be writing about it for the next few weeks. I’m doubling this as my outlines for my actual capstone paper, so bare with me even if I talk about the dull stuff a little too long.
“The other dynamic feature, which is less of a run-time feature, but just a handy built in tool will be the object generation wizard. The idea behind this is, you don’t need to write the objects yourself, but just point the wizard at the database and the objects will be generated automagically. Still have to hash out exactly how it’ll work.. but my initial test code worked nicely.”
Such a “blindfolded” approach is quite interesting I must say. I’d like to see how you end up implementing this.
Keep it up!
What you both are talking about here is object-relational mapping (ORM) - never heard of a “Dynamic Data Layer”.. sounds like a made up phrase. There’s lots of PHP object-relational mappers that are pretty solid and go beyond simple CRUD operations to handle data relationships with other features. One of the most famous ORMs in use for web apps is in the Ruby language called “ActiveRecord” which introspects the data schema at runtime to expose attributes (sounds similar to your auto-generation script, except this is do dynamically at runtime) and has an elegant DSL for declaring relationships.
Also, using a column to specify existence/deletion is a great idea but be careful with this as *every* query involving the table (even through multiple joins/subqueries) *must* check this field, or you’ll have phantom data start to appear. You also may want to consider changing this to a timestamp like deleted_at whereby NULL represents an existing row and NOT NULL means its deleted (with the added benefit of recording the time at which this was done).
Good luck on the project, and your Capstone!