1#!/usr/bin/env python3
2
3"""New low-level database access library with proper support for multiple connections.
4
5For example:
6
7from chart.db.connection import db_connect
8db_orbhisto = db_connect('ORB_HISTO')
9db_orbhisto.execute('xxx')
10db_orbhisto.commit()
11
12
13"""
14
15# from typing import Protocol # lets keep Python 3.6 compatibility for now
16# as the system Centos 7.9 install uses it. Once Concorde is upgraded
17# we're on at least 3.9 everywhere
18
19from chart.project import settings
20from chart.common.traits import name_of_thing
21from chart.db.settings import DatabaseEngine
22
23import logging
24
25# Cache of DB connection names against connections
26cache = {}
27
28logger = logging.getLogger()
29
30class Connection:
31# class Connection(Protocol):
32 """For now, just so we can type hint database connections."""
33 pass
34
35
36class ProxyConnection:
37 """Late binding to the Connection object functions.
38 Connections are often created at module scope, but the user may override the default
39 database connection via command line argument. So we make sure the actual database
40 engine is not determined until first use.
41
42 Connections are created using either a table name (in which case is converted internally
43 to a connection name after checking for split tables) or a connection name directly.
44
45 """
46
47 def __init__(self, table=None, connection=None):
48 self.table = table
49 self.connection = connection
50 self._imp = None
51
52 def __str__(self):
53 return 'ProxyConnection<{eng}>'.format(eng=self.imp().engine.name)
54
55 def conn_name(self):
56 """Compute the name of the required database connection."""
57 from chart.db.model.table import TableInfo
58 if self.table is not None:
59 # client has requested a connection by table name; look up which db connection
60 # is correct for that table
61 if self.connection is not None:
62 raise ValueError('Cannot set table and connection name')
63
64 name = settings.DB_SPLIT_TABLES.get(name_of_thing(self.table))
65 if name is None:
66 # allow use of '*' global wildcard in DB_SPLIT_TABLES to refer to all TS tables
67 name = settings.DB_SPLIT_TABLES.get('*')
68 if name is None:
69 # if no split table used, take the primary connection
70 # name = settings.DB_NAME
71 # name = 'default'
72 name = settings.DATABASES['default']['DB_NAME']
73 # logger.debug('global name ' + str(name))
74 # if isinstance(self.table, str):
75 # if the user passed in a string tablename make sure it is real
76 # TableInfo(self.table)
77
78 else:
79 logger.info('Accessing {table} using {conn}'.format(
80 table=name_of_thing(self.table), conn=name))
81
82 elif self.connection is not None:
83 name = self.connection
84
85 else:
86 name = settings.DATABASES['default']['DB_NAME']
87 # name = settings.DB_NAME
88 # name = 'default'
89
90 # print('conn name ' + str(name))
91 return name
92
93 def imp(self):
94 """Create our underlying implementation (a CHART database engine object)."""
95 if self._imp is not None:
96 return self._imp
97
98 name = self.conn_name()
99 result = cache.get(name)
100 if result is None:
101 DB = settings.DATABASES.get(name)
102 if DB is None:
103 raise ValueError('No such connection {name}'.format(
104 name=name))
105
106 if DB['ENGINE'] == 'django.db.backends.oracle':
107 from chart.db.drivers.oracle import OracleConnection
108 result = OracleConnection(name)
109
110 elif DB['ENGINE'] == 'django.db.backends.sqlite3':
111 from chart.db.drivers.sqlite import SQLiteConnection
112 result = SQLiteConnection(name)
113
114 elif DB['ENGINE'] == 'django.db.backends.postgresql_psycopg2' or\
115 DB['ENGINE'] == 'django.db.backends.postgresql':
116 from chart.db.drivers.postgresql import PostgresqlConnection
117 result = PostgresqlConnection(name)
118
119 elif DB['ENGINE'] == 'django.db.backends.mysql':
120 from chart.db.drivers.mysql import MySQLConnection
121 result = MySQLConnection(name)
122
123 else:
124 raise ValueError('Engine {eng} not supported'.format(eng=DB['ENGINE']))
125
126 # assert hasattr(result, 'desc')
127 # assert result.desc is not None
128 cache[name] = result
129
130 self._imp = result
131 return result
132
133 def execute(self, sql, *args, **kwargs):
134 """Call though to implementation."""
135 # this should work but doesn't if the client passes in a **bindvars dict
136 return self.imp().query(sql, *args, **kwargs)
137
138 def executemany(self, sql, rows):
139 """Call though to implementation."""
140 # this should work but doesn't if the client passes in a **bindvars dict
141 return self.imp().executemany(sql, rows)
142
143 def query(self, sql, *args, **kwargs):
144 """Call though to implementation."""
145 # this should work but doesn't if the client passes in a **bindvars dict
146 return self.imp().query(sql, *args, **kwargs)
147 # if len(args) == 0:
148 # return self.imp().query(sql, **kwargs)
149
150 # else:
151 # return self.imp().query(sql, *args)
152
153 def query_insert_with_return(self, sql, field, *args, **kwargs):
154 """Call though to implementation."""
155 return self.imp().query_insert_with_return(sql, field, *args, **kwargs)
156
157 def prepared_cursor(self, sql, wrapped=False):
158 """Create a prepared cursor. If the underlying implementation does not support
159 prepared cursors it will return a Python object that behaves like one.
160 The `wrapped` is set the return object will be a Python wrapper which converts
161 exceptions coming from the driver layer into standard CHART exceptions."""
162 return self.imp().prepared_cursor(sql, wrapped)
163
164 def delayed_prepared_cursor(self, sql):
165 """There is no point using this function under the new db scheme and this should not be
166 used. """
167 return self.imp().prepared_cursor(sql)
168
169 def begin(self):
170 """Call though to implementation."""
171 self.imp().begin()
172
173 def commit(self):
174 """Call though to implementation."""
175 self.imp().commit()
176
177 def rollback(self):
178 """Call though to implementation."""
179 self.imp().rollback()
180
181 def setinputsizes(self, cursor, types):
182 """Call though to implementation."""
183 self.imp().setinputsizes(cursor, types)
184
185 def setarraysize(self, cursor, size):
186 """Call though to implementation."""
187 self.imp().setarraysize(cursor, size)
188
189 def cursor(self, wrapped=True):
190 """Call though to implementation."""
191 return self.imp().cursor(wrapped)
192
193 @property
194 def engine(self):
195 """Allow client code to retrieve our engine type without actually instantiating
196 the connection."""
197 params = settings.DATABASES[self.conn_name()]
198 if params['ENGINE'] == 'django.db.backends.oracle':
199 return DatabaseEngine.ORACLE
200
201 elif params['ENGINE'] == 'django.db.backends.sqlite3':
202 return DatabaseEngine.SQLITE
203
204 elif params['ENGINE'] == 'django.db.backends.postgresql_psycopg2' or\
205 params['ENGINE'] == 'django.db.backends.postgresql':
206 return DatabaseEngine.POSTGRESQL
207
208 else:
209 return None
210
211 @property
212 def params(self):
213 """Allow access to the original configuration dictionary."""
214 return self.imp().params
215
216 def all_tables(self):
217 """Return list of all tables existing in the database."""
218 return self.imp().all_tables()
219
220 def read_table(self, table):
221 """Populate a database table node object."""
222 return self.imp().read_table(table)
223
224 def cursor_statement(self, cursor):
225 """Retrieve a string object giving the SQL statement from a cursor."""
226 return self.imp().cursor_statement()
227
228 def extract_bytes(self, value):
229 """Convert `value`, an item in a cursor, into a Python bytes object."""
230 return self.imp().extract_bytes(value)
231
232 def as_json(self, value):
233 """Convert `value`, a value from a cursor in a JSONB column, to a dict."""
234 return self.imp().as_json(value)
235
236def db_connect(table=None, connection=None, late_bind=False):
237 """Might be faster to do the caching and table lookup here."""
238 return ProxyConnection(table, connection)