db.py 3.02 KB
Newer Older
aoh's avatar
aoh committed
1
2
3
4
import pymysql

class DB():
    def __init__(self):
5
6
7
        with open("/etc/lysns/db-pass", "r") as f:
            self.pw = f.readline().strip()

aoh's avatar
aoh committed
8
9
10
    def __enter__(self):
        self.connection = pymysql.connect(host='lysns.lysator.liu.se',
                                 user='lysns',
11
                                 password=self.pw,
aoh's avatar
aoh committed
12
13
14
15
16
17
18
                                 db='lysns',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
        return self
    def __exit__(self, type, value, traceback):
        self.connection.close()

aoh's avatar
aoh committed
19
    def get_domain_ids(self):
aoh's avatar
aoh committed
20
21
22
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT id FROM domain')
            return [item.get("id") for item in cursor.fetchall()]
aoh's avatar
aoh committed
23

aoh's avatar
aoh committed
24
25
26
27
28
    def get_domain_name(self, id):
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT name FROM domain WHERE id = %s', id)
            return cursor.fetchone().get("name")

aoh's avatar
aoh committed
29
30
31
32
33
    def get_subdomain_ids(self, domainid):
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT id FROM subdomain WHERE domainid = %s', domainid)
            return [item.get("id") for item in cursor.fetchall()]

aoh's avatar
aoh committed
34
35
36
37
38
    def get_subdomain_name(self, id):
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT name FROM subdomain WHERE id = %s', id)
            return cursor.fetchone().get("name")

aoh's avatar
aoh committed
39
40
41
42
43
44
45
46
    def get_key(self, subid):
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT secret FROM update_key WHERE subid = %s LIMIT 1', subid)
            secret = cursor.fetchone()
            if secret is None:
                return None
            else:
                return secret.get("secret")
aoh's avatar
aoh committed
47
48
49
50
51
52
53
54

    def get_record(self, subid):
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT subdomain.name, subdomain.ttl, record_types.name '
                           'FROM subdomain '
                           'INNER JOIN record_types ON subdomain.rr = record_types.id '
                           'WHERE subdomain.id = %s LIMIT 1', subid)
            return cursor.fetchone()
55
56
57
58
59
60

    def get_subdomain_ids_without_keys(self, domainid):
        with self.connection.cursor() as cursor:
            cursor.execute('SELECT subdomain.id '
                           'FROM subdomain '
                           'LEFT OUTER JOIN update_key ON subdomain.id = update_key.subid '
aoh's avatar
aoh committed
61
                           'WHERE subdomain.domainid = %s AND update_key.secret IS NULL', domainid)
62
            return [item.get("id") for item in cursor.fetchall()]
63
64
65
66
67
68
69
70
71
72

    def set_subdomain_keys(self, domainid, public, private):
        with self.connection.cursor() as cursor:
            res = cursor.execute('INSERT INTO update_key '
                                 '(`subid`, `update_key`, `secret`) VALUES (%s, %s, %s)',
                                 (domainid, public, private,))
            if res == 1:
                self.connection.commit()

            return (res == 1)