/
pysql.py
281 lines (252 loc) · 11.1 KB
/
pysql.py
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
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# primary key shouldnt set twice times
# default(DB) -> Dir(Relation, ex.STUDENT is a relation, STUDENT.conf is the relation attribute setting config file)
# And STUDENT_DB is the table we set for demo from STUDENT relation, the column attribute follow the conf file
# if user dont use create database, then the DB default setting is in default Dir
import cmd
import getpass
import os
import re
import sys
# the module defined by myself
import DBMS
import File
#change the command line to array
def str2List(str):
fromIndex = str.find('from')
if fromIndex >= 0: str = str[:fromIndex].replace(' ','') + ' ' + str[fromIndex:]
return str.split()
#main function of PySQL
class PySQL(cmd.Cmd):
def __init__(self):
cmd.Cmd.__init__(self)
self.prompt = 'pysql>>> '
self.intro = "****** Chris Hsu own SQL DB for DBMS project by Python ******"
self.authority = 'user'
#see the command line u have typed in
def do_hist(self, args):
"""Print a list of commands that have been entered"""
print self._hist
#shortcut to enter admin
def do_root(self, args):
self.prompt = 'chrisql>>> '
self.authority = 'admin'
#sign up for PySQL
def do_register(self, args):
password = getpass.getpass(prompt='type in your password:')
repassword = getpass.getpass(prompt='confirm your password:')
if password == repassword:
File.appendNewRecord(None, './dbConfig/useradmin', password+'\n')
print 'Register to PySQL suceessfully!!'
else:
print '[Error] the password you tyoe in are not the same :( Plz try again.'
def do_login(self, args):
password = getpass.getpass()
if DBMS.isAdmin(password):
self.prompt = 'chrisql>>> '
self.authority = 'admin'
print 'you are boss in PySQL!!'
elif DBMS.isUser(password):
self.authority = 'user_admin'
print 'hello, visiter, wellcome to PySQL :)'
else: print '[Error] Wrong Password :( Plz try again.'
def do_logout(self, args):
print "logout"
if self.authority == 'admin' or self.authority == 'user_admin':
self.prompt = 'pysql>>> '
self.authority = 'user'
def do_define(self, args):
if self.authority == 'admin':
args = args.split()[0].lower() + ' ' + args.split()[1]
match = re.search('relation\s*\w*', args)
if match is None: print '[Syntax Error] try "define --help" or "deifne -h" to get help.'
else:
newTableName = match.group().split()[1]
if not os.path.exists('./DB/default/' + newTableName): os.mkdir( './DB/default/' + newTableName )
else: print '[Setting Error] The Relation already exists. Plz new another relation.'
self.tableConfPath = './DB/default/'+newTableName+'/'+newTableName+'.conf'
elif self.authority == 'user_admin': print '[Error] authority not enough.'
else:
print '[Error] authority not enough.'
def do_DEFINE(self, args):
self.do_define( args )
#primary key & column name judge
def do_set(self, args):
if self.authority == 'admin':
match = re.search('attribute\s*\w*', args)
if match is None:
if re.search('primary\s*key\s*\w*', args):
argList = str2List(args)
columnName = argList.pop()
tableConfContent, tablePrimaryKeyIndex = File.findContentAndPrimaryKeyIndex(self.tableConf, self.tableConfPath, columnName)
if tablePrimaryKeyIndex >= 0:
File.setPrimaryKeyColumn(self.tableConf, self.tableConfPath, tableConfContent, tablePrimaryKeyIndex)
self.primaryKeyExist = True
elif tablePrimaryKeyIndex == -999:
print '[Setting Error] Primary key already set.'
else:
print '[Setting Error] No such column for primary key setting, try "set --help" or "set -h" to get help.'
else: print '[Syntax Error] try "set --help" or "set -h" to get help.'
else:
tableConfContent = File.findContentAndPrimaryKeyIndex(None, self.tableConfPath, '')[0]
DBMS.setRelation(match, str2List(args), self.tableConfPath, tableConfContent)
else:
print '[Error] authority not enough.'
def do_SET(self, args):
self.do_set( args )
#create the table folder to save record
def do_create(self, args):
argList = args.split()
if argList[0] == 'table' or argList[0] == 'TABLE':
if self.authority == 'admin':
tmpDBName = './DB/default/'
tmpTableConfName = tmpDBName + argList[1] + '/' + argList[1] + '.conf'
if os.path.exists(tmpTableConfName):
self.selectDB = tmpDBName
tmpTableConf = open(tmpTableConfName, 'r')
if tmpTableConf.read().find('*') >= 0 or self.primaryKeyExist:
tmpTableConf.close()
os.mkdir('./DB/default/' + argList[1] + '/' + argList[2] + '/')
else: '[Setting Error] No column was set as primary key, try "set --help" or "set -h" to get help.'
else: print '[Relation Error] There is no such relation. Plz try "set --help" or "set -h" to get help.'
else: print '[Error] authority not enough.'
else: print '[Syntax Error] try "create table --help" or "create table -h" to get help.'
def do_CREATE(self, args):
self.do_create( args )
#insert record for table
def do_insert(self, args):
argList = args.split()
if self.authority == 'admin':
if self.selectDB is not None:
selectTablePath, selectRecordPath = DBMS.findTableAndRecordPath(self.selectDB ,argList)
if selectTablePath is not None:
if 'values' in argList: argList.remove('values')
if DBMS.MatchTableSetting(selectTablePath, argList, 'insert'):
DBMS.insertTable(selectTablePath, argList)
else: print '[Insert Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.'
else: print '[Error] authority not enough.'
def do_INSERT(self, args):
self.do_insert( args )
#can show columns/DBs/tables
def do_show(self, args): #show all DB/tables
argList = str2List(args)
if args == 'databases' or args == 'DATABASES':
print "###################"
for DB in os.listdir('./DB'):
print DB
print "###################"
elif args == 'tables' or args == 'TABLES':
print "###################"
for table in os.listdir('./DB/default'):
print table
print "###################"
elif argList[1] == 'columns' or argList[1] == 'COLUMNS':
print "###################"
if self.selectDB is not None:
selectTablePath = DBMS.findTableAndRecordPath(self.selectDB, argList)[0]
Column2IndexDict = DBMS.getAllColumn2IndexDict(selectTablePath)
for column in Column2IndexDict.keys():
print column
print "###################"
def do_SHOW(self, args):
self.do_show( args )
#choose which databases u want
def do_use(self, args): #assign which DB to use
if args in DBMS.findAllDBs(): self.selectDB = './DB/' + args + '/'
else: print '[Use Error] There is no such DB, check again!!'
def do_USE(self, args):
self.do_use( args )
#following detail, plz see the DBMS.py file
def do_select(self, args):
argList = str2List(args)
if self.selectDB is not None:
selectTablePath = DBMS.findTableAndRecordPath(self.selectDB ,argList)[0]
if selectTablePath is not None:
DBMS.selectRecords(selectTablePath, argList)
else: print '[Select Error] The table does not exist. Plz try "use --help" or "use -h" to get help.'
else: print '[Select Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.'
def do_SELECT(self, args):
self.do_select( args )
def do_update(self, args):
argList = str2List(args)
if self.authority == 'admin':
if self.selectDB is not None:
selectTablePath, selectRecordPath = DBMS.findTableAndRecordPath(self.selectDB ,argList)
if selectTablePath is not None:
if selectRecordPath is not None:
if DBMS.MatchTableSetting(selectTablePath, argList, 'update'):
DBMS.updateRecord(selectTablePath, selectRecordPath, argList)
else: print '[Update Error] The record does not exist. Plz try "use --help" or "use -h" to get help.'
else: print '[Update Error] The table does not exist. Plz try "use --help" or "use -h" to get help.'
else: print '[Update Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.'
else: print '[Error] authority not enough.'
def do_UPDATE(self, args):
self.do_update( args )
def do_delete(self, args):
argList = str2List(args)
if self.authority == 'admin':
if self.selectDB is not None:
selectTablePath, selectRecordPath = DBMS.findTableAndRecordPath(self.selectDB ,argList)
if selectTablePath is not None:
if selectRecordPath is not None:
DBMS.deleteRecord(selectRecordPath)
else: print '[Delete Error] The record does not exist. Plz try "use --help" or "use -h" to get help.'
else: print '[Delete Error] The table does not exist. Plz try "use --help" or "use -h" to get help.'
else: print '[Delete Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.'
else: print '[Error] authority not enough.'
def do_DELETE(self, args):
self.do_delete( args )
#deal with the empty input
def emptyline(self):
"""Do nothing on empty input line"""
pass
## Override methods in Cmd object ##
def preloop(self):
"""Initialization before prompting user for commands.
Despite the claims in the Cmd documentaion, Cmd.preloop() is not a stub.
"""
cmd.Cmd.preloop(self) ## sets up command completion
self._hist = [] ## No history yet
self._locals = {} ## Initialize execution namespace for user
self._globals = {}
self.cmd = None
self.tableConf = None
# self.selectRelation = None
self.selectDB = None
self.primaryKeyExist = False
def postloop(self):
"""Take care of any unfinished business.
Despite the claims in the Cmd documentaion, Cmd.postloop() is not a stub.
"""
cmd.Cmd.postloop(self) ## Clean up command completion
print "Exiting PySQL..."
#excute before the cmd execution
def precmd(self, line):
""" This method is called after the line has been input but before
it has been interpreted. If you want to modifdy the input line
before execution (for example, variable substitution) do it here.
"""
if line.strip() != '': self.cmd = line.strip().split()[0]
self._hist += [ line.strip() ]
if self.cmd == 'exit':
self.do_exit(line)
elif self.cmd == 'hist':
self._hist.pop()
elif len(self._hist) >= 2:
if (self._hist[len(self._hist)-2].find('define') >= 0) and (self.cmd != 'set'):
if self.authority == 'admin':
print "[Error] Plz finish relation setting first."
self._hist.pop()
line = ''
return line
#execute after cmd execution
def postcmd(self, stop, line):
"""If you want to stop the console, return something that evaluates to true.
If you want to do some post command processing, do it here.
"""
return stop
def do_exit(self, args):
return True
if __name__ == '__main__':
PySQL().cmdloop()