~louis77/pg2sqlite

ref: 400f42498cbc785d17eb26a3203842ff495b37ad pg2sqlite/sqlite.go -rw-r--r-- 4.3 KiB
400f4249 — Louis Brauer Update README 4 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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
/*	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"
	"log"
	"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
}

func CountRows(tablename string) (uint64, error) {
	rows, err := sqliteDb.Query(fmt.Sprintf("SELECT COUNT(*) FROM %s", tablename))
	if err != nil {
		return 0, err
	}

	defer func(rows *sql.Rows) {
		err := rows.Close()
		if err != nil {
			log.Println(err)
		}
	}(rows)

	if !rows.Next() {
		return 0, fmt.Errorf("unable to count rows, no estimate returned")
	}

	var rowcount uint64
	if err := rows.Scan(&rowcount); err != nil {
		return 0, fmt.Errorf("unable to count rows: %w", err)
	}

	return rowcount, nil
}