Interacting with the Database - Revision history

Demitri at 23:57, 6 February 2014

2014-02-06T23:57:03Z

Demitri at 23:55, 6 February 2014

2014-02-06T23:55:23Z

Jake at 21:19, 23 August 2013

2013-08-23T21:19:37Z

Jake at 22:41, 20 August 2013

2013-08-20T22:41:31Z

Jake at 22:56, 15 August 2013

2013-08-15T22:56:06Z

Jake at 22:02, 15 August 2013

2013-08-15T22:02:21Z

Jake at 21:32, 14 August 2013

2013-08-14T21:32:21Z

Jake at 20:57, 14 August 2013

2013-08-14T20:57:05Z

Jake at 22:57, 8 August 2013

2013-08-08T22:57:33Z

Jake at 22:56, 8 August 2013

2013-08-08T22:56:46Z

← Older revision		Revision as of 23:57, 6 February 2014
Line 1:		Line 1:
	[[Category:Development]]		[[Category:Development]]
−	~~{TODO: Finish CDbCommand, Models,How To's for all sections}~~
	== Introduction ==		== Introduction ==

@@ Line 3: / Line 3: @@
 == Introduction ==
-X2CRM uses MySQL for its database architecture. All tables are prefixed with 'x2_' to differentiate them from any other tables in the database and allow for users to install X2CRM without having to create a database specifically for it. Nearly all tables have an 'id' column which serves as the primary key. Relational data is handled through the x2_relationships table which allows for the specification of model types and IDs rather than through join tables. Tables which are used to store records or other user data generally have associated Model classes to allow for easier data manipulation, while some of the more technical and system data focused tables often have no model record and must be accessed/modified in other ways. There are three ways for a developer to access data stored in one of the tables.
+X2Engine uses MySQL for its database architecture. All tables are prefixed with 'x2_' to differentiate them from any other tables in the database and allow for users to install X2Engine without having to create a database specifically for it. Nearly all tables have an 'id' column which serves as the primary key. Relational data is handled through the x2_relationships table which allows for the specification of model types and IDs rather than through join tables. Tables which are used to store records or other user data generally have associated Model classes to allow for easier data manipulation, while some of the more technical and system data focused tables often have no model record and must be accessed/modified in other ways. There are three ways for a developer to access data stored in one of the tables.
 == Raw SQL ==
@@ Line 20: / Line 20: @@
 === Examples ===
-Raw SQL can (for the most part) be used in 3 ways within X2CRM. The first two are both similar and are exclusively ways of accessing rows of data from the database. These are CSqlDataProvider, and the Model's findBySql method. The purposes of these examples are to show how to get effectively the same set of data using three different methods, even though this is not the ideal situation to use Raw SQL. Thus, some of these queries are excessively simplistic and do not return very useful data, but an example of how to use SQL in this context is demonstrated.
+Raw SQL can (for the most part) be used in 3 ways within X2Engine. The first two are both similar and are exclusively ways of accessing rows of data from the database. These are CSqlDataProvider, and the Model's findBySql method. The purposes of these examples are to show how to get effectively the same set of data using three different methods, even though this is not the ideal situation to use Raw SQL. Thus, some of these queries are excessively simplistic and do not return very useful data, but an example of how to use SQL in this context is demonstrated.
 '''CSqlDataProvider'''
@@ Line 88: / Line 88: @@
 In general, I prefer to use CDbCommand only when there is no Model for the table I am working with, or if I only need 1-2 columns of data and loading the full model would be a waste of memory. It has its uses in some very specific situations but Models should usually be used in favor of this.
 == Models ==
-Models are the primary method of interaction with the database in X2CRM. All Models in the software extend one of two classes: [[x2doc:X2Model|X2Model]] or [[yii:CActiveRecord|CActiveRecord]]. X2Model is a special model class that extends CActiveRecord and is used for Models which have dynamic fields rather than a fixed set of attributes. Examples of this include Contacts, Accounts, and most of the major modules. CActiveRecord models are for tables with static field definitions and don't need the advanced functionality of X2Model. Examples of this include models like Fields, Profiles, and most non-major module related models.
+Models are the primary method of interaction with the database in X2Engine. All Models in the software extend one of two classes: [[x2doc:X2Model|X2Model]] or [[yii:CActiveRecord|CActiveRecord]]. X2Model is a special model class that extends CActiveRecord and is used for Models which have dynamic fields rather than a fixed set of attributes. Examples of this include Contacts, Accounts, and most of the major modules. CActiveRecord models are for tables with static field definitions and don't need the advanced functionality of X2Model. Examples of this include models like Fields, Profiles, and most non-major module related models.
 Models provide the easiest method of interaction with database records. For example, if we wanted to load a model into a form, allow a user to edit the data, and then update the database record, here is how it would be done in each of the three methods:
@@ Line 129: / Line 129: @@
 $model->save();
 </syntaxhighlight>
-As can be seen, the Model method is by far the simplest. What's more, none of the 3 listed methods above will actually work with the X2CRM codebase. This is because the way data is stored is not always the same as it is entered by or displayed to a user. For example, date fields display in a nice, localized format and are entered via a Date Picker widget. However, they are stored in the database as UNIX Timestamps for ease of conversion to other formats and mathematical operations. So simply setting
+As can be seen, the Model method is by far the simplest. What's more, none of the 3 listed methods above will actually work with the X2Engine codebase. This is because the way data is stored is not always the same as it is entered by or displayed to a user. For example, date fields display in a nice, localized format and are entered via a Date Picker widget. However, they are stored in the database as UNIX Timestamps for ease of conversion to other formats and mathematical operations. So simply setting
 <syntaxhighlight lang="php">
 $model->attributes = $_POST['Contacts'];

@@ Line 17: / Line 17: @@
 * Security. Improperly used SQL can create vulnerabilities in the software. Raw SQL should be avoided for user entered data if possible to prevent SQL injection. The other methods have easier ways of handling this.
-Personally I try to use this only when I need something that can't easily be accessed via CDbCommands or Models, or when I need to write a query that will have lots of dynamic parts and doesn't really fit well as a CDbCriteria.
+Personally I try to use this only when I need something that can't easily be accessed via CDbCommands or Models, or when I need to write a query that will have lots of dynamic parts and doesn't really fit well as a CDbCriteria. Obviously this is the only method of interaction with the database through an administrative interface like phpMyAdmin or Virtualmin, and no parameter binding is available.
 === Examples ===
-Raw SQL can (for the most part) be used in 3 ways within X2CRM. The first two are both similar and are exclusively ways of accessing rows of data from the database. These are CSqlDataProvider, and the Model's findBySql method.
+Raw SQL can (for the most part) be used in 3 ways within X2CRM. The first two are both similar and are exclusively ways of accessing rows of data from the database. These are CSqlDataProvider, and the Model's findBySql method. The purposes of these examples are to show how to get effectively the same set of data using three different methods, even though this is not the ideal situation to use Raw SQL. Thus, some of these queries are excessively simplistic and do not return very useful data, but an example of how to use SQL in this context is demonstrated.
 '''CSqlDataProvider'''
@@ Line 43: / Line 43: @@
 '''Find By SQL'''
 <syntaxhighlight lang="php">
 </syntaxhighlight>
 == CDbCommand ==

@@ Line 17: / Line 17: @@
 * Security. Improperly used SQL can create vulnerabilities in the software. Raw SQL should be avoided for user entered data if possible to prevent SQL injection. The other methods have easier ways of handling this.
-Personally I try to use this only when I need something that can't easily be accessed via CDbCommands or Models, or when I need to write a query that will have lots of dynamic parts and doesn't really fit well as a CDbCriteria.
+'''Find By SQL'''
 == CDbCommand ==
@@ Line 109: / Line 135: @@
 * Size. Models involve loading all of the data for a row from the database table, but have a lot of associated overhead. There are a large number of method calls and pieces of other information stored in the model that can increase overhead.
 * Limited Purpose. Models are mostly intended to be a representation of a row of data, which is fantastic if that's what you need (and for most of what X2 does, it is) but for complicated or specific queries (especially ones collecting aggregate data or statistics) they are not particularly useful.

← Older revision		Revision as of 22:56, 15 August 2013
Line 105:		Line 105:
	* Ease of use. Models are incredibly easy to work with and require little to no SQL knowledge to use.		* Ease of use. Models are incredibly easy to work with and require little to no SQL knowledge to use.
	* Functionality. Models have a very large number of built in functions to make your life easier, and we've made even more of these available through X2Model and its related behaviors.		* Functionality. Models have a very large number of built in functions to make your life easier, and we've made even more of these available through X2Model and its related behaviors.
		+	* Flexibility. Yii's code has an absolute ton of functionality designed to work with Models. Many, many things are available to load models, get lists of them and render those lists, etc.
		+	'''Cons'''
		+	* Size. Models involve loading all of the data for a row from the database table, but have a lot of associated overhead. There are a large number of method calls and pieces of other information stored in the model that can increase overhead.
		+	* Limited Purpose. Models are mostly intended to be a representation of a row of data, which is fantastic if that's what you need (and for most of what X2 does, it is) but for complicated or specific queries (especially ones collecting aggregate data or statistics) they are not particularly useful.

@@ Line 73: / Line 73: @@
 <syntaxhighlight lang="php">
 // Code to get the database ID
-$attributes=Yii::app()->db->createCommand()
+$attributes = Yii::app()->db->createCommand()
      ->select('*')
      ->from('x2_contacts')
-     ->where('id=:id',array(':id'=>$id))
+     ->where('id=:id', array(':id' => $id))
      ->queryRow();
 // Code to pass $attributes into the form and load new attributes into $_POST['Contacts']
 Yii::app()->db->createCommand()
-     ->update('x2_contacts',$_POST['Contacts'],'id=:id',array(':id'=>$id));
+     ->update('x2_contacts', $_POST['Contacts'], 'id=:id', array(':id' => $id));
 </syntaxhighlight>
 '''Model'''
 // Code to get the database ID
-$model=X2Model::model
+$model = X2Model::model('Contacts')->findByPk($id);

@@ Line 55: / Line 55: @@
 Models provide the easiest method of interaction with database records. For example, if we wanted to load a model into a form, allow a user to edit the data, and then update the database record, here is how it would be done in each of the three methods:
-Raw SQL
+'''Raw SQL'''
 <syntaxhighlight lang="php">
 // Code to get the database ID
-$attributes=Yii::app()->db->createCommand('SELECT * FROM x2_contacts WHERE id='.$id)->queryRow;
+$attributes=Yii::app()->db->createCommand('SELECT * FROM x2_contacts WHERE id='.$id)->queryRow();
-// Code to pass $attributes into the form.
+// Code to pass $attributes into the form and load new attributes into $_POST['Contacts']
 $update='';
 foreach($_POST['Contacts'] as $attribute=>$value){
@@ Line 66: / Line 65: @@
+}
 if(!empty($update)){
-$update=substr($update,0,-2);
+    $update=substr($update,0,-2);
-Yii::app()->db->createCommand('UPDATE x2_contacts SET '.$updateQuery.' WHERE id='.$id);
+    Yii::app()->db->createCommand('UPDATE x2_contacts SET '.$updateQuery.' WHERE id='.$id);
+}
 </syntaxhighlight>
-CDbCommand
+'''CDbCommand'''
 <syntaxhighlight lang="php">
 </syntaxhighlight>

@@ Line 1: / Line 1: @@
 [[Category:Development]]
 = Introduction =
-{TODO: Finish CDbCommand, Models,How To's for all sections}
 X2CRM uses MySQL for its database architecture. All tables are prefixed with 'x2_' to differentiate them from any other tables in the database and allow for users to install X2CRM without having to create a database specifically for it. Nearly all tables have an 'id' column which serves as the primary key. Relational data is handled through the x2_relationships table which allows for the specification of model types and IDs rather than through join tables. Tables which are used to store records or other user data generally have associated Model classes to allow for easier data manipulation, while some of the more technical and system data focused tables often have no model record and must be accessed/modified in other ways. There are three ways for a developer to access data stored in one of the tables.

← Older revision		Revision as of 22:56, 8 August 2013
Line 38:		Line 38:
	->queryAll();		->queryAll();
	</syntaxhighlight>		</syntaxhighlight>
		+	= Models =