SQL

Технология баз данных используется для сохранения данных на дисках, поиска нужной информации. Хранящиеся данные мы будем называть базой данных. Мы создадим базу данных, в которой будем сохранять оценки студентов. Мы будем использовать систему управления базами данных (СУБД) PostgreSQL. Сервер PostgreSQL - запускается на компьютере на котором хранятся и сами данные. Сервер принимает запросы от программ клиентов, написанные на языке SQL, и возвращает ответы.

Создание базы данных на render.com

Мы могли бы установить postgresql локально, но мы разместим нашу базу данных на render.com, online web hosting service.

1. Зайдите на https://render.com/, создайте аккаунт, если его ещё нет.
2. На dashboard "New" и выберите "PostgreSQL".
3. Поле Name задайте значение hogwarts. Нажмите конопку Create Database
4. Скопируйте в ide значения internal Database URL, External Database URL.

В URL указан протокол postgres, имя пользователя, пароль, адрес сервера, название базы данных. Нам будет удобно сохранить external URL в переменную окружения, чтобы иметь к ней доступ из разных программ. Перейдите в ide.cs50.io, создайте в домашнем каталоге файл с именем .profile . В этом файле введите строку

export DB_URL=ваш_url

Закройте терминал, откройте снова. Когда мы открываем терминал, он считывает ~/.profile и выполняет команды в нём. Написанная нами строчка создаёт переменную окружения. Проверьте, что терминал имеет к ней доступ, для этого введите в терминале

echo $DB_URL

psql

psql $DATABASE_URL

Если все прошло удачно, то перед нами окно, в котором мы можем вводить запросы к серверу. Нам надо освоить четыре вида запросов на языке SQL: создать, прочитать, изменить и удалить. Начнём с создать. Создадим таблицу задания.

create table задание(задание  serial primary key, название text);

Первый столбец таблицы имеет тип serial - это целые числа, автоматически увеличивающиеся, столбец будет primary key (первичный ключ), мы будем использовать значения из него для ссылки из других таблиц. Столбец название будет содержать данные типа текст. Добавим данные в таблицу.

insert into задание(название) values ('Приворотное зелье'), ('Напиток живой смерти');

select * from задание;

drop table if exists имя_таблицы;

create table факультет(факультет serial primary key, название text);
insert into факультет(название) values ('Гриффиндор'), 
('Слизерин'), ('Когтевран'), ('Пуффендуй');
select * from факультет;
create table роль(роль serial primary key, название text);
insert into роль (название) values ('профессор'), ('ученик');
select * from роль;
create table герой(герой serial primary key, фамилия text, имя text,
с_факультета int references факультет(факультет), 
выполняет_роль int references роль(роль),
username text, password text);
insert into герой (фамилия, имя, с_факультета, 
выполняет_роль, username, password) values
('Поттер', 'Гарри', 1, 2, 'potter', '12345'),
('Грейнждер', 'Гермиона', 1, 2, 'germiona', '12345'),
('Уизли', 'Рон', 1, 2, 'uron', '12345'),
('Снегг', 'Северус', 2, 1, 'snegg', '12345');
select фамилия, имя, факультет.название from герой, факультет where 
герой.с_факультета = факультет.факультет and герой.с_факультета=1;
create table журнал(выполнил_задание int references задание(задание), 
оцениваем int references герой(герой), получил_оценку int, 
primary key(выполнил_задание, оцениваем));
insert into журнал (выполнил_задание, оцениваем, получил_оценку) values
(1, 1, 5), (2, 1, 5), (1, 2, 5), (2, 2, 5), (1, 3, 4), (2, 3, 3);
select * from  журнал;
select герой.фамилия, задание.название, получил_оценку 
from журнал, герой, задание where 
журнал.оцениваем = герой.герой and 
журнал.выполнил_задание = задание.задание;

SQL запросы из nodejs

"type":"module"

npm install pg 

//Импортируем класс Pool из модуля pg
import dotenv from 'dotenv'
import pg from "pg";
dotenv.config()

//Создаём пул соединений с сервером.
export const pool = new pg.Pool({connectionString: process.env.DB_URL,
	    ssl: { rejectUnauthorized: false}
})

Объект process, предоставляет информацию о текущем процессе. Через свойство env мы получаем доступ к переменным окружения.

Создайте файл test.js.

import {pool} from "./pgConfig.js"
const sql = "select s1.фамилия, задание.название, \
	s1.получил_оценку from \
	(select герой.фамилия, журнал.получил_оценку, журнал.выполнил_задание \
	from журнал join герой on \
	журнал.оцениваем = герой.герой where герой.фамилия = $1) \
	as s1 join задание on s1.выполнил_задание = задание.задание;"

async function f(){
	const results = await pool.query(sql, ["Поттер"]);
	pool.end();
	console.log(results.rows);
	}
f();

Связь веб приложения с БД.

npm install express ejs body-parser

Создадим каталог views, в нём мы будем сохранять шаблоны веб страниц. Создайте в нём файл index.ejs.