<commit_before># Extension 01 Setup - REST API Postgres

## Prerequisites

### Postgres

#### Installation
Download the latest version of Postgres for your OS at:  
https://www.postgresql.org/download/

Follow the instructions to finish the installation.  
--> Make sure to remember the __pwd__.  
--> The admin user will be: __postgres__

#### Setup
- Open the management tool for Postgres (e.g. pgAdmin).
- Create a new database (name: blog)
- Create a user
- Create a new table with a sequence for the id in the database (schema:public):

__create user__  
Create a user in the user administration e.g. pgAdmin - in this case we used __"dbUser"__.


__create table:__  
```sql
CREATE SEQUENCE post_id_seq;
CREATE TABLE public.post
(
    "id" integer PRIMARY KEY default nextval('post_id_seq'),
    "title" character varying(40),
    "text" character varying(1024)
);
alter sequence post_id_seq owned by public.post.id;
GRANT ALL ON TABLE public.post TO "dbUser";
GRANT ALL ON SEQUENCE post_id_seq TO "dbUser";
GRANT ALL ON TABLE public.post TO "postgres";
```
Insert some records to have some sample data:

```sql
INSERT INTO public.post("title", "text")
	VALUES ('Welcome to my blog', 'This is the first blog entry...'),
    ('The second post', 'Here we go, post number two...'),
    ('Nuuuuuuuuumber 3 is here', 'Hello there mr. three');
```

## Back to the js-project

## Folder structure:
```bash
project  
│   .babelrc
│   babel.hook.js
│   babel.hook.mocha.js
│   package.json
│   webpack.config.js  
│
└───app
│   │   application data (react redux)
│
└───extensions
│   │   different extensions e.g. api setup etc.
│     
└───server
    │   index.html.js
    │   index.js  
    │
    └───models
    │   │   post.js
    │   │
    │   └───db
    │       │   index.js
    │       │   post.sql.js
    │        
    └───router
    │   │   post.router.js
    │
    └───test
        │   test.api.post.js

```

#### Installation
Install the pg (postgress package) and the koa bodyparser (to work with post - form and json data) with the follwoing command:
```bash
$ npm install pg@7.3.0 koa-bodyparser@4.2.0
```

#### project setup

__model & db:__  
Add the model and the db folter to the server directory.  
In the __db__ folder we need two files:

__index.js:__  
The file will establish the connection to the postgres database based on a pool and the provided parameter.  
Make sure that all the paramter fit what was setup in postgres!

```javascript
import {Pool} from 'pg'
const pool = new Pool({
  user: 'dbUser',
  host: 'localhost',
  database: 'blog',
  password: '<PASSWORD>',
  port: 5432,
})
export default pool
```

__post.sql.js__:  
In this file we will add the select statements for our api.
As this is only for demo purposes we will not do any authentication and we will create all main
routes:
- select the list
- select a single post
- insert a new post
- delete an exsisting post
- update an exsisting post

We will use the connection from the db connection setup file and maintain all required sql statements for the post object.

Let's have a look at the examples for getting all post and updating the posts in the upcoming sections.  
The rest can be reviewed in the repository.


```javascript
export default {
  selectPosts: () => pool.query('SELECT * FROM public.post'),
  updatePost: (id, title, text) =>
    pool.query('UPDATE public.post SET title = $2, text = $3 WHERE id = $1',
    [id, title, text])
  ...<commit_msg>typos<commit_after># Extension 01 Setup - REST API Postgres

## Prerequisites

### Postgres

#### Installation
Download the latest version of Postgres for your OS at:  
https://www.postgresql.org/download/

Follow the instructions to finish the installation.  
--> Make sure to remember the __pwd__.  
--> The admin user will be: __postgres__

#### Setup
- Open the management tool for Postgres (e.g. pgAdmin).
- Create a new database (name: blog)
- Create a user
- Create a new table with a sequence for the id in the database (schema:public):

__create user__  
Create a user in the user administration e.g. pgAdmin - in this case we used __"dbUser"__.


__create table:__  
```sql
CREATE SEQUENCE post_id_seq;
CREATE TABLE public.post
(
    "id" integer PRIMARY KEY default nextval('post_id_seq'),
    "title" character varying(40),
    "text" character varying(1024)
);
alter sequence post_id_seq owned by public.post.id;
GRANT ALL ON TABLE public.post TO "dbUser";
GRANT ALL ON SEQUENCE post_id_seq TO "dbUser";
GRANT ALL ON TABLE public.post TO "postgres";
```
Insert some records to have some sample data:

```sql
INSERT INTO public.post("title", "text")
	VALUES ('Welcome to my blog', 'This is the first blog entry...'),
    ('The second post', 'Here we go, post number two...'),
    ('Nuuuuuuuuumber 3 is here', 'Hello there mr. three');
```

## Back to the js-project

## Folder structure:
```bash
project  
│   .babelrc
│   babel.hook.js
│   babel.hook.mocha.js
│   package.json
│   webpack.config.js  
│
└───app
│   │   application data (react redux)
│
└───extensions
│   │   different extensions e.g. api setup etc.
│     
└───server
    │   index.html.js
    │   index.js  
    │
    └───models
    │   │   post.js
    │   │
    │   └───db
    │       │   index.js
    │       │   post.sql.js
    │        
    └───router
    │   │   post.router.js
    │
    └───test
        │   test.api.post.js

```

#### Installation
Install the pg (postgress package) and the koa bodyparser (to work with post - form and json data) with the follwoing command:
```bash
$ npm install pg@7.3.0 koa-bodyparser@4.2.0
```

#### project setup

__model & db:__  
Add the model and the db folder to the server directory.  
In the __db__ folder we need two files:

__index.js:__  
The file will establish the connection to the postgres database based on a pool and the provided parameter.  
Make sure that all the paramter fit what was setup in postgres!

```javascript
import {Pool} from 'pg'
const pool = new Pool({
  user: 'dbUser',
  host: 'localhost',
  database: 'blog',
  password: '<PASSWORD>',
  port: 5432,
})
export default pool
```

__post.sql.js__:  
In this file we will add the select statements for our api.
As this is only for demo purposes we will not do any authentication and we will create all main
routes:
- select the list
- select a single post (not included in docu)
- insert a new post (not included in docu)
- delete an exsisting post (not included in docu)
- update an exsisting post

We will use the connection from the db connection setup file and maintain all required sql statements for the post object.

Let's have a look at the examples for getting all post and updating the posts in the upcoming sections.  
The rest can be reviewed in the repository.


```javascript
export default {
  selectPosts: () => pool.query('SELECT * FROM public.post'),
  updatePost: (id, title, text) =>
    pool.query('UPDATE public.post SET title = $2, text = $3 WHERE id = $1',
    [id, title, text])
  ...
{'fraction_non_alphanumeric': 0.10736328405339457, 'fraction_numerical': 0.006315487297258504, 'mean_word_length': 3.190018039687312, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 13, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6611802', 'n_tokens_mistral': 2234, 'n_tokens_neox': 2223, 'n_words': 954}