Reverse Engineering a CRUD Matrix in Oracle (again) – with CRUDO

来源:转载

Dependencies between a table and database stored program units can be found in the DBA_DEPENDENCIES dictionary view. However, this only records the fact that there is a dependency.

Say you have a long-running report that will benefit from an index on a table. It would be good to know if there’s a packaged procedure that does a large update on that table and may therefore take longer with a new index to populate. How can you figure out if there are any such procedures ?

Well, you’re in luck.

CRUDO is not, as you may think, a domestic cleaning product. It is, in fact, a PL/SQL application for generating CRUD matrices for Stored Program Units against the tables that they reference.

I’ve been playing around with something like this for a while now, and

this time, I’ve posted the resulting code on GitHub .

CRUDO enables you to :

Determine a CRUD Matrix for each database stored program unit against a given table ( or synonym on that table) generate Matrices for all tables in a schema record the results in a table for fast querying update existing records only for program units that have been changed since the last update of it’s record specify override records for situations where the dependency may not be recorded in DBA_DEPENDENCIES(e.g. when using dynamic SQL statements) view runtime information on what it is doing amend logging levels to provide more detailed information on large runs laugh at my inability to come up with a decent name for it

I developed CRUDO on Oracle 11gR2 and it will run on any Oracle Database Edition ( XE, Standard One, Standard, Enterprise).

Installing CRUDO

First thing to do is to get CRUDO from Github.

Head over to the CRUDO Github repository and click the Download ZIP button.

Congratulations, you are now the proud owner of a file called crudo-master.zip .

Extract this into a location of your choosing.

Full installation instructions are included in the installation folder.

You can either install into an existing schema, or – the default option – you can create a new schema called CRUDO.

The examples that follow assume that you have done the latter.

You can create the CRUDO schema by running the script provied.From the installation directory, connect to the database as a user with CREATE USER privileges and run…

@scripts/crudo_owner.sql

You will be prompted for :

The name of the user you want to create ( CRUDO by default) The default tablespace for the new user ( default is USERS) The temporary tablespace for the new user ( default is TEMP)

If you want to use any of the defaults, just hit return at the appropriate prompt.

The output of the script will look something like this :

SQL> @scripts/crudo_owner.sqlEnter the name of the new crudo Application Owner schema [crudo] : Default tablespace for the new schema [users] : Temporary tablespace for the new schem [temp] : Enter a password for the new schema [] : User created.Grant succeeded.User altered.SQL>

If you want to check that the user has been created as expected, well, there’s a script for that as well.Connect as the newly created schema (in this case, CRUDO) and…

@scripts/pre_install_check.sqlSQL> @scripts/pre_install_checkPre-requisite checks complete.PL/SQL procedure successfully completed.SQL>

Now to install the database components that comprise the application.Still in the installation directory, connect to the database as the application owner (crudo in this example) and…

@crudo_deploy.sql

Running this script will generate a log file in the same directory. If all goes well, it should look something like this :

Creating tables===============APPLICATION_LOGSTable created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.CRUD_MATRICESTable created.Table altered.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Creating Packages=================LOGSPackage created.Package body created.SEARCH_CODEPackage created.Package body created.GENERATE_MATRICESPackage created.Package body created.Deployment completed. Optional Step – Installing the SQLDeveloper Extensions

CRUDO is a PL/SQL application. This means that all of the functionality is available via it’s PL/SQL packages.

We’ll look at these in detail later on.

If you are a SQLDeveloper user, then you may want to install the SQLDeveloper Extensions that come with the application to make life just that little bit more, well, GUI.

The Extensions are written in XML ( no Java). They can be added to SQLDeveloper in the usual way, namely…

Open SQLDeveloper, go to the Tools menu and select Preferences Expand the Database node and select User Defined Extensions . It should look something like this :

Click the Add Row button. In the new row that’s created, click in the Type column and select EDITOR from the drop-down

In the new row, click in the Location column and choose the crud_tab.xml file. You can find this file in the sqldev_extensions folder.

Next, add the Context Menu.

Click the Add Row button. In the new row that’s created, click in the Type column and select ACTION from the drop-down

In the new row, click in the Location column and choose the crud_table_action.xml file. You can find this file in the sqldev_extensions folder. Cick OK . Re-start SQLDeveloper.

You should now see the CRUD MATRIX tab on the Tables view :

If you right-click on a table, you should see the CRUDO Table Menu :

The Database Components

Whether you’ve decided to install the SQLDeveloper extensions or not, you will now have a database schema which contains the following tables :

CRUD_MATRICES – the table that holds all of the CRUD_MATRICES APPLICATION_LOGS – table that holds any log messages generated by the application

…and the following packages…

