# 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')
```
Raw data
{
"_id": null,
"home_page": "https://github.com/dbmruphy/SchemaAlterRulesEngine",
"name": "schema-change-risk-engine",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": "",
"keywords": "mysql,schema",
"author": "David Murphy",
"author_email": "David Murphy <david.b.murphy.tx@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/0b/7b/d0fb1f9d6ba44428eb5e780ee8bb00bb82bf306a7b71b3bfadda436a04f8/schema-change-risk-engine-0.0.10.tar.gz",
"platform": null,
"description": "# SchemaChangeRiskEngine (SCRE):\n\nA tool for assessing the risk of schema changes in a MySQL database when using tools like gh-ost or flywheel.\n\n## The problem\n\nBased on [Impact analysis of database schema changes](https://www.researchgate.net/publication/221555365_Impact_analysis_of_database_schema_changes)\nand real world learning. It was found we should restrict riskier changes and patterns.\n\nSuch patterns include:\n\n* BLOB & TEXT column overuse and storage/memory waste\n* ENUM columns issues with casting during value parsing of a change\n* SET columns issues with casting during value parsing of a change\n* Foreign Key and Trigger usage preventing non-blocking and non-atomic changes\n* No Primary Key causing slow migration or table level locking verses row level locking\n* Renaming columns and tables leading toward application, data warehouse, and data lake sync issues\n\n## The solution\n\nThis tool addresses this by allowing you to pass any CREATE or ALTER statement, and it will return a boolean if it's safe.\n\n### Example\n\n```python\nfrom schema_change_risk_engine import SchemaChangeRiskEngine as SCRE\n\nengine = SCRE()\nchangeStatements = [\n \"\"\"\n CREATE TABLE `test` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar(255) NOT NULL,\n PRIMARY KEY (`id`)\n )\n ENGINE=InnoDB\n DEFAULT CHARSET=utf8mb4\n COLLATE=utf8mb4_0900_ai_ci\n \"\"\",\n \"ALTER TABLE `test` ADD COLUMN `age` int(11) NOT NULL DEFAULT 0\",\n \"ALTER TABLE `test` RENAME COLUMN `age` to `years_old`\",\n \"ALTER TABLE `test` ADD COLUMN `gener` ENUM('M', 'F','T','NC') NOT NULL DEFAULT 'NC'\",\n \"ALTER TABLE `test` ADD COLUMN `hobbies` SET('S', 'R','T','NC') NOT NULL DEFAULT 'NC'\",\n \"ALTER TABLE `test` ADD COLUMN `bio` TEXT NOT NULL\",\n \"ALTER TABLE `test` ADD COLUMN `photo` BLOB NOT NULL\",\n \"ALTER TABLE `test` ADD COLUMN `order_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP\",\n \"ALTER TABLE `test` ADD TRIGGER `test_trigger` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO `test` (`name`) VALUES ('test'); END\",\n \"ALTER TABLE `test` ADD FOREIGN KEY (`id2`) REFERENCES `test` (`id`)\",\n \"ALTER TABLE `test` RENAME TO `test2`\",\n \"ALTER TABLE `test` RENAME TABLE `test2` TO `test`\"\n]\n\n> for idx, change in enumerate(changeStatements):\n print(\"Statement %s: %s\" % (idx + 1, engine.validate(change)))\n\nStatement\n1: (True, None)\nStatement\n2: (True, None)\nStatement\n3: (False, 'Renaming columns is not allowed')\nStatement\n4: (False, 'ENUM data type is not allowed')\nStatement\n5: (False, 'SET is not allowed')\nStatement\n6: (False, 'TEXT columns are not allowed')\nStatement\n7: (False, 'BLOB columns are not allowed')\nStatement\n8: (False, 'DATETIME data type is not allowed')\nStatement\n9: (False, 'Triggers are not allowed')\nStatement\n10: (False, 'Foreign keys are not allowed')\nStatement\n11: (False, 'Renaming tables is not allowed')\nStatement\n12: (False, 'Renaming tables is not allowed')\n\n\n\n```\n",
"bugtrack_url": null,
"license": "",
"summary": "Common MySQL Schema Alter Issues Check Engine Package",
"version": "0.0.10",
"split_keywords": [
"mysql",
"schema"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "bcdeccc5999257760cbdc1d5fc8eb515d6c99ec4d87abdc3c2df2685e4221704",
"md5": "349089290fc372a9607e4c47d0756d3e",
"sha256": "ab5e082d9c228bd4e5b58118911c8a72a82dc38f5145f79d65390dc960b60a04"
},
"downloads": -1,
"filename": "schema_change_risk_engine-0.0.10-py3-none-any.whl",
"has_sig": false,
"md5_digest": "349089290fc372a9607e4c47d0756d3e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.6",
"size": 3883,
"upload_time": "2023-03-20T05:06:15",
"upload_time_iso_8601": "2023-03-20T05:06:15.605776Z",
"url": "https://files.pythonhosted.org/packages/bc/de/ccc5999257760cbdc1d5fc8eb515d6c99ec4d87abdc3c2df2685e4221704/schema_change_risk_engine-0.0.10-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "0b7bd0fb1f9d6ba44428eb5e780ee8bb00bb82bf306a7b71b3bfadda436a04f8",
"md5": "1aab6d6b1f6d6062278cb0529c877d33",
"sha256": "de06ff5aebd18ce00bd9a82309e2277c9085a18ff8de67db082f782a1e3c0197"
},
"downloads": -1,
"filename": "schema-change-risk-engine-0.0.10.tar.gz",
"has_sig": false,
"md5_digest": "1aab6d6b1f6d6062278cb0529c877d33",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 5600,
"upload_time": "2023-03-20T05:06:17",
"upload_time_iso_8601": "2023-03-20T05:06:17.327379Z",
"url": "https://files.pythonhosted.org/packages/0b/7b/d0fb1f9d6ba44428eb5e780ee8bb00bb82bf306a7b71b3bfadda436a04f8/schema-change-risk-engine-0.0.10.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-03-20 05:06:17",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "dbmruphy",
"github_project": "SchemaAlterRulesEngine",
"lcname": "schema-change-risk-engine"
}
JSON
