db.py 10.3 KB
Newer Older
Monica Rainer's avatar
Monica Rainer committed
1
2
3
4
5
6
7
"""
Last modified: 2017-03-08

Creation and settings of the calibrations' database.
Connection to tre ramp processor database.
"""

8
import sqlite3, os, glob
Monica Rainer's avatar
Monica Rainer committed
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from drslib.config import CONFIG
from collections import OrderedDict

def create_db_calib():
    try:
        os.mkdir(CONFIG['CALIB_DB_DIR'])
    except OSError:
        pass
    dbcalib = sqlite3.connect(CONFIG['DB_CALIB_PATH'])
    cursor = dbcalib.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS "  + \
                   CONFIG['DB_CALIB_TBL'] + \
                   "(" + \
                   ",".join([CONFIG['DB_CALIB_COLS'][colname] + ' ' + CONFIG['DB_CALIB_DATATYPE'][colname] for colname in CONFIG['DB_CALIB_COLS']]) + \
                   ')')
Monica Rainer's avatar
Monica Rainer committed
24
25
26
27
28
29
30

    for colname in CONFIG['DB_CALIB_COLS']:
        try:
            cursor.execute('ALTER TABLE {table} ADD COLUMN {column} {datatype}'\
            .format(table=CONFIG['DB_CALIB_TBL'],column=CONFIG['DB_CALIB_COLS'][colname], datatype=CONFIG['DB_CALIB_DATATYPE'][colname]))
        except:
            #print CONFIG['DB_CALIB_TBL']
Monica Rainer's avatar
Monica Rainer committed
31
            #print colname
Monica Rainer's avatar
Monica Rainer committed
32
33
34
35
            #print CONFIG['DB_CALIB_DATATYPE'][colname]
            pass
    

Monica Rainer's avatar
Monica Rainer committed
36
37
38
39
40
    dbcalib.commit()
    return dbcalib

def create_db_night():
    try:
Monica Rainer's avatar
Monica Rainer committed
41
        os.mkdir(CONFIG['WEBUI_DB_DIR'])
Monica Rainer's avatar
Monica Rainer committed
42
43
44
45
46
    except OSError:
        pass
    dbcalib = sqlite3.connect(CONFIG['DB_NIGHT_PATH'])
    cursor = dbcalib.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS "  + \
Monica Rainer's avatar
Monica Rainer committed
47
                   CONFIG['DB_2D_TBL'] + \
Monica Rainer's avatar
Monica Rainer committed
48
                   "(" + \
Monica Rainer's avatar
Monica Rainer committed
49
                   ",".join([CONFIG['DB_2D_COLS'][colname] + ' ' + CONFIG['DB_2D_DATATYPE'][colname] for colname in CONFIG['DB_2D_COLS']]) + \
Monica Rainer's avatar
Monica Rainer committed
50
51
                   ')')

Monica Rainer's avatar
Monica Rainer committed
52
53
54
55
56
    cursor.execute("CREATE TABLE IF NOT EXISTS "  + \
                   CONFIG['DB_1D_TBL'] + \
                   "(" + \
                   ",".join([CONFIG['DB_1D_COLS'][colname] + ' ' + CONFIG['DB_1D_DATATYPE'][colname] for colname in CONFIG['DB_1D_COLS']]) + \
                   ')')
Monica Rainer's avatar
Monica Rainer committed
57

58
59
60
61
62
63
64
    cursor.execute("CREATE TABLE IF NOT EXISTS "  + \
                   CONFIG['DB_CAL_TBL'] + \
                   "(" + \
                   ",".join([CONFIG['DB_CAL_COLS'][colname] + ' ' + CONFIG['DB_CAL_DATATYPE'][colname] for colname in CONFIG['DB_CAL_COLS']]) + \
                   ')')


Monica Rainer's avatar
Monica Rainer committed
65
66
67
68
69
70
71
72
73
    dbcalib.commit()
    return dbcalib


def connect_db_ramp():
    dbramps = sqlite3.connect(CONFIG['DB_RAMP'])
    return dbramps


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
def remove_dbfile(night,cal=False):
    """
    Removes all instances of a calibration file from the database and
    from the calibrations directory.
    Requires 2 args:
        - night in the same format used in the filename (yyyy_mm_dd or yyyy-mm-dd)
        - calibrations suffix (optional): UNE, FP, DARK, FLAT.
              If missing, all the calibration files of the night will be removed
              and the entire row for that night will be removed.
    How to call from gofio directory:
        from drslib.db import remove_dbfile
        remove_dbfile('2017_06_20','UNE')
    """

    if cal:
        filename = ''.join(['%',night,'%',cal,'%'])
        globname = ''.join([CONFIG['CALIB_DIR'],'/*',night,'*',cal,'*'])
    else:
        filename = ''.join(['%',night,'%'])
        globname = ''.join([CONFIG['CALIB_DIR'],'/*',night,'*'])

    night = night.replace('_','-')

    dbcalib = sqlite3.connect(CONFIG['DB_CALIB_PATH'])
    cursor = dbcalib.cursor()
    #print CONFIG['DB_CALIB_COLS']
    for colname in CONFIG['DB_CALIB_COLS'].values():
        cursor.execute("UPDATE {table} SET {calib}=NULL WHERE {calib} LIKE ?"\
            .format(table='calibrations',calib=colname),(filename,))
    if not cal:
        cursor.execute("DELETE FROM {table} WHERE {dcalib}=?"\
            .format(table='calibrations',dcalib=CONFIG['DB_CALIB_COLS']['01_data']),(night,))

    dbcalib.commit()

    for f in glob.glob(globname):
        os.remove(f)

    return


