7 define("DATABASE_USER", "pcd2013devUser");
\r
9 define("DATABASE_PWD", "MoonStone");
\r
11 define("DATABASE_NAME", "pcd2013dev");
\r
14 define("QUERY_CREATE_authUserTable", "");
\r
15 define("QUERY_CREATE_stageObjectTable", "");
\r
17 //SQL文 for authUserTable
\r
18 define("QUERY_SELECT_USER_ID", "SELECT id from authUserTable where name = ?");
\r
19 define("QUERY_SELECT_USER_ID_TYPES", "s");
\r
20 define("QUERY_SELECT_USER_STAGE", "SELECT userStageName from authUserTable where id=?");
\r
21 define("QUERY_SELECT_USER_STAGE_TYPES", "i");
\r
22 define("QUERY_SELECT_USER_STAGE_AND_TIMESTAMP", "SELECT userStageName, lastResponseTimestamp from authUserTable where id=?");
\r
23 define("QUERY_SELECT_USER_STAGE_AND_TIMESTAMP_TYPES", "i");
\r
24 define("QUERY_ADD_USER", "insert into authUserTable (name, userStageName) values (?, ?)");
\r
25 define("QUERY_ADD_USER_TYPES", "ss");
\r
26 define("QUERY_UPDATE_USER_STAGE", "UPDATE authUserTable SET userStageName=? WHERE id=?");
\r
27 define("QUERY_UPDATE_USER_STAGE_TYPES", "si");
\r
28 define("QUERY_UPDATE_USER_TIMESTAMP", "UPDATE authUserTable SET lastResponseTimestamp=? WHERE id=?");
\r
29 define("QUERY_UPDATE_USER_TIMESTAMP_TYPES", "ii");
\r
30 define("QUERY_SELECT_USER_TIMESTAMP", "SELECT lastResponseTimestamp from authUserTable where id = ?");
\r
31 define("QUERY_SELECT_USER_TIMESTAMP_TYPES", "i");
\r
32 define("QUERY_DELETE_OFFLINE_USER", "DELETE from authUserTable WHERE lastResponseTimestamp<=?");
\r
33 define("QUERY_DELETE_OFFLINE_USER_TYPES", "i");
\r
34 define("QUERY_SELECT_USER_CCLASS", "SELECT userCharacterBaseClass from authUserTable where id=?");
\r
35 define("QUERY_SELECT_USER_CCLASS_TYPES", "i");
\r
36 define("QUERY_UPDATE_USER_CCLASS", "UPDATE authUserTable SET userCharacterBaseClass=? where id=?");
\r
37 define("QUERY_UPDATE_USER_CCLASS_TYPES", "si");
\r
38 //SQL文 for stageObjectTable
\r
39 define("QUERY_ADD_OBJECT", "insert into stageObjectTable (locationX, locationY, velocityX, velocityY, stageName, className, objectAttribute, constructorArgs, ownerUserID, modifiedTimestamp, addedTimestamp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
\r
40 define("QUERY_ADD_OBJECT_TYPES", "ddddssssiii");
\r
41 define("QUERY_DELETE_USER_OBJECT_ALL", "delete from stageObjectTable where ownerUserID=?");
\r
42 define("QUERY_DELETE_USER_OBJECT_ALL_TYPES", "i");
\r
43 define("QUERY_SELECT_ADDED_OBJECT", "SELECT id, locationX, locationY, velocityX, velocityY, className, objectAttribute, constructorArgs, ownerUserID FROM stageObjectTable WHERE stageName=? AND ownerUserID<>? AND addedTimestamp>? AND objectAttribute<>'Deleted'");
\r
44 define("QUERY_SELECT_ADDED_OBJECT_TYPES", "sii");
\r
45 define("QUERY_SELECT_UPDATED_OBJECT", "SELECT id, locationX, locationY, velocityX, velocityY, className, objectAttribute FROM stageObjectTable WHERE stageName=? AND ownerUserID<>? AND modifiedTimestamp>=? AND addedTimestamp<? AND objectAttribute<>'Deleted'");
\r
46 define("QUERY_SELECT_UPDATED_OBJECT_TYPES", "siii");
\r
47 define("QUERY_SELECT_DELETED_OBJECT", "SELECT id, locationX, locationY, velocityX, velocityY, className, objectAttribute FROM stageObjectTable WHERE stageName=? AND ownerUserID<>? AND modifiedTimestamp>=? AND objectAttribute='Deleted'");
\r
48 define("QUERY_SELECT_DELETED_OBJECT_TYPES", "sii");
\r
49 define("QUERY_UPDATE_STAGE_OBJECT", "UPDATE stageObjectTable SET locationX=?, locationY=?, velocityX=?, velocityY=?, objectAttribute=?, modifiedTimestamp=? WHERE stageName=? AND id=? AND objectAttribute<>'Deleted'");
\r
50 define("QUERY_UPDATE_STAGE_OBJECT_TYPES", "ddddsisi");
\r
51 define("QUERY_SELECT_ALL_OTHER_USER_OBJECT", "SELECT id, locationX, locationY, velocityX, velocityY, className, objectAttribute, constructorArgs, ownerUserID FROM stageObjectTable WHERE stageName=? AND ownerUserID<>? AND modifiedTimestamp>? AND objectAttribute<>'Deleted'");
\r
52 define("QUERY_SELECT_ALL_OTHER_USER_OBJECT_TYPES", "sii");
\r
53 define("QUERY_CHECK_AND_SET_DELETED_OBJECT", "UPDATE stageObjectTable SET objectAttribute='Deleted', modifiedTimestamp=? WHERE modifiedTimestamp<=? AND objectAttribute<>'Deleted'");
\r
54 define("QUERY_CHECK_AND_SET_DELETED_OBJECT_TYPES", "ii");
\r
55 define("QUERY_DELETE_OFFLINE_OBJECT", "DELETE from stageObjectTable WHERE modifiedTimestamp<=?");
\r
56 define("QUERY_DELETE_OFFLINE_OBJECT_TYPES", "i");
\r
58 define("DEFAULT_START_STAGE", "cselect");
\r
61 define("OBJECT_ALIVE_TIME_MS", 2000);
\r
62 define("OBJECT_DELETE_TIME_MS", 30000);
\r
63 define("USER_ALIVE_TIME_MS", 2000);
\r
64 define("USER_DELETE_TIME_MS", 180000);
\r
66 function escapeMySQLArgumentString(&$str)
\r
68 $str = str_replace("\\", "", $str);
\r
69 $str = str_replace("\"", "", $str);
\r
70 $str = str_replace("'", "", $str);
\r
71 $str = str_replace("¥", "", $str);
\r
72 $str = str_replace("|", "", $str);
\r
75 function updateUserTimestamp($db, $userID)
\r
77 $stmt = $db->prepare(QUERY_UPDATE_USER_TIMESTAMP);
\r
78 $stmt->bind_param(QUERY_UPDATE_USER_TIMESTAMP_TYPES, getTimeStampMs(), $userID);
\r
84 function getUserLastResponseTimestamp($db, $userID)
\r
87 $stmt = $db->prepare(QUERY_SELECT_USER_TIMESTAMP);
\r
88 $stmt->bind_param(QUERY_SELECT_USER_TIMESTAMP_TYPES, $userID);
\r
90 if($stmt->errno == 0){
\r
92 $stmt->store_result();
\r
94 if($stmt->num_rows != 0){
\r
96 $stmt->bind_result($ts);
\r
104 function getTimeStampMs()
\r
106 return ceil(microtime(true)*1000);
\r