// Executes a file of SQL statements one statement at a time, stopping everything
// if one of them has an error
func main() {
// -f (filename) is a required program argument
var fileName = flag.StringP("file", "f", "", "path of the SQL file to run")
dbInfo := pgutil.DbInfo{}
verFlag, helpFlag := dbInfo.Populate()
if verFlag {
fmt.Fprintf(os.Stderr, "%s version %s\n", os.Args[0], version)
fmt.Fprintln(os.Stderr, "Copyright (c) 2016 Jon Carlson. All rights reserved.")
fmt.Fprintln(os.Stderr, "Use of this source code is governed by the MIT license")
fmt.Fprintln(os.Stderr, "that can be found here: http://opensource.org/licenses/MIT")
os.Exit(1)
}
if helpFlag {
usage()
}
if len(*fileName) == 0 {
fmt.Fprintln(os.Stderr, "Missing required filename argument (-f)")
usage()
}
exists, _ := fileutil.Exists(*fileName)
if !exists {
fmt.Fprintf(os.Stderr, "File does not exist: %s\n", *fileName)
os.Exit(2)
}
runFile(*fileName, &dbInfo)
}
// Reads the file and runs the SQL statements one by one
func runFile(fileName string, dbInfo *pgutil.DbInfo) {
// Open connection to the database
db, err := dbInfo.Open()
check("opening database", err)
// Read each statement from the file one at a time and execute them
sqlChan := sqlStatements(fileName)
for sql := range sqlChan {
// Execute SQL. If not successful, stop and ask user
// whether or not we should continue
fmt.Println("\n---")
log.Print("Executing SQL: ", sql)
runSql := true // Let's us loop for rerunning error
for runSql {
result, err := db.Exec(sql)
// If there was an error, ask user whether or not we should continue
if err != nil {
fmt.Fprintln(os.Stderr, "Error: ", err)
action := misc.ChooseOne("Continue, Quit, or Redo? Enter c, q, or r: ", "c", "q", "r")
// runSql has to be true here
if action == "c" {
runSql = false
}
if action == "q" {
os.Exit(1)
}
} else {
runSql = false
rowCnt, err := result.RowsAffected()
check("getting rows affected count", err)
log.Printf("Rows affected: %d\n", rowCnt)
}
}
}
log.Println("Done!")
}
/*
* Queries the given table name and copies the column values to either an INSERT statement or
* an UPDATE statement.
*
* Example: pgcp -U myuser -d mydb INSERT t_user "WHERE user_id < 4"
*/
func main() {
var outputFileName string
flag.StringVarP(&outputFileName, "output-file", "o", "", "Sends output to a file")
dbInfo := pgutil.DbInfo{}
verFlag, helpFlag := dbInfo.Populate()
if verFlag {
fmt.Fprintf(os.Stderr, "%s version %s\n", os.Args[0], version)
fmt.Fprintln(os.Stderr, "Copyright (c) 2015 Jon Carlson. All rights reserved.")
fmt.Fprintln(os.Stderr, "Use of this source code is governed by the MIT license")
fmt.Fprintln(os.Stderr, "that can be found here: http://opensource.org/licenses/MIT")
os.Exit(1)
}
if helpFlag {
usage()
}
// Remaining args:
args := flag.Args()
if len(args) < 2 {
fmt.Fprintln(os.Stderr, "Missing some arguments")
usage()
}
// genType
genType := strings.ToUpper(args[0])
if genType != "INSERT" && genType != "UPDATE" {
fmt.Fprintf(os.Stderr, "Invalid generation type: %s. Requires either INSERT or UPDATE\n", genType)
usage()
}
if len(outputFileName) > 0 {
var err error
out, err = os.Create(outputFileName)
if err != nil {
fmt.Fprintf(os.Stderr, "Cannot open file: %s. \n", outputFileName)
fmt.Fprintln(os.Stderr, err)
os.Exit(2)
}
}
// tableName
tableName := args[1]
// idColumn (UPDATE only) and whereClause
whereClause := ""
idCol := ""
if genType == "INSERT" {
if len(args) > 2 {
whereClause = args[2]
}
} else {
if len(args) < 3 {
fmt.Fprintf(os.Stderr, "UPDATE requires an idColumn.")
usage()
}
idCol = args[2]
if len(args) > 3 {
whereClause = args[3]
}
}
if len(whereClause) == 0 {
// Make sure user intended there to be no where clause
if !misc.PromptYesNo("Did you intend to have no where clause?", true) {
os.Exit(1)
}
}
db, err := dbInfo.Open()
check("opening database", err)
query := "SELECT * FROM " + tableName + " " + whereClause
printf("-- Creating %s(s) from query: %s\n", genType, query)
rowChan, columnNames := querySqlValues(db, query)
for row := range rowChan {
if genType == "INSERT" {
generateInsert(tableName, row, columnNames)
} else {
generateUpdate(tableName, row, idCol)
}
}
}