Monica Rainer's avatar
Monica Rainer committed
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
# ---------- OPERATIONS: calibration database -----------------


def insert_dbfile(dbcalib, calname, calfile):

    cursor = dbcalib.cursor()

    cursor.execute("UPDATE {table} SET {calib}=? WHERE {dcalib}=?"\
        .format(table=CONFIG['DB_CALIB_TBL'],calib=CONFIG['DB_CALIB_COLS'][calname],dcalib=CONFIG['DB_CALIB_COLS']['01_data']), (calfile,CONFIG['DATE'],))

    cursor.execute("INSERT OR IGNORE INTO {table} ({dcalib},{calib}) VALUES (?,?)"\
        .format(table=CONFIG['DB_CALIB_TBL'],dcalib=CONFIG['DB_CALIB_COLS']['01_data'],calib=CONFIG['DB_CALIB_COLS'][calname]),(CONFIG['DATE'],calfile,))

    dbcalib.commit()


def copy_dbfile(dbcalib,calname):

    cursor = dbcalib.cursor()
    cursor.execute("SELECT {calib} FROM {table} WHERE {calib} IS NOT NULL\
        ORDER BY ABS( julianday(datetime(?)) - julianday({dcalib}) ) asc limit 1"\
        .format(calib=CONFIG['DB_CALIB_COLS'][calname], table=CONFIG['DB_CALIB_TBL'], dcalib=CONFIG['DB_CALIB_COLS']['01_data'],),(CONFIG['DATE'],))
    calfile = cursor.fetchone()[0]

    cursor.execute("UPDATE {table} SET {calib}=? WHERE {dcalib}=?"\
        .format(table=CONFIG['DB_CALIB_TBL'],calib=CONFIG['DB_CALIB_COLS'][calname],dcalib=CONFIG['DB_CALIB_COLS']['01_data'],),(calfile,CONFIG['DATE'],))

    cursor.execute("INSERT OR IGNORE INTO {table} ({dcalib},{calib}) VALUES (?,?)"\
        .format(table=CONFIG['DB_CALIB_TBL'],dcalib=CONFIG['DB_CALIB_COLS']['01_data'],calib=CONFIG['DB_CALIB_COLS'][calname]),(CONFIG['DATE'],calfile,))

    dbcalib.commit()


def extract_dbfile(dbcalib,calname):

    cursor = dbcalib.cursor()
    cursor.execute("SELECT {calib} FROM {table} WHERE {dcalib}=?"\
        .format(calib=CONFIG['DB_CALIB_COLS'][calname],table=CONFIG['DB_CALIB_TBL'],dcalib=CONFIG['DB_CALIB_COLS']['01_data']),(CONFIG['DATE'],))
    calfile = cursor.fetchone()[0]
    dbcalib.commit()

    return calfile



def check_dbfile(dbcalib,calname):
    """
    Return False if there is already a calibration for the night.
    """

    cursor = dbcalib.cursor()
    try:
        cursor.execute("SELECT {calib} FROM {table} WHERE {dcalib}=?"\
                .format(calib=CONFIG['DB_CALIB_COLS'][calname],table=CONFIG['DB_CALIB_TBL'],dcalib=CONFIG['DB_CALIB_COLS']['01_data']),(CONFIG['DATE'],))
        calfile = cursor.fetchone()[0]
        dbcalib.commit()
        if calfile:
            return False
        else:
            return True

    except:
        dbcalib.commit()
        return True

    return False


# --------------- OPERATIONS: ramp processor database ------------

def check_raw(dbramps,rawfile):
    """
    Check if the file is in the database.
    """
    cursor = dbramps.cursor()

    try:
192
193
        #cursor.execute("SELECT * FROM {table} WHERE {fpath}=?"\
        cursor.execute("SELECT * FROM {table} WHERE instr({fpath},?) > 0"\
Monica Rainer's avatar
Monica Rainer committed
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
                .format(table=CONFIG['DB_RAMP_TBL'],fpath=CONFIG['DB_RAMP_COLS']['fpath']),(rawfile,))
        filename = cursor.fetchone()[0]
        if filename:
            return True
        else:
            return False
    except:
        return False


def read_ramps(dbramps,rawfile,keyword):
    """
    Read the information on objects to be reduced.
    """
    cursor = dbramps.cursor()
    cursor.execute("SELECT {col} FROM {table} WHERE {fpath}=?"\
        .format(col=keyword,table=CONFIG['DB_RAMP_TBL'],fpath=CONFIG['DB_RAMP_COLS']['fpath']),(rawfile,))
    value = cursor.fetchone()[0]
    return value

