Colin Henry
b4529e018f
Architecture Changes: - Split dialect implementations into separate files for better organization - Move SQLite dialect from dialect.go to sqlite.go - Keep only Dialect interface in dialect.go with comprehensive documentation - Each dialect now in its own file following single responsibility principle New Features: - Add PostgreSQL dialect support (Postgres() function) - PostgreSQL uses SERIAL PRIMARY KEY (auto-incrementing integer) - PostgreSQL uses $1, $2 placeholders instead of ? for parameters - PostgreSQL uses SELECT 1 for table existence check (more efficient) - Both dialects implement proper SQL identifier quoting for security Testing: - Add comprehensive dialect-specific tests in sqlite_test.go - Add comprehensive dialect-specific tests in postgres_test.go - Test SQL generation for all dialect methods - Test SQL injection protection via identifier escaping - All tests pass (8 test functions, 10 subtests) Documentation: - Update README with PostgreSQL usage example - Add "Supported Databases" section listing SQLite and PostgreSQL - Improve code examples with proper imports and error handling - Document how to implement Dialect interface for other databases File Structure: - dialect.go: Interface definition only (18 lines) - sqlite.go: SQLite dialect implementation (39 lines) - postgres.go: PostgreSQL dialect implementation (42 lines) - sqlite_test.go: SQLite dialect tests (67 lines) - postgres_test.go: PostgreSQL dialect tests (67 lines) Security: - Both dialects use quoteIdentifier() for SQL injection protection - Identifiers are quoted and internal quotes are escaped - Follows SQL standard quoting mechanism (double quotes for escaping) This change maintains backward compatibility while adding PostgreSQL support and improving code organization for future dialect additions.
43 lines
1.3 KiB
Go
43 lines
1.3 KiB
Go
package migrate
|
|
|
|
import (
|
|
"fmt"
|
|
"strings"
|
|
)
|
|
|
|
// Postgres returns a PostgreSQL dialect implementation
|
|
func Postgres() Dialect {
|
|
return postgres{}
|
|
}
|
|
|
|
type postgres struct{}
|
|
|
|
// quoteIdentifier safely quotes a SQL identifier to prevent SQL injection
|
|
// PostgreSQL uses double quotes for identifiers and doubles them for escaping
|
|
func (p postgres) quoteIdentifier(identifier string) string {
|
|
// Replace any existing quotes with double quotes (SQL escape mechanism)
|
|
escaped := strings.ReplaceAll(identifier, `"`, `""`)
|
|
return fmt.Sprintf(`"%s"`, escaped)
|
|
}
|
|
|
|
func (p postgres) CreateTable(table string) string {
|
|
return fmt.Sprintf(`CREATE TABLE %s (
|
|
id SERIAL PRIMARY KEY,
|
|
description VARCHAR,
|
|
applied TIMESTAMP);`, p.quoteIdentifier(table))
|
|
}
|
|
|
|
func (p postgres) TableExists(table string) string {
|
|
// PostgreSQL-specific way to check if table exists
|
|
return fmt.Sprintf("SELECT 1 FROM %s LIMIT 1;", p.quoteIdentifier(table))
|
|
}
|
|
|
|
func (p postgres) CheckVersion(table string) string {
|
|
return fmt.Sprintf("SELECT id FROM %s ORDER BY id DESC LIMIT 1;", p.quoteIdentifier(table))
|
|
}
|
|
|
|
func (p postgres) InsertVersion(table string) string {
|
|
// PostgreSQL uses $1, $2 for placeholders instead of ?
|
|
return fmt.Sprintf("INSERT INTO %s(description, applied) VALUES ($1, $2);", p.quoteIdentifier(table))
|
|
}
|