source: setup_db.py @ 149

Last change on this file since 149 was 149, checked in by pvkouteren, 11 years ago

Minor fixes to ibidas2.py and setup_db.py
Added test file patricktest.py

  • Property svn:mime-type set to text/plain
File size: 11.7 KB
Line 
1#!/usr/bin/env python
2"""
3This script will set up the database
4@author: Patrick van Kouteren <H.J.W.vanKouteren@student.tudelft.nl>
5@version: 1.1
6"""
7
8import config
9import container
10from container import postgres
11from psycopg2 import ProgrammingError, OperationalError
12import psycopg2
13import sys
14import time
15
16'''
17Setup database connection (we assume this is working).
18Note that we use the db 'template1' as this is a db ALWAYS present.
19We need a db to open a psycopg2 connection.
20'''
21_ConfigDefault = {
22    "setup.db_name":                "ibidas",
23    "setup.db_user":                "postgres",
24    "setup.db_user_pw":             "Put the postgres password here",
25    "setup.db_host":                "127.0.0.1",
26    "setup.db_port":                "5432",
27    "setup.authenticated_mode":     "yes",
28    "setup.setup_type":             "new"
29}
30
31setupconfig = config.loadConfig('setup.ini', _ConfigDefault)
32
33''' Extra security: Check if the database exists. If so, prompt if the user's sure to delete it '''
34
35try:
36    conn = psycopg2.connect("dbname='"+setupconfig['setup.db_name']+"' " \
37                               "user='"+setupconfig['setup.db_user']+"' "\
38                               "host='"+setupconfig['setup.db_host']+"' "\
39                               "port='"+setupconfig['setup.db_port']+"' "\
40                               "password='"+setupconfig['setup.db_user_pw']+"'");
41    conn.close()
42    db_already_exists = True
43except:
44    db_already_exists = False
45
46if (db_already_exists is True):
47    try:
48        yn = sys.argv[1]
49    except:
50        yn = ""
51    while(not (yn == "y" or yn == "n")):
52        yn = raw_input("The database you've specified in setup.ini already exist. By proceeding, you'll "\
53                       "completely clear and delete this existing database. Are you sure you want to do this? (y/n)\n")
54    if (yn == "n"):
55        print "=== Database setup script overview ==="
56        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] User aborted script. Database was not overwritten."
57        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Please supply your preferred data in setup.ini and run this setup again"
58        print "=== End of database setup script ==="
59        sys.exit()
60
61       
62''' If the database already exists, the user has decided to clear it. Let's do some work! '''
63
64try:
65    cd = postgres.openConnection(setupconfig['setup.db_host'],\
66                                 setupconfig['setup.db_port'],"template1",\
67                                 setupconfig['setup.db_user'],setupconfig['setup.db_user_pw'])
68except OperationalError:
69    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Could not open database connection." \
70          "Please check the setup.ini config file and run setup.py again"
71    sys.exit()
72
73
74''' Now we have an open database connection. Let's start! '''
75
76print "=== Database setup script ==="
77''' Obtain current isolation level '''           
78il = cd.isolation_level
79''' For dropping db, we need isolation level 0 '''
80cd.set_isolation_level(0)
81cursor = cd.getSingletonCursor()
82try:
83    cursor.execute("DROP DATABASE " + setupconfig['setup.db_name'] + ";")
84    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Dropping old database.."
85except ProgrammingError:
86    ''' The database did not exist '''
87    pass
88except OperationalError:
89    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
90    sys.exit()
91
92
93''' Drop the user ibidas if it exists '''
94
95try:
96    cursor.execute("DROP USER ibidas;")
97    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Dropping user 'ibidas'.."
98except ProgrammingError:
99    ''' The database did not exist '''
100    pass
101#except OperationalError:
102#    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
103#    sys.exit()
104
105   
106''' Create the database '''
107
108try:
109    cursor.execute("CREATE DATABASE " + setupconfig['setup.db_name'] + " WITH ENCODING 'UTF8';")
110    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Database " + setupconfig['setup.db_name'] + " created."
111except OperationalError:
112    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
113    sys.exit()
114   
115cd.set_isolation_level(il)
116cd.close()
117
118
119''' Now that the database itself is created, connect and create the tables '''
120
121cd = postgres.openConnection(setupconfig['setup.db_host'],\
122            setupconfig['setup.db_port'],setupconfig['setup.db_name'],\
123            setupconfig['setup.db_user'],setupconfig['setup.db_user_pw'])
124cursor = cd.getSingletonCursor()
125
126print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Creating tables.."
127filename = "sql/preimport.sql" # The file which contains the sql to create the db schema
128file = open(filename, 'r')
129sql = file.read()
130
131''' Obtain current isolation level and save for later. We need level 0 for now. '''           
132il = cd.isolation_level
133cd.set_isolation_level(0)
134
135''' Execute the contents of the sql file '''
136try:
137    cursor.execute(sql)
138except ProgrammingError:
139    print "ProgrammingError!"
140    pass
141file.close()
142
143print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Tables created."
144
145'''
146We need a 'superuser', so let's create it.
147'''
148
149#if (setupconfig['setup.authenticated_mode'] == "no"):
150# create one 'superuser'
151ibidas_user_sql = "CREATE USER ibidas WITH PASSWORD 'ibidas' NOCREATEDB CREATEUSER CREATEROLE;"
152try:
153    cursor.execute(ibidas_user_sql)
154except ProgrammingError:
155    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create superuser ibidas. Please run this script again!"
156    sys.exit()
157
158''' Apply user restrictions on the tables '''
159print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Applying user restrictions.."
160try:
161    cursor.execute("REVOKE ALL ON DATABASE " + setupconfig['setup.db_name'] + " FROM PUBLIC")
162    cursor.execute("GRANT CONNECT ON DATABASE " + setupconfig['setup.db_name'] + " TO PUBLIC")
163except ProgrammingError:
164    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Error applying restrictions. Please run this script again!"
165    sys.exit()
166
167
168''' Create views and apply restrictions on them '''
169print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Creating views.."
170
171tables = cd._getTables()
172existing = []
173for table in tables:
174    try:
175        cursor.execute("SELECT source_id FROM " + table['name'] +";")
176        existing.append(table['name'])
177    except ProgrammingError:
178        pass
179
180
181'''
182The variable existing now contains all tables for which a view should be defined.
183These tables contain a link to set (by means of source_id) and so can be related to mydatasets_view
184'''
185
186# Define mydatasets_view
187mydatasets_view_sql = "CREATE VIEW mydatasets_view AS SELECT source_id FROM set_pg_group WHERE grosysid IN (SELECT grosysid FROM pg_group WHERE groname IN (SELECT applicable_roles.role_name FROM information_schema.applicable_roles WHERE applicable_roles.grantee::name = (SELECT \"current_user\"() as \"current_user\")));"
188mydatasets_rights_sql = "GRANT SELECT ON TABLE mydatasets_view TO PUBLIC; GRANT SELECT ON TABLE pg_group TO PUBLIC; GRANT SELECT ON TABLE information_schema.applicable_roles TO PUBLIC;"
189try:
190    cursor.execute(mydatasets_view_sql)
191except ProgrammingError:
192    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create the mydatsets_view. Please check the query and run this script again!"
193    sys.exit()
194 
195for viewtable in existing:
196    viewtable_view = viewtable + "_view"
197    if viewtable == 'set':
198        view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE set_id IN (SELECT source_id FROM mydatasets_view);"
199    else:
200        view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE source_id IN (SELECT * FROM mydatasets_view);"
201    rights_sql = "GRANT SELECT ON TABLE " + viewtable_view + " TO PUBLIC"
202    try:
203        cursor.execute(view_sql)
204        cursor.execute(rights_sql)
205    except ProgrammingError:
206        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create the " + viewtable_view +". Please check the query and run this script again!"
207        sys.exit()
208print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Created " + str(len(existing) + 1) + " views.."
209
210
211''' Return to the normal isolation level '''
212cd.set_isolation_level(il)
213
214
215''' Create the config file based on the authenticated_mode variable '''
216
217print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Checking Ibidas operating mode.."
218
219if (setupconfig['setup.authenticated_mode'] == "yes"):
220    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Authenticated mode is ON. The " \
221          "configuration file 'ibidas.ini' will contain the login credentials of the setup " \
222          "configuration file. Please create one or more users and change these credentials " \
223          "for your own system's security purposes!"
224    _dbConfigDefault = {
225        "database.name":                setupconfig['setup.db_name'],
226        "database.user":                setupconfig['setup.db_user'],
227        "database.password":            setupconfig['setup.db_user_pw'],
228        "database.host":                setupconfig['setup.db_host'],
229        "database.port":                setupconfig['setup.db_port'],
230        "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode'],
231        "ibidas.query_logging":         'on',
232        "ibidas.log_level":             'DEBUG'
233    }
234    dbconfig = config.loadConfig('ibidas.ini', _dbConfigDefault)
235    config.writeConfig('ibidas.ini', _dbConfigDefault)
236elif (setupconfig['setup.authenticated_mode'] == "no"):
237    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Authenticated mode is OFF. The " \
238          "configuration file 'ibidas.ini' will contain a default superuser 'ibidas'. " \
239          "Please note that although ibidas operates quicker in this mode, it is intended only for " \
240          "instances containing openly available datasets!"
241    _dbConfigDefault = {
242        "database.name":                setupconfig['setup.db_name'],
243        "database.user":                'ibidas',
244        "database.password":            'ibidas',
245        "database.host":                setupconfig['setup.db_host'],
246        "database.port":                setupconfig['setup.db_port'],
247        "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode'],
248        "ibidas.query_logging":         'on',
249        "ibidas.log_level":             'DEBUG'
250    }
251    dbconfig = config.loadConfig('ibidas.ini', _dbConfigDefault)
252    config.writeConfig('ibidas.ini', _dbConfigDefault)
253else:
254    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] No valid value for 'authenticated_mode' was " \
255          "found in the setup.ini file. Please supply a valid argument (yes / no) and run setup again."
256    sys.exit()
257
258''' Close connections '''
259   
260cursor.close()
261cd.close()
262
263''' Change the setup.ini setup_type variable '''
264print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Resetting setup.ini variable 'setup_type' to prevent clearing this database next time."
265setupconfig['setup.setup_type'] = 'none'
266config.writeConfig('setup.ini', setupconfig)
267
268print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Setup_db script finished."
269print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] We hope you'll enjoy using Ibidas. For support, please visit https://wiki.nbic.nl/index.php/Ibidas"
270print "=== End of database setup script ==="
271   
272'''
273End of script. We have:
274Dropped and created the database and filled it with tables and views
275Defined access rights and created the configuration file.
276'''
Note: See TracBrowser for help on using the repository browser.