这个国产数据库的进化，实测结果让我感到惊讶

CREATETABLE docs (
    id BIGINT PRIMARY KEY,
    title     VARCHAR(200),
    content CLOB,
    embedding VECTOR(3)
);

-- 方式一: 通过TO_VECTOR字符串转换
INSERT INTO docs VALUES
(1, '多模数据库入门', '本文介绍原生多模数据库的技术路线与产品演进', TO_VECTOR('[0.12,0.88,0.34]')),
(2, 'AI时代的数据库', '探讨大模型时代数据库需要具备的新能力',       TO_VECTOR('[0.21,0.65,0.77]')),
(3, '传统SQL优化技巧', '索引、代价模型与执行计划调优实战',            TO_VECTOR('[0.55,0.10,0.44]')),
(4, '向量检索原理',    '从 KNN 到 HNSW，深入理解 ANN 算法演进',    TO_VECTOR('[0.78,0.32,0.65]')),
(5, '图数据库选型指南','Neo4j / JanusGraph / 原生图能力对比',     TO_VECTOR('[0.43,0.71,0.18]'));

-- 方式二: 注册 Embedding 模型后通过 AI_EMBED 函数生成
-- EXEC DBMS_LLM.CREATE_MODEL('embed_model', 'http://host:port', 'model-id', 'embedding', 'openai', 'sk-xxx');
-- INSERT INTO docs VALUES (6, '标题', '内容', AI_EMBED('embed_model', '文本内容', 3));

SELECT id, title FROM docs
ORDER BY embedding <-> TO_VECTOR('[0.78,0.32,0.65]', 3)
FETCH FIRST 3 ROWS ONLY;

-- 预期结果: top1 为 id=4（与查询向量完全相同）
-- id=4, title=向量检索原理
-- id=3, title=传统SQL优化技巧
-- id=2, title=AI时代的数据库

CREATE VECTOR INDEX idx_docs_emb ON docs(embedding)
ORGANIZATION NEIGHBOR GRAPH WITH DISTANCE COSINE
PARAMETERS (TYPE HNSW, M 16, efConstruction 64);

EXPLAIN SELECT id, title FROM docs
ORDER BY embedding <-> TO_VECTOR('[0.78,0.32,0.65]', 3)
FETCH APPROX FIRST 3 ROWS ONLY;

SELECT id, embedding <-> TO_VECTOR('[0.78,0.32,0.65]', 3) AS dist
FROM docs ORDER BY id;

-- id=4 与自身距离 = 0.0（验证正确）

SELECT id, cosine_distance(embedding, TO_VECTOR('[0.78,0.32,0.65]', 3)) AS dist
FROM docs ORDER BY dist FETCH APPROX FIRST 3 ROWS ONLY;

-- top1: id=4, cosine距离最小

SELECT id, title, embedding <-> TO_VECTOR('[0.21,0.65,0.77]', 3) AS dist
FROM docs WHERE id <= 3
ORDER BY dist FETCH FIRST 2 ROWS ONLY;

-- top1: id=2（与查询向量相同，且在 id<=3 过滤条件下）

CREATE TABLE person (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age  INT
);

CREATE TABLE friendship (
    src_id  INT,
    dst_id  INT,
    since   DATE,
    PRIMARY KEY (src_id, dst_id)
);

INSERT INTO person VALUES (1,'Alice',30),(2,'Bob',28),(3,'Carol',35),(4,'Dave',40);
INSERT INTO friendship VALUES (1,2,DATE'2020-01-01'),(2,3,DATE'2021-05-12'),(3,4,DATE'2022-09-09');

CREATE PROPERTY GRAPH social
VERTEX TABLES ( person KEY (id) LABEL person PROPERTIES (name, age, id) )
EDGE TABLES (
    friendship KEY (src_id, dst_id)
    SOURCEKEY (src_id) REFERENCES person(id)
    DESTINATION KEY (dst_id) REFERENCES person(id)
    LABEL knows PROPERTIES (since)
);

SELECT * FROM GRAPH_TABLE(social
MATCH (a is person)-[r is knows]->(b is person)
WHERE a.name = 'Alice'
COLUMNS (b.name AS friend, r.since AS since)
);
-- 预期: friend=Bob, since=2020-01-01

SELECT * FROM GRAPH_TABLE(social
MATCH (a is person)-[r is knows]->{1,2}(b is person)
WHERE a.name = 'Alice'
COLUMNS (b.name AS friend, count(r.since) AS hops)
);

-- 预期: Bob(1跳) + Carol(2跳)

SELECT * FROM GRAPH_TABLE(social
MATCH (a is person)-[r is knows]->(b is person)
WHERE b.name = 'Bob'
COLUMNS (a.name AS knows_bob)
);

-- 预期: Alice

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    details JSON
) ORGANIZATION HEAP;

