db.py 8.58 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
24
25
26
27
28
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']]) + \
                   ')')
    dbcalib.commit()
    return dbcalib

def create_db_night():
    try:
Monica Rainer's avatar
Monica Rainer committed
29
        os.mkdir(CONFIG['WEBUI_DB_DIR'])
Monica Rainer's avatar
Monica Rainer committed
30
31
32
33
34
    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
35
                   CONFIG['DB_2D_TBL'] + \
Monica Rainer's avatar
Monica Rainer committed
36
                   "(" + \
Monica Rainer's avatar
Monica Rainer committed
37
                   ",".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
38
39
                   ')')

Monica Rainer's avatar
Monica Rainer committed
40
41
42
43
44
    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
45
46
47
48
49
50
51
52
53
54

    dbcalib.commit()
    return dbcalib


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


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
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
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
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
192
193
194
195
196
197
198
199
200
201
202
# ---------- 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:
        cursor.execute("SELECT * FROM {table} WHERE {fpath}=?"\
                .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()

    try:
Monica Rainer's avatar
Monica Rainer committed
203
204
205
206
207
208
209
210
211
212
213
        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,))
        stamp = cursor.fetchone()[0]
        if stamp:
            cursor.execute("SELECT * FROM {table} WHERE {stamp}=?"\
                .format(table=CONFIG['DB_1D_TBL'],stamp=CONFIG['DB_1D_COLS']['stamp']),(stamp,))
            reduced = cursor.fetchone()[0]
            if reduced:
                return True
            else:
                return False
Monica Rainer's avatar
Monica Rainer committed
214
215
216
217
218
219
        else:
            return False
    except:
        return False


Monica Rainer's avatar
Monica Rainer committed
220
221
222
223
224
225
226
227
228
229
230
231
232
def insert_dbnight(dbnight, reduced, stamp):

    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
233
234
235

    cursor = dbnight.cursor()

Monica Rainer's avatar
Monica Rainer committed
236
237
    cursor.execute("INSERT OR IGNORE INTO {table} ({path},{stamp},{rid},{ftype},{stat},{name},{slit},{snry},{snrj},{snrh},{snrk}) VALUES (?,?,?,?,?,?,?,?,?,?,?)"\
        .format(table=CONFIG['DB_1D_TBL'],path=CONFIG['DB_1D_COLS']['path'], stamp=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'], ))
Monica Rainer's avatar
Monica Rainer committed
238
239
240

    dbnight.commit()