· 9 years ago · May 19, 2017, 05:58 PM
1#!/usr/bin/env python3
2import os
3import sys
4import subprocess
5import argparse
6import psycopg2
7from app_config import cfg
8from colorama import Fore, Back, Style
9
10
11
12
13class Migrator:
14 conn = psycopg2.connect("dbname={0} user={1} password={2} host=127.0.0.1 port={3}".format(cfg['db_name'], cfg['db_user'], cfg['db_pass'], cfg['db_port']))
15 def get_version(self):
16 """ Gets version number from db """
17 try:
18 conn = self.conn
19 cur = conn.cursor()
20 cur.execute("SELECT version from version order by id asc limit 1;")
21 version = cur.fetchone()[0]
22 return version
23 except Exception as err:
24 print("{1}\nERROR Could not get current version number.\nStacktrace ==>\n{0}\n{1}".format(err, "~"*50))
25 raise err
26
27 def update_version_number(self, new_version):
28 """ Updates version number in version table"""
29 conn = self.conn
30 cur = conn.cursor()
31 cur.execute("UPDATE version SET version = '{0}' WHERE id = 1;".format(new_version))
32 conn.commit()
33
34
35 def execute_sql(self, scripts):
36 """ Executes SQL script line by line, exits if error is encountered"""
37 conn = self.conn
38 cur = conn.cursor()
39 try:
40 for script in scripts:
41 print(Fore.CYAN + "EXECUTING SCRIPT {0}".format(script))
42 with open("{0}{1}".format(cfg['sql_dir'], script), 'r') as s:
43 sql_content = s.read()
44 statements = [stmt.strip() for stmt in sql_content.split(";") if stmt.strip() != '' and not stmt.startswith('--')]
45 for cmd in statements:
46 print(Fore.WHITE+cmd)
47 cur.execute(cmd)
48 conn.commit()
49 print(Fore.GREEN+"{0}........PASSED".format(script))
50 self.update_version_number(script[-1])
51 except Exception as e:
52 print(Fore.RED+"...FAILED ! Investigate the stacktrace above")
53 print(e)
54 finally:
55 #reset terminal colours
56 print(Fore.WHITE+"")
57
58
59 def ensure_version_exists(self):
60 check_table = """ SELECT EXISTS (
61 SELECT 1
62 FROM pg_tables
63 WHERE schemaname = 'public'
64 AND tablename = 'version'
65 ); """
66 create_query = """ CREATE TABLE version(
67 id serial not null,
68 version varchar(50) not null
69 );
70 COMMIT;
71 insert into version (version) values (''); """
72 conn = self.conn
73 cur = conn.cursor()
74 cur.execute(check_table)
75 table_exists = cur.fetchone()
76 print(table_exists[0])
77 if not table_exists[0]:
78 cur.execute(create_query)
79 conn.commit()
80
81
82
83 def do_update(self, args):
84 self.ensure_version_exists()
85 if args['file']:
86 if os.path.exists("{0}{1}".format(cfg['sql_dir'], args['file'])):
87 to_execute = [f for f in os.listdir('{0}'.format(cfg['sql_dir'])) if f == args['file']]
88 else:
89 sys.exit(Fore.RED + "ERROR : Invalid script name")
90 elif args['all']:
91 to_execute = sorted([f for f in os.listdir('{0}'.format(cfg['sql_dir']))])
92 elif args['current']:
93 from_version = self.get_version()
94 to_execute = sorted([f for f in os.listdir('{0}'.format(cfg['sql_dir'])) if f > from_version])
95
96 print(Fore.YELLOW+"Scripts to execute => "+str(to_execute)+Fore.WHITE)
97 if len(to_execute) > 0:
98 self.execute_sql(to_execute)
99 self.conn.close()
100
101if __name__ == '__main__':
102 parser = argparse.ArgumentParser(description="Updates DB schemas from given version to latest")
103 parser.add_argument('-c','--current', action='store_true', help="upgrade starting from current version" ,required = False)
104 parser.add_argument('-a','--all', action='store_true', help="run all the sql scripts", required = False)
105 parser.add_argument('-f','--file', type=str, help="Run a single file by its name" ,required=False)
106 args = vars(parser.parse_args())
107 if args['current'] and args['all']:
108 sys.exit(Fore.RED + "ERROR : Specify either --current or --all, not both")
109 migrator = Migrator()
110 migrator.do_update(args)