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