/**
* UpdateDatabaseState
* Add code to perform actual database updgrad operation
*
* @param $dbhandle handle to database
* @return $status true if operation was successful
*/
function UpdateDatabaseState($dbhandle, $state)
{
$logger = LoggerSingleton::GetInstance();
$logger->LogInfo("UpdateDatabaseState - Enter ({$state})");
$logger->LogInfo("UpdateDatabaseState - Get last row of version table");
$status = SelectLastRowFrom_Version($dbhandle, $id, $MajorVersion, $MinorVersion, $dbState);
if (false == $status) {
$logger->LogError("UpdateDatabaseState - Unable to get last row");
}
if (true == $status) {
$logger->LogInfo("UpdateDatabaseState - Update last row with the db state");
$status = UpdateInto_Version($dbhandle, $id, $MajorVersion, $MinorVersion, $state);
if (false == $status) {
$logger->LogError("UpdateDatabaseState - Unable to change to new state {$state}");
}
}
$logger->LogInfo("UpdateDatabaseState status - {$status}");
return $status;
}
/**
* Upgrades for MajorVersion = 0, MinorVersion = 0
*
* Add code to perform actual database updgrad operation
* For first version, create supporting tables
*
* @param $dbhandle handle to database
* @return $status true if operation was successful
*/
function V_Major0_Minor0_Upgrade($dbhandle)
{
$logger = LoggerSingleton::GetInstance();
$logger->LogInfo("V0_0_Upgrade - Enter ");
$status = true;
/* create password table if not persent */
// check if existing
if (true == $status) {
$status = IsExisting_Password($dbhandle);
$logger->LogInfo("IsExisting Password table status :" . var_export($status, true));
}
// Create table
if (false == $status) {
$status = CreateTable_Password($dbhandle);
$logger->LogInfo("Create Password table status : " . var_export($status, true));
}
/* Create UserDetails table if not present */
if (true == $status) {
$status = IsExisting_UserDetails($dbhandle);
$logger->LogInfo("IsExisting UserDetails table status :" . var_export($status, true));
}
// Create table
if (false == $status) {
$status = CreateTable_UserDetails($dbhandle);
$logger->LogInfo("Create UserDetails table status : " . var_export($status, true));
}
/* Create TodoList table if not present already */
if (true == $status) {
$status = IsExisting_TodoList($dbhandle);
$logger->LogInfo("IsExisting TodoList table status :" . var_export($status, true));
}
// Create table
if (false == $status) {
$status = CreateTable_TodoList($dbhandle);
$logger->LogInfo("Create TodoList table status : " . var_export($status, true));
}
$logger->LogInfo("Exit form V_Major0_Minor0_Upgrade with status :" . var_export($status, true));
return $status;
}
/**
* Delete row into tables
*
* @param $id provide id of the record to delete
* @return $status true if the operation is successful
*/
function DeleteFrom_UserDetails($dbhandle, $id)
{
$logger = LoggerSingleton::GetInstance();
$logger->LogInfo("DeleteFrom_UserDetails : Enter");
global $UserDetailsTable_Id;
global $UserDetailsTable_Name;
// delete
$query = "DELETE FROM {$UserDetailsTable_Name} WHERE {$UserDetailsTable_Id} = {$id}";
// execte
$status = $dbhandle->query($query);
if (false == $status) {
$logger->LogError("DeleteFrom_UserDetails : Could not delete record - {$dbhandle->error}");
}
return $status;
}
<!-- Drop Table -->
<?php
// includes
require_once 'common_require.php';
require_once 'VersionTable.php';
require_once 'UserDetailsTable.php';
require_once 'PasswordTable.php';
require_once 'TodoListTable.php';
$logger = LoggerSingleton::GetInstance();
$logger->LogError("*************************************************************");
$logger->LogError("*\n* TODO LIST DROP \n*\n");
$logger->LogError("*************************************************************");
/*
if ($argc < 4)
{
die("Invalid Syntax. Try : $argv[0] <database name> <username> <password>\n");
}
*/
// get database name
$dbhostname = 'localhost';
$dbname = 'tododb';
$username = '******';
$password = '******';
$status = true;
$logger->LogInfo("Main - Open connection to db");
$dbhandle = new mysqli($dbhostname, $username, $password, $dbname);
if ($dbhandle->connect_error == true) {
$logger->LogError("Unable to open connection to {$dbname} : {$dbhandle->error}");
$status = false;
}
/**
* Select row into tables
*
* @param table variables
* @param $userid provide id of the record to retrieve
* @param are arrays, to return values by reference // not a good idea
* @return $status true if the operation is successful
*/
function SelectByUserIdFrom_TodoList($dbhandle, &$id, $userId, &$priority, &$task_status, &$task, &$createdDate, &$estimatedDate, &$completedDate, &$remainderDate)
{
$logger = LoggerSingleton::GetInstance();
$logger->LogInfo("SelectByIdFrom_TodoList : Enter ({$id})");
global $TodoListTable_Name;
global $TodoListTable_Id;
global $TodoListTable_UserId;
global $TodoListTable_Priority;
global $TodoListTable_Status;
global $TodoListTable_CreatedDate;
global $TodoListTable_EstimatedDate;
global $TodoListTable_CompletedDate;
global $TodoListTable_Remainder;
global $TodoListTable_TaskDescription;
$status = true;
// select
$query = "SELECT * FROM {$TodoListTable_Name}\n WHERE {$TodoListTable_Id} = {$id}";
// execte
$result = $dbhandle->query($query);
if (FALSE == $result) {
$logger->LogError("SelectByIdFrom_TodoList : No records with id - {$id} found");
$status = false;
}
$num_rows = $result->num_rows;
if ($num_rows > 0) {
while ($row = $result->fetch_assoc()) {
array_push($id, $row["{$TodoListTable_Id}"]);
array_push($userId, $row["{$TodoListTable_UserId}"]);
array_push($priority, $row["{$TodoListTable_Priority}"]);
array_push($task_status, $row["{$TodoListTable_Status}"]);
array_push($task, $row["{$TodoListTable_TaskDescription}"]);
array_push($createdDate, $row["{$TodoListTable_CreatedDate}"]);
array_push($estimatedDate, $row["{$TodoListTable_EstimatedDate}"]);
array_push($completedDate, $row["{$TodoListTable_CompletedDate}"]);
array_push($remainderDate, $row["{$TodoListTable_Remainder}"]);
}
$status = true;
} else {
$logger->LogError("SelectByIdFrom_TodoList : No records found");
$status = false;
}
return $status;
}
/**
* Delete row into tables
*
* @param $id provide id of the record to delete
* @return $status true if the operation is successful
*/
function DeleteFrom_Version($dbhandle, $id)
{
$logger = LoggerSingleton::GetInstance();
$logger->LogInfo("DeleteFrom_Version : Enter");
global $VersionTable_Id;
global $VersionTable_Name;
global $VersionTable_MajorVersion;
global $VersionTable_MinorVersion;
global $VersionTable_DbState;
// select
$query = "DELETE FROM {$VersionTable_Name} WHERE {$VersionTable_Id} = {$id}";
// execte
$status = $dbhandle->query($query);
if (false == $status) {
$logger->LogError("DeleteFrom_Version : Could not delete record - {$dbhandle->error}");
}
return $status;
}
