stamler / charade Goto Github PK

View Code? Open in Web Editor NEW

0.0 1.0 0.0 108 KB

Realtime JSON API translator for SQLAlchemy models

License: MIT License

Python 95.81% Shell 1.73% Dockerfile 2.46%

json-api sqlalchemy

charade's Introduction

charade

Realtime JSON API translator for SQLAlchemy models

MIT License

Charade is a Python WSGI application that connects to any database supported by SQLAlchemy and loads the schema defined in model.py. It then presents a sensible JSON API based on that schema. If no model.py is provided, it will use SQLAlchemy Automap to reflect and generate an API automatically. Middleware is provided to authenticate against Microsoft Azure AD.

Charade is built, tested, and deployed inside a Docker container.

Installation

Set mysql and AzureAD environment variables in Dockerfile
cd /path/to/charade
[optional] edit config.py
docker build -t charade .

Run

docker run -v /path/to/app:/app -p 9090:9090 charade

Roadmap

N-to-N relationship behaviour (Users to Projects) Users detail should list Projects /Users/{id}/Projects Projects detail should list Users /Projects/{id}/Users
If a table has more than one foreign key and those FKs each reference tables with no Foreign keys, build a many-to-many relationship endpoint: i.e. /TableA/{id}/TableB

API

GET       /
GET       /Resources
GET       /Resources/id
POST      /Resources
PUT/PATCH /Resources/id
DELETE    /Resources/id

Versioned sections of a table

Move the current FK into the primary table. For example, Computers should have a locations_id column that's an FK referencing locations.id. The old way was to have a joiner table and this is much more complex to query and insert to.
When inserting a new item into the primary table (ie Computers) include an FK if desired but it's not necessary.
When updating an item in the primary table (ie Computers) use the runtime inspection API before a flush to verify whether specified attributes have changed. InstanceState and AttributeState may be the tools used. If one of the specified attributes has been changed, save the previous value to a separate versions table defined in the model (ie ComputerLocations_history).

charade's People

Contributors

Watchers

charade's Issues

Historical value handling

When a table exists that stores history of a property on a primary table, this is a 1-to-N relationship. The behaviour should be such that a GET request to, for example, /Computers/{id}/LocationsHistory should return a list of objects with historical locations and datetimes of creation.

implement Authorization

Some sort of RBAC needs to be implemented against the existing AzureAD system. If Azure resource tokens can include security group information (it can) then the actual configuration can happen in the on-premises AD instance. The charade database stores a list of tasks (id, HTTP verb, Resource) and permissions (id, task_id, group). A hook in the router will check group id of the supplied token and compare it to the requested resource. If it's not allowed, the system will return an HTTP error, possibly stating the required permissions?

fix Docker config so it actually runs as advertised

Lots of work happening outside of docker but deployment is crucial. Ensure deployment from docker works with environment variables.

Move all instance configuration into model.py

All necessary configuration for an instance should be inside model.py except for possibly the AzureAD stuff (tenant/app etc). This should include complete validation of object properties.

implement support for PATCH

the HTTP PATCH method against an individual resource object will map to an UPDATE statement in SQLAlchemy. The format will likely be JSON Patch. This will complete CRUD functionality as POST, GET, PATCH, DELETE HTTP verbs for every resource. Some resources will return 405 Method Not Allowed, of course.

implement 'add' and 'replace' JSON Patch ops in PATCH method

JSON Patch (RFC 6902) supports 6 operations: add, remove, replace, move, copy, and test. Currently only replace is defined. add and replace should reflect adding objects to related tables rather than the primary table since by implication they are adding or removing properties that don't exist as columns in the primary table. Otherwise we would simply be doing a replace, even to set an unset value for the first time.

double log entries, either process duplication or just log issues

5f467bb introduces an issue where multiple of debug log entries (from __init__ Database and onward) are in duplicate. Either logging is running in duplicate or there are two copies of the running process.

write tests

No unit tests or integration tests have been created for charade. Cannot release without creating a solid test suite, verifying coverage, verifying passing.

Separate tests for the AzureADTokenValidator could be useful as well, although this middleware would ideally be broken out into a separate project / importable module

RBAC built against custom model.py doesn't work with reflected database (no model.py)

There is a private model.py file which provides a schema and functionality to charade. If this file is not available then charade will reflect the entire database and is supposed to operate normally. Unfortunately the custom model.py creates table names that don't match the reflected class names if reflection is used, breaking auth.

The best approach to fix this is to break authorization out into a submodule that always uses the same table names and model.py then import this into charade as middleware.

The interim solution is to hard-code the auth schema and tables into database.py then reflect any other data tables into the app for export as a JSON API. This is a useful intermediate step to breaking out the authorization system as well.

Child-object handling

Databases have foreign keys which define 1-to-N relationships. GET requests to endpoints such as /SoftwareTitles/{id}/SoftwareKeys should return a list of SoftwareKeys objects that belong to the specified SoftwareTitles object. These could be cached in client to reduce requests as well.

Support versioned one-to-many relationships

Leverage SQL Alchemy for versioned joins with history. Some examples are here. This is the most terse example. Some further potentially useful background is here.

After considering this problem, the basic implementation should centre around having the current value of a property being stored in the row of the table. Previous value(s) would be stored in a separate table with a datetime stamp indicating when that record was created. Rows would be added to the history table every time a row is edited in the primary table. There would be one history table per property.

JSON API compatibility

Ensure all responses (including errors) conform to JSON API. Also ensure that requests that do not confirm to JSON API are replied to with an error message.

The falcon documentation discusses a JSON Schema validator that may be useful.

Let's Encrypt integration in the Docker image

Build functionality inside the Docker image to simply acquire a Let's Encrypt certificate for rapid deployment.