128 lines
4.2 KiB
Go
128 lines
4.2 KiB
Go
package sqldir_test
|
|
|
|
import (
|
|
"fmt"
|
|
"log"
|
|
|
|
"git.warky.dev/wdevs/relspecgo/pkg/readers"
|
|
"git.warky.dev/wdevs/relspecgo/pkg/readers/sqldir"
|
|
"git.warky.dev/wdevs/relspecgo/pkg/writers"
|
|
"git.warky.dev/wdevs/relspecgo/pkg/writers/sqlexec"
|
|
)
|
|
|
|
// Example demonstrates how to read SQL scripts from a directory and execute them
|
|
func Example() {
|
|
// Step 1: Read SQL scripts from a directory
|
|
// Directory structure example:
|
|
// migrations/
|
|
// 1_001_create_schema.sql
|
|
// 1_002_create_users_table.sql
|
|
// 1_003_create_posts_table.pgsql
|
|
// 2_001_add_indexes.sql
|
|
// 2_002_seed_data.sql
|
|
|
|
reader := sqldir.NewReader(&readers.ReaderOptions{
|
|
FilePath: "/path/to/migrations",
|
|
Metadata: map[string]any{
|
|
"schema_name": "public",
|
|
"database_name": "myapp",
|
|
},
|
|
})
|
|
|
|
// Read the database schema with scripts
|
|
database, err := reader.ReadDatabase()
|
|
if err != nil {
|
|
log.Fatalf("Failed to read scripts: %v", err)
|
|
}
|
|
|
|
fmt.Printf("Read %d schemas\n", len(database.Schemas))
|
|
fmt.Printf("Found %d scripts in schema '%s'\n",
|
|
len(database.Schemas[0].Scripts),
|
|
database.Schemas[0].Name)
|
|
|
|
// Step 2: Execute the scripts against a PostgreSQL database
|
|
writer := sqlexec.NewWriter(&writers.WriterOptions{
|
|
Metadata: map[string]any{
|
|
"connection_string": "postgres://user:password@localhost:5432/myapp?sslmode=disable",
|
|
},
|
|
})
|
|
|
|
// Execute all scripts in Priority then Sequence order
|
|
if err := writer.WriteDatabase(database); err != nil {
|
|
log.Fatalf("Failed to execute scripts: %v", err)
|
|
}
|
|
|
|
fmt.Println("All scripts executed successfully!")
|
|
}
|
|
|
|
// Example_withSingleSchema shows how to read and execute scripts for a single schema
|
|
func Example_withSingleSchema() {
|
|
// Read scripts
|
|
reader := sqldir.NewReader(&readers.ReaderOptions{
|
|
FilePath: "/path/to/migrations",
|
|
})
|
|
|
|
schema, err := reader.ReadSchema()
|
|
if err != nil {
|
|
log.Fatalf("Failed to read schema: %v", err)
|
|
}
|
|
|
|
// Execute scripts
|
|
writer := sqlexec.NewWriter(&writers.WriterOptions{
|
|
Metadata: map[string]any{
|
|
"connection_string": "postgres://localhost/testdb",
|
|
},
|
|
})
|
|
|
|
if err := writer.WriteSchema(schema); err != nil {
|
|
log.Fatalf("Failed to execute scripts: %v", err)
|
|
}
|
|
|
|
fmt.Println("Schema scripts executed successfully!")
|
|
}
|
|
|
|
// Example_fileNamingConvention shows the expected file naming pattern
|
|
func Example_fileNamingConvention() {
|
|
// File naming pattern: {priority}_{sequence}_{name}.sql or .pgsql
|
|
// OR: {priority}-{sequence}-{name}.sql or .pgsql
|
|
//
|
|
// Both underscore (_) and hyphen (-) separators are supported and can be mixed.
|
|
//
|
|
// Components:
|
|
// - priority: Integer (0-9999) - Scripts with lower priority execute first
|
|
// - sequence: Integer (0-9999) - Within same priority, lower sequence executes first
|
|
// - separator: Underscore (_) or hyphen (-)
|
|
// - name: Descriptive name (alphanumeric, underscores, hyphens)
|
|
// - extension: .sql or .pgsql
|
|
//
|
|
// Examples (underscore format):
|
|
// ✓ 1_001_create_users.sql (Priority=1, Sequence=1)
|
|
// ✓ 1_002_create_posts.sql (Priority=1, Sequence=2)
|
|
// ✓ 2_001_add_indexes.pgsql (Priority=2, Sequence=1)
|
|
// ✓ 10_100_migration.sql (Priority=10, Sequence=100)
|
|
//
|
|
// Examples (hyphen format):
|
|
// ✓ 1-001-create-users.sql (Priority=1, Sequence=1)
|
|
// ✓ 1-002-create-posts.sql (Priority=1, Sequence=2)
|
|
// ✓ 2-001-add-indexes.pgsql (Priority=2, Sequence=1)
|
|
// ✓ 10-10-create-newid.pgsql (Priority=10, Sequence=10)
|
|
//
|
|
// Mixed format (both in same directory):
|
|
// ✓ 1_001_create_users.sql (underscore format)
|
|
// ✓ 1-002-create-posts.sql (hyphen format)
|
|
// ✓ 2_001_add_indexes.sql (underscore format)
|
|
//
|
|
// Execution order for mixed examples:
|
|
// 1. 1_001_create_users.sql (Priority 1, Sequence 1)
|
|
// 2. 1-002-create-posts.sql (Priority 1, Sequence 2)
|
|
// 3. 2_001_add_indexes.sql (Priority 2, Sequence 1)
|
|
//
|
|
// Invalid filenames (will be ignored):
|
|
// ✗ migration.sql (missing priority/sequence)
|
|
// ✗ 1_create_users.sql (missing sequence)
|
|
// ✗ create_users.sql (missing priority/sequence)
|
|
// ✗ 1_001_create_users.txt (wrong extension)
|
|
|
|
fmt.Println("See comments for file naming conventions")
|
|
}
|