On an earlier post, I discussed in depth my idea for a new, extremely powerful data handler for PHP. I’ve been developing it for a few months, and most of the features are already implemented, but something kept bothering me – the complexity of the configuration files for database tables. At the moment, they are PHP arrays. One of my problems with this is the barrier of entry – this is an insanely powerful data handling library, and I believe that it would be possible to allow end-users (non-developers) to use it to create their own databases, manage the relationships between their data, and view/manage it easily. It might seem like a very far-fetched plan, but it is possible. Since the HTML for all the pages is generated on the fly, given a table configuration file, that means that if a tool is created to generate configuration files (and maybe even create database structures based on those same configuration files), it would allow anyone to take full control of whatever data they want.
And then I had an epiphany.
There is no need to create a tool to generate configuration files. I created four tables (as a proof of concept) on MySQL, one for the tables, one for the fields, one for relationships, and one for validation rules. By creating a table configurations file with a PHP array that connects all those tables properly, it becomes possible to create new configuration files using the data handling library itself – since the configuration data is on tables on a database, it is possible to manage it using the generated HTML pages, or actual code. When the user is done, it is possible to select a table and all its fields, and use the export() function to export that data into the php array format that is used by the data handling library.
Formidably, this all is merely an intelligent use of the library, and nothing new is necessary to make it work. The generated configuration files are PHP arrays, which means there is no extra overhead (as is the case with INI files, XML files, or even getting the configurations from the database), and they can be imported/exported on demand whenever necessary, so if anyone should ever port this library to another language, they change their “configfile” export implementation to match their language’s most efficient data structure.
But the idea of having to export configuration data into files, and having to have a database for managing configuration data still seemed too much for me. I even considered using a self-contained SQLite database, but that would still force users to export data into configuration files. I could make it so that it would use the SQLite database rather than relying on php files and arrays, but that would mean overhead that I wasn’t willing to accept. So, I came up with something else:
The database containing the four tables will be in the format of a PHP array itself, (which is possible thanks to how easy I’ve made it to create new database drivers), which removes the need for exporting completely. The php configuration files will instead be edited directly. By default, php arrays will be used, but any other data driver can be used, if you wish (which means you can use YAML, XML, or even a real database system, such as MySQL or SQLite). Included in the distribution of this library as well, there will be a php script that can be run on the browser to effortlessly start managing table configurations.
A few additional methods are included in this library to make it even easier to get started: discoverTableStructure(), discoverDatabaseStructure(), createTableStructure() and createDatabaseStructure().
The discover structure methods simply look up a table or a database’s structure and generate a configuration file from it, as well as adding it to the SQLite database for easy management.
The create structure methods take an existing configuration file and automatically create the table structure represented by them. This helps importing database structures onto a variety of database systems, very easily.
updateDatabaseStructure()
This is actually one really big problem for most developers: How to keep the database structure up to date at all times. There are a few projects for keeping a database under a version control system, the best of which is (in my opinion) dbdeploy. They use a table to keep track of what updates have been applied to any given database, and the so-called delta files to figure out what SQL to execute for a certain update, and how to roll it back.
I wanted to take this step a bit further. Imagine opening up your users table in the database admin, adding an extra field called BIRTHDATE, checking the box that says “Do you want to update the actual database structure?”, pressing enter, and knowing that that’s the last thing you’ll need to worry about? This involves a bit of set up on the repository’s side – you have to tell it to ignore the dbversion file, because that’s what contains the current database version, and you need to set it up to automatically run updateDatabaseStructure() upon deployment. These things can still be done manually, but my goal is to make it as effortless as possible, and if we can use the version control system to help us out, that’s even better.
And if you ever make any changes to the database structure that require you to run some SQL to keep it all working, you can add that in along with the update (but you’ll be required to write it in code so that the library can generate the SQL for all the different database systems and generate rollbacks as well).
Well, that’s one thing out of the way – the database structure. That’s extremely important for me, because it’s the foundation of the whole library. I know that most database libraries don’t rely so much on configuration files describing the database structure, field validation rules, relationships, and all of that, but this isn’t merely a database library, and it is this reliance in these configurations that allows me to make it even easier for people to use the library, and even manage their databases. With this library, you will never have to worry about anything related to your data again. Sheer simplicity is on its way.