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

Сервер бази даних 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

Advertisements

Залишити відповідь

Заповніть поля нижче або авторизуйтесь клікнувши по іконці

Лого WordPress.com

Ви коментуєте, використовуючи свій обліковий запис WordPress.com. Log Out / Змінити )

Twitter picture

Ви коментуєте, використовуючи свій обліковий запис Twitter. Log Out / Змінити )

Facebook photo

Ви коментуєте, використовуючи свій обліковий запис Facebook. Log Out / Змінити )

Google+ photo

Ви коментуєте, використовуючи свій обліковий запис Google+. Log Out / Змінити )

З’єднання з %s

%d блогерам подобається це: