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
|
# -*- encoding: utf-8 -*-
"""
sqlite3 database module for MURSAT1 Mission Dashboard
"""
import sqlite3
class Db:
def __init__ (self):
self.conn = sqlite3.connect ('/usr/local/mmd/db/mmd.db')
self.conn.text_factory = str
self.conn.row_factory = sqlite3.Row
self.cursor = self.conn.cursor ()
self.cursor.execute ('PRAGMA foreign_keys = ON')
self.conn.commit ()
def sessionFind (self, session_id):
self.cursor.execute ('SELECT * FROM session WHERE id=?', (session_id,))
return self.cursor.fetchone ()
def sessionInit (self, session_id, email, expires, renewal, status):
try:
self.cursor.execute ('INSERT INTO session (id, email, expires, renewal, status) VALUES (?,?,?,?,?)', (session_id, email, expires, renewal, status,))
self.conn.commit ()
return True
except sqlite3.IntegrityError:
return False
def sessionDelete (self, session_id):
self.cursor.execute ('DELETE FROM session WHERE id=?', (session_id,))
self.conn.commit ()
def sessionUpdate (self, session_id, expires):
self.cursor.execute ('UPDATE session SET expires=? WHERE id=?', (expires, session_id,))
self.conn.commit ()
def sessionRenew (self, session_id, expires, renewal, token):
self.cursor.execute ('UPDATE session SET expires=?,renewal=?,id=? WHERE id=?', (expires, renewal, token, session_id,))
self.conn.commit ()
def sessionSetEmail (self, session_id, email):
self.cursor.execute ('UPDATE session SET email=? WHERE id=?', (email, session_id))
self.conn.commit ()
def sessionSetStatus (self, session_id, status):
self.cursor.execute ('UPDATE session SET status=? WHERE id=?', (status, session_id))
self.conn.commit ()
def sessionDeleteExpired (self, expires):
self.cursor.execute ('DELETE FROM session WHERE expires<?', (expires,))
self.conn.commit ()
return self.cursor.rowcount
#
# user related methods
#
def userCreate (self, user_id, firstname, lastname, email, callsign, password, code, regtimeout):
try:
self.cursor.execute ('INSERT INTO user (id, firstname, lastname, email, callsign, password, code, regtimeout) VALUES (?,?,?,?,?,?,?,?)',
(user_id, firstname, lastname, email, callsign, password, code, regtimeout))
self.conn.commit ()
return True
except sqlite3.IntegrityError:
return False
def userConfirm (self, code):
self.cursor.execute ('UPDATE user SET status="valid" WHERE code=?', (code, ))
self.conn.commit ()
def userCancel (self, code):
self.cursor.execute ('DELETE FROM user WHERE code=?', (code, ))
self.conn.commit ()
def userDelete (self, user_id):
self.cursor.execute ('DELETE FROM user WHERE id=?', (user_id,))
self.conn.commit ()
def userFindId (self, user_id):
self.cursor.execute ('SELECT * FROM user WHERE id=?', (user_id,))
return self.cursor.fetchone ()
def userFindEmail (self, email):
self.cursor.execute ('SELECT * FROM user WHERE email=?', (email,))
return self.cursor.fetchone ()
def userFindPending (self, code):
self.cursor.execute ('SELECT * FROM user WHERE code=? and status="pending"', (code,))
return self.cursor.fetchone ()
# location related methods
def locationCreate (self, location_id, name, longitude, latitude, user_id, is_default):
try:
self.cursor.execute ('INSERT INTO location (id, name, longitude, latitude, user_id, is_default) VALUES (?,?,?,?,?,?)', (location_id, name, longitude, latitude, user_id, is_default))
self.conn.commit ()
return True
except sqlite3.IntegrityError:
return False
def locationFindUserId (self, user_id):
self.cursor.execute ('SELECT * FROM location WHERE user_id=?', (user_id,))
return self.cursor.fetchone ()
def locationListByUserId (self, user_id):
self.cursor.execute ('SELECT id FROM location WHERE user_id=?', (user_id,))
return self.cursor.fetchall ()
def locationFindId (self, location_id):
self.cursor.execute ('SELECT * FROM location WHERE id=?', (location_id,))
return self.cursor.fetchone ()
def locationDeleteId (self, location_id):
self.cursor.execute ('DELETE FROM location WHERE id=?', (location_id,))
self.conn.commit ()
# satellite methods
def satelliteGetLatestSSP (self):
self.cursor.execute ('SELECT * FROM ssps ORDER BY timestamp DESC')
return self.cursor.fetchone ()
def satelliteInsertNewSSP (self, timestamp, longitude, latitude):
self.cursor.execute ('INSERT INTO ssps (timestamp, longitude, latitude) VALUES (?,?,?)', (timestamp, longitude, latitude,))
self.conn.commit ()
def close (self):
self.cursor.close ()
self.conn.close ()
if __name__ == "__main__":
db = Db ()
print db.sessionFind ('1234567890')
db.sessionInit ('1234567890', 'jogi@mur.at', '1')
data = db.sessionFind ('1234567890')
print data['id'], data['email']
db.sessionDelete ('1234567890')
print db.sessionFind ('1234567890')
print db.userFindId ('1')
db.userCreate ('1', 'Jogi', 'Hofmüller', 'jogi@mur.at', '', '', '', '')
print db.userFindId ('1')
db.userDelete ('1')
# vim: tw=0 ts=2 expandtab
# EOF
|
