diff options
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 316 |
1 files changed, 150 insertions, 166 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 208e81965..fad27adda 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -14,9 +14,13 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- --- Everything in one big transaction BEGIN; +SELECT _v.register_patch('exchange-0001', NULL, NULL); + +CREATE SCHEMA exchange; +COMMENT ON SCHEMA exchange IS 'taler-exchange data'; + SET search_path TO exchange; --------------------------------------------------------------------------- @@ -38,7 +42,7 @@ COMMENT ON COLUMN exchange_tables.name COMMENT ON COLUMN exchange_tables.version IS 'Version of the DB in which the given action happened'; COMMENT ON COLUMN exchange_tables.action - IS 'Action to take on the table (e.g. create, alter, constrain, foreign, or drop). Create, alter and drop are done for master and each partition; constrain is only for partitions or for master if there are no partitions; master only on master (takes no argument); foreign only on master if there are no partitions.'; + IS 'Action to take on the table (e.g. create, constrain, foreign, or drop). Create is done for the master table and each partition; constrain is only for partitions or for master if there are no partitions; master only on master (takes no argument); foreign only on master if there are no partitions.'; COMMENT ON COLUMN exchange_tables.partitioned IS 'TRUE if the table is partitioned'; COMMENT ON COLUMN exchange_tables.by_range @@ -48,51 +52,58 @@ COMMENT ON COLUMN exchange_tables.finished CREATE FUNCTION create_partitioned_table( - IN table_definition VARCHAR - ,IN table_name VARCHAR - ,IN main_table_partition_str VARCHAR -- Used only when it is the main table - we do not partition shard tables - ,IN shard_suffix VARCHAR DEFAULT NULL + IN table_definition VARCHAR -- SQL template for table creation + ,IN table_name VARCHAR -- base name of the table + ,IN main_table_partition_str VARCHAR -- declaration for how to partition the table + ,IN partition_suffix VARCHAR DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - IF shard_suffix IS NOT NULL THEN - table_name=table_name || '_' || shard_suffix; + IF partition_suffix IS NULL + THEN + -- no partitioning, disable option main_table_partition_str = ''; + ELSE + IF partition_suffix > 0 + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; END IF; EXECUTE FORMAT( table_definition, table_name, main_table_partition_str ); -END -$$; +END $$; COMMENT ON FUNCTION create_partitioned_table - IS 'Generic function to create a table that is partitioned.'; + IS 'Generic function to create a table that is partitioned or sharded.'; CREATE FUNCTION comment_partitioned_table( IN table_comment VARCHAR ,IN table_name VARCHAR - ,IN shard_suffix VARCHAR DEFAULT NULL + ,IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - IF shard_suffix IS NOT NULL THEN - table_name=table_name || '_' || shard_suffix; - main_table_partition_str = ''; + IF ( (partition_suffix IS NOT NULL) AND + (partition_suffix > 0) ) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; END IF; EXECUTE FORMAT( - COMMENT ON TABLE %s IS '%s' + 'COMMENT ON TABLE %s IS %s' ,table_name - ,table_comment + ,quote_literal(table_comment) ); -END -$$; +END $$; COMMENT ON FUNCTION comment_partitioned_table IS 'Generic function to create a comment on table that is partitioned.'; @@ -102,34 +113,37 @@ CREATE FUNCTION comment_partitioned_column( IN table_comment VARCHAR ,IN column_name VARCHAR ,IN table_name VARCHAR - ,IN shard_suffix VARCHAR DEFAULT NULL + ,IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - IF shard_suffix IS NOT NULL THEN - table_name=table_name || '_' || shard_suffix; - main_table_partition_str = ''; + IF ( (partition_suffix IS NOT NULL) AND + (partition_suffix > 0) ) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; END IF; EXECUTE FORMAT( - COMMENT ON COLUMN %s.%s IS '%s' + 'COMMENT ON COLUMN %s.%s IS %s' ,table_name ,column_name - ,table_comment + ,quote_literal(table_comment) ); -END -$$; +END $$; COMMENT ON FUNCTION comment_partitioned_column IS 'Generic function to create a comment on column of a table that is partitioned.'; +--------------------------------------------------------------------------- +-- Main DB setup loop +--------------------------------------------------------------------------- CREATE FUNCTION create_tables( num_partitions INTEGER --- FIXME: not implemented like this, but likely good: -- NULL: no partitions, add foreign constraints -- 0: no partitions, no foreign constraints -- 1: only 1 default partition @@ -139,176 +153,146 @@ CREATE FUNCTION create_tables( LANGUAGE plpgsql AS $$ DECLARE - -- FIXME: use only ONE cursor and then switch on action! tc CURSOR FOR SELECT table_serial_id ,name ,action + ,partitioned ,by_range FROM exchange_tables WHERE NOT finished - AND partitioned - AND (action='create' - OR action='alter' - OR action='drop') - ORDER BY table_serial_id ASC; -DECLARE - ta CURSOR FOR - SELECT table_serial_id - ,name - ,action - ,by_range - FROM exchange_tables - WHERE NOT finished - AND partitioned - AND action='constrain' - ORDER BY table_serial_id ASC; -DECLARE - tf CURSOR FOR - SELECT table_serial_id - ,name - ,action - ,by_range - FROM exchange_tables - WHERE NOT finished - AND partitioned - AND action='foreign' - ORDER BY table_serial_id ASC; -DECLARE - tm CURSOR FOR - SELECT table_serial_id - ,name - ,action - ,by_range - FROM exchange_tables - WHERE NOT finished - AND partitioned - AND action='master' ORDER BY table_serial_id ASC; BEGIN - - -- run create/alter/drop actions FOR rec IN tc LOOP - -- First create the master table, either - -- completely unpartitioned, or with one - -- master and the 'default' partition - IF IS NULL num_partitions - THEN - -- No partitions at all. - EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,NULL - ); - ELSE - -- One default partition only. - EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,0 - ); - - IF NOT IS NULL num_partitions + CASE rec.action + -- "create" actions apply to master and partitions + WHEN "create" THEN - IF rec.by_range + IF (rec.partitioned AND + (num_partitions IS NOT NULL)) THEN - -- range partitions (only create default) - -- Create default partition. + -- Create master table with partitioning. EXECUTE FORMAT( - 'CREATE TABLE %s_default PARTITION OF %s DEFAULT' + 'PERFORM %s_table_%s (%s)'::text + ,rec.action ,rec.name + ,0 + ); + IF (rec.by_range OR + (num_partitions = 0)) + THEN + -- Create default partition. + IF (rec.by_range) + THEN + -- Range partition + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %s_default' + ' PARTITION OF %s' + ' FOR DEFAULT' + ,rec.name + ,rec.name + ); + ELSE + -- Hash partition + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %s_default' + ' PARTITION OF %s' + ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)' + ,rec.name + ,rec.name + ); + END IF; + ELSE + FOR i IN 1..num_partitions LOOP + -- Create num_partitions + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I' + ' PARTITION OF %I' + ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' + ,rec.name || '_' || i + ,rec.name + ,num_partitions + ,i-1 + ); + END LOOP; + END IF; + ELSE + -- Only create master table. No partitions. + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + END IF; + -- Constrain action apply to master OR each partition + WHEN "constrain" + THEN + ASSERT rec.partitioned, 'constrain action only applies to partitioned tables'; + IF (num_partitions IS NULL) + THEN + -- Constrain master table + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action ,rec.name + ,NULL ); ELSE - -- hash partitions - IF 0=num_partitions + IF (num_partitions = 0) THEN - -- Create default partition. - EXECUTE FORMAT( - 'CREATE TABLE IF NOT EXISTS %s_default PARTITION OF %s FOR VALUES WITH (MODULUS 1, REMAINDER 0)' - ,rec.name - ,rec.name - ); - END IF - FOR i IN 1..num_partitions LOOP - -- Create num_partitions + -- Constrain default table EXECUTE FORMAT( - 'CREATE TABLE IF NOT EXISTS %I' - ' PARTITION OF %I' - ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' - ,rec.name || '_' || i + 'PERFORM %s_table_%s (%s)'::text + ,rec.action ,rec.name - ,num_partitions - ,i-1 + ,'default' ); - END LOOP; + ELSE + -- Constrain each partition + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,i + ); + END LOOP; + END IF; END IF; - END IF; - UPDATE exchange_tables - SET finished=TRUE - WHERE table_serial_id=rec.table_serial_id; - END LOOP; -- create/alter/drop actions - - -- Run constrain actions - FOR rec IN ta - LOOP - IF IS NULL num_partitions + -- Foreign actions only apply if partitioning is off + WHEN "foreign" THEN - -- Constrain master - EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,NULL - ); - END IF - - IF 0=num_partitions + IF (num_partitions IS NULL) + THEN + -- Only create master table. No partitions. + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + END IF; + WHEN "master" THEN - -- constrain default partition EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)'::text + 'PERFORM %s_table_%s'::text ,rec.action ,rec.name - ,0 ); - END IF - FOR i IN 1..num_partitions LOOP - -- constrain each partition - EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,i::varchar - ); - END LOOP; + ELSE + ASSERT FALSE, 'unsupported action type: ' || rec.action; + END CASE; -- END CASE (rec.action) + -- Mark as finished UPDATE exchange_tables SET finished=TRUE WHERE table_serial_id=rec.table_serial_id; - END LOOP; + END LOOP; -- create/alter/drop actions +END $$; - -- run foreign actions - FOR rec IN tf - LOOP - IF IS NULL num_partitions - THEN - -- Add foreign constraints - EXECUTE FORMAT( - 'PERFORM %s_table_%s ()'::text - ,rec.action - ,rec.name - ); - END IF - UPDATE exchange_tables - SET finished=TRUE - WHERE table_serial_id=rec.table_serial_id; - END LOOP; +COMMENT ON FUNCTION create_tables + IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.'; -END -$$; -COMMENT ON FUNCTION create_tables - IS 'Creates all tables for the given number of partitions that need creating.'; +COMMIT; |