GENERATE_MATRICES – containing all of the publicly called procedures and functions for the application SEARCH_CODE – called from GENERATE_MATRICES to find DML statements in the source code LOGS – used to manage entries to the APPLICATION_LOGS table Granting Access to other users

A script is provided to grant access to the application to database users.To run this, from the installation directory, connect as either the application owner (CRUDO in our case), or a user with GRANT ANY privileges and…

@scripts/grants.sql

You will be prompted for the application owner ( defaults to CRUDO) and the name of the user you’re granting access to. For example, if I want to grant access to MIKE it would look like this :

SQL> @scripts/grants.sqlEnter the the Application Owning Schema [CRUDO] :Enter the name of the user to grant access to : mikeold 1: grant execute on &&app_owner..generate_matrices to &app_usernew 1: grant execute on CRUDO.generate_matrices to mikeGrant succeeded.old 1: grant select on &&app_owner..crud_matrices to &app_usernew 1: grant select on CRUDO.crud_matrices to mikeGrant succeeded.old 1: grant select on &&app_owner..application_logs to &app_usernew 1: grant select on CRUDO.application_logs to mikeGrant succeeded.Grants completed.SQL>

Now you’ve got everything setup it’s time to…

Generating your first CRUD

In the following examples, I’m connected as MIKE. I’m going to be working on the HR schema.

First up, we’re going to generate matrices for the HR.EMPLOYEES table. There are two ways to do this…

Generating from a PL/SQL block begin crudo.generate_matrices.crud_table('HR', 'EMPLOYEES'); commit;end;/ Generating From SQLDeveloper

If you’re using the SQLDeveloper Extensions…

In the Object Tree, navigate to Other Users and find HR .

Expand the HR node, then the Tables and right-click on EMPLOYEES .

In the context menu that pops up, click on CRUDO Table Menu then CRUD Table .

You should now see this :

You’ll notice that the Owner and Table Name are displayed for information and are not enterable.

You’ll also notice that there is a third parameter – Refresh Type .

By default this is set to DELTA.

I’ll cover the REFRESH TYPE option a bit later. For now though, just accept the defaults and click Apply .

NOTE– in SQLDeveloper, the Menu Options run in an Autonomous transaction and commit automatically. This is done to ensure that they do not interfere with any other transactions that may be ongoing in your current SQLDeveloper session.

Viewing the Crud Matrices

Whatever method you’ve used, you should now be able to see the matrices we’ve generated for employees. In SQL*Plus :

select object_owner, object_name, object_type,create_flag, read_flag, update_flag, delete_flag,last_updated, last_updated_userfrom crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'EMPLOYEES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE C R U D LAST_UPDATEDLAST_UPDATED_USER------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------HR EMP_DETAILS_VIEW VIEW N Y N N 16-OCT-15MIKEHR SECURE_EMPLOYEES TRIGGER Y N Y Y 16-OCT-15MIKEHR UPDATE_JOB_HISTORY TRIGGER N N Y N 16-OCT-15MIKE3 rows selected.

Alternatively, in SQLDeveloper, simply navigate to the table as before.You’ll notice that there is an now some data on the CRUD Matrix tab…

However you view the output, you should see entries for all dependent objects that are not wrapped.

Generating matrices one table at a time may get a bit tedious. Fortunately, CRUDO allows you to…

Generate Matrices for all tables in a schema

As with the CRUD_TABLE procedure, CRUD_SCHEMA also has an optional REFRESH_TYPE parameter which is also set to DELTA by default.

the length of time this procedure runs for will depend on a number of factors such as the number of tables in the schema and the number, length and type of program units that are dependent on them.

Therefore, it’s not necessarily suited to interactive running and so there is no SQLDeveloper menu option for this.

Additionally, whilst the procedure will update the ACTION column in V$SESSION, you may want a bit more detail about what it’s doing.You can get this by setting the Application’s logging level.

CRUDO has three logging levels :

ERROR – only errors are written to the APPLICATION_LOGS table ( the default) INFO – Errors and Information messages are written to the table DEBUG – All messages are written to the table

To find out what the log setting is for the current session :

select crudo.generate_matrices.get_log_levelfrom dual/

If we’re running the schema crud, we may want to set the level to INFO :

begin crudo.generate_matrices.set_log_level('INFO');end;/

One other setting you may want to play around with is the BULK_COLLECT_LIMIT. This dictates the bulk collect limit used when processing objects dependent on a table. To check the current limit :

select crudo.generate_matrices.get_bulk_collect_limit from dual/

To change the limit for the current session :

begin crudo.generate_matrices.set_bulk_collect_limit(500);end;/

To generate matrices for all tables in a schema, you can run…

begin crudo.generate_matrices.crud_schema('HR'); commit;end;/

