def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\Tables"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
if os.path.isfile(file_name):
fh.file_str_replace(file_name, 'GO', '')
fh.file_str_replace(file_name, 'go', '')
pattern = re.compile(
r"(?<=dbo.).+?(?=.table.sql)") # 取两个字符串中间的字符
table_name = pattern.findall(f)[0]
header = '''IF NOT EXISTS(SELECT TOP 1 1 FROM sys.tables t WITH(NOLOCK)
WHERE SCHEMA_NAME(schema_id) = 'DBO' AND OBJECT_NAME(object_id) =\'''' + table_name + '''\' AND type = 'U')
BEGIN
'''
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
pattern = re.compile(r'(FK)')
if pattern.search(context):
print(f)
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\Tables"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
if os.path.isfile(file_name):
fh.file_str_replace(file_name, 'GO', '')
fh.file_str_replace(file_name, 'go', '')
pattern = re.compile(
r"(?<=dbo.).+?(?=.table.sql)") # 取两个字符串中间的字符
table_name = pattern.findall(f)[0]
header = '''IF NOT EXISTS(SELECT TOP 1 1 FROM sys.tables t WITH(NOLOCK)
WHERE SCHEMA_NAME(schema_id) = 'DBO' AND OBJECT_NAME(object_id) =\'''' + table_name + '''\' AND type = 'U')
BEGIN
'''
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
# print(context)
tail = '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Table ''' + table_name + '''...')
END
GO'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, encoding='utf-8')
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables\Keys"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\ForeignKeys"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read pkey files
if os.path.isfile(file_name) and re.search(r'fkey', f) is not None:
try:
fh.file_str_replace(file_name, 'GO', '')
fh.file_str_replace(file_name, 'go', '')
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name,encoding_name)
ex_table = re.compile(r"\[.*?\]+")
l = ex_table.findall(context)
if l[0].replace('[','').replace(']','') != 'dbo':
print(1/0)
table_name = l[1].replace('[','').replace(']','')
fk_name = l[2].replace('[','').replace(']','')
# if re.search('FK_' , fk_name) is None:
# fh.check_obj_name(fk_name, f)
header = '''IF NOT EXISTS
(
SELECT TOP 1 1
FROM sys.foreign_keys fk
WHERE OBJECT_ID = OBJECT_ID(N\'''' + fk_name + '''\')
AND fk.parent_object_id = OBJECT_ID(N\'''' + table_name + '''\')
)
BEGIN
'''
tail = '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Foreign Key '''+ fk_name+'''...')
END;
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {} {}'.format(f,table_name,pk_name))
except:
print(f)
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Programmability\Stored Procedures"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\StoredProcedures"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
if f == "dbo.UserStory59.proc.sql": # except this script
continue
file_name = os.path.join(src_dir, f)
# only read trigger files
if os.path.isfile(file_name):
try:
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name,encoding_name)
ex = re.compile(r"\[.*?\]+")
l = ex.findall(context)
sp_name = l[1].replace('[', '').replace(']', '')
"""debug code below 3 rows"""
# fh.check_obj_name(sp_name, f)
header = '''IF EXISTS(SELECT TOP 1 1 FROM sys.procedures i with(nolock)
WHERE i.name = \'''' + sp_name + '''\')
BEGIN
DROP PROCEDURE ''' + sp_name + '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Dropped Stored Procedure '''+sp_name+'''...')
END;
GO
'''
tail = '''
GO
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Stored Procedure '''+sp_name+'''...')
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {}'.format(f,trigger_name))
except:
print(f)
pass
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Programmability\Functions"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\\UserFunctions"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read trigger files
if os.path.isfile(file_name):
try:
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name,encoding_name)
ex = re.compile(r"\[.*?\]+")
l = ex.findall(context)
function_name = l[1].replace('[', '').replace(']', '')
"""debug code below 3 rows"""
fh.check_obj_name(function_name, f)
header = '''
IF OBJECT_ID(\'''' + function_name + '''\') IS NOT NULL
BEGIN
DROP FUNCTION ''' + function_name + '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Dropped Function '''+ function_name+'''...')
END;
GO
'''
tail = '''
GO
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Function '''+ function_name+'''...')
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {}'.format(f,trigger_name))
except:
print(f)
pass
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables\Indexes"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\Indexes"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read ukey files
if re.search(r'index', f) is not None:
try:
fh.file_str_replace(file_name, 'GO', '')
fh.file_str_replace(file_name, 'go', '')
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
ex = re.compile(r"\[.*?\]+")
l = ex.findall(context)
idx_name = l[0].replace('[', '').replace(']', '')
if idx_name == 'dbo':
print(f)
fh.check_obj_name(idx_name, f)
header = '''IF NOT EXISTS(SELECT TOP 1 1 FROM sys.indexes i with(nolock)
WHERE i.name = \'''' + idx_name + '''\')
BEGIN
'''
tail = '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Index ''' + idx_name + '''...')
END
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {} {}'.format(f,table_name,pk_name))
except:
print(f)
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables\Keys"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\PrimaryKeys"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read pkey files
if os.path.isfile(file_name) and re.search(r'pkey', f) is not None:
try:
fh.file_str_replace(file_name, 'GO', '')
fh.file_str_replace(file_name, 'go', '')
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
ex_table = re.compile(r"\[.*?\]+")
l = ex_table.findall(context)
table_name = l[1].replace('[', '').replace(']', '')
pk_name = l[2].replace('[', '').replace(']', '')
fh.check_obj_name(pk_name, f)
header = '''IF NOT EXISTS(SELECT TOP 1 1 FROM sys.tables t WITH(NOLOCK)
JOIN sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1
WHERE SCHEMA_NAME(t.schema_id) = 'DBO' AND OBJECT_NAME(t.object_id) =\'''' + table_name + '''\' AND t.type = 'U')
BEGIN
'''
tail = '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Primary Key ''' + pk_name + '''\',' ON Table ''' + table_name + '''...')
END
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {} {}'.format(f,table_name,pk_name))
except:
print(f)
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables\Triggers"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\Triggers"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read trigger files
if os.path.isfile(file_name) and re.search(r'trigger',
f) is not None:
try:
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
ex = re.compile(r"\[.*?\]+")
l = ex.findall(context)
trigger_name = l[1].replace('[', '').replace(']', '')
fh.check_obj_name(trigger_name, f)
header = '''IF EXISTS(SELECT * FROM sys.triggers WHERE name =\'''' + trigger_name + '''\')
BEGIN
DROP TRIGGER ''' + trigger_name + '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Dropped Trigger ''' + trigger_name + '''...')
END;
GO
'''
tail = '''
GO
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Trigger ''' + trigger_name + '''...')
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {}'.format(f,trigger_name))
except:
print(f)
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables\Keys"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\\UniqueKeys"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read ukey files
if os.path.isfile(file_name) and re.search(r'ukey', f) is not None:
try:
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
ex_table = re.compile(r"\[.*?\]+")
l = ex_table.findall(context)
if l[0].replace('[', '').replace(']', '') != 'dbo':
print(1 / 0)
table_name = l[1].replace('[', '').replace(']', '')
uk_name = l[2].replace('[', '').replace(']', '')
fh.check_obj_name(uk_name, f)
header = '''IF NOT EXISTS(SELECT * FROM sys.indexes i WHERE i.name =\'''' + uk_name + '''\')
BEGIN
'''
tail = '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Unique Key ''' + uk_name + '''...')
END;
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {} {}'.format(f,table_name,uk_name))
except:
print(f)
def run():
# source script location
src_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\AspiraFocusDatabaseCodes\Databases\CA\Schema Objects\Tables\Constraints"
# target script location
tgt_dir = "E:\Work-RAOutdoors\CA\AspiraFocusDB\\UniversalDatabasesCodes\Databases\Constraints"
if os.path.exists(src_dir) and os.path.exists(tgt_dir):
files = os.listdir(src_dir)
for f in files:
file_name = os.path.join(src_dir, f)
# only read trigger files
if os.path.isfile(file_name):
try:
fh.file_str_replace(file_name, 'GO', '')
fh.file_str_replace(file_name, 'go', '')
encoding_name = fh.get_file_encoding(file_name)
context = fh.get_file_context(file_name, encoding_name)
ex = re.compile(r"\[.*?\]+")
l = ex.findall(context)
constraint_name = l[2].replace('[', '').replace(']', '')
# fh.check_obj_name(constraint_name, f)
header = '''IF (OBJECT_ID(\'''' + constraint_name + '''\')) IS NULL
BEGIN
'''
tail = '''
PRINT concat('[INFO] ', convert(varchar,getdate(),120), ' - Created Constraint ''' + constraint_name + '''...')
END;
GO
'''
new_context = header + context + tail
new_file_name = os.path.join(tgt_dir, f)
fh.write_file(new_file_name, new_context, 'utf-8')
# print('{} {}'.format(f, constraint_name))
except:
print(f)