Metadata-Version: 2.1
Name: schema-change-risk-engine
Version: 0.0.10
Summary: Common MySQL Schema Alter Issues Check Engine Package
Home-page: https://github.com/dbmruphy/SchemaAlterRulesEngine
Author: David Murphy
Author-email: David Murphy <david.b.murphy.tx@gmail.com>
Project-URL: homepage, https://github.com/dbmurphy/SchemaAlterRulesEngine
Project-URL: bugs, https://github.com/dbmurphy/SchemaAlterRulesEngine/issues
Project-URL: source, https://github.com/dbmurphy/SchemaAlterRulesEngine
Project-URL: changelog, https://github.com/dbmurphy/SchemaAlterRulesEngine
Keywords: mysql,schema
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: Topic :: Software Development :: Build Tools
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.6
Classifier: Programming Language :: Python :: 3.7
Classifier: Programming Language :: Python :: 3.8
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.6
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: docs
Requires-Dist: furo (>=2021.08.31) ; extra == 'docs'
Requires-Dist: sphinx (~=6.0) ; extra == 'docs'
Requires-Dist: sphinx-argparse-cli (>=1.5) ; extra == 'docs'
Requires-Dist: sphinx-autodoc-typehints (>=1.10) ; extra == 'docs'
Provides-Extra: test
Requires-Dist: filelock (>=3) ; extra == 'test'
Requires-Dist: pytest (>=6.2.4) ; extra == 'test'
Requires-Dist: pytest-cov (>=2.12) ; extra == 'test'
Requires-Dist: pytest-mock (>=2) ; extra == 'test'
Requires-Dist: pytest-rerunfailures (>=9.1) ; extra == 'test'
Requires-Dist: pytest-xdist (>=1.34) ; extra == 'test'
Requires-Dist: wheel (>=0.36.0) ; extra == 'test'
Requires-Dist: setuptools (>=42.0.0) ; (python_version < "3.10") and extra == 'test'
Requires-Dist: setuptools (>=56.0.0) ; (python_version >= "3.10") and extra == 'test'
Provides-Extra: typing
Requires-Dist: importlib-metadata (>=5.1) ; extra == 'typing'
Requires-Dist: mypy (==1.1.1) ; extra == 'typing'
Requires-Dist: tomli ; extra == 'typing'
Requires-Dist: typing-extensions (>=3.7.4.3) ; extra == 'typing'
Provides-Extra: virtualenv
Requires-Dist: virtualenv (>=20.0.35) ; extra == 'virtualenv'

# SchemaChangeRiskEngine (SCRE):

A tool for assessing the risk of schema changes in a MySQL database when using tools like gh-ost or flywheel.

## The problem

Based on [Impact analysis of database schema changes](https://www.researchgate.net/publication/221555365_Impact_analysis_of_database_schema_changes)
and real world learning. It was found we should restrict riskier changes and patterns.

Such patterns include:

* BLOB & TEXT column overuse and storage/memory waste
* ENUM columns issues with casting during value parsing of a change
* SET columns issues with casting during value parsing of a change
* Foreign Key and Trigger usage preventing non-blocking and non-atomic changes
* No Primary Key causing slow migration or table level locking verses row level locking
* Renaming columns and tables leading toward application, data warehouse, and data lake sync issues

## The solution

This tool addresses this by allowing you to pass any CREATE or ALTER statement, and it will return a boolean if it's safe.

### Example

```python
from schema_change_risk_engine import SchemaChangeRiskEngine as SCRE

engine = SCRE()
changeStatements = [
    """
        CREATE TABLE `test` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL,
        PRIMARY KEY (`id`)
        )
        ENGINE=InnoDB
        DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_0900_ai_ci
    """,
    "ALTER TABLE `test` ADD COLUMN `age` int(11) NOT NULL DEFAULT 0",
    "ALTER TABLE `test` RENAME COLUMN `age` to `years_old`",
    "ALTER TABLE `test` ADD COLUMN `gener` ENUM('M', 'F','T','NC') NOT NULL DEFAULT 'NC'",
    "ALTER TABLE `test` ADD COLUMN `hobbies` SET('S', 'R','T','NC') NOT NULL DEFAULT 'NC'",
    "ALTER TABLE `test` ADD COLUMN `bio` TEXT NOT NULL",
    "ALTER TABLE `test` ADD COLUMN `photo` BLOB NOT NULL",
    "ALTER TABLE `test` ADD COLUMN `order_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
    "ALTER TABLE `test` ADD TRIGGER `test_trigger` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO `test` (`name`) VALUES ('test'); END",
    "ALTER TABLE `test` ADD FOREIGN KEY (`id2`) REFERENCES `test` (`id`)",
    "ALTER TABLE `test` RENAME TO `test2`",
    "ALTER TABLE `test` RENAME TABLE `test2` TO `test`"
]

> for idx, change in enumerate(changeStatements):
    print("Statement %s: %s" % (idx + 1, engine.validate(change)))

Statement
1: (True, None)
Statement
2: (True, None)
Statement
3: (False, 'Renaming columns is not allowed')
Statement
4: (False, 'ENUM data type is not allowed')
Statement
5: (False, 'SET is not allowed')
Statement
6: (False, 'TEXT columns are not allowed')
Statement
7: (False, 'BLOB columns are not allowed')
Statement
8: (False, 'DATETIME data type is not allowed')
Statement
9: (False, 'Triggers are not allowed')
Statement
10: (False, 'Foreign keys are not allowed')
Statement
11: (False, 'Renaming tables is not allowed')
Statement
12: (False, 'Renaming tables is not allowed')



```