Once this has completed, we should see records in the CRUD_DETAILS table for all tables in the HR schema :

select distinct table_namefrom crudo.crud_matriceswhere table_owner = 'HR'/TABLE_NAME------------------------------COUNTRIESDEPARTMENTSEMPLOYEESJOBSJOB_HISTORYLOCATIONSREGIONS

Before I go too much further, I should probably explain…

Refresh Types

By default CRUDO will only re-examine program units that have a last_ddl_time later than the crud records were last created.

It will however, remove records for any program unit that has been dropped since it was last run.

If you want to ensure that all program units are processed, irrespective of when they were last changed, you specify a Refresh Type of FULL.

To demonstrate how this works, let’s start by making a note of the time at which our CRUD_MATRICES records were generated for the COUNTRIES table :

select object_owner, object_name, object_type,to_char(last_updated, 'HH24:MI')from crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'COUNTRIES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE TO_CH------------------------------ ------------------------------ ------------------------------ -----HR EMP_DETAILS_VIEW VIEW 13:111 row selected.

Now, let’s create the following function (as the HR user), which reads the COUNTRIES table :

create or replace function get_country_name( i_country_id in countries.country_id%type)return countries.country_name%typeisl_rtn countries.country_name%type;beginselect country_name into l_rtnfrom countrieswhere country_id = upper(i_country_id);return l_rtn;exception when no_data_found thenraise_application_error( -20000, 'No record for this country id');end;/

If we now run a DELTA refresh ( the default, remember), either via SQLDeveloper or via PL/SQL…

begin crudo.generate_matrices.crud_table('HR', 'COUNTRIES'); commit;end;/

…we can see that the new record has been added, but the pre-existing record has not been updated…

select object_owner, object_name, object_type,to_char(last_updated, 'HH24:MI')from crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'COUNTRIES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE TO_CH------------------------------ ------------------------------ ------------------------------ -----HR EMP_DETAILS_VIEW VIEW 13:11HR GET_COUNTRY_NAME FUNCTION 13:302 rows selected.

By contrast, if we were to run a FULL refresh, we can see that all of these records are overwritten. Using PL/SQL…

begincrudo.generate_matrices.crud_table(i_owner => 'HR',i_table_name => 'EMPLOYEES',i_refresh_type => 'FULL');commit;end;/

In SQLDeveloper, you simply need to select FULL from the drop-down

If we now re-query the records, we can see that they have all been overwritten :

select object_owner, object_name, object_type,to_char(last_updated, 'HH24:MI')from crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'EMPLOYEES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE TO_CH------------------------------ ------------------------------ ------------------------------ -----HR EMP_DETAILS_VIEW VIEW 18:35HR SECURE_EMPLOYEES TRIGGER 18:35HR UPDATE_JOB_HISTORY TRIGGER 18:35

Generally then, a FULL refresh will re-calculate all of the matrices for a given table. There is one type of record however, where this does not apply…

Override CRUD records

Consider the following procedure :

create or replace procedure add_country( i_id in varchar2, i_name in varchar2, i_region_id in varchar2)isbegin execute immediate 'insert into countries(country_id, country_name, region_id) values(:1, :2, :3)' using i_id, i_name, i_region_id;end;/

Because the only reference to the COUNTRIES table is in a dynamic SQL statement, no dependency between the procedure and the COUNTRIES table is recorded in the data dictionary…

select owner, name, type from all_dependencieswhere referenced_owner = 'HR'and referenced_name = 'COUNTRIES'/OWNER NAME TYPE------------------------------ ------------------------------ ------------------HR EMP_DETAILS_VIEW VIEWHR GET_COUNTRY_NAME FUNCTION

Sure enough, if we run a crud against the table…

begin crudo.generate_matrices.crud_table( 'HR', 'COUNTRIES'); commit;end;/

… we won’t have a record for this procedure…

select table_owner, table_namefrom crudo.crud_matriceswhere object_owner = 'HR'and object_name = 'ADD_COUNTRY'and object_type = 'PROCEDURE'/no rows selected

In order to make sure that this crud matrix is recorded, you can…

Creating an override record

In PL/SQL, you can do this as follows :

begincrudo.generate_matrices.set_override(i_table_owner => 'HR',i_table_name => 'COUNTRIES',i_object_owner => 'HR',i_object_name => 'ADD_COUNTRY',i_object_type => 'PROCEDURE',i_create => 'Y',i_read => 'N',i_update => 'N',i_delete => 'N');commit;end;/

In SQLDeveloper, you can do this from the CRUDO Table Menu using the Add CRUD Override Record option.

When you select this option, you’ll see the following dialog box :

In the dialog :

