TableEditor for CodeIgniter: User Guide Version 0.4

Contents


TableEditor for CodeIgniter: What is it about?

TableEditor for CodeIgniger provides a convenient way to edit your tables in your database. You will provide in a declarative manner through a configuration file which of your tables (and its columns) shall be editable. TableEditor combines it with CodeIgniter's powerful form-validation class.
Here is a given scenario: You have a table containing countries and you would like to make it editable. No need to write any further CRUD-operation with Controllers and Models. This small framework will handle that for you! You might even want to include it into your existing CMS.

Features:


System Requirements


Installation

For a quick start, it is recommended to download the bundle, containing CodeIgniter Version 1.7.2. Following files are needed if you would like to install them manually: If you will use hooks, you will need following additional file: Please note that there is already a german translation among the language-files.

After you did download the bundle, make sure your database is up and running. Run the content of CREATE_TABLE.sql in order to have a running example. The file resides on the root-folder of the bundle. Don't forget to adjust the credentials in config/database.php and to set 'base_url' in config/config.php according to your environment.
Once you are done with the installation, call: http://'your_base_url'/index.php/tableEditor/itemOverview/Country If a page with the title 'Administration Countries' appears, you were successfull.

Please note that there is a CSS included in the bundle: css/te_stylesheet.css It is up to you to adjust or create your own css.


Requirements for your tables

In order to make TableEditor work with your tables, they need to provide following three columns:

Column Description
Id The Id identifies each entry by an unique number. Autoincremented.
HdCreateDate TableEditor will add automatically the creation-date of a new entry.
HdOrder According to this column, TableEditor orders the items.


TableEditor's metadata: Make your tables alive

The most important file you need to know is config/tableEditor_metadata.php. Here you define in an associative array all your tables you would like to make editable.

Define your tables

The root-items declarate the tables you would like to edit. According to the example, we would like to edit our table Country: $config = array(
   // Table Country
   'te_Country' => array( [...] ),
); Each table you add needs to have the prefix te_ in order to avoid name-conflicts with other configuration-variables. This results in te_Country.

Optionally, you could define following nodes within the table's node:

Node Type Description Default
label string Optional. Give your table a label. It'll be used instead of the table's name.
description string Optional. You can add some more information about the table and its data.
addItem boolean Optional. If set to false, it won't be possible to add new items to the table. true
deleteItem boolean Optional. If set to false, it won't be possible to add items from the table. true
order boolean Optional. If set to false, it won't be possible to manually move the items up or down within the table. true
orderBy string Optional. Here you can define one or more columns according to which TableEditor will sort your overview. If you define it, the node order will be overwritten to false.
Example: 'orderBy' => 'Name DESC, Filename'
hook array Optional. Here you can define methods which shall be called by the available hooks.
Example: 'hook' => array('pre_saveItem' => 'doc_beforeSave')


Here a code-example: $config = array(
   // Table Country
   'te_Country' => array(
         'label' => 'Administration Countries',
         'description' => 'Here you can administrate your countries.',
         'addItem' => false,
         'deleteItem' => false,
         'order' => false,
         [...]
   ),
);

Define the table's overview

Each table-node needs to implement the node overview. This node contains at least one further node defining what shall be listed in the overview:

Node Type Description Default
label string Mandatory. Labels the column.
column string Mandatory. The table's column which shall be listed in the overview.
search bool Optional. If set, a input-field within the search-form is generated for this column. false

Here a code-example: $config = array(
   // Table Country
   'te_Country' => array(
         [...]
         'overview' => array(
                     'label' => 'Countrycode',
                     'column' => 'Code'
               ),
               array(
                     'label' => 'Description',
                     'column' => 'Description',
                     'search' => true
               ),
         [...]
   ),
); This example would make TableEditor to concat the columns Code and Description on each line, labeled with Countrycode and Description. Depending on your configuration, TableEditor will list the following functions for each entry:
If you didn't disable the 'add item' - feature, the link Add new entry will be generated at the top of the overview.

