Passed
Push — master ( c7fbd6...227ddb )
by P.R.
03:02 queued 31s
created

backuppc_clone.DataLayer.DataLayer.overview_get_stats()   A

Complexity

Conditions 1

Size

Total Lines 34
Code Lines 3

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 1
eloc 3
nop 1
dl 0
loc 34
rs 10
c 0
b 0
f 0
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