Standardize sql entities from 'setup_tables.py'

[jeff1evesque]

We need to update our setup_tables.py, based on the following diagram:

The following are some of the business rules:

• A user may have zero or more permissions, while a given permission may be associated with zero or more users
• A user may have zero or more roles, while a given permission may be associated with zero or more roles
• A role may have zero or more permissions, while a given permission may be associated with zero or more permission
• A permission must have exactly one permission value, while a permission value may be associated with zero or more permissions
• A permission must be associated with exactly one permission type, while a permission type may be associated with zero or more permissions
• A user may own zero or more results, while a result must be owned by exactly one user
• A role may own zero or more results, while a result may be associated with zero or more roles
• A user may own zero or more models, while a model must be owned by exactly one user
• A role may own zero or more models, while a model may be associated with zero or more roles
• A user may own zero or more collections, while a collection must be owned by exactly one user
• A role may own zero or more collections, while a collection may be associated with zero or more roles
• A result must have exactly one model type, while a model type can be associated with zero or more results
• A result may have one or more result value, while a result value must have exactly one result
• A result may have one or more result value parameter, while a result value parameter must have exactly one result

Note: the corresponding docx can be reviewed, which provides a full outline of requirements. This will likely explode to various other feature requests.

[jeff1evesque]

5c759c9: we've reapplied the proposed legacy tables, since this issue doesn't not entail converting the backend logic, which implements the sql tables. As other features are developed to apply the new tables, or replace existing tables, each corresponding legacy table will be phased out. This iterative phasing, will put less of a burden on this issue, to completely transform the application to be compliant with the proposed new database structure.

Note: we'll need to create additional issues to connect the new/updated tables to the backend, which may entail creating additional issues for the frontend. This will significantly define milestone 0.8.

[jeff1evesque]

Our earlier docx references of "Enterprise Keys", are more commonly known as GUID. However, our flavor of SQL, specifically mariadb, uses UUID. Furthermore, our crafted implementation, will be time-based. To sum, we'll need to make some adjustments to our earlier word document, which will entail fixing the corresponding diagram, since we'll need to add two additional UUID based tables. One for permissions, and a second for Ownership. However, we'll proceed initially to mocking up code, with a later intention to fix the corresponding word document, and diagram.

[jeff1evesque]

b2589ef: we need to test the proper syntax to add the CREATE ROUTINE privilege on the database level. This will allow us to run, our two custom functions to create UUID. So, we'll need to test if the proper location to specify this permission, is when we create a database, since mysql_grant derived permissions, seem to require specifying a table.

Note: the corresponding puppet module, provides additional provisioning examples.

[jeff1evesque]

138dbdb: is sufficient syntax, providing the ability to run our custom functions, used to compute our UUID. It turns out, we could rely on .* to signify the database level permissions. However, we have some sql syntax errors, indicated by our travis ci.

[jeff1evesque]

The corresponding docx has been updated.