INSERT INTO products VALUES
(1, 'YashanDB',
JSON('{"version":"23.5.4","tags":["多模","AI"],"specs":{"cpu":16,"mem_gb":64}}')),
(2, 'Neo4j',
JSON('{"version":"5.0","tags":["graph"],"specs":{"cpu":8,"mem_gb":32}}'));

SELECT id, name, JSON_VALUE(details, '$.version') ASversion
FROM products ORDER BY id;

-- 预期:
-- id=1, name=YashanDB, version=23.5.4
-- id=2, name=Neo4j,    version=5.0

SELECT id, name FROM products
WHERE JSON_EXISTS(details, '$.specs.cpu')
ORDER BY id;

-- 预期: 返回两条（都有 specs.cpu）

SELECT id, name, JSON_QUERY(details, '$.specs') AS specs
FROM products ORDER BY id;

-- 预期:
-- id=1, specs={"cpu":16,"mem_gb":64}
-- id=2, specs={"cpu":8,"mem_gb":32}

YAS-04401 data type JSON expected, but VARCHAR got

-- [不推荐] 直接传JSON_QUERY结果 → 失败
SELECT JSON_ARRAY_GET(JSON_QUERY(details, '$.tags'), 0) FROM products;
-- 报错: YAS-04401 data type JSON expected, but VARCHAR got

-- [可行] CAST为JSON后再传
SELECT JSON_SERIALIZE(JSON_ARRAY_GET(JSON(JSON_QUERY(details, '$.tags')), 0))
FROM products WHERE id = 1;
-- 结果: "多模"

-- [最佳实践] 直接用JSON_VALUE+路径下标
SELECT
    JSON_VALUE(details, '$.tags[0]')  AS tag0,
    JSON_VALUE(details, '$.tags[1]')  AS tag1,
    JSON_VALUE(details, '$.specs.cpu') AS cpu
FROM products WHERE id = 1;
-- 结果: tag0=多模, tag1=AI, cpu=16

-- 紧凑格式
SELECT JSON_SERIALIZE(details) AS compact FROM products WHERE id = 1;

-- 美化格式
SELECT JSON_SERIALIZE(details PRETTY) AS pretty FROM products WHERE id = 1;

CREATE TABLESPACE doc_tbs
DATABUCKET 'doc_bucket'
MAXSIZE 10G;

BEGIN
    DBMS_FS.CREATE_VOLUME('doc_vol', 'doc_tbs', 'doc_bucket', 1073741824);
END;
/

BEGIN
    DBMS_FS.SET_VOLUME('doc_vol');
    DBMS_FS.MKDIR('/data');
END;
/

BEGIN
    DBMS_FS.SET_VOLUME('doc_vol');
    DBMS_FS.MKDIR('/data/docs');
    DBMS_FS.CREATE('/data/docs/readme.txt');
    DBMS_FS.WRITE('/data/docs/readme.txt', 'Hello YashanDB File System!');
COMMIT;
END;
/

DECLARE
    v_content VARCHAR2(4000);
    v_len     BINARY_INTEGER;
BEGIN
    DBMS_FS.SET_VOLUME('doc_vol');
    DBMS_FS.READ('/data/docs/readme.txt', 0, 4000, v_content, v_len);
    DBMS_OUTPUT.PUT_LINE('Content: ' || v_content);
END;
/

DECLARE
    v_entries DBMS_FS.DIRENTRY_TABLE;
    v_count   INTEGER;
BEGIN
    DBMS_FS.SET_VOLUME('doc_vol');
    DBMS_FS.LISTDIR('/data', v_entries, v_count);
    DBMS_OUTPUT.PUT_LINE('Items: ' || v_count);
END;
/

DECLARE
    v_exists BOOLEAN;
BEGIN
    DBMS_FS.SET_VOLUME('doc_vol');
    DBMS_FS.EXIST('/data/docs/readme.txt', v_exists);
    IF v_exists THEN
        DBMS_OUTPUT.PUT_LINE('File exists');
END IF;
END;
/

CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    title VARCHAR(200),
    body CLOB
);

INSERT INTO articles VALUES
(1, '多模数据库白皮书', '本文讨论原生多模数据库的技术路线与产品演进'),
(2, 'AI 数据库评测',    'AI 时代数据库需要支持向量、图、文档等多种模型'),
(3, '传统 SQL 优化',    '索引设计是关系型数据库性能的核心');

CREATE SEARCH INDEX articles_idx ON articles(body);

SELECT id, title FROM articles WHERE CONTAINS(body, '数据库') > 0;

-- 预期: 返回3行（所有文章都包含"数据库"）

SELECT id, title FROM articles
WHERE CONTAINS(body, '数据库') > 0 AND CONTAINS(body, '多模') > 0;

