db-metadata-cg-lib

1. Introduction

The objective of this code is to query the metadata of databases from drivers such as Oracle, Postgres and MariaDB. Three types of calls can be made: schemes, tables and columns. This is for the purpose of knowing the table structure of databases and knowing how to make queries, inserts, updates and deletes.

This library is useful to be added to any nodejs project.

Within "db-metadata-cg-lib" there is one additional library is used: sequelize: This library is used to create a connection with the databases and perform the queries to get de metadata and return the final result that is managed as JSON objects.

The next is an example, we are going to get all the schemas from an Oracle database and we are going to send all the required parameters like the next example:

{
        "dialect": "oracle",
        "database": "ORCL",
        "host":"oracle.training.cgdemos.com",
        "user": "TEST_USER",
        "password": "admin",
        "type":"schemas",
        "filter":""  
    }

This is the result:

[
    {
        "OWNER": "SYS"
    },
    {
        "OWNER": "SYSTEM"
    },
    {
        "OWNER": "DBSNMP"
    },
    {
        "OWNER": "APPQOSSYS"
    },
    {
        "OWNER": "TEST_USER"
    },
    {
        "OWNER": "DBSFWUSER"
    },
    {
        "OWNER": "REMOTE_SCHEDULER_AGENT"
    },
    {
        "OWNER": "PUBLIC"
    },
    {
        "OWNER": "CTXSYS"
    },
    {
        "OWNER": "AUDSYS"
    },
    {
        "OWNER": "OJVMSYS"
    },
    {
        "OWNER": "SI_INFORMTN_SCHEMA"
    },
    {
        "OWNER": "DVF"
    },
    {
        "OWNER": "DVSYS"
    },
    {
        "OWNER": "GSMADMIN_INTERNAL"
    },
    {
        "OWNER": "ORDPLUGINS"
    },
    {
        "OWNER": "MDSYS"
    },
    {
        "OWNER": "ORDDATA"
    },
    {
        "OWNER": "OLAPSYS"
    },
    {
        "OWNER": "LBACSYS"
    },
    {
        "OWNER": "OUTLN"
    },
    {
        "OWNER": "ORACLE_OCM"
    },
    {
        "OWNER": "HRAPPS"
    },
    {
        "OWNER": "XDB"
    },
    {
        "OWNER": "WMSYS"
    },
    {
        "OWNER": "ORDSYS"
    }
]

2. Methods explanation

_2.1. metadataProcess

The library can be installed from npm page with the next sentences:

npm install db-metadata-cg-lib, npm i db-metadata-cg-lib or yarn install db-metadata-cg-lib

This method is used to perform all the operations that the library can do for the different database managers that are Oracle, Postgres and MariaDB, sending the required parameters.

3. Argument and result explanation

• Arguments: The only and main method needs a list of parameters, then all the parameters are listed with their description:

  • dialect. - parameter to know which database manager is going to be consulted.
  • database. - the name of the database
  • host. - numeric or alphanumeric address to connect to the database.
  • user. - the username to connect to the database.
  • password. - the password to connect to the database.
  • type. - parameter that defines what type of query will be made to the database, there are three types:
    • schemas: this type will return all the schemas that exist in the database connection.
    • tables: this type will return all the tables that exist in the schema that is sent in the "filter" parameter.
    • columns: this type will return all the columns of the tables and their data type that are sent in the "filter" parameter, the name of the table(s) must be sent separated by a comma.
  • filter. - this parameter is used to filter the metadata when the “type” parameter is “tables” or “columns”, with type “schema” it has no effect, to send more than one table we have to separate by coma.

• Result: The final result varies depending on the process, if it is "schemas" it will return a JSON as a result with the schemas of the database, if it is an "tables" it will return a JSON with all the tables of the schema selected, and the last one "columns" it will return a JSON with the columns of the tables that we send in the filter parameter.

3. Examples

Example 1: We are going to get all the schemas from an Oracle database and we are going to send all the required parameters like the next example:

{
        "dialect": "oracle",
        "database": "ORCL",
        "host":"oracle.training.cgdemos.com",
        "user": "TEST_USER",
        "password": "admin",
        "type":"schemas",
        "filter":""  
    }

