Database extension

You can utilize 3 approaches to store extension data to the database.

1. New tables can be created to support the data storage 
2. Existing tables can be altered to add new fields and indexes. 
3. Dataset tools are offered by AbanteCart to save data without creating or altering database. 

As first two are straight forward, we will cover #3 in more details. 
Dataset aproach offers quick and worry free method to handle data without dealing with SQL and creating specific database tables. Data can be handled via easy dataset class methods.
Good example for dataset usage will be occasionally edited information for products, categories, customers and orders data extension, polls, questioners, etc.

What is Dataset?

Say you need to develop a poll extension and need to store records of poll questions and answers. Normally, you would create and add 2 or more tables to the databases to handle data for the poll. With the dataset, we created an abstraction layer to the database, that allows you to skip database creation and go directly to handling data.
Dataset class will handle all the data storing and management for you with interface of methods provided. It is very easy. You name new dataset (traditionally table) or use existing dataset . Dataset can be created during extension installation from XML file provided in the extension package. It can be also removed with extension uninstall. No direct access to SQL or database is needed. If restriction access required to the dataset, it needs to be handled pragmatically based on AbanteCart permissions. If you decide to use traditional database tables in extension, you will need to create SQL scripts with install, uninstall and disable extension management process.

How does Dataset work?

First you have to recognize namings in dataset class. There are “object”, “datarow”, “fieldset” and their “definitions”.

“Object” is any abstract part of your extension or core. It may be anything that we call “owner”. Object is owner of data sets. Menu, charts, polls, etc. may contain some data, therefore, they are owners of data sets, objects. “Object” entity have two attributes: name and key. Name is common text unique identifier of the object (menu, poll, etc). “Key” is identifier from another section or table of AbanteCart, for example, extension’s key(id). Pair “name-key” must be unique. Key can be also understood as a sort of foreign key to any other table in the system. Key is not required and if not provided name part of the object needs to stay unique. Foreign key connection to any table can be also build pragmatically with use any field in the dataset fieldset.
Key can also be a subset name in case of multiple tables used within one feature or set.

In our poll example you have poll_questions and poll_answers tables. You will create 2 datasets as following:

$poll_qs = new ADataset();
$poll_qs -> createDataset('poll','poll_questions');
$poll_asw = new ADataset();	
$poll_asw -> createDataset('poll','poll_answers');

To work with dataset object you just create a new instance of dataset class or use one that you have created before:

//Create new instance:
$my_data_obj = new ADataset ( $object_name, $object_key );
After you create a new dataset you can set properties to the dataset. These properties are global to given dataset and can be always be accessible. Properties can be some generic values that give a state to the dataset:
$my_data_obj->setDatasetProperties(  array( name => value, ….  )  );

or

$poll_qs->setDatasetProperties(	 array(  	'start_date'=>'2011-07-01 12:00',
   					 	'stop_date'=>'2011-08-01 12:00')	);

Setting properties method:

$my_data -> setDatasetProperties(	
 array(
"some_property_name"=>"some_value", 			
"some_property_name"=>"some_value") );

Properties can be accessed with getDatasetProperties() method. This method returns an array with all the properties:

$my_data_obj->getDatasetProperties();

Next step for new dataset, is to set columns and properties for them. This is similar in concept of creating fields for the database. Each dataset is a representation of one table and it has columns and rows.

// define columns for questions
$poll_qs->defineColumns (
   		 array ( "name"=>"language_code",
   			 	"type"=>"varchar"),
   		 array ( "name"=>"question",
   			 	"type"=>"text"),
      		 };

Dataset’s columns definition contain list of value’s descriptions (fields). Every dataset consist of columns or fieldset. Now dataset concept supports following column types: 
integer
float
varchar
text
boolean
timestamp

//set properties for the dataset
$poll_qs->setColumnProperties ( 
array ( "column_name" => "language_code",
   				 "property_name" => "length",
   				 "property_value" => "2" ),		
array ( "column_name" => "question",
   				 "property_name" => "translation_key",
   				 "property_value" => "question_txt" ),		 
   			 );

Column properties are used to keep global information for the columns for the given dataset . This properties can be used to identify data type and constrains for the column. Validation code can use these properties before saving data.

In some cases, column properties can be used to store data and act as 1 row table. If you have only 1 row of data and it is not changing you can just use columns properties to store data and avoid extra step to use rows.
Columns properties are not required, but helpful.
Actual data for the datasets is stored in rows that are similar to rows in traditional database or spreadsheet. Every row have columns (fields) that are set in the defineColumns method described above.

