1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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
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
203
204
205
206
207
208
209
210
211
212
213
|
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
'''
This script join two tuptime db files into an other one. It works as follows:
- Takes two paths of db files and an output file as destination of the join.
- Look for first btime on both files and take the one with the older value as starting DB
- Look on starting DB for last register and sum (btime + uptime)
- Look on the newer file for a btime greater than previous sum value
- Insert registers that match on destination file DB
# tuptime_join.py /path/to/old.db /path/to/new.db -d /path/to/joined.db
Maybe after upgrade your computer and install new stuff, you want to continue with the registers
that you have before. It is possible to join the new ones into the old ones without any awkward jump.
Please, see the following example:
Join registers of two db files into other:
tuptime_join.py /backup/old/tuptime.db /var/lib/tuptime/tuptime.db -d /tmp/tt.db
Check if all is ok:
tuptime --noup -t -f /tmp/tt.db
Check owner (usually tuptime:tuptime) and copy modified file to right location. Re-check owner:
ls -al /var/lib/tuptime/tuptime.db
cp /tmp/tt.db /var/lib/tuptime/tuptime.db
ls -al /var/lib/tuptime/tuptime.db
'''
import sys, argparse, locale, signal, logging, sqlite3
from shutil import copyfile
__version__ = '1.2.1'
# Terminate when SIGPIPE signal is received
signal.signal(signal.SIGPIPE, signal.SIG_DFL)
# Set locale to the users default settings (LANG env. var)
locale.setlocale(locale.LC_ALL, '')
def get_arguments():
"""Get arguments from command line"""
parser = argparse.ArgumentParser()
parser.add_argument(
'files',
metavar='FILE',
nargs=2,
type=str,
help='files to join'
)
parser.add_argument(
'-d', '--dest',
dest='dest',
default=False,
action='store',
type=str,
required=True,
help='destination file to store join'
)
parser.add_argument(
'-v', '--verbose',
dest='verbose',
action='count',
default=0,
help='verbose output (vv=detail)'
)
arg = parser.parse_args()
if arg.verbose:
logging.basicConfig(format='%(levelname)s:%(message)s', level=logging.DEBUG)
logging.info('Version: %s', (__version__))
logging.info('Arguments: %s', vars(arg))
return arg
def order_files(arg):
"""Identify older file"""
# Open file0 DB
db_conn0 = sqlite3.connect(arg.files[0])
db_conn0.row_factory = sqlite3.Row
db_conn0.set_trace_callback(logging.debug)
conn0 = db_conn0.cursor()
# Open file1 DB
db_conn1 = sqlite3.connect(arg.files[1])
db_conn1.row_factory = sqlite3.Row
db_conn1.set_trace_callback(logging.debug)
conn1 = db_conn1.cursor()
# Check if DBs have the old format
for conn, fname in ((conn0, arg.files[0]), (conn1, arg.files[1])):
columns = [i[1] for i in conn.execute('PRAGMA table_info(tuptime)')]
if 'rntime' and 'slptime' and 'bootid' not in columns:
logging.error('DB format outdated on file: %', str(fname))
sys.exit(1)
# Check older file
conn0.execute('select btime from tuptime where rowid = (select min(rowid) from tuptime)')
conn1.execute('select btime from tuptime where rowid = (select min(rowid) from tuptime)')
# File with large btime is newer
if (conn0.fetchone()[0]) > (conn1.fetchone()[0]):
return arg.files[1], arg.files[0]
return arg.files[0], arg.files[1]
def main():
"""Main logic"""
arg = get_arguments()
f_old, f_new = order_files(arg)
print('Old source file: \t' + str(f_old))
print('New source file:\t' + str(f_new))
# Use old file as starting DB. Copy as destination file.
copyfile(f_old, arg.dest)
fl0 = {'path': arg.dest} # file0 is destination file
fl1 = {'path': f_new} # file1 is source newer file
print('Destination file: \t' + str(fl0['path']))
# Open file0 DB
db_conn0 = sqlite3.connect(fl0['path'])
db_conn0.row_factory = sqlite3.Row
db_conn0.set_trace_callback(logging.debug)
conn0 = db_conn0.cursor()
# Open file1 DB
db_conn1 = sqlite3.connect(fl1['path'])
db_conn1.row_factory = sqlite3.Row
db_conn1.set_trace_callback(logging.debug)
conn1 = db_conn1.cursor()
# Get all rows from source file0 and print raw rows
conn0.execute('select rowid as startup, * from tuptime')
db_rows = conn0.fetchall()
print('\nDestination rows before:\t' + str(len(db_rows)))
if arg.verbose > 1:
for row in db_rows:
print("\t", end='')
print(dict(row))
# Get last startup, btime and uptime from last row on file0 to calculate offbtime
conn0.execute('select rowid, btime, uptime from tuptime where rowid = (select max(rowid) from tuptime)')
fl0['startup'], fl0['btime'], fl0['uptime'] = conn0.fetchone()
fl0['offbtime'] = fl0['btime'] + fl0['uptime']
# Get all rows from file1 where btime is greater than file0 offbtime
conn1.execute('select rowid as startup, * from tuptime where btime >?', (fl0['offbtime'],))
db_rows = conn1.fetchall()
print('\nRows to add from newer file: \t' + str(len(db_rows)))
# Insert rows on file0
for row in db_rows:
# At first row on file1, set offbtime, endst and downtime to last row on file0
if row == db_rows[0]:
print(' Fix shutdown values on row: ' + str(fl0['startup']))
fl0['downtime'] = row['btime'] - fl0['offbtime']
conn0.execute('update tuptime set offbtime =?, endst = 1, downtime =? '
'where rowid = (select max(rowid) from tuptime)', (fl0['offbtime'], fl0['downtime']))
if arg.verbose:
print('\toffbtime = ' + str(fl0['offbtime']))
print('\tendst = 1')
print('\tdowntime = ' + str(fl0['downtime']))
# Add registers to file0
print(' Adding startup row: ' + str(row['startup']))
conn0.execute('insert into tuptime values (?,?,?,?,?,?,?,?,?)',
(row['bootid'], row['btime'], row['uptime'], row['rntime'], row['slptime'], row['offbtime'], row['endst'], row['downtime'], row['kernel']))
if arg.verbose:
print('\tbootid = ' + str(row['bootid']))
print('\tbtime = ' + str(row['btime']))
print('\tuptime = ' + str(row['uptime']))
print('\trntime = ' + str(row['rntime']))
print('\tslptime = ' + str(row['slptime']))
print('\toffbtime = ' + str(row['offbtime']))
print('\tendst = ' + str(row['endst']))
print('\tdowntime = ' + str(row['downtime']))
print('\tkernel = ' + str(row['kernel']))
# At last row, empty offbtime, endst and downtime
if row == db_rows[-1]:
print(' Fix shutdown values on last row')
conn0.execute('update tuptime set offbtime = NULL, endst = 0, downtime = NULL where rowid = (select max(rowid) from tuptime)')
db_conn0.commit()
# Print raw rows
conn0.execute('select rowid as startup, * from tuptime')
db_rows = conn0.fetchall()
print('\nDestination rows after: \t' + str(len(db_rows)))
if arg.verbose > 1:
for row in db_rows:
print("\t", end='')
print(dict(row))
db_conn0.close()
db_conn1.close()
print('\nDone.')
if __name__ == "__main__":
main()
|
