1#!/usr/bin/env python3
2
3"""Oracle specific support functions for db_manage."""
4
5from enum import Enum
6from typing import List
7
8from chart.project import settings
9from chart.db.ddl.engine_base import Engine
10from chart.common.traits import Datatype
11from chart.common.traits import TimePrecision
12from chart.common.traits import name_of_thing
13from chart.common.traits import Trait
14from chart.project import SID
15from chart.db.model.table import TableInfo
16
17class Layout(Enum):
18 """Partitioning schemes."""
19
20 SINGLE = 'single' # just use default namespace
21 SIMPLE = 'simple' # misc, unknown, stats, scids
22 CFID_OGSID = 'cfid-ogsid' # chart msg tce
23 ARGUS = 'argus' # eps mpstar ope + val
24 SID = 'sid' # split into one datafile per sid
25
26
27def layout(conn):
28 """Retrieve current layout option for default db connection."""
29 res = conn.params.get('layout')
30 if res is not None:
31 return res
32
33 conn.params['layout'] = Layout(conn.params['LAYOUT'])
34 return conn.params['layout']
35
36
37class OracleEngine(Engine):
38 """DDL creation for Oracle databases."""
39
40 FUNC_DIR = settings.ORACLE_FUNC_DIR
41
42 def __init__(self, db_conn):
43 super(OracleEngine, self).__init__()
44 self.db_conn = db_conn
45
46 def schema(self):
47 """Read current schema."""
48 schema = self.db_conn.params.get('SCHEMA')
49 if schema is None:
50 raise ValueError('Current DB connection does not have a SCHEMA defined')
51
52 return schema.upper()
53
54 def with_schema(self, name, quoted=True):
55 """Return "name" if no schema is set, otherwise schema."name"."""
56 # quoted = False
57 q = '"' if quoted else ''
58
59 if self.db_conn.params.get('LAYOUT') is Layout.ARGUS:
60 return '{q}&SchemaOwner{q}.{q}{name}{q}'.format(name=name, q=q)
61
62 elif self.db_conn.params.get('SCHEMA') is None:
63 return '{q}{name}{q}'.format(name=name, q=q)
64
65 else:
66 return '{schema}.{q}{name}{q}'.format(schema=self.schema(), name=name, q=q)
67
68 def table_name(self, table):
69 """Convert `name` into a suitable table name for this database."""
70 return self.with_schema(name_of_thing(table))
71
72 def view_name(self, name):
73 """Convert `name` into a suitable view name for this database."""
74 return self.with_schema(name)
75
76 def trigger_name(self, name):
77 """Convert `name` into a suitable trigger name for this database."""
78 return self.with_schema(name)
79
80 def index_name(self, name):
81 """Convert `name` into a suitable index name for this database."""
82 return self.with_schema(name)
83
84 def field_name(self, field):
85 """Convert `name` into a suitable field name for this database."""
86 return '"' + name_of_thing(field) + '"'
87
88 def tablespace_name(self, ts):
89 """Convert `ts` into a suitable tablespace name for this database."""
90 return ts.upper()
91
92 def sql_index(self,
93 sql,
94 index_name,
95 table_name,
96 fields,
97 unique=False,
98 primary_key=False,
99 # partitioned=False,
100 # tablespace=False,
101 compress=None):
102 """Generate SQL code to create an index."""
103
104 tablespace = ' TABLESPACE {t}'.format(t=self.db_conn.params['TABLESPACE_INDEX'])
105
106 if compress is not None:
107 compress = ' COMPRESS {cols}'.format(cols=compress)
108
109 else:
110 compress = ''
111
112 if primary_key:
113 # build an index first
114 sql.append(
115 'CREATE UNIQUE INDEX {index} ON {table} ({fields}){compress}{tablespace}'.format(
116 index=self.with_schema(index_name),
117 table=self.with_schema(table_name),
118 # fields=','.join('{f}'.format(f=f) for f in fields),
119 fields=','.join('"{f}"'.format(f=f) for f in fields),
120 compress=compress,
121 tablespace=self.tablespace_name(tablespace)))
122
123 # then use it so set a primary key
124 sql.append(
125 'ALTER TABLE {table} ADD CONSTRAINT {index} PRIMARY KEY ({fields}) '
126 'USING INDEX {schema_index}'.format(
127 schema_index=self.with_schema(index_name),
128 table=self.with_schema(table_name),
129 index=index_name,
130 # fields=','.join('{f}'.format(f=f) for f in fields),
131 fields=','.join('"{f}"'.format(f=f) for f in fields)))
132
133 else:
134 sql.append('CREATE {unique}INDEX {index} ON '
135 '{table_name} ({fields}){tablespace}{compress}'.format(
136 index=self.index_name(index_name),
137 table_name=self.table_name(table_name),
138 # fields=','.join(fields),
139 fields=','.join('"' + f + '"' for f in fields),
140 unique='UNIQUE ' if unique else '',
141 tablespace=self.tablespace_name(tablespace),
142 compress=compress))
143
144 def sql_table_postfix(self,
145 sql: List[str],
146 db_tablename: str,
147 table_info: TableInfo):
148 """Add any post-creation configuration needed for the table."""
149 pass
150
151 def sql_field(self,
152 field_info,
153 name_override=None,
154 datatype_override=None,
155 length_override=None):
156 """Return DDL fragment to create a single field."""
157 name = field_info.name
158 if name_override is not None:
159 name = name_override
160
161 datatype = field_info.datatype
162 if datatype_override is not None:
163 datatype = datatype_override
164
165 length = field_info.length
166 if length_override is not None:
167 length = length_override
168
169 if datatype is Datatype.STRING:
170 sqltype = 'VARCHAR2({length})'.format(length=length)
171
172 elif datatype is Datatype.BINARY:
173 # cx_Oracle seems to need field size twice the length
174 sqltype = 'VARCHAR2({length} byte)'.format(length=(2 * length))
175
176 elif datatype is Datatype.INT:
177 if length <= 32:
178 digits = 11
179
180 else:
181 digits = 20
182
183 sqltype = 'NUMBER({digits})'.format(digits=digits)
184
185 elif datatype is Datatype.UINT:
186 if length <= 32:
187 digits = 11
188
189 else:
190 digits = 20
191
192 sqltype = 'NUMBER({digits}) CHECK ({name} >= 0)'.format(
193 name=self.field_name(name),
194 digits=digits)
195
196 elif datatype is Datatype.BOOL:
197 sqltype = 'NUMBER(1) CHECK ({name} BETWEEN 0 and 1)'.format(
198 name=self.field_name(name))
199
200 elif datatype is Datatype.DATETIME:
201 if field_info.time_precision is TimePrecision.MICROSECOND:
202 sqltype = 'TIMESTAMP(6)'
203
204
205 elif field_info.time_precision is TimePrecision.SECOND:
206 sqltype = 'TIMESTAMP(0)'
207
208 else: # field_info.time_precision is TimePrecision.MILLISECOND:
209 sqltype = 'TIMESTAMP(3)'
210
211 # else:
212 # raise ValueError('Unknown time precision {p}'.format(p=field_info.time_precision))
213
214 elif datatype is Datatype.FLOAT:
215 sqltype = 'BINARY_DOUBLE'
216
217 elif datatype is Datatype.UNICODE:
218 sqltype = 'NVARCHAR2({length})'.format(length=length)
219
220 elif datatype is Datatype.XML:
221 sqltype = 'XMLTYPE'
222
223 # Oracle 21 adds a binary JSON datatype but we're on Oracle 19 so we use plain text as
224 # storage
225 # The user has to manually add something like:
226 # ALTER TABLE S3_OWNER.TC_STORE ADD CONSTRAINT TC_STORE_JSON_JSON_PAYLOAD_CHECK CHECK
227 # (payload IS json);
228 # To get server JSON functionality
229 elif datatype is Datatype.JSONB:
230 # 4000 chars ought to be enough for anybody
231 sqltype = 'VARCHAR2(4000)'
232
233 else:
234 raise ValueError('Unknown datatype {d}'.format(d=datatype))
235
236 return '\t{name} {sqltype}{default}{null}'.format(
237 name=self.field_name(name),
238 sqltype=sqltype,
239 default=' DEFAULT ' + field_info.default if field_info.default is not Trait.NO_DEFAULT else '',
240 null='' if field_info.allow_null else ' NOT NULL')
241
242 def sql_table_trigger(self, sql, table_name, autoincrement=None, timestamp=None):
243 """Generate SQL code to create a trigger and other objects to make the field
244 `autoincrement` an autoincrement field and/or make `timestamp` a field that is
245 automatically set to the row creation time.
246 """
247
248 # exit if no trigger is required for this table
249 if autoincrement is None and timestamp is None:
250 return
251
252 if autoincrement is not None:
253 sql.append("""DECLARE
254 i INTEGER;
255 BEGIN
256 SELECT count(*) INTO i FROM all_catalog
257 WHERE owner='{schema}' AND table_name='{seq_name_base}' AND table_type='SEQUENCE';
258 IF i = 0 THEN
259 EXECUTE IMMEDIATE 'CREATE SEQUENCE {seq_name}';
260 END IF;
261 END;""".format(schema=self.schema(), # schema needed to seq exist test
262 seq_name_base=table_name + '_SQ',
263 seq_name=self.with_schema(table_name + '_SQ')))
264
265 sql.append("""CREATE OR REPLACE TRIGGER {trigger_name}
266 BEFORE INSERT ON {table_name}
267 FOR EACH ROW
268 BEGIN
269 SELECT {seq_name}.nextval INTO :new.{field_name} FROM dual;
270 END;""".format(seq_name=self.with_schema(table_name + '_SQ'),
271 trigger_name=self.trigger_name(table_name + '_GEN'),
272 field_name=autoincrement,
273 table_name=self.table_name(table_name)))
274
275 # @memoized
276 def all_views(self):
277 """List all views in database."""
278 return set(
279 row[0] for row in self.db_conn.query(
280 "SELECT view_name FROM all_views WHERE owner='{schema}'".format(
281 schema=self.schema())))
282
283 # @memoized
284 def all_tables(self):
285 """List all tables in database. synonyms.py needs it."""
286 return set(row[0] for row in self.db_conn.query(
287 "SELECT table_name FROM all_tables WHERE owner='{schema}'".format(
288 schema=self.schema())))
289
290 # @memoized
291 # def all_indexes():
292 # """List all indexes in database"""
293 # return set(x[0] for x in
294 # db.query("SELECT index_name FROM all_indexes WHERE owner='{schema}'".format(
295 # schema=schema())))
296
297 # @memoized
298 # def all_triggers():
299 # """List all triggers in database"""
300 # return set(row[0] for row in db.query("SELECT trigger_name FROM all_triggers "
301 # "WHERE owner='{schema}'".format(schema=schema())))
302
303 # @memoized
304 # def all_sequences():
305 # """List all sequences in database"""
306 # return set(row[0] for row in db.query(
307 # "SELECT sequence_name FROM all_sequences "
308 # "WHERE sequence_owner='{schema}'".format(schema=schema())))
309
310 # @memoized
311 # def all_functions():
312 # """List all user defined functions in database"""
313 # return set(row[0] for row in db.query(
314 # "SELECT name FROM all_source WHERE owner='{schema}' "
315 # "AND type='FUNCTION' GROUP BY name".format(schema=schema())))
316
317 def sys_suffix(self):
318 """String to be added to the end of a SYS table definition, after the close brackets."""
319 # assert 'LAYOUT' in settings.DB, 'Use a database with a LAYOUT option for creating tables'
320
321 # if layout(self.db_conn) is Layout.SINGLE:
322 # return ' TABLESPACE {space}'.format(space=self.db_conn.params['TABLESPACE'])
323
324 # elif layout(self.db_conn) is Layout.SIMPLE:
325 # return ' TABLESPACE {ts}'.format(ts=self.db_conn.params['TABLESPACE'])
326
327 # elif layout(self.db_conn) is Layout.CFID_OGSID:
328 # return ' TABLESPACE {pre}misc'.format(pre=self.db_conn.params['TABLESPACE_PREFIX'])
329
330 # return ''
331
332 if self.db_conn.params['TABLESPACE_SYS'] is None:
333 return ''
334
335 else:
336 return ' TABLESPACE {t}'.format(t=self.db_conn.params['TABLESPACE_SYS'])
337
338 def ap_partitions(self):
339 """Canned text to add the partitioning rules."""
340 if layout(self.db_conn) is Layout.SINGLE:
341 return ' TABLESPACE {space}'.format(space=self.db_conn.params['TABLESPACE'])
342
343 elif layout(self.db_conn) is Layout.SIMPLE:
344 ts_prefix = self.db_conn.params['TABLESPACE_PREFIX']
345 return """
346PCTFREE 0
347STORAGE (
348 PCTINCREASE 0
349 MINEXTENTS 1
350)
351PARTITION BY LIST (sid_num) (
352{parts}
353 PARTITION {misc} VALUES (DEFAULT) TABLESPACE {misc}
354)
355ENABLE ROW MOVEMENT""".format(
356 parts='\n'.join(
357 '\tPARTITION {name} VALUES ({value}) TABLESPACE {name},'.format(
358 name=self.tablespace_name('{prefix}{scid}'.format(prefix=ts_prefix, scid=sid.scid)),
359 value=sid.sid_num) for sid in SID.all()),
360 misc=self.tablespace_name('{prefix}misc'.format(prefix=ts_prefix)))
361
362 elif layout(self.db_conn) is Layout.CFID_OGSID:
363 # partitioning schema for tcdbs6 new schema
364 # for s in ('MSG1', 'MSG2', 'MSG3', 'OTHER'):
365 # pass
366
367 return """
368 PCTFREE 0
369 STORAGE (
370 PCTINCREASE 0
371 MINEXTENTS 1
372 )
373 PARTITION BY LIST (SCID)
374 SUBPARTITION BY LIST (OGSID)
375 (
376 PARTITION p_msg1 VALUES ('MSG1')
377 (
378 SUBPARTITION sp_msg1_prim VALUES ('PRIM') TABLESPACE {prefix}msg1_prim,
379 SUBPARTITION sp_msg1_back VALUES (DEFAULT) TABLESPACE {prefix}msg1_back
380 ),
381 PARTITION p_msg2 VALUES ('MSG2')
382 (
383 SUBPARTITION sp_msg2_prim VALUES ('PRIM') TABLESPACE {prefix}msg2_prim,
384 SUBPARTITION sp_msg2_back VALUES (DEFAULT) TABLESPACE {prefix}msg2_back
385 ),
386 PARTITION p_msg3 VALUES ('MSG3')
387 (
388 SUBPARTITION sp_msg3_prim VALUES ('PRIM') TABLESPACE {prefix}msg3_prim,
389 SUBPARTITION sp_msg3_back VALUES (DEFAULT) TABLESPACE {prefix}msg3_back
390 ),
391 PARTITION p_msg4 VALUES ('MSG4')
392 (
393 SUBPARTITION sp_msg4_prim VALUES ('PRIM') TABLESPACE {prefix}msg4_prim,
394 SUBPARTITION sp_msg4_back VALUES (DEFAULT) TABLESPACE {prefix}msg4_back
395 ),
396 PARTITION p_other VALUES (DEFAULT)
397 (
398 SUBPARTITION p_other_other VALUES (DEFAULT) TABLESPACE {prefix}other
399 )
400 )
401 ENABLE ROW MOVEMENT""".format(prefix=self.db_conn.params['TABLESPACE_PREFIX'])
402
403 # SUBPARTITION TEMPLATE
404 # (
405 # SUBPARTITION prim VALUES ('PRIM'),
406 # SUBPARTITION back VALUES ('BACK'),
407 # SUBPARTITION other VALUES (DEFAULT)
408 # )
409
410 elif layout(self.db_conn) is Layout.ARGUS:
411 tablespace_names = ('_DATA_LARGE', '_INDX_LARGE')
412 subpartition_min_year = 2005 # first year to create partitioning rule for
413 subpartition_max_year = 2018 # last year to create partitioning rule for
414 partition_scids = ('M01', 'M02', 'M03', 'N18', 'N19') # scids for individual top level
415 # partitions
416 # scids for "other" partition
417 partition_other_scids = ('T01', 'T02', 'T03', 'MH2', 'MH4', 'MH5')
418 partitions = [" PARTITION p_{scid} VALUES ('{scid}')".format(
419 scid=scid) for scid in partition_scids]
420 subpartitions = [
421 " SUBPARTITION s{year:04} VALUES LESS THAN (TO_TIMESTAMP('"
422 "{year1:04}-01-01', 'YYYY-MM-DD')) TABLESPACE {tablespace}".format(
423 year=year,
424 tablespace=self.with_schema('_DATA_{year:04}'.format(year=year), quoted=False),
425 year1=year + 1) for year in range(subpartition_min_year,
426 subpartition_max_year + 1)]
427 other_partition = " PARTITION p_TEST VALUES ({scids})".format(
428 scids=','.join("'" + scid + "'" for scid in partition_other_scids))
429
430 return """
431 NOCOMPRESS
432 TABLESPACE {tablespace}
433 PCTFREE 0
434 STORAGE (
435 PCTINCREASE 0
436 MINEXTENTS 1
437 )
438 PARTITION BY LIST (SCID)
439 SUBPARTITION BY RANGE (SENSING_TIME)
440 SUBPARTITION TEMPLATE
441 (
442 {subpartitions} )
443 (
444 {partitions},
445 {other_partition}
446 )
447 ENABLE ROW MOVEMENT""".format(
448 tablespace=self.with_schema(tablespace_names[0], quoted=False),
449 partitions=',\n'.join(partitions),
450 subpartitions=',\n'.join(subpartitions),
451 other_partition=other_partition)
452
453 else:
454 return ''
455
456 def stats_partitions(self):
457 """Suffix to table definition for stats tables."""
458 if layout(self.db_conn) is Layout.SINGLE:
459 return ' TABLESPACE {space}'.format(space=self.db_conn.params['TABLESPACE'])
460
461 elif layout(self.db_conn) is Layout.SIMPLE:
462 ts = self.db_conn.params.get(
463 'TABLESPACE_STATS', self.db_conn.params.get('TABLESPACE'))
464 if ts is None:
465 return ''
466
467 else:
468 return ' TABLESPACE {ts}'.format(ts=ts)
469
470 elif layout(self.db_conn) is Layout.CFID_OGSID:
471 return """
472 TABLESPACE {prefix}stats
473 PCTFREE 0
474 STORAGE (
475 PCTINCREASE 0
476 MINEXTENTS 1
477 )""".format(prefix=self.db_conn.params['TABLESPACE_PREFIX'])
478
479 if layout(self.db_conn) is Layout.ARGUS:
480 tablespace_names = ('_DATA_LARGE', '_INDX_LARGE')
481 return """
482 NOCOMPRESS
483 TABLESPACE {schema}.{tablespace}
484 PCTFREE 0
485 STORAGE (
486 PCTINCREASE 0
487 MINEXTENTS 1
488 )""".format(schema=self.schema(),
489 tablespace=tablespace_names[0])
490
491 else:
492 return ''