def create_fixtures(self):
db = database.LibDatabase()
con = db._connect_db()
# Open Cursor
cur = con.cursor()
param_vals = ['test_create_id-0ac6cb428b23', '2016-12-31 23:59:59',
'test_update_id-0ac6cb428b23', '2016-12-31 23:59:59',
0, '10.50.60.50',
'{"status": 1, "node_id": "node_id-11111111111", ' +\
'"tenant_name": "tenant_name001"}']
cur.execute(
"INSERT INTO NAL_GLOBAL_IP_MNG(create_id, " +
"create_date, update_id, update_date, delete_flg, " +
"global_ip, extension_info) VALUES " +
"(%s, %s, %s, %s, %s, %s, %s)", param_vals)
cur.execute('SELECT last_insert_id() FROM NAL_GLOBAL_IP_MNG')
global ID1
ID1 = cur.fetchall()[0][0]
con.commit()
# Close Cursor
cur.close()
# Close Database
con.close()
def execute(self, request_params, table_name):
# ------------------------------------------------------
# Initialaize
# ------------------------------------------------------
table_conf = getattr(tables_conf, table_name, {})
table_pkey = table_conf.get('primaryKey')
request_params_id = request_params.get('id')
# Create Instance(LibDatabase)
db = database.LibDatabase()
# ------------------------------------------------------
# Get Current Record(extension_info)
# ------------------------------------------------------
sel_sql = ''
sel_param_vals = []
sel_sql_where = ''
sel_sql = 'SELECT * '
sel_sql += ' FROM ' + table_name
if len(table_pkey) > 0 and len(request_params_id) == len(table_pkey):
sel_sql_where = ' WHERE '
sel_sql_where += ' = %s AND '.join(table_pkey) + ' = %s '
sel_param_vals = request_params_id
else:
raise exception.InvalidRequestParam
sel_sql += sel_sql_where
res = db.execute_sql(sel_sql, sel_param_vals)
if len(res) == 0:
raise exception.NotFound
# ------------------------------------------------------
# Delete Record
# ------------------------------------------------------
sql = 'DELETE FROM ' + table_name
sql_where = ''
param_vals = []
sql_where = ' WHERE '
sql_where += ' = %s AND '.join(table_pkey) + ' = %s '
param_vals += request_params_id
sql += sql_where
# Execute SQL
return db.execute_sql(sql, param_vals)
def destroy_fixtures(self):
db = database.LibDatabase()
con = db._connect_db()
# Open Cursor
cur = con.cursor()
# Execute SQL
param_vals = ['test_create_id-0ac6cb428b23']
cur.execute("DELETE FROM NAL_GLOBAL_IP_MNG WHERE " + "create_id = %s",
param_vals)
# Commit Transaction
con.commit()
# Close Cursor
cur.close()
# Close Database
con.close()
def execute(self, request_params, table_name):
# ------------------------------------------------------
# Initialaize
# ------------------------------------------------------
table_conf = getattr(tables_conf, table_name, {})
table_columns = table_conf.get('columns')
table_extension_columns = table_conf.get('extension_columns')
request_params_body = request_params.get('body')
now_date = datetime.datetime.today().strftime("%Y-%m-%d %H:%M:%S")
# Create Instance(LibDatabase)
db = database.LibDatabase()
# ------------------------------------------------------
# Insert Record
# ------------------------------------------------------
sql_columns = ''
sql_values = ''
param_vals = []
for column in table_columns:
if column != 'extension_info' \
and column in request_params_body:
val = request_params_body.get(column)
else:
if column == 'create_date' or column == 'update_date':
val = now_date
else:
continue
if len(sql_columns) > 0:
sql_columns += ','
sql_values += ','
sql_columns += column
sql_values += '%s'
param_vals.append(val)
extension_info = {}
for extension_column in table_extension_columns:
if extension_column in request_params_body:
extension_val = request_params_body.get(extension_column)
else:
extension_val = ''
extension_info[extension_column] = extension_val
sql_columns += ',extension_info'
sql_values += ',%s'
param_vals.append(json.dumps(extension_info))
sql = 'INSERT INTO ' + table_name
sql += '(' + sql_columns + ') VALUES (' + sql_values + ')'
# Execute SQL
res = db.execute_sql(sql, param_vals)
# Return Create Primary Key
return {'ID': res[0]['last_insert_id()']}
def execute(self, request_params, table_name):
# ------------------------------------------------------
# Initialaize
# ------------------------------------------------------
request_id = request_params.get('request_id', '')
log = logger.LibLogger(request_id)
# Get Table Definition
table_conf = getattr(tables_conf, table_name, {})
# Get ConfigValue
table_columns = table_conf.get('columns')
table_extension_columns = table_conf.get('extension_columns')
table_pkey = table_conf.get('primaryKey')
request_params_id = request_params.get('id', [])
request_params_query = request_params.get('query', {})
# Create Instance(LibDatabase)
db = database.LibDatabase()
# ------------------------------------------------------
# Select Record
# ------------------------------------------------------
# Get ColumnName
select_columns = []
for key in table_columns:
select_columns.append(self._edit_select_column(key, \
table_columns.get(key).get('type')))
# Edit Query
sql = 'SELECT ' + ','.join(select_columns)
sql += ' FROM ' + table_name
param_vals = []
sql_where = ''
# Map Parameters
if len(request_params_id) > 0:
if len(request_params_id) == len(table_pkey):
sql_where += ' = %s AND '.join(table_pkey) + ' = %s '
param_vals = request_params_id
else:
raise exception.InvalidRequestParam
if len(request_params_query) > 0:
for column in table_columns:
if column in request_params_query:
if len(sql_where) > 0:
sql_where += ' AND'
sql_where += ' ' + column + ' = %s'
param_vals.append(request_params_query[column])
if len(sql_where) > 0:
sql_where = ' WHERE ' + sql_where
# Add Where Phrase
sql += sql_where
# Add Where Phrase
sql += ' ORDER BY ID ASC'
# Execute SQL
res = db.execute_sql(sql, param_vals)
# Edit ResultData to Array
result = []
extension_info = []
for res_row in res:
row = {}
qurey_unmatch_flg = False
# Set Columns Info
for column_key, column_val in res_row.items():
if column_key == 'extension_info':
if column_val is None:
extension_info = {}
else:
extension_info = json.loads(column_val)
else:
if column_val is None:
row[column_key] = ''
else:
row[column_key] = column_val
# Set Ext Columns Info
for ext_column in table_extension_columns:
if ext_column in extension_info:
row[ext_column] = extension_info[ext_column]
else:
row[ext_column] = ''
# Check query matching
if ext_column in request_params_query and \
str(request_params_query[ext_column]) \
!= str(extension_info[ext_column]):
qurey_unmatch_flg = True
break
# Set Records
if qurey_unmatch_flg == False:
result.append(row)
# Return ResultData
return result
def execute(self, request_params, table_name):
# ------------------------------------------------------
# Initialaize
# ------------------------------------------------------
# Get ConfigValue
table_conf = getattr(tables_conf, table_name, {})
table_pkey = table_conf.get('primaryKey')
table_columns = table_conf.get('columns')
table_extension_columns = table_conf.get('extension_columns')
request_params_id = request_params.get('id')
request_params_body = request_params.get('body')
# Get Date
now_date = datetime.datetime.today().strftime("%Y-%m-%d %H:%M:%S")
# Create Instance(LibDatabase)
db = database.LibDatabase()
# ------------------------------------------------------
# Get Current Record(extension_info)
# ------------------------------------------------------
sel_sql = ''
sel_param_vals = []
sel_sql_where = ''
sel_sql = 'SELECT extension_info'
sel_sql += ' FROM ' + table_name
if len(table_pkey) > 0 and len(request_params_id) == len(table_pkey):
sel_sql_where = ' WHERE '
sel_sql_where += ' = %s AND '.join(table_pkey) + ' = %s '
sel_param_vals = request_params_id
else:
raise exception.InvalidRequestParam
sel_sql += sel_sql_where
res = db.execute_sql(sel_sql, sel_param_vals)
if len(res) == 0:
raise exception.NotFound
# ------------------------------------------------------
# Update Record
# ------------------------------------------------------
sql_columns = []
param_vals = []
for column in table_columns:
if column in request_params_body:
val = request_params_body.get(column)
else:
if column == 'update_date':
val = now_date
else:
continue
sql_columns.append(column)
param_vals.append(val)
# Set Extension Clumns
extension_info = json.loads(res[0]['extension_info'])
extension_info_update = {}
for extension_column in table_extension_columns:
if extension_column in request_params_body:
extension_val = request_params_body.get(extension_column)
else:
extension_val = extension_info.get(extension_column)
extension_info_update[extension_column] = extension_val
sql_columns.append('extension_info')
param_vals.append(json.dumps(extension_info_update))
sql = 'UPDATE ' + table_name + ' SET '
if len(sql_columns) > 0:
sql += ' = %s, '.join(sql_columns) + ' = %s'
sql_where = ''
sql_where = ' WHERE '
sql_where += ' = %s AND '.join(table_pkey) + ' = %s '
param_vals += request_params_id
sql += sql_where
# Execute SQL
return db.execute_sql(sql, param_vals)