-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase_util.php
378 lines (351 loc) · 16 KB
/
database_util.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
<?php
date_default_timezone_set('Asia/Shanghai');
$conn = connect_db('localhost', 'web_user', '');
function connect_db($host,$user_name,$password)
{
$conn = mysqli_connect($host, $user_name, $password);
if(!$conn) die("数据库服务器发生错误：".mysqli_connect_error());
mysqli_set_charset($conn,'utf8');
return $conn;
}
function create_db($conn, $name)
{
$sql = "CREATE DATABASE ".$name;
if(! mysqli_query($conn,$sql))
die("创建数据库失败：".mysqli_error($conn));
echo "数据库".$name."创建成功";
}
function delete_db($conn, $name)
{
$forbid = array("mysql","sys","information_schema","performance_schema"); // 禁止删除的数据库
foreach($forbid as $f) if(strcasecmp($name,$f)==0) die("禁止删除此数据库！");
$sql = "DROP DATABASE ".$name;
if(! mysqli_query($conn,$sql))
die("删除数据库失败：".mysqli_error($conn));
echo "数据库".$name."已删除";
}
function select_db($conn, $name)
{
if(! mysqli_select_db($conn,$name))
die("进入数据库失败：".mysqli_error($conn));
echo "当前数据库：".$name;
$_SESSION['use_db'] = $name;
}
function echo_table($val)
{
echo '<br />';
$first_in = 1;
$n = 0;
while($row = mysqli_fetch_array($val))
{
if($first_in)
{
$keys = array_keys($row);
$n = count($keys);
if($n == 0) break;
echo '<table border="1"><tr>';
for($i=1;$i<$n;$i+=2) echo '<td><b>'.$keys[$i].'</b></td>';
echo '</tr>';
$first_in = 0;
}
echo '<tr>';
for($i=1;$i<$n;$i+=2) echo '<td>'.$row[$keys[$i]].'</td>';
echo '</tr>';
}
if($n == 0) echo "No content.";
else echo '</table>';
}
function show_dbs($conn)
{
$sql = "SHOW DATABASES";
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("查询数据库失败：".mysqli_error($conn));
echo_table($retval);
}
function show_tbs($conn)
{
select_db($conn, $_SESSION['use_db']);
$sql = "SHOW TABLES";
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("查询数据表失败：".mysqli_error($conn));
echo_table($retval);
}
function show_tb($conn,$name)
{
select_db($conn, $_SESSION['use_db']);
$sql = "SELECT * FROM ".$name;
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("查询失败：".mysqli_error($conn));
echo "→".$name;
echo_table($retval);
}
function show_tb_attr($conn,$name)
{
select_db($conn, $_SESSION['use_db']);
$sql = "SHOW COLUMNS FROM ".$name;
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("查询失败：".mysqli_error($conn));
echo "→".$name;
echo_table($retval);
}
function execute_sql_debug($conn,$sql)
{
select_db($conn, $_SESSION['use_db']);
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("<br />语句执行错误：".mysqli_error($conn));
if(strcasecmp(substr($sql,0,6),"SELECT")==0)
{
$begin = stripos($sql,"FROM ") + 5;
$end = stripos($sql," ",$begin);
if(!$end) $end = strlen($sql);
$name = substr($sql,$begin,$end-$begin);
echo "→".$name;
echo_table($retval);
}
echo "<br />执行语句成功：".$sql;
}
function query_one($conn,$select,$from,$where_key,$where_value)
{ //调用此函数须确保查询的结果唯一
$sql = 'select '.$select.' from '.$from.' where '.$where_key.'='.$where_value;
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("<br />查询失败：".mysqli_error($conn));
$row = mysqli_fetch_array($retval);
if(!$row) return NULL;
else return $row[0];
}
function find($conn,$select,$from,$where_key,$where_value,$value)
{ //查找是否存在符合条件的值
$sql = 'select '.$select.' from '.$from.' where '.$where_key.'='.$where_value;
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("<br />查询失败：".mysqli_error($conn));
while($row = mysqli_fetch_array($retval))
{
if($value == $row[0]) return True;
}
return False;
}
function execute_sql($conn, $sql)
{
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("<br />语句执行错误：".mysqli_error($conn));
#echo "<br />执行语句成功：".$sql;
return $retval;
}
function build_web_database($conn)
{
// TODO: 主码，外码，check，自增等等
// 用户表：uid，账号，密码，邮箱，昵称，头像地址，权限(0游客/封禁，1用户，2管理员，3大老板)
$table = 'user_info(
user_id int auto_increment,
user_name varchar(32) unique,
user_pwd blob(128),
user_email varchar(32) unique,
user_nickname varchar(32),
user_headpic_url varchar(256),
user_permission int,
primary key (user_id)) ENGINE=InnoDB;';
execute_sql($conn, 'CREATE table if not exists sakura.'.$table);
$sql = 'ALTER table sakura.user_info CONVERT TO CHARACTER SET utf8';
execute_sql($conn, $sql);
$sql = "INSERT into sakura.user_info (user_name,user_pwd,user_email,user_nickname,user_permission)
values ('boss', PASSWORD('boss'), '[email protected]', '博士', 3),
('bokoblin', PASSWORD('bokoblin'), '[email protected]', '猪', 1),
('moblin', PASSWORD('moblin'), '[email protected]', '莫布林', 1),
('momo', PASSWORD('momo'), '[email protected]', '桃の花', 1),
('yuki', PASSWORD('yuki'), '[email protected]', '冻住不许走', 1),
('pikachu', PASSWORD('pikachu'), '[email protected]', '电气老鼠', 1),
('akie', PASSWORD('akie'), '[email protected]', 'Akie秋绘', 1),
('yousa', PASSWORD('yousa'), '[email protected]', '冷鸟yousa', 1),
('kirlia', PASSWORD('kirlia'), '[email protected]', 'Lovely', 1);";
execute_sql($conn, $sql);
// 版面表：bid（1为总版面），名称
$table = 'board(
board_id int auto_increment,
board_name varchar(32) unique,
primary key (board_id)) ENGINE=InnoDB;';
execute_sql($conn, 'CREATE table if not exists sakura.'.$table);
$sql = 'ALTER table sakura.board CONVERT TO CHARACTER SET utf8';
execute_sql($conn, $sql);
$sql = "INSERT into sakura.board (board_name)
value ('所有版面'), ('旷野之息'), ('宝可梦'), ('唱见'), ('痒痒鼠'), ('明日方舟');";
execute_sql($conn, $sql);
// 版面管理表：bid（1为总版面），管理员(uid)
$table = 'manage(
bid int,
uid int,
foreign key(bid) references sakura.board(board_id) on delete cascade,
foreign key(uid) references sakura.user_info(user_id) on delete cascade,
constraint unique_cond UNIQUE (bid,uid)) ENGINE=InnoDB;';
execute_sql($conn, 'CREATE table if not exists sakura.'.$table);
$sql = 'ALTER table sakura.board CONVERT TO CHARACTER SET utf8';
execute_sql($conn, $sql);
$sql = "INSERT into sakura.manage (bid,uid)
value (1,1), (2,2), (3,6), (4,8), (5,4), (6,7)";
execute_sql($conn, $sql);
// 帖子表：pid，标题，所属版面，发帖用户，创建时间，更新时间(最迟回复时间)，
// 内容(检测越界，内容过多则用文件储存，数据库放文件路径)，
// 状态(1正常，2违规锁定，3不可回复, 4置顶可回复，5置顶不可回复)
$table = 'posts(
post_id int auto_increment,
post_title varchar(128),
post_bid int,
post_uid int,
post_createtime bigint,
post_updatetime bigint,
post_content varchar(16384),
post_state int,
primary key (post_id),
foreign key(post_bid) references sakura.board(board_id) on delete cascade,
foreign key(post_uid) references sakura.user_info(user_id) on delete cascade) ENGINE=InnoDB;';
execute_sql($conn, 'CREATE table if not exists sakura.'.$table);
$sql = 'ALTER table sakura.posts CONVERT TO CHARACTER SET utf8';
execute_sql($conn, $sql);
$sql = "INSERT into sakura.posts (post_title, post_bid, post_uid, post_createtime, post_updatetime, post_content, post_state)
values ('净网倡议', 1, 1, 10000000, 10000000, '大家注意下别搞黄色。', 4),
('别再欺负猪猪了！', 2, 2, 10001234, 10001234, '猪猪活得很艰难，猪猪心很累。', 1),
('有能耐把我删了', 3, 6, 10005000, 10005000, '听说剑盾要删一批幸运儿么，不知道有没有我，好紧张啊(=^ ^=)', 1),
('吹爆绘总', 4, 8, 10010010, 10010010, '啊绘总唱歌太好听了，我冷鸟吹爆！', 1),
('莫布林的水帖', 2, 3, 10017000, 10017000, '刚好五个字', 1);";
execute_sql($conn, $sql);
// 回复表：rid，发回复的用户，所属帖子，回复时间，
// 内容(检测越界，内容过多则用文件储存，数据库放文件路径)，状态(1正常，2违规)
$table = 'reply(
reply_id int auto_increment,
reply_uid int,
reply_pid int,
reply_createtime bigint,
reply_content varchar(16384),
reply_state int,
primary key (reply_id),
foreign key(reply_pid) references sakura.posts(post_id) on delete cascade,
foreign key(reply_uid) references sakura.user_info(user_id) on delete cascade) ENGINE=InnoDB;';
execute_sql($conn, 'CREATE table if not exists sakura.'.$table);
$sql = 'ALTER table sakura.reply CONVERT TO CHARACTER SET utf8';
execute_sql($conn, $sql);
$sql = "INSERT into sakura.reply (reply_uid, reply_pid, reply_createtime, reply_content, reply_state)
values (7, 4, 10012000, '啊谢谢冷鸟捧场w', 1),
(9, 5, 10018888, '我也水水经验', 1),
(2, 5, 10020000, '每日水一水', 1);";
execute_sql($conn, $sql);
// 私信
// state 0=正常 1=sender删除 2=receiver删除 3=双方删除
$table = 'message(
msg_id int auto_increment,
msg_sender int,
msg_receiver int,
msg_time bigint,
msg_content varchar(1024),
msg_state int,
primary key (msg_id),
foreign key (msg_sender) references sakura.user_info(user_id) on delete cascade,
foreign key (msg_receiver) references sakura.user_info(user_id) on delete cascade)';
execute_sql($conn, 'CREATE table sakura.'.$table);
$sql = 'ALTER table sakura.message CONVERT TO CHARACTER SET utf8';
execute_sql($conn, $sql);
$sql = "INSERT into sakura.message
(msg_sender,msg_receiver,msg_time,msg_content,msg_state)
values (1, 2, 100022, 'hello', 0),
(2, 1, 100031, 'hi. how are you?', 0),
(1, 2, 100045, 'i\'m fine, and you?', 0),
(2, 1, 100062, 'i\'m die.', 0),
(3, 1, 123456, 'awsl', 0),
(4, 5, 130000, 'yukiちゃん，私信功能上线了诶', 0),
(5, 4, 130014, '是啊momoちゃん，好像挺好用呢', 0),
(5, 4, 130028, '我们不如来测试测试bug吧(\#^.^#)', 0),
(4, 5, 130035, '好啊！', 0),
(4, 5, 130049, '先测试看看两个人同时发消息会是什么情况吧~', 0),
(5, 4, 130063, 'ok，那么就从我下一条消息之后数7秒，一起发一条测试吧？', 0),
(4, 5, 130070, '好', 0),
(5, 4, 130077, '开始', 0),
(5, 4, 130084, 'test', 0),
(4, 5, 130084, 'TEST', 0),
(4, 5, 130091, '然后换我，从我下一条开始数7秒一起发', 0),
(5, 4, 130098, '行', 0),
(4, 5, 130105, '开始', 0),
(4, 5, 130112, 'TEST', 0),
(5, 4, 130112, 'test', 0),
(5, 4, 130119, '看起来比较随机...', 0),
(4, 5, 130133, '嗯。。好我们再测试看看如果是很长的文本的话，对话框会不会爆炸吧==', 0),
(5, 4, 130147, '嗯试试看。\n这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本这是很长的文本\n这是好长好长的文本啊！！', 0),
(4, 5, 130154, '=2-lpgl323=-fpl=l=-dkv=-o,\'23n4ri0ij0\'\\2340m0vm\\\)qwoimrifv=qq\\/s,ospmk20o34-it,-gi-3i4-f-03,-i2-3f-\-0v-033423r23r090<>?\"][[2p=p230f129r123i4\`~2\|3-fmvng0vj=3r0-i1-fk-ovmomefim20icma//./,./,{}::[]]fl,-vr-cmnv=1=0jf0soc,;03cr0nvminb-1n-ifnnvefinnsnniomoomosom\n按法律卡死了可麻烦了开幕式代理费目前我离开你过来口令卡萌沙拉咔玫琳凯马萨莱卡棉\nおさかなのりアップデエルアーダ伝えてアシスタン葵葵補遺おさ￥おいいジャパンネット出掛ける飲まない了解しました', 0),
(4, 5, 130161, '好像完全没有问题！', 0),
(5, 4, 130168, '是啊，甚至连对齐和换行都没有出问题呢！', 0),
(4, 5, 130175, '这样优秀的作业，我想，给个满分都不过分吧？', 0),
(5, 4, 130182, '同意！满分满分~', 0),
(4, 5, 130196, '现在才1970年，离作业ddl还有50年呢^_^我先去睡一会啦', 0),
(5, 4, 130210, '嗯嗯我也先睡了，おやすみなさい～', 0),
(4, 5, 130217, 'おやすみ～', 0);";
execute_sql($conn, $sql);
}
function build_database_user($conn)
{
$sql = "CREATE USER 'web_user'@'localhost' ";
execute_sql($conn, $sql);
$sql = "GRANT select,insert,delete,update ON sakura.* TO 'web_user'@'localhost'";
execute_sql($conn, $sql);
}
function check_usrpsw($conn,$name,$psw)
{ //检查用户账号密码是否正确，返回uid
$sql = 'SELECT user_id FROM sakura.user_info WHERE user_name="'.$name.'"'
. 'AND user_pwd=PASSWORD("'.$psw.'")';
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("<br />发生错误：".mysqli_error($conn));
$row = mysqli_fetch_array($retval);
if(!$row) return NULL;
else return $row[0];
}
function init($conn)
{
execute_sql($conn, 'DROP database if exists sakura');
execute_sql($conn, 'CREATE database sakura');
build_web_database($conn);
$_SESSION['uid'] = 0;
}
function check_board_manager($conn,$bid)
{
if(!find($conn,'uid','sakura.manage','bid','1',$_SESSION['uid']) &&
!find($conn,'uid','sakura.manage','bid',$bid,$_SESSION['uid']))
return FALSE;
else return TRUE;
}
function query_num($conn,$from,$where)
{ //调用此函数须确保查询的结果唯一
$sql = 'select count(*) from '.$from.' where '.$where;
$retval = mysqli_query($conn,$sql);
if(! $retval)
die("<br />查询失败：".mysqli_error($conn));
$row = mysqli_fetch_array($retval);
if(!$row) return NULL;
else return $row[0];
}
function echo_msg_item($row)
{
# msg_id, msg_sender, msg_receiver, msg_time, msg_content #
echo "<div style='min-height: 48px; padding: 0 16px 16px;'>";
if ($row[1] == $_SESSION['uid'])
{
$float = 'right';
$color = 'green';
}
else
{
$float = 'left';
$color = 'black';
}
$msg = "<p style='text-align: $float; margin: 0px; color: $color;'>". date('Y-m-d H:i:s', $row[3]) . '</p>' .
"<p style='float: $float; margin: 0px;'>". $row[4] . "</p>";
echo "<div style='float: $float; max-width: 80%;'>".
$msg. "</div>";
echo "<div style='clear: both; height: 0;'></div>";
echo "</div>";
}
?>