~louis77/pg2sqlite

ref: d77f7c776a6a9c2bb6f2c4645fd7c962b6da0085 pg2sqlite/sqlite.go -rw-r--r-- 3.8 KiB
d77f7c77 — Louis Brauer Add --ignore-columns 3 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
/*	pg2sqlite - Migrate tables from PostgresQL to SQLite
	Copyright (C) 2021  Louis Brauer

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program.  If not, see <https://www.gnu.org/licenses/>.
*/

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"os"
	"strings"
)

type typeMap map[string]string

var (
	typeMapings = typeMap{
		"integer":                  "INTEGER",
		"smallint":                 "INTEGER",
		"numeric":                  "REAL",
		"date":                     "TEXT",
		"array":                    "TEXT",
		"character":                "TEXT",
		"character varying":        "TEXT",
		"timestamp with time zone": "TEXT",
		"__other":                  "TEXT",
	}

	sqliteDb *sql.DB
)

func ValidateSqlite(filename, tablename string, ignoreExistingTable bool) error {
	_, err := os.Stat(filename)
	if err != nil {
		return fmt.Errorf("Unable to access sqlite3 file: %w\n", err)
	}

	connStr := fmt.Sprintf("file:%s", filename)
	sqliteDb, err = sql.Open("sqlite3", connStr)
	if err != nil {
		return fmt.Errorf("unable to open Sqlite3 database: %w", err)
	}
	if err := sqliteDb.Ping(); err != nil {
		return fmt.Errorf("unable to ping Sqlite3 database: %w", err)
	}

	if !ignoreExistingTable {
		_, err = sqliteDb.Query(fmt.Sprintf("SELECT * FROM %s LIMIT 1", tablename))
		if err == nil {
			return fmt.Errorf("sqlite table %s already exists", tablename)
		}
	}

	return nil
}

func BuildCreateTableSQL(schema *TableSchema) (string, error) {
	newSchema := TableSchema{
		Name: schema.Name,
	}

	for _, col := range schema.Cols {
		if col.Ignored {
			continue
		}
		newType, err := mapColumnType(col.Type)
		if err != nil {
			return "", fmt.Errorf("error during column type mapping: %w", err)
		}
		newSchema.Cols = append(newSchema.Cols, TableColumn{Name: col.Name, Type: newType})
	}

	sqlTmpl := "CREATE TABLE " + schema.Name + " ( %s )"
	var colStrings []string
	for _, col := range newSchema.Cols {
		colStrings = append(colStrings, "\t"+col.Name+" "+col.Type)
	}
	sqlCreateString := fmt.Sprintf(sqlTmpl, strings.Join(colStrings, ", \n"))

	return sqlCreateString, nil
}

func mapColumnType(origType string) (string, error) {
	newType, ok := typeMapings[origType]
	if !ok {
		newType, ok = typeMapings["__other"]
		if !ok {
			return "", fmt.Errorf("type %s could not be mapped, this should not happen", origType)
		}
	}
	return newType, nil
}

func DropTable(tablename string) error {
	_, err := sqliteDb.Exec("DROP TABLE IF EXISTS " + tablename)
	if err != nil {
		return fmt.Errorf("unable to drop sqlite table: %w", err)
	}
	return nil
}

func CreateTable(stmt string) error {
	_, err := sqliteDb.Exec(stmt)
	if err != nil {
		return fmt.Errorf("unable to create sqlite table: %w", err)
	}

	return nil
}

func CloseSqlite() error {
	return sqliteDb.Close()
}

func InsertRow(tablename string, vals []interface{}) error {
	placeholder := strings.Join(strings.Split(strings.Repeat("?", len(vals)), ""), ", ")

	result, err := sqliteDb.Exec(fmt.Sprintf("INSERT INTO %s VALUES (%s)", tablename, placeholder), vals...)
	if err != nil {
		return err
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("unable to determine affected rows: %w", err)
	}
	if affected != 1 {
		return fmt.Errorf("no row affected, this should not happend")
	}

	return nil
}