compat-tools

How to Get the Component

compat-tools

Introduction

This project is a set of compatibility tools. It aims to provide compatibility for necessary functions and system views created for OSs migrated from other asynchronous databases to MogDB, thereby facilitating the follow-up system maintenance and application modification.

The script is executed based on the version information. When you execute the script, it will be executed in terms of the following three situations:

1. If the object to be created does not exist in the target database, it will be directly created.
2. If the version of the object to be created is later than that of the object in the target database, the target database will be upgraded and has the object re-created.
3. If the version of the object to be created is earlier than that of the object in the target database, the creation operation will be skipped.

Software Architecture

SQL file

• runMe.sql: Total scheduling script
• Oracle_Views.sql: Compatible with Oracle database compatibility data dictionaries and views
• Oracle_Functions.sql: Compatible with Oracle database compatibility functions
• Oracle_Packages.sql: Compatible with Oracle database management packages
• MySQL_Views.sql: Compatible with MySQL database compatibility data dictionaries and views //TODO
• MySQL_Functions.sql: Compatible with MySQL database compatibility functions
• DB2_Functions.sql: DB2 database compatibility functions

Usage Description

Execute the SQL script in the MogDB database.

Note: All the following actions need to be executed in the directory where the current script is located.

Creating All Compatibility Objects

Create all compatibility objects, including views, functions, management packages, and other objects.

# Log in to the default database as a local user.
gsql -f runMe.sql

# Detailed creation syntax: Pay attention to the test user's permission (the user needs to have the sysadmin permission).
gsql -h 127.0.0.1 -p 5432 -U test -d postgres -f runMe.sql

Creating a Single Compatibility Object

The following uses the Oracle compatibility view as an example. For other compatibility objects, the following method also applies.

# Log in to the default database as a local user.
gsql -f Oracle_Views.sql

# Detailed creation syntax: Pay attention to the test user's permission (the user needs to have the sysadmin permission).
gsql -h 127.0.0.1 -p 5432 -U test -d postgres -f Oracle_Views.sql

Results Description

At the end of script execution, the following two types of content are automatically output.

1. The compatibility objects involved in this script, and the corresponding operation contents

   gsql:Oracle_Functions.sql:1035: NOTICE:  -- =====================================================================
   gsql:Oracle_Functions.sql:1035: NOTICE:  -- Compat Object List:
   gsql:Oracle_Functions.sql:1035: NOTICE:  -- =====================================================================
   gsql:Oracle_Functions.sql:1035: NOTICE:     | type      | name                                             | version | language | operation           |
   gsql:Oracle_Functions.sql:1035: NOTICE:     |-----------|--------------------------------------------------|---------|----------|---------------------|
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | months_between(timestamptz,timestamptz)          | 2.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | sys_guid()                                       | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | aggregate | wm_concat(text)                                  | 1.0     | internal | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | nvl2(anyelement,anyelement,anyelement)           | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | trunc(timestamp,text)                            | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | trunc(timestamptz,text)                          | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | nanvl(numeric,numeric)                           | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | regexp_substr(text,text,int4)                    | 2.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | regexp_substr(text,text,int4,int4,text,int4)     | 2.0     | plpgsql  | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | regexp_instr(text,text,int4,int4,int4,text,int4) | 1.0     | plpgsql  | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | regexp_replace(text,text,text,int4,int4,text)    | 1.0     | plpgsql  | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | regexp_count(text,text,int4,text)                | 2.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | replace(text,text)                               | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | ora_hash(anyelement,int4,int4)                   | 1.0     | plpgsql  | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | show(text)                                       | 1.0     | sql      | Skip due to version |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | function  | show_parameter(text)                             | 1.0     | sql      | Skip due to version |

2. Summary of the runs of the test cases built into this script

   Make sure that all test cases are up to date and that there are no test cases of type FAILED.

   gsql:Oracle_Functions.sql:1035: NOTICE:  -- =====================================================================
   gsql:Oracle_Functions.sql:1035: NOTICE:  -- Function Test Result:
   gsql:Oracle_Functions.sql:1035: NOTICE:  -- =====================================================================
   gsql:Oracle_Functions.sql:1035: NOTICE:     | result_type | case_count | start_time                 | complete_time              |
   gsql:Oracle_Functions.sql:1035: NOTICE:     |-------------|------------|----------------------------|----------------------------|
   gsql:Oracle_Functions.sql:1035: NOTICE:     | PASSED      |        387 | 2021-11-25 09:42:20.016619 | 2021-11-25 09:42:52.127892 |
   gsql:Oracle_Functions.sql:1035: NOTICE:     | NULL        |          6 | 2021-11-25 09:42:20.016619 | 2021-11-25 09:42:20.016619 |