Enter the name of the program unit you want to create the record for ( note, this is not case sensitive) select the program unit’s type from the drop-down set the Create, Read, Update and Delete flags to match the CRUD of this program unit against the table

In our example the dialog box should now look something like this :

Once you’re happy with what you’ve entered, click Apply.

We can now see the new record in the CRUD_DETAILS table :

select object_owner, object_name, object_type,create_flag, read_flag, update_flag, delete_flag,last_updated, last_updated_user,override_flagfrom crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'COUNTRIES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE C R U D LAST_UPDATEDLAST_UPDATED_USER O------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------ -HR ADD_COUNTRY PROCEDURE Y N N N 16-OCT-15MIKE YHR EMP_DETAILS_VIEW VIEW N Y N N 16-OCT-15MIKE NHR GET_COUNTRY_NAME FUNCTION N Y N N 16-OCT-15MIKE N

From this point on, anytime we run a CRUD against the COUNTRIES table, even if we specify a full refresh, the override record will remain untouched.

CRUDO is smart enough to know that it’s not as smart as you

:-)

.

Removing an Override Record

Imagine time has moved on a bit and the developer has come to realise that using dynamic SQL in this procedure is, perhaps, not the best way to approach matters.Instead, the procedure has been re-written like this :

create or replace procedure add_country( i_id in varchar2, i_name in varchar2, i_region_id in varchar2)isbeginmerge into countries using dualon ( country_id = i_id)when matched thenupdateset country_name = nvl(i_name,country_name),region_id = nvl(i_region_id, region_id)when not matched theninsert( country_id, country_name, region_id)values( i_id, i_name, i_region_id);end;/

Notice that the CRUD on COUNTRIES will have changed as the procedure may now UPDATE, as well as INSERT.Additionally, because we’re no longer using dynamic SQL, Oracle recognises that there is a dependency for this procedure on the countries table :

select referenced_owner, referenced_namefrom all_dependencieswhere owner = 'HR'and name = 'ADD_COUNTRY'and type = 'PROCEDURE'and referenced_type = 'TABLE'/REFERENCED_OWNER REFERENCED_NAME------------------------------ ------------------------------HR COUNTRIES

Whilst CRUDO would be able to recognize and record this crud, it will not contradict the override record.Therefore, we’ll need to remove it.

Once again, you can either do this in PL/SQL…

begincrudo.generate_matrices.remove_override(i_table_owner => 'HR',i_table_name => 'COUNTRIES',i_object_owner => 'HR',i_object_name => 'ADD_COUNTRY',i_object_type => 'PROCEDURE');commit;end;/

…or use the CRUDO Table Menu in SQLDeveloper…

From the menu, select the Remove Crud Override Record.You will see the following dialog :

Enter the object name and select the object type from the drop-down, then click Apply.

When you re-query the CRUD_MATRICES table, you’ll notice that the override record has not been removed, but it has been updated…

select object_owner, object_name, object_type,create_flag, read_flag, update_flag, delete_flag,last_updated, last_updated_user,override_flagfrom crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'COUNTRIES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE C R U D LAST_UPDATEDLAST_UPDATED_USER O------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------ -HR ADD_COUNTRY PROCEDURE Y N N N 01-JAN-70MIKE NHR EMP_DETAILS_VIEW VIEW N Y N N 16-OCT-15MIKE NHR GET_COUNTRY_NAME FUNCTION N Y N N 16-OCT-15MIKE N

So, the last_updated date is now 01-JAN-1970 and the override_flag is set to N.This means that, when we come to run a CRUD against this table, this record will be overwritten, irrespective of the Refresh Type we choose…

begin crudo.generate_matrices.crud_table('HR', 'COUNTRIES'); commit;end;/

If we now check, we can see that the record has indeed been updated…

select object_owner, object_name, object_type,create_flag, read_flag, update_flag, delete_flag,last_updated, last_updated_user,override_flagfrom crudo.crud_matriceswhere table_owner = 'HR'and table_name = 'COUNTRIES'order by object_owner, object_name, object_type/OBJECT_OWNER OBJECT_NAME OBJECT_TYPE C R U D LAST_UPDATEDLAST_UPDATED_USER O------------------------------ ------------------------------ ------------------------------ - - - - ------------------ ------------------------------ -HR ADD_COUNTRY PROCEDURE Y N Y N 16-OCT-15MIKE NHR EMP_DETAILS_VIEW VIEW N Y N N 16-OCT-15MIKE NHR GET_COUNTRY_NAME FUNCTION N Y N N 16-OCT-15MIKE NSQL>

That just about wraps it up for our wander through CRUDO ( I hope those shoes weren’t expensive !)If you decide to give it a go, I’d be interested to hear what you think.



分享给朋友:
您可能感兴趣的文章:
随机阅读: