schema_utils

NAME
      schema_utils - Schema management and utilities.

DESCRIPTION
      A collection of schema management tools and related utilities that work
      on schemas

methods

createFromCsv

Generates SQL CREATE TABLE statement from CSV file.

schema_utils.createFromCsv([filename][, delimiter][,
      column_name][, first_as_pk][, pk_auto_inc])

*filename: String - The CSV file path.

• delimiter: String - The field delimiter.
• column_name: Bool - Use the first row as column name. Default is True.
• first_as_pk: Bool - Use the first column as Primary Key. Default is True.
• pk_auto_inc: Bool - The PK will be defined as int unsigned auto_increment. If the first_as_pk is false, a new column will be added but invisible. Default is False.

 JS  schema_utils.createFromCsv('address.csv', ',', true, true, true)
CREATE TABLE address (
   `address_id` int unsigned auto_increment primary key,
   `address` varchar(38),
   `address2` varchar(0),
   `district` varchar(20),
   `city_id` smallint unsigned,
   `postal_code` varchar(5),
   `phone` varchar(12),
   `last_update` datetime
);

deleteProcedures

Delete stored procedures.

schema_utils.deleteProcedures([schema][, routine][, session])

• schema: String. The schema which to delete the stored procedures from.
• routine: String. The routine/procedure to be deleted..
• session: Object. The session to be used on the operation.

 JS  schema_utils.deleteProcedures('test')
Total dropped: 1

showDefaults

Lists the default values of each column in a table

schema_utils.showDefaults(table[, schema][, session])

• table: String. Table name to use.
• schema: String. Schema to use.
• session: Object. The session to be used on the operatio

 JS  schema_utils.showDefaults('default_test', 'test')
+--------------------------------+-----------------+----------------------------------------------------+---------------------------+
| ColumnName                     | Type            | Default                                            | Example                   |
+--------------------------------+-----------------+----------------------------------------------------+---------------------------+
| bi_col_exp                     | bigint(20)      | (8 * 8)                                            | 64                        |
| d_col                          | date            | curdate()                                          | 2020-08-01                |
| d_col_exp                      | date            | (curdate() + 8)                                    | 20200809                  |
| dt_col                         | datetime        | CURRENT_TIMESTAMP                                  | 2020-08-01 00:06:23       |
| vc_col_exp                     | varchar(20)     | concat(_utf8mb4'test',_utf8mb4'test')              | testtest                  |
+--------------------------------+-----------------+----------------------------------------------------+---------------------------+
Total: 5

showInvalidDates

Show Invalide Dates

schema_utils.showInvalidDates([table][, schema][, session])

• table: String. The table to check
• schema: String. The schema to check.
• session: Object. The session to be used on the operation.

+--------------------------------+-----------------+------------+-----------------+
| Schema and Table               | Column          | Type       | # Invalid       |
+--------------------------------+-----------------+------------+-----------------+
| dates.new_table                | date_table      | date       | 2               |
+--------------------------------+-----------------+------------+-----------------+
| dates.new_table_default        | date_table      | date       | 4               |
+--------------------------------+-----------------+------------+-----------------+

 JS  \sql select date_table from dates.new_table;
+------------+
| date_table |
+------------+
| 0000-00-00 | <--
| 2000-00-00 |
| 0000-00-00 | <--
+------------+

showProcedures

Lists all stored procedures.

schema_utils.showProcedures([schema][, session])

• schema: String. The schema which to list the stored procedures from.
• session: Object. The session to be used on the operation.

 JS  schema_utils.showProcedures('test')
+--------------+
| ROUTINE_NAME |
+--------------+
| p1           |
+--------------+