Query the Created Compatibility Objects and Versions

select * from compat_tools.compat_version;

Query Test Case Details

select * from compat_tools.compat_testing;

Oracle Compatibility Views

Oracle_Views.sql includes the following Oracle compatibility views:

Management Views: ALL_*, DBA_*, USER_*

ALL_ views are consistent with DBA_ views, which are not verified with permission. In later versions, permission check will be performed.

• [DBA,ALL,USER]_CATALOG
• [DBA,ALL,USER]_CONS_COLUMNS
• [DBA,ALL,USER]_CONSTRAINTS
• [DBA,ALL,USER]_DATA_FILES
• [DBA,ALL,USER]_DEPENDENCIES
• [DBA,ALL,USER]_IND_COLUMNS
• [DBA,ALL,USER]_IND_PARTITIONS
• [DBA,ALL,USER]_IND_STATISTICS
• [DBA,ALL,USER]_INDEX_USAGE
• [DBA,ALL,USER]_INDEXES
• [DBA,ALL,USER]_OBJECTS
• [DBA,ALL,USER]_PART_INDEXES
• [DBA,ALL,USER]_PART_TABLES
• [DBA,ALL,USER]_PROCEDURES
• [DBA,ALL,USER]_SEGMENTS
• [DBA,ALL,USER]_SEQUENCES
• [DBA,ALL,USER]_SOURCE
• [DBA,ALL,USER]_SYNONYMS
• [DBA,ALL,USER]_TAB_COL_STATISTICS
• [DBA,ALL,USER]_TAB_COLS
• [DBA,ALL,USER]_TAB_COLUMNS
• [DBA,ALL,USER]_TAB_COMMENTS
• [DBA,ALL,USER]_TAB_MODIFICATIONS
• [DBA,ALL,USER]_TAB_PARTITIONS
• [DBA,ALL,USER]_TAB_STATISTICS
• [DBA,ALL,USER]_TABLES
• [DBA,ALL,USER]_TABLESPACES
• [DBA,ALL,USER]_TRIGGER_COLS
• [DBA,ALL,USER]_TRIGGERS
• [DBA,ALL,USER]_TYPES
• [DBA,ALL,USER]_VIEWS
• [DBA,ALL,USER]_JOBS
• [DBA,ALL,USER]_JOBS_RUNNING
• [DBA,ALL]_USERS
• DBA_SOURCE_ALL
• NLS_DATABASE_PARAMETERS
• NLS_INSTANCE_PARAMETERS
• NLS_SESSION_PARAMETERS

Other Short-Name Views

• DICTIONARY
• DICT
• COLS
• IND
• OBJ
• TAB
• DUAL (For 2.0 and above)

Dynamic Performance Views: GV,V

• [GV,V]$DATAFILE
• [GV,V]$LOCK
• [GV,V]$PARAMETER
• [GV,V]$PARAMETER_VALID_VALUES
• [GV,V]$SESSION
• [GV,V]$SESSTAT
• [GV,V]$SPPARAMETER
• [GV,V]$SYSSTAT
• [GV,V]$TABLESPACE
• [GV,V]$VERSION
• [GV,V]$NLS_PARAMETERS
• [GV,V]$NLS_VALID_VALUES

Views of Unified Permission Query

• DBA_DETAIL_PRIVILEGES
• DBA_ALL_PRIVILEGES
• DBA_ALL_PRIVILEGES_SQL

Oracle Compatibility Functions

Character Functions

• regexp_substr(text,text,int4)
• regexp_substr(text,text,int4,int4,text,int4) -- Note: The p_subexpr parameter is not supported.
• regexp_instr(text,text,int4,int4,text,int4)
• regexp_replace(text,text,text,int4,int4,text)
• regexp_count(text,text,int4,text)
• replace(text,text)

Time Functions

• months_between(timestamp, timestamp)
• trunc(timestamp, text)
• trunc(timestamptz, text)
• round(timestamptz,text)

Number Functions

• nanvl(numeric,numeric)

Aggregate Functions

• wm_concat(text)

NULL-Related Functions

• nullif(anyelement, anyelement)
• nvl2(anyelement, anyelement, anyelement)

Other Functions

• sys_guid()
• ora_hash(anyelement,bigint,bigint)
• show(text) - provides functions similar to "show xxx" in Oracle for viewing related parameters.
• show_parameter(text) - provides functions as show(text)
• dump(anynonarray)

Oracle Management Packages

DBMS_METADATA

• get_ddl(object_type, object_name, schema_name)

DBMS_OUTPUT

