Super Database Access/Service


/ Published in: PHP
Save to your folder(s)



Copy this code and paste it in your HTML
  1. <?php
  2.  
  3.  
  4. define('DB_HOST', 'localhost');
  5. define('DB_USER', 'root');
  6. define('DB_PASS', 'fred');
  7.  
  8. class SuperDatabaseAccess {
  9.  
  10. /**
  11.   * @var
  12.   */
  13. public $apiDatabase;
  14.  
  15. /**
  16.   * @var
  17.   */
  18. public $apiMaxCount = 200;
  19.  
  20. /**
  21.   * @var
  22.   */
  23. public $apiUseKey = false;
  24.  
  25. /**
  26.   * @var
  27.   */
  28. public $apiUseThrottle;
  29.  
  30. /**
  31.   * @var
  32.   */
  33. public $apiThrottleMax;
  34.  
  35. /**
  36.   * @var
  37.   */
  38. public $apiResultFormat;
  39.  
  40. /**
  41.   * @var
  42.   */
  43. public $svcDatabase;
  44.  
  45. /**
  46.   * @var
  47.   */
  48. public $svcTable;
  49.  
  50. /**
  51.   * @var
  52.   */
  53. public $svcType;
  54.  
  55. /**
  56.   * @var
  57.   */
  58. private $dsn = '';
  59.  
  60. /**
  61.   * @var
  62.   */
  63. private $dbh = null;
  64.  
  65. private $callResultsPerPage = 0;
  66. private $callResultsCurrentPage = 0;
  67. private $callResultsOffset = 0;
  68. private $callResultsTotalPages = 0;
  69. private $callResultsTotal = 0;
  70. /**
  71.   *
  72.   * @param object $dbType [optional]
  73.   * @param object $dbHost [optional]
  74.   * @param object $dbPort [optional]
  75.   * @param object $dbUser [optional]
  76.   * @param object $dbPass [optional]
  77.   * @return
  78.   */
  79. public function __construct($dbType = 'mysql', $dbHost = 'localhost', $dbPort = 3306, $dbUser = null, $dbPass = null) {
  80.  
  81. if ($dbType == 'mysql') {
  82. $this->svcType = 'mysql';
  83. $this->dsn = 'mysql:host='.$dbHost.'';
  84. $this->dsn .= $dbPort != null ? ':'.$dbPort.';' : ';';
  85.  
  86. } else if ($dbType == 'sqlite') {
  87.  
  88. $this->svcType = 'sqlite';
  89. $this->dsn = 'sqlite2:'.$dbName.'.db';
  90. }
  91.  
  92. try {
  93. $this->dbh = new PDO($this->dsn, $dbUser, $dbPass);
  94. $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  95. }
  96. catch(PDOException $e) {
  97. echo 'Connection failed: '.$e->getMessage();
  98. die();
  99. }
  100.  
  101. }
  102.  
  103. /**
  104.   *
  105.   * @param string $mode
  106.   * @param string $database
  107.   * @param string $table [optional]
  108.   * @param string $sql [optional]
  109.   * @param array $vo [optional]
  110.   * @return
  111.   */
  112. public function execute($mode, $database, $table = null, $sql = null, $vo = null) {
  113.  
  114. try {
  115.  
  116. $result = array();
  117. $return = null;
  118.  
  119. //Function management variables
  120. $voColumns = '';
  121. $voBindColumns = '';
  122. $voKeyValue = '';
  123. $voValues = array();
  124. $voPrimaryKey = null;
  125.  
  126. /**
  127.   * Check if we are returning data
  128.   * @var
  129.   */
  130. if ($mode == 'select' || $mode == 'search') {
  131. $statement = $this->dbh->prepare($sql);
  132. $statement->execute();
  133. $records = array();
  134. while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  135. $records[] = $row;
  136. }
  137. $result = array(
  138. 'status'=>'success',
  139. 'database'=>$database,
  140. 'table'=>$table,
  141. 'count'=>$statement->rowCount(),
  142. 'page'=>$this->callResultsCurrentPage,
  143. 'pages' => $this->callResultsTotalPages,
  144. 'total' => $this->callResultsTotal,
  145. 'data'=>$records);
  146. $return[] = $result;
  147.  
  148. //Check if its an insert
  149. } else if ($mode == 'insert') {
  150.  
  151. //Make sure there is assoc array for vo
  152. if ($vo != null) {
  153.  
  154. //Loop each key=value for the vo
  155. foreach ($vo as $column=>$value) {
  156. $voColumns .= $column.', ';
  157.  
  158. //build the bind params
  159. $voBindColumns .= '?, ';
  160.  
  161. //push the values to a value array
  162. array_push($voValues, $value);
  163. }
  164.  
  165. //Trim the strings
  166. $voColumns = $this->trimSQL($voColumns);
  167. $voBindColumns = $this->trimSQL($voBindColumns);
  168.  
  169. //Build the sql
  170. $sql = "INSERT INTO $database.$table ( $voColumns ) VALUES ( $voBindColumns )";
  171.  
  172. //log it
  173. # $this->writeLog('SQL', $sql);
  174.  
  175. //Prepare database with the ? placeholders
  176. $statement = $this->dbh->prepare($sql);
  177.  
  178. //If the query executed properly
  179. if ($statement->execute($voValues)) {
  180.  
  181. //Prep the result to return
  182. $result = array('status'=>'success', 'database'=>$database, 'table'=>$table, 'count' => 1, 'data'=>array('insertid'=>$this->dbh->lastInsertId()));
  183. $return[] = $result;
  184. }
  185. }
  186.  
  187. /**
  188.   * Update
  189.   * @var
  190.   */
  191. } else if ($mode == 'update') {
  192. if ($vo != null) {
  193.  
  194. $voPrimaryKey = $this->getKey($database, $table);
  195.  
  196. if (array_key_exists($voPrimaryKey, $vo)) {
  197. $voKeyValue = $vo[$voPrimaryKey];
  198. }
  199. foreach ($vo as $column=>$value) {
  200. $voBindColumns .= $column.'= ?, ';
  201. array_push($voValues, $value);
  202. }
  203. array_push($voValues, $voKeyValue);
  204.  
  205. $voBindColumns = $this->trimSQL($voBindColumns);
  206.  
  207. $sql = "UPDATE $database.$table SET $voBindColumns WHERE $voPrimaryKey = ?";
  208.  
  209.  
  210. $statement = $this->dbh->prepare($sql);
  211.  
  212. if ($statement->execute($voValues)) {
  213. $result = array('status'=>'success', 'database'=>$database, 'table'=>$table, 'count' => 1, 'data'=>array('updateid'=>$voKeyValue));
  214. $return[] = $result;
  215. }
  216. }
  217. /**
  218.   * delete
  219.   * @var
  220.   */
  221. } else if ($mode == 'delete') {
  222. if ($vo != null) {
  223. $voPrimaryKey = $this->getKey($database, $table);
  224. $voKeyValue = '';
  225. if (array_key_exists($voPrimaryKey, $vo)) {
  226. $voKeyValue = $vo[$voPrimaryKey];
  227. }
  228.  
  229. $sql = "DELETE FROM $database.$table WHERE $voPrimaryKey = ?";
  230.  
  231. # $this->writeLog('SQL', $sql);
  232. $statement = $this->dbh->prepare($sql);
  233.  
  234. if ($statement->execute(array($voKeyValue))) {
  235. $result = array('status'=>'success', 'database'=>$database, 'table'=>$table, 'count' => 1,'data'=>array('deleteid'=>$voKeyValue));
  236. $return[] = $result;
  237. }
  238. }
  239.  
  240. }
  241.  
  242. }
  243. catch(PDOException $e) {
  244. $result = array('status'=>'error', 'message'=>$e->getMessage());
  245. $return[] = $result;
  246. }
  247.  
  248.  
  249. return $return;
  250. }
  251.  
  252. /**
  253.   * I make a select statement and return the results.
  254.   *
  255.   * @param string $database
  256.   * @param string $table
  257.   * @param string $columns [optional]
  258.   * @param number $count [optional]
  259.   * @param number $page [optional]
  260.   * @param string $sort [optional]
  261.   * @return
  262.   */
  263. public function select($database, $table, $columns = "*", $count = 25, $page = 0, $sort = null) {
  264.  
  265. $this->callResultsTotal = $this->count($database, $table);
  266. $this->callResultsCurrentPage = $page;
  267. $this->callResultsPerPage = $count;
  268. $this->callResultsTotalPages = ceil($this->callResultsTotal / $this->callResultsPerPage);
  269. if ($page > 0){
  270. $this->callResultsOffset = ($this->callResultsPerPage * $this->callResultsCurrentPage - $this->callResultsPerPage );
  271.  
  272. } else if ($page == 1){
  273. $this->callResultsOffset = 0;
  274. }
  275.  
  276.  
  277. $sorting = ($sort != null) ? "ORDER BY $sort" : "";
  278. $count = ($count >= $this->apiMaxCount) ? $this->apiMaxCount : $count;
  279. $counting = 'LIMIT '.$this->callResultsOffset.', '. $this->callResultsPerPage.';';
  280. $sql = "SELECT $columns FROM $database.$table $sorting $counting";
  281.  
  282. return $this->execute('select', $database, $table, $sql);
  283. }
  284.  
  285. /**
  286.   *
  287.   * @param object $database
  288.   * @param object $table
  289.   * @param object $vo
  290.   * @return
  291.   */
  292. public function selectOne($database, $table, $keyvalue) {
  293. $primarykey = $this->getKey($database, $table);
  294. #$primarykeyValue = $vo[$primarykey];
  295. $sql = "SELECT * FROM $database.$table WHERE $primarykey = $keyvalue LIMIT 1";
  296.  
  297. $this->writeLog('SQL-selectOne', $sql);
  298.  
  299. return $this->executeAndReturn($sql);
  300. }
  301.  
  302. /**
  303.   *
  304.   * @param object $database
  305.   * @param object $table
  306.   * @param object $columns [optional]
  307.   * @param object $where [optional]
  308.   * @param object $query [optional]
  309.   * @param object $count [optional]
  310.   * @param object $page [optional]
  311.   * @param object $sort [optional]
  312.   * @return
  313.   */
  314. public function search($database, $table, $columns = "*", $where = null, $query = null, $count = 25, $page = 0, $sort = null) {
  315. $this->callResultsTotal = $this->count($database, $table);
  316. $this->callResultsCurrentPage = $page;
  317. $this->callResultsPerPage = $count;
  318. $this->callResultsTotalPages = ceil($this->callResultsTotal / $this->callResultsPerPage);
  319. if ($page > 1){
  320. $this->callResultsOffset = ($this->callResultsPerPage * $this->callResultsCurrentPage - $this->callResultsPerPage );
  321.  
  322. } else if ($page == 1){
  323. $this->callResultsOffset = 0;
  324. }
  325.  
  326.  
  327. $sorting = ($sort != null) ? "ORDER BY $sort" : "";
  328. $counting = 'LIMIT '.$this->callResultsOffset.', '. $this->callResultsPerPage.';';
  329. $sql = "SELECT $columns FROM $database.$table WHERE $where LIKE '%$query%' $sorting $counting";
  330.  
  331. $this->writeLog('SQL-search', $sql);
  332.  
  333. return $this->execute('search', $database, $table, $sql);
  334. }
  335.  
  336. public function count($database, $table){
  337. $sql = "SELECT COUNT(*) FROM $database.$table";
  338. $result = $this->executeAndReturn($sql);
  339.  
  340. $this->writeLog('SQL-count', $sql);
  341.  
  342. return $result[0]['COUNT(*)'];
  343. }
  344.  
  345. /**
  346.   *
  347.   * @param object $database
  348.   * @param object $table
  349.   * @param object $vo
  350.   * @return
  351.   */
  352. public function save($database, $table, $vo) {
  353. $primarykey = $this->getKey($database, $table);
  354. $primarykeyValue = $vo[$primarykey];
  355.  
  356. $choice = '';
  357. if ($primarykeyValue == 0 || $primarykeyValue == '') {
  358. $choice = $this->create($database, $table, $vo);
  359. } else {
  360. $choice = $this->update($database, $table, $vo);
  361. }
  362. return $choice;
  363. }
  364.  
  365. /**
  366.   *
  367.   * @param object $database
  368.   * @param object $table
  369.   * @param object $vo
  370.   * @return
  371.   */
  372. public function update($database, $table, $vo) {
  373. return $this->execute('update', $database, $table, null, $vo);
  374. }
  375.  
  376. /**
  377.   *
  378.   * @param object $database
  379.   * @param object $table
  380.   * @param object $vo
  381.   * @return
  382.   */
  383. public function create($database, $table, $vo) {
  384. return $this->execute('insert', $database, $table, null, $vo);
  385. }
  386.  
  387. /**
  388.   *
  389.   * @param object $database
  390.   * @param object $table
  391.   * @param object $vo
  392.   * @return
  393.   */
  394. public function remove($database, $table, $vo) {
  395. return $this->execute('delete', $database, $table, null, $vo);
  396. }
  397.  
  398. /**
  399.   *
  400.   * @param object $database
  401.   * @param object $table
  402.   * @return
  403.   */
  404. public function getKey($database, $table) {
  405.  
  406. if ($this->svcType == 'mysql') {
  407.  
  408. $sql = "SHOW INDEX FROM $database.$table";
  409. $keys = $this->executeAndReturn($sql);
  410. $primaryKey = '';
  411. if ($keys) {
  412. //TODO: Find a alt to tables not having a key
  413. foreach ($keys as $key) {
  414. if ($key['Key_name'] == 'PRIMARY') {
  415. $primaryKey = $key['Column_name'];
  416. }
  417. }
  418. $this->writeLog('SQL-getKey', $sql);
  419.  
  420. return $primaryKey;
  421. }
  422. }
  423. }
  424. /**
  425.   * I get the databases in the mysql server
  426.   *
  427.   * @return [array] - Tree ready array of database, tables, and fields
  428.   */
  429. public function getDatabases() {
  430. if ($this->svcType == 'mysql') {
  431.  
  432. $sql = "SHOW DATABASES";
  433.  
  434. $databases = array();
  435.  
  436. $statement = $this->dbh->prepare($sql);
  437. $statement->execute();
  438. while ($database = $statement->fetch(PDO::FETCH_ASSOC)) {
  439.  
  440.  
  441. $tables = array();
  442.  
  443. foreach ($database as $data) {
  444. $tables = $this->getTables($data);
  445. }
  446.  
  447. $databases[] = array("label"=>$data, "type"=>"database", "children"=>$tables);
  448.  
  449. }
  450.  
  451. $this->writeLog('SQL-getDatabases', $sql);
  452.  
  453. return $databases;
  454. }
  455. }
  456. /**
  457.   * I get the tables, fields, and information about the tables from the database.
  458.   *
  459.   * @param [string] $database
  460.   * @return array
  461.   */
  462. public function getTables($whatDatabase) {
  463. if ($this->svcType == 'mysql') {
  464.  
  465. $tables = array();
  466. $sql = "SHOW TABLES FROM $whatDatabase";
  467. $statement = $this->dbh->prepare($sql);
  468. $statement->execute();
  469. while ($table = $statement->fetch(PDO::FETCH_ASSOC)) {
  470.  
  471. $fields = array();
  472. $indexes = array();
  473.  
  474. foreach ($table as $t_key=>$t_value) {
  475. $fields = $this->describeTable($whatDatabase, $t_value);
  476. $primaryKey = $this->getKey($whatDatabase, $t_value);
  477. }
  478.  
  479. $tables[] = array("label"=>$t_value, "type"=>'table', "key"=>$primaryKey, "children"=>$fields);
  480.  
  481. }
  482. sort($tables);
  483.  
  484. $this->writeLog('SQL-getDatabases', $sql);
  485.  
  486. return $tables;
  487. }
  488. }
  489.  
  490. /**
  491.   * I describe a table for the getDatabasesAndTables() method
  492.   *
  493.   * @param [string] $database the database
  494.   * @param [string] $table the table
  495.   * @return [array]
  496.   */
  497. public function describeTable($whatDatabase, $whatTable) {
  498. $sql = "DESCRIBE $whatDatabase.$whatTable";
  499. $statement = $this->dbh->prepare($sql);
  500. $statement->execute();
  501.  
  502. $tables = array();
  503. $pattern = '/(\(\d*\))/';
  504.  
  505. while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  506.  
  507. $field = $row['Field']; //Field Name
  508. $type = $row['Type']; //Field Type
  509.  
  510. preg_match($pattern, $type, $matches);
  511.  
  512. #$typeOpts = $matches[0];
  513. #$type = str_replace( $type, $matches[0] );
  514.  
  515. //TODO: Fix these values
  516. $default = $row['Default']; //Field Default Value
  517. $nullable = ($row['Null'] == 'YES') ? 'true' : 'false';
  518. $tables[] = array('label'=>$field, 'type'=>'field', 'fieldDefault'=>$default, 'fieldType'=>$type, 'fieldLength'=>'', 'fieldNullable'=>$nullable);
  519. }
  520.  
  521. $this->writeLog('SQL-getDatabases', $sql);
  522.  
  523. return $tables;
  524. }
  525.  
  526.  
  527. /**
  528.   *
  529.   * @param object $sql
  530.   * @return
  531.   */
  532. private function executeAndReturn($sql) {
  533. try {
  534.  
  535.  
  536. $statement = $this->dbh->prepare($sql);
  537. $statement->execute();
  538. $result = array();
  539. $records = array();
  540. while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  541. $records[] = $row;
  542. }
  543. $result = $records;
  544. if (count($result) <= 0) {
  545. $result = false;
  546. }
  547. }
  548. catch(PDOException $e) {
  549. $result = false;
  550. }
  551.  
  552. return $result;
  553. }
  554.  
  555. /**
  556.   *
  557.   * @param object $obj
  558.   * @return
  559.   */
  560. private function mapObject($obj) {
  561. require_once "ValueObject.php";
  562. $array = array();
  563.  
  564. while ($row = mysqli_fetch_assoc($obj)) {
  565. $vo = new ValueObject();
  566. foreach ($row as $key=>$value) {
  567. $vo->__set($key, $value);
  568. }
  569. $array[] = $vo;
  570. }
  571. return $array;
  572. }
  573.  
  574. /**
  575.   *
  576.   * @param object $value
  577.   * @return
  578.   */
  579. private function escape($value) {
  580. $escaped = $value;
  581.  
  582. if (!is_numeric($value)) {
  583. $escaped = $this->dbh->quote(trim($value));
  584. }
  585. return $escaped;
  586. }
  587.  
  588. /**
  589.   *
  590.   * @param object $sql
  591.   * @return
  592.   */
  593. private function trimSQL($sql) {
  594. return substr($sql, 0, strlen($sql) - 2);
  595. }
  596.  
  597. /**
  598.   *
  599.   * @param object $type
  600.   * @param object $var
  601.   * @return
  602.   */
  603. public function writeLog($type, $var) {
  604. $file = "/tmp/SuperDatabaseAccess.log";
  605. $fp = fopen($file, "a+");
  606. $date = date('[D M j Y H:i:s] ', mktime());
  607. $contents = "\n".$date.'['.$type.'] '.$var;
  608. fwrite($fp, $contents);
  609. fclose($fp);
  610. }
  611.  
  612.  
  613. /**
  614.   *
  615.   * @param object $prefix
  616.   * @return
  617.   */
  618. public function generateKey($prefix) {
  619. $hash = uniqid($prefix);
  620.  
  621. return $hash;
  622. }
  623.  
  624. /**
  625.   *
  626.   * @param object $key
  627.   * @return
  628.   */
  629. public function checkKey($key) {
  630. $return = false;
  631. $sql = "SELECT * FROM ".$this->apiDatabase.".keys WHERE apikey = '$key'";
  632. $result = $this->executeAndReturn($sql);
  633. if ($result) {
  634. $return = true;
  635. }
  636. return $return;
  637. }
  638.  
  639. /**
  640.   *
  641.   * @param object $prefix
  642.   * @param object $userid
  643.   * @return
  644.   */
  645. public function updateKey($prefix, $userid) {
  646. $return = false;
  647. $key = $this->generateKey($prefix);
  648. $sql = "UPDATE ".$this->apiDatabase.".keys SET apikey = '$key' WHERE user_id = $userid";
  649. $result = $this->executeAndReturn($sql);
  650. if ($result) {
  651. $return = true;
  652. }
  653. return $return;
  654. }
  655.  
  656. }
  657.  
  658. /* *************************
  659.  * Testing
  660.  * ************************
  661.  echo '<pre>';
  662.  $svc = new DatabaseAccess('mysql', 'localhost', null, 'root', 'fred');
  663.  $svc->apiDatabase = 'restapi';
  664.  $svc->apiMaxCount = 50;
  665.  $svc->apiUseKey = false;
  666.  $svc->apiResultFormat = 'json';
  667.  $prefix = 'api-';
  668.  $email = '[email protected]';
  669.  $newkey = $svc->generateKey($prefix);
  670.  $oldkey = 'api-4ab5f0b7091f5';
  671.  $validKey = $svc->checkKey($oldkey);
  672.  $updateKey = $svc->updateKey($prefix, '2');
  673.  echo '<h3>Insert Record</h3>';
  674.  $newrecord = array('id'=>0, 'name'=>'New Work Schedule', 'color'=>'Green', 'user_id'=>7);
  675.  $create = $svc->create('test', 'calendars', $newrecord);
  676.  print_r($create);
  677.  echo "<br/>";
  678.  echo '<h3>Update Record</h3>';
  679.  $oldrecord = array('id'=>2, 'name'=>'Old Work Schedule', 'color'=>'Blue', 'user_id'=>3);
  680.  $update = $svc->update('test', 'calendars', $oldrecord);
  681.  print_r($update);
  682.  echo "<br/>";
  683.  echo '<h3>Delete Record</h3>';
  684.  $removeKey = ($create['response']['insertid'] - 1);
  685.  $delete = $svc->remove('test', 'calendars', array('id'=>$removeKey));
  686.  print_r($delete);
  687.  echo "<br/>";
  688.  echo '<h3>Get Records</h3>';
  689.  $getrecord = $svc->select('test', 'posts', '*', null, 2);
  690.  print_r($getrecord);
  691.  echo "<br/>";
  692.  echo '<h3>Search Records</h3>';
  693.  $searchrecord = $svc->search('test', 'addresses', '*', 'state', 'ca', 15, 0, 'id asc');
  694.  print_r($searchrecord);
  695.  echo "<br/>";
  696.  echo '<h3>Old API Key</h3>';
  697.  echo $oldkey;
  698.  echo "<br/>";
  699.  echo '<h3>New API Key</h3>';
  700.  echo $newkey;
  701.  echo "<br/>";
  702.  echo '<h3>Update API Key</h3>';
  703.  var_dump($updateKey);
  704.  echo "<br/>";
  705.  echo '<h3>Valid Key Check</h3>';
  706.  var_dump($validKey);
  707.  echo "<br/>";
  708.  echo '</pre>';
  709.  */
  710.  
  711. /**
  712.  * @author
  713.  * @version
  714.  * @license
  715.  * @classDescription - This class can be used many ways, as a rpc for Flex/Flash application using either http or http remoting
  716.  * Here is examples on testing this class to make sure its working.
  717.  *
  718.  * HTTP:
  719.  * url: http://rest/SuperDatabaseService.php?action=select&database=test&table=users&format=xml
  720.  *
  721.  * Output Formats:
  722.  * Here is the supported output formats.
  723.  * 1. json - [{"status":"success","database":"test","table":"users","count":1,"page":1,"pages":5000,"total":"5000","data":[{"id":"1","username":"[email protected]","created":"2010-01-11 00:43:58"}]}]
  724.  * 2. php - a:1:{i:0;a:8:{s:6:"status";s:7:"success";s:8:"database";s:4:"test";s:5:"table";s:5:"users";s:5:"count";i:1;s:4:"page";i:1;s:5:"pages";d:5000;s:5:"total";s:4:"5000";s:4:"data";a:1:{i:0;a:3:{s:2:"id";s:1:"1";s:8:"username";s:27:"[email protected]";s:7:"created";s:19:"2010-01-11 00:43:58";}}}}
  725.  * 3. xml -
  726. <code>
  727.  
  728. <?xml version="1.0"?>
  729. <results
  730. status="success"
  731. database="test"
  732. table="users"
  733. pages="5000"
  734. page="1"
  735. total="5000"
  736. count="1">
  737. <row number="1">
  738. <id>1</id>
  739. <username>[email protected]</username>
  740. <created>2010-01-11 00:43:58</created>
  741. </row>
  742. </results>
  743.  
  744. </code>
  745.  * 4. byte array
  746.  *
  747.  *
  748.  * @projectDescription
  749.  * @copyright
  750.  */
  751. class SuperDatabaseService {
  752. private $service;
  753. public $svcResultFormat;
  754. public $svcDatabase;
  755. public $svcTable;
  756.  
  757. /**
  758.   * I am a simple service class for multiplating a mysql database. Either through amfphp or javascript.
  759.   * For returning json just set the resultFormat = 'json'. Then you should be all set. Be sure to
  760.   * enter your credentials in the constructor of this class.
  761.   * @return
  762.   */
  763. public function __construct() {
  764. //TODO: Credentials here
  765.  
  766. #$this->service = new SuperDatabaseAccess('mysql', 'localhost', null, 'root', 'fred');
  767. $this->service = new SuperDatabaseAccess('mysql', DB_HOST, null, DB_USER, DB_PASS);
  768. }
  769. /**
  770.   * I return all the databases on the server with information about the contents.
  771.   * @return [array] - Tree ready array holding all databases with child nodes as tables, and table fields as child nodes.
  772.   */
  773. public function getDatabases() {
  774. $args = array();
  775. return $this->returnResult( 'getDatabases', $args, $this->service->getDatabases());
  776. }
  777. /**
  778.   * I return all records from the specified database/table.
  779.   * @param [string] - $database - The database name.
  780.   * @param [string] - $table - The table name.
  781.   * @return [array] - All records from table.
  782.   */
  783. public function select($database, $table, $columns = "*", $count = 25, $page = 0, $sort = null) {
  784. $this->svcDatabase = $database;
  785. $this->svcTable = $table;
  786. $args = array('columns' => $columns, 'count' => $count, 'page' => $page, 'sort' => $sort);
  787. return $this->returnResult( 'get', $args, $this->service->select($database, $table, $columns, $count, $page, $sort));
  788. }
  789.  
  790. public function one($database, $table, $keyvalue){
  791. return $this->returnResult('get', null, $this->service->selectOne($database, $table, $keyvalue));
  792. }
  793.  
  794. /**
  795.   * I create/update a record in the databases table.
  796.   * @param [string] - $database - The database name.
  797.   * @param [string] - $table - The table name.
  798.   * @param [object] - $vo - Assoc array containing all name/value for the object.
  799.   * This can be post fields where the key are the column names and the value is the new/edited values.
  800.   * If the table does not have a primary key then there is a problem and will not be saved.
  801.   * For testing in amfphp create your object like this. (ex. For updating: {"id":"4", "tag":"Flex"}) (ex. For creating: {"id":"0", "tag":"Flex"} )
  802.   * @return [object] - The object that was sent if the call was successfull or false if it was not.
  803.   */
  804. public function save($database, $table, $vo) {
  805. $this->svcDatabase = $database;
  806. $this->svcTable = $table;
  807. $args = $vo;
  808. return $this->returnResult( 'save', $args, $this->service->save($database, $table, $vo));
  809. }
  810. /**
  811.   * I create a record in the databases table.
  812.   * @param [string] - $database - The database name.
  813.   * @param [string] - $table - The table name.
  814.   * @param [object] - $vo - Assoc array containing all name/value for the object.
  815.   * This can be post fields where the key are the column names and the value is the new/edited values.
  816.   * If the table does not have a primary key then there is a problem and will not be saved.
  817.   * For testing in amfphp create your object like this. (ex. For creating: {"id":"0", "tag":"Flex"} )
  818.   * @return [object] - The object that was sent if the call was successfull or false if it was not.
  819.   */
  820. public function create($database, $table, $vo) {
  821. $this->svcDatabase = $database;
  822. $this->svcTable = $table;
  823. $args = $vo;
  824. return $this->returnResult( 'create', $args, $this->service->create($database, $table, $vo));
  825. }
  826. /**
  827.   * I update a record in the databases table.
  828.   *
  829.   * @param [string] - $database - The database name.
  830.   * @param [string] - $table - The table name.
  831.   * @param [object] - $vo - Assoc array containing all name/value for the object.
  832.   * This can be post fields where the key are the column names and the value is the new/edited values.
  833.   * If the table does not have a primary key then there is a problem and will not be saved.
  834.   * For testing in amfphp create your object like this. (ex. For updating: {"id":"4", "tag":"Flex"})
  835.   * @return [object] - The object that was sent if the call was successfull or false if it was not.
  836.   */
  837. public function update($database, $table, $vo) {
  838. $this->svcDatabase = $database;
  839. $this->svcTable = $table;
  840. $args = $vo;
  841. return $this->returnResult( 'update', $args, $this->service->update($database, $table, $vo));
  842. }
  843.  
  844. /**
  845. * I return the number of records in the specified table.
  846. *
  847. * @param object $database
  848. * @param object $table
  849. * @return
  850. */
  851. public function count($database, $table){
  852. $args = array();
  853. return $this->returnResult('count', $args, $this->service->count($database, $table));
  854. }
  855. /**
  856.   * I remove a record from the database/table.
  857.   *
  858.   * @param [string] - $database - The database name.
  859.   * @param [string] - $table - The table name.
  860.   * @param [object] - $vo - Assoc array containing name/value pairs, for deleting to be successful send the primary key as the key and the record id value
  861.   * to delete. (ex. Amfphp: {"id":"4"} )
  862.   * @return - [bool] - true or false of the call
  863.   */
  864. public function remove($database, $table, $vo) {
  865. $this->svcDatabase = $database;
  866. $this->svcTable = $table;
  867. $args = $vo;
  868. return $this->returnResult( 'search', $args, $this->service->remove($database, $table, $vo));
  869. }
  870. /**
  871.   * I get all of the tables with information about them from the specified database.
  872.   *
  873.   * @param [string] - $database - The database name.
  874.   * @return [array] - All tables with information
  875.   */
  876. public function getTables($database) {
  877. $this->svcDatabase = $database;
  878.  
  879. $args = array('database' => $database);
  880. return $this->returnResult( 'getTables', $args, $this->service->getTables($database));
  881. }
  882. /**
  883.   * I search any table/column that is specified in the paramenters.
  884.   *
  885.   * @param [string] - $database - the database
  886.   * @param [string] - $table - the table
  887.   * @param [array] - $columns [optional] - array of columns to select
  888.   * @param [string] - $where [optional] - condition to search
  889.   * @param [string] - $query [optional]
  890.   * @param [number] - $count [optional]
  891.   * @param [number] - $page [optional]
  892.   * @param [string] - $sort [optional]
  893.   * @return
  894.   */
  895. public function search($database, $table, $columns = "*", $where = null, $query = null, $count = 25, $page = 0, $sort = null) {
  896. $this->svcDatabase = $database;
  897. $this->svcTable = $table;
  898. $args = array('columns' => $columns, 'where' => $where, 'query' => $query, 'count' => $count, 'page' => $page, 'sort' => $sort);
  899. return $this->returnResult( 'search', $args, $this->service->search($database, $table, $columns, $where, $query, $count, $page, $sort));
  900. }
  901.  
  902. /**
  903.   *
  904.   * @return
  905.   */
  906. public function readLog() {
  907. $args = array();
  908. return $this->returnResult( 'readLog', $args, $this->service->readLog());
  909. }
  910.  
  911. public function writeLog($title, $var) {
  912. $args = array();
  913. return $this->returnResult( 'writeLog', $args, $this->service->writeLog($title, $var));
  914. }
  915.  
  916.  
  917.  
  918. /**
  919.   * I return the result from the call.
  920.   * Based on the result format specified in the
  921.   * url's extension.
  922.   *
  923.   * @param object $call - not used?
  924.   * @param object $args - array of arguments
  925.   * @param object $results - result array
  926.   * @return
  927.   */
  928. private function returnResult($call, $args, $results) {
  929. $argCount = count($args);
  930.  
  931.  
  932. if ('json' === $this->svcResultFormat) {
  933. #header('Content-type: application/json');
  934.  
  935. header('Content-type: text/plain');
  936. return json_encode($results);
  937.  
  938. } else if ('php' === $this->svcResultFormat) {
  939. #header('Content-type: application/x-httpd-php');
  940.  
  941. header('Content-type: text/plain');
  942. return serialize($results);
  943.  
  944. } else if ('sql' === $this->svcResultFormat) {
  945.  
  946. header('Content-type: text/plain');
  947. $sql = '';
  948.  
  949. foreach($results["data"] as $result) {
  950.  
  951. $sql .= 'INSERT INTO '.$this->svcDatabase.'.'.$this->svcTable.' VALUES ';
  952. foreach ($result as $key=>$value) {
  953. $sql .= $key.' = '.$value.', ';
  954. }
  955. $sql = substr($sql, 0, strlen($sql) - 2);
  956. $sql .= ";\n";
  957. }
  958. return $sql;
  959.  
  960.  
  961. } else if ('compressed' === $this->svcResultFormat){
  962. #$postedData = file_get_contents( 'php://input' );
  963. //uncompress this data
  964. //$uncompressedPostedData = gzuncompress($postedData);
  965. $compressed = gzcompress($results, 9);
  966. return $compressed;
  967.  
  968. } else if ('xml' === $this->svcResultFormat) {
  969. header('Content-type: text/xml; charset: utf-8;');
  970.  
  971. $xml = '<?xml version="1.0"?>';
  972. $xml .= "\n";
  973. $xml .= '<results
  974. status="'.$results[0]["status"].'"
  975. database="'.$results[0]["database"].'"
  976. table="'.$results[0]["table"].'"
  977. pages="'.$results[0]["pages"].'"
  978. page="'.$results[0]["page"].'"
  979. total="'.$results[0]["total"].'"
  980. count="'.$results[0]["count"].'">';
  981.  
  982. if($results[0]["data"]){
  983. $resultsData = $results[0]["data"];
  984. #$xml .= "\n";
  985.  
  986. if ($call === 'get' || $call === 'search'){
  987.  
  988. for ($i = 0; $i < count($resultsData); $i++) {
  989.  
  990. #$xml .= '<'.$results["table"].' row="'.($i + 1).'">';
  991. $xml .= '<row number="'.($i + 1).'">';
  992. #$xml .= "\n";
  993.  
  994. foreach ($resultsData[$i] as $key=>$value) {
  995. $xml .= '<'.htmlentities(trim($key)).'>'.htmlentities(trim($value)).'</'.htmlentities(trim($key)).'>';
  996. $xml .= "\n";
  997. }
  998. #$xml .= '</'.$results["table"].'>';
  999.  
  1000. $xml .= "</row>";
  1001. $xml .= "\n";
  1002. }
  1003. } else {
  1004. foreach ($resultsData as $key=>$value) {
  1005. $xml .= '<'.htmlentities(trim($key)).'>'.htmlentities(trim($value)).'</'.htmlentities(trim($key)).'>';
  1006. $xml .= "\n";
  1007. }
  1008. }
  1009. }
  1010.  
  1011. $xml .= "\n";
  1012. $xml .= '</results>';
  1013.  
  1014. return $xml;
  1015. } else if('zlib' == $this->svcResultFormat){
  1016. $out = $results;
  1017. $out = 'zlib';
  1018. $rawStr = implode(',', $results[0]);
  1019. $out = base64_encode($rawStr);
  1020. return ;
  1021. }
  1022. }
  1023. }
  1024.  
  1025.  
  1026. switch($_SERVER['REQUEST_METHOD'])
  1027. {
  1028. case 'GET':
  1029.  
  1030.  
  1031. if (isset($_GET['action'])){
  1032. $database = isset($_GET['database']) ? $_GET['database'] : null; //defaults
  1033. $table = isset($_GET['table']) ? $_GET['table'] : null;//defaults
  1034. $columns = isset($_GET['columns']) ? $_GET['columns'] : '*';//defaults
  1035. $where = isset($_GET['where']) ? $_GET['where'] : null;//defaults
  1036. $count = isset($_GET['count']) ? $_GET['count'] : 25;//defaults
  1037. $page = isset($_GET['page']) ? $_GET['page'] : 1;//defaults
  1038. $sort = isset($_GET['sort']) ? $_GET['sort'] : null;//defaults
  1039. $resultFormat = isset($_GET['format']) ? $_GET['format'] : null;//defaults
  1040. $query = isset($_GET['q']) ? $_GET['q'] : null;//defaults
  1041. $data = isset($_GET) ? $_GET : array();
  1042.  
  1043. $svc = new SuperDatabaseService();
  1044. $svc->svcResultFormat = $resultFormat;
  1045. #print_r( $data );
  1046.  
  1047.  
  1048. switch($_GET['action']){
  1049. case 'select':
  1050. echo $svc->select($database, $table, $columns, $count, $page, $sort);
  1051. break;
  1052. case 'search':
  1053. echo $svc->search($database, $table, $columns, $where, $query, $count, $page, $sort);
  1054. break;
  1055. case 'getDatabases':
  1056. echo $svc->getDatabases();
  1057. break;
  1058. case 'getTables':
  1059. echo $svc->getTables($database);
  1060. break;
  1061. case 'getLog':
  1062. echo $svc->readLog();
  1063. break;
  1064. case 'count':
  1065. echo $svc->count($database, $table);
  1066. break;
  1067.  
  1068. default:
  1069. echo 'Please specify an action';
  1070. break;
  1071. }
  1072. }
  1073. break;
  1074.  
  1075.  
  1076. case 'POST':
  1077.  
  1078. if (isset($_POST['action'])){
  1079. $database = isset($_POST['database']) ? $_POST['database'] : '';
  1080. $table = isset($_POST['table']) ? $_POST['table'] : '';
  1081. $resultFormat = isset($_POST['format']) ? $_POST['format'] : '';
  1082.  
  1083. $data = isset($_POST) ? $_POST : array();
  1084.  
  1085. unset($data['database']);
  1086. unset($data['table']);
  1087. unset($data['format']);
  1088. unset($data['action']);
  1089.  
  1090. $svc = new SuperDatabaseService();
  1091. $svc->svcResultFormat = $resultFormat;
  1092.  
  1093. #print_r( $data );
  1094.  
  1095. switch($_POST['action']){
  1096. case 'save':
  1097. echo $svc->save($database, $table, $data);
  1098. break;
  1099. case 'create':
  1100. echo $svc->create($database, $table, $data);
  1101. break;
  1102. case 'update':
  1103. echo $svc->update($database, $table, $data);
  1104. break;
  1105. case 'delete':
  1106. echo $svc->remove($database, $table, $data);
  1107. break;
  1108. default:
  1109. echo 'Please specify an action';
  1110. break;
  1111. }
  1112. }
  1113.  
  1114. break;
  1115.  
  1116. }
  1117.  
  1118.  
  1119.  
  1120.  
  1121. ?>

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.