dialect.go

v1.0.0

package store

import "fmt"

// dialect encapsulates SQL differences between database backends.
type dialect struct {
	driverName string

	migrationSQL string

	// Query templates.
	resolveModuleSQL string
	addModuleSQL     string
	deleteModuleSQL  string
	addPatternSQL    string
	deletePatternSQL string

	// Import queries (INSERT OR IGNORE equivalent).
	importModuleSQL  string
	importPatternSQL string

	// Sumdb queries.
	insertSumdbRecordSQL string
	insertSumdbHashSQL   string
	getRecordsSQL        string // SELECT ... WHERE id >= ? AND id < ?
	lookupRecordSQL      string // SELECT id FROM sumdb_records WHERE key = ?
	recordCountSQL       string
}

var sqliteDialect = dialect{
	driverName: "sqlite",

	migrationSQL: `
		CREATE TABLE IF NOT EXISTS modules (
			name       TEXT PRIMARY KEY,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);

		CREATE TABLE IF NOT EXISTS module_patterns (
			id         INTEGER PRIMARY KEY AUTOINCREMENT,
			pattern    TEXT NOT NULL UNIQUE,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			priority   INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);

		CREATE TABLE IF NOT EXISTS sumdb_records (
			id   INTEGER PRIMARY KEY,
			key  TEXT NOT NULL,
			data BLOB NOT NULL
		);

		CREATE TABLE IF NOT EXISTS sumdb_hashes (
			id   INTEGER PRIMARY KEY,
			hash BLOB NOT NULL
		);

		CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
	`,

	resolveModuleSQL: "SELECT vcs, repo, web, private FROM modules WHERE name = ?",
	addModuleSQL:     "INSERT INTO modules (name, vcs, repo, web, private) VALUES (?, ?, ?, ?, ?)",
	deleteModuleSQL:  "DELETE FROM modules WHERE name = ?",
	addPatternSQL:    "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES (?, ?, ?, ?, ?, ?)",
	deletePatternSQL: "DELETE FROM module_patterns WHERE id = ?",

	importModuleSQL:  "INSERT OR IGNORE INTO modules (name, vcs, repo, web, private) VALUES (?, ?, ?, ?, ?)",
	importPatternSQL: "INSERT OR IGNORE INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES (?, ?, ?, ?, ?, ?)",

	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES (?, ?, ?)",
	insertSumdbHashSQL:   "INSERT INTO sumdb_hashes (id, hash) VALUES (?, ?)",
	getRecordsSQL:        "SELECT data FROM sumdb_records WHERE id >= ? AND id < ? ORDER BY id",
	lookupRecordSQL:      "SELECT id FROM sumdb_records WHERE key = ?",
	recordCountSQL:       "SELECT COUNT(*) FROM sumdb_records",
}

var postgresDialect = dialect{
	driverName: "postgres",

	migrationSQL: `
		CREATE TABLE IF NOT EXISTS modules (
			name       TEXT PRIMARY KEY,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			created_at TIMESTAMP NOT NULL DEFAULT NOW()
		);

		CREATE TABLE IF NOT EXISTS module_patterns (
			id         BIGSERIAL PRIMARY KEY,
			pattern    TEXT NOT NULL UNIQUE,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			priority   INTEGER NOT NULL DEFAULT 0,
			created_at TIMESTAMP NOT NULL DEFAULT NOW()
		);

		CREATE TABLE IF NOT EXISTS sumdb_records (
			id   BIGINT PRIMARY KEY,
			key  TEXT NOT NULL,
			data BYTEA NOT NULL
		);

		CREATE TABLE IF NOT EXISTS sumdb_hashes (
			id   BIGINT PRIMARY KEY,
			hash BYTEA NOT NULL
		);

		CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
	`,

	resolveModuleSQL: "SELECT vcs, repo, web, private FROM modules WHERE name = $1",
	addModuleSQL:     "INSERT INTO modules (name, vcs, repo, web, private) VALUES ($1, $2, $3, $4, $5)",
	deleteModuleSQL:  "DELETE FROM modules WHERE name = $1",
	addPatternSQL:    "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES ($1, $2, $3, $4, $5, $6)",
	deletePatternSQL: "DELETE FROM module_patterns WHERE id = $1",

	importModuleSQL:  "INSERT INTO modules (name, vcs, repo, web, private) VALUES ($1, $2, $3, $4, $5) ON CONFLICT DO NOTHING",
	importPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING",

	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES ($1, $2, $3)",
	insertSumdbHashSQL:   "INSERT INTO sumdb_hashes (id, hash) VALUES ($1, $2)",
	getRecordsSQL:        "SELECT data FROM sumdb_records WHERE id >= $1 AND id < $2 ORDER BY id",
	lookupRecordSQL:      "SELECT id FROM sumdb_records WHERE key = $1",
	recordCountSQL:       "SELECT COUNT(*) FROM sumdb_records",
}

func dialectFor(driver string) (dialect, error) {
	switch driver {
	case "sqlite":
		return sqliteDialect, nil
	case "postgres":
		return postgresDialect, nil
	default:
		return dialect{}, fmt.Errorf("unsupported database driver: %q (use sqlite or postgres)", driver)
	}
}