Let first see how we can add a row to example dataset:

$poll_qs->addRows (  array(
	array(   language_code => "en", question => "What color do you like?" ),
	array(   language_code => "en", question => "What shape do you like?" )  
) );

If all rows are inserted successful the return will be total number of rows added that shouldbe same as imputed array count.

Now we can select all the rows from the dataset

$rows = poll_qs->getRows ();

Or for big datasets we can search for specific row based on needed column:

$rows = poll_qs->searchRows ( array(   name=> "language_code",  operator => "=",  value => "en"   )  ); 	

or

$rows = poll_qs->searchRows ( array(   name=> "question",  operator => "LIKE",  value => "color"   )  ); 

where “condition” may be one of list:
“=”
”>”
”<”
LIKE

To delete the row from the dataset you can simply call method deleteRows

$my_data->deleteRows( array( "column_name"=>string, "operator"=>string, "value"=>string )  );

If success, method will return total number of rows deleted.

To drop dataset you can use simple command:

$my_data->dropDataset();

To make things simple to batch load there is a method to load data set via XML. You can create and populate data set with one function loadXML and simple XML node structure

<?xml version="1.0" encoding="UTF-8"?>
<datasets>
	<dataset>
		<action>update</action>
		<dataset_name>abo</dataset_name>
		<dataset_key>menu</dataset_key>		
		<dataset_definition>
			<column_definition>
				<column_name>menu_id</column_name>
				<column_type>integer</column_type>
				<column_sort_order>1</column_sort_order>				
	 		</column_definition>
	 		<column_definition>
				<column_name>menu_name</column_name>
				<column_type>varchar</column_type>
				<column_sort_order>2</column_sort_order>				
	 		</column_definition>
	 		<column_definition>
				<column_name>menu_text</column_name>
				<column_type>varchar</column_type>
				<column_sort_order>3</column_sort_order>				
	 		</column_definition>
	 		<column_definition>
				<column_name>sort_order</column_name>
				<column_type>integer</column_type>
				<column_sort_order>4</column_sort_order>				
	 		</column_definition>	
	 	</dataset_definition>	 	

	 	<dataset_properties>
	 		<dataset_property>	 			
	 			<dataset_property_name>some_prop</dataset_property_name>
	 			<dataset_property_value>25100</dataset_property_value>
	 		</dataset_property>	 		 		
	 	</dataset_properties>

	 	
	 	<column_properties>
	 		<column_property>
	 			<column_name>menu_id</column_name>
	 			<column_property_name>max_id</column_property_name>
	 			<column_property_value>100</column_property_value>
	 		</column_property>
	 		<column_property>
	 			<column_name>menu_id</column_name>
	 			<column_property_name>min_id</column_property_name>
	 			<column_property_value>10</column_property_value>
	 		</column_property>	 		
	 	</column_properties>
	 	
	 	<dataset_rows>
	 		<dataset_row>
	 			<cell>
	 				<column_name>menu_id</column_name>
	 				<value>11</value>
	 			</cell>
	 			<cell>	
	 				<column_name>menu_name</column_name>
	 				<value>some name1</value>
	 			</cell>
	 			<cell>	
	 				<column_name>menu_text</column_name>
	 				<value>some text1</value>
	 			</cell>
	 			<cell>	
	 				<column_name>sort_order</column_name>
	 				<value>1</value>
	 			</cell>
	 		</dataset_row>
	 		<dataset_row>
	 			<cell>
	 				<column_name>menu_id</column_name>
	 				<value>12</value>
	 			</cell>
	 			<cell>	
	 				<column_name>menu_name</column_name>
	 				<value>some name2</value>
	 			</cell>
	 			<cell>	
	 				<column_name>menu_text</column_name>
	 				<value>some text2</value>
	 			</cell>
	 			<cell>	
	 				<column_name>sort_order</column_name>
	 				<value>2</value>
	 			</cell>
	 		</dataset_row>
	 		<dataset_row>
	 			<cell>
	 				<column_name>menu_id</column_name>
	 				<value>13</value>
	 			</cell>
	 			<cell>	
	 				<column_name>menu_name</column_name>
	 				<value>some name3</value>
	 			</cell>
	 			<cell>	
	 				<column_name>menu_text</column_name>
	 				<value>some text3</value>
	 			</cell>
	 			<cell>	
	 				<column_name>sort_order</column_name>
	 				<value>3</value>
	 			</cell>
	 		</dataset_row>
	 	 </dataset_rows>	 	
	</dataset>
</datasets>