Modifications to the schema and adapting the MVC files to the changes

I had to write a work description for a potential Filemaker contractor and while doing that, I went over the database schema in detail. I found multiple problems to be fixed, some are already in other issues but I gather it all here as I go through the schema.

These changes should be final unless we have a major blocking problem so if you need any change in the db, now is the time, please comment below.

Main modifications

Fields

• remove artifacts.custom_designation
• artifacts.weight : decimal
• artifacts.thickness decimal
• artifacts.height decimal
• artifacts.width decimal
• add users:active bool
• add users:modified date
• add users:can_view_forum
• rename users:can_view_private_catalogues to users:can_view_private_artifacts
• rename users:can_view_private_transliterations to users:can_view_private_inscriptions
• rename users:can_edit_transliterations to users:can_edit_inscriptions
• rename users:can_view_IPadWeb to users:can_view_cdlitablet
• rename users:download_hd_images to users:can_download_hd_images
• rename users:filtering to users:hd_image_restrict_coll (int) -> use collection id
• add modified (date), email, and affiliation fields to the authors table
• create the articles table
• create the articles_authors table
• add sequence field to authors_publications and articles_authors
• add field "east_asian_order" in authors (bool)
• add inscriptions:structure
• add inscriptions:translation
• add inscriptions:transcription
• remove artifacts:modified_by
• remove artifacts:modified
• add artifacts_publications:publication_comments @larsgw
• remove artifacts:primary_publication_comments
• Convert artifacts:created_by to int (ref to author_id)
• Make sure all these artifacts fields are "bool" type is_school_text, is_public, is_atf_public, are_images_public, is_provenience_uncertain, is_period_uncertain, is_object_type_uncertain
• change to text type : stratigraphic_level, surface_preservation, general_comments
• remove artifacts:general_comments
• remove artifacts:dates_referenced
• remove artifacts:date_comments
• make artifacts:museum_no, condition_description, findspot_square, join_information, artifact_preservation, alternative_years, condition_description text type
• create new table "artifacts_sources" : id artifact_id author_id type (translation or atf) because there can be more than one author
• change name of credits table to artifacts_updates
• remove artifacts.credit_id field
• add a first_name and last_name fields to the authors table
• add last_login_time (date) and inactive (bool) to table users
• add retired_artifacts.retired_by int (fk = authors.id)
• add retired_artifacts.retired_for text
• add inscriptions.jtf text field (or json if that's possible, check FM compatibility)
• Make sure all tables and text fields are encoded to utf8mb4_unicode_ci
• [x] Add a staff bool field to the authors table
• [x] Add a relational table between articles and publications
• Create the highlights table
• Create a role table with roles and granular accesses in it
• Create a users_roles table
• add salt & iterations fields to the db
• change all contemporary date fields to DATETIME format (YYYY-MM-DD HH:mm.. )
• [ ] Check that all FKs are set

Changes in the data itself

• Add 6 highlights entry to the db
• Remove genre.genre id $ 8 after making sure any association from artifacts.genres with that genre id 8 is replaced by setting a 1 flag in the artifact.is_school_text field
• clay artifacts are marked as stone, change their material in artifacts_materials

Adapting the model, controller and view files

• Modify all the models then views, test everything and correct problems...

Others

• Conceptualize how to manage retired entries so we can redirect from old P# to new P#
• Conceptualize how to handle api suggestions and audit trail for artifacts catalogue changes #213 (closed)