1
|
|
|
""" |
2
|
|
|
BackupPC Clone |
3
|
|
|
""" |
4
|
|
|
import csv |
5
|
|
|
import os |
6
|
|
|
import sqlite3 |
7
|
|
|
|
8
|
|
|
|
9
|
|
|
class DataLayer: |
10
|
|
|
""" |
11
|
|
|
Provides an interface to the SQLite3 data_layer |
12
|
|
|
""" |
13
|
|
|
instance = None |
14
|
|
|
""" |
15
|
|
|
The singleton instance of this class. |
16
|
|
|
|
17
|
|
|
:type instance: backuppc_clone.DataLayer.DataLayer |
18
|
|
|
""" |
19
|
|
|
|
20
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
21
|
|
|
def __init__(self, database): |
22
|
|
|
""" |
23
|
|
|
Object constructor. |
24
|
|
|
|
25
|
|
|
:param str database: Path to the SQLite database. |
26
|
|
|
""" |
27
|
|
|
if DataLayer.instance is not None: |
28
|
|
|
raise Exception("This class is a singleton!") |
29
|
|
|
else: |
30
|
|
|
DataLayer.instance = self |
31
|
|
|
|
32
|
|
|
self.__database = database |
33
|
|
|
""" |
34
|
|
|
The path to the SQLite database. |
35
|
|
|
|
36
|
|
|
:type: str |
37
|
|
|
""" |
38
|
|
|
|
39
|
|
|
self.__connection = sqlite3.connect(database, isolation_level="EXCLUSIVE") |
40
|
|
|
""" |
41
|
|
|
The connection to the database. |
42
|
|
|
|
43
|
|
|
:type: sqlite3.Connection |
44
|
|
|
""" |
45
|
|
|
|
46
|
|
|
self.__last_rowid = -1 |
47
|
|
|
""" |
48
|
|
|
The last rowid as returns by the last used cursor. |
49
|
|
|
|
50
|
|
|
:type: int |
51
|
|
|
""" |
52
|
|
|
|
53
|
|
|
tmp_dir = os.path.join(os.path.dirname(database), 'tmp') |
54
|
|
|
self.execute_none('pragma temp_store = 1') |
55
|
|
|
self.execute_none('pragma temp_store_directory = \'{}\''.format(tmp_dir)) |
56
|
|
|
|
57
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
58
|
|
|
def disconnect(self): |
59
|
|
|
""" |
60
|
|
|
Disconnects from the SQLite database. |
61
|
|
|
""" |
62
|
|
|
self.__connection.commit() |
63
|
|
|
self.__connection.close() |
64
|
|
|
|
65
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
66
|
|
|
def connect(self): |
67
|
|
|
""" |
68
|
|
|
Connects to the SQLite database. |
69
|
|
|
""" |
70
|
|
|
self.__connection = sqlite3.connect(self.__database, isolation_level="EXCLUSIVE") |
71
|
|
|
|
72
|
|
|
tmp_dir = os.path.join(os.path.dirname(self.__database), 'tmp') |
73
|
|
|
self.execute_none('pragma temp_store = 1') |
74
|
|
|
self.execute_none('pragma temp_store_directory = \'{}\''.format(tmp_dir)) |
75
|
|
|
|
76
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
77
|
|
|
def backup_delete(self, bck_id): |
78
|
|
|
""" |
79
|
|
|
Deletes cascading a host backup. |
80
|
|
|
|
81
|
|
|
:param int bck_id: The ID of the host backup. |
82
|
|
|
""" |
83
|
|
|
self.backup_empty(bck_id) |
84
|
|
|
self.execute_none('delete from BKC_BACKUP where bck_id=?', (bck_id,)) |
85
|
|
|
|
86
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
87
|
|
|
def backup_empty(self, bck_id): |
88
|
|
|
""" |
89
|
|
|
Removes the tree from a host backup. |
90
|
|
|
|
91
|
|
|
:param int bck_id: The ID of the host backup. |
92
|
|
|
""" |
93
|
|
|
self.execute_none('delete from BKC_BACKUP_TREE where bck_id=?', (bck_id,)) |
94
|
|
|
|
95
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
96
|
|
|
def backup_get_all(self): |
97
|
|
|
""" |
98
|
|
|
Selects all cloned backups. |
99
|
|
|
""" |
100
|
|
|
sql = """ |
101
|
|
|
select hst.hst_name |
102
|
|
|
, bck.bck_number |
103
|
|
|
from BKC_HOST hst |
104
|
|
|
inner join BKC_BACKUP bck on bck.hst_id = hst.hst_id""" |
105
|
|
|
|
106
|
|
|
return self.execute_rows(sql) |
107
|
|
|
|
108
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
109
|
|
|
def backup_get_next(self, end_time): |
110
|
|
|
""" |
111
|
|
|
Selects the next backup to clone. |
112
|
|
|
|
113
|
|
|
:rtype: dict|None |
114
|
|
|
""" |
115
|
|
|
sql = """ |
116
|
|
|
select bob.bob_host |
117
|
|
|
, bob.bob_number |
118
|
|
|
, bob.bob_end_time |
119
|
|
|
, bob.bob_level |
120
|
|
|
, bob.bob_type |
121
|
|
|
from BKC_ORIGINAL_BACKUP bob |
122
|
|
|
left outer join BKC_HOST hst on hst.hst_name = bob.bob_host |
123
|
|
|
left outer join BKC_BACKUP bck on bck.hst_id = hst.hst_id and |
124
|
|
|
bck.bck_number = bob.bob_number |
125
|
|
|
where bck.bck_id is null |
126
|
|
|
and bob.bob_version like '3.%' |
127
|
|
|
and bob.bob_type in ('full', 'incr') |
128
|
|
|
and bob.bob_end_time is not null |
129
|
|
|
and (bob.bob_end_time < ? or ? = -1) |
130
|
|
|
order by bob.bob_type |
131
|
|
|
, bob.bob_end_time desc |
132
|
|
|
limit 0, 1""" |
133
|
|
|
|
134
|
|
|
return self.execute_row0(sql, (end_time, end_time)) |
135
|
|
|
|
136
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
137
|
|
|
def backup_get_obsolete(self): |
138
|
|
|
""" |
139
|
|
|
Selects obsolete host backups. |
140
|
|
|
|
141
|
|
|
:rtype: list[dict] |
142
|
|
|
""" |
143
|
|
|
sql = """ |
144
|
|
|
select hst.hst_name |
145
|
|
|
, bck.bck_number |
146
|
|
|
from BKC_HOST hst |
147
|
|
|
inner join BKC_BACKUP bck on bck.hst_id = hst.hst_id |
148
|
|
|
left outer join BKC_ORIGINAL_BACKUP bob on bob.bob_host = hst.hst_name and |
149
|
|
|
bob.bob_number = bck.bck_number |
150
|
|
|
where bob.rowid is null |
151
|
|
|
order by hst.hst_name |
152
|
|
|
, bck.bck_number""" |
153
|
|
|
|
154
|
|
|
return self.execute_rows(sql) |
155
|
|
|
|
156
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
157
|
|
|
def backup_partially_cloned(self): |
158
|
|
|
""" |
159
|
|
|
Selects partially cloned host backups. |
160
|
|
|
|
161
|
|
|
:rtype: list[dict] |
162
|
|
|
""" |
163
|
|
|
sql = """ |
164
|
|
|
select hst.hst_name |
165
|
|
|
, bck.bck_number |
166
|
|
|
from BKC_HOST hst |
167
|
|
|
inner join BKC_BACKUP bck on bck.hst_id = hst.hst_id |
168
|
|
|
where ifnull(bck.bck_in_progress, 1) = 1 |
169
|
|
|
order by hst.hst_name |
170
|
|
|
, bck.bck_number""" |
171
|
|
|
|
172
|
|
|
return self.execute_rows(sql) |
173
|
|
|
|
174
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
175
|
|
|
def backup_set_in_progress(self, bck_id, bck_in_progress): |
176
|
|
|
""" |
177
|
|
|
Updates the in progress flag of a host backup. |
178
|
|
|
|
179
|
|
|
:param int bck_id: The ID of the host backup. |
180
|
|
|
:param int bck_in_progress: The in progress flag. |
181
|
|
|
""" |
182
|
|
|
if bck_in_progress != 0: |
183
|
|
|
bck_in_progress = 1 |
184
|
|
|
|
185
|
|
|
sql = """ |
186
|
|
|
update BKC_BACKUP |
187
|
|
|
set bck_in_progress = ? |
188
|
|
|
where bck_id = ?""" |
189
|
|
|
|
190
|
|
|
return self.execute_none(sql, (bck_in_progress, bck_id)) |
191
|
|
|
|
192
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
193
|
|
|
def backup_get_stats(self, bck_id): |
194
|
|
|
""" |
195
|
|
|
Selects the statistics of a host backup. |
196
|
|
|
|
197
|
|
|
:param int bck_id: The ID of the host backup. |
198
|
|
|
""" |
199
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
200
|
|
|
|
201
|
|
|
sql = """ |
202
|
|
|
select count(bbt_inode_original) '#files' |
203
|
|
|
, sum(case when bbt_inode_original is null then 1 else 0 end) '#dirs' |
204
|
|
|
from BKC_BACKUP_TREE |
205
|
|
|
where bck_id = ?""" |
206
|
|
|
|
207
|
|
|
return self.execute_row1(sql, (bck_id,)) |
208
|
|
|
|
209
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
210
|
|
|
def backup_prepare_required_clone_pool_files(self, bck_id): |
211
|
|
|
""" |
212
|
|
|
Prepares the files required for a host backup that are not yet copied from the original pool to the clone pool. |
213
|
|
|
|
214
|
|
|
:param int bck_id: The ID of the host backup. |
215
|
|
|
|
216
|
|
|
:rtype: int |
217
|
|
|
""" |
218
|
|
|
self.execute_none('delete from TMP_CLONE_POOL_REQUIRED') |
219
|
|
|
|
220
|
|
|
sql = """ |
221
|
|
|
insert into TMP_CLONE_POOL_REQUIRED( bpl_inode_original |
222
|
|
|
, bpl_dir |
223
|
|
|
, bpl_name ) |
224
|
|
|
select distinct bpl_inode_original |
225
|
|
|
, bpl_dir |
226
|
|
|
, bpl_name |
227
|
|
|
from BKC_BACKUP_TREE bbt |
228
|
|
|
inner join BKC_POOL bpl on bpl.bpl_inode_original = bbt.bbt_inode_original |
229
|
|
|
where bbt.bck_id = ? |
230
|
|
|
and bpl.bpl_inode_clone is null""" |
231
|
|
|
|
232
|
|
|
self.execute_none(sql, (bck_id,)) |
233
|
|
|
|
234
|
|
|
sql = """ |
235
|
|
|
select count(distinct bpl_inode_original) |
236
|
|
|
from TMP_CLONE_POOL_REQUIRED""" |
237
|
|
|
|
238
|
|
|
return self.execute_singleton1(sql) |
239
|
|
|
|
240
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
241
|
|
|
def backup_prepare_tree(self, bck_id): |
242
|
|
|
""" |
243
|
|
|
Selects the file entries of a host backup. |
244
|
|
|
|
245
|
|
|
:param int bck_id: The ID of the host backup. |
246
|
|
|
|
247
|
|
|
:rtype: int |
248
|
|
|
""" |
249
|
|
|
self.execute_none('delete from TMP_BACKUP_TREE') |
250
|
|
|
|
251
|
|
|
sql = """ |
252
|
|
|
insert into TMP_BACKUP_TREE( bpl_inode_original |
253
|
|
|
, bpl_dir |
254
|
|
|
, bpl_name |
255
|
|
|
|
256
|
|
|
, bbt_seq |
257
|
|
|
, bbt_inode_original |
258
|
|
|
, bbt_dir |
259
|
|
|
, bbt_name ) |
260
|
|
|
select bpl.bpl_inode_original |
261
|
|
|
, bpl.bpl_dir |
262
|
|
|
, bpl.bpl_name |
263
|
|
|
|
264
|
|
|
, bbt.bbt_seq |
265
|
|
|
, bbt.bbt_inode_original |
266
|
|
|
, bbt.bbt_dir |
267
|
|
|
, bbt.bbt_name |
268
|
|
|
from BKC_BACKUP_TREE bbt |
269
|
|
|
left outer join BKC_POOL bpl on bpl.bpl_inode_original = bbt.bbt_inode_original |
270
|
|
|
where bbt.bck_id = ?""" |
271
|
|
|
|
272
|
|
|
self.execute_none(sql, (bck_id,)) |
273
|
|
|
|
274
|
|
|
sql = """ |
275
|
|
|
select count(*) |
276
|
|
|
from TMP_BACKUP_TREE""" |
277
|
|
|
|
278
|
|
|
return self.execute_singleton1(sql) |
279
|
|
|
|
280
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
281
|
|
|
def backup_yield_required_clone_pool_files(self): |
282
|
|
|
""" |
283
|
|
|
Selects the pool files required for a host backup that are not yet copied from the original pool to the clone |
284
|
|
|
pool. |
285
|
|
|
""" |
286
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
287
|
|
|
|
288
|
|
|
sql = """ |
289
|
|
|
select bpl_inode_original |
290
|
|
|
, bpl_dir |
291
|
|
|
, bpl_name |
292
|
|
|
from TMP_CLONE_POOL_REQUIRED |
293
|
|
|
order by bpl_dir |
294
|
|
|
, bpl_name""" |
295
|
|
|
|
296
|
|
|
cursor = self.__connection.cursor() |
297
|
|
|
cursor.execute(sql) |
298
|
|
|
while True: |
299
|
|
|
rows = cursor.fetchmany(10000) |
300
|
|
|
if not rows: |
301
|
|
|
cursor.close() |
302
|
|
|
raise StopIteration() |
303
|
|
|
yield rows |
304
|
|
|
|
305
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
306
|
|
|
def backup_yield_tree(self): |
307
|
|
|
""" |
308
|
|
|
Selects the file entries of a host backup. |
309
|
|
|
""" |
310
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
311
|
|
|
|
312
|
|
|
sql = """ |
313
|
|
|
select bpl_inode_original |
314
|
|
|
, bpl_dir |
315
|
|
|
, bpl_name |
316
|
|
|
|
317
|
|
|
, bbt_inode_original |
318
|
|
|
, bbt_dir |
319
|
|
|
, bbt_name |
320
|
|
|
from TMP_BACKUP_TREE |
321
|
|
|
order by bbt_seq |
322
|
|
|
, bpl_dir |
323
|
|
|
, bpl_name""" |
324
|
|
|
|
325
|
|
|
cursor = self.__connection.cursor() |
326
|
|
|
cursor.execute(sql) |
327
|
|
|
while True: |
328
|
|
|
rows = cursor.fetchmany(10000) |
329
|
|
|
if not rows: |
330
|
|
|
cursor.close() |
331
|
|
|
raise StopIteration() |
332
|
|
|
yield rows |
333
|
|
|
|
334
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
335
|
|
|
def commit(self): |
336
|
|
|
""" |
337
|
|
|
Commits the current transaction. |
338
|
|
|
""" |
339
|
|
|
self.__connection.commit() |
340
|
|
|
|
341
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
342
|
|
|
@staticmethod |
343
|
|
|
def dict_factory(cursor, old_row): |
344
|
|
|
""" |
345
|
|
|
Dictionary factory for return results with dictionaries. |
346
|
|
|
|
347
|
|
|
:param sqlite3.Cursor cursor: The cursor. |
348
|
|
|
:param list old_row: A row from the result a a query. |
349
|
|
|
|
350
|
|
|
:rtype: dict |
351
|
|
|
""" |
352
|
|
|
new_row = {} |
353
|
|
|
for index, col in enumerate(cursor.description): |
354
|
|
|
new_row[col[0]] = old_row[index] |
355
|
|
|
|
356
|
|
|
return new_row |
357
|
|
|
|
358
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
359
|
|
|
def execute_none(self, sql, *params): |
360
|
|
|
""" |
361
|
|
|
Executes a SQL statement that does not select any rows |
362
|
|
|
|
363
|
|
|
:param str sql: The SQL calling the stored procedure. |
364
|
|
|
:param iterable params: The arguments for the stored procedure. |
365
|
|
|
""" |
366
|
|
|
self.__connection.row_factory = None |
367
|
|
|
|
368
|
|
|
cursor = self.__connection.cursor() |
369
|
|
|
cursor.execute(sql, *params) |
370
|
|
|
self.__last_rowid = cursor.lastrowid |
371
|
|
|
cursor.close() |
372
|
|
|
|
373
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
374
|
|
|
def execute_row0(self, sql, *params): |
375
|
|
|
""" |
376
|
|
|
Executes a SQL statement that selects 0 or 1 row. |
377
|
|
|
|
378
|
|
|
:param str sql: The SQL calling the stored procedure. |
379
|
|
|
:param iterable params: The arguments for the stored procedure. |
380
|
|
|
|
381
|
|
|
:rtype: dict |
382
|
|
|
""" |
383
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
384
|
|
|
|
385
|
|
|
cursor = self.__connection.cursor() |
386
|
|
|
cursor.execute(sql, *params) |
387
|
|
|
rows = cursor.fetchall() |
388
|
|
|
self.__last_rowid = cursor.lastrowid |
389
|
|
|
cursor.close() |
390
|
|
|
|
391
|
|
|
if len(rows) == 0: |
392
|
|
|
return None |
393
|
|
|
|
394
|
|
|
return rows[0] |
395
|
|
|
|
396
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
397
|
|
|
def execute_row1(self, sql, *params): |
398
|
|
|
""" |
399
|
|
|
Executes a SQL statement that selects 1 row. |
400
|
|
|
|
401
|
|
|
:param str sql: The SQL calling the stored procedure. |
402
|
|
|
:param iterable params: The arguments for the stored procedure. |
403
|
|
|
|
404
|
|
|
:rtype: dict |
405
|
|
|
""" |
406
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
407
|
|
|
|
408
|
|
|
cursor = self.__connection.cursor() |
409
|
|
|
cursor.execute(sql, *params) |
410
|
|
|
rows = cursor.fetchall() |
411
|
|
|
self.__last_rowid = cursor.lastrowid |
412
|
|
|
cursor.close() |
413
|
|
|
|
414
|
|
|
return rows[0] |
415
|
|
|
|
416
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
417
|
|
|
def execute_rows(self, sql, *params): |
418
|
|
|
""" |
419
|
|
|
Executes a SQL statement that selects 0, 1, or more rows. |
420
|
|
|
|
421
|
|
|
:param str sql: The SQL calling the stored procedure. |
422
|
|
|
:param iterable params: The arguments for the stored procedure. |
423
|
|
|
|
424
|
|
|
:rtype: list[dict] |
425
|
|
|
""" |
426
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
427
|
|
|
|
428
|
|
|
cursor = self.__connection.cursor() |
429
|
|
|
cursor.execute(sql, *params) |
430
|
|
|
rows = cursor.fetchall() |
431
|
|
|
self.__last_rowid = cursor.lastrowid |
432
|
|
|
cursor.close() |
433
|
|
|
|
434
|
|
|
return rows |
435
|
|
|
|
436
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
437
|
|
|
def execute_singleton0(self, sql, *params): |
438
|
|
|
""" |
439
|
|
|
Executes a SQL statement that selects 0 or 1 row with 1 column. Returns the value of selected column or None. |
440
|
|
|
|
441
|
|
|
:param str sql: The SQL calling the stored procedure. |
442
|
|
|
:param iterable params: The arguments for the stored procedure. |
443
|
|
|
|
444
|
|
|
:rtype: * |
445
|
|
|
""" |
446
|
|
|
self.__connection.row_factory = None |
447
|
|
|
|
448
|
|
|
cursor = self.__connection.cursor() |
449
|
|
|
cursor.execute(sql, *params) |
450
|
|
|
row = cursor.fetchone() |
451
|
|
|
if row: |
452
|
|
|
ret = row[0] |
453
|
|
|
else: |
454
|
|
|
ret = None |
455
|
|
|
self.__last_rowid = cursor.lastrowid |
456
|
|
|
cursor.close() |
457
|
|
|
|
458
|
|
|
return ret |
459
|
|
|
|
460
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
461
|
|
|
def execute_singleton1(self, sql, *params): |
462
|
|
|
""" |
463
|
|
|
Executes a SQL statement that selects 1 row with 1 column. Returns the value of selected column. |
464
|
|
|
|
465
|
|
|
:param str sql: The SQL calling the stored procedure. |
466
|
|
|
:param iterable params: The arguments for the stored procedure. |
467
|
|
|
|
468
|
|
|
:rtype: * |
469
|
|
|
""" |
470
|
|
|
self.__connection.row_factory = None |
471
|
|
|
|
472
|
|
|
cursor = self.__connection.cursor() |
473
|
|
|
cursor.execute(sql, *params) |
474
|
|
|
ret = cursor.fetchone()[0] |
475
|
|
|
self.__last_rowid = cursor.lastrowid |
476
|
|
|
cursor.close() |
477
|
|
|
|
478
|
|
|
return ret |
479
|
|
|
|
480
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
481
|
|
|
def get_host_id(self, hostname): |
482
|
|
|
""" |
483
|
|
|
Returns the ID of a host. If the host does not exists it will be inserted. |
484
|
|
|
|
485
|
|
|
:param str hostname: The name of the host. |
486
|
|
|
|
487
|
|
|
:rtype: int |
488
|
|
|
""" |
489
|
|
|
hst_id = self.execute_singleton0('select hst_id from BKC_HOST where hst_name = ?', (hostname,)) |
490
|
|
|
if hst_id is None: |
491
|
|
|
self.execute_none('insert into BKC_HOST(hst_name) values(?)', (hostname,)) |
492
|
|
|
hst_id = self.__last_rowid |
493
|
|
|
|
494
|
|
|
return hst_id |
495
|
|
|
|
496
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
497
|
|
|
def get_bck_id(self, hst_id, bck_number): |
498
|
|
|
""" |
499
|
|
|
Returns the ID of a host backup. If the backup does not exists it will be inserted. |
500
|
|
|
|
501
|
|
|
:param int hst_id: The ID of the host. |
502
|
|
|
:param int bck_number: The number of the backup. |
503
|
|
|
|
504
|
|
|
:rtype: int |
505
|
|
|
""" |
506
|
|
|
sql = """ |
507
|
|
|
select bck_id |
508
|
|
|
from BKC_BACKUP |
509
|
|
|
where hst_id = ? |
510
|
|
|
and bck_number = ?""" |
511
|
|
|
|
512
|
|
|
bck_id = self.execute_singleton0(sql, (hst_id, bck_number)) |
513
|
|
|
if bck_id is None: |
514
|
|
|
self.execute_none('insert into BKC_BACKUP(hst_id, bck_number) values(?, ?)', (hst_id, bck_number)) |
515
|
|
|
bck_id = self.__last_rowid |
516
|
|
|
|
517
|
|
|
return bck_id |
518
|
|
|
|
519
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
520
|
|
|
def host_delete(self, host): |
521
|
|
|
""" |
522
|
|
|
Deletes cascading a host. |
523
|
|
|
|
524
|
|
|
:param str host: The name of the host. |
525
|
|
|
""" |
526
|
|
|
sql = """ |
527
|
|
|
select bck_id |
528
|
|
|
from BKC_HOST hst |
529
|
|
|
inner join BKC_BACKUP bck on bck.hst_id = hst.hst_id |
530
|
|
|
where hst.hst_name = ?""" |
531
|
|
|
|
532
|
|
|
rows = self.execute_rows(sql, (host,)) |
533
|
|
|
for row in rows: |
534
|
|
|
self.backup_delete(row['bck_id']) |
535
|
|
|
|
536
|
|
|
self.execute_none('delete from BKC_HOST where hst_name=?', (host,)) |
537
|
|
|
|
538
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
539
|
|
|
def host_get_obsolete(self): |
540
|
|
|
""" |
541
|
|
|
Selects host that are cloned but not longer in original. |
542
|
|
|
|
543
|
|
|
:rtype: list[dict] |
544
|
|
|
""" |
545
|
|
|
sql = """ |
546
|
|
|
select hst.hst_name |
547
|
|
|
from BKC_HOST hst |
548
|
|
|
left outer join BKC_ORIGINAL_BACKUP bob on bob.bob_host = hst.hst_name |
549
|
|
|
where bob.rowid is null |
550
|
|
|
order by hst.hst_name""" |
551
|
|
|
|
552
|
|
|
return self.execute_rows(sql) |
553
|
|
|
|
554
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
555
|
|
|
def import_csv(self, table_name, column_names, path, truncate=True, defaults=None): |
556
|
|
|
""" |
557
|
|
|
Import a CSV file into a table. |
558
|
|
|
|
559
|
|
|
:param str table_name: The name of the table. |
560
|
|
|
:param list column_names: The columns names. |
561
|
|
|
:param str path: The path to the CSV file. |
562
|
|
|
:param bool truncate:" If True the table will be truncated first. |
563
|
|
|
:param dict[str,*]|None defaults: The default values for columns not in the CSV file. |
564
|
|
|
""" |
565
|
|
|
if truncate: |
566
|
|
|
self.execute_none('delete from {}'.format(table_name)) |
567
|
|
|
|
568
|
|
|
default_values = [] |
569
|
|
|
if defaults: |
570
|
|
|
for column_name in defaults: |
571
|
|
|
column_names.append(column_name) |
572
|
|
|
default_values.append(defaults[column_name]) |
573
|
|
|
|
574
|
|
|
place_holders = [] |
575
|
|
|
for _ in range(0, len(column_names)): |
576
|
|
|
place_holders.append('?') |
577
|
|
|
|
578
|
|
|
sql = 'insert into {}({}) values ({})'.format(table_name, ', '.join(column_names), ', '.join(place_holders)) |
579
|
|
|
cursor = self.__connection.cursor() |
580
|
|
|
with open(path, 'rt') as csv_file: |
581
|
|
|
csv_reader = csv.reader(csv_file) |
582
|
|
|
for row in csv_reader: |
583
|
|
|
# Replace empty string with None. |
584
|
|
|
for index, field in enumerate(row): |
585
|
|
|
if field == '': |
586
|
|
|
row[index] = None |
587
|
|
|
|
588
|
|
|
if defaults: |
589
|
|
|
row.extend(default_values) |
590
|
|
|
cursor.execute(sql, row) |
591
|
|
|
cursor.close() |
592
|
|
|
|
593
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
594
|
|
|
def original_backup_insert(self, bob_host, bob_number, bob_end_time, bob_version, bob_level, bob_type): |
595
|
|
|
""" |
596
|
|
|
Inserts an original host backup. |
597
|
|
|
""" |
598
|
|
|
sql = """ |
599
|
|
|
insert into BKC_ORIGINAL_BACKUP( bob_host |
600
|
|
|
, bob_number |
601
|
|
|
, bob_end_time |
602
|
|
|
, bob_version |
603
|
|
|
, bob_level |
604
|
|
|
, bob_type ) |
605
|
|
|
values( ? |
606
|
|
|
, ? |
607
|
|
|
, ? |
608
|
|
|
, ? |
609
|
|
|
, ? |
610
|
|
|
, ? )""" |
611
|
|
|
self.execute_none(sql, (bob_host, bob_number, bob_end_time, bob_version, bob_level, bob_type)) |
612
|
|
|
|
613
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
614
|
|
|
def original_backup_get_stats(self): |
615
|
|
|
""" |
616
|
|
|
Select statistics of the original backups. |
617
|
|
|
|
618
|
|
|
:rtype: dict |
619
|
|
|
""" |
620
|
|
|
sql = """ |
621
|
|
|
select count(distinct bob_host) '#hosts' |
622
|
|
|
, count(*) '#backups' |
623
|
|
|
from BKC_ORIGINAL_BACKUP""" |
624
|
|
|
|
625
|
|
|
return self.execute_row1(sql) |
626
|
|
|
|
627
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
628
|
|
|
def original_backup_truncate(self): |
629
|
|
|
""" |
630
|
|
|
Truncates table BKC_ORIGINAL_BACKUP. |
631
|
|
|
""" |
632
|
|
|
self.execute_none('delete from BKC_ORIGINAL_BACKUP') |
633
|
|
|
|
634
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
635
|
|
|
def overview_get_stats(self): |
636
|
|
|
""" |
637
|
|
|
Select statistics of the original backups and cloned backups. |
638
|
|
|
|
639
|
|
|
:rtype: dict |
640
|
|
|
""" |
641
|
|
|
sql = """ |
642
|
|
|
select sum(case when cnt1=1 then 1 else 0 end) n_backups |
643
|
|
|
, sum(case when cnt1=1 and cnt2=1 then 1 else 0 end) n_cloned_backups |
644
|
|
|
, sum(case when cnt1=1 and cnt2=0 then 1 else 0 end) n_not_cloned_backups |
645
|
|
|
, sum(case when cnt1=0 and cnt2=1 then 1 else 0 end) n_obsolete_cloned_backups |
646
|
|
|
from |
647
|
|
|
( |
648
|
|
|
select sum(case when src=1 then 1 else 0 end) cnt1 |
649
|
|
|
, sum(case when src=2 then 1 else 0 end) cnt2 |
650
|
|
|
from ( |
651
|
|
|
select bob_host |
652
|
|
|
, bob_number |
653
|
|
|
, 1 src |
654
|
|
|
from BKC_ORIGINAL_BACKUP |
655
|
|
|
|
656
|
|
|
union all |
657
|
|
|
|
658
|
|
|
select hst.hst_name |
659
|
|
|
, bck.bck_number |
660
|
|
|
, 2 src |
661
|
|
|
from BKC_BACKUP bck |
662
|
|
|
join BKC_HOST hst on hst.hst_id = bck.hst_id |
663
|
|
|
) t |
664
|
|
|
group by bob_host |
665
|
|
|
, bob_number |
666
|
|
|
)""" |
667
|
|
|
|
668
|
|
|
return self.execute_row1(sql) |
669
|
|
|
|
670
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
671
|
|
|
def parameter_get_value(self, prm_code): |
672
|
|
|
""" |
673
|
|
|
Select the value of a parameter. |
674
|
|
|
|
675
|
|
|
:param str prm_code: The code of the parameter. |
676
|
|
|
|
677
|
|
|
:rtype: * |
678
|
|
|
""" |
679
|
|
|
return self.execute_singleton1('select prm_value from BKC_PARAMETER where prm_code = ?', (prm_code,)) |
680
|
|
|
|
681
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
682
|
|
|
def parameter_update_value(self, prm_code, prm_value): |
683
|
|
|
""" |
684
|
|
|
Sets the value of a parameter. |
685
|
|
|
|
686
|
|
|
:param str prm_code: The code of the parameter. |
687
|
|
|
:param str prm_value: The value of the parameter. |
688
|
|
|
""" |
689
|
|
|
return self.execute_none('update BKC_PARAMETER set prm_value = ? where prm_code = ?', (prm_value, prm_code)) |
690
|
|
|
|
691
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
692
|
|
|
def pool_delete_obsolete_original_rows(self): |
693
|
|
|
""" |
694
|
|
|
Deletes rows (i.e. files) from BKC_POOL that are not longer in the actual original pool. |
695
|
|
|
""" |
696
|
|
|
self.execute_none('delete from TMP_ID') |
697
|
|
|
|
698
|
|
|
sql = """ |
699
|
|
|
insert into TMP_ID(tmp_id) |
700
|
|
|
select bpl.bpl_id |
701
|
|
|
from BKC_POOL bpl |
702
|
|
|
left outer join IMP_POOL imp on imp.imp_inode = bpl.bpl_inode_original and |
703
|
|
|
imp.imp_dir = bpl.bpl_dir and |
704
|
|
|
imp.imp_name = bpl.bpl_name |
705
|
|
|
where bpl.bpl_inode_original is not null |
706
|
|
|
and imp.rowid is null""" |
707
|
|
|
self.execute_none(sql) |
708
|
|
|
|
709
|
|
|
sql = """ |
710
|
|
|
delete from BKC_POOL |
711
|
|
|
where bpl_id in (select tmp_id from TMP_ID)""" |
712
|
|
|
self.execute_none(sql) |
713
|
|
|
|
714
|
|
|
return self.execute_singleton1('select count(*) from TMP_ID') |
715
|
|
|
|
716
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
717
|
|
|
def pool_delete_row(self, bpl_id): |
718
|
|
|
""" |
719
|
|
|
Deletes a row from the pool metadata. |
720
|
|
|
|
721
|
|
|
:param int bpl_id: The rowid. |
722
|
|
|
""" |
723
|
|
|
self.execute_none('delete from BKC_POOL where bpl_id=?', (bpl_id,)) |
724
|
|
|
|
725
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
726
|
|
|
def pool_insert_new_original(self): |
727
|
|
|
""" |
728
|
|
|
Inserts new row into BKC_POOL based on an import from the original pool. |
729
|
|
|
""" |
730
|
|
|
self.execute_none('delete from TMP_POOL') |
731
|
|
|
|
732
|
|
|
sql = """ |
733
|
|
|
insert into TMP_POOL( tmp_inode |
734
|
|
|
, tmp_dir |
735
|
|
|
, tmp_name ) |
736
|
|
|
select tmp_inode |
737
|
|
|
, tmp_dir |
738
|
|
|
, tmp_name |
739
|
|
|
from |
740
|
|
|
( |
741
|
|
|
select bpl_inode_original tmp_inode |
742
|
|
|
, bpl_dir tmp_dir |
743
|
|
|
, bpl_name tmp_name |
744
|
|
|
, 1 src |
745
|
|
|
from BKC_POOL |
746
|
|
|
|
747
|
|
|
union all |
748
|
|
|
|
749
|
|
|
select imp_inode |
750
|
|
|
, imp_dir |
751
|
|
|
, imp_name |
752
|
|
|
, 2 src |
753
|
|
|
from IMP_POOL |
754
|
|
|
) t |
755
|
|
|
group by tmp_inode |
756
|
|
|
, tmp_dir |
757
|
|
|
, tmp_name |
758
|
|
|
having sum(case when src=1 then 1 else 0 end) < sum(case when src=2 then 1 else 0 end)""" |
759
|
|
|
self.execute_none(sql) |
760
|
|
|
|
761
|
|
|
sql = """ |
762
|
|
|
insert into BKC_POOL( bpl_inode_original |
763
|
|
|
, bpl_dir |
764
|
|
|
, bpl_name ) |
765
|
|
|
select tmp_inode |
766
|
|
|
, tmp_dir |
767
|
|
|
, tmp_name |
768
|
|
|
from TMP_POOL""" |
769
|
|
|
|
770
|
|
|
self.execute_none(sql) |
771
|
|
|
|
772
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
773
|
|
|
def pool_prepare_obsolete_clone_files(self): |
774
|
|
|
""" |
775
|
|
|
Prepares the clone pool files that are obsolete (i.e. not longer in the original pool). |
776
|
|
|
|
777
|
|
|
:rtype: int |
778
|
|
|
""" |
779
|
|
|
self.execute_none('delete from TMP_CLONE_POOL_OBSOLETE') |
780
|
|
|
|
781
|
|
|
sql = """ |
782
|
|
|
insert into TMP_CLONE_POOL_OBSOLETE( bpl_id |
783
|
|
|
, bpl_dir |
784
|
|
|
, bpl_name ) |
785
|
|
|
select bpl.bpl_id |
786
|
|
|
, bpl.bpl_dir |
787
|
|
|
, bpl.bpl_name |
788
|
|
|
from BKC_POOL bpl |
789
|
|
|
left outer join IMP_POOL imp on imp.imp_inode = bpl.bpl_inode_original and |
790
|
|
|
imp.imp_dir = bpl.bpl_dir and |
791
|
|
|
imp.imp_name = bpl.bpl_name |
792
|
|
|
where bpl.bpl_inode_clone is not null |
793
|
|
|
and imp.rowid is null""" |
794
|
|
|
|
795
|
|
|
self.execute_none(sql) |
796
|
|
|
|
797
|
|
|
sql = """ |
798
|
|
|
select count(*) |
799
|
|
|
from TMP_CLONE_POOL_OBSOLETE""" |
800
|
|
|
|
801
|
|
|
return self.execute_singleton1(sql) |
802
|
|
|
|
803
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
804
|
|
|
def pool_update_by_inode_original(self, bpl_inode_original, bpl_inode_clone, pbl_size, pbl_mtime): |
805
|
|
|
""" |
806
|
|
|
Sets the inode number of the clone, mtime and size of a file in the pool given a inode number of a file the the |
807
|
|
|
original pool. |
808
|
|
|
|
809
|
|
|
:param int bpl_inode_original: The inode number of a file file in the original pool. |
810
|
|
|
:param int bpl_inode_clone: The inode number of the pool file in the clone. |
811
|
|
|
:param int pbl_size: The size of the pool file. |
812
|
|
|
:param int pbl_mtime: The mtime of the pool file. |
813
|
|
|
""" |
814
|
|
|
sql = """ |
815
|
|
|
update BKC_POOL |
816
|
|
|
set bpl_inode_clone = ? |
817
|
|
|
, bpl_size = ? |
818
|
|
|
, bpl_mtime = ? |
819
|
|
|
where bpl_inode_original = ?""" |
820
|
|
|
|
821
|
|
|
self.execute_none(sql, (bpl_inode_clone, pbl_size, pbl_mtime, bpl_inode_original)) |
822
|
|
|
|
823
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
824
|
|
|
def pool_yield_obsolete_clone_files(self): |
825
|
|
|
""" |
826
|
|
|
Selects the clone pool files that are obsolete (i.e. not longer in the original pool). |
827
|
|
|
""" |
828
|
|
|
self.__connection.row_factory = DataLayer.dict_factory |
829
|
|
|
|
830
|
|
|
sql = """ |
831
|
|
|
select bpl_id |
832
|
|
|
, bpl_dir |
833
|
|
|
, bpl_name |
834
|
|
|
from TMP_CLONE_POOL_OBSOLETE""" |
835
|
|
|
|
836
|
|
|
cursor = self.__connection.cursor() |
837
|
|
|
cursor.execute(sql) |
838
|
|
|
while True: |
839
|
|
|
rows = cursor.fetchmany(10000) |
840
|
|
|
if not rows: |
841
|
|
|
cursor.close() |
842
|
|
|
raise StopIteration() |
843
|
|
|
yield rows |
844
|
|
|
|
845
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
846
|
|
|
def vacuum(self): |
847
|
|
|
""" |
848
|
|
|
Executes the vacuum command. |
849
|
|
|
""" |
850
|
|
|
self.execute_none('vacuum') |
851
|
|
|
|
852
|
|
|
# ---------------------------------------------------------------------------------------------------------------------- |
853
|
|
|
|