devmorgan.com

So, before I sit back and talk about all of the actual methods on the MagicDB object, we need to talk about a problem that is specific to PHP and the mysqli extension.

Now, the basic idea I had was to build something that could generate prepared statements dynamically. This will (if done correctly) help protect against SQL injection, and ideally help speed up a few frequently run queries (as long as the SQL structure is always consistent). We need to select mysqli because it has the mysqli_stmt::prepare ( string $query ) function.

Ok, so, now we come to the binding parts of the process… and here is where it gets sticky. We are presented with these two methods:

mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )
mysqli_stmt::bind_result ( mixed &$var1 [, mixed &$... ] )

This presents us with a problem. Notice how both methods (which are required for prepared statements) allow for multiple parameters. This is handy — wait.. really.. it’s never handy. I really don’t know why they designed it this way. In fact.. Damn those PHP people. OK.. so maybe I do know why,  but it gets in to reference/value discussion.. so.. lets move on and just figure out a way to fix it.

In an ideal world, for being dynamic, the functions should be designed something more like this:

mysqli_stmt::bind_param ( string $types ,  array &$params )
mysqli_stmt::bind_result (  array &$results )

Instantly this becomes a lot better. Since the parameters/results are stored as an array, they can be dynamically generated and of any length.  To address this concern, we need to tackle both functions a little bit differently. First off, the bind_param function:

I wrote a simple private function that is included in MagicDB that will accept a mysqli_stmt object, and a list of parameters. From it, the function will call the bind_param method with the proper values and return the statement object.

Now, lets discuss the major concern with this code. The data types of the parameter array that is passed in are used for truth. So, as a developer you need to be careful. This will not cause any security risks, but it may reject your parameter if the wrong data type is used. This is a known concern, but one that I am not worried about since I know to pass an int when I need an int. The beauty of this code is in the last couple lines. I use the php variable-variable-name functionality to generate a dynamic variable reference. I call each variable $bind#, and then assign the parameter to it. I then can use the & symbol to add the variable by reference to the array. Using  the PHP function call_user_func_array, we can get around the multiple parameter problem by simple passing an object reference, a method name, and an array of parameters.

Now for the binding of the result. This code isn’t stored in a function, although it could be. It’s stored in my “get” method on the MagicDB object. Some variables are out of scope, but here is the snip.

So here, without getting into too much detail about how the get() function works, we want to return an object of the type that $object is.  The object should have all the values populated with the results of the database. We bind the variables in the same manner that we bound the parameters in the previous function.  Where it gets fancy is in the use of reflection. We get the type of the object, and pass it into what is called a ReflectionClass.

This will generate a generic class of the type we specify. We must then instantiate the object by calling newInstanceArgs() method. This allows us to pass an array with the parameters for the constructor. This is why the constructor is required in all of the data objects. We do this for each iteration of the fetch, and return the array of objects.

Sexy eh?

Next up we talk about the API and how the MagicDB functions actually work.

coding, data, technology

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.

1. Data Objects (PHP) must follow specific naming requirements.
2. 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.

coding, data, technology

So, I figure it’s about time I actually talk a little bit about code. I know many of my readers aren’t programmers, so my apologies. For my capstone work I have been playing around a lot with data, and I want to share my experiences.

So, first things first. Lets talk about Dynamic Database Layers. We want to build a database layer that is as dynamic as possible and flexible, but provides understandable structure and expected results. We want something that is secure, but also usable. So lets break it down:

What are the goals?

1. Ease of use.
2. Low learning curve.
3. Clear and understandable “rules”
4. Secure
5. Solves routine problems naturally.
6. Allows non-routine problems to be solved easily.

Ease of use. The first and foremost goal we have for our DyDL is that its easily usable. Ideally, it should make accessing the data simple and trivial. If you need to write lines upon lines of code just to invoke simple calls, developers won’t use it. Make easy things easy. If a developer needs to do something more complicated, try to make it easy - but they’ll be willing to do more work to accomplish it.

Low learning curve. One of the most difficult tasks as a programmer is to adapt to new technology. Understanding the lingo, the structure, and the benefits is required to be efficient. Don’t make it difficult to use. Use common paradigms to make entry into use easy. This leads us into the next requirement..

Clear and understandable “rules”. Make how the software layer works straight forward and simple. Don’t build an application that has situations where if you want to do one thing, you have to do it differently than another thing. Make it clear to the developer what each function is actually doing. If all data objects require certain structure, make it clear. Giving meaningful messages and documentation for the layer.

Secure. This is straight forward. Make your best attempt to make the code secure. If you can prevent SQL injections, do so. Build as much structure to the framework such that you can limit the ways to exploit it. Don’t trust unknown data.

Solves routine problems naturally. Make simple stuff simple. If you need to do a simple access of data based on a few parameters, make it so that is one simple call. Try to abstract your calls so the developer doesn’t need to know the SQL that is being run underneath. Allow simple complexity by allowing the developer to do so, but don’t require them to.

Allows non-routine problems to be solved easily. Always allow the developer to decide if their way is better. Provide a simple interface such that a developer can still use your engine to do things that you haven’t accounted for. If you can, try to make it easier. If you can’t, don’t stand in their way.

For a portion of my capstone, I have implemented what I believe is an excellent start of a DyDL, codenamed MagicDB.  I will be writing about this project more in the coming weeks, and will be showing bits of code, and explaining decisions made. Hope I don’t bore anyone.

coding, data, technology