• enable(size)
• disable()
• get_line(line, status)
• get_lines(lines, numlines)
• new_line()
• put(text)
• put_line(text)

DBMS_RANDOM

• initialize(int4) -- deprecated in Oracle
• normal()
• random() -- deprecated in Oracle
• seed(int4)
• seed(text)
• string(char,int4)
• terminate() -- deprecated in Oracle
• value()
• value(numeric,numeric)

For details about how to use DBMS_RANDOM to generate random data (number, string, and date), see DBMS_RANDOM.

DBMS_JOB

• broken(int8,bool,timestamp)
• change(int8,text,timestamp,text,int4,bool)
• instance(int8,int4,bool)
• interval(int8,text)
• next_date(int8,timestamp)
• remove(int8)
• run(int8,bool)
• submit(int8,text,timestamp,text,bool,int4,bool)
• user_export(int8,text)
• what(int8,text)

MySQL Compatibility Functions

NULL-Related Functions

Note: The effect of ifnull is equivalent to the general function coalesce. If conditions allow, it is recommended to modify SQL to use the coalesce function, which is supported in almost all databases.

• ifnull(text,text)
• ifnull(numeric,numeric)
• ifnull(timestamp,timestamp)
• ifnull(timestamptz,timestamptz)
• isnull(text) -- Note: If the default A compatibility mode is used when creating the database in MogDB, then the '' empty string will also be recognized as a NULL value
• isnull(numeric)
• isnull(timestamptz)

Condition Control Functions

• if(bool,text,text)
• if(bool,numeric,numeric)
• if(bool,timestamptz,timestamptz)

Character Functions

• find_in_set(text,text)
• find_in_set(text,text[])
• field(text,text[])
• elt(int4,text[])
• strcmp(text,text)
• insert(text,int8,int8,text)
• lcase(text)
• ucase(text)
• space(int4)
• mid(text,int8,int8)
• locate(text,text,int4)
• to_base64(text)
• from_base64(text)

Numerical Functions

• field(numeric,numeric[])
• log10(numeric)
• log10(float8)
• rand(int4)

Time Functions

• unix_timestamp(timestamp)
• unix_timestamp(timestamptz)
• from_unixtime(int8)
• from_unixtime(numeric)
• from_unixtime(numeric,text)
• to_days(timestamp)
• to_seconds(timestamp)
• to_seconds(timestamptz)
• timediff(timestamptz,timestamptz)
• time_to_sec(time)
• sec_to_time(int4)
• date_format(timestamp, text)
• date_format(timestamptz, text)
• timestampdiff(text,timestamptz,timestamptz) -- Note: The Schema prefixed with pg_catalog. must be used, and the first unit parameter must be wrapped in single quotes
• str_to_date(text,text) -- Due to the difference in time types between MySQL and MogDB, this compatible function is not completely compatible. For details, please refer to the use case description section at the end of the corresponding function.

Other Functions

• uuid() -- Provide hash calculation uuid based on random value + timestamp
• uuid_to_bin(uuid, int4)
• bin_to_uuid(bytea, int4)

DB2 Compatibility Functions

NULL-Related Functions

Note: The effect of value is equivalent to the general function coalesce. If conditions allow, it is recommended to modify SQL to use the coalesce function, which is supported in almost all databases.

• value(text,text)
• value(numeric,numeric)
• value(timestamp,timestamp)
• value(timestamptz,timestamptz)

Character Functions

• posstr(text,text)
• locate_in_string(text,text,int4,int4,text)
• regexp_match_count(text,text,int4,text,text)

Time Functions

• year (text)
• year (timestamptz)
• year (interval)
• month (text)
• month (timestamptz)
• month (interval)
• quarter (timestamptz)
• week (timestamptz)
• day (text)
• day (timestamptz)
• day (interval)
• hour (text)
• hour (timestamptz)
• hour (interval)
• minute (text)
• minute (timestamptz)
• minute (interval)
• second (text)
• second (timestamptz)
• second (interval)
• days (timestamptz)
• dayofyear (timestamptz)
• dayofweek (timestamptz)
• dayofweek_iso (timestamptz)
• dayname (timestamptz)
• monthname (timestamptz)
• midnight_seconds (timestamptz)
• next_day (timestamptz,text,text)
• next_month (timestamptz)
• next_quarter (timestamptz)
• next_week (timestamptz)
• next_year (timestamptz)
• last_day (timestamptz)
• first_day (timestamptz)
• this_month (timestamptz)
• this_quarter (timestamptz)
• this_week (timestamptz)
• this_year (timestamptz)
• days_between (timestamptz,timestamptz)
• years_between (timestamptz,timestamptz)
• ymd_between (timestamptz,timestamptz)