Learning programming examples

TypeScript basics

Python basics

Bash basics

PostgreSQL basics

Реляционная модель

• Данние воспринимаются пользователем как таблицы
• Способность поддерживать связи между элементами данных
• Каскадное обновление или удаление данных (автоматическое и декларативное)
• До реляционная эпоха: файли, записи, поля
• Реляционная эпоха: таблица, строка, столбец (атомарное значение)
• Формальная теория: отношение, кортеж, атрибут (теория множеств и исчисление предикатов)
  • Отношение: заголовок и тело
    • Степень отношения - количество атрибутов
    • Кардинальное число - количество кортежей
• Ключи - используются для идентификации строк в таблицах и для связи таблиц между собой
  • Потенциальный ключ - неизбыточная комбинация атрибутов таблицы, позволяющая уникальным образом идентифицыровать строки в ней
  • Первычный ключ - один из потенциальных ключей (остальные потенциальные ключи алтернативные ключи)
    • Первычный ключ может быть естественным или суррогатным
    • Суррогатных ключ - позволяет сократить число атрибутов во внешних ключах до одного
  • Внешний ключ - ссылается на потенцыальный ключ в ссылочной таблице (ссылочная целостность)
    • Внешний ключ служит для связи таблиц между собой
    • Внешний ключ не обязан быть уникальным
    • Внешний ключ обеспечивает каскадное обновление и удаление данных
• Индекс - ускоряет доступ к строкам таблицы и предотвращает дублирование значений ключевых атрибутов
  • Для первичного ключа индекс создается автоматически
• Транзакция - набор операций над базой данных, рассматриваемых как единая и неделимая единица работы, выполняемая полностью или не выполняемая вовсе, если произошел сбой (обеспечение согласованности данных)

Язык SQL

• SQL является основным и единым языком доступа к RDBMS
  • Data Definition Language (DDL): CREATE, ALTER, DROP
  • Data Manupulaiton Language (DML): INSERT, UPDATE, DELETE, SELECT
  • Transaction Control Language (DCL): BEGIN, COMMIT, ROLLBACK
  • Data Control Language (DCL): GRANT, REVOKE

Типы данных

• Числовые типы
  • Челочисленные типы: smallint, integer, bigint
  • Числа фиксированной точности: numeric(scale, precision) (псевдоним decimal)
    • SELECT 0.1::numeric * 10 = 1.0::numeric > True
  • Числа с плавающей точкой: real, double precision (Inf, -Inf, NaN)
    • float(p) може быть или real или double precision в зависимости то (p) (стандарт SQL)
    • SELECT 0.1::real * 10 = 1.0::real > False
  • Последовательные типы: smallserial, serial, bigserial (уникальные значения суррогатного первичного ключа)
    • pk serial = CREATE SEQUENCE sq, pk integer NOT NULL DEFAULT nextval(sq), ALTER SEQUENCE sq OWNED BY pk
• Строковые типы
  • character(n) - ограниченная строка, дополняется пробелами (псевдоним char(n))
  • character varying(n) - ограниченная строка, не дополняется пробелами (псевдоним varchar(n))
  • text - неограниченная строка
• Типы дати и времени
  • date - current_date
  • time - current_time(p)
  • timestamp - current_timestamp(p), timestamp '2019-05-31 12:34:56'
  • timestamptz - current_timestamp at time zone 'UTC'
  • interval - '1 year 2 month [ago]'::interval
• Логический тип
  • boolean - TRUE, FALSE, NULL (трехзначная логика)
• Массивы
  • integer[] - '{1, 2, 3, 4}'::integer[], '{"alpha", "beta", "gamma"}'::text[], arr[1], arr[1:2]
• Типы JSON
  • json - JSON сохраняется в строковом виде, каждый раз нужно делать разбор при доступе
  • jsonb - JSON сохраняется в двоичном виде, быстрый доступ (рекомендуемый тип)
  • '{"name": "Vlad", "happy": true}'::jsonb, json->'name', json->0
  • SELECT '{"first_name": "Volodymyr"}'::jsonb || '{"last_name": "Prokopyuk"}'::jsonb;
  • SELECT '{"first_name": "Vlad"}'::jsonb || '{"last_name": "Prokopyuk"}'::jsonb - 'last_name';

Язык опредения данных

