Как удалить процедуру или функцию из базы данных

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

Читать далее Как удалить процедуру или функцию из базы данных

Перенос базы данных PostgreSQL с OpenShift v2 на Heroku

С Heroku будем работать с помощью утилиты Heroku CLI, а с OpenShift — с  помощью rhc.

Подключаемся к серверу OpenShift по SSH.

rhc ssh ИМЯ_ПРИЛОЖЕНИЯ

На сервере делаем дамп базы данных.

pg_dump --format=c -f app-root/data/database.dump

Опция --format задает формат нужный для Heroku.

Уходим с сервера.

exit

Загружаем дамп с сервера OpenShift на локальный компьютер.

rhc scp ИМЯ_ПРИЛОЖЕНИЯ download ./ app-root/data/database.dupm

Файл дампа нужно загрузить в интернет, так, чтобы была прямая ссылка на файл (https://АДРЕС/database.dupm).

Восстанавливаем базу данных на Heroku из дампа.

heroku pg:backups:restore -a ИМЯ_ПРИЛОЖЕНИЯ 'https://АДРЕС/database.dupm'

Извлечение древовидной структуры из базы данных

Для всех запросов в статье используется синтаксис СУБД SQLite. Для других СУБД синтаксис может незначительно отличаться.

Чтобы представить древовидную структуру в реляционной базе данных, можно создать следующую таблицу:

create table tree (
  id     integer,
  parent integer,
  name   text,

  primary key (id),
  foreign key (parent) references tree(id)
    on update cascade on delete cascade
);

В поле parent задается родитель для элемента древовидной структуры, причем в качестве значения этого поля используется значение поля id другого элемента. В поле name задается имя элемента.

Для наглядности зададимся древовидной структурой:

  • Овощи
    • Картофель
      • Аспиа
      • Виталот
      • Диво
    • Томаты
      • Томат обыкновенный
      • Томат Перуанский
  • Фрукты
    • Груши
      • Груша обыкновенная
    • Яблоки

Такую структуру можно создать следующими запросами:

insert into tree values ( 1, null, 'Овощи');
insert into tree values ( 2,    1, 'Картофель');
insert into tree values ( 3,    2, 'Аспиа');
insert into tree values ( 4,    2, 'Виталот');
insert into tree values ( 5,    2, 'Диво');
insert into tree values ( 6,    1, 'Томаты');
insert into tree values ( 7,    6, 'Томат обыкновенный');
insert into tree values ( 8,    6, 'Томат перуанский');
insert into tree values ( 9, null, 'Фрукты');
insert into tree values (10,    9, 'Груши');
insert into tree values (11,   10, 'Груша обыкновенная');
insert into tree values (12,    9, 'Яблоко');

Здесь null означает корневую группу.

Предположим, что требуется извлечь данные из таблицы tree в следующем виде:

/Овощи
/Фрукты
/Овощи/Картофель
/Овощи/Томаты
/Фрукты/Груши
/Фрукты/Яблоко
/Овощи/Картофель/Аспиа
/Овощи/Картофель/Виталот
/Овощи/Картофель/Диво
/Овощи/Томаты/Томат обыкновенный
/Овощи/Томаты/Томат перуанский
/Фрукты/Груши/Груша обыкновенная

Для этого понадобится рекурсивный запрос:

with cte as (
  select
    id,
    parent,
    '/' || name as name
  from tree where parent is null
  
  union all
  
  select
    tree.id,
    tree.parent,
    cte.name || '/' || tree.name
  from cte, tree on tree.parent = cte.id
)
select name from cte;

Рекурсивные запросы поддерживаются в SQLite начиная с версии 3.8.3 от 3 февраля 2014 года.

Функции для работы с датой и временем в SQLite

SQLite поддерживает пять функций для работы с датой и временем:

  1. date(timestring, modifier, modifier, ...)
  2. time(timestring, modifier, modifier, ...)
  3. datetime(timestring, modifier, modifier, ...)
  4. julianday(timestring, modifier, modifier, ...)
  5. strftime(format, timestring, modifier, modifier, ...)

Все функции принимают в качестве аргумента строку времени (timestring). За строкой времени следуют необязательные модификаторы (modifier). Функция strftime(), кроме этих аргументов, принимает строку формата  (format) в качестве первого аргумента.

Для представления даты и времени функции используют подмножество форматов из стандарта ISO 8601.  Функция date() возвращает дату в формате «YYYY-MM-DD». Функция time() возвращает время в формате «HH:MM:SS». Функция datetime() возвращает «YYYY-MM-DD HH:MM:SS». Функция julianday() возвращает Юлианскую дату — количество дней с полудня 24 ноября 4714 года до н. э. григорианского календаря по Гринвичу.

Функция strftime() возвращает строку в соответствии со строкой формата, которая указывается в качестве первого аргумента этой функции. Строка формата поддерживает наиболее распространенные шаблоны из функции strftime() стандартной библиотеки Си и два новых шаблона: %f и %j. Полный список поддерживаемых шаблонов:

Шаблон Значение
%d день месяца: 01…31
%f доли секунд: SS.SSS
%H час: 00…23
%j день года: 001…366
%J Юлианская дата
%m месяц: 01…12
%M минута: 00…59
%s количество секунд с 1 января 1970 года
%S секунда: 00…59
%w день недели: 0…6 (0 — воскресенье)
%W неделя года: 00…53
%Y год: 0000…9999
%% %

Любую функцию можно заменить функцией strftime():

Функция Эквивалент
date(…) strftime(‘%Y-%m-%d’, …)
time(…) strftime(‘%H:%M:%S’, …)
datetime(…) strftime(‘%Y-%m-%d %H:%M:%S’, …)
julianday(…) strftime(‘%J’, …)

Другие функции, кроме strftime(), предоставляются для обеспечения удобства и эффективности.

Строка времени

Строка времени (timestring) может иметь один из следующих форматов:

  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

Форматы с 5 по 7 используют символ «T» для разделения даты и времени, как требует стандарт ISO 8601. Форматы с 8 по 10, которые определяют только время, предполагают дату 2000-01-01. Формат 11, строка ‘now’, преобразуется в текущие дату и время, получаемые методом xCurrentTime объекта sqlite3_vfs. При этом используется всемирное координированное время (UTC). Формат 12 — Юлианской дата, представленная числом с плавающей точкой. В форматах 4, 7 и 10 дробные секунды SS.SSS могут иметь одну или более цифр после десятичной точки. В примере указаны три цифры, потому что только три цифры влияют на результат, но входная строка может содержать меньше или больше трех цифр. Кроме того, в формате 12 показано 10 цифр, однако функции могут принимать столько цифр, сколько необходимо для преставления Юлианской даты.

Модификаторы

За строкой времени может следовать несколько модификаторов или ни одного. Модификатор указывает на то, какое преобразование надо применить ко времени. Модификаторы применяются слева на право; порядок важен. Доступны следующие модификаторы:

  1. NNN days
  2. NNN hours
  3. NNN minutes
  4. NNN.NNNN seconds
  5. NNN months
  6. NNN years
  7. start of month
  8. start of year
  9. start of day
  10. weekday N
  11. unixepoch
  12. localtime
  13. utc

Первые шесть модификаторов (с 1 по 6) прибавляют указанное количество часов, минут, секунд, месяцев или лет к дате и времени. Если первоначальная дата в формате YYYY-MM-DD, то модификатор ‘±NNN month’ добавляет ±NNN месяцев к значению MM, а затем нормализует результат. Например, для даты 2001-03-31 указан модификатор ‘+1 month’, это дает 2001-04-31, но в апреле только 30 дней, поэтому дата нормализуется в 2001-05-01. Аналогичный эффект возникает, когда в первоначальной дате указано 29 февраля високосного года и применяется модификатор ‘±N years’, где N не кратно четырем.

Модификаторы ‘start of …’ (с 7 по 9) сдвигают дату назад к началу текущего месяца, года или дня.

Модификатор ‘weekday N’ продвигает дату вперед, где номер дня недели — N. 0 — это воскресенье, 1 — понедельник и так далее.

Модификатор ‘unixepoch’ (11) работает только если строка времени в формате DDDDDDDDDD. Модификатор заставляет функции интерпретировать DDDDDDDDDD не как Юлианскую дату, как это должно быть в обычной ситуации, а как UNIX-время — количество секунд с 1970 года. Если модификатор ‘unixepoch’ не следует после строки времени в формате DDDDDDDDDD, выражающей количество секунд с 1970 года, или если другой модификатор стоит между модификатором ‘unixepoch’ и строкой времени, то результат будет неопределенным. Из-за ограничений, налагаемых использованием 64-разрядных целых чисел, модификатор ‘unixepoch’ работает только для дат между 0000-01-01 00:00:00 и 5352-11-01 10:52:47 (UNIX-время от −62167219200 до 10675199167 секунд включительно).

Модификатор ‘localtime’ предполагает, что строка времени соответствует всемирному координированному времени (UTC), и приводит время к локальному. Модификатор ‘utc’ противоположен ‘localtime’, предполагает, что время соответствует локальному, и приводит время к UTC.

Примеры

Вычисляет текущую дату.

SELECT date('now');

Вычисляет последний день текущего месяца.

SELECT date('now','start of month','+1 month','-1 day');

Вычисляет дату и время для 1092941466 секунд с 1970 года.

SELECT datetime(1092941466, 'unixepoch');

Вычисляет дату и время для 1092941466 секунд с 1970 года и приводит к локальному времени.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Вычисляет текущее UNIX-время.

SELECT strftime('%s','now');

Вычисляет количество дней прошедших с подписания Декларации независимости США.

SELECT julianday('now') - julianday('1776-07-04');

Вычисляет количество секунд с некоторого момент в 2004 году.

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Вычисляет дату первого вторника октября текущего года.

SELECT date('now','start of year','+9 months','weekday 2');

Вычисляет время в секундах с начала эпохи UNIX:

SELECT (julianday('now') - 2440587.5)*86400.0;

Предостережения и ошибки

Расчет локального времени в большей степени зависит от прихоти политиков, поэтому тяжело получить корректный результат для всех локалей. SQLite использует функцию localtime_r() из стандартной библиотеки Си. Эта функция работает правильно только для дат между 1970 и 2037 годами. Для дат за пределами этого диапазона, SQLite пытается отобразить год в год внутри этого диапазона, производит расчет и отображает год обратно.

Функции работают только с датами между 0000-01-01 00:00:00 и 9999-12-31 23:59:59 (Юлианские даты с 1721059.5 по 5373484.5). Для дат за пределами этого диапазона, результат функций неопределен.

Операционные системы семейства Windows (кроме Vista) поддерживают только один набор правил перехода на летнее время. Vista поддерживает два. Поэтому, на этих платформах, исторический расчет летнего времени будет некорректным. Например, в США в 2007 году изменились правила перехода на летнее время. Windows (кроме Windows Vista) применяет новые правила для всех предыдущих лет. Vista дает немного лучший результат, корректный до 1986 года, в котором правила были также изменены.

Все внутренние вычисления происходят в соответствии с Григорианским календарем. Кроме того, предполагается, что каждый день продолжительностью ровно 86 400 секунд.

Ссылки

Функции даты и времени в официальной документации SQLite (на английском).