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 Edge (
  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 define("QUERY_UPDATE_Edge", "
  67 UPDATE Edge SET
  68         typeid=unhex(replace(?, '-', '')), nodeid0=unhex(replace(?, '-', '')), nodeid1=unhex(replace(?, '-', '')), modtimestamp=?
  69 WHERE
  70         edgeid=unhex(replace(?, '-', ''))
  71 ");
  72 define("QUERY_UPDATE_Edge_TYPES", "sssis");
  73 //
  74 define("QUERY_SELECT_ALL_Node", "select hex(nodeid), hex(typeid), identifier from Node");
  75 define("QUERY_SELECT_ALL_Node_With_modtimestamp", "select hex(nodeid), hex(typeid), identifier, modtimestamp from Node");
  76 define("QUERY_SELECT_ALL_Edge", "select hex(edgeid), hex(typeid),  hex(nodeid0), hex(nodeid1) from Edge");
  77 define("QUERY_SELECT_ALL_Edge_With_modtimestamp", "select hex(edgeid), hex(typeid),  hex(nodeid0), hex(nodeid1), modtimestamp from Edge");
  78
  79 define("QUERY_SELECT_modified_Node", "select hex(nodeid), hex(typeid), identifier from Node WHERE modtimestamp>?");
  80 define("QUERY_SELECT_modified_Node_TYPES", "i");
  81
  82 //FOR DEBUG
  83 mysqli_report(MYSQLI_REPORT_ERROR);
  84
  85 $db = connectDB();
  86
  87 //action解釈
  88 if(isset($_GET['action'])){
  89         $action = $_GET['action'];
  90         if(strcmp($action, 'rebuild') == 0){
  91                 rebuildDB($db);
  92                 exitWithResponseCode("CEA95615-649C-4837-9E24-0C968FA57647", "OK");
  93         } else if(strcmp($action, 'getallnode') == 0){
  94                 $stmt = $db->prepare(QUERY_SELECT_ALL_Node);
  95                 $stmt->execute();
  96                 //
  97                 $stmt->store_result();
  98                 if($stmt->errno != 0){
  99                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 100                 }
 101
 102                 $stmt->bind_result($uuid, $typeid, $ident);
 103                 while($stmt->fetch()){
 104                         echoNode(
 105                                 getFormedUUIDString($uuid),
 106                                 getFormedUUIDString($typeid),
 107                                 $ident
 108                         );
 109                         echo(PHP_EOL);
 110                 }
 111                 $stmt->close();
 112                 // put timestamp tag
 113                 echoMemoryDBNetworkTimestamp();
 114                 exit();
 115         } else if(strcmp($action, 'getalledge') == 0){
 116                 $stmt = $db->prepare(QUERY_SELECT_ALL_Edge);
 117                 $stmt->execute();
 118                 //
 119                 $stmt->store_result();
 120                 if($stmt->errno != 0){
 121                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 122                 }
 123
 124                 $stmt->bind_result($uuid, $typeid, $nid0, $nid1);
 125                 while($stmt->fetch()){
 126                         echoEdge(
 127                                 getFormedUUIDString($uuid),
 128                                 getFormedUUIDString($typeid),
 129                                 getFormedUUIDString($nid0),
 130                                 getFormedUUIDString($nid1)
 131                         );
 132                         echo(PHP_EOL);
 133                 }
 134                 $stmt->close();
 135                 // put timestamp tag
 136                 echoMemoryDBNetworkTimestamp();
 137                 exit();
 138         } else if(strcmp($action, 'getallnodemod') == 0){
 139                 if(isset($_GET['t'])){
 140                         $ts = $_GET['t'];
 141                 } else{
 142                         $ts = 0;
 143                 }
 144                 $stmt = $db->prepare(QUERY_SELECT_modified_Node);
 145                 $stmt->bind_param(QUERY_SELECT_modified_Node_TYPES, $ts);
 146                 $stmt->execute();
 147                 //
 148                 $stmt->store_result();
 149                 if($stmt->errno != 0){
 150                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 151                 }
 152
 153                 $stmt->bind_result($uuid, $typeid, $ident);
 154                 while($stmt->fetch()){
 155                         echoNode(
 156                                 getFormedUUIDString($uuid),
 157                                 getFormedUUIDString($typeid),
 158                                 $ident
 159                         );
 160                         echo(PHP_EOL);
 161                 }
 162                 $stmt->close();
 163                 // put timestamp tag
 164                 echoMemoryDBNetworkTimestamp();
 165                 exit();
 166         } else if(strcmp($action, 'viewallnode') == 0){
 167                 $stmt = $db->prepare(QUERY_SELECT_ALL_Node_With_modtimestamp);
 168                 $stmt->execute();
 169                 //
 170                 $stmt->store_result();
 171                 if($stmt->errno != 0){
 172                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 173                 }
 174
 175                 $stmt->bind_result($uuid, $typeid, $ident, $mts);
 176                 while($stmt->fetch()){
 177                         echoNode(
 178                                 getFormedUUIDString($uuid),
 179                                 getFormedUUIDString($typeid),
 180                                 $ident
 181                         );
 182                         echo(' @' . $mts);
 183                         echo("<br />");
 184                 }
 185                 echo($stmt->num_rows);
 186                 $stmt->close();
 187                 exit(" OK " . getTimeStampMs());
 188         } else if(strcmp($action, 'viewalledge') == 0){
 189                 $stmt = $db->prepare(QUERY_SELECT_ALL_Edge_With_modtimestamp);
 190                 $stmt->execute();
 191                 //
 192                 $stmt->store_result();
 193                 if($stmt->errno != 0){
 194                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B");
 195                 }
 196
 197                 $stmt->bind_result($uuid, $typeid, $nid0, $nid1, $mts);
 198                 while($stmt->fetch()){
 199                         echoEdge(
 200                                 getFormedUUIDString($uuid),
 201                                 getFormedUUIDString($typeid),
 202                                 getFormedUUIDString($nid0),
 203                                 getFormedUUIDString($nid1)
 204                         );
 205                         echo(' @' . $mts);
 206                         echo("<br />");
 207                 }
 208                 echo($stmt->num_rows);
 209                 $stmt->close();
 210                 exit(" OK " . getTimeStampMs());
 211         } else if(strcmp($action, 'addnode') == 0){
 212                 if(isset($_GET['nid'])){
 213                         $nodeid = $_GET['nid'];
 214                 } else{
 215                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid needed.");
 216                 }
 217                 if(isset($_GET['tid'])){
 218                         $typeid = $_GET['tid'];
 219                 } else{
 220                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 221                 }
 222                 if(isset($_GET['ident'])){
 223                         $ident = $_GET['ident'];
 224                 } else{
 225                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "ident needed.");
 226                 }
 227
 228                 $stmt = $db->prepare(QUERY_ADD_Node);
 229                 $mts = getTimeStampMs();
 230                 $stmt->bind_param(QUERY_ADD_Node_TYPES, $nodeid, $typeid, $ident, $mts);
 231                 $stmt->execute();
 232                 //
 233                 $stmt->store_result();
 234                 if($stmt->errno != 0){
 235                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 236                 }
 237                 $stmt->close();
 238                 exitWithResponseCode("cea95615-649c-4837-9e24-0c968fa57647", "OK");
 239         } else if(strcmp($action, 'updatenode') == 0){
 240                 if(isset($_GET['nid'])){
 241                         $nodeid = $_GET['nid'];
 242                 } else{
 243                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid needed.");
 244                 }
 245                 if(isset($_GET['tid'])){
 246                         $typeid = $_GET['tid'];
 247                 } else{
 248                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 249                 }
 250                 if(isset($_GET['ident'])){
 251                         $ident = $_GET['ident'];
 252                 } else{
 253                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "ident needed.");
 254                 }
 255
 256                 $stmt = $db->prepare(QUERY_UPDATE_Node);
 257                 $mts = getTimeStampMs();
 258                 $stmt->bind_param(QUERY_UPDATE_Node_TYPES, $typeid, $ident, $mts, $nodeid);
 259                 $stmt->execute();
 260                 //
 261                 $stmt->store_result();
 262                 if($stmt->errno != 0){
 263                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 264                 }
 265                 $stmt->close();
 266                 exitWithResponseCode("cea95615-649c-4837-9e24-0c968fa57647", "OK");
 267         } else if(strcmp($action, 'addedge') == 0){
 268                 if(isset($_GET['eid'])){
 269                         $edgeid = $_GET['eid'];
 270                 } else{
 271                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "edgeid needed.");
 272                 }
 273                 if(isset($_GET['tid'])){
 274                         $typeid = $_GET['tid'];
 275                 } else{
 276                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 277                 }
 278                 if(isset($_GET['nid0'])){
 279                         $nid0 = $_GET['nid0'];
 280                 } else{
 281                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid0 needed.");
 282                 }
 283                 if(isset($_GET['nid1'])){
 284                         $nid1 = $_GET['nid1'];
 285                 } else{
 286                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid1 needed.");
 287                 }
 288
 289                 $stmt = $db->prepare(QUERY_ADD_Edge);
 290                 $mts = getTimeStampMs();
 291                 $stmt->bind_param(QUERY_ADD_Edge_TYPES, $edgeid, $typeid, $nid0, $nid1, $mts);
 292                 $stmt->execute();
 293                 //
 294                 $stmt->store_result();
 295                 if($stmt->errno != 0){
 296                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 297                 }
 298                 $stmt->close();
 299                 exitWithResponseCode("cea95615-649c-4837-9e24-0c968fa57647", "OK");
 300         } else if(strcmp($action, 'updateedge') == 0){
 301                 if(isset($_GET['eid'])){
 302                         $edgeid = $_GET['eid'];
 303                 } else{
 304                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "edgeid needed.");
 305                 }
 306                 if(isset($_GET['tid'])){
 307                         $typeid = $_GET['tid'];
 308                 } else{
 309                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 310                 }
 311                 if(isset($_GET['nid0'])){
 312                         $nid0 = $_GET['nid0'];
 313                 } else{
 314                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid0 needed.");
 315                 }
 316                 if(isset($_GET['nid1'])){
 317                         $nid1 = $_GET['nid1'];
 318                 } else{
 319                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", "nodeid1 needed.");
 320                 }
 321
 322                 $stmt = $db->prepare(QUERY_UPDATE_Edge);
 323                 $mts = getTimeStampMs();
 324                 $stmt->bind_param(QUERY_UPDATE_Edge_TYPES, $typeid, $nid0, $nid1, $mts, $edgeid);
 325                 $stmt->execute();
 326                 //
 327                 $stmt->store_result();
 328                 if($stmt->errno != 0){
 329                         exitWithResponseCode("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 330                 }
 331                 $stmt->close();
 332                 exitWithResponseCode("cea95615-649c-4837-9e24-0c968fa57647", "OK");
 333         }
 334 }
 335
 336 //NOP error
 337 exitWithResponseCode("b539657c-0fa6-49c2-afb0-13af5c7866ed");
 338
 339 function exitWithResponseCode($errid, $description = "")
 340 {
 341         echoNode("1eeb6d3d-751f-444f-91c8-ed940e65f8bd", $errid, $description);
 342         exit();
 343 }
 344
 345 function echoMemoryDBNetworkTimestamp()
 346 {
 347         echoNode(
 348                 "a2560a9c-dcf7-4746-ac14-347188518cf2",
 349                 "e3346fd4-ac17-41c3-b3c7-e04972e5c014",
 350                 getTimeStampMs()
 351         );
 352 }
 353
 354 function echoNode($nid, $tid, $ident)
 355 {
 356         echo('["' . $nid .'","' . $tid .'","' . $ident . '"]');
 357 }
 358
 359 function echoEdge($eid, $tid, $nid0, $nid1)
 360 {
 361         echo('["' . $eid .'","' . $tid .'","' . $nid0 .'","' . $nid1 . '"]');
 362 }
 363
 364 function connectDB()
 365 {
 366         $db = new mysqli('localhost', DATABASE_USER, DATABASE_PWD, DATABASE_NAME);
 367
 368         if (mysqli_connect_error()) {
 369                 // DB connect error
 370                 exitWithResponseCode("3A8CF3C8-E6B6-4A99-9134-343CA341B591", mysqli_connect_error());
 371         }
 372
 373         // 文字化け防止
 374         $db->set_charset("utf8");
 375
 376         //データベース存在確認
 377         $stmt = $db->prepare("show tables");
 378         $stmt->execute();
 379         //
 380         $stmt->store_result();
 381         if($stmt->errno != 0){
 382                 exitWithResponseCode("80FA2D65-9473-40B0-A3CE-159AE8E67017");
 383         }
 384         //テーブルの存在確認
 385         $stmt->bind_result($tablename);
 386         $found = 0;
 387         while($stmt->fetch()){
 388                 if($tablename == "Node"){
 389                         $found |= 1;
 390                 }
 391                 if($tablename == "Edge"){
 392                         $found |= 2;
 393                 }
 394         }
 395         if(($found & 3) != 3){
 396                 rebuildDB($db);
 397         }
 398         $stmt->close();
 399
 400         return $db;
 401 }
 402
 403 function rebuildDB($db)
 404 {
 405         //
 406         // 削除
 407         //
 408
 409         // Node
 410         $stmt = $db->query("drop table if exists Node");
 411
 412         // Edge
 413         $stmt = $db->query("drop table if exists Edge");
 414
 415         //
 416         // 再構築
 417         //
 418
 419         // Node
 420         $stmt = $db->query(QUERY_CREATE_TABLE_Node);
 421
 422         // Edge
 423         $stmt = $db->query(QUERY_CREATE_TABLE_Edge);
 424 }
 425
 426 function getFormedUUIDString($str)
 427 {
 428         $str = strtolower($str);
 429         return (
 430                 substr($str, 0, 8) . "-" .
 431                 substr($str, 8, 4) . "-" .
 432                 substr($str, 12, 4) . "-" .
 433                 substr($str, 16, 4) . "-" .
 434                 substr($str, 20, 12)
 435         );
 436 }
 437
 438 function getTimeStampMs()
 439 {
 440         return ceil(microtime(true)*1000);
 441 }
 442 ?>