Changeset: a61b9de0a676 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a61b9de0a676
Modified Files:
dump.sql
Branch: monetdbe-proxy
Log Message:
Properly format SQL type in table element.
diffs (90 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -7,15 +7,76 @@ CREATE FUNCTION ENI (s STRING) RETURNS S
CREATE FUNCTION comment_on(ob STRING, id STRING, r STRING) RETURNS STRING
BEGIN RETURN ifthenelse(r IS NOT NULL, '\nCOMMENT ON ' || ob || ' ' || id || '
IS ' || SQ(r) || ';', ''); END;
+CREATE FUNCTION dump_type(type STRING, digits INT, scale INT) RETURNS STRING
BEGIN
+ RETURN
+ CASE
+ WHEN type = 'boolean' THEN 'BOOLEAN'
+ WHEN type = 'int' THEN 'INTEGER'
+ WHEN type = 'smallint' THEN 'SMALLINT'
+ WHEN type = 'tinyiny' THEN 'TINYINT'
+ WHEN type = 'bigint' THEN 'BIGINT'
+ WHEN type = 'hugeint' THEN 'HUGEINT'
+ WHEN type = 'date' THEN 'DATE'
+ WHEN type = 'month_interval' THEN CASE
+ WHEN digits = 1 THEN 'INTERVAL YEAR'
+ WHEN digits = 2 THEN 'INTERVAL YEAR TO MONTH'
+ ELSE 'INTERVAL MONTH' --ASSUMES digits = 3
+ END
+ WHEN type LIKE '%_INTERVAL' THEN CASE
+ WHEN digits = 4 THEN 'INTERVAL DAY'
+ WHEN digits = 5 THEN 'INTERVAL DAY TO HOUR'
+ WHEN digits = 6 THEN 'INTERVAL DAY TO MINUTE'
+ WHEN digits = 7 THEN 'INTERVAL DAY TO SECOND'
+ WHEN digits = 8 THEN 'INTERVAL HOUR'
+ WHEN digits = 9 THEN 'INTERVAL HOUR TO MINUTE'
+ WHEN digits = 10 THEN 'INTERVAL HOUR TO SECOND'
+ WHEN digits = 11 THEN 'INTERVAL MINUTE'
+ WHEN digits = 12 THEN 'INTERVAL MINUTE TO SECOND'
+ ELSE 'INTERVAL SECOND' --ASSUMES digits = 13
+ END
+ WHEN type = 'varchar' OR type = 'clob' THEN CASE
+ WHEN digits = 0 THEN 'CHARACTER LARGE OBJECT'
+ ELSE 'CHARACTER LARGE OBJECT(' || digits || ')'
--ASSUMES digits IS NOT NULL
+ END
+ WHEN type = 'blob' THEN CASE
+ WHEN digits = 0 THEN 'BINARY LARGE OBJECT'
+ ELSE 'BINARY LARGE OBJECT(' || digits || ')' --ASSUMES
digits IS NOT NULL
+ END
+ WHEN type = 'timestamp' THEN 'TIMESTAMP' || ifthenelse(digits
<> 7, '(' || (digits -1) || ') ', ' ')
+ WHEN type = 'timestampz' THEN 'TIMESTAMP' || ifthenelse(digits
<> 7, '(' || (digits -1) || ') ', ' ') || 'WITH TIME ZONE'
+ WHEN type = 'time' THEN 'TIME' || ifthenelse(digits <> 1, '('
|| (digits -1) || ') ', ' ')
+ WHEN type = 'timez' THEN 'TIME' || ifthenelse(digits <> 1, '('
|| (digits -1) || ') ', ' ') || 'WITH TIME ZONE'
+ WHEN type = 'real' THEN CASE
+ WHEN digits = 24 AND scale=0 THEN 'REAL'
+ WHEN scale=0 THEN 'FLOAT(' || digits || ')'
+ ELSE 'FLOAT(' || digits || ',' || scale || ')'
+ END
+ WHEN type = 'double' THEN CASE
+ WHEN digits = 53 AND scale=0 THEN 'DOUBLE'
+ WHEN scale = 0 THEN 'FLOAT(' || digits || ')'
+ ELSE 'FLOAT(' || digits || ',' || scale || ')'
+ END
+ WHEN type = 'decimal' THEN CASE
+ WHEN (digits = 1 AND scale = 0) OR digits = 0 THEN
'DECIMAL'
+ WHEN scale = 0 THEN 'DECIMAL(' || digits || ')'
+ WHEN digits = 39 THEN 'DECIMAL(' || 38 || ',' || scale
|| ')'
+ WHEN digits = 19 AND (SELECT COUNT(*) = 0 FROM
sys.types WHERE sqlname = 'hugeint' ) THEN 'DECIMAL(' || 18 || ',' || scale ||
')'
+ ELSE 'DECIMAL(' || digits || ',' || scale || ')'
+ END
+ ELSE upper(type) || '(' || digits || ',' || scale || ')'
--TODO: might be a bit too simple
+ END;
+END;
+
--TODO expand dump_column_definition functionality
CREATE FUNCTION dump_column_definition(tid INT) RETURNS STRING BEGIN
RETURN
SELECT
- GROUP_CONCAT(DQ(c.name) || ' ' || c.type ||
ifthenelse(c."null" = 'false', ' NOT NULL ', ''), ', ')
- --c.type_digits,
- --c.type_scale,
- --c.null,
- --c.default,
+ GROUP_CONCAT(
+ DQ(c.name) || ' ' ||
+ dump_type(c.type, c.type_digits, c.type_scale)
||
+ ifthenelse(c."null" = 'false', ' NOT NULL ',
'') ||
+ ifthenelse(c."default" IS NOT NULL, ' DEFAULT '
|| c."default", '')
+ , '')
--c.number
FROM sys._columns c
WHERE c.table_id = tid;
@@ -142,3 +203,4 @@ END;
SELECT dump_database(TRUE);
ROLLBACK;
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