Define the detail-view of the table's items

Each table-node needs to implement the node detail. This node contains at least one further node defining what columns shall be listed in the detail-view:

Node Type Description Default
label string Mandatory. Labels the column.
column string Mandatory. The table's column which shall be listed in the detail-view.
editable bool Optional. If set to false, the column is listed, but can't be changed. true
inputType string Optional. Other input-types: checkbox, textarea or file. input
rules string Optional. Here you can define the validation-rules as they are used in CI's Form Validation Class. Check CI's documentation for further information.
Example: trim|required|max_length[2]
unique bool Optional. If set to true, TableEditor will check before saving if the value is unique within the column. Like this, you can prevent nasty SQL-Errors (if your column is defined as unique) and retreive a readable error-message. false
delete_restrict array Optional. Typically, some other table will contain a column with a foreign key, referencing your Country-table. You could define this column here. TableEditor would then first check if the item is referenced before deleting. If yes, deleting wouldn't be possible anymore. It behaves like a delete restrict - constraint.
Example (Table User, FK-column Country): 'delete_restrict' => array('User.Country')
foreignKey string Optional. This will define the column as a foreign-key, telling TableEditor to load the defined lookup-table in a drop-down.
Example: 'foreignKey' => 'Country.Code,Description'
virtual bool Optional. If set to true, the column won't be loaded from the DB. This becomes usefull when you use hooks. false

Here a code-example: $config = array(
   // Table Country
   'te_Country' => array(
         [...]
         'detail' => array(
                     'label' => 'Creation date',
                     'column' => 'HdCreateDate'
                     'editable' => false
               ),
               array(
                     'label' => 'Countrycode (CH, DE etc.)*',
                     'column' => 'code',
                     'rules' => 'trim|required|max_length[2]',
                     'unique' => true,
                     'delete_restrict' => array('User.CountryCode')
               ),
         [...]
   ),
);

Build the URI

To build the URI is easy. You simply pass the table's name to the controller you wish to edit. Find below the examples which you can use with the bundle:
Following line loads the administration of the table Country: http://'your_base_url'/index.php/tableEditor/itemOverview/Country Following line loads the administration of the table User: http://'your_base_url'/index.php/tableEditor/itemOverview/User Following line loads the administration of the table Document: http://'your_base_url'/index.php/tableEditor/itemOverview/Document

Language

In the bundle, there is currently english and german contained. If you would like to use the german (or any other language you translated), you would need to adjust the Controller's constructor where the language-file is loaded. In the bundle, the english file is loaded.

