Vanilla

Vanilla 1 Documentation Archive

Trace: » sqlbuilder
 
 
 
vanilla:development:sqlbuilder
Table of Contents

Sqlbuilder

Sqlbuilder class (Framework.Class.Sqlbuilder.php) builds string of sql query to be executed. Vanilla by default only support query generation in MySQL. However, PostgreSQL generation is in the works. 

Class Sqlbuilder {
 
 Function SqlBuilder(&$Context)
 Function SetMainTable($TableName, $TableAlias = '', $CustomTablePrefix = '')
 Function AddCaseSelect($CaseTableAlias, $CaseField, $CaseFieldAlias, $CaseArray, $ElseValue = 'null')
 Function AddFieldNameValue($FieldName, $FieldValue = '', $QuoteValue = 1, $Function = '')
 Function AddGroupBy($Field, $TableAlias)
 Function AddJoin($NewTable, $NewTableAlias, $NewTableField, $ExistingAlias, $ExistingField, $JoinMethod, $AdditionalJoinMethods = '', $CustomTablePrefix = '') 
 Function AddLimit($Index, $Length)
 Function AddOrderBy($FieldName, $TableAlias, $SortDirection = 'asc', $Function = '', $InnerFunction = '', $InnerFunctionParams = '')
 Function AddSelect($Field, $TableAlias, $FieldAlias = '', $Function = '', $FunctionParameters = '', $GroupByThisField = '0', $FieldAddendum = '')
 Function AddWhere($TableAlias1, $Parameter1, $TableAlias2, $Parameter2, $ComparisonOperator, $AppendMethod = 'and', $Function = '', $QuoteParameter2 = '1', $StartWhereGroup = '0')
 Function Clear()
 Function EndWhereGroup()
 Function GetDelete()
 Function GetInsert($UseIgnore = "0")
 Function GetSelect($SelectPrefix = "")
 Function GetUpdate()
 Function GetWheres($ForUpdating = 0)
 Function GroupWheres()
 Function StartWhereGroup()
 Function WriteDebug($String)
}

Examples

SELECT Statement

Select one field from a particular row

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Discussion','Discussion');
$sql->AddSelect('DateExpire', 'Discussion','"Expiration Date"');
$sql->AddWhere('Discussion','DiscussionID','',3,'=');
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'An error occurred while fetching DateExpire field.');

This will generate the following MySQL query 

SELECT Discussion.DateExpire AS "Expiration Date"
FROM LUM_Discussion Discussion 
WHERE Discussion.DiscussionID = '3'

Select multiple fields from a particular row

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Comment','Comment');
$sql->AddSelect(array('Body', 'FormatType'), 'Comment');
$sql->AddWhere('Comment','DiscussionID','',3,'=');
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT Comment.Body AS Body, Comment.FormatType AS FormatType
FROM LUM_Comment Comment 
WHERE Comment.DiscussionID = '2'

Select multiple fields from two tables limited to 5 rows

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Comment','Comment');
$sql->AddSelect(array('CommentID','DateCreated','DiscussionID'),'Comment');
$sql->AddJoin('Discussion', 'Discussion', 'FirstCommentID','Comment','CommentID','LEFT JOIN');
$sql->AddSelect('Name','Discussion');
$sql->AddWhere('Discussion','CategoryID','',1,'!=');
$sql->AddWhere('Discussion','CategoryID','',2,'!=');
$sql->AddWhere('Discussion','FirstCommentID','Comment','CommentID','=', 'and', '', '0');
$sql->AddLimit(0, 5);
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT Comment.CommentID AS CommentID, 
       Comment.DateCreated AS DateCreated, 
       Comment.DiscussionID AS DiscussionID, 
       Discussion.Name AS Name 
FROM LUM_Comment Comment LEFT JOIN LUM_Discussion Discussion ON Comment.CommentID = Discussion.FirstCommentID 
WHERE Discussion.CategoryID != '1' and Discussion.CategoryID != '2' and Discussion.FirstCommentID = Comment.CommentID 
LIMIT 0, 5

Select multiple fields from two tables limited to 20 rows by descending order

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Comment','Comment');
$sql->AddSelect(array('CommentID','DateCreated','DiscussionID'),'Comment');
$sql->AddJoin('Discussion', 'Discussion', 'FirstCommentID','Comment','CommentID','LEFT JOIN');
$sql->AddSelect('Name','Discussion');
$sql->AddOrderBy ('DateCreated','Discussion', 'DESC' );
$sql->AddLimit(0, 20);
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT Comment.CommentID AS CommentID, 
       Comment.DateCreated AS DateCreated, 
       Comment.DiscussionID AS DiscussionID, 
       Discussion.Name AS Name 
FROM LUM_Comment Comment LEFT JOIN LUM_Discussion Discussion ON Comment.CommentID = Discussion.FirstCommentID 
ORDER BY  Discussion.DateCreated DESC
LIMIT 0,20;

Select multiple fields and group them

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Comment','Comment');
$sql->AddSelect(array('AuthUserID','CommentID'),'Comment');
$sql->AddGroupBy ('CommentID','Comment');
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT Comment.AuthUserID AS AuthUserID, Comment.CommentID AS CommentID
FROM LUM_Comment Comment
GROUP BY Comment.CommentID

INSERT Statement

