banco_dados.md

@@ -33,10 +33,96 @@ We have decided to use two types of database Firebase noSQL for user and Postgre
 
 <img src="resources/esquemaLogico.svg">
 
-Aqui deve ser explicado com modelos e explicações como o Banco de Dados foi construido, onde se deve focar em:
-
-- Como ele foi desenvolvido, com Imagens e Diagramas
-- o Collections(Entities)
-- o ScriptSQL
 
 ## Implementação
+
+This subsection is dedicated to elucidating the implementation of the database, be it SQL or noSQL. All implementation was done using the knex library.
+
+### Knex
+
+Knex is a technique that is used to build queries. It supports various databases like Postgres, MySQL, SQLite, Oracle, and some others as well. It provides both callbacks and promise interface. It provides connection pooling and standardized responses.
+
+### schemas
+
+Thinking about the user flow, he will need to have a login to save favorite recipes and send new recipes. Because of this, we use firebase to create a user profile.
+The implementation of the profile follows the script defined in the logical scheme of the previous session.
+
+#### Postgrees
+
+The tables created in Postgrees SQL are made through the knex and can be found in the backend inside this directory `OLIVE-RECIPES-API/src/database/migrations/`. 
+
+**recipes**
+
+Script for table creation:
+`
+export async function up(knex: Knex): Promise<void> {
+  return knex.schema
+    .hasTable('recipes')
+    .then((exists) => {
+      if (!exists) {
+        return knex.schema.createTable('recipes', (table) => {
+          table.increments('recipe_id').primary().notNullable();
+          table.string('ingredients', 1000).notNullable();
+          table.string('description', 1000).notNullable();
+          table.integer('preparation_time').unsigned().notNullable();
+          table.integer('difficulty').unsigned().notNullable();
+          table.string('user_id', 255).notNullable();
+          table.string('status', 20).notNullable();
+          table.integer('likes_number').notNullable();
+          table.string('title', 150).notNullable();
+        });
+      }
+    });
+}
+`
+Then it was necessary to add a script to change the recipes table. It is necessary to have a new column containing the information `active`, as the recipes will not have hard delete, only soft delete. In this case, the change follows:
+
+`
+export async function up(knex: Knex): Promise<void> {
+  return knex.schema.alterTable('recipes', (table) => {
+    table.integer('active', 1).notNullable().defaultTo(1);
+  });
+}
+`
+
+**steps**
+
+All recipes need steps to be made, below the script:
+
+`
+export async function up(knex: Knex): Promise<void> {
+  return knex.schema.hasTable('steps')
+    .then((exists) => {
+      if (!exists) {
+        return knex.schema.createTable('steps', (table) => {
+          table.increments('step_id').primary().notNullable();
+          table.string('title', 100).notNullable();
+          table.string('description', 1000).notNullable();
+          table.integer('position').notNullable();
+          table.integer('recipe_id').references('recipes.recipe_id').onDelete('CASCADE').onUpdate('CASCADE');
+        });
+      }
+    });
+}
+`
+
+**tags**
+
+At this point, we need put some tags, so here was the script:
+`
+export async function up(knex: Knex): Promise<void[]> {
+  return Promise.all([
+    knex.schema
+      .createTable('tags', (table) => {
+        table.increments('tag_id').primary().notNullable();
+        table.string('title', 100).notNullable();
+      }),
+    knex.schema
+      .createTable('recipes_tags', (table) => {
+        table.increments('id').primary().notNullable();
+        table.integer('recipe_id').references('recipes.recipe_id').onDelete('CASCADE').onUpdate('CASCADE');
+        table.integer('tag_id').references('tags.tag_id').onDelete('CASCADE').onUpdate('CASCADE');
+      }),
+  ]);
+}
+`
\ No newline at end of file