Furthermore, you can make all labels (and the table's description) language-depending: Simply add {lang} as a prefix, followed by the language-code. Make sure you defined that code in a language-file and loaded it.
Example: 'label' => '{lang}title_countrycode'

Hooks: Teach TableEditor some additional behaving

There are moments where you would like to jump into TableEditor's system, mostly because you need some specialized behaving. That's where hooks become handy.

Currently, there are following hooks available:
Hook Description
pre_saveItem This hook is called before TableEditor saves a new or modified entry to the DB.
pre_deleteItem This hook is called before TableEditor deletes an entry from the DB.
pre_renderOverview This hook is called before TableEditor lists all items in the overview.


Here a code-example in the config-file: $config = array(
   // Table Document
   'te_Document' => array(
         'label' => 'Administration Documents',
         'description' => 'Here you can administrate your documents.',
         'hook' => array(
                  'pre_saveItem' => 'doc_beforeSave',
                  'pre_deleteItem' => 'doc_beforeDelete',
                  'pre_renderOverview' => 'doc_overview',
                  ),
         [...]
   ),
); You also might want to call more than one method with the same hook. In order to do that, define an array:          'hook' => array(
                  'pre_saveItem' => array('doc_beforeSave', 'someOtherMethod'),
                  [...] Once you decided to use hooks, you need to create a file called tableeditor_hooks.php in the folder libraries. The minimal structure of this file without any methods should look like this: class Tableeditor_hooks {

   public function __construct() {}

} Within this file, you will need to place all methods you plan to connect with the available hooks in the config-file. The different hooks require different method signatures:
Hook Expected Method Signature
pre_saveItem void pre_saveItemMethod(array &$newData, array $oldData)
  • &$newData: Contains the array with the submitted form-data you might want to modify.
  • $oldData: Contains the entire row from the database.
pre_deleteItem void pre_deleteItemMethod(array $data)
  • $data: Contains the entire row from the database, which will be deleted.
pre_renderOverview void pre_renderOverviewMethod(string $columnName, string &$columnData, array $rowData)
  • $columnName: Contains the name of the column.
  • &$columnData: Contains the data of the column you might want to modify.
  • $rowData: Contains the row from the Database containing the fields as defined in the config-file, node overview.
Please note that this hook is called for each single row and each column as defined in the node overview.

You might have noticed the ampersand (&) in front of some arguments. This is important if you plan to modify the containing data, since the ampersand makes php to pass the data by reference.


TableEditor won't expect any return-value from the methods. If something goes wrong within your code, you can tell it by throwing the following Exception: throw new TE_Exception_hook( 'Something went wrong, sorry!' ); The signature of TE_Exception_hook looks like this:
Parameter Description Default
$message This information will be sent back to the client's browser, showing the error.
$internalMessage Optional. This information is only for logging-purposes and might contain technical information.
$debugLevel Optional. Here you can define your debug-level of the message. The message will be logged with CodeIgniter's log-mechanism. For more information, see CodeIgniter's Error Handling section. 'error'

Some more advanced Exception might look like this: throw new TE_Exception_hook( 'Something went wrong, sorry!', 'Failure in Hook-Method doc_beforeSave', 'debug' );

Example: Teach TableEditor how to upload files

This example demonstrates how you can take advantage of hooks. The example is contained in the bundle as well. Make sure you set up the table Document in your database.

Config-file: ///// Table Document
'te_Document' => array(

   'label' => 'Administration Documents',      // Optional
   'description' => 'Here you can administrate your documents.',      // Optional

   'hook' => array(
      'pre_saveItem' => 'doc_beforeSave',      // Hook is called before the item is saved to the DB
      'pre_deleteItem' => 'doc_beforeDelete',      // Hook is called before the item is deleted from the DB
      'pre_renderOverview' => 'doc_overview',      // Hook is called before the labels and their data are passed to the view
      ),

   'overview' => array(
      array(
         'label' => 'Description',
         'column' => 'Name'
         ),
      array(
         'label' => 'Name of document',
         'column' => 'Filename'
         ),
      array(
         'label' => 'Document on Server',
         'column' => 'DocOnServer',
         'virtual' => true,      // No correspondending column exists in DB for 'DocOnServer', makes it handy for hooks
         ),
      ),

   'detail' => array(
      array(
         'label' => 'Create-Date',
         'column' => 'HdCreateDate',
         'editable' => false,
         ),
      array(
         'label' => 'Description*',
         'column' => 'Name',
         'rules' => 'trim|required|max_length[64]',
         ),
      array(
         'label' => 'Filename',
         'column' => 'Filename',
         'editable' => false,
         ),
      array(
         'label' => 'Browse your file to upload',
         'column' => 'Filedata',
         'inputType' => 'file',      // This will result in an html-upload input field
         'virtual' => true,      // No correspondending column exists in DB for 'Filedata', makes it handy for hooks
         ),

      ),
   ), Implemented hooks in libraries/tableeditor_hooks.php: 
class Tableeditor_hooks {

	private $CI;
	
    public function __construct()
    {
		$this->CI =& get_instance();
    }


	/**
	 * Hook is called before the item of the table documentation is saved. It uploads the selected file.
	 * 
	 * $newData contains all data which will be saved to the DB. It needs to be passed by reference (&), otherwise manipulations won't have any effect!
	 * $oldData contains the complete item-row of the table with the old data (or empty, if new entry)
	 */
	public function doc_beforeSave(&$newData, $oldData)
	{
		
		if(!isset($_FILES['Filedata']) || trim($_FILES['Filedata']['name']) == '')
			return;	// No File has been selected, upload nothing
		
		$uploadDir = 'upload/';		// You might want to load this from a config-file instead
		// Upload file. For more information, see CI's Upload class.
		$config['upload_path']		= $uploadDir;	// Upload-directory
		$config['allowed_types']	= 'pdf';		// Allow only pdf
		$config['max_size']			= '2000';		// Max. size
		
		$this->CI->load->library('upload', $config);
		
		$_FILES['Filedata']['name'] = time() . ".pdf";		// Give the file an unique name
		$newData['Filename'] = $_FILES['Filedata']['name'];	// Save the file's name into the DB-field 'Filename'
		
		if ( !$this->CI->upload->do_upload('Filedata') )
			throw new TE_Exception_hook( $this->CI->upload->display_errors('', '') );	// Something went wrong in CI's upload class, show error
		
		// We check in the old data if there is an old file around, and if yes, delete it
		if(isset($oldData['Filename']) && strlen($oldData['Filename']) > 0)
		{
			$relFilePath = $uploadDir . $oldData['Filename'];
			if (file_exists($relFilePath) )
				unlink($relFilePath);
		}
	}
	

	/**
	 * Hook is called before the item of the table documentation is deleted. Any linked file would be deleted as well from the upload-directory.
	 * 
	 * $data contains the complete item-row of the table which is about to be deleted.
	 */
	public function doc_beforeDelete($data)
	{
		$uploadDir = 'upload/';		// You might want to load this from a config-file instead
		// We check in the old data if there is an old file around, and if yes, delete it
		if( strlen($data['Filename']) > 0 )
		{
			$relFilePath = $uploadDir . $data['Filename'];
			if (file_exists($relFilePath) )
				unlink($relFilePath);
		}
	}
	
	
	/**
	 * Hook is called before the list is rendered to the overview. It is called for each single overview-item as defined under meta-data, and each row in the table
	 * 
	 * $columnName	Column's name
	 * $columnData	Contains the data of the column (by reference!) which can be changed within the hook
	 * $rowData		Contains the fields from the DB.
	 */
	public function doc_overview($columnName, &$columnData, $rowData)
	{
		$columnData = htmlspecialchars($columnData);	// Don't forget to do this: as soon as you use a pre_renderOverview-hook, you are responsible to escape special html-chars!
		$uploadDir = 'upload/';		// You might want to load this from a config-file instead
		switch ($columnName) {
			case 'DocOnServer':				// Remember? We declared this column as virtual in the metadata, now we will fill its data with some value...
				$relFilePath = $uploadDir . $rowData->Filename;
				(file_exists($relFilePath) && strlen($rowData->Filename > 0) ) ? $columnData = "<b style=\"color:green\">yes</b>" : $columnData = "<b style=\"color:red\">no</b>";
				break;
			case 'Filename':				// We insert here a link to the file
				$absFilePath = base_url() . $uploadDir . $rowData->Filename;
				$columnData = '<a href="'.$absFilePath.'" target="_blank">' . $columnData . "</a>";
				break;
		}
	}
	
}

Search form

If you defined one or more columns as searchable within the overview-node, TableEditor will generate a search form on the top of the item overview. You can use following wildcards within the input fields:
Wildcard Description
* Placeholder for no, one or more characters.
Example: '*vanni' would find all strings ending with 'vanni'.
? Placeholder for one character.
Example: 'P?ter'
... This is usefull for numeric ranges.
Example: '12...55' would find all values >= 12 and <= 55

Protect TableEditor from Unauthorised Access

Typically, you have an authorisation system in order to protect the administration-side of your RIA. Depending on your system, there are different ways to include TableEditor: Bottom line: It simply depends on your system how you will protect TableEditor from unauthorised access.

Feedback, Problems or anything else...

You would find a thread on the CI's forum here.

Licensing

This software is available under the General Public License