The SQL Data Dependency Tool (sqldd
) is a tookit used for analyzing dependencies between rows in a database. Given a table and a primary key, it recursively analyzes foreign keys to generate a map of all downstream tables and foreign keys the initial "root row" depends on.
The sqldd
module is published on the Python Package Index, so you can install it using pip
or easy_install
.
pip install sqldd
Or:
easy_install sqldd
It installs a sqldd
module in the path for simple shell usage.
The most common usage of the tool is from the shell with sqldd
.
For example, to find dependencies of row 53 in table A: :
$: sqldd A 53 --database sqldd_db --pretty
{
"A": [
53
],
"D': [
1,
2,
20
],
"M': [
48
]
}
This indicates that in the complete dependency tree for that row, tables D
and M
have rows that matter to foreign keys. This may not be a direct child dependency of A
, but possibly a sub-dependency (A:53 --> D:1 --> M:48
)
For more complex analysis, where one may want to look at multiple tables/rows, a JSON file can be specified with table names as keys, and values are arrays of primary keys.
{
"A": [53],
"another_table": [1, 4, 10],
"one_more_table": ["string_pk_1", "string_pk_2"]
}
Now invoking will give more output:
$: sqldd --json input.json --database sqldd_db --pretty
{
"A": [53],
"D": [1, 2, 20],
"M": [48],
"P": [800, 908],
"another_table": [1, 4, 10, 22, 28],
"one_more_table": ["string_pk_1", "string_pk_2", "string_pk_4444"]
}
A docker image of the tool is also available. It is invoked using docker run
the same way as the pip command.
$: docker run --rm rkeilty/sqldd A 53 --database my_db --pretty
{
"A": [
53
],
"D': [
1,
2,
20
],
"M': [
48
]
}
usage: sqldd [-h] [--json JSON_FILE] [--server SERVER] [--port PORT]
--database DATABASE [--username USERNAME] [--password PASSWORD]
[--pretty] [--sqldump] [--sqldump_table_defs]
[table] [primary_key]
SQL Data Dependency Tool
One of either ([table][primary_key]) or [--json] is required.
positional arguments:
table Table to analyze
primary_key Primary key for row in table to analyze
optional arguments:
-h, --help show this help message and exit
--json JSON_FILE File containing tables and rows to analyze
--server SERVER Database server
--port PORT Database port
--database DATABASE Database name
--username USERNAME Database username
--password PASSWORD Database password
--pretty Pretty print the output
--sqldump Generate a sql dump file of all dependencies
--sqldump_table_defs Dump _all_ table defs, even those without
dependencies. Useful for constructing skeleton DBs.
- Allow for traversing up from root rows, rather than just downstream dependencies.
- Compound primary keys are not supported.
- Restricted to MySQL, expand connection strings to allow any SQL compatible DB access.
sqldd
is licensed under the terms of the 3-clause BSD license.
All contributions are welcome, including but not limited to:
- Documentation fixes / updates
- New features (requests as well as implementations)
- Bug fixes (see issues list)
If you encounter any errors in the code, please file an issue on github: https://github.com/rkeilty/sql-data-dependency/issues.
- Author: Rick Keilty
- Email: rkeilty@gmail.com
- Repository: http://github.com/rkeilty/sql-data-dependency
- Version: 1.0.0
- Release Date: 2017-02-07
- Release Date: 2017-02-07
- Changes:
- Python 2/3 portability
- Dockerized command
- Basic tests
- Simplified shell connections
- Release Date: 2017-02-03
- Changes:
- Allow for older SQLAlchemy usage
- Release Date: 2017-01-28
- Changes:
- Fix for mysqldump command check
- Documentation updates
- Release Date: 2017-01-27
- Changes:
- Initial release