And this is the result of that:

[
    {
        "OWNER": "SYS"
    },
    {
        "OWNER": "SYSTEM"
    },
    {
        "OWNER": "DBSNMP"
    },
    {
        "OWNER": "APPQOSSYS"
    },
    {
        "OWNER": "TEST_USER"
    },
    {
        "OWNER": "DBSFWUSER"
    },
    {
        "OWNER": "REMOTE_SCHEDULER_AGENT"
    },
    {
        "OWNER": "PUBLIC"
    },
    {
        "OWNER": "CTXSYS"
    },
    {
        "OWNER": "AUDSYS"
    },
    {
        "OWNER": "OJVMSYS"
    },
    {
        "OWNER": "SI_INFORMTN_SCHEMA"
    },
    {
        "OWNER": "DVF"
    },
    {
        "OWNER": "DVSYS"
    },
    {
        "OWNER": "GSMADMIN_INTERNAL"
    },
    {
        "OWNER": "ORDPLUGINS"
    },
    {
        "OWNER": "MDSYS"
    },
    {
        "OWNER": "ORDDATA"
    },
    {
        "OWNER": "OLAPSYS"
    },
    {
        "OWNER": "LBACSYS"
    },
    {
        "OWNER": "OUTLN"
    },
    {
        "OWNER": "ORACLE_OCM"
    },
    {
        "OWNER": "HRAPPS"
    },
    {
        "OWNER": "XDB"
    },
    {
        "OWNER": "WMSYS"
    },
    {
        "OWNER": "ORDSYS"
    }
]

Now we take one schema for the next call, we are going to use the schema to filter the tables by that schema, this is the payload:

    {
        "dialect": "oracle",
        "database": "ORCL",
        "host":"oracle.training.cgdemos.com",
        "user": "TEST_USER",
        "password": "admin",
        "type":"tables",
        "filter":"TEST_USER"  
    }

And this is the result:

[
    {
        "TABLE_NAME": "ALL_ORDERS"
    },
    {
        "TABLE_NAME": "ERPDATA"
    },
    {
        "TABLE_NAME": "IL_ITEMS"
    },
    {
        "TABLE_NAME": "IL_ORDERS"
    },
    {
        "TABLE_NAME": "MEX_ITEMS"
    },
    {
        "TABLE_NAME": "MEX_ORDERS"
    },
    {
        "TABLE_NAME": "PERSONA"
    },
    {
        "TABLE_NAME": "PERSONA_NAME"
    },
    {
        "TABLE_NAME": "SLC_HRINF_OUTBOUND_STG"
    },
    {
        "TABLE_NAME": "WI_ITEMS"
    },
    {
        "TABLE_NAME": "WI_ORDERS"
    }
]

Now the last call is to get the columns, so we are going to send a table or tables into the filter parameters like the next example:

    {
        "dialect": "oracle",
        "database": "ORCL",
        "host":"oracle.training.cgdemos.com",
        "user": "TEST_USER",
        "password": "admin",
        "type":"columns",
        "filter":"IL_ORDERS,MEX_ITEMS"  
    }

The result is this:

[
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "ID",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 50
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "ORDER_EXPECTED_DATE",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 50
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "COMPANY",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 50
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "CUSTOMER_NAME",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 100
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "CUSTOMER_EMAIL",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 50
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "CUSTOMER_ADDRESS",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 200
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "WHS_LOCATION",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 20
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "ORDER_TOTAL",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 20
    },
    {
        "TABLE_NAME": "IL_ORDERS",
        "COLUMN_NAME": "ORDER_DAYS_DURATION",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 20
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "LINE_ID",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 50
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "ITEM_SKU",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 20
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "NAME",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 150
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "QUANTITY",
        "DATA_TYPE": "NUMBER",
        "DATA_LENGTH": 22
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "PRICE",
        "DATA_TYPE": "FLOAT",
        "DATA_LENGTH": 22
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "EFFECTIVE_DATE",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 20
    },
    {
        "TABLE_NAME": "MEX_ITEMS",
        "COLUMN_NAME": "PARENT_ORDER",
        "DATA_TYPE": "VARCHAR2",
        "DATA_LENGTH": 50
    }
]