def create_topic_entity(self, topic: Topic) -> None:
try:
self.connect()
entity_name = as_table_name(topic)
# noinspection SqlType
script = f'''
CREATE TABLE {entity_name} (
\tid_ BIGINT,
{build_columns(topic)}
{build_aggregate_assist_column(topic)}
{build_version_column(topic)}
\ttenant_id_ VARCHAR(50),
\tinsert_time_ DATETIME,
\tupdate_time_ DATETIME,
{build_unique_indexes(topic)}
{build_indexes(topic)}
\tINDEX (tenant_id_),
\tINDEX (insert_time_),
\tINDEX (update_time_),
\tPRIMARY KEY (id_)
)'''
self.connection.execute(text(script))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
finally:
self.close()
def create_topic_entity(self, topic: Topic) -> None:
try:
self.connect()
entity_name = as_table_name(topic)
# noinspection SqlType
script = f'''
CREATE TABLE {entity_name} (
\tid_ NUMBER(20),
{build_columns(topic)}
{build_aggregate_assist_column(topic)}
{build_version_column(topic)}
\ttenant_id_ VARCHAR2(50),
\tinsert_time_ DATE,
\tupdate_time_ DATE,
\tCONSTRAINT pk_{entity_name} PRIMARY KEY (id_)
)'''
self.connection.execute(text(script))
# try to add index
for unique_index_script in build_unique_indexes_script(topic):
self.connection.execute(text(unique_index_script))
for index_script in build_indexes_script(topic):
self.connection.execute(text(index_script))
self.connection.execute(
text(f'CREATE INDEX i_{entity_name}_tenant_id_ ON {entity_name} (tenant_id_)'))
self.connection.execute(
text(f'CREATE INDEX i_{entity_name}_insert_time_ ON {entity_name} (insert_time_)'))
self.connection.execute(
text(f'CREATE INDEX i_{entity_name}_update_time_ ON {entity_name} (update_time_)'))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
finally:
self.close()
def update_topic_entity(self, topic: Topic, original_topic: Topic) -> None:
"""
1. drop no column,\n
2. factor indexes from original topic are dropped,\n
3. factor indexes from topic are created,\n
4. compatible column type changes are applied,\n
5. any exception is ignored.
"""
try:
self.connect()
entity_name = as_table_name(topic)
self.connection.execute(
text(f"CALL DROP_INDEXES_ON_TOPIC_CHANGED('{entity_name}')"))
# try to change column anyway, ignore when failed
for column_script in build_columns_script(topic, original_topic):
try:
self.connection.execute(text(column_script))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
# try to add index
for unique_index_script in build_unique_indexes_script(topic):
try:
self.connection.execute(text(unique_index_script))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
for index_script in build_indexes_script(topic):
try:
self.connection.execute(text(index_script))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
try:
# noinspection SqlResolve
self.connection.execute(
text(
f'ALTER TABLE {as_table_name(topic)} ADD INDEX (tenant_id_)'
))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
try:
# noinspection SqlResolve
self.connection.execute(
text(
f'ALTER TABLE {as_table_name(topic)} ADD INDEX (insert_time_)'
))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
try:
# noinspection SqlResolve
self.connection.execute(
text(
f'ALTER TABLE {as_table_name(topic)} ADD INDEX (update_time_)'
))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
finally:
self.close()
def append_topic_to_trino(self, topic: Topic) -> None:
self.connection.insert_one(
self.find_document('_schema'), {
'table':
as_table_name(topic.name),
'fields':
ArrayHelper(build_to_trino_fields(topic)).map(
lambda x: x.to_dict()).to_list()
})
def drop_topic_entity(self, topic_name: str) -> None:
entity_name = as_table_name(topic_name)
try:
self.connect()
self.connection.drop_collection(entity_name)
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
finally:
self.close()
def drop_topic_entity(self, topic_name: str) -> None:
entity_name = as_table_name(topic_name)
try:
self.connect()
# noinspection SqlResolve
self.connection.execute(text(f'DROP TABLE {entity_name}'))
except Exception as e:
logger.error(e, exc_info=True, stack_info=True)
finally:
self.close()
def build_by_regular(topic: Topic) -> MongoDocument:
return MongoDocument(
name=as_table_name(topic),
columns=[
create_pk(TopicDataColumnNames.ID.value),
*create_columns(topic.factors),
create_tuple_id_column(TopicDataColumnNames.TENANT_ID.value,
nullable=False),
create_datetime(TopicDataColumnNames.INSERT_TIME.value,
nullable=False),
create_datetime(TopicDataColumnNames.UPDATE_TIME.value,
nullable=False)
])
def build_by_aggregation(topic: Topic) -> MongoDocument:
return MongoDocument(
name=as_table_name(topic),
columns=[
create_pk(TopicDataColumnNames.ID.value),
*create_columns(topic.factors),
create_json(TopicDataColumnNames.AGGREGATE_ASSIST.value),
create_tuple_id_column(TopicDataColumnNames.TENANT_ID.value,
nullable=False),
create_int(TopicDataColumnNames.VERSION.value),
create_datetime(TopicDataColumnNames.INSERT_TIME.value,
nullable=False),
create_datetime(TopicDataColumnNames.UPDATE_TIME.value,
nullable=False)
])
def build_by_raw(topic: Topic) -> MongoDocument:
return MongoDocument(
name=as_table_name(topic),
columns=[
create_pk(TopicDataColumnNames.ID.value),
*create_columns(
ArrayHelper(
topic.factors).filter(lambda x: x.flatten).to_list()),
create_json(TopicDataColumnNames.RAW_TOPIC_DATA.value),
create_tuple_id_column(TopicDataColumnNames.TENANT_ID.value,
nullable=False),
create_datetime(TopicDataColumnNames.INSERT_TIME.value,
nullable=False),
create_datetime(TopicDataColumnNames.UPDATE_TIME.value,
nullable=False),
])
def build_by_regular(topic: Topic) -> Table:
columns = [
create_pk(TopicDataColumnNames.ID.value, Integer),
*create_columns(topic.factors),
create_tuple_id_column(TopicDataColumnNames.TENANT_ID.value,
nullable=False),
create_datetime(TopicDataColumnNames.INSERT_TIME.value,
nullable=False),
create_datetime(TopicDataColumnNames.UPDATE_TIME.value, nullable=False)
]
return Table(
as_table_name(topic),
meta_data,
*columns,
extend_existing=True,
include_columns=ArrayHelper(columns).map(lambda x: x.name).to_list())
def build_columns_script(topic: Topic, original_topic: Topic) -> List[str]:
entity_name = as_table_name(topic)
original_factors: Dict[str, Factor] = ArrayHelper(original_topic.factors) \
.to_map(lambda x: x.name.strip().lower(), lambda x: x)
# noinspection SqlResolve
def build_column_script(factor: Tuple[Factor, Optional[Factor]]) -> str:
current_factor, original_factor = factor
if original_factor is None:
return f'ALTER TABLE {entity_name} ADD COLUMN {ask_column_name(factor[0])} {ask_column_type(factor[0])}'
elif current_factor.flatten and not original_factor.flatten:
return f'ALTER TABLE {entity_name} ADD COLUMN {ask_column_name(factor[0])} {ask_column_type(factor[0])}'
else:
return f'ALTER TABLE {entity_name} ALTER COLUMN {ask_column_name(factor[0])} {ask_column_type(factor[0])}'
if is_raw_topic(topic):
factors = ArrayHelper(topic.factors) \
.filter(lambda x: x.flatten) \
.to_list()
else:
factors = topic.factors
columns = ArrayHelper(factors) \
.map(lambda x: (x, original_factors.get(x.name.strip().lower()))) \
.map(build_column_script) \
.to_list()
if is_raw_topic(topic) and not is_raw_topic(original_topic):
columns.append(
f'ALTER TABLE {entity_name} ADD COLUMN data_ NVARCHAR(MAX)')
if is_aggregation_topic(
topic) and not is_aggregation_topic(original_topic):
columns.append(
f'ALTER TABLE {entity_name} ADD COLUMN aggregate_assist_ NVARCHAR(1024)'
)
columns.append(
f'ALTER TABLE {entity_name} ADD COLUMN version_ DECIMAL(8)')
return columns
def drop_topic_from_trino(self, topic: Topic) -> None:
self.connection.delete_many(self.find_document('_schema'),
{'table': as_table_name(topic.name)})
def build_literal(tables: List[Table], a_literal: Literal, build_plain_value: Callable[[Any], Any] = None):
if isinstance(a_literal, ColumnNameLiteral):
if is_blank(a_literal.entityName):
# table name is not given
if len(tables) == 0:
# in subquery, no table passed-in
return literal_column(a_literal.columnName)
elif len(tables) != 1:
raise UnexpectedStorageException(
'Available table must be unique when entity name is missed in column name literal.')
else:
# noinspection PyPropertyAccess
return tables[0].c[a_literal.columnName]
else:
table_name = as_table_name(a_literal.entityName)
table = ArrayHelper(tables).find(lambda x: x.name == table_name)
if table is None:
raise UnexpectedStorageException(f'Entity[{a_literal.entityName}] not found.')
return table.c[a_literal.columnName]
elif isinstance(a_literal, ComputedLiteral):
operator = a_literal.operator
if operator == ComputedLiteralOperator.ADD:
return ArrayHelper(a_literal.elements) \
.map(lambda x: build_literal(tables, x, to_decimal)) \
.reduce(lambda prev, current: prev + current, None)
elif operator == ComputedLiteralOperator.SUBTRACT:
return ArrayHelper(a_literal.elements) \
.map(lambda x: build_literal(tables, x, to_decimal)) \
.reduce(lambda prev, current: prev - current, None)
elif operator == ComputedLiteralOperator.MULTIPLY:
return ArrayHelper(a_literal.elements) \
.map(lambda x: build_literal(tables, x, to_decimal)) \
.reduce(lambda prev, current: prev * current, None)
elif operator == ComputedLiteralOperator.DIVIDE:
return ArrayHelper(a_literal.elements) \
.map(lambda x: build_literal(tables, x, to_decimal)) \
.reduce(lambda prev, current: prev / current, None)
elif operator == ComputedLiteralOperator.MODULUS:
return ArrayHelper(a_literal.elements) \
.map(lambda x: build_literal(tables, x, to_decimal)) \
.reduce(lambda prev, current: prev % current, None)
elif operator == ComputedLiteralOperator.YEAR_OF:
# year is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.year(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.HALF_YEAR_OF:
# month is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return case((
func.month(build_literal(tables, a_literal.elements[0])) <= 6, DateTimeConstants.HALF_YEAR_FIRST.value),
else_=DateTimeConstants.HALF_YEAR_SECOND.value
)
elif operator == ComputedLiteralOperator.QUARTER_OF:
# quarter is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.quarter(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.MONTH_OF:
# month is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.month(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.WEEK_OF_YEAR:
# week is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.week(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.WEEK_OF_MONTH:
# weekofmonth is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.weekofmonth(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.DAY_OF_MONTH:
# day is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.day(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.DAY_OF_WEEK:
# weekday is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.weekday(build_literal(tables, a_literal.elements[0]))
elif operator == ComputedLiteralOperator.CASE_THEN:
elements = a_literal.elements
cases = ArrayHelper(elements).filter(lambda x: isinstance(x, Tuple)) \
.map(lambda x: (build_criteria_statement(tables, x[0]), build_literal(tables, x[1]))) \
.to_list()
anyway = ArrayHelper(elements).find(lambda x: not isinstance(x, Tuple))
if anyway is None:
return case(*cases)
else:
return case(*cases, else_=build_literal(tables, anyway))
elif operator == ComputedLiteralOperator.CONCAT:
literals = ArrayHelper(a_literal.elements).map(lambda x: build_literal(tables, x)).to_list()
literal_count = len(literals)
if literal_count == 1:
return literals[0]
elif literal_count == 2:
return func.concat(literals[0], literals[1])
else:
return ArrayHelper(a_literal.elements[2:]) \
.reduce(lambda prev, x: func.concat(prev, x), func.concat(literals[0], literals[1]))
elif operator == ComputedLiteralOperator.YEAR_DIFF:
# yeardiff is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.yeardiff(
build_literal(tables, a_literal.elements[0]), build_literal(tables, a_literal.elements[1]))
elif operator == ComputedLiteralOperator.MONTH_DIFF:
# monthdiff is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.monthdiff(
build_literal(tables, a_literal.elements[0]), build_literal(tables, a_literal.elements[1]))
elif operator == ComputedLiteralOperator.DAY_DIFF:
# datediff is a customized function, which can be found in data-scripts folder
# make sure each topic storage have this function
return func.datediff(
build_literal(tables, a_literal.elements[0]), build_literal(tables, a_literal.elements[1]))
elif operator == ComputedLiteralOperator.FORMAT_DATE:
return func.to_char(
build_literal(tables, a_literal.elements[0]), translate_date_format(a_literal.elements[1]))
elif operator == ComputedLiteralOperator.CHAR_LENGTH:
return func.length(func.ifnull(build_literal(tables, a_literal.elements[0]), ''))
else:
raise UnsupportedComputationException(f'Unsupported computation operator[{operator}].')
elif isinstance(a_literal, datetime):
return func.to_date(a_literal.strftime('%Y-%m-%d %H:%M:%S'), 'YYYY-MM-DD HH24:MI:SS')
elif isinstance(a_literal, date):
return func.to_date(a_literal.strftime('%Y-%m-%d'), 'YYYY-MM-DD')
elif isinstance(a_literal, time):
return func.to_date(a_literal.strftime('%H:%M:%S'), 'HH24:MI:SS')
elif build_plain_value is not None:
return build_plain_value(a_literal)
else:
# a value, return itself
return a_literal