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,
  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 define("QUERY_ADD_Node", "
  41 insert into Node (
  42         nodeid, typeid, identifier, modtimestamp
  43 ) values (
  44         unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), ?, ?
  45 )
  46 ");
  47 define("QUERY_ADD_Node_TYPES", "sssi");
  48 //
  49 define("QUERY_ADD_Edge", "
  50 insert into Node (
  51         edgeid, typeid, nodeid0, nodeid1, modtimestamp
  52 ) values (
  53         unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), ?
  54 )
  55 ");
  56 define("QUERY_ADD_Edge_TYPES", "ssssi");
  57 //
  58 define("QUERY_UPDATE_Node", "
  59 UPDATE Node SET
  60         typeid=unhex(replace(?, '-', '')), identifier=?, modtimestamp=?
  61 WHERE
  62         nodeid=unhex(replace(?, '-', ''))
  63 ");
  64 define("QUERY_UPDATE_Node_TYPES", "ssis");
  65 //
  66
  67 define("QUERY_SELECT_ALL_Node", "select hex(nodeid), hex(typeid), identifier from Node");
  68 define("QUERY_SELECT_ALL_Node_With_modtimestamp", "select hex(nodeid), hex(typeid), identifier, modtimestamp from Node");
  69 define("QUERY_SELECT_ALL_Edge", "select hex(edgeid), hex(typeid),  hex(nodeid0), hex(nodeid1) from Edge");
  70
  71 define("QUERY_SELECT_modified_Node", "select hex(nodeid), hex(typeid), identifier from Node WHERE modtimestamp>?");
  72 define("QUERY_SELECT_modified_Node_TYPES", "i");
  73
  74 //FOR DEBUG
  75 mysqli_report(MYSQLI_REPORT_ERROR);
  76
  77 $db = connectDB();
  78
  79 //action解釈
  80 if(isset($_GET['action'])){
  81         $action = $_GET['action'];
  82         if(strcmp($action, 'rebuild') == 0){
  83                 rebuildDB($db);
  84                 exitWithResponseCode("CEA95615-649C-4837-9E24-0C968FA57647", "OK");
  85         } else if(strcmp($action, 'getallnode') == 0){
  86                 $stmt = $db->prepare(QUERY_SELECT_ALL_Node);
  87                 $stmt->execute();
  88                 //
  89                 $stmt->store_result();
  90                 if($stmt->errno != 0){
  91                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
  92                 }
  93
  94                 $stmt->bind_result($uuid, $typeid, $ident);
  95                 while($stmt->fetch()){
  96                         echoNode(
  97                                 getFormedUUIDString($uuid),
  98                                 getFormedUUIDString($typeid),
  99                                 $ident
 100                         );
 101                         echo(PHP_EOL);
 102                 }
 103                 $stmt->close();
 104                 // put timestamp tag
 105                 echoMemoryDBNetworkTimestamp();
 106                 exit();
 107         } else if(strcmp($action, 'getnodemod') == 0){
 108                 if(isset($_GET['t'])){
 109                         $ts = $_GET['t'];
 110                 } else{
 111                         $ts = 0;
 112                 }
 113                 $stmt = $db->prepare(QUERY_SELECT_modified_Node);
 114                 $stmt->bind_param(QUERY_SELECT_modified_Node_TYPES, $ts);
 115                 $stmt->execute();
 116                 //
 117                 $stmt->store_result();
 118                 if($stmt->errno != 0){
 119                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 120                 }
 121
 122                 $stmt->bind_result($uuid, $typeid, $ident);
 123                 while($stmt->fetch()){
 124                         echoNode(
 125                                 getFormedUUIDString($uuid),
 126                                 getFormedUUIDString($typeid),
 127                                 $ident
 128                         );
 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                         echoNode(
 147                                 getFormedUUIDString($uuid),
 148                                 getFormedUUIDString($typeid),
 149                                 $ident
 150                         );
 151                         echo(' @' . $mts);
 152                         echo("<br />");
 153                 }
 154                 echo($stmt->num_rows);
 155                 $stmt->close();
 156                 exit(" OK " . getTimeStampMs());
 157         } else if(strcmp($action, 'addnode') == 0){
 158                 if(isset($_GET['nid'])){
 159                         $nodeid = $_GET['nid'];
 160                 } else{
 161                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid needed.");
 162                 }
 163                 if(isset($_GET['tid'])){
 164                         $typeid = $_GET['tid'];
 165                 } else{
 166                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 167                 }
 168                 if(isset($_GET['ident'])){
 169                         $ident = $_GET['ident'];
 170                 } else{
 171                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "ident needed.");
 172                 }
 173
 174                 $stmt = $db->prepare(QUERY_ADD_Node);
 175                 $mts = getTimeStampMs();
 176                 $stmt->bind_param(QUERY_ADD_Node_TYPES, $nodeid, $typeid, $ident, $mts);
 177                 $stmt->execute();
 178                 //
 179                 $stmt->store_result();
 180                 if($stmt->errno != 0){
 181                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 182                 }
 183                 $stmt->close();
 184                 exitWithResponseCode("cea95615-649c-4837-9e24-0c968fa57647", "OK");
 185         } else if(strcmp($action, 'updatenode') == 0){
 186                 if(isset($_GET['nid'])){
 187                         $nodeid = $_GET['nid'];
 188                 } else{
 189                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid needed.");
 190                 }
 191                 if(isset($_GET['tid'])){
 192                         $typeid = $_GET['tid'];
 193                 } else{
 194                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 195                 }
 196                 if(isset($_GET['ident'])){
 197                         $ident = $_GET['ident'];
 198                 } else{
 199                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "ident needed.");
 200                 }
 201
 202                 $stmt = $db->prepare(QUERY_UPDATE_Node);
 203                 $mts = getTimeStampMs();
 204                 $stmt->bind_param(QUERY_UPDATE_Node_TYPES, $typeid, $ident, $mts, $nodeid);
 205                 $stmt->execute();
 206                 //
 207                 $stmt->store_result();
 208                 if($stmt->errno != 0){
 209                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 210                 }
 211                 $stmt->close();
 212                 exitWithResponseCode("cea95615-649c-4837-9e24-0c968fa57647", "OK");
 213         }
 214 }
 215
 216 //NOP error
 217 exitWithResponseCode("b539657c-0fa6-49c2-afb0-13af5c7866ed");
 218
 219 function exitWithResponseCode($errid, $description = "")
 220 {
 221         echoNode("1eeb6d3d-751f-444f-91c8-ed940e65f8bd", $errid, $description);
 222         exit();
 223 }
 224
 225 function echoMemoryDBNetworkTimestamp()
 226 {
 227         echoNode(
 228                 "a2560a9c-dcf7-4746-ac14-347188518cf2",
 229                 "e3346fd4-ac17-41c3-b3c7-e04972e5c014",
 230                 getTimeStampMs()
 231         );
 232 }
 233
 234 function echoNode($nid, $tid, $ident)
 235 {
 236         echo('["' . $nid .'","' . $tid .'","' . $ident . '"]');
 237 }
 238
 239 function connectDB()
 240 {
 241         $db = new mysqli('localhost', DATABASE_USER, DATABASE_PWD, DATABASE_NAME);
 242
 243         if (mysqli_connect_error()) {
 244                 // DB connect error
 245                 exitWithResponseCode("3A8CF3C8-E6B6-4A99-9134-343CA341B591", mysqli_connect_error());
 246         }
 247
 248         // 文字化け防止
 249         $db->set_charset("utf8");
 250
 251         //データベース存在確認
 252         $stmt = $db->prepare("show tables");
 253         $stmt->execute();
 254         //
 255         $stmt->store_result();
 256         if($stmt->errno != 0){
 257                 exitWithResponseCode("80FA2D65-9473-40B0-A3CE-159AE8E67017");
 258         }
 259         //テーブルの存在確認
 260         $stmt->bind_result($tablename);
 261         $found = 0;
 262         while($stmt->fetch()){
 263                 if($tablename == "Node"){
 264                         $found |= 1;
 265                 }
 266                 if($tablename == "Edge"){
 267                         $found |= 2;
 268                 }
 269         }
 270         if(($found & 3) != 3){
 271                 rebuildDB($db);
 272         }
 273         $stmt->close();
 274
 275         return $db;
 276 }
 277
 278 function rebuildDB($db)
 279 {
 280         //
 281         // 削除
 282         //
 283
 284         // Node
 285         $stmt = $db->query("drop table if exists Node");
 286
 287         // Edge
 288         $stmt = $db->query("drop table if exists Edge");
 289
 290         //
 291         // 再構築
 292         //
 293
 294         // Node
 295         $stmt = $db->query(QUERY_CREATE_TABLE_Node);
 296
 297         // Edge
 298         $stmt = $db->query(QUERY_CREATE_TABLE_Edge);
 299 }
 300
 301 function getFormedUUIDString($str)
 302 {
 303         $str = strtolower($str);
 304         return (
 305                 substr($str, 0, 8) . "-" .
 306                 substr($str, 8, 4) . "-" .
 307                 substr($str, 12, 4) . "-" .
 308                 substr($str, 16, 4) . "-" .
 309                 substr($str, 20, 12)
 310         );
 311 }
 312
 313 function getTimeStampMs()
 314 {
 315         return ceil(microtime(true)*1000);
 316 }
 317 ?>