SDE表的行ID

基本

SDE利用自己的机制去管理表中每行的唯一标识。其通过i表管理行的唯一标识号，通过i_get_ids()函数和i_return_ids()函数对i表进行操作，并得到合适的行标识号。不同的表将有一个对应的编号并分别对应i表、i_get_ids()和i_return_ids()。如某表编号为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