Hoppa till innehåll

Database Tables for Osclass plugins using dliCore

One thing that a lot of plugins wish to do is to save persistent data. This can be done in a number of ways. There are volatile ways like storing them in memory etc and non volatile ones like storing data to files, databases and similar. Since Osclass itself uses the MySQL database (probably works fine with Miranda DB to) a lot of plugins use this to store their data.

As with most things, working with databases has had a “semi official” way established.

Most plugins tend to do things in the following way.

Define table structure

The table structure is placed in a file. This is commonly named struct.sql but could be named anything. Here’s a small mock-up of a table for storing some random extra data for a users profile.

CREATE TABLE /*TABLE_PREFIX*/t_extendedprofile_data (
    fk_i_user_id INT UNSIGNED,
    i_data_type INT UNSIGNED NOT NULL,
    s_data_text  VARCHAR(256) DEFAULT NULL,
    PRIMARY KEY (fk_i_user_id),
    FOREIGN KEY (fk_i_user_id) REFERENCES /*TABLE_PREFIX*/t_user (pk_i_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET 'UTF8' COLLATE 'UTF8_GENERAL_CI';

Osclass internally uses prefixes on column names to convey what type of data the column contains.

The comment /*TABLE_PREFIX*/ will be automatically replaced with the prefix defined in the config.php file in the root folder of your Osclass installation.

Some plugins have one table defined in the file, some have several, others have several files with one table in each file etc. For instance our i_data_type column might have a table with mapping between datatype id’s and actual names. However, this could also be mapped in the PHP code.

This file is then read and executed during plugin installations. I have seen several ways, but a common one is to call something like this in the plugins install function

$path = osc_plugin_resource('userprofile/struct.sql') ;
        	$sql = file_get_contents($path);

        	if(!$this->dao->importSQL($sql) ){
        		throw new Exception( $this->dao->getErrorLevel().' - '.$this->dao->getErrorDesc() ) ;
        	}

DAO objects

Most plugins then create their own DAO object by extending the DAO class of Osclass. I have also seen examples of plugins doing queries directly in their own functions (often contained in a very messy index.php file… hehe). I would suggest that you at least use the base DAO object in Osclass to communicate with your database since it handles a lot of work for you.

Extending it and making your own class is good if you want to encapsulate functions within it.

If we for example wanted to get the data of a particular type for a given user you could have a function in your DAO object that looked something like this

public function getUserData($userId, $dataType) {
  $this->dao->select('s_data_text');
  $this->dao->from( $this->getTable());
  $this->dao->where('fk_user_id', $userId);
  $this->dao->where('i_data_type', $dataType);

  $result = $this->dao->get();

  if( !$result) {
    return null;
  }

  $row = $result->row();
  if($row['s_data_text'] === null) {
    return null;
  }
  return $row['s_data_text'];
}

You would then build up more and more functionality and add more and more functions for what you needed to be able to do with your data.

Something to think about

There are some issues with this approach. Not really problems with Osclass itself, but things people should think about.

Most sites I have visited has has their struct.sql files visible. That means that anyone could type in the url to the struct file and view the layout. This could then be used to identify potential ways to compromise the site. Since most plugins use this common approach and naming scheme it’s quite easy to locate schema files to view. You could “fix” this by editing your .htaccess file to not allow .sql files to be viewed. Something lite this should work.

<FilesMatch "\.sql">
    Order allow,deny
    Deny from all
    Satisfy All
</FilesMatch>

Also, passing data around as associative arrays makes it easy to introduce errors that go unnoticed until code is in production. If you use an IDE that gives you nice warnings about typos in function names etc, make sure you type your array keys correctly since it will not report errors in those 🙂

How you could do it using dliCore

Since dliCore is all about encapsulating things and streamlining there is functionality in place to handle databases to. Instead of manually adding code to a plugins install and uninstall function to read and execute code to install the needed schemas dliCore let you define tables as classes.

Defining a table in dliCore

Our previous example table in a plugin named extendedProfile would be created something like this.

<?php
namespace extendedProfile\Table;

use dliLib\Db\AbstractTable;
class extendedProfileTable extends AbstractTable
{
    protected $_tableName = 't_userProfile';
    
    protected $_struct = 
    "CREATE TABLE IF NOT EXISTS /*TABLE_NAME*/ (
      fk_i_user_id INT UNSIGNED,
      i_data_type INT UNSIGNED NOT NULL,
      s_data_text  VARCHAR(256) DEFAULT NULL,
      PRIMARY KEY (fk_i_user_id),
      FOREIGN KEY (fk_i_user_id) REFERENCES /*TABLE_PREFIX*/t_user (pk_i_id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARACTER SET 'UTF8' COLLATE 'UTF8_GENERAL_CI';";
}

Note that most look the same. But we use /*TABLE_NAME*/ instead of /*TABLE_PREFIX*/. This is a new option in dliCore that will equate to /*TABLE_PREFIX*/ plus the $_tableName of the table in question. This is mostly for convenience.

Since the struct is now part of the class it’s not visible to a user trying to access it like it could have been in a .sql file.

The AbstractTable base class contains functions to install and uninstall the table. We could add an optional function called updateSchema which will be called if a plugin is updated. It will in that case be passed the previously installed version and the newly installed one.

/**
* Called on all registered tables when a Plugin is updated
*
* @param unknown $previousVersion
* @param unknown $newVersion
*/
public function updateSchema($previousVersion, $newVersion) {
}

In that function any alterations to lift a schema to new versions could be carried out.

Registering tables

In order to have your table installed and uninstalled with a plugin (as well as notified when a plugin is updated) you register it with the plugin that is the owner of it. This can be done in your plugins _init function.

class userProfilePlugin extends Plugin
{
  protected function _init() {
    /* Register Tables */
    $this->_registerTable('userProfile\Table\userProfileTable', 'userProfile');
  }

...

The _registerTable function is a member function of he Plugin class and takes either an instance of the table or the class name of the table and an internal name. Using the class name makes sure that no instance is created until it’s needed.

Any table registered with a plugin has their installSchema function called when the plugin is installed and their uninstallSchema function called when a plugin is uninstalled.

So adding a table to a dliCore based plugin is a matter of creating your table class like above and registering it with the plugin. The rest is done for you.

Accessing data

Note that we didn’t create any functions to access the data in our table class like we did in our DAO object. You could do this if you want to. As with most things in dliCore it’s optional to use features or stick to the old school way of doing things. But another way to access and work with data objects is to use the dliCore dbModel class. I will talk more about that next time.

Kommentarer

Dela i ditt aktivitets flöde

Powered by WP LinkPress

Kommentarer

Dela i ditt aktivitets flöde

Powered by WP LinkPress