# --------------- OPERATIONS: night reduced database ------------

def check_night(dbnight,rawfile):
    """
    Check if the file is in the database.
    """
    cursor = dbnight.cursor()

222
223
224
225
226
227
228
229
230
    try: 
        cursor.execute("SELECT * FROM {table} WHERE instr({fpath},?) > 0"\
                .format(table=CONFIG['DB_CAL_TBL'],fpath=CONFIG['DB_CAL_COLS']['names']),(rawfile,))
        fstamp = cursor.fetchone()[0]
        if fstamp:
            return True
    except:
        pass

Monica Rainer's avatar
Monica Rainer committed
231
    try:
Monica Rainer's avatar
Monica Rainer committed
232
233
        cursor.execute("SELECT {stamp} FROM {table} WHERE instr({fpath},?) > 0"\
                .format(stamp=CONFIG['DB_2D_COLS']['stamp'],table=CONFIG['DB_2D_TBL'],fpath=CONFIG['DB_2D_COLS']['names']),(rawfile,))
234
235
        fstamp = cursor.fetchone()[0]
        if fstamp:
Monica Rainer's avatar
Monica Rainer committed
236
            cursor.execute("SELECT * FROM {table} WHERE {stamp}=?"\
237
                .format(table=CONFIG['DB_1D_TBL'],stamp=CONFIG['DB_1D_COLS']['stamp']),(fstamp,))
Monica Rainer's avatar
Monica Rainer committed
238
239
240
241
242
            reduced = cursor.fetchone()[0]
            if reduced:
                return True
            else:
                return False
Monica Rainer's avatar
Monica Rainer committed
243
    except:
244
245
        pass
    return False
Monica Rainer's avatar
Monica Rainer committed
246
247


Monica Rainer's avatar
Monica Rainer committed
248
249
def insert_dbnight(dbnight, reduced, stamp):

250
251
252
    #reduced = ','.join(map(os.path.basename,reduced))
    reduced = ','.join(map(str,reduced))

Monica Rainer's avatar
Monica Rainer committed
253
254
255
256
257
258
259
260
261
262
263
    cursor = dbnight.cursor()

    cursor.execute("UPDATE {table} SET {fpath}=?, {fstamp}=? WHERE instr({fpath},?) > 0"\
        .format(table=CONFIG['DB_2D_TBL'],fpath=CONFIG['DB_2D_COLS']['names'],fstamp=CONFIG['DB_2D_COLS']['stamp']),(reduced,stamp,reduced,))

    cursor.execute("INSERT OR IGNORE INTO {table} ({fpath},{fstamp}) VALUES (?,?)"\
        .format(table=CONFIG['DB_2D_TBL'],fpath=CONFIG['DB_2D_COLS']['names'],fstamp=CONFIG['DB_2D_COLS']['stamp']),(reduced,stamp,))

    dbnight.commit()

def insert_dbreduced(dbnight, reduced, stamp):
Monica Rainer's avatar
Monica Rainer committed
264
265
266

    cursor = dbnight.cursor()

267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
    cursor.execute("INSERT OR IGNORE INTO {table} ({path},{fstamp},{rid},{ftype},{stat},{name},{slit},{snry},{snrj},{snrh},{snrk}) VALUES (?,?,?,?,?,?,?,?,?,?,?)"\
        .format(table=CONFIG['DB_1D_TBL'],path=CONFIG['DB_1D_COLS']['path'], fstamp=CONFIG['DB_1D_COLS']['stamp'], rid=CONFIG['DB_1D_COLS']['id'], ftype=CONFIG['DB_1D_COLS']['type'], stat=CONFIG['DB_1D_COLS']['stat'], name=CONFIG['DB_1D_COLS']['name'], slit=CONFIG['DB_1D_COLS']['slit'], snry=CONFIG['DB_1D_COLS']['snry'], snrj=CONFIG['DB_1D_COLS']['snrj'], snrh=CONFIG['DB_1D_COLS']['snrh'], snrk=CONFIG['DB_1D_COLS']['snrk']),(reduced['path'], stamp, reduced['id'], reduced['type'], 0, reduced['name'], reduced['slit'], reduced['snry'], reduced['snrj'], reduced['snrh'], reduced['snrk'], ))

    dbnight.commit()

def insert_calib(dbnight, caltype, frames, quality, stamp):

    #frames = ','.join(map(os.path.basename,frames))
    frames = ','.join(map(str,frames))
    quality = ','.join(map(str,quality))

    cursor = dbnight.cursor()

    cursor.execute("INSERT OR IGNORE INTO {table} ({ftype},{names},{status},{fstamp}) VALUES (?,?,?,?)"\
        .format(table=CONFIG['DB_CAL_TBL'],ftype=CONFIG['DB_CAL_COLS']['type'], names=CONFIG['DB_CAL_COLS']['names'], status=CONFIG['DB_CAL_COLS']['status'], fstamp=CONFIG['DB_CAL_COLS']['stamp']),(caltype, frames, quality, stamp, ))
Monica Rainer's avatar
Monica Rainer committed
282
283
284

    dbnight.commit()