auth

Paddy 2015-03-21 Child:8267e1c8bcd1

148:06fb735031bb Go to Latest

auth/profile_postgres.go

Do a first, naive pass at storing profiles in Postgres. This is untested against an actual database. It's a best-guess attempt at SQL. It _should_ work. I think. Start storing things in Postgres, starting with Profiles and Logins. This necessitates the addition of a Deleted property to the Profile type, because I'm not deleting those in case of accidental deletion. Logins, though, we'll delete. This also necessitates updating the profileStore interface to no longer have a deleteProfile method, because we're tracking that through updates now. Then we need to update our profileStore tests, because they no longer clean up after themselves. Which, come to think of it, may cause some problems later.

Download raw file
View source Diff to previous Annotate
History
paddy@148 1 package auth
paddy@148 2
paddy@148 3 import (
paddy@148 4 "time"
paddy@148 5
paddy@148 6 "code.secondbit.org/uuid.hg"
paddy@148 7 "github.com/secondbit/pan"
paddy@148 8 )
paddy@148 9
paddy@148 10 func (p Profile) GetSQLTableName() string {
paddy@148 11 return "profiles"
paddy@148 12 }
paddy@148 13
paddy@148 14 func (l Login) GetSQLTableName() string {
paddy@148 15 return "logins"
paddy@148 16 }
paddy@148 17
paddy@148 18 func (p *postgres) getProfileByIDSQL(id uuid.ID) *pan.Query {
paddy@148 19 var profile Profile
paddy@148 20 fields, _ := pan.GetQuotedFields(profile)
paddy@148 21 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile))
paddy@148 22 query.IncludeWhere()
paddy@148 23 query.Include(pan.GetColumn(profile, "ID")+" = ? AND "+pan.GetColumn(profile, "Deleted")+" = ?", id, false)
paddy@148 24 return query.FlushExpressions(" ")
paddy@148 25 }
paddy@148 26
paddy@148 27 func (p *postgres) getProfileByID(id uuid.ID) (Profile, error) {
paddy@148 28 query := p.getProfileByIDSQL(id)
paddy@148 29 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 30 if err != nil {
paddy@148 31 return Profile{}, err
paddy@148 32 }
paddy@148 33 var profile Profile
paddy@148 34 for rows.Next() {
paddy@148 35 err := pan.Unmarshal(rows, &profile)
paddy@148 36 if err != nil {
paddy@148 37 return Profile{}, err
paddy@148 38 }
paddy@148 39 }
paddy@148 40 if err := rows.Err(); err != nil {
paddy@148 41 return Profile{}, err
paddy@148 42 }
paddy@148 43 return profile, nil
paddy@148 44 }
paddy@148 45
paddy@148 46 func (p *postgres) getProfileByLoginSQL(value string) *pan.Query {
paddy@148 47 var profile Profile
paddy@148 48 var login Login
paddy@148 49 fields, _ := pan.GetAbsoluteFields(profile)
paddy@148 50 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile))
paddy@148 51 query.Include("INNER JOIN " + pan.GetTableName(login))
paddy@148 52 query.Include("ON " + pan.GetAbsoluteColumnName(profile, "ID") + " = " + pan.GetAbsoluteColumnName(login, "ProfileID"))
paddy@148 53 query.IncludeWhere()
paddy@148 54 query.Include(pan.GetAbsoluteColumnName(login, "Value")+" = ? AND "+pan.GetAbsoluteColumnName(profile, "Deleted")+" = ?", value, false)
paddy@148 55 return query.FlushExpressions(" ")
paddy@148 56 }
paddy@148 57
paddy@148 58 func (p *postgres) getProfileByLogin(value string) (Profile, error) {
paddy@148 59 query := p.getProfileByLoginSQL(value)
paddy@148 60 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 61 if err != nil {
paddy@148 62 return Profile{}, err
paddy@148 63 }
paddy@148 64 var profile Profile
paddy@148 65 for rows.Next() {
paddy@148 66 err := pan.Unmarshal(rows, &profile)
paddy@148 67 if err != nil {
paddy@148 68 return Profile{}, err
paddy@148 69 }
paddy@148 70 }
paddy@148 71 if err := rows.Err(); err != nil {
paddy@148 72 return Profile{}, err
paddy@148 73 }
paddy@148 74 return profile, nil
paddy@148 75 }
paddy@148 76
paddy@148 77 func (p *postgres) saveProfileSQL(profile Profile) *pan.Query {
paddy@148 78 fields, values := pan.GetQuotedFields(profile)
paddy@148 79 query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(profile))
paddy@148 80 query.Include("(" + pan.QueryList(fields) + ")")
paddy@148 81 query.Include("VALUES")
paddy@148 82 query.Include("("+pan.VariableList(len(values))+")", values...)
paddy@148 83 return query.FlushExpressions(" ")
paddy@148 84 }
paddy@148 85
paddy@148 86 func (p *postgres) saveProfile(profile Profile) error {
paddy@148 87 query := p.saveProfileSQL(profile)
paddy@148 88 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 89 return err
paddy@148 90 }
paddy@148 91
paddy@148 92 func (p *postgres) updateProfileSQL(id uuid.ID, change ProfileChange) *pan.Query {
paddy@148 93 var profile Profile
paddy@148 94 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ")
paddy@148 95 query.IncludeIfNotNil(pan.GetColumn(profile, "Name")+" = ?", change.Name)
paddy@148 96 query.IncludeIfNotNil(pan.GetColumn(profile, "Passphrase")+" = ?", change.Passphrase)
paddy@148 97 query.IncludeIfNotNil(pan.GetColumn(profile, "Iterations")+" = ?", change.Iterations)
paddy@148 98 query.IncludeIfNotNil(pan.GetColumn(profile, "Salt")+" = ?", change.Salt)
paddy@148 99 query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseScheme")+" = ?", change.PassphraseScheme)
paddy@148 100 query.IncludeIfNotNil(pan.GetColumn(profile, "Compromised")+" = ?", change.Compromised)
paddy@148 101 query.IncludeIfNotNil(pan.GetColumn(profile, "LockedUntil")+" = ?", change.LockedUntil)
paddy@148 102 query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseReset")+" = ?", change.PassphraseReset)
paddy@148 103 query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseResetCreated")+" = ?", change.PassphraseResetCreated)
paddy@148 104 query.IncludeIfNotNil(pan.GetColumn(profile, "LastSeen")+" = ?", change.LastSeen)
paddy@148 105 query.IncludeIfNotNil(pan.GetColumn(profile, "Deleted")+" = ?", change.Deleted)
paddy@148 106 query.FlushExpressions(", ")
paddy@148 107 query.IncludeWhere()
paddy@148 108 query.Include(pan.GetColumn(profile, "ID")+"= ?", id)
paddy@148 109 return query.FlushExpressions(" ")
paddy@148 110 }
paddy@148 111
paddy@148 112 func (p *postgres) updateProfile(id uuid.ID, change ProfileChange) error {
paddy@148 113 query := p.updateProfileSQL(id, change)
paddy@148 114 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 115 return err
paddy@148 116 }
paddy@148 117
paddy@148 118 func (p *postgres) updateProfilesSQL(ids []uuid.ID, change BulkProfileChange) *pan.Query {
paddy@148 119 var profile Profile
paddy@148 120 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ")
paddy@148 121 query.IncludeIfNotNil(pan.GetColumn(profile, "Compromised")+" = ?", change.Compromised)
paddy@148 122 query.FlushExpressions(", ")
paddy@148 123 query.IncludeWhere()
paddy@148 124 intids := make([]interface{}, len(ids))
paddy@148 125 for pos, id := range ids {
paddy@148 126 intids[pos] = id
paddy@148 127 }
paddy@148 128 query.Include(pan.GetColumn(profile, "ID")+" IN ("+pan.VariableList(len(ids))+")", intids...)
paddy@148 129 return query.FlushExpressions(" ")
paddy@148 130 }
paddy@148 131
paddy@148 132 func (p *postgres) updateProfiles(ids []uuid.ID, change BulkProfileChange) error {
paddy@148 133 query := p.updateProfilesSQL(ids, change)
paddy@148 134 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 135 return err
paddy@148 136 }
paddy@148 137
paddy@148 138 func (p *postgres) addLoginSQL(login Login) *pan.Query {
paddy@148 139 fields, values := pan.GetQuotedFields(login)
paddy@148 140 query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(login))
paddy@148 141 query.Include("(" + pan.QueryList(fields) + ")")
paddy@148 142 query.Include("VALUES")
paddy@148 143 query.Include("("+pan.VariableList(len(values))+")", values...)
paddy@148 144 return query.FlushExpressions(" ")
paddy@148 145 }
paddy@148 146
paddy@148 147 func (p *postgres) addLogin(login Login) error {
paddy@148 148 query := p.addLoginSQL(login)
paddy@148 149 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 150 return err
paddy@148 151 }
paddy@148 152
paddy@148 153 func (p *postgres) removeLoginSQL(value string, profile uuid.ID) *pan.Query {
paddy@148 154 var login Login
paddy@148 155 query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login))
paddy@148 156 query.IncludeWhere()
paddy@148 157 query.Include(pan.GetColumn(login, "Value")+"= ? AND "+pan.GetColumn(login, "ProfileID")+"= ?", value, profile)
paddy@148 158 return query.FlushExpressions(" ")
paddy@148 159 }
paddy@148 160
paddy@148 161 func (p *postgres) removeLogin(value string, profile uuid.ID) error {
paddy@148 162 query := p.removeLoginSQL(value, profile)
paddy@148 163 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 164 return err
paddy@148 165 }
paddy@148 166
paddy@148 167 func (p *postgres) recordLoginUseSQL(value string, when time.Time) *pan.Query {
paddy@148 168 var login Login
paddy@148 169 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(login)+" SET ")
paddy@148 170 query.Include(pan.GetColumn(login, "LastUsed")+"= ?", when)
paddy@148 171 query.IncludeWhere()
paddy@148 172 query.Include(pan.GetColumn(login, "Value")+"= ?", value)
paddy@148 173 return query.FlushExpressions(" ")
paddy@148 174 }
paddy@148 175
paddy@148 176 func (p *postgres) recordLoginUse(value string, when time.Time) error {
paddy@148 177 query := p.recordLoginUseSQL(value, when)
paddy@148 178 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 179 return err
paddy@148 180 }
paddy@148 181
paddy@148 182 func (p *postgres) listLoginsSQL(profile uuid.ID, num, offset int) *pan.Query {
paddy@148 183 var login Login
paddy@148 184 fields, _ := pan.GetQuotedFields(login)
paddy@148 185 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(login))
paddy@148 186 query.IncludeWhere()
paddy@148 187 query.Include(pan.GetColumn(login, "ProfileID")+"= ?", profile)
paddy@148 188 query.IncludeLimit(int64(num))
paddy@148 189 query.IncludeOffset(int64(offset))
paddy@148 190 return query.FlushExpressions(" ")
paddy@148 191 }
paddy@148 192
paddy@148 193 func (p *postgres) listLogins(profile uuid.ID, num, offset int) ([]Login, error) {
paddy@148 194 query := p.listLoginsSQL(profile, num, offset)
paddy@148 195 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 196 if err != nil {
paddy@148 197 return []Login{}, err
paddy@148 198 }
paddy@148 199 var logins []Login
paddy@148 200 for rows.Next() {
paddy@148 201 var login Login
paddy@148 202 err = pan.Unmarshal(rows, &login)
paddy@148 203 if err != nil {
paddy@148 204 return logins, err
paddy@148 205 }
paddy@148 206 logins = append(logins, login)
paddy@148 207 }
paddy@148 208 if err := rows.Err(); err != nil {
paddy@148 209 return logins, err
paddy@148 210 }
paddy@148 211 return logins, nil
paddy@148 212 }