[Machine Translation] Create project_id column in Test Postgres tables

[moonlight-komorebi]

Background

<!> This should all be done in the testing database environment. Once this is working together with the updated scripts, we have a separate ticket to actually create those changes in the production database.

Follow the instructions in the Readme to set up your testing environment. This sets up local docker containers for a Postgres Database and PGAdmin instance to manipulate the DB.

<!> Please use the https://github.com/newrelic/docs-website/tree/feature/machine-translation feature branch

In order to reduce code duplication and effort, we will be adding a new column called project_id to a couple of existing tables. This is so we don't need separate tables for each type of translation, and can re-use the same scripts with a little tweaking.

Edit Tables

Adding project_id to the following tables is necessary for us to know whether a page needs to be sent to Machine or Human translation project in Smartling, and also identify how it's been translated when we download it.

• translations
• jobs

We can do that in the PGAdmin console via the Query Tool:

⚠️ SPOILER - Click to see image!

⚠️ SPOILER - Click to see SQL Code!

```sql ALTER TABLE IF EXISTS public.jobs ADD COLUMN project_id TEXT; ALTER TABLE IF EXISTS public.translations ADD COLUMN project_id TEXT; ```

Edit Models

We will also need to alter the Models files for both jobs.js and translations.js to make sure they reflect the new table structure:

⚠️ SPOILER - Click to see Code!

```js project_id: { type: DataTypes.INTEGER, allowNull: false, references: { model: Projects(sequelize, DataTypes), key: 'projects', deferrable: Deferrable.INITIALLY_IMMEDIATE, }, }, ```

Edit Test Creation Code

We will also want to alter the query code for the testing docker container in creation_and_cleanup.sql, adding the following to the CREATE TABLE translations() and CREATE TABLE jobs() functions:

⚠️ SPOILER - Click to see Code!

- `project_id TEXT`

Testing Existing Scripts

🛑 You can use the MT Project ID for this but only test with a 1 word change, see here

The reason for this is we have a 2 million word limit per year specifically for Machine Translation

Average is about 850 words per document. total is about 1.6 million. For MT it’s a total of 2 million words can be translated over a year. we have approximately 1800 pages x 850 (avg word count per page) = 1.6 million

Acceptance Criteria

note: All Deven team members should be given access to AWS. - @zstix

• [x] Created new columns in the tables of the test environment
• [x] Edit the model files
• [x] Edit the test Creation Code
• [x] Test the current scripts do not break anything in the testing environment

Useful Links

• Current database schema
• AWS RDS Database
• Database Repo - the physical resources in AWS are terraformed, the database schema is manumatic. so if we a new table, or have to add a new field, need to expand the current script for that.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be automatically closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it was a stale issue that had no recent activity. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be automatically closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be automatically closed if no further activity occurs. Thank you for your contributions.

[jpvajda]

@rudouglas will work on AC...

[moonlight-komorebi]

project_id column should be string. i dont know for sure that the vendor uses non-integer ids, but string is a more flexible / forgiving type just in case.

[rudouglas]

@nr-kkenney is there a STRING type? I thought the main string types were VARCHAR and TEXT which are essentially the same: https://www.postgresqltutorial.com/postgresql-char-varchar-text/

[moonlight-komorebi]

@rudouglas that is what i meant 🙈 didnt know the db types off the top of my head, but you got to the right place 👍

at a quick glance, it looks like the project_id value is not a number, so some text type is probably what we want.

[rudouglas]

ah cool that makes sense 🍾