<?php
namespace Jackbooted\DB;
/**
* @copyright Confidential and copyright (c) 2016 Jackbooted Software. All rights reserved.
*
* Written by Brett Dutton of Jackbooted Software
* brett at brettdutton dot com
*
* This software is written and distributed under the GNU General Public
* License which means that its source code is freely-distributed and
* available to the general public.
*/
class DBMaintenance extends \Jackbooted\Util\JB {
public static function dbNextNumber ( $dbh, $tName ) {
// Loop max of 10 times to get the correc next number (optimistic locking)
for ( $i=0; $i<10; $i++ ) {
$qry = 'SELECT fldNext,fldFormat FROM tblNextNumber WHERE fldTable=?';
$row = DB::oneRow ( $dbh, $qry, $tName, DB::FETCH_ASSOC );
if ( $row === false ) {
self::addTableToNextNumber ( $tName, 'XX000000', 'Auto Inserted by dbNextNumber' );
$row = DB::oneRow ( $dbh, $qry, $tName, DB::FETCH_ASSOC );
if ( $row === false ) return false;
}
$nextNumberS = $row['fldNext'];
$nextNumber = intval ( $nextNumberS );
$format = $row['fldFormat'];
$updatedNumber = $nextNumber + 1;
$qry = 'UPDATE tblNextNumber SET fldNext=? WHERE fldTable=? AND fldNext=?';
$rowsAffected = DB::exec ( $dbh, $qry, [ $updatedNumber, $tName, $nextNumberS ] );
if ( $rowsAffected == 1 ) break;
}
// Apply the format to the number. This will be stored in the database
return substr ( $format, 0, strlen ( $format ) - strlen ( $nextNumberS ) ). $nextNumberS;
}
public static function getTableList() {
$qry = ( DB::driver( ) == DB::SQLITE ) ? "SELECT name FROM sqlite_master WHERE type='table'" : 'SHOW TABLES';
return DBTable::factory ( DB::DEF, $qry, null, DB::FETCH_NUM )->getColumn ( 0 );
}
public static function addTableToNextNumber ( $tName, $fmt, $comment="" ) {
// See if the table has been created
$tableExists = DB::oneValue ( DB::DEF, 'SELECT count(fldNextNumberID) FROM tblNextNumber WHERE fldTable=?', $tName );
if ( $tableExists != 0 ) return false;
$key = self::dbNextNumber ( DB::DEF, 'tblNextNumber' );
$lines = DB::exec ( DB::DEF, "INSERT INTO tblNextNumber VALUES (?,?,1,?,?,? )",
[ $key, $tName, $fmt, $comment, self::getTableChecksum ( $tName ) ] );
return ( $lines > 0 );
}
public static function updateTableChecksum ( $tableName ) {
$checksum = self::getTableChecksum ( $tableName );
DB::exec ( DB::DEF, 'UPDATE tblNextNumber SET fldTableChecksum=? WHERE fldTable=?',
[ $checksum, $tableName ] );
}
public static function getTableChecksum ( $tableName ) {
$createSyntax = self::getTableSyntax ( $tableName );
return md5 ( $createSyntax );
}
public static function getTableComments ( $tableName ) {
$attributes = [];
$createSyntax = self::getTableSyntax ( $tableName );
$p = strpos ( $createSyntax, "COMMENT=" );
if ( $p ) {
$comments = substr ( $createSyntax, $p + 9 );
$comments = substr ( $comments, 0, strlen ( $comments ) - 1 );
foreach ( explode ( ';', $comments ) as $attrib ) {
list ( $key, $val ) = explode ( '=', $attrib );
$attributes[$key] = $val;
}
}
return $attributes;
}
public static function addTableComments ( $tableName, $attributes ) {
$allComments = array_merge ( self::getTableComments ( $tableName ), $attributes );
self::setTableComments ( $allComments );
}
public static function setTableComments ( $tableName, $attributes ) {
$tempArray = [];
foreach ( $attributes as $key => $val ) $tempArray[] = $key . '=' . $val;
$comments = join ( ';', $tempArray );
$sql = "ALTER TABLE {$tableName} COMMENT ?";
DB::exec ( DB::DEF, $sql , $comments );
}
private static function getTableSyntax ( $tableName ) {
if ( DB::driver() == DB::SQLITE ) {
return DB::oneValue( DB::DEF, "SELECT sql FROM sqlite_master where type='table' and tbl_name='$tableName'" );
}
else {
$createTable = new DBTable ( DB::DEF, 'SHOW CREATE TABLE ' . $tableName );
return $createTable->getValue ( 1, 0 );
}
}
}
|
Download
