AntDB 数据库分布式功能-FQS增强16

tocata 2024-09-02
附录

-- 表格 fqs_bmsql_new_order：
DROP TABLE IF EXISTS fqs_bmsql_new_order;
CREATE TABLE fqs_bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
) distribute by hash(no_w_id);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (3001, 1, 1);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (1, 1, 1);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (2, 1, 1);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (3001, 1, 2);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (1, 1, 2);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (2, 1, 2);


-- 表格 fqs_bmsql_order_line：
DROP TABLE IF EXISTS fqs_bmsql_order_line;
CREATE TABLE fqs_bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
) distribute by hash(ol_w_id);
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3001, 1, 1, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (1, 1, 1, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (2, 1, 1, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3001, 1, 2, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (1, 1, 2, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (2, 1, 2, 1, 1, 2, 1, 22.46, 's_dist_02');


-- 表格 fqs_bmsql_oorder：
DROP TABLE IF EXISTS fqs_bmsql_oorder;
create table fqs_bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp
) distribute by hash(o_w_id);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (3001, 1, 1, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (1, 1, 1, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (2, 1, 1, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (3001, 1, 2, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (1, 1, 2, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (2, 1, 2, 1, '2022-04-24 14:16:10.828', 5, 1);


-- 表格 fqs_bmsql_customer
DROP TABLE IF EXISTS fqs_bmsql_customer;
CREATE TABLE fqs_bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500)
) distribute by hash(c_w_id);
INSERT INTO fqs_bmsql_customer VALUES (1, 1, 1, 0.4395, 'GC', 'BARBARBAR', 'HNVBhIjtjsku8', 50000.00, -10.00, 10.00, 1, 1, 'HDJJSDc1RLmwqHcr', 'kIWYlHTvnf7kUxa', 'ra6KA3FJjY', 'BU', '557011111', '9366706537194349', '2022-09-20 17:44:43.022', 'OE', 'RXEzmlIlVMBZV0vuNjuvmzBZR3t1wLxeduFW9xlzsrpwqewHEVqb9TkGx6kI0iWMy
5nuuK8F48JToOYbCq2VQxwNbwoPnCoJKmMNJiJEwXBB6eEls9jZfngID6MMJhQ7XgYMRnDWG0qh5ln4i92A1uE2PKjjIysF2YTKNo5XLWmWq2NNNhsdOAuvpW6UwEGwtYDsegshgpqKI6koRjCvW3GPQBTxaCBL7vh1mE4
1AG0elXsPBUSXGbQEHrw6tFsw3plpHfYkH36jagdIi5Dk2TcfS19z8veG9VSBjUwRhg2i7jwsKC86gQ9GB67DNgYkzZdsCNv36cByw3vOA4B0QvOcKnByOkFwUijzta9ZBBwnHrao3n4m979UzeK6bRCvMXrM2N2ewngvTkZmtoysKA');
INSERT INTO fqs_bmsql_customer VALUES (1, 1, 2, 0.1748, 'GC', 'BARBAROUGHT', 'kEPmpoUTA0', 50000.00, -10.00, 10.00, 1, 1, 'Hi7ZDuNEFVL3BFhq3V', 'mCM8h1kALRvU5I4k'
, 'c3VpfkpPADAe1Th2', 'FA', '976111111', '3045334757503957', '2022-09-20 17:44:43.023', 'OE', 'fXcA9aGK36u4ILnvtdDdd95XkPEeeSNwho03B8fhHy41MB15FOwvqEQrSRkiWDPRgYaXXou
gfj0tFl72xfVRY0ELDKEKoII0AA1f3ncdK8SRLgZK18uAVnS0daMnV1xkrOZyHmQqSI7Thnp8qT1T02AfTxIz6jr1lzOY7T3bFlX8S1DkvoNjCOMh97LEYBXWdoxGYGjb6rWSRKDJuIIARyv1fhuTW6TYb4mCi8pa5JcA3
xnir5FuzutQbUcis76g2sQQCrgEg3shKwCtoSRStFy1XqjfQr9Un0hydSoJeLwXKqo6SBgBGuI3XTpgvLEgTw93OsE0M2HinmOpHFPEb3Qr3LhwHZePB7mWnt4f8JocvcYYhh9eWcaB0yWn3PRrMWygGIXX2NY2bVL6hr3
hgABe4oEYDih4wMpAxNx4mao');


-- 表格 fqs_bmsql_warehouse：
DROP TABLE IF EXISTS fqs_bmsql_warehouse;
CREATE TABLE fqs_bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
) distribute by hash(w_id);
INSERT INTO fqs_bmsql_warehouse VALUES (1, 300000.00, 0.0468, 'gehIih', 'CIJ5N3Y3F5s', 'ul7E6xoKz37', 'W2Kn3kOm7F7IH', 'FL', '007411111');
INSERT INTO fqs_bmsql_warehouse VALUES (2, 300000.00, 0.0720, 'TwfKXI', 'qT5gTWKSYLqeteLYZ6A', 'zG4c4w9F2sWCU', 'Kr0SnDySPb9j5Fe1', 'HA', '499111111');
INSERT INTO fqs_bmsql_warehouse VALUES (3, 300000.00, 0.1337, 'ec9fPQ9', 'c4z8apy6f44aOnKT', 'kgCuQ6HCmITVLsseI2L', 'Y3KHRBZygx', 'LY', '529111111');
INSERT INTO fqs_bmsql_warehouse VALUES (4, 300000.00, 0.0422, 'f3qgAZWl', 'H6ygGef5QIS', 'ToYe3XH8fL01f', 'k3FgsJ5dB8ZjVwhMpyim', 'KH', '998111111');
INSERT INTO fqs_bmsql_warehouse VALUES (5, 300000.00, 0.1464, 'Er8Mt3', 'cOdXHSNZgUjcByP9', 'zCujFInAWxrmDl4X', 'Wpc7AX0IC0y2P6Na', 'BB', '159811111');

-- 表格 fqs_bmsql_district：
DROP TABLE IF EXISTS fqs_bmsql_district;
CREATE TABLE fqs_bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
) distribute by hash(d_w_id);
INSERT INTO fqs_bmsql_district VALUES (1, 1, 30000.00, 0.0140, 3001, 'Ou2TyklYKp', 'QU7IWKeF7CmEPw', 'wvvLhFQNfms4ubgNtutj', 'LUsGPw2sLQhidDIQeJ8', 'PH', '596911111');
INSERT INTO fqs_bmsql_district VALUES (1, 2, 30000.00, 0.0885, 3001, 'z7ooGzlm', 'p3k33NWJYeo6IK5m', 'Mrl6ehDCQcY2RPEa', 'KJiO0vplriI', 'XY', '568211111');
INSERT INTO fqs_bmsql_district VALUES (1, 3, 30000.00, 0.0753, 3001, 'uQhmcio', 'ZZd4QYPh2R4jP', 'BUzVx3gLsRIu', 'MInqHFI7K8', 'DD', '964711111');
INSERT INTO fqs_bmsql_district VALUES (2, 1, 30000.00, 0.0706, 3001, 'wI93M62hoW', 'oMYOpBCYlyQFJ7iKBs', 'cycBxwfjw02LjP0hS3', 'YnKNWyaIpqyqJvnGR', 'AI', '118411111');
INSERT INTO fqs_bmsql_district VALUES (2, 2, 30000.00, 0.1235, 3001, 'zsyRwYjEI', 'GxfjQUMmGvb', 'rvdyaHeqCvd2vm1OqX1S', 'hJbAR2M0pfehPCCADVfD', 'UR', '157011111');
INSERT INTO fqs_bmsql_district VALUES (2, 3, 30000.00, 0.0722, 3001, 'Yl3frBMW', 'AIxDjNtK9zHFVd79EJK', 'Lxe2AGES0S5ckpOceXm', 'CqCCYhGddw71Y', 'XM', '812911111');


-- 表格 fqs_window_test：
DROP TABLE IF EXISTS fqs_window_test;
CREATE TABLE fqs_window_test (
 id int4 NULL,
 province varchar(10) NULL,
 city varchar(10) NULL
);
INSERT INTO fqs_window_test (id,province,city) VALUES
(1,'guangdong','guangzhou')
,(2,'guangdong','shenzhen')
,(3,'guangdong','foushan')
,(4,'hunan','changsha')
,(5,'sichuan','chengdu')
,(6,'hunan','hengyang');


-- 表格 fqs_bmsql_warehouse：
DROP TABLE IF EXISTS fqs_bmsql_warehouse;
CREATE TABLE fqs_bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
) distribute by hash(w_id);
INSERT INTO fqs_bmsql_warehouse VALUES (1, 300000.00, 0.0468, 'gehIih', 'CIJ5N3Y3F5s', 'ul7E6xoKz37', 'W2Kn3kOm7F7IH', 'FL', '007411111');
INSERT INTO fqs_bmsql_warehouse VALUES (2, 300000.00, 0.0720, 'TwfKXI', 'qT5gTWKSYLqeteLYZ6A', 'zG4c4w9F2sWCU', 'Kr0SnDySPb9j5Fe1', 'HA', '499111111');
INSERT INTO fqs_bmsql_warehouse VALUES (3, 300000.00, 0.1337, 'ec9fPQ9', 'c4z8apy6f44aOnKT', 'kgCuQ6HCmITVLsseI2L', 'Y3KHRBZygx', 'LY', '529111111');
INSERT INTO fqs_bmsql_warehouse VALUES (4, 300000.00, 0.0422, 'f3qgAZWl', 'H6ygGef5QIS', 'ToYe3XH8fL01f', 'k3FgsJ5dB8ZjVwhMpyim', 'KH', '998111111');
INSERT INTO fqs_bmsql_warehouse VALUES (5, 300000.00, 0.1464, 'Er8Mt3', 'cOdXHSNZgUjcByP9', 'zCujFInAWxrmDl4X', 'Wpc7AX0IC0y2P6Na', 'BB', '159811111');


-- 表格 FQS_COMPANY：
DROP TABLE IF EXISTS FQS_COMPANY;
CREATE TABLE FQS_COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO FQS_COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );


-- 表格 FQS_DEPARTMENT：
DROP TABLE IF EXISTS FQS_DEPARTMENT;
CREATE TABLE FQS_DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

INSERT INTO FQS_DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO FQS_DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO FQS_DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

-- 表格 fqs_t1：
DROP TABLE IF EXISTS fqs_t1;
CREATE TABLE fqs_t1 (
  c1    integer primary key,
   rel_id         INT      NOT NULL
) distribute by hash(c1);

-- 表格 fqs_t2:
DROP TABLE IF EXISTS fqs_t2;
CREATE TABLE fqs_t2(
   c1 INT PRIMARY KEY      NOT NULL,
   rel_id         INT      NOT NULL
) distribute by hash(c1);

-- 表格 fqs_pgbench_history：
DROP TABLE IF EXISTS fqs_pgbench_history;
CREATE TABLE fqs_pgbench_history (tid integer,bid integer,aid integer,delta integer,mtime timestamp without time zone,filler character(22))
DISTRIBUTE BY HASH(tid);


-- 表格 fqs_test_mod：
DROP TABLE IF EXISTS fqs_test_mod;
CREATE TABLE fqs_test_mod(id int, name text) distribute by modulo(id);
INSERT INTO fqs_test_mod values (1,'1'),(2,'2'),(3,'3');


-- 表格 fqs_test_repl：
DROP TABLE IF EXISTS fqs_test_repl;
CREATE TABLE fqs_test_repl(id int, name text) distribute by REPLICATION;
INSERT INTO fqs_test_repl values (1,'1'),(2,'2'),(3,'3');


-- 表格 fqs_test_random：
DROP TABLE IF EXISTS fqs_test_random;
CREATE TABLE fqs_test_random(id int, name text) distribute by random;
INSERT INTO fqs_test_random values (1,'1'),(2,'2'),(3,'3');


-- 表格 fqs_t_char：
DROP TABLE IF EXISTS fqs_t_char;
CREATE TABLE fqs_t_char(id int,iid int,name varchar) distribute by hash(name);
INSERT INTO fqs_t_char SELECT i,floor(100*i),'name'||i FROM generate_series(0, 1000) i;

-- 表格 fqs_t_int：
DROP TABLE IF EXISTS fqs_t_int;
CREATE TABLE fqs_t_int(id int,iid int,name varchar) distribute by hash(id);
INSERT INTO fqs_t_int SELECT i,floor(100*i),'name'||i FROM generate_series(0, 1000) i;

-- 表格 fqs_aa、fqs_bb、fqs_cc：
DROP TABLE IF EXISTS fqs_aa;
DROP TABLE IF EXISTS fqs_bb;
DROP TABLE IF EXISTS fqs_cc;
CREATE TABLE fqs_aa (
  a integer primary key,
  b integer,
  c integer,
  d integer
);
CREATE TABLE fqs_bb (
  a integer primary key,
  b integer,
  c integer,
  d integer
);
CREATE TABLE fqs_cc (
  a integer primary key,
  b integer,
  c integer,
  d integer
);
INSERT INTO fqs_aa VALUES(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
INSERT INTO fqs_bb VALUES(1,1,2,1),(2,2,3,2),(3,3,4,3),(4,4,4,4);
INSERT INTO fqs_aa VALUES(5,1,1,1),(6,2,2,2),(7,3,3,3),(8,4,4,4);
INSERT INTO fqs_cc VALUES(1,1,2,1),(2,2,3,2),(3,3,4,3),(4,4,4,4);


-- 表格 fqs_t_DN1：
DROP TABLE IF EXISTS fqs_t_DN1;
CREATE TABLE fqs_t_DN1(id int, num int, des text) to node(DN1);
INSERT INTO fqs_t_DN1 VALUES(1,1,'111'),(2,2,'22'),(3,3,'33'),(4,4,'444');


-- 表格 fqs_test_hash1、fqs_test_hash2：
DROP TABLE IF EXISTS fqs_test_hash1;
CREATE TABLE fqs_test_hash1(id int, name text) distribute by hash(id);
INSERT INTO fqs_test_hash1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_hash2;
CREATE TABLE fqs_test_hash2(id int, name text) distribute by hash(id);
INSERT INTO fqs_test_hash2 values (1,'1'),(2,'2'),(3,'3');

-- 表格 fqs_test_repl1、fqs_test_repl2：
DROP TABLE IF EXISTS fqs_test_repl1;
CREATE TABLE fqs_test_repl1(id int, name text) distribute by replication;
INSERT INTO fqs_test_repl1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_repl2;
CREATE TABLE fqs_test_repl2(id int, name text) distribute by replication;
INSERT INTO fqs_test_repl2 values (1,'1'),(2,'2'),(3,'3');

-- 表格 fqs_test_mod1、fqs_test_mod2：
DROP TABLE IF EXISTS fqs_test_mod1;
CREATE TABLE fqs_test_mod1(id int, name text) distribute by modulo(id);
INSERT INTO fqs_test_mod1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_mod2;
CREATE TABLE fqs_test_mod2(id int, name text) distribute by modulo(id);
INSERT INTO fqs_test_mod2 values (1,'1'),(2,'2'),(3,'3');

-- 表格 fqs_test_random1、fqs_test_random2：
DROP TABLE IF EXISTS fqs_test_random1;
CREATE TABLE fqs_test_random1(id int, name text) distribute by random;
INSERT INTO fqs_test_random1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_random2;
CREATE TABLE fqs_test_random2(id int, name text) distribute by random;
INSERT INTO fqs_test_random2 values (1,'1'),(2,'2'),(3,'3');

复制
antdb