MazeGame/sql.py at main · rhealgabxs/MazeGame · GitHub

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
"""
SQL
"""

# 新規DB作成時のCREATE TABLE
CREATE_TABLE = '''
CREATE TABLE player (
    id INTEGER PRIMARY KEY
  , name TEXT
  , seed INTEGER
  , floor INTEGER
  , x INTEGER
  , y INTEGER
  , direction INTEGER
);

CREATE TABLE count_key (
    id INTEGER PRIMARY KEY
  , key INTEGER
  , steps INTEGER
  , mapping INTEGER
  , map_all INTEGER
  , guide_up INTEGER
  , guide_down INTEGER
  , save INTEGER
  , load INTEGER
);

CREATE TABLE maze (
    id INTEGER
  , floor INTEGER
  , size_x INTEGER
  , size_y INTEGER
  , mapping BLOB
  , stairs_up_x INTEGER
  , stairs_up_y INTEGER
  , stairs_down_x INTEGER
  , stairs_down_y INTEGER
  , PRIMARY KEY(id, floor)
);
'''

# プレイヤー情報テーブルSQL
SELECT_PLAYER = '''
SELECT *
FROM player
WHERE id = :id
'''
INSERT_PLAYER = '''
INSERT INTO player
VALUES (
    :id
  , :name
  , :seed
  , :floor
  , :x
  , :y
  , :direction
)
'''
UPDATE_PLAYER = '''
UPDATE player
SET name = :name
  , seed = :seed
  , floor = :floor
  , x = :x
  , y = :y
  , direction = :direction
WHERE id = :id
'''

# キー回数テーブルSQL
SELECT_COUNT_KEY = '''
SELECT *
FROM count_key
WHERE id = :id
'''
INSERT_COUNT_KEY = '''
INSERT INTO count_key
VALUES (
    :id
  , :key
  , :steps
  , :mapping
  , :map_all
  , :guide_up
  , :guide_down
  , :save
  , :load
)
'''
UPDATE_COUNT_KEY = '''
UPDATE count_key
SET key = :key
  , steps = :steps
  , mapping = :mapping
  , map_all = :map_all
  , guide_up = :guide_up
  , guide_down = :guide_down
  , save = :save
  , load = :load
WHERE id = :id
'''

# 迷路関連テーブルSQL
# サイズ指定しているのは、異なる大きさのデータは読めないようにするため
SELECT_MAZE = '''
SELECT *
FROM maze
WHERE id = :id
  AND size_x = :size_x
  AND size_y = :size_y
ORDER BY floor
'''
SELECT_MAZE_BY_KEY = '''
SELECT *
FROM maze
WHERE id = :id
  AND floor = :floor
'''
INSERT_MAZE = '''
INSERT INTO maze
VALUES (
    :id
  , :floor
  , :size_x
  , :size_y
  , :mapping
  , :stairs_up_x
  , :stairs_up_y
  , :stairs_down_x
  , :stairs_down_y
)
'''
UPDATE_MAZE = '''
UPDATE maze
SET size_x = :size_x
  , size_y = :size_y
  , mapping = :mapping
  , stairs_up_x = :stairs_up_x
  , stairs_up_y = :stairs_up_y
  , stairs_down_x = :stairs_down_x
  , stairs_down_y = :stairs_down_y
WHERE id = :id
  AND floor = :floor
'''