Insert a row with some values, missing fields will get default values

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Discussion','d');
$sql->AddFieldNameValue('AuthUserID', 15, 0);
$sql->AddFieldNameValue('FirstCommentID', 15, 0);
$sql->AddFieldNameValue('Name', 'Test post');
$sql->AddFieldNameValue('DateCreated', '', 0, NOW);
$sql->AddFieldNameValue('CategoryID', 3, 0);
$result = $Context->Database->Insert($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

INSERT INTO LUM_Discussion (AuthUserID, FirstCommentID, Name, DateCreated, CategoryID)
VALUES (15, 15, 'Test post', NOW(), 3)

UPDATE Statement

Update one field from multiple rows

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Discussion','d');
$sql->AddFieldNameValue('Closed', 1, 1);
$sql->AddWhere('d','Closed','','"0"','=','and', '', 0, 1);
$sql->AddWhere('d','DateExpire','','0000-00-00','>','and', '', 0, 0);
$sql->AddWhere('d','DateExpire','','NOW()','<','and', '', 0, 0);
$sql->AddWhere('d','Active','','"1"','=','and', '', 0, 0);
$sql->EndWhereGroup();
$result = $Context->Database->Update($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

UPDATE LUM_Discussion
SET Closed="1"
WHERE (Closed="0" AND DateExpire > "0000-00-00" AND DateExpire < NOW() AND Active="1")

DELETE Statement

How to delete a row from the database

$sql->SetMainTable('Discussion', 'D');
$sql->AddWhere('D', 'DiscussionID', '', 3, '=');
$result = $Context->Database->Delete($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query. 

DELETE FROM LUM_Discussion 
WHERE DiscussionID = '3'

*warning* Delete function in vanilla doesn’t allow limit values, so this command will delete ALL rows that match the WHERE clause. If you want to just delete the latest record, use the EXECUTE method mentioned below.

DATE Functions

How to use date related functions

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Discussion', 'D');
$sql->AddSelect('DateCreated', 'D','"Day of the month"', 'DAYOFMONTH','');
$sql->AddSelect('DateCreated', 'D','"Year"', 'YEAR','');
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT DAYOFMONTH(D.DateCreated) AS "Day of the month", YEAR(D.DateCreated) AS "Year"
FROM LUM_Discussion D

How to pass parameters

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Discussion', 'D');
$sql->AddSelect('DateCreated', 'D','"Old Date"');
$sql->AddSelect('DateCreated', 'D','"Updated Date"', 'ADDDATE','INTERVAL "10:20" HOUR_MINUTE');
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT D.DateCreated AS "Old Date", ADDDATE(D.DateCreated, INTERVAL "10:20" HOUR_MINUTE) AS "Updated Date"
FROM LUM_Discussion D     

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Discussion', 'D');
$sql->AddSelect('DateCreated', 'D','"Descriptive date"', 'DATE_FORMAT','"%M %e, %Y"');
$sql->AddSelect('DateCreated', 'D','"Hours/Minutes"', 'TIME_FORMAT','"%H:%i"');
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT DATE_FORMAT(D.DateCreated, "%M %e, %Y")  AS "Descriptive date", TIME_FORMAT(D.DateCreated,'%H:%i') AS "Hours/Minutes" 
FROM LUM_Discussion D

CASE Functions

How to use CASE conditional statement

$sql = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
$sql->SetMainTable('Category','v');
$sql->AddSelect(array('Name','CategoryID'),'v');
$sql->AddCaseSelect('v', 'CategoryID','Policy', array(0 => array("WhenValue" =>1, "ThenValue" =>'"This is an adult oriented category. 18 only."'), 1=>array("WhenValue" =>2, "ThenValue" =>'"This is for females only category."')), '"OK to view."');
$sql->AddOrderBy ('Name','v', 'asc' );
$result = $Context->Database->Select($sql, 'SenderObject', 'SenderMethod' , 'Some error message.');

This will generate the following MySQL query 

SELECT v.Name AS Name, v.CategoryID AS CategoryID,
CASE v.CategoryID
 WHEN 1 THEN "This is an adult oriented category. 18 only." 
 WHEN 2 THEN "This is for females only category."
 ELSE "OK to view."
END AS Policy
FROM LUM_Category v
ORDER BY v.Name asc

Database EXECUTE method

How to use the execute method to run any sort of query

$Data = $Context->Database->Execute(
'SELECT 
  c.'.$ComFields['WhisperUserID'].' AS com_iswhisper, 
  d.'.$DiscFields['Closed'].' AS closed, 
  c.'.$ComFields['AuthUserID'].' AS user, 
  d.'.$DiscFields['DiscussionID'].' AS id, 
  d.'.$DiscFields['CountComments'].' AS com_num, 
  d.'.$DiscFields['FirstCommentID'].' AS first_com, 
  c.'.$ComFields['Deleted'].' AS deleted 
FROM '.$DiscTbl.' AS d 
JOIN '.$ComTbl.' AS c on d.'.$DiscFields['DiscussionID'].' = c.'.$ComFields['DiscussionID'].' 
WHERE c.'.$ComFields['CommentID'].' = '.$CommentID.' 
LIMIT 1;', 
'', '', 'An error occured while attempting to retrieve the comment status');
		
$Context->Database->GetRow($Data);
 
 
 
 
vanilla/development/sqlbuilder.txt · Last modified: 2009/03/28 14:19
Vanilla 1 Documentation archive (Wiki by DokuWiki)