def newUser(self, db, force=False):
progressTotal = 3
files.progress(
count=1,
total=progressTotal,
status="VALIDATING...",
title="CREATING " + self.user,
)
cursor = db.cursor()
# Firts, we need to validate if the user exist
sql = "SELECT COUNT(1) AS v_count FROM dba_users WHERE username = :db_user"
cursor.execute(sql, {"db_user": self.user})
user = cursor.fetchone()
if user[0]:
if force:
files.progress(
count=2, total=progressTotal, status="DROP USER %s" % self.user
)
cursor.execute("DROP USER %s CASCADE" % self.user)
else:
return False
# Create the user
files.progress(
count=2, total=progressTotal, status="CREATING USER %s" % self.user
)
sql = (
"CREATE USER %s IDENTIFIED BY %s DEFAULT TABLESPACE %s TEMPORARY TABLESPACE %s QUOTA UNLIMITED ON %s"
% (
self.user,
self.password,
self.db_default_table_space,
self.db_temp_table_space,
self.db_default_table_space,
)
)
cursor.execute(sql)
files.progress(
count=3, total=progressTotal, status="USER %s CREATED" % self.user, end=True
)
return True
def createGramtsTo(self, originSchema, detinationSchema, db=None):
cursor = db.cursor()
i = 0
permisions = [
"GRANT CREATE PROCEDURE TO",
"GRANT CREATE SEQUENCE TO",
"GRANT CREATE TABLE TO",
"GRANT CREATE VIEW TO",
"GRANT CREATE TRIGGER TO",
"GRANT EXECUTE ANY PROCEDURE TO",
"GRANT SELECT ANY DICTIONARY TO",
"GRANT CREATE SESSION TO",
"GRANT SELECT ANY DICTIONARY TO",
"GRANT EXECUTE ANY PROCEDURE TO",
"GRANT EXECUTE ANY TYPE TO",
"GRANT ALTER ANY TABLE TO",
"GRANT ALTER ANY SEQUENCE TO",
"GRANT UPDATE ANY TABLE TO",
"GRANT DEBUG ANY PROCEDURE TO",
"GRANT DEBUG CONNECT ANY to",
"GRANT DELETE ANY TABLE TO",
"GRANT ALTER ANY INDEX TO",
"GRANT INSERT ANY TABLE TO",
"GRANT READ ANY TABLE TO",
"GRANT SELECT ANY TABLE TO",
"GRANT SELECT ANY SEQUENCE TO",
"GRANT UPDATE ON SYS.SOURCE$ TO",
"GRANT EXECUTE ON SYS.DBMS_LOCK TO",
]
# Prepare vars to progress bar
progressTotal = len(permisions)
files.progress(
i,
progressTotal,
status="LISTING PERMISSIONS %s" % detinationSchema,
title="GIVE GRANTS",
)
for p in permisions:
# Write progress bar
files.progress(i, progressTotal, status="GRANT TO %s " % detinationSchema)
# Excute to db
cursor.execute(p + " " + detinationSchema)
i += 1
# This is a special permission
cursor.execute(
"CREATE SYNONYM %s.FERIADOS FOR OMEGA.FERIADOS" % detinationSchema
)
files.progress(
i, progressTotal, status="GRANT TO %s " % detinationSchema, end=True
)
def createSynonyms(self, originSchema, detinationSchema, db):
""" Create synonyms types ('SEQUENCE', 'TABLE', 'TYPE') from originSchema to destinationSchema """
cursor = db.cursor()
sql = """ SELECT oo.object_name, oo.object_type, oo.status
FROM sys.dba_objects oo
WHERE oo.owner = '%s'
AND oo.object_type IN ('SEQUENCE', 'TABLE', 'TYPE')
AND oo.object_name NOT LIKE 'SYS_PLSQL_%%'
AND oo.object_name NOT LIKE 'QTSF_CHAIN_%%'
AND oo.object_name <> 'PLADMIN_METADATA'
AND NOT EXISTS
(SELECT 1
FROM sys.dba_objects tob
WHERE tob.owner = '%s'
AND tob.object_name = oo.object_name)
AND status = 'VALID' """ % (
originSchema,
detinationSchema,
)
synonyms = self.getData(query=sql, db=db)
# Params to process bar
progressTotal = len(synonyms)
i = 0
files.progress(i, progressTotal, "LISTING TABLES", title="CREATE SYNONYMS")
for synon in synonyms:
# Write progress bar
files.progress(
i,
progressTotal,
status="CREATE SYNONYM %s.%s"
% (detinationSchema, synon["object_name"]),
)
sql = "CREATE SYNONYM %s.%s FOR %s.%s" % (
detinationSchema,
synon["object_name"],
originSchema,
synon["object_name"],
)
cursor.execute(sql)
i += 1
files.progress(i, progressTotal, status="SYNONYMS CREATED", end=True)
cursor.close()
def createReplaceDbObject(self, path=None, db=None, showInfo=False):
"""
Creates or Replaces packges, views, procedures and functions.
params:
------
path (list): path routes of the object on the file system
db (cx_Oracle.Connection): If you opened a db connection puth here please to avoid
return (list) with errors if some package were an error
"""
success = []
errors = []
localClose = False
if not db:
db = self.dbConnect()
localClose = True
cursor = db.cursor()
# Prepare data for progress bar
progressTotal = len(path)
i = 0
files.progress(
i,
progressTotal,
status="LISTING PACKAGES...",
title="CREATE OR REPLACE PACKAGES",
)
for f in path:
fname, ftype, objectType = files.getFileName(f)
# Only valid extencions sould be processed
if not "." + ftype in self.extentions:
continue
# Display progress bar
files.progress(i, progressTotal, "CREATING %s" % fname)
i += 1
opf = open(f, "r")
content = opf.read()
opf.close()
context = "CREATE OR REPLACE "
if ftype == "vw":
context = "CREATE OR REPLACE FORCE VIEW %s AS \n" % fname
# Execute create or replace package
try:
cursor.execute(context + content)
success.append(fname)
except Exception as e:
errors.append(e)
if showInfo:
print(e)
pass
files.progress(
i,
progressTotal,
status="OBJECTS HAS BEEN CREATED (ERRORS: %s)" % len(errors),
end=True,
)
if localClose:
db.close()
return success, errors