MogDB
Ecological Tools
Doc Menu

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 //TODO

Usage Description

Execute the SQL script in the openGauss database.

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

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]_USERS
  • DBA_SOURCE_ALL

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

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)
  • 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).

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.

MySQL Compatibility Functions

Character Function

  • find_in_set(text,text)
  • find_in_set(text,text[])
  • ifnull (text, text)
  • ifnull (numeric, numeric)
  • ifnull (timestamp, timestamp)
  • ifnull (timestamptz, timestamptz)