Аналог таблиці dual в oracle для mysql, postgresql, hsqldb

таблиця аналогічна таблиці dual в oracle.

Oracle:

select systimestamp from dual;

MySQL:

select current_timestamp;

PostgreSQL:

select current_timestamp;

HSQLDB:

select current_timestamp from unnest(sequence_array(1, 1, 1));

або

select * from (values('X'));

=-=-=-=-=
Powered by Blogilo

Advertisements

Обгортка для рекурсивних запитів

Сервер бази даних H2database має експериментальну підтримку рекурсивних запитів із використанням так званих "загальних табличних виразів" ("common table expressions" CTE).

Приклад:

WITH RECURSIVE T(N) AS (
    SELECT 1
    UNION ALL
    SELECT N+1 FROM T WHERE N<10
)
SELECT * FROM T;

— повертає значення 1 .. 10

WITH RECURSIVE T(N) AS (
    SELECT 1
    UNION ALL
    SELECT N*2 FROM T WHERE N<10
)
SELECT * FROM T;

— повертає значення 1, 2, 4, 8, 16

CREATE TABLE FOLDER(ID INT PRIMARY KEY, NAME VARCHAR(255), PARENT INT);

INSERT INTO FOLDER VALUES(1, null, null), (2, 'src', 1),
(3, 'main', 2), (4, 'org', 3), (5, 'test', 2);

WITH LINK(ID, NAME, LEVEL) AS (
    SELECT ID, NAME, 0 FROM FOLDER WHERE PARENT IS NULL
    UNION ALL
    SELECT FOLDER.ID, IFNULL(LINK.NAME || '/', '') || FOLDER.NAME, LEVEL + 1
    FROM LINK INNER JOIN FOLDER ON LINK.ID = FOLDER.PARENT
)
SELECT NAME FROM LINK WHERE NAME IS NOT NULL ORDER BY ID;

— повертає значення

 src
 src/main
 src/main/org
 src/test

Дуже корисні запити, але існує суттевий недолік, рекурсивні запити неможна застосовувати для створення видів (документація пише: "Views with recursive queries are not supported.").

Розробники рекомендують використовувати UDF (User-Defined Function).

Отже. Створюємо клас, який виконує рекурсивний запит і повертає ResultSet :

package udf;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.h2.tools.SimpleResultSet;

/**
 *
 * @author vw
 */
public class Bind {
  
  public static SimpleResultSet query(java.sql.Connection conn, String sqltext) {
    //
    SimpleResultSet rs = new SimpleResultSet();
    //
    try {
      PreparedStatement ps = conn.prepareCall(sqltext);
      ResultSet tab = ps.executeQuery();
      ResultSetMetaData md = tab.getMetaData();
      int cc = md.getColumnCount();
      //System.out.println(cc);
      for (int i=1; i<=cc; i++) {
        //rs.addColumn("n", Types.INTEGER, 0, 0);
        String cname = md.getColumnName(i);
        int ct = md.getColumnType(i);
        int cp = md.getPrecision(i);
        int cs = md.getScale(i);
        rs.addColumn(cname, ct, cp, cs);
      }
      //
      while (tab.next()) {
        Object rec[] = new Object[cc];
        for (int i=0; i<cc; i++) {
          rec[i] = tab.getString(i+1);
        }
        rs.addRow(rec);
      }
    } catch (SQLException ex) {
      Logger.getLogger(Bind.class.getName()).log(Level.SEVERE, null, ex);
    }
    return rs;
  }
}

Компілюємо jar-файл та додаємо його до $classpath сервера.

Запускаємо сервер H2.

java -cp $classpath org.h2.tools.Server -tcp -tcpPort 9092 -baseDir "$database"

Створюємо псевдонім

create alias public.bindquery for "udf.Bind.query";

Тепер маємо можливість описати вид із застосуванням рекурсивного запиту:

create view v_recursive_test as
select N
  from public.bindquery(
     'WITH RECURSIVE T(N) AS ('
   || ' SELECT 1'
   || ' UNION ALL'
   || ' SELECT N+1 FROM T WHERE N<10)'
 || ' SELECT N FROM T');

Перевіряємо:

select * from v_recursive_test;

— отримуємо результат

 N
--
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

=-=-=-=-=
Powered by Blogilo

Номер рядка в pl/sql

— Номер рядка в pl/sql коді oracle —

Для обробки помилок в коді pl/sql процедури можна використовувати системну змінну:

...
  v_linenum := $$PLSQL_LINE;
...
  exception
    when no_data_found then
      rollback;
      l_except.message := sqlerrm;
      l_except.inf := dbms_utility.format_call_stack;
      except_p(v_linenum,l_except.message,l_except.inf);
      p_result := to_char(v_linenum) || v_msg;
    when others then
      rollback;
      l_except.message := sqlerrm;
      l_except.inf := dbms_utility.format_call_stack;
      except_p(v_linenum,l_except.message,l_except.inf);
      p_result := l_except.message;
  end;

=-=-=-=-=
Powered by Blogilo

sysContext для H2database

(або H2database для розробників розбещених можливостями серверу бази даних Oracle)

У сервері бази даних Oracle реалізовано цікаву функцію sys_context(”,”), яка дозволяє отримати багато цінної інформації про контекст сеансу. Кожен сеанс має ініціалізований контекст ‘USERENV’.

Ось приклад:

          select sys_context('USERENV', 'CURRENT_USER') sc from dual
union all select sys_context('USERENV', 'CURRENT_USERID') sc from dual
union all select sys_context('USERENV', 'IP_ADDRESS') sc from dual
union all select sys_context('USERENV', 'DB_NAME') sc from dual
union all select sys_context('USERENV', 'EXTERNAL_NAME') sc from dual
union all select sys_context('USERENV', 'HOST') sc from dual
union all select sys_context('USERENV', 'INSTANCE') sc from dual
union all select sys_context('USERENV', 'ISDBA') sc from dual
union all select sys_context('USERENV', 'OS_USER') sc from dual
union all select sys_context('USERENV', 'SESSION_USER') sc from dual
union all select sys_context('USERENV', 'SESSION_USERID') sc from dual
union all select sys_context('USERENV', 'SESSIONID') sc from dual
union all select sys_context('USERENV', 'TERMINAL') sc from dual
union all select sys_context('userenv', 'module') from dual;

Більше інформації про функцію можна знайти в документації.

Серед усіх змінних для реалізації розподілу доступу цікава змінна ‘OS_USER’. Ця змінна містить назву облікового запису з яким користувач увійшов у систему.

У сервері бази даних H2 подібної функції немає, але є можливість визначати свої змінні. Змінні зберігаються тільки в активній сесії і з інших сесій недоступні, що робить їх надзвичайно цінними з точки зору управління сесією. Крім того така змінна може бути визначена під час підключення клієнта до серверу бази даних.

На додаток до вже описаним настройки, інші настройки бази даних можуть бути передані в базу даних URL. Додавання, INIT=set в кінці URL-адресу бази даних так само, як виконання інструкції SET встановлене значення тільки після підключення. Список підтримуваних параметрах див SQL граматики.

Для цього необхідно в URL додати наступний текст:

;INIT=set @userenv='host_user=localhost,prog_user=test,os_user=user1'\;

URL може мати такий вигляд:

jdbc:h2:tcp://localhost:9092/testbase;INIT=set @userenv='host_user=localhost,prog_user=test,os_user=user'\;

=-=-=-=-=
Powered by Blogilo