• Значение по умолчанию <атрибут> DEFAULT <выражение> (current_timestamp, current_user)
• Ограничение <атрибут> NOT NULL (CHECK <атрибут> IS NOT NULL)
• CONSTRAINT <имя ограничения>
• Ограничение CHECK <выражение>
• Ограничение UNIQUE <атрибуты> (потенциальный ключ)
  • Для ограничений UNIQUE автоматически создается индекс
• Ограничение PRIMARY KEY <атрибуты> (UNIQUE <атрибуты> NOT NULL) (первычный ключ)
  • Для ограничений PRIMARY KEY автоматически создается индекс
• Ограничение FOREIGN KEY <атрибуты FK> REFERENCES <атрибуты PK> ON {UPDATE | DELETE} {CASCADE | RESTRICT (default) | SET NULL | SET DEFAULT} (ссылочная целостность) (внешний ключ)
• COMMENT ON {DATABASE | SHCEMA | TABLE | COLUMN} <таблица>.<столбец> IS <комментарий>;
• CREATE VIEW <представление> AS <запрос>; SELECT * FROM <представление>
  • В отличии от таблиц, представления не содержат данных. При каждом обращению к представлению данные выбираються из таблиц
  • Представления являються интерфейсом доступа к данным, но сам запрос <запрос> может изменяться. Использование представлений упрощает присладные программы и скривает сложноть запросов
  • Представления избавляют от необходимости создавать дополнительные таблицы, дублируя данные для разных нужд использования данных
  • Представления являються сохранненными запросами в базе данных
  • Представления являються средством разграничения полномочий доступа к данным
  • A VIEW is not phisically materialized
  • The query is run every time the VIEW is referenced
  • A TEMPORARY VIEW is automatically dropped at the end of the current session
• CREATE MATERIALIZED VIEW <представление> AS <запрос>; REFRESH MATERIALIZED VIEW <представление>; SELECT * FROM <представление>
  • Материализованное представление снижает время выполнения сложных запросов
  • Пример: для формарования отчета требуется длительное врея, а запросы к отчету будут неоднократными
  • A MATERIALIZED VIEW is populated immediately
  • REFRESH MATERIALIZED VIEW refreshes the VIEW content later upon demand
• CREATE SCHEMA <схема>; SET search_path = <схемы>
  • Схема это логическая часть базы данных, в которой содержаться объекты бази данних (таблицы, представления)
  • Схема образут пространство имен для объектов бази данных

Язык изменения данных

• INSERT INTO <таблица> SELECT * FROM <таблица> [RETUNRNING <выражение>]
• INSERT INTO <таблица> VALUES <значения> [ON CONFLICT {DO NOTHING | DO UPDATE SET <значения>}] [RETUNRNING <выражение>]
• COPY <таблица> FROM <файл> - массовый ввод данных с файла
• COPY <таблица> TO <файл> - массовый вывод данных в файл
• UPDATE <таблица> SET <значения> [FROM <таблицы>] WHERE <условия> [RETUNRNING <выражение>]
• DELETE FROM <таблица> [USING <таблицы>] WHERE <условия> [RETUNRNING <выражение>]
• TRUNCATE <таблица>

Индексы

• Индекс - это специальная упорядоченная струстура данных (btree), которая создается на основе полей каждой строки таблицы и служит для повышения производительности операций выборки, обновления и удаления строк из таблицы
• Строки в таблице хранятся в неупорядоченном виде
• Элемент индекса содержыт значение сформированное на основе одного или нескольких полей и выражений с ними строки таблицы и указатель (прямую ссылку) на строку таблицы
• Индекс является упорядоченной структурой, что значительно ускоряет поиск данных в индексе и заменяет полный перебор строк таблицы
• Индекс требует некоторых накладных расходов на его создание и поддержание
• Нндекс позволяет выполнить проверку на дублирование (потенцыального UNIQUE или первичного PRIMARY KEY ключа) очень быстро
• Наличие индекса ускоряет выборку строк из таблицы, если индекс создан не основе полей в предложении WHERE, по которым наиболее часто производиться выбокра
• Индекс, созданный по столбцу, участвующему в соединении JOIN двух таблиц, ускоряет выборку записей из таблиц
• Выборка записей в отсортированном порядке ORDER BY по полях, по которым создан индекс ускоряется
• CREATE INDEX [<нидекс>] ON <таблица> (<поля> | <выражения>) [WHERE <условие> (частичный индекс)]
• Индексы боллее еффективны, когда из таблицы выбирается лишь небольшая доля строк (высокая селективность выборки). Если же доля выбираемых строк высока, то большого положительного эффекта он налиичия индекса не будет
• По умолчанию, при создании индекса, для каждого стобца используется порядок по возрастанию ASC/DESC и NULL значения располагаються в конце NULL LAST/NULL FIRST
• PostgreSQL умеет совершать обход индекса как по возрастанию так и по убыванию, если нидекс создан на основе одного столбца
• Индексы на основе выражений ускоряют выборки на основе выражений

