1import os 2import errno 3import sqlite3 4import logging 5from . import __version__ 6 7 8def dict_factory(cursor, row): 9 d = {} 10 for idx, col in enumerate(cursor.description): 11 d[col[0]] = row[idx] 12 return d 13 14 15class Database(object): 16 def __init__(self, filename): 17 # create the directory if it does not exist 18 db_dir = os.path.dirname(filename) 19 try: 20 os.makedirs(db_dir) 21 except OSError as e: 22 if e.errno != errno.EEXIST: 23 raise 24 else: 25 logging.info("Created directory %s" % db_dir) 26 27 self.__conn = sqlite3.connect(filename, check_same_thread=False) 28 self.__conn.row_factory = dict_factory 29 30 # update table for version and any schema updates 31 self.update() 32 # create new tables if they doesn't exist 33 self.create(filename) 34 35 def update(self): 36 c = self.__conn.cursor() 37 # Check if we have a version table. 38 try: 39 c.execute('SELECT version FROM Version') 40 result = c.fetchone() 41 # Add autoconnect to profile before 0.1.6 42 if result['version'] < '0.1.6': 43 c.execute('ALTER TABLE profile ADD COLUMN autoconnect INTEGER DEFAULT 0') 44 except sqlite3.Error: 45 pass 46 47 try: 48 c.execute('UPDATE Version SET version=?', __version__) 49 except sqlite3.Error: 50 pass 51 52 def create(self, filename): 53 c = self.__conn.cursor() 54 # Check if we have a version table. If not, then the scheme is too old and needs updating 55 try: 56 c.execute('SELECT version FROM Version') 57 except sqlite3.Error: 58 # We need to drop old table before this new schema starting with version 0.1.5 59 try: 60 c.execute('DROP TABLE custom') 61 except sqlite3.Error: 62 pass 63 c.execute('CREATE TABLE Version (version TEXT)') 64 c.execute('INSERT INTO Version (version) values(?)', (__version__,)) 65 66 c.execute('CREATE TABLE IF NOT EXISTS ' 67 'driver (id INTEGER PRIMARY KEY AUTOINCREMENT,' 68 'label TEXT, profile INTEGER)') 69 # JM 2018-07-23: Adding custom drivers table 70 c.execute('CREATE TABLE IF NOT EXISTS ' 71 'custom (id INTEGER PRIMARY KEY AUTOINCREMENT,' 72 'label TEXT UNIQUE, name TEXT, family TEXT, exec TEXT, version TEXT)') 73 # JM 2018-07-23: Renaming custom drivers to remote since this is what they really are. 74 c.execute('CREATE TABLE IF NOT EXISTS ' 75 'remote (id INTEGER PRIMARY KEY AUTOINCREMENT,' 76 'drivers TEXT, profile INTEGER)') 77 c.execute('CREATE TABLE IF NOT EXISTS ' 78 'profile (id INTEGER PRIMARY KEY AUTOINCREMENT,' 79 'name TEXT UNIQUE, port INTEGER DEFAULT 7624, ' 80 'autostart INTEGER DEFAULT 0, ' 81 'autoconnect INTEGER DEFAULT 0)') 82 c.execute('UPDATE Version SET version=?', (__version__,)) 83 84 self.__conn.commit() 85 86 c.execute('SELECT id FROM profile') 87 if not c.fetchone(): 88 c.execute('INSERT INTO profile (name) VALUES ("Simulators")') 89 c.execute('INSERT INTO driver (profile, label) VALUES (1, "Telescope Simulator")') 90 c.execute('INSERT INTO driver (profile, label) VALUES (1, "CCD Simulator")') 91 c.execute('INSERT INTO driver (profile, label) VALUES (1, "Focuser Simulator")') 92 self.__conn.commit() 93 c.close() 94 95 def get_autoprofile(self): 96 """Get auto start profile""" 97 98 cursor = self.__conn.execute('SELECT profile FROM autostart') 99 result = cursor.fetchone() 100 return result['profile'] if result else '' 101 102 def get_profiles(self): 103 """Get all profiles from database""" 104 105 cursor = self.__conn.execute('SELECT * FROM profile') 106 return cursor.fetchall() 107 108 def get_custom_drivers(self): 109 """Get all custom drivers from database""" 110 111 cursor = self.__conn.execute('SELECT * FROM custom') 112 return cursor.fetchall() 113 114 def get_profile_drivers_labels(self, name): 115 """Get all drivers labels for a specific profile from database""" 116 117 cursor = self.__conn.execute( 118 'SELECT label FROM driver ' 119 'WHERE profile=(SELECT id FROM profile WHERE name=?)', (name,)) 120 return cursor.fetchall() 121 122 def get_profile_remote_drivers(self, name): 123 """Get remote drivers list for a specific profile""" 124 125 cursor = self.__conn.execute( 126 'SELECT drivers FROM remote ' 127 'WHERE profile=(SELECT id FROM profile WHERE name=?)', (name,)) 128 return cursor.fetchone() 129 130 def delete_profile(self, name): 131 """Delete Profile""" 132 133 c = self.__conn.cursor() 134 c.execute('DELETE FROM driver WHERE profile=' 135 '(SELECT id FROM profile WHERE name=?)', (name,)) 136 c.execute('DELETE FROM profile WHERE name=?', (name,)) 137 self.__conn.commit() 138 c.close() 139 140 def add_profile(self, name): 141 """Add Profile""" 142 143 c = self.__conn.cursor() 144 try: 145 c.execute('INSERT INTO profile (name) VALUES(?)', (name,)) 146 except sqlite3.IntegrityError: 147 logging.warning("Profile name %s already exists.", name) 148 return c.lastrowid 149 150 def get_profile(self, name): 151 """Get profile info""" 152 153 cursor = self.__conn.execute('SELECT * FROM profile WHERE name=?', 154 (name,)) 155 return cursor.fetchone() 156 157 def update_profile(self, name, port, autostart=False, autoconnect=False): 158 """Update profile info""" 159 160 c = self.__conn.cursor() 161 if autostart: 162 # If we have a profile with autostart=1, reset everyone else to 0 163 c.execute('UPDATE profile SET autostart=0') 164 c.execute('UPDATE profile SET port=?, autostart=?, autoconnect=? WHERE name=?', 165 (port, autostart, autoconnect, name)) 166 self.__conn.commit() 167 c.close() 168 169 def save_profile_drivers(self, name, drivers): 170 """Save profile drivers""" 171 172 c = self.__conn.cursor() 173 c.execute('SELECT id FROM profile WHERE name=?', (name,)) 174 result = c.fetchone() 175 if result: 176 pid = result['id'] 177 else: 178 pid = self.add_profile(name) 179 180 c.execute('DELETE FROM driver WHERE profile=?', (pid,)) 181 c.execute('DELETE FROM remote WHERE profile=?', (pid,)) 182 183 for driver in drivers: 184 if 'label' in driver: 185 c.execute('INSERT INTO driver (label, profile) VALUES(?, ?)', 186 (driver['label'], pid)) 187 elif 'remote' in driver: 188 c.execute('INSERT INTO remote (drivers, profile) VALUES(?, ?)', 189 (driver['remote'], pid)) 190 self.__conn.commit() 191 c.close() 192 193 def save_profile_custom_driver(self, driver): 194 """Save custom profile driver""" 195 196 c = self.__conn.cursor() 197 try: 198 c.execute('INSERT INTO custom (label, name, family, exec, version)' 199 ' VALUES(?, ?, ?, ?, ?)', 200 (driver['Label'], driver['Name'], driver['Family'], driver['Exec'], driver['Version'])) 201 self.__conn.commit() 202 # Ignore duplicates 203 except sqlite3.Error: 204 pass 205 c.close() 206