From fa72f449465905ff2fc9b79886cb5e4b948a8fa8 Mon Sep 17 00:00:00 2001 From: Michael Hunteman Date: Fri, 24 May 2024 10:44:46 -0700 Subject: Connect server to database --- server/.bashrc | 1 - server/cmd/main.go | 44 ++++++++++++++++---------- server/go.mod | 11 +++++++ server/go.sum | 28 +++++++++++++++++ server/guests/store.go | 84 ++++++++++++++++++++++++++++++++++++++++++++------ server/post.json | 4 +-- server/schema.sql | 15 +++++++++ 7 files changed, 157 insertions(+), 30 deletions(-) delete mode 100644 server/.bashrc create mode 100644 server/go.sum create mode 100644 server/schema.sql (limited to 'server') diff --git a/server/.bashrc b/server/.bashrc deleted file mode 100644 index 802d935..0000000 --- a/server/.bashrc +++ /dev/null @@ -1 +0,0 @@ -PATH=$PATH:/c/Go/bin diff --git a/server/cmd/main.go b/server/cmd/main.go index 3cd118d..f886e2b 100644 --- a/server/cmd/main.go +++ b/server/cmd/main.go @@ -1,11 +1,15 @@ package main import ( + "context" "encoding/json" + "fmt" "log" "net/http" + "os" "regexp" - "strconv" + + "github.com/jackc/pgx/v5/pgxpool" "git.huntm.net/wedding/server/guests" ) @@ -15,12 +19,18 @@ type guestHandler struct { } type guestStore interface { - Get() (map[int]guests.Guest, error) - Add(id int, guest guests.Guest) error - Update(id int, guest guests.Guest) error + Get() ([]guests.Guest, error) + Add(guest guests.Guest) error + Update(guest guests.Guest) error } var ( + user = os.Getenv("USER") + pass = os.Getenv("PASS") + host = "localhost" + port = "5432" + database = "postgres" + guestRe = regexp.MustCompile(`^/guests/*$`) guestIdRe = regexp.MustCompile(`^/guests/([0-9]+)$`) ) @@ -51,26 +61,26 @@ func (h *guestHandler) getGuests(w http.ResponseWriter, _ *http.Request) { func (h *guestHandler) createGuest(w http.ResponseWriter, r *http.Request) { var guest guests.Guest err := json.NewDecoder(r.Body).Decode(&guest) - defer r.Body.Close() if err != nil { http.Error(w, err.Error(), http.StatusBadRequest) return } + defer r.Body.Close() - guests, err := h.store.Get() + guestSlice, err := h.store.Get() if err != nil { http.Error(w, err.Error(), http.StatusBadRequest) return } - for _, g := range guests { + for _, g := range guestSlice { if g.Id == guest.Id { http.Error(w, "Id already exists", http.StatusBadRequest) return } } - err = h.store.Add(guest.Id, guest) + err = h.store.Add(guest) if err != nil { http.Error(w, err.Error(), http.StatusBadRequest) return @@ -88,19 +98,13 @@ func (h *guestHandler) updateGuest(w http.ResponseWriter, r *http.Request) { var guest guests.Guest err := json.NewDecoder(r.Body).Decode(&guest) - defer r.Body.Close() if err != nil { http.Error(w, err.Error(), http.StatusBadRequest) return } + defer r.Body.Close() - id, err := strconv.Atoi(matches[1]) - if err != nil { - http.Error(w, "Cannot convert string to integer", http.StatusBadRequest) - return - } - - err = h.store.Update(id, guest) + err = h.store.Update(guest) if err != nil { http.Error(w, "Guest not found", http.StatusBadRequest) return @@ -121,7 +125,13 @@ func (h *guestHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) { } func main() { - store := guests.NewMemStore() + db, err := pgxpool.New(context.Background(), fmt.Sprintf("postgres://%s:%s@%s:%s/%s", user, pass, host, port, database)) + if err != nil { + log.Fatal(err) + } + defer db.Close() + + store := guests.NewMemStore(db) guestHandler := newGuestHandler(store) mux := http.NewServeMux() diff --git a/server/go.mod b/server/go.mod index 737eac4..3ab08ef 100644 --- a/server/go.mod +++ b/server/go.mod @@ -1,3 +1,14 @@ module git.huntm.net/wedding/server go 1.22.2 + +require github.com/jackc/pgx/v5 v5.5.5 + +require ( + github.com/jackc/pgpassfile v1.0.0 // indirect + github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect + github.com/jackc/puddle/v2 v2.2.1 // indirect + golang.org/x/crypto v0.23.0 // indirect + golang.org/x/sync v0.1.0 // indirect + golang.org/x/text v0.15.0 // indirect +) diff --git a/server/go.sum b/server/go.sum new file mode 100644 index 0000000..23b6653 --- /dev/null +++ b/server/go.sum @@ -0,0 +1,28 @@ +github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= +github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM= +github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg= +github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a h1:bbPeKD0xmW/Y25WS6cokEszi5g+S0QxI/d45PkRi7Nk= +github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= +github.com/jackc/pgx/v5 v5.5.5 h1:amBjrZVmksIdNjxGW/IiIMzxMKZFelXbUoPNb+8sjQw= +github.com/jackc/pgx/v5 v5.5.5/go.mod h1:ez9gk+OAat140fv9ErkZDYFWmXLfV+++K0uAOiwgm1A= +github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk= +github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= +github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= +github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= +github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= +github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= +github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= +github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk= +github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4= +golang.org/x/crypto v0.23.0 h1:dIJU/v2J8Mdglj/8rJ6UUOM3Zc9zLZxVZwwxMooUSAI= +golang.org/x/crypto v0.23.0/go.mod h1:CKFgDieR+mRhux2Lsu27y0fO304Db0wZe70UKqHu0v8= +golang.org/x/sync v0.1.0 h1:wsuoTGHzEhffawBOhz5CYhcrV4IdKZbEyZjBMuTp12o= +golang.org/x/sync v0.1.0/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM= +golang.org/x/text v0.15.0 h1:h1V/4gjBv8v9cjcR6+AR5+/cIYK5N/WAgiv4xlsEtAk= +golang.org/x/text v0.15.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU= +gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= +gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= +gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= +gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= diff --git a/server/guests/store.go b/server/guests/store.go index f1d8558..597cc80 100644 --- a/server/guests/store.go +++ b/server/guests/store.go @@ -1,26 +1,90 @@ package guests +import ( + "context" + + "github.com/jackc/pgx/v5/pgxpool" +) + type MemStore struct { - guestMap map[int]Guest + db *pgxpool.Pool } -func NewMemStore() *MemStore { - guestMap := make(map[int]Guest) +func NewMemStore(db *pgxpool.Pool) *MemStore { return &MemStore{ - guestMap, + db, } } -func (m MemStore) Get() (map[int]Guest, error) { - return m.guestMap, nil +func (m MemStore) Get() ([]Guest, error) { + rows, err := m.db.Query(context.Background(), "select * from guest") + if err != nil { + return nil, err + } + defer rows.Close() + + guestSlice := []Guest{} + for rows.Next() { + var guest Guest + err := rows.Scan(&guest.Id, &guest.FirstName, &guest.LastName, &guest.Attendance, &guest.Email, &guest.Message, &guest.PartySize) + if err != nil { + return guestSlice, err + } + guestSlice = append(guestSlice, guest) + } + + rows, err = m.db.Query(context.Background(), "select * from party") + if err != nil { + return guestSlice, err + } + defer rows.Close() + + for rows.Next() { + var guestId int + var partyGuest PartyGuest + err := rows.Scan(&guestId, &partyGuest.FirstName, &partyGuest.LastName) + if err != nil { + return guestSlice, err + } + for i, g := range guestSlice { + if guestId == g.Id { + guestSlice[i].PartyList = append(g.PartyList, partyGuest) + } + } + } + return guestSlice, nil } -func (m MemStore) Add(id int, guest Guest) error { - m.guestMap[id] = guest +func (m MemStore) Add(guest Guest) error { + statement := "insert into guest (id, first_name, last_name, attendance, email, message, party_size) values ($1, $2, $3, $4, $5, $6, $7)" + _, err := m.db.Exec(context.Background(), statement, guest.Id, guest.FirstName, guest.LastName, guest.Attendance, guest.Email, guest.Message, guest.PartySize) + if err != nil { + return err + } + + statement = "insert into party (guest_id, first_name, last_name) values ($1, $2, $3)" + for _, pg := range guest.PartyList { + _, err = m.db.Exec(context.Background(), statement, guest.Id, pg.FirstName, pg.LastName) + if err != nil { + return err + } + } return nil } -func (m MemStore) Update(id int, guest Guest) error { - m.guestMap[id] = guest +func (m MemStore) Update(guest Guest) error { + statement := "update guest set attendance = $1, email = $2, message = $3, party_size = $4 where id = $5" + _, err := m.db.Exec(context.Background(), statement, guest.Attendance, guest.Email, guest.Message, guest.PartySize, guest.Id) + if err != nil { + return err + } + + statement = "update party set first_name = $1, last_name = $2 where guest_id = $3" + for _, pg := range guest.PartyList { + _, err = m.db.Exec(context.Background(), statement, pg.FirstName, pg.LastName, guest.Id) + if err != nil { + return err + } + } return nil } diff --git a/server/post.json b/server/post.json index f550b1b..39e1d55 100644 --- a/server/post.json +++ b/server/post.json @@ -2,10 +2,10 @@ "id": 1, "firstName": "Michael", "lastName": "Hunteman", - "attendance": "true", + "attendance": "yes", "email": "mhunteman@cox.net", "message": "Hi", - "partySize": 1, + "partySize": 2, "partyList": [ { "firstName": "Madison", diff --git a/server/schema.sql b/server/schema.sql new file mode 100644 index 0000000..b2dd533 --- /dev/null +++ b/server/schema.sql @@ -0,0 +1,15 @@ +create table guest ( + id serial PRIMARY KEY, + first_name varchar(64), + last_name varchar(64), + attendance varchar(8) DEFAULT 'no' NOT NULL, + email varchar(64), + message varchar(128), + party_size integer +); + +create table party ( + guest_id integer NOT NULL references guests(id) ON DELETE CASCADE, + first_name varchar(64), + last_name varchar(64) +); \ No newline at end of file -- cgit v1.2.3