Транзакции

• Транзакция - это совокупность операций (BEGIN) на базой данных, которие било выполняются все вместе (COMMIT), либо не выполняются совсем (ROLLBACK отменяются все изменения выполненные в рамках транзакции)
• Наряду с ограничениями целостности (CONSTRAINT), транзакции являються одним из средств обеспечения согласованности базы данных: транзакция переводит базу данных из одного согласованного состояния в другое
• Реализация транзакций в PostgreSQL основана на Multiversion Concurrency Control (MVCC): каждая операция видит отдельный согласованний снимок бази данных на момент начала выполнения операции
• Когда параллелные танзакции изменяют одни и те же строки таблиц, тогда создаються отдельние версии этоих строк доступные только соответствующим транзакциям
• При MVCC операции чтения никогда не блокируются операциями записи, а операции записи никогда не блокируются операциями чтения
• Свойства транзакций
  • Атомарность (atomicity) либо транзакция будет зафиксирована в базе данных полностью, либо не будет зафиксирована ни одна операция транзакции
  • Согласованность (consistency) в результате успешного выполнения транзакции база данных будет переведена из одного согласованного состояния в другое
  • Изолированность (isolation) во время выполнения транзакции другие транзакции должны оказывать по возможности минимальное влияние на нее
  • Долговечность (durability) после успешной фиксации транзакции, данные надежно сохранены в базе данных независимо от последующих возмозных сбоев в системе
• При параллельном выполнении транзакций возможны следующие феномены
  • Потерянное обновление (lost update) когда разные транзакции одновременно изменяют одни и те же данные, после фикскации изменений может оказатся, что одна транзакция перезаписала данные, обновленные и зафиксированные другой транзакцией
  • Грязное чтение (dirty read) транзакция читает данные, измененные параллелной транзакцией которая еще не завершилась. Если эта параллельная транзакция в итоге будет отменена, тогда окажеться, что первая тарнзакция прочитала данные, которых нет в системе
  • Неповторяющееся чтение (non-repeatable read) при повторном чтении тех же самых данных в рамказ одной транзакции оказывается, что другая транзакция успела изменить и зафиксировать это данные. В результате тот же самый запрос выдает другой результат
  • Фантомное чтение (phantom read) транзакция повторно выбирает множество строк в соответствии с одним и тем же критерием. В интервале времени между выполнением этих выборок другая транзакция добавляет новые строки и успешно фиксирует изменения. В результате при выполнении повторной выборки в первой транзакции может быть получено другое множество строк
  • Аномалия сериализации (serialization anomaly) результат успешной фиксации группы транзакций, выполняющихся параллельно, не совпадает с результатом ни одного из возможных вариантов упорядочения этих транзакций, если бы они выполнялись последовательно
• Сериализация транзакицй - для двух транзакций А и Б, возможны только два варианта упорядочения при их последовательном выполнении: сначала А, затем Б или сначала Б, затем А. Рузультаты реализации двух вариантов можут в общем случае не совпадать
• Для конкретизации степени независимости параллелных тарнзакций вводиться понятие уровня изоляции транзакций. Каждый уровень характеризуется перечнем тех феноменов, которые на данном уровне не допускаются
  • Read uncommitted допускается чтение грязных, незафиксированных данних (SQL standartd). PostgreSQL не допускает чтения грязных данных и потерянные обновления даже на этом уровне
  • Read committed (default) не допускается чтение грязных, незафиксированных данних и отсутствие потерянных обновлений (SQL standartd). PostgreSQL уровни read committed и read uncommitted совпадают. Транзакция может видеть только те незафиксированные изменения данных, которые произведены в ходе выполнения ее самой. Транзакции, работающие на уровне изоляции Read Committed, видят только свои собственные обновления и обновления, зафиксированные параллельными транзакциями
  • Repeatable read не допускается чтение грязных данных и неповторяющееся чтение (SQL standard). PostgreSQL не допускает также фантомное чтение. Транзакция создает снимок данных не перед виполнением каждого запроса, а только однократно, перед выполнением первого запроса транзакции. В следствие этого PostgreSQL не позволит зафиксировать транзакцию, которая попитается изменить уже измененную строку. По этому придется выполнить транзакцию повторно
  • Serializable не допускается ни одни из феноменов перечисленных выше, в том числе и аномалии сереализации. Транзакции могут работать параллельно точно так же, как если бы они выполнялись последовательно одна за другой, однако придется перезапустить транзакцию, которая была прервана системой из-за обнаружения зависимостей чтения/записи между транзакциями. Группа транзакций может быть параллельно выполнена и успешно зафиксирована в том случае, когда результат их параллельного выполнения был бы эквивалентен результату выполнения этих транзакций при выборе одного из возможных вариантов их упорядочения, если бы они выполнялись последовательно, одна за другой. Транзакция работает с тем снимком базы данных, который был сделан непосредственно перед выполнением ее первого оператора
