mariadb.sql
· 2.4 KiB · MySQL
Surowy
SELECT
'type ' || table_name || ' struct {' || CHR(10) || fields || CHR(10) || '}' AS models
FROM
(
SELECT
table_name,
REPLACE(RTRIM(XMLAGG(XMLELEMENT(E, CHR(9) || column_name || CHR(9) || type_info || CHR(9) || annotation || CHR(10))).EXTRACT('//text()').GETCLOBVAL(), CHR(10)), '"', '"') AS fields
FROM
(
SELECT
REPLACE(INITCAP(table_name), '_', '') AS table_name,
REPLACE(INITCAP(column_name), '_', '') AS column_name,
CASE data_type
WHEN 'TIMESTAMP' THEN 'time.Time'
WHEN 'TIMESTAMP(6)' THEN 'time.Time'
WHEN 'TIMESTAMP WITH TIME ZONE' THEN 'time.Time'
WHEN 'TIMESTAMP WITH LOCAL TIME ZONE' THEN 'time.Time'
WHEN 'LONG' THEN 'int64'
WHEN 'NUMBER' THEN
CASE
WHEN data_precision IS NULL AND data_scale = 0 THEN 'int'
WHEN data_precision IS NOT NULL AND data_scale = 0 THEN 'int64'
ELSE 'float64'
END
WHEN 'FLOAT' THEN 'float32'
WHEN 'BINARY_FLOAT' THEN 'float32'
WHEN 'BINARY_DOUBLE' THEN 'float64'
WHEN 'CHAR' THEN 'string'
WHEN 'VARCHAR2' THEN 'sql.NullString'
WHEN 'NCHAR' THEN 'string'
WHEN 'NVARCHAR2' THEN 'string'
WHEN 'CLOB' THEN 'go_ora.Clob'
WHEN 'NCLOB' THEN 'go_ora.Nlob'
WHEN 'BLOB' THEN 'go_ora.Blob'
WHEN 'DATE' THEN 'go_ora.NullTimeStamp'
WHEN 'RAW' THEN '[]byte'
WHEN 'UNDEFINED' THEN 'database.OraUNDEFINED'
-- add your own type converters as needed or it will default to 'string'
ELSE 'UNKNOWN'
END AS type_info,
'`json:"' || column_name || '" db:"' || column_name || '"`' AS annotation
FROM all_tab_columns
WHERE owner = 'DELOPRO'
)
--WHERE type_info = 'UNKNOWN'
GROUP BY table_name
)
--WHERE table_name = 'V8EventVw'
ORDER BY table_name
| 1 | SELECT |
| 2 | 'type ' || table_name || ' struct {' || CHR(10) || fields || CHR(10) || '}' AS models |
| 3 | FROM |
| 4 | ( |
| 5 | SELECT |
| 6 | table_name, |
| 7 | REPLACE(RTRIM(XMLAGG(XMLELEMENT(E, CHR(9) || column_name || CHR(9) || type_info || CHR(9) || annotation || CHR(10))).EXTRACT('//text()').GETCLOBVAL(), CHR(10)), '"', '"') AS fields |
| 8 | FROM |
| 9 | ( |
| 10 | SELECT |
| 11 | REPLACE(INITCAP(table_name), '_', '') AS table_name, |
| 12 | REPLACE(INITCAP(column_name), '_', '') AS column_name, |
| 13 | CASE data_type |
| 14 | WHEN 'TIMESTAMP' THEN 'time.Time' |
| 15 | WHEN 'TIMESTAMP(6)' THEN 'time.Time' |
| 16 | WHEN 'TIMESTAMP WITH TIME ZONE' THEN 'time.Time' |
| 17 | WHEN 'TIMESTAMP WITH LOCAL TIME ZONE' THEN 'time.Time' |
| 18 | WHEN 'LONG' THEN 'int64' |
| 19 | WHEN 'NUMBER' THEN |
| 20 | CASE |
| 21 | WHEN data_precision IS NULL AND data_scale = 0 THEN 'int' |
| 22 | WHEN data_precision IS NOT NULL AND data_scale = 0 THEN 'int64' |
| 23 | ELSE 'float64' |
| 24 | END |
| 25 | WHEN 'FLOAT' THEN 'float32' |
| 26 | WHEN 'BINARY_FLOAT' THEN 'float32' |
| 27 | WHEN 'BINARY_DOUBLE' THEN 'float64' |
| 28 | WHEN 'CHAR' THEN 'string' |
| 29 | WHEN 'VARCHAR2' THEN 'sql.NullString' |
| 30 | WHEN 'NCHAR' THEN 'string' |
| 31 | WHEN 'NVARCHAR2' THEN 'string' |
| 32 | WHEN 'CLOB' THEN 'go_ora.Clob' |
| 33 | WHEN 'NCLOB' THEN 'go_ora.Nlob' |
| 34 | WHEN 'BLOB' THEN 'go_ora.Blob' |
| 35 | WHEN 'DATE' THEN 'go_ora.NullTimeStamp' |
| 36 | WHEN 'RAW' THEN '[]byte' |
| 37 | WHEN 'UNDEFINED' THEN 'database.OraUNDEFINED' |
| 38 | -- add your own type converters as needed or it will default to 'string' |
| 39 | ELSE 'UNKNOWN' |
| 40 | END AS type_info, |
| 41 | '`json:"' || column_name || '" db:"' || column_name || '"`' AS annotation |
| 42 | FROM all_tab_columns |
| 43 | WHERE owner = 'DELOPRO' |
| 44 | ) |
| 45 | --WHERE type_info = 'UNKNOWN' |
| 46 | GROUP BY table_name |
| 47 | ) |
| 48 | --WHERE table_name = 'V8EventVw' |
| 49 | ORDER BY table_name |
oracle.sql
· 2.4 KiB · MySQL
Surowy
SELECT
'type ' || table_name || ' struct {' || CHR(10) || fields || CHR(10) || '}' AS models
FROM
(
SELECT
table_name,
REPLACE(RTRIM(XMLAGG(XMLELEMENT(E, CHR(9) || column_name || CHR(9) || type_info || CHR(9) || annotation || CHR(10))).EXTRACT('//text()').GETCLOBVAL(), CHR(10)), '"', '"') AS fields
FROM
(
SELECT
REPLACE(INITCAP(table_name), '_', '') AS table_name,
REPLACE(INITCAP(column_name), '_', '') AS column_name,
CASE data_type
WHEN 'TIMESTAMP' THEN 'time.Time'
WHEN 'TIMESTAMP(6)' THEN 'time.Time'
WHEN 'TIMESTAMP WITH TIME ZONE' THEN 'time.Time'
WHEN 'TIMESTAMP WITH LOCAL TIME ZONE' THEN 'time.Time'
WHEN 'LONG' THEN 'int64'
WHEN 'NUMBER' THEN
CASE
WHEN data_precision IS NULL AND data_scale = 0 THEN 'int'
WHEN data_precision IS NOT NULL AND data_scale = 0 THEN 'int64'
ELSE 'float64'
END
WHEN 'FLOAT' THEN 'float32'
WHEN 'BINARY_FLOAT' THEN 'float32'
WHEN 'BINARY_DOUBLE' THEN 'float64'
WHEN 'CHAR' THEN 'string'
WHEN 'VARCHAR2' THEN 'sql.NullString'
WHEN 'NCHAR' THEN 'string'
WHEN 'NVARCHAR2' THEN 'string'
WHEN 'CLOB' THEN 'go_ora.Clob'
WHEN 'NCLOB' THEN 'go_ora.Nlob'
WHEN 'BLOB' THEN 'go_ora.Blob'
WHEN 'DATE' THEN 'go_ora.NullTimeStamp'
WHEN 'RAW' THEN '[]byte'
WHEN 'UNDEFINED' THEN 'database.OraUNDEFINED'
-- add your own type converters as needed or it will default to 'string'
ELSE 'UNKNOWN'
END AS type_info,
'`json:"' || column_name || '" db:"' || column_name || '"`' AS annotation
FROM all_tab_columns
WHERE owner = 'DELOPRO'
)
--WHERE type_info = 'UNKNOWN'
GROUP BY table_name
)
--WHERE table_name = 'V8EventVw'
ORDER BY table_name
| 1 | SELECT |
| 2 | 'type ' || table_name || ' struct {' || CHR(10) || fields || CHR(10) || '}' AS models |
| 3 | FROM |
| 4 | ( |
| 5 | SELECT |
| 6 | table_name, |
| 7 | REPLACE(RTRIM(XMLAGG(XMLELEMENT(E, CHR(9) || column_name || CHR(9) || type_info || CHR(9) || annotation || CHR(10))).EXTRACT('//text()').GETCLOBVAL(), CHR(10)), '"', '"') AS fields |
| 8 | FROM |
| 9 | ( |
| 10 | SELECT |
| 11 | REPLACE(INITCAP(table_name), '_', '') AS table_name, |
| 12 | REPLACE(INITCAP(column_name), '_', '') AS column_name, |
| 13 | CASE data_type |
| 14 | WHEN 'TIMESTAMP' THEN 'time.Time' |
| 15 | WHEN 'TIMESTAMP(6)' THEN 'time.Time' |
| 16 | WHEN 'TIMESTAMP WITH TIME ZONE' THEN 'time.Time' |
| 17 | WHEN 'TIMESTAMP WITH LOCAL TIME ZONE' THEN 'time.Time' |
| 18 | WHEN 'LONG' THEN 'int64' |
| 19 | WHEN 'NUMBER' THEN |
| 20 | CASE |
| 21 | WHEN data_precision IS NULL AND data_scale = 0 THEN 'int' |
| 22 | WHEN data_precision IS NOT NULL AND data_scale = 0 THEN 'int64' |
| 23 | ELSE 'float64' |
| 24 | END |
| 25 | WHEN 'FLOAT' THEN 'float32' |
| 26 | WHEN 'BINARY_FLOAT' THEN 'float32' |
| 27 | WHEN 'BINARY_DOUBLE' THEN 'float64' |
| 28 | WHEN 'CHAR' THEN 'string' |
| 29 | WHEN 'VARCHAR2' THEN 'sql.NullString' |
| 30 | WHEN 'NCHAR' THEN 'string' |
| 31 | WHEN 'NVARCHAR2' THEN 'string' |
| 32 | WHEN 'CLOB' THEN 'go_ora.Clob' |
| 33 | WHEN 'NCLOB' THEN 'go_ora.Nlob' |
| 34 | WHEN 'BLOB' THEN 'go_ora.Blob' |
| 35 | WHEN 'DATE' THEN 'go_ora.NullTimeStamp' |
| 36 | WHEN 'RAW' THEN '[]byte' |
| 37 | WHEN 'UNDEFINED' THEN 'database.OraUNDEFINED' |
| 38 | -- add your own type converters as needed or it will default to 'string' |
| 39 | ELSE 'UNKNOWN' |
| 40 | END AS type_info, |
| 41 | '`json:"' || column_name || '" db:"' || column_name || '"`' AS annotation |
| 42 | FROM all_tab_columns |
| 43 | WHERE owner = 'DELOPRO' |
| 44 | ) |
| 45 | --WHERE type_info = 'UNKNOWN' |
| 46 | GROUP BY table_name |
| 47 | ) |
| 48 | --WHERE table_name = 'V8EventVw' |
| 49 | ORDER BY table_name |
postgres.sql
· 1.1 KiB · MySQL
Surowy
WITH models AS (
WITH data AS (
SELECT
replace(initcap(table_name::text), '_', '') table_name,
replace(initcap(column_name::text), '_', '') column_name,
CASE data_type
WHEN 'timestamp without time zone' THEN 'time.Time'
WHEN 'timestamp with time zone' THEN 'time.Time'
WHEN 'boolean' THEN 'bool'
WHEN 'bigint' THEN 'int64'
WHEN 'integer' THEN 'int'
WHEN 'ARRAY' THEN 'pgtype.Array[string]'
WHEN 'date' THEN 'pgtype.Date'
WHEN 'character varying' THEN 'string'
-- add your own type converters as needed or it will default to 'string'
ELSE 'UNKNOWN'
END AS type_info,
'`json:"' || column_name ||'"`' AS annotation
FROM information_schema.columns
WHERE table_schema IN ('public')
ORDER BY table_schema, table_name, ordinal_position
)
SELECT table_name, STRING_AGG(E'\t' || column_name || E'\t' || type_info || E'\t' || annotation, E'\n') fields
FROM data
GROUP BY table_name
)
SELECT 'type ' || table_name || E' struct {\n' || fields || E'\n}' models
FROM models ORDER BY table_name
| 1 | WITH models AS ( |
| 2 | WITH data AS ( |
| 3 | SELECT |
| 4 | replace(initcap(table_name::text), '_', '') table_name, |
| 5 | replace(initcap(column_name::text), '_', '') column_name, |
| 6 | CASE data_type |
| 7 | WHEN 'timestamp without time zone' THEN 'time.Time' |
| 8 | WHEN 'timestamp with time zone' THEN 'time.Time' |
| 9 | WHEN 'boolean' THEN 'bool' |
| 10 | WHEN 'bigint' THEN 'int64' |
| 11 | WHEN 'integer' THEN 'int' |
| 12 | WHEN 'ARRAY' THEN 'pgtype.Array[string]' |
| 13 | WHEN 'date' THEN 'pgtype.Date' |
| 14 | WHEN 'character varying' THEN 'string' |
| 15 | -- add your own type converters as needed or it will default to 'string' |
| 16 | ELSE 'UNKNOWN' |
| 17 | END AS type_info, |
| 18 | '`json:"' || column_name ||'"`' AS annotation |
| 19 | FROM information_schema.columns |
| 20 | WHERE table_schema IN ('public') |
| 21 | ORDER BY table_schema, table_name, ordinal_position |
| 22 | ) |
| 23 | SELECT table_name, STRING_AGG(E'\t' || column_name || E'\t' || type_info || E'\t' || annotation, E'\n') fields |
| 24 | FROM data |
| 25 | GROUP BY table_name |
| 26 | ) |
| 27 | SELECT 'type ' || table_name || E' struct {\n' || fields || E'\n}' models |
| 28 | FROM models ORDER BY table_name |