1	package store
2
3	import "fmt"
4
5	// dialect encapsulates SQL differences between database backends.
6	type dialect struct {
7	driverName string
8
9	migrationSQL string
10
11	// Query templates.
12	resolveModuleSQL string
13	addModuleSQL string
14	deleteModuleSQL string
15	addPatternSQL string
16	deletePatternSQL string
17
18	// Import queries (INSERT OR IGNORE equivalent).
19	importModuleSQL string
20	importPatternSQL string
21
22	// Sumdb queries.
23	insertSumdbRecordSQL string
24	insertSumdbHashSQL string
25	getRecordsSQL string // SELECT ... WHERE id >= ? AND id < ?
26	lookupRecordSQL string // SELECT id FROM sumdb_records WHERE key = ?
27	recordCountSQL string
28	}
29
30	var sqliteDialect = dialect{
31	driverName: "sqlite",
32
33	migrationSQL: `
34	CREATE TABLE IF NOT EXISTS modules (
35	name TEXT PRIMARY KEY,
36	vcs TEXT NOT NULL DEFAULT 'git',
37	repo TEXT NOT NULL,
38	web TEXT NOT NULL DEFAULT '',
39	private INTEGER NOT NULL DEFAULT 0,
40	created_at TEXT NOT NULL DEFAULT (datetime('now'))
41	);
42
43	CREATE TABLE IF NOT EXISTS module_patterns (
44	id INTEGER PRIMARY KEY AUTOINCREMENT,
45	pattern TEXT NOT NULL UNIQUE,
46	vcs TEXT NOT NULL DEFAULT 'git',
47	repo TEXT NOT NULL,
48	web TEXT NOT NULL DEFAULT '',
49	private INTEGER NOT NULL DEFAULT 0,
50	priority INTEGER NOT NULL DEFAULT 0,
51	created_at TEXT NOT NULL DEFAULT (datetime('now'))
52	);
53
54	CREATE TABLE IF NOT EXISTS sumdb_records (
55	id INTEGER PRIMARY KEY,
56	key TEXT NOT NULL,
57	data BLOB NOT NULL
58	);
59
60	CREATE TABLE IF NOT EXISTS sumdb_hashes (
61	id INTEGER PRIMARY KEY,
62	hash BLOB NOT NULL
63	);
64
65	CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
66	`,
67
68	resolveModuleSQL: "SELECT vcs, repo, web, private FROM modules WHERE name = ?",
69	addModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private) VALUES (?, ?, ?, ?, ?)",
70	deleteModuleSQL: "DELETE FROM modules WHERE name = ?",
71	addPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES (?, ?, ?, ?, ?, ?)",
72	deletePatternSQL: "DELETE FROM module_patterns WHERE id = ?",
73
74	importModuleSQL: "INSERT OR IGNORE INTO modules (name, vcs, repo, web, private) VALUES (?, ?, ?, ?, ?)",
75	importPatternSQL: "INSERT OR IGNORE INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES (?, ?, ?, ?, ?, ?)",
76
77	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES (?, ?, ?)",
78	insertSumdbHashSQL: "INSERT INTO sumdb_hashes (id, hash) VALUES (?, ?)",
79	getRecordsSQL: "SELECT data FROM sumdb_records WHERE id >= ? AND id < ? ORDER BY id",
80	lookupRecordSQL: "SELECT id FROM sumdb_records WHERE key = ?",
81	recordCountSQL: "SELECT COUNT(*) FROM sumdb_records",
82	}
83
84	var postgresDialect = dialect{
85	driverName: "postgres",
86
87	migrationSQL: `
88	CREATE TABLE IF NOT EXISTS modules (
89	name TEXT PRIMARY KEY,
90	vcs TEXT NOT NULL DEFAULT 'git',
91	repo TEXT NOT NULL,
92	web TEXT NOT NULL DEFAULT '',
93	private INTEGER NOT NULL DEFAULT 0,
94	created_at TIMESTAMP NOT NULL DEFAULT NOW()
95	);
96
97	CREATE TABLE IF NOT EXISTS module_patterns (
98	id BIGSERIAL PRIMARY KEY,
99	pattern TEXT NOT NULL UNIQUE,
100	vcs TEXT NOT NULL DEFAULT 'git',
101	repo TEXT NOT NULL,
102	web TEXT NOT NULL DEFAULT '',
103	private INTEGER NOT NULL DEFAULT 0,
104	priority INTEGER NOT NULL DEFAULT 0,
105	created_at TIMESTAMP NOT NULL DEFAULT NOW()
106	);
107
108	CREATE TABLE IF NOT EXISTS sumdb_records (
109	id BIGINT PRIMARY KEY,
110	key TEXT NOT NULL,
111	data BYTEA NOT NULL
112	);
113
114	CREATE TABLE IF NOT EXISTS sumdb_hashes (
115	id BIGINT PRIMARY KEY,
116	hash BYTEA NOT NULL
117	);
118
119	CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
120	`,
121
122	resolveModuleSQL: "SELECT vcs, repo, web, private FROM modules WHERE name = $1",
123	addModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private) VALUES ($1, $2, $3, $4, $5)",
124	deleteModuleSQL: "DELETE FROM modules WHERE name = $1",
125	addPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES ($1, $2, $3, $4, $5, $6)",
126	deletePatternSQL: "DELETE FROM module_patterns WHERE id = $1",
127
128	importModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private) VALUES ($1, $2, $3, $4, $5) ON CONFLICT DO NOTHING",
129	importPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING",
130
131	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES ($1, $2, $3)",
132	insertSumdbHashSQL: "INSERT INTO sumdb_hashes (id, hash) VALUES ($1, $2)",
133	getRecordsSQL: "SELECT data FROM sumdb_records WHERE id >= $1 AND id < $2 ORDER BY id",
134	lookupRecordSQL: "SELECT id FROM sumdb_records WHERE key = $1",
135	recordCountSQL: "SELECT COUNT(*) FROM sumdb_records",
136	}
137
138	func dialectFor(driver string) (dialect, error) {
139	switch driver {
140	case "sqlite":
141	return sqliteDialect, nil
142	case "postgres":
143	return postgresDialect, nil
144	default:
145	return dialect{}, fmt.Errorf("unsupported database driver: %q (use sqlite or postgres)", driver)
146	}
147	}
148

dialect.go

Source Files