diff options
Diffstat (limited to 'vendor/github.com/jackc/pgx/examples')
9 files changed, 500 insertions, 0 deletions
diff --git a/vendor/github.com/jackc/pgx/examples/README.md b/vendor/github.com/jackc/pgx/examples/README.md new file mode 100644 index 0000000..6a97bc0 --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/README.md @@ -0,0 +1,7 @@ +# Examples + +* chat is a command line chat program using listen/notify. +* todo is a command line todo list that demonstrates basic CRUD actions. +* url_shortener contains a simple example of using pgx in a web context. +* [Tern](https://github.com/jackc/tern) is a migration tool that uses pgx (uses v1 of pgx). +* [The Pithy Reader](https://github.com/jackc/tpr) is a RSS aggregator that uses pgx. diff --git a/vendor/github.com/jackc/pgx/examples/chat/README.md b/vendor/github.com/jackc/pgx/examples/chat/README.md new file mode 100644 index 0000000..a093525 --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/chat/README.md @@ -0,0 +1,25 @@ +# Description + +This is a sample chat program implemented using PostgreSQL's listen/notify +functionality with pgx. + +Start multiple instances of this program connected to the same database to chat +between them. + +## Connection configuration + +The database connection is configured via enviroment variables. + +* CHAT_DB_HOST - defaults to localhost +* CHAT_DB_USER - defaults to current OS user +* CHAT_DB_PASSWORD - defaults to empty string +* CHAT_DB_DATABASE - defaults to postgres + +You can either export them then run chat: + + export CHAT_DB_HOST=/private/tmp + ./chat + +Or you can prefix the chat execution with the environment variables: + + CHAT_DB_HOST=/private/tmp ./chat diff --git a/vendor/github.com/jackc/pgx/examples/chat/main.go b/vendor/github.com/jackc/pgx/examples/chat/main.go new file mode 100644 index 0000000..517508c --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/chat/main.go @@ -0,0 +1,95 @@ +package main + +import ( + "bufio" + "fmt" + "github.com/jackc/pgx" + "os" + "time" +) + +var pool *pgx.ConnPool + +func main() { + var err error + pool, err = pgx.NewConnPool(extractConfig()) + if err != nil { + fmt.Fprintln(os.Stderr, "Unable to connect to database:", err) + os.Exit(1) + } + + go listen() + + fmt.Println(`Type a message and press enter. + +This message should appear in any other chat instances connected to the same +database. + +Type "exit" to quit. +`) + + scanner := bufio.NewScanner(os.Stdin) + for scanner.Scan() { + msg := scanner.Text() + if msg == "exit" { + os.Exit(0) + } + + _, err = pool.Exec("select pg_notify('chat', $1)", msg) + if err != nil { + fmt.Fprintln(os.Stderr, "Error sending notification:", err) + os.Exit(1) + } + } + if err := scanner.Err(); err != nil { + fmt.Fprintln(os.Stderr, "Error scanning from stdin:", err) + os.Exit(1) + } +} + +func listen() { + conn, err := pool.Acquire() + if err != nil { + fmt.Fprintln(os.Stderr, "Error acquiring connection:", err) + os.Exit(1) + } + defer pool.Release(conn) + + conn.Listen("chat") + + for { + notification, err := conn.WaitForNotification(time.Second) + if err == pgx.ErrNotificationTimeout { + continue + } + if err != nil { + fmt.Fprintln(os.Stderr, "Error waiting for notification:", err) + os.Exit(1) + } + + fmt.Println("PID:", notification.Pid, "Channel:", notification.Channel, "Payload:", notification.Payload) + } +} + +func extractConfig() pgx.ConnPoolConfig { + var config pgx.ConnPoolConfig + + config.Host = os.Getenv("CHAT_DB_HOST") + if config.Host == "" { + config.Host = "localhost" + } + + config.User = os.Getenv("CHAT_DB_USER") + if config.User == "" { + config.User = os.Getenv("USER") + } + + config.Password = os.Getenv("CHAT_DB_PASSWORD") + + config.Database = os.Getenv("CHAT_DB_DATABASE") + if config.Database == "" { + config.Database = "postgres" + } + + return config +} diff --git a/vendor/github.com/jackc/pgx/examples/todo/README.md b/vendor/github.com/jackc/pgx/examples/todo/README.md new file mode 100644 index 0000000..eb3d95b --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/todo/README.md @@ -0,0 +1,72 @@ +# Description + +This is a sample todo list implemented using pgx as the connector to a +PostgreSQL data store. + +# Usage + +Create a PostgreSQL database and run structure.sql into it to create the +necessary data schema. + +Example: + + createdb todo + psql todo < structure.sql + +Build todo: + + go build + +## Connection configuration + +The database connection is configured via enviroment variables. + +* TODO_DB_HOST - defaults to localhost +* TODO_DB_USER - defaults to current OS user +* TODO_DB_PASSWORD - defaults to empty string +* TODO_DB_DATABASE - defaults to todo + +You can either export them then run todo: + + export TODO_DB_HOST=/private/tmp + ./todo list + +Or you can prefix the todo execution with the environment variables: + + TODO_DB_HOST=/private/tmp ./todo list + +## Add a todo item + + ./todo add 'Learn go' + +## List tasks + + ./todo list + +## Update a task + + ./todo add 1 'Learn more go' + +## Delete a task + + ./todo remove 1 + +# Example Setup and Execution + + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ createdb todo + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ psql todo < structure.sql + Expanded display is used automatically. + Timing is on. + CREATE TABLE + Time: 6.363 ms + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ go build + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ export TODO_DB_HOST=/private/tmp + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo add 'Learn Go' + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list + 1. Learn Go + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo update 1 'Learn more Go' + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list + 1. Learn more Go + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo remove 1 + jack@hk-47~/dev/go/src/github.com/jackc/pgx/examples/todo$ ./todo list diff --git a/vendor/github.com/jackc/pgx/examples/todo/main.go b/vendor/github.com/jackc/pgx/examples/todo/main.go new file mode 100644 index 0000000..bacdf24 --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/todo/main.go @@ -0,0 +1,140 @@ +package main + +import ( + "fmt" + "github.com/jackc/pgx" + "os" + "strconv" +) + +var conn *pgx.Conn + +func main() { + var err error + conn, err = pgx.Connect(extractConfig()) + if err != nil { + fmt.Fprintf(os.Stderr, "Unable to connection to database: %v\n", err) + os.Exit(1) + } + + if len(os.Args) == 1 { + printHelp() + os.Exit(0) + } + + switch os.Args[1] { + case "list": + err = listTasks() + if err != nil { + fmt.Fprintf(os.Stderr, "Unable to list tasks: %v\n", err) + os.Exit(1) + } + + case "add": + err = addTask(os.Args[2]) + if err != nil { + fmt.Fprintf(os.Stderr, "Unable to add task: %v\n", err) + os.Exit(1) + } + + case "update": + n, err := strconv.ParseInt(os.Args[2], 10, 32) + if err != nil { + fmt.Fprintf(os.Stderr, "Unable convert task_num into int32: %v\n", err) + os.Exit(1) + } + err = updateTask(int32(n), os.Args[3]) + if err != nil { + fmt.Fprintf(os.Stderr, "Unable to update task: %v\n", err) + os.Exit(1) + } + + case "remove": + n, err := strconv.ParseInt(os.Args[2], 10, 32) + if err != nil { + fmt.Fprintf(os.Stderr, "Unable convert task_num into int32: %v\n", err) + os.Exit(1) + } + err = removeTask(int32(n)) + if err != nil { + fmt.Fprintf(os.Stderr, "Unable to remove task: %v\n", err) + os.Exit(1) + } + + default: + fmt.Fprintln(os.Stderr, "Invalid command") + printHelp() + os.Exit(1) + } +} + +func listTasks() error { + rows, _ := conn.Query("select * from tasks") + + for rows.Next() { + var id int32 + var description string + err := rows.Scan(&id, &description) + if err != nil { + return err + } + fmt.Printf("%d. %s\n", id, description) + } + + return rows.Err() +} + +func addTask(description string) error { + _, err := conn.Exec("insert into tasks(description) values($1)", description) + return err +} + +func updateTask(itemNum int32, description string) error { + _, err := conn.Exec("update tasks set description=$1 where id=$2", description, itemNum) + return err +} + +func removeTask(itemNum int32) error { + _, err := conn.Exec("delete from tasks where id=$1", itemNum) + return err +} + +func printHelp() { + fmt.Print(`Todo pgx demo + +Usage: + + todo list + todo add task + todo update task_num item + todo remove task_num + +Example: + + todo add 'Learn Go' + todo list +`) +} + +func extractConfig() pgx.ConnConfig { + var config pgx.ConnConfig + + config.Host = os.Getenv("TODO_DB_HOST") + if config.Host == "" { + config.Host = "localhost" + } + + config.User = os.Getenv("TODO_DB_USER") + if config.User == "" { + config.User = os.Getenv("USER") + } + + config.Password = os.Getenv("TODO_DB_PASSWORD") + + config.Database = os.Getenv("TODO_DB_DATABASE") + if config.Database == "" { + config.Database = "todo" + } + + return config +} diff --git a/vendor/github.com/jackc/pgx/examples/todo/structure.sql b/vendor/github.com/jackc/pgx/examples/todo/structure.sql new file mode 100644 index 0000000..567685d --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/todo/structure.sql @@ -0,0 +1,4 @@ +create table tasks ( + id serial primary key, + description text not null +); diff --git a/vendor/github.com/jackc/pgx/examples/url_shortener/README.md b/vendor/github.com/jackc/pgx/examples/url_shortener/README.md new file mode 100644 index 0000000..cc04d60 --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/url_shortener/README.md @@ -0,0 +1,25 @@ +# Description + +This is a sample REST URL shortener service implemented using pgx as the connector to a PostgreSQL data store. + +# Usage + +Create a PostgreSQL database and run structure.sql into it to create the necessary data schema. + +Edit connectionOptions in main.go with the location and credentials for your database. + +Run main.go: + + go run main.go + +## Create or Update a Shortened URL + + curl -X PUT -d 'http://www.google.com' http://localhost:8080/google + +## Get a Shortened URL + + curl http://localhost:8080/google + +## Delete a Shortened URL + + curl -X DELETE http://localhost:8080/google diff --git a/vendor/github.com/jackc/pgx/examples/url_shortener/main.go b/vendor/github.com/jackc/pgx/examples/url_shortener/main.go new file mode 100644 index 0000000..f6a22c3 --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/url_shortener/main.go @@ -0,0 +1,128 @@ +package main + +import ( + "github.com/jackc/pgx" + log "gopkg.in/inconshreveable/log15.v2" + "io/ioutil" + "net/http" + "os" +) + +var pool *pgx.ConnPool + +// afterConnect creates the prepared statements that this application uses +func afterConnect(conn *pgx.Conn) (err error) { + _, err = conn.Prepare("getUrl", ` + select url from shortened_urls where id=$1 + `) + if err != nil { + return + } + + _, err = conn.Prepare("deleteUrl", ` + delete from shortened_urls where id=$1 + `) + if err != nil { + return + } + + // There technically is a small race condition in doing an upsert with a CTE + // where one of two simultaneous requests to the shortened URL would fail + // with a unique index violation. As the point of this demo is pgx usage and + // not how to perfectly upsert in PostgreSQL it is deemed acceptable. + _, err = conn.Prepare("putUrl", ` + with upsert as ( + update shortened_urls + set url=$2 + where id=$1 + returning * + ) + insert into shortened_urls(id, url) + select $1, $2 where not exists(select 1 from upsert) + `) + return +} + +func getUrlHandler(w http.ResponseWriter, req *http.Request) { + var url string + err := pool.QueryRow("getUrl", req.URL.Path).Scan(&url) + switch err { + case nil: + http.Redirect(w, req, url, http.StatusSeeOther) + case pgx.ErrNoRows: + http.NotFound(w, req) + default: + http.Error(w, "Internal server error", http.StatusInternalServerError) + } +} + +func putUrlHandler(w http.ResponseWriter, req *http.Request) { + id := req.URL.Path + var url string + if body, err := ioutil.ReadAll(req.Body); err == nil { + url = string(body) + } else { + http.Error(w, "Internal server error", http.StatusInternalServerError) + return + } + + if _, err := pool.Exec("putUrl", id, url); err == nil { + w.WriteHeader(http.StatusOK) + } else { + http.Error(w, "Internal server error", http.StatusInternalServerError) + } +} + +func deleteUrlHandler(w http.ResponseWriter, req *http.Request) { + if _, err := pool.Exec("deleteUrl", req.URL.Path); err == nil { + w.WriteHeader(http.StatusOK) + } else { + http.Error(w, "Internal server error", http.StatusInternalServerError) + } +} + +func urlHandler(w http.ResponseWriter, req *http.Request) { + switch req.Method { + case "GET": + getUrlHandler(w, req) + + case "PUT": + putUrlHandler(w, req) + + case "DELETE": + deleteUrlHandler(w, req) + + default: + w.Header().Add("Allow", "GET, PUT, DELETE") + w.WriteHeader(http.StatusMethodNotAllowed) + } +} + +func main() { + var err error + connPoolConfig := pgx.ConnPoolConfig{ + ConnConfig: pgx.ConnConfig{ + Host: "127.0.0.1", + User: "jack", + Password: "jack", + Database: "url_shortener", + Logger: log.New("module", "pgx"), + }, + MaxConnections: 5, + AfterConnect: afterConnect, + } + pool, err = pgx.NewConnPool(connPoolConfig) + if err != nil { + log.Crit("Unable to create connection pool", "error", err) + os.Exit(1) + } + + http.HandleFunc("/", urlHandler) + + log.Info("Starting URL shortener on localhost:8080") + err = http.ListenAndServe("localhost:8080", nil) + if err != nil { + log.Crit("Unable to start web server", "error", err) + os.Exit(1) + } +} diff --git a/vendor/github.com/jackc/pgx/examples/url_shortener/structure.sql b/vendor/github.com/jackc/pgx/examples/url_shortener/structure.sql new file mode 100644 index 0000000..0b9de25 --- /dev/null +++ b/vendor/github.com/jackc/pgx/examples/url_shortener/structure.sql @@ -0,0 +1,4 @@ +create table shortened_urls ( + id text primary key, + url text not null +);
\ No newline at end of file |