• BEGIN/SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
• По умолчанию каждая SQL-команда, образует отдельную транзакцию с уровнем изоляции Read Committed
• Блокировки (для read committed)
  • Неявные блокировки создаються транзакциями
  • PostgreSQL позволяет также создавать явные блокировки данных как на уровне отдельных строк SELECT FOR UPDATE, так и на уровне целых таблиц LOCK TABLE
  • Блокировки могут быть востребованы при проектировании транзакций с уровнем изоляции, как правило, Read Committed, когда требуется более детальное управление параллельным выполнением транзакций
  • Команда SELECT имеет предложение FOR UPDATE, которое позволяет заблокировать отдельные строки таблицы с целью их последующего обновления. Если одна транзакция заблокировала строки с помощью этой команды, тогда параллельные транзакции не смогут заблокировать эти же строки до тех пор, пока первая транзакция не завершится, и тем самым блокировка не будет снята, после чего параллельные транзакции буду работать уже с обновленными данными

Повышениеп роизводительности

• Методы доступа используются для просмотра таблиц и извлечения только тех строк, которые соответствуют критериям выбора
  • Последовательный просмотр (sequential scan) индекс не используется. Метод применятся когда надо выбрать все строки из таблицы или значительную их часть (селективность выборки низка). Filter
  • Просмотр по индексу (index scan) после отыскания в индексе нужного ключа, произвониться обращение к соответствующей строке таблицы по ее ндентификатору. Записи в индексе упорядочены, строки в таблице не упорядочены. Index Cond
  • Просмотр исключительно на основе индекса (index only scan) исползует карту видимости (visibility map) при транзакциях. Метод особенно эффективен, когда выбираемые данные изменяються редко. Метод может применятся, когда в предложении SELECT указаны только имена столбцов, по которым создан индекс
  • Просмотр на основе битовой карти (bitmap scan) оптимизирует поиск по индексу за счет создания битовой карты для всех искомых строк и обращения к каждой странице только один раз
• Методы соединения наборов строк (JOIN) за одни раз соединяться только два набора строк
  • Вложенный цыкл (nested loop) перебор строк. Метод поддержывает любые условия соединения строк. Метод эффективен для небольшых выборок
  • Хеширование (hash join) создание хеш таблицы. Метод поддержывает только проверку на равентсво как условие соединения строк. Метод эффективен для больших выборок
  • Слияние (merge join) производиться сортировка строк. Метод поддержывает только проверку на навентсво как условие соединения строк. Метод эффективен для больших выборок
• План выполнения запроса (EXPLAIN)
  • План выполенния запроса предствляет собой дерево, состоящее из узлов плана (plan nodes)
  • Нижние узлы - это узлы выборки строк: просмотр и выдача строк таблицы, которые осуществляются с помощью методов доступа
  • Верхние узлы - это узлы соединения строк: соединение, агрегировение и сортировка строк, которые осуществляются с помощью методов соединения наборов строк
• Методы повышения производительности запросов
  • Изменения схемы базы данных (database schema), денормализация (denormalization), метериализованные представления (materialized view + refresh materialized view), создание временных таблиц (create temporary table)
  • Изменения запроса, змена вложенных запросов (nested query) соединениями (join), исползование общих табличних выражений (CTE - with)
  • Создание индексов (create index)
  • Общие табличные выражения (CTE - WITH) всегда материализуются, т. е. вычисляются однократно и результат их вычисления сохраняется в памяти, а затем все последующие обращения в рамках запроса направляются уже к этому материализованному результату

Idris basics

Gnuplot basics

Scheme basics