基本
SDE利用自己的机制去管理表中每行的唯一标识。其通过i表管理行的唯一标识号，通过i_get_ids()函数和i_return_ids()函数对i表进行操作，并得到合适的行标识号。不同的表将有一个对应的编号并分别对应i表、i_get_ids()和i_return_ids()。如某表在sde_table_registry中的注册id为13，则创建对应的id表和函数为：i13，i13_get_ids()，i13_return_ids()
- i表创建：
-- Table: sde.i13
-- DROP TABLE IF EXISTS sde.i13;
CREATE TABLE IF NOT EXISTS sde.i13
(
id_type integer NOT NULL,
base_id bigint NOT NULL,
num_ids bigint NOT NULL,
last_id bigint,
CONSTRAINT i13_pk PRIMARY KEY (id_type, num_ids, base_id)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS sde.i13
OWNER to sde;
i表默认插入行：
insert into i13 (id_type, base_id, num_ids, last_id) values (2, 1, -1, 1)
- i_get_ids()函数：
-- FUNCTION: sde.i13_get_ids(integer, integer)
-- DROP FUNCTION IF EXISTS sde.i13_get_ids(integer, integer);
CREATE OR REPLACE FUNCTION sde.i13_get_ids(
i_id_type integer,
i_num_requested_ids integer,
OUT sql_code integer,
OUT o_base_id bigint,
OUT o_num_obtained_ids integer)
RETURNS record
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE
l_count INTEGER;
i_table_name VARCHAR(256);
l_sqlstmt VARCHAR(528);
l_sqlstmt_upd VARCHAR(528);
l_sqlstmt_del VARCHAR(528);
l_cursor REFCURSOR;
l_record_temp RECORD;
SE_SUCCESS CONSTANT INTEGER NOT NULL DEFAULT 0;
SE_SUCCESS_WITH_WARNINGS CONSTANT INTEGER NOT NULL DEFAULT - 1019;
INT32_MAX CONSTANT INTEGER NOT NULL DEFAULT 2147483647;
BEGIN
sql_code := -1;
o_base_id := -1;
o_num_obtained_ids := -1;
i_table_name := 'sde.i13';
IF i_num_requested_ids < 0 THEN
l_sqlstmt := 'LOCK TABLE ' || i_table_name || ' IN EXCLUSIVE MODE';
EXECUTE l_sqlstmt;
--Resetting the generator.
l_sqlstmt := 'UPDATE ' || i_table_name || ' SET base_id = ' ||
'base_id + ' || i_num_requested_ids || ' WHERE num_ids = -1 ' ||
'AND id_type = ' || i_id_type;
EXECUTE l_sqlstmt;
--Delete fragments and update the base value.
l_sqlstmt := 'DELETE FROM ' || i_table_name ||
' WHERE id_type = ' || i_id_type ||
' AND num_ids != -1';
EXECUTE l_sqlstmt;
ELSE
IF i_num_requested_ids > 0 THEN
-- Obtain a range of ids.
l_sqlstmt := 'SELECT base_id, num_ids FROM ' || i_table_name ||
' WHERE id_type = ' || i_id_type ||
' ORDER BY num_ids DESC FOR UPDATE ';
ELSE
-- only interested in base id
l_sqlstmt := 'SELECT base_id, num_ids FROM ' ||
i_table_name ||
' WHERE id_type = ' || i_id_type ||
' AND num_ids = -1 FOR UPDATE ';
END IF;
l_count := 0;
OPEN l_cursor FOR EXECUTE l_sqlstmt;
FETCH NEXT FROM l_cursor INTO l_record_temp;
WHILE FOUND
LOOP
l_count := l_count + 1;
o_base_id := l_record_temp.base_id;
o_num_obtained_ids := l_record_temp.num_ids;
IF i_num_requested_ids = 0 THEN
o_num_obtained_ids = 0;
ELSE
IF o_num_obtained_ids = -1 THEN
-- user got the amount we wanted.
o_num_obtained_ids = i_num_requested_ids;
--update the last id and base id.
l_sqlstmt_upd := 'UPDATE ' || i_table_name ||
' SET base_id = base_id + ' || o_num_obtained_ids || ', '
' last_id = ' || o_base_id ||
' WHERE CURRENT OF ' || quote_ident(l_cursor::TEXT);
EXECUTE l_sqlstmt_upd;
ELSE
-- user got a fragment, delete the fragment row
l_sqlstmt_del := 'DELETE FROM ' || i_table_name ||
' WHERE base_id = ' || l_record_temp.base_id ||
' AND num_ids = ' || l_record_temp.num_ids;
EXECUTE l_sqlstmt_del;
END IF;
END IF;
EXIT;
END LOOP;
CLOSE l_cursor;
IF l_count = 0 THEN RAISE EXCEPTION 'No Record Found'; END IF;
--Since we have gotten this far without an exception,
--it must be OK to return SE_SUCCESS or SE_SUCCESS_WITH_WARNINGS.
IF(o_base_id + o_num_obtained_ids) > INT32_MAX THEN
sql_code := SE_SUCCESS_WITH_WARNINGS;
ELSE
sql_code := SE_SUCCESS;
END IF;
END IF;
RETURN;
END;
$BODY$;
ALTER FUNCTION sde.i13_get_ids(integer, integer)
OWNER TO sde;
- i_return_ids()函数：
-- FUNCTION: sde.i13_return_ids(integer, bigint, integer)
-- DROP FUNCTION IF EXISTS sde.i13_return_ids(integer, bigint, integer);
CREATE OR REPLACE FUNCTION sde.i13_return_ids(
i_id_type integer,
i_base_id bigint,
i_num_ids integer)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE
sql_code INTEGER;
l_last_id BIGINT;
l_base_id BIGINT;
i_table_name VARCHAR(256);
l_sqlstmt VARCHAR(528);
l_sqlstmt_upd VARCHAR(528);
l_record_temp RECORD;
BEGIN
sql_code := -1;
i_table_name := 'sde.i13';
l_sqlstmt := 'LOCK TABLE ' || i_table_name || ' in exclusive mode';
EXECUTE l_sqlstmt;
l_sqlstmt := 'SELECT last_id, base_id FROM ' || i_table_name ||
' WHERE num_ids = -1 AND id_type = ' || i_id_type || ' FOR UPDATE';
FOR l_record_temp IN EXECUTE l_sqlstmt
LOOP
l_last_id := l_record_temp.last_id;
l_base_id := l_record_temp.base_id;
IF (l_last_id < i_base_id AND l_base_id = (i_base_id + i_num_ids)) THEN
-- only return ids if no one else has grabbed a block
-- and were returning the remainder of the block.
l_sqlstmt_upd := 'UPDATE ' || i_table_name ||
' SET base_id = ' || i_base_id ||
' WHERE num_ids = -1 AND id_type = ' || i_id_type;
ELSE
-- Insert a new fragment
l_sqlstmt_upd := 'INSERT INTO ' || i_table_name ||
' (base_id, num_ids, id_type) VALUES( '|| i_base_id ||
' , ' || i_num_ids || ', ' || i_id_type || ')';
END IF;
EXECUTE l_sqlstmt_upd;
END LOOP;
-- Since weve gotten this far without an exception,
-- it must be OK to return SE_SUCCESS.
sql_code := 0;
RETURN sql_code;
END;
$BODY$;
ALTER FUNCTION sde.i13_return_ids(integer, bigint, integer)
OWNER TO sde;
使用
i表和函数创建好后，插入默认行：
insert into i13 (id_type, base_id, num_ids, last_id) values (2, 1, -1, 1)
假设从13表中插入10行数据，首先使用i_get_ids()获取到新行的id和插入的数量
select i13_get_ids(2, 10);
参数2代表类型，10代表需要插入10行数据，执行sql后返回结果：
(0,1,10)
以上结果表示新行id从1开始，可插入10行数据，即10行数据的id为1-10。
此时i13表中数据为：
| id_type | base_id | num_ids | last_id |
|---|---|---|---|
| 2 | 11 | -1 | 1 |
假如因为某些原因，只插入了三行数据，使用i_return_ids将未使用的id返回给i表：
select i13_return_ids(2,4,7);
参数2代表类型，4代表未使用的id号，7代表剩余的id数量，此时i13表中数据为：
| id_type | base_id | num_ids | last_id |
|---|---|---|---|
| 2 | 4 | -1 | 1 |
网友评论