def __init__(self, displayInfo=False):
if not os.path.exists(files.db_cnfpath):
print(
"Database config file does not exist. Please, copy .env.sample file to your PL/SQL code path with new name as .env, change the params and try again"
)
exit()
try:
# Load database config
load_dotenv(files.db_cnfpath)
self.disallowed_keywords = os.getenv("DISALLOW_KEYWORDS")
self.db_admin_user = os.getenv("DB_ADMIN_USER").upper()
self.db_admin_password = os.getenv("DB_ADMIN_PASSWORD")
self.db_default_table_space = os.getenv("DB_DEFAULT_TABLE_SPACE").upper()
self.db_temp_table_space = os.getenv("DB_TEMP_TABLE_SPACE").upper()
self.db_main_schema = os.getenv("DB_MAIN_SCHEMA").upper()
self.service_name = os.getenv("DB_SERVICE_NAME")
self.user = os.getenv("DB_USER").upper()
self.password = os.getenv("DB_PASSWORD")
self.host = os.getenv("DB_HOST")
self.port = os.getenv("DB_PORT")
except Exception as e:
print("Make sure you have all params setup on .env file")
self.types = files.objectsTypes().keys()
self.extentions = files.objectsTypes().values()
self.displayInfo = displayInfo
files.displayInfo = self.displayInfo
def metadataInsert(self, data, md5=False, db=None):
""" Insert data into metadata table.
Params:
data: list that contain and dict with the following keys: object_name, object_type, object_path, last_commit, last_ddl_time
"""
localClose = False
if not db:
db = self.dbConnect()
localClose = True
cursor = db.cursor()
for obj in data:
md5 = files.fileMD5(obj["object_path"])
sql = (
"INSERT INTO %s.PLADMIN_METADATA VALUES('%s', '%s', '%s','%s', sysdate, TO_DATE('%s','RRRR/MM/DD HH24:MI:SS'))"
% (
self.user,
obj["object_name"],
obj["object_type"],
obj["object_path"],
md5,
obj["last_ddl_time"],
)
)
cursor.execute(sql)
cursor.close()
if localClose:
db.commit()
db.close()
def getObjects(
self,
objectTypes=None,
objectName=None,
status=None,
withPath=False,
fetchOne=None,
db=None
):
"""
List Packages, Functions and Procedures and Views
Params:
------
status (string): Valid values [VALID, INVALID].
objectTypes (list): List of object type that you want [PACKAGE, FUNCTION, PROCEDURE]
withPath (Boolean): [True] if you want to include the path of the object
return (dic) with all objects listed
"""
types = "', '".join(self.types)
if objectTypes:
types = "', '".join(objectTypes)
query = (
"""SELECT owner, object_id, object_name, object_type, status, last_ddl_time, created FROM dba_objects WHERE owner = '%s' AND object_type in ('%s')"""
% (self.user, types)
)
if (status == "INVALID") or status == "VALID":
query += " AND status = '%s'" % status
if objectName:
query += " AND object_name = '%s'" % objectName
# Return a dic with the data
result = self.getData(query=query, fetchOne=fetchOne, db=db)
if fetchOne:
return result
if len(result) and withPath:
for obj in result:
p = files.findObjFileByType(
objectType=obj["object_type"], objectName=obj["object_name"]
)
obj.update({"object_path": ''})
if len(p):
obj.update({"object_path": p[0]})
return result
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 createSchema(self, force=False):
# To create users, give permission, etc. We need to connect with admin user using param sysDBA
db = self.dbConnect(sysDBA=True)
# Drop and create the user
user = self.newUser(db=db, force=force)
if not user:
print(
"\n The user %s already exist, use --force option override the schema"
% self.user
)
exit()
# Give grants to the user
self.createGramtsTo(
originSchema=self.db_main_schema, detinationSchema=self.user, db=db
)
# Create table of migrations
# self.createMetaTableScripts()
# Create synonyms
self.createSynonyms(
originSchema=self.db_main_schema, detinationSchema=self.user, db=db
)
# Close SYS admin db connection
db.close()
# Create meta table
self.createMetaTable()
# Create o replace packages, views, functions and procedures (All elements in files.objectsTypes())
data = files.listAllObjsFiles()
self.createReplaceDbObject(path=data, showInfo=True)
# If some objects are invalids, try to compile
invalids = self.compileObjects()
# Getting up object type, if it's package, package body, view, procedure, etc.
data = self.getObjects(withPath=True)
self.metadataInsert(data)
return invalids
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()
#!/usr/local/bin/python
from __future__ import absolute_import
import sys, getopt, json, os, argparse, time, hashlib, re
from termcolor import colored
from prettytable import PrettyTable
from datetime import datetime, timedelta
from pladmin.database import Database
from pladmin.files import Files
from pladmin.utils import utils
from pladmin.migrations import Migrations
db = Database(displayInfo=True)
files = Files(displayInfo=True)
# Table for wc2db and db2wc methods
info = PrettyTable(["Object", "Type", "Path", "Action", "Status", "Info"])
infoScript = PrettyTable(["Name", "GroupID", "Type", "Status", "Output"])
info.align = infoScript.align = 'l'
def watch(path_to_watch):
""" Watch the provided path for changes in any of it's subdirectories """
print("Watching " + path_to_watch)
before = files.files_to_timestamp()
while 1:
time.sleep(0.5)
after = files.files_to_timestamp()
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