memdb/memdb.php

   1 <?php
   2
   3 //
   4 // Settings
   5 //
   6
   7 // grant 権限内容(all privileges) on 権限対象(dbname.tablename) to ユーザー@ホスト名 [ identified by "パスワード"];
   8
   9 //データベースユーザー名
  10 define("DATABASE_USER", "aiclient");
  11 //データベースパスワード
  12 define("DATABASE_PWD", "WhoAmI?");
  13 //データベース名
  14 define("DATABASE_NAME", "MemoryDB");
  15
  16 //
  17 // Static values
  18 //
  19
  20 define("QUERY_CREATE_TABLE_Node", "
  21 create table Node (
  22         nodeid binary(16) primary key,
  23         typeid binary(16) not null,
  24         identifier text character set utf8 not null,
  25         modtimestamp bigint
  26 )
  27 ");
  28
  29 define("QUERY_CREATE_TABLE_Edge", "
  30 create table Edge (
  31         edgeid binary(16) primary key,
  32         typeid binary(16) not null,
  33         nodeid0 binary(16) not null,
  34         nodeid1 binary(16) not null,
  35         modtimestamp bigint
  36 )
  37 ");
  38
  39 //
  40
  41 define("QUERY_ADD_Node", "
  42 insert into Node (
  43         nodeid, typeid, identifier, modtimestamp
  44 ) values (
  45         unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), ?, ?
  46 )
  47 ");
  48 define("QUERY_ADD_Node_TYPES", "sssi");
  49
  50 define("QUERY_ADD_Edge", "
  51 insert into Node (
  52         edgeid, typeid, nodeid0, nodeid1, modtimestamp
  53 ) values (
  54         unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), ?
  55 )
  56 ");
  57 define("QUERY_ADD_Edge_TYPES", "ssssi");
  58
  59 //
  60
  61 define("QUERY_SELECT_ALL_Node", "select hex(nodeid), hex(typeid), identifier from Node");
  62 define("QUERY_SELECT_ALL_Node_With_modtimestamp", "select hex(nodeid), hex(typeid), identifier, modtimestamp from Node");
  63 define("QUERY_SELECT_ALL_Edge", "select hex(edgeid), hex(typeid),  hex(nodeid0), hex(nodeid1) from Edge");
  64
  65 define("QUERY_SELECT_modified_Node", "select hex(nodeid), hex(typeid), identifier from Node WHERE modtimestamp>?");
  66 define("QUERY_SELECT_modified_Node_TYPES", "i");
  67
  68 //FOR DEBUG
  69 mysqli_report(MYSQLI_REPORT_ERROR);
  70
  71 $db = connectDB();
  72
  73 //action解釈
  74 if(isset($_GET['action'])){
  75         $action = $_GET['action'];
  76         if(strcmp($action, 'rebuild') == 0){
  77                 rebuildDB($db);
  78                 exitWithResponseCode("CEA95615-649C-4837-9E24-0C968FA57647", "OK");
  79         } else if(strcmp($action, 'getallnode') == 0){
  80                 $stmt = $db->prepare(QUERY_SELECT_ALL_Node);
  81                 $stmt->execute();
  82                 //
  83                 $stmt->store_result();
  84                 if($stmt->errno != 0){
  85                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
  86                 }
  87
  88                 $stmt->bind_result($uuid, $typeid, $ident);
  89                 while($stmt->fetch()){
  90                         $uuid = strtolower($uuid);
  91                         echo('["');
  92                         echo(getFormedUUIDString($uuid));
  93                         echo('","');
  94                         echo(getFormedUUIDString($typeid));
  95                         echo('","');
  96                         echo($ident);
  97                         echo('"]');
  98                         echo(PHP_EOL);
  99                 }
 100                 $stmt->close();
 101                 // put timestamp tag
 102                 echoMemoryDBNetworkTimestamp();
 103                 exit();
 104         } else if(strcmp($action, 'getnodemod') == 0){
 105                 if(isset($_GET['t'])){
 106                         $ts = $_GET['t'];
 107                 } else{
 108                         $ts = 0;
 109                 }
 110                 $stmt = $db->prepare(QUERY_SELECT_modified_Node);
 111                 $stmt->bind_param(QUERY_SELECT_modified_Node_TYPES, $ts);
 112                 $stmt->execute();
 113                 //
 114                 $stmt->store_result();
 115                 if($stmt->errno != 0){
 116                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 117                 }
 118
 119                 $stmt->bind_result($uuid, $typeid, $ident);
 120                 while($stmt->fetch()){
 121                         $uuid = strtolower($uuid);
 122                         echo('["');
 123                         echo(getFormedUUIDString($uuid));
 124                         echo('","');
 125                         echo(getFormedUUIDString($typeid));
 126                         echo('","');
 127                         echo($ident);
 128                         echo('"]');
 129                         echo(PHP_EOL);
 130                 }
 131                 $stmt->close();
 132                 // put timestamp tag
 133                 echoMemoryDBNetworkTimestamp();
 134                 exit();
 135         } else if(strcmp($action, 'viewallnode') == 0){
 136                 $stmt = $db->prepare(QUERY_SELECT_ALL_Node_With_modtimestamp);
 137                 $stmt->execute();
 138                 //
 139                 $stmt->store_result();
 140                 if($stmt->errno != 0){
 141                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 142                 }
 143
 144                 $stmt->bind_result($uuid, $typeid, $ident, $mts);
 145                 while($stmt->fetch()){
 146                         $uuid = strtolower($uuid);
 147                         echo('["');
 148                         echo(getFormedUUIDString($uuid));
 149                         echo('","');
 150                         echo(getFormedUUIDString($typeid));
 151                         echo('","');
 152                         echo($ident);
 153                         echo('"]');
 154                         echo(' @' . $mts);
 155                         echo("<br />");
 156                 }
 157                 echo($stmt->num_rows);
 158                 $stmt->close();
 159                 exit(" OK " . getTimeStampMs());
 160         } else if(strcmp($action, 'addnode') == 0){
 161                 if(isset($_GET['nid'])){
 162                         $nodeid = $_GET['nid'];
 163                 } else{
 164                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid needed.");
 165                 }
 166                 if(isset($_GET['tid'])){
 167                         $typeid = $_GET['tid'];
 168                 } else{
 169                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 170                 }
 171                 if(isset($_GET['ident'])){
 172                         $ident = $_GET['ident'];
 173                 } else{
 174                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "ident needed.");
 175                 }
 176
 177                 $stmt = $db->prepare(QUERY_ADD_Node);
 178                 $mts = getTimeStampMs();
 179                 $stmt->bind_param(QUERY_ADD_Node_TYPES, $nodeid, $typeid, $ident, $mts);
 180                 $stmt->execute();
 181                 //
 182                 $stmt->store_result();
 183                 if($stmt->errno != 0){
 184                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 185                 }
 186                 $stmt->close();
 187                 exitWithResponseCode("CEA95615-649C-4837-9E24-0C968FA57647", "OK");
 188         } else if(strcmp($action, 'saytest') == 0){
 189                 /*
 190                 //for Mac OSX say command.
 191                 system("say " . escapeshellarg("sayのテストをしています。"));
 192                 */
 193         }
 194 }
 195
 196 //NOP error
 197 exitWithResponseCode("B539657C-0FA6-49C2-AFB0-13AF5C7866ED");
 198
 199 function exitWithResponseCode($errid, $description = "")
 200 {
 201         die('["' . $errid .'","' . $description . '"]');
 202 }
 203
 204 function echoMemoryDBNetworkTimestamp()
 205 {
 206         echo('["a2560a9c-dcf7-4746-ac14-347188518cf2","e3346fd4-ac17-41c3-b3c7-e04972e5c014","');
 207         echo(getTimeStampMs());
 208         echo('"]');
 209         echo(PHP_EOL);
 210 }
 211
 212 function connectDB()
 213 {
 214         $db = new mysqli('localhost', DATABASE_USER, DATABASE_PWD, DATABASE_NAME);
 215
 216         if (mysqli_connect_error()) {
 217                 // DB connect error
 218                 exitWithResponseCode("3A8CF3C8-E6B6-4A99-9134-343CA341B591", mysqli_connect_error());
 219         }
 220
 221         // 文字化け防止
 222         $db->set_charset("utf8");
 223
 224         //データベース存在確認
 225         $stmt = $db->prepare("show tables");
 226         $stmt->execute();
 227         //
 228         $stmt->store_result();
 229         if($stmt->errno != 0){
 230                 exitWithResponseCode("80FA2D65-9473-40B0-A3CE-159AE8E67017");
 231         }
 232         //テーブルの存在確認
 233         $stmt->bind_result($tablename);
 234         $found = 0;
 235         while($stmt->fetch()){
 236                 if($tablename == "Node"){
 237                         $found |= 1;
 238                 }
 239                 if($tablename == "Edge"){
 240                         $found |= 2;
 241                 }
 242         }
 243         if(($found & 3) != 3){
 244                 rebuildDB($db);
 245         }
 246         $stmt->close();
 247
 248         return $db;
 249 }
 250
 251 function rebuildDB($db)
 252 {
 253         //
 254         // 削除
 255         //
 256
 257         // Node
 258         $stmt = $db->query("drop table if exists Node");
 259
 260         // Edge
 261         $stmt = $db->query("drop table if exists Edge");
 262
 263         //
 264         // 再構築
 265         //
 266
 267         // Node
 268         $stmt = $db->query(QUERY_CREATE_TABLE_Node);
 269
 270         // Edge
 271         $stmt = $db->query(QUERY_CREATE_TABLE_Edge);
 272 }
 273
 274 function getFormedUUIDString($str)
 275 {
 276         $str = strtolower($str);
 277         return (
 278                 substr($str, 0, 8) . "-" .
 279                 substr($str, 8, 4) . "-" .
 280                 substr($str, 12, 4) . "-" .
 281                 substr($str, 16, 4) . "-" .
 282                 substr($str, 20, 12)
 283         );
 284 }
 285
 286 function getTimeStampMs()
 287 {
 288         return ceil(microtime(true)*1000);
 289 }
 290 ?>