-- 预期: id=1（同时包含"数据库"和"多模"）

SELECTid FROM articles
WHERE CONTAINS(body, '数据库') > 0 AND CONTAINS(body, '模型') > 0;

-- 预期: id=2（包含"数据库"和"模型"）

CREATE TABLE tb_company (
    id INT,
    name VARCHAR(200),
    embedding VECTOR(3)
);

INSERT INTO tb_company VALUES
(1, 'oceanbase',       TO_VECTOR('[1,0,0]')),
(2, 'oracle database', TO_VECTOR('[1,0,3]')),
(3, 'yashan database', TO_VECTOR('[1,0,2]')),
(4, 'tidb',            TO_VECTOR('[1,0,1]'));

-- 创建向量索引
CREATE VECTOR INDEX vidx1 ON tb_company (embedding)
ORGANIZATION NEIGHBOR GRAPH DISTANCE COSINE;

-- 在同一表上创建全文索引
CREATE SEARCH INDEX sidx2 ON tb_company(name);

-- 全文检索
SELECT id, nameFROM tb_company WHERE CONTAINS(name, 'database') > 0;
-- 预期: id=2 (oracle database), id=3 (yashan database)

CREATE TABLE city_buildings (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(point, 4326)    -- WGS 84 坐标系
);

INSERT INTO city_buildings VALUES (1, 'A 楼', ST_GeomFromText('POINT(116.397 39.908)'));
INSERT INTO city_buildings VALUES (2, 'B 楼', ST_GeomFromText('POINT(121.473 31.230)'));
INSERT INTO city_buildings VALUES (3, 'C 楼', ST_GeomFromText('POINT(113.264 23.129)'));

CREATE RTREE INDEX idx_geom ON city_buildings(geom);

-- 查询落入指定矩形范围的建筑
SELECT id, name FROM city_buildings
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((110 30, 120 30, 120 40, 110 40, 110 30))', 4326),
    geom
);

-- 预期: id=1 (A 楼，天安门在 (110-120, 30-40) 范围内)

-- WGS 84 (4326) 转 中国 2000 (4490)
SELECT id, name, ST_AsText(ST_Transform(geom, 4490)) AS geom_cgcs2000
FROM city_buildings WHERE id = 1;

-- 预期: POINT (116.397... 39.908...)

SELECT a.name AS loc_a, b.name AS loc_b,
       ST_Distance(a.geom, b.geom) AS dist
FROM city_buildings a, city_buildings b
WHERE a.id = 1 AND b.id = 2;

-- 预期: A 楼 -> B 楼 (北京到上海) ≈ 1066 km

SELECT id, name FROM tb_company WHERE CONTAINS(name, 'database') > 0
UNION
(SELECT id, nameFROM tb_company
ORDER BY cosine_distance(embedding, TO_VECTOR('[1, 1, 1]', 3))
FETCH APPROXIMATE FIRST 2 ROWS ONLY);

-- 预期: 返回 3 行（全文匹配2条 + 向量top2去重后共3条）

CREATE OR REPLACE VIEW entity AS
SELECT id, name FROM tb_company
WHERE CONTAINS(name, 'database') > 0 AND id > 1
UNION
(SELECT id, name FROM tb_company
ORDER BY cosine_distance(embedding, TO_VECTOR('[1, 1, 1]', 3))
FETCH APPROXIMATE FIRST 2 ROWS ONLY);

CREATE TABLE relation (
    r_id        INT,
    s_entity_id INT,
    d_entity_id INT,
    label       VARCHAR(20)
);

INSERT INTO relation VALUES (1, 1, 2, 'label1');
INSERT INTO relation VALUES (2, 1, 3, 'label2');
INSERT INTO relation VALUES (3, 1, 4, 'label3');
INSERT INTO relation VALUES (4, 3, 4, 'label4');
INSERT INTO relation VALUES (5, 2, 3, 'label5');
INSERT INTO relation VALUES (6, 3, 2, 'label6');

CREATE PROPERTY GRAPH entity_graph
VERTEX TABLES (
    entity KEY (id)
    LABEL entity_prop
    PROPERTIES (name)
)
EDGE TABLES (
    relation KEY (r_id)
SOURCE KEY (s_entity_id) REFERENCES entity(id)
    DESTINATION KEY (d_entity_id) REFERENCES entity(id)
    LABEL relation_prop
    PROPERTIES (label)
);

SELECT aname, label, bname
FROM GRAPH_TABLE(entity_graph
MATCH (a is entity_prop)-[r is relation_prop]->(b is entity_prop)
COLUMNS (a.name AS aname, r.label AS label, b.name AS bname)
);

-- 预期: 返回视图 entity 内节点之间的所有边关系
-- 如: yashan database -> oracle database, oracle database -> yashan database 等