SQL tables to Go structs

mariadb.sql · 2.4 KiB · MySQL Raw

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)), '&quot;', '"') 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 Raw

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)), '&quot;', '"') 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.2 KiB · MySQL Raw

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'
        WHEN 'double precision' THEN 'float64'
        -- 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	WHEN 'double precision' THEN 'float64'
16	-- add your own type converters as needed or it will default to 'string'
17	ELSE 'UNKNOWN'
18	END AS type_info,
19	'`json:"' \|\| column_name \|\|'"`' AS annotation
20	FROM information_schema.columns
21	WHERE table_schema IN ('public')
22	ORDER BY table_schema, table_name, ordinal_position
23	)
24	SELECT table_name, STRING_AGG(E'\t' \|\| column_name \|\| E'\t' \|\| type_info \|\| E'\t' \|\| annotation, E'\n') fields
25	FROM data
26	GROUP BY table_name
27	)
28	SELECT 'type ' \|\| table_name \|\| E' struct {\n' \|\| fields \|\| E'\n}' models
29	FROM models ORDER BY table_name