dialect.go

v1.2.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

	// Credential queries.
	addCredentialSQL    string
	updateCredentialSQL string
	deleteCredentialSQL string
	getCredentialSQL    string
	listCredentialsSQL  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 credentials (
			name       TEXT PRIMARY KEY,
			type       TEXT NOT NULL,
			data       TEXT NOT NULL,
			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, credential_name FROM modules WHERE name = ?",
	addModuleSQL:     "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
	deleteModuleSQL:  "DELETE FROM modules WHERE name = ?",
	addPatternSQL:    "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
	deletePatternSQL: "DELETE FROM module_patterns WHERE id = ?",

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

	addCredentialSQL:    "INSERT INTO credentials (name, type, data) VALUES (?, ?, ?)",
	updateCredentialSQL: "UPDATE credentials SET type = ?, data = ? WHERE name = ?",
	deleteCredentialSQL: "DELETE FROM credentials WHERE name = ?",
	getCredentialSQL:    "SELECT name, type, data, created_at FROM credentials WHERE name = ?",
	listCredentialsSQL:  "SELECT name, type, data, created_at FROM credentials ORDER BY name",

	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 credentials (
			name       TEXT PRIMARY KEY,
			type       TEXT NOT NULL,
			data       TEXT NOT NULL,
			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, credential_name FROM modules WHERE name = $1",
	addModuleSQL:     "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6)",
	deleteModuleSQL:  "DELETE FROM modules WHERE name = $1",
	addPatternSQL:    "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7)",
	deletePatternSQL: "DELETE FROM module_patterns WHERE id = $1",

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

	addCredentialSQL:    "INSERT INTO credentials (name, type, data) VALUES ($1, $2, $3)",
	updateCredentialSQL: "UPDATE credentials SET type = $1, data = $2 WHERE name = $3",
	deleteCredentialSQL: "DELETE FROM credentials WHERE name = $1",
	getCredentialSQL:    "SELECT name, type, data, created_at FROM credentials WHERE name = $1",
	listCredentialsSQL:  "SELECT name, type, data, created_at FROM credentials ORDER BY name",

	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	// Credential queries.
23	addCredentialSQL string
24	updateCredentialSQL string
25	deleteCredentialSQL string
26	getCredentialSQL string
27	listCredentialsSQL string
28
29	// Sumdb queries.
30	insertSumdbRecordSQL string
31	insertSumdbHashSQL string
32	getRecordsSQL string // SELECT ... WHERE id >= ? AND id < ?
33	lookupRecordSQL string // SELECT id FROM sumdb_records WHERE key = ?
34	recordCountSQL string
35	}
36
37	var sqliteDialect = dialect{
38	driverName: "sqlite",
39
40	migrationSQL: `
41	CREATE TABLE IF NOT EXISTS modules (
42	name TEXT PRIMARY KEY,
43	vcs TEXT NOT NULL DEFAULT 'git',
44	repo TEXT NOT NULL,
45	web TEXT NOT NULL DEFAULT '',
46	private INTEGER NOT NULL DEFAULT 0,
47	created_at TEXT NOT NULL DEFAULT (datetime('now'))
48	);
49
50	CREATE TABLE IF NOT EXISTS module_patterns (
51	id INTEGER PRIMARY KEY AUTOINCREMENT,
52	pattern TEXT NOT NULL UNIQUE,
53	vcs TEXT NOT NULL DEFAULT 'git',
54	repo TEXT NOT NULL,
55	web TEXT NOT NULL DEFAULT '',
56	private INTEGER NOT NULL DEFAULT 0,
57	priority INTEGER NOT NULL DEFAULT 0,
58	created_at TEXT NOT NULL DEFAULT (datetime('now'))
59	);
60
61	CREATE TABLE IF NOT EXISTS credentials (
62	name TEXT PRIMARY KEY,
63	type TEXT NOT NULL,
64	data TEXT NOT NULL,
65	created_at TEXT NOT NULL DEFAULT (datetime('now'))
66	);
67
68	CREATE TABLE IF NOT EXISTS sumdb_records (
69	id INTEGER PRIMARY KEY,
70	key TEXT NOT NULL,
71	data BLOB NOT NULL
72	);
73
74	CREATE TABLE IF NOT EXISTS sumdb_hashes (
75	id INTEGER PRIMARY KEY,
76	hash BLOB NOT NULL
77	);
78
79	CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
80	`,
81
82	resolveModuleSQL: "SELECT vcs, repo, web, private, credential_name FROM modules WHERE name = ?",
83	addModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
84	deleteModuleSQL: "DELETE FROM modules WHERE name = ?",
85	addPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
86	deletePatternSQL: "DELETE FROM module_patterns WHERE id = ?",
87
88	importModuleSQL: "INSERT OR IGNORE INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
89	importPatternSQL: "INSERT OR IGNORE INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
90
91	addCredentialSQL: "INSERT INTO credentials (name, type, data) VALUES (?, ?, ?)",
92	updateCredentialSQL: "UPDATE credentials SET type = ?, data = ? WHERE name = ?",
93	deleteCredentialSQL: "DELETE FROM credentials WHERE name = ?",
94	getCredentialSQL: "SELECT name, type, data, created_at FROM credentials WHERE name = ?",
95	listCredentialsSQL: "SELECT name, type, data, created_at FROM credentials ORDER BY name",
96
97	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES (?, ?, ?)",
98	insertSumdbHashSQL: "INSERT INTO sumdb_hashes (id, hash) VALUES (?, ?)",
99	getRecordsSQL: "SELECT data FROM sumdb_records WHERE id >= ? AND id < ? ORDER BY id",
100	lookupRecordSQL: "SELECT id FROM sumdb_records WHERE key = ?",
101	recordCountSQL: "SELECT COUNT(*) FROM sumdb_records",
102	}
103
104	var postgresDialect = dialect{
105	driverName: "postgres",
106
107	migrationSQL: `
108	CREATE TABLE IF NOT EXISTS modules (
109	name TEXT PRIMARY KEY,
110	vcs TEXT NOT NULL DEFAULT 'git',
111	repo TEXT NOT NULL,
112	web TEXT NOT NULL DEFAULT '',
113	private INTEGER NOT NULL DEFAULT 0,
114	created_at TIMESTAMP NOT NULL DEFAULT NOW()
115	);
116
117	CREATE TABLE IF NOT EXISTS module_patterns (
118	id BIGSERIAL PRIMARY KEY,
119	pattern TEXT NOT NULL UNIQUE,
120	vcs TEXT NOT NULL DEFAULT 'git',
121	repo TEXT NOT NULL,
122	web TEXT NOT NULL DEFAULT '',
123	private INTEGER NOT NULL DEFAULT 0,
124	priority INTEGER NOT NULL DEFAULT 0,
125	created_at TIMESTAMP NOT NULL DEFAULT NOW()
126	);
127
128	CREATE TABLE IF NOT EXISTS credentials (
129	name TEXT PRIMARY KEY,
130	type TEXT NOT NULL,
131	data TEXT NOT NULL,
132	created_at TIMESTAMP NOT NULL DEFAULT NOW()
133	);
134
135	CREATE TABLE IF NOT EXISTS sumdb_records (
136	id BIGINT PRIMARY KEY,
137	key TEXT NOT NULL,
138	data BYTEA NOT NULL
139	);
140
141	CREATE TABLE IF NOT EXISTS sumdb_hashes (
142	id BIGINT PRIMARY KEY,
143	hash BYTEA NOT NULL
144	);
145
146	CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
147	`,
148
149	resolveModuleSQL: "SELECT vcs, repo, web, private, credential_name FROM modules WHERE name = $1",
150	addModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6)",
151	deleteModuleSQL: "DELETE FROM modules WHERE name = $1",
152	addPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7)",
153	deletePatternSQL: "DELETE FROM module_patterns WHERE id = $1",
154
155	importModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING",
156	importPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT DO NOTHING",
157
158	addCredentialSQL: "INSERT INTO credentials (name, type, data) VALUES ($1, $2, $3)",
159	updateCredentialSQL: "UPDATE credentials SET type = $1, data = $2 WHERE name = $3",
160	deleteCredentialSQL: "DELETE FROM credentials WHERE name = $1",
161	getCredentialSQL: "SELECT name, type, data, created_at FROM credentials WHERE name = $1",
162	listCredentialsSQL: "SELECT name, type, data, created_at FROM credentials ORDER BY name",
163
164	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES ($1, $2, $3)",
165	insertSumdbHashSQL: "INSERT INTO sumdb_hashes (id, hash) VALUES ($1, $2)",
166	getRecordsSQL: "SELECT data FROM sumdb_records WHERE id >= $1 AND id < $2 ORDER BY id",
167	lookupRecordSQL: "SELECT id FROM sumdb_records WHERE key = $1",
168	recordCountSQL: "SELECT COUNT(*) FROM sumdb_records",
169	}
170
171	func dialectFor(driver string) (dialect, error) {
172	switch driver {
173	case "sqlite":
174	return sqliteDialect, nil
175	case "postgres":
176	return postgresDialect, nil
177	default:
178	return dialect{}, fmt.Errorf("unsupported database driver: %q (use sqlite or postgres)", driver)
179	}
180	}
181

dialect.go

Source Files