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.<\/p>\n\n\n\n
As with most things, working with databases has had a \u201csemi official\u201d way established.<\/p>\n\n\n\n
Most plugins tend to do things in the following way.<\/p>\n\n\n\n
The table structure is placed in a file. This is commonly named struct.sql<\/em> but could be named anything. Here\u2019s a small mock-up of a table for storing some random extra data for a users profile.<\/p>\n\n\n\n Osclass internally uses prefixes on column names to convey what type of data the column contains.<\/p>\n\n\n\n The comment \/*TABLE_PREFIX*\/<\/em> will be automatically replaced with the prefix defined in the config.php<\/em> file in the root folder of your Osclass installation.<\/p>\n\n\n\n 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<\/em> column might have a table with mapping between datatype id\u2019s and actual names. However, this could also be mapped in the PHP code.<\/p>\n\n\n\n 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<\/p>\n\n\n\n Most plugins then create their own DAO<\/em> object by extending the DAO<\/strong> 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\u2026 hehe). I would suggest that you at least use the base DAO<\/strong> object in Osclass to communicate with your database since it handles a lot of work for you.<\/p>\n\n\n\n Extending it and making your own class is good if you want to encapsulate functions within it.<\/p>\n\n\n\n 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<\/p>\n\n\n\n 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.<\/p>\n\n\n\n There are some issues with this approach. Not really problems with Osclass itself, but things people should think about.<\/p>\n\n\n\n Most sites I have visited has has their struct.sql<\/em> 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\u2019s quite easy to locate schema files to view. You could \u201cfix\u201d this by editing your .htaccess<\/em> file to not allow .sql<\/em> files to be viewed. Something lite this should work.<\/p>\n\n\n\n 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 \ud83d\ude42<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Our previous example table in a plugin named extendedProfile <\/em>would be created something like this.<\/p>\n\n\n\n Note that most look the same. But we use \/*TABLE_NAME*\/<\/em> instead of \/*TABLE_PREFIX*\/<\/em>. This is a new option in dliCore that will equate to \/*TABLE_PREFIX*\/<\/em> plus the $_tableName<\/em> of the table in question. This is mostly for convenience.<\/p>\n\n\n\n Since the struct is now part of the class it\u2019s not visible to a user trying to access it like it could have been in a .sql<\/em> file.<\/p>\n\n\n\n The AbstractTable<\/strong> base class contains functions to install and uninstall the table. We could add an optional function called updateSchema <\/em>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.<\/p>\n\n\n\n In that function any alterations to lift a schema to new versions could be carried out.<\/p>\n\n\n\n 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<\/em> function.<\/p>\n\n\n\n The _registerTable<\/em> function is a member function of he Plugin<\/em> 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\u2019s needed.<\/p>\n\n\n\n Any table registered with a plugin has their installSchema<\/em> function called when the plugin is installed and their uninstallSchema<\/em> function called when a plugin is uninstalled.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Note that we didn\u2019t 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\u2019s 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<\/strong> class. I will talk more about that next time.<\/p>\n\t\t\t\n\n\nCREATE TABLE \/*TABLE_PREFIX*\/t_extendedprofile_data (\n fk_i_user_id INT UNSIGNED,\n i_data_type INT UNSIGNED NOT NULL,\n s_data_text VARCHAR(256) DEFAULT NULL,\n PRIMARY KEY (fk_i_user_id),\n FOREIGN KEY (fk_i_user_id) REFERENCES \/*TABLE_PREFIX*\/t_user (pk_i_id) ON DELETE SET NULL\n) ENGINE=InnoDB DEFAULT CHARACTER SET 'UTF8' COLLATE 'UTF8_GENERAL_CI';<\/pre>\n\n\n\n
$path = osc_plugin_resource('userprofile\/struct.sql') ;\n \t$sql = file_get_contents($path);\n\n \tif(!$this->dao->importSQL($sql) ){\n \t\tthrow new Exception( $this->dao->getErrorLevel().' - '.$this->dao->getErrorDesc() ) ;\n \t}<\/pre>\n\n\n\nDAO objects<\/h3>\n\n\n\n
public function getUserData($userId, $dataType) {\n $this->dao->select('s_data_text');\n $this->dao->from( $this->getTable());\n $this->dao->where('fk_user_id', $userId);\n $this->dao->where('i_data_type', $dataType);\n\n $result = $this->dao->get();\n\n if( !$result) {\n return null;\n }\n\n $row = $result->row();\n if($row['s_data_text'] === null) {\n return null;\n }\n return $row['s_data_text'];\n}<\/pre>\n\n\n\nSomething to think about<\/h3>\n\n\n\n
<FilesMatch \"\\.sql\">\n Order allow,deny\n Deny from all\n Satisfy All\n<\/FilesMatch><\/pre>\n\n\n\n
How you could do it using dliCore<\/h2>\n\n\n\n
Defining a table in dliCore<\/h3>\n\n\n\n
<?php\nnamespace extendedProfile\\Table;\n\nuse dliLib\\Db\\AbstractTable;\nclass extendedProfileTable extends AbstractTable\n{\n protected $_tableName = 't_userProfile';\n \n protected $_struct = \n \"CREATE TABLE IF NOT EXISTS \/*TABLE_NAME*\/ (\n fk_i_user_id INT UNSIGNED,\n i_data_type INT UNSIGNED NOT NULL,\n s_data_text VARCHAR(256) DEFAULT NULL,\n PRIMARY KEY (fk_i_user_id),\n FOREIGN KEY (fk_i_user_id) REFERENCES \/*TABLE_PREFIX*\/t_user (pk_i_id) ON DELETE SET NULL\n ) ENGINE=InnoDB DEFAULT CHARACTER SET 'UTF8' COLLATE 'UTF8_GENERAL_CI';\";\n}<\/pre>\n\n\n\n\/**\n* Called on all registered tables when a Plugin is updated\n*\n* @param unknown $previousVersion\n* @param unknown $newVersion\n*\/\npublic function updateSchema($previousVersion, $newVersion) {\n}<\/pre>\n\n\n\nRegistering tables<\/h3>\n\n\n\n
class userProfilePlugin extends Plugin\n{\n protected function _init() {\n \/* Register Tables *\/\n $this->_registerTable('userProfile\\Table\\userProfileTable', 'userProfile');\n }\n\n...<\/pre>\n\n\n\nAccessing data<\/h3>\n\n\n\n
Comments<\/h3>\n