dbmysql.php

   1 <?php
   2 //responseError("");
   3
   4 //
   5 // Settings
   6 //
   7
   8 //データベースユーザー名
   9 define("DATABASE_USER", "aiclient");
  10 //データベースパスワード
  11 define("DATABASE_PWD", "WhoAmI?");
  12 //データベース名
  13 define("DATABASE_NAME", "aimemory");
  14
  15 //
  16 // Static values
  17 //
  18
  19 define("QUERY_CREATE_TABLE_MEMORY_TAG_ROOT", "
  20 create table MemoryTagRoot (
  21         uuid binary(16) primary key,
  22         typeid binary(16) not null,
  23         description text character set utf8 not null,
  24         created_timestamp bigint,
  25         modified_timestamp bigint,
  26         data text character set utf8 not null,
  27         index(uuid)
  28 )
  29 ");
  30
  31 define("QUERY_ADD_MTAG", "insert into MemoryTagRoot (
  32         uuid, typeid, description, created_timestamp, modified_timestamp, data
  33 ) values (
  34         unhex(replace(?, '-', '')), unhex(replace(?, '-', '')), ?, ?, ?, ?
  35 )");
  36 define("QUERY_ADD_MTAG_TYPES", "sssiis");
  37
  38 define("QUERY_SELECT_ALL_MTAG", "select hex(uuid), hex(typeid), description, created_timestamp, modified_timestamp, data from MemoryTagRoot");
  39
  40
  41 //FOR DEBUG
  42 mysqli_report(MYSQLI_REPORT_ERROR);
  43
  44 $db = connectDB();
  45
  46 //action解釈
  47 if(isset($_GET['action'])){
  48         $action = $_GET['action'];
  49         if(strcmp($action, 'rebuild') == 0){
  50                 rebuildDB($db);
  51                 responseError("CEA95615-649C-4837-9E24-0C968FA57647", "OK");
  52         } else if(strcmp($action, 'viewall') == 0){
  53                 $stmt = $db->prepare(QUERY_SELECT_ALL_MTAG);
  54                 $stmt->execute();
  55                 //
  56                 $stmt->store_result();
  57                 if($stmt->errno != 0){
  58                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B");
  59                 }
  60
  61                 $stmt->bind_result($uuid, $typeid, $desc, $cts, $mts, $data);
  62                 while($stmt->fetch()){
  63                         $uuid = strtolower($uuid);
  64                         echo('["');
  65                         echo(getFormedUUIDString($uuid));
  66                         echo('","');
  67                         echo(getFormedUUIDString($typeid));
  68                         echo('","');
  69                         echo($desc);
  70                         echo('",');
  71                         echo($cts);
  72                         echo(',');
  73                         echo($mts);
  74                         echo(',"');
  75                         echo($data);
  76                         echo('"]');
  77                         echo(PHP_EOL);
  78                 }
  79                 echo($stmt->num_rows);
  80                 $stmt->close();
  81                 exit(" OK");
  82         } else if(strcmp($action, 'viewallhtml') == 0){
  83                 $stmt = $db->prepare(QUERY_SELECT_ALL_MTAG);
  84                 $stmt->execute();
  85                 //
  86                 $stmt->store_result();
  87                 if($stmt->errno != 0){
  88                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B");
  89                 }
  90
  91                 $stmt->bind_result($uuid, $typeid, $desc, $cts, $mts, $data);
  92                 while($stmt->fetch()){
  93                         $uuid = strtolower($uuid);
  94                         echo('["');
  95                         echo(getFormedUUIDString($uuid));
  96                         echo('","');
  97                         echo(getFormedUUIDString($typeid));
  98                         echo('","');
  99                         echo($desc);
 100                         echo('",');
 101                         echo($cts);
 102                         echo(',');
 103                         echo($mts);
 104                         //echo(',"');
 105                         //echo($data);
 106                         //echo('"]');
 107                         echo("<br />");
 108                 }
 109                 echo($stmt->num_rows);
 110                 $stmt->close();
 111                 exit(" OK");
 112         } else if(strcmp($action, 'add') == 0){
 113                 if(isset($_GET['uuid'])){
 114                         $uuid = $_GET['uuid'];
 115                 } else{
 116                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B", "uuid needed.");
 117                 }
 118                 if(isset($_GET['typeid'])){
 119                         $typeid = $_GET['typeid'];
 120                 } else{
 121                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B", "typeid needed.");
 122                 }
 123                 if(isset($_GET['desc'])){
 124                         $desc = $_GET['desc'];
 125                 } else{
 126                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B", "desc needed.");
 127                 }
 128                 if(isset($_GET['data'])){
 129                         $data = $_GET['data'];
 130                 } else{
 131                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B", "data needed.");
 132                 }
 133                 $stmt = $db->prepare(QUERY_ADD_MTAG);
 134                 //$uuid = "12363456-96EC-4E56-BC1F-B58DD0A76161";
 135                 //$typeid = "12323456-96EC-4E36-BC1F-B58DD0A76161";
 136                 $mts = getTimeStampMs();
 137                 //$desc = "aiueo";
 138                 //$data = "[0]";
 139                 $stmt->bind_param(QUERY_ADD_MTAG_TYPES, $uuid, $typeid, $desc, $mts, $mts, $data);
 140                 $stmt->execute();
 141                 //
 142                 $stmt->store_result();
 143                 if($stmt->errno != 0){
 144                         responseError("A0518702-C90C-4785-B5EA-1A213DD0205B", mysqli_error($db));
 145                 }
 146                 $stmt->close();
 147                 exit("OK");
 148         }
 149 }
 150
 151 //NOP error
 152 responseError("B539657C-0FA6-49C2-AFB0-13AF5C7866ED");
 153
 154 function responseError($errid, $description = "")
 155 {
 156         die('["' . $errid .'","' . $description . '"]');
 157 }
 158
 159 function connectDB()
 160 {
 161         $db = new mysqli('localhost', DATABASE_USER, DATABASE_PWD, DATABASE_NAME);
 162
 163         if (mysqli_connect_error()) {
 164                 // DB connect error
 165                 responseError("3A8CF3C8-E6B6-4A99-9134-343CA341B591", mysqli_connect_error());
 166         }
 167
 168         // 文字化け防止
 169         $db->set_charset("utf8");
 170
 171         //データベース存在確認
 172         $stmt = $db->prepare("show tables");
 173         $stmt->execute();
 174         //
 175         $stmt->store_result();
 176         if($stmt->errno != 0){
 177                 responseError("80FA2D65-9473-40B0-A3CE-159AE8E67017");
 178         }
 179         //テーブルの存在確認
 180         $stmt->bind_result($tablename);
 181         $found = false;
 182         while($stmt->fetch()){
 183                 if($tablename == "MemoryTagRoot"){
 184                         $found = true;
 185                 }
 186         }
 187         if(!$found){
 188                 rebuildDB($db);
 189         }
 190         $stmt->close();
 191
 192         return $db;
 193 }
 194
 195 function rebuildDB($db)
 196 {
 197         //すでにあるテーブルの削除
 198         //MemoryTagRoot
 199         $stmt = $db->prepare("drop table if exists MemoryTagRoot");
 200         $stmt->execute();
 201         //エラーチェック省略
 202         $stmt->close();
 203
 204         //再構築
 205         $stmt = $db->prepare(QUERY_CREATE_TABLE_MEMORY_TAG_ROOT);
 206         $stmt->execute();
 207         //エラーチェック省略
 208         $stmt->close();
 209 }
 210
 211 function getFormedUUIDString($str)
 212 {
 213         $str = strtolower($str);
 214         return (
 215                 substr($str, 0, 8) . "-" .
 216                 substr($str, 8, 4) . "-" .
 217                 substr($str, 12, 4) . "-" .
 218                 substr($str, 16, 4) . "-" .
 219                 substr($str, 20, 12)
 220         );
 221 }
 222
 223 function getTimeStampMs()
 224 {
 225         return ceil(microtime(true)*1000);
 226 }
 227 ?>