auth
2015-03-21
Child:8267e1c8bcd1
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.
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/profile_postgres.go Sat Mar 21 01:23:33 2015 -0400 1.3 @@ -0,0 +1,212 @@ 1.4 +package auth 1.5 + 1.6 +import ( 1.7 + "time" 1.8 + 1.9 + "code.secondbit.org/uuid.hg" 1.10 + "github.com/secondbit/pan" 1.11 +) 1.12 + 1.13 +func (p Profile) GetSQLTableName() string { 1.14 + return "profiles" 1.15 +} 1.16 + 1.17 +func (l Login) GetSQLTableName() string { 1.18 + return "logins" 1.19 +} 1.20 + 1.21 +func (p *postgres) getProfileByIDSQL(id uuid.ID) *pan.Query { 1.22 + var profile Profile 1.23 + fields, _ := pan.GetQuotedFields(profile) 1.24 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile)) 1.25 + query.IncludeWhere() 1.26 + query.Include(pan.GetColumn(profile, "ID")+" = ? AND "+pan.GetColumn(profile, "Deleted")+" = ?", id, false) 1.27 + return query.FlushExpressions(" ") 1.28 +} 1.29 + 1.30 +func (p *postgres) getProfileByID(id uuid.ID) (Profile, error) { 1.31 + query := p.getProfileByIDSQL(id) 1.32 + rows, err := p.db.Query(query.String(), query.Args...) 1.33 + if err != nil { 1.34 + return Profile{}, err 1.35 + } 1.36 + var profile Profile 1.37 + for rows.Next() { 1.38 + err := pan.Unmarshal(rows, &profile) 1.39 + if err != nil { 1.40 + return Profile{}, err 1.41 + } 1.42 + } 1.43 + if err := rows.Err(); err != nil { 1.44 + return Profile{}, err 1.45 + } 1.46 + return profile, nil 1.47 +} 1.48 + 1.49 +func (p *postgres) getProfileByLoginSQL(value string) *pan.Query { 1.50 + var profile Profile 1.51 + var login Login 1.52 + fields, _ := pan.GetAbsoluteFields(profile) 1.53 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile)) 1.54 + query.Include("INNER JOIN " + pan.GetTableName(login)) 1.55 + query.Include("ON " + pan.GetAbsoluteColumnName(profile, "ID") + " = " + pan.GetAbsoluteColumnName(login, "ProfileID")) 1.56 + query.IncludeWhere() 1.57 + query.Include(pan.GetAbsoluteColumnName(login, "Value")+" = ? AND "+pan.GetAbsoluteColumnName(profile, "Deleted")+" = ?", value, false) 1.58 + return query.FlushExpressions(" ") 1.59 +} 1.60 + 1.61 +func (p *postgres) getProfileByLogin(value string) (Profile, error) { 1.62 + query := p.getProfileByLoginSQL(value) 1.63 + rows, err := p.db.Query(query.String(), query.Args...) 1.64 + if err != nil { 1.65 + return Profile{}, err 1.66 + } 1.67 + var profile Profile 1.68 + for rows.Next() { 1.69 + err := pan.Unmarshal(rows, &profile) 1.70 + if err != nil { 1.71 + return Profile{}, err 1.72 + } 1.73 + } 1.74 + if err := rows.Err(); err != nil { 1.75 + return Profile{}, err 1.76 + } 1.77 + return profile, nil 1.78 +} 1.79 + 1.80 +func (p *postgres) saveProfileSQL(profile Profile) *pan.Query { 1.81 + fields, values := pan.GetQuotedFields(profile) 1.82 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(profile)) 1.83 + query.Include("(" + pan.QueryList(fields) + ")") 1.84 + query.Include("VALUES") 1.85 + query.Include("("+pan.VariableList(len(values))+")", values...) 1.86 + return query.FlushExpressions(" ") 1.87 +} 1.88 + 1.89 +func (p *postgres) saveProfile(profile Profile) error { 1.90 + query := p.saveProfileSQL(profile) 1.91 + _, err := p.db.Exec(query.String(), query.Args...) 1.92 + return err 1.93 +} 1.94 + 1.95 +func (p *postgres) updateProfileSQL(id uuid.ID, change ProfileChange) *pan.Query { 1.96 + var profile Profile 1.97 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ") 1.98 + query.IncludeIfNotNil(pan.GetColumn(profile, "Name")+" = ?", change.Name) 1.99 + query.IncludeIfNotNil(pan.GetColumn(profile, "Passphrase")+" = ?", change.Passphrase) 1.100 + query.IncludeIfNotNil(pan.GetColumn(profile, "Iterations")+" = ?", change.Iterations) 1.101 + query.IncludeIfNotNil(pan.GetColumn(profile, "Salt")+" = ?", change.Salt) 1.102 + query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseScheme")+" = ?", change.PassphraseScheme) 1.103 + query.IncludeIfNotNil(pan.GetColumn(profile, "Compromised")+" = ?", change.Compromised) 1.104 + query.IncludeIfNotNil(pan.GetColumn(profile, "LockedUntil")+" = ?", change.LockedUntil) 1.105 + query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseReset")+" = ?", change.PassphraseReset) 1.106 + query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseResetCreated")+" = ?", change.PassphraseResetCreated) 1.107 + query.IncludeIfNotNil(pan.GetColumn(profile, "LastSeen")+" = ?", change.LastSeen) 1.108 + query.IncludeIfNotNil(pan.GetColumn(profile, "Deleted")+" = ?", change.Deleted) 1.109 + query.FlushExpressions(", ") 1.110 + query.IncludeWhere() 1.111 + query.Include(pan.GetColumn(profile, "ID")+"= ?", id) 1.112 + return query.FlushExpressions(" ") 1.113 +} 1.114 + 1.115 +func (p *postgres) updateProfile(id uuid.ID, change ProfileChange) error { 1.116 + query := p.updateProfileSQL(id, change) 1.117 + _, err := p.db.Exec(query.String(), query.Args...) 1.118 + return err 1.119 +} 1.120 + 1.121 +func (p *postgres) updateProfilesSQL(ids []uuid.ID, change BulkProfileChange) *pan.Query { 1.122 + var profile Profile 1.123 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ") 1.124 + query.IncludeIfNotNil(pan.GetColumn(profile, "Compromised")+" = ?", change.Compromised) 1.125 + query.FlushExpressions(", ") 1.126 + query.IncludeWhere() 1.127 + intids := make([]interface{}, len(ids)) 1.128 + for pos, id := range ids { 1.129 + intids[pos] = id 1.130 + } 1.131 + query.Include(pan.GetColumn(profile, "ID")+" IN ("+pan.VariableList(len(ids))+")", intids...) 1.132 + return query.FlushExpressions(" ") 1.133 +} 1.134 + 1.135 +func (p *postgres) updateProfiles(ids []uuid.ID, change BulkProfileChange) error { 1.136 + query := p.updateProfilesSQL(ids, change) 1.137 + _, err := p.db.Exec(query.String(), query.Args...) 1.138 + return err 1.139 +} 1.140 + 1.141 +func (p *postgres) addLoginSQL(login Login) *pan.Query { 1.142 + fields, values := pan.GetQuotedFields(login) 1.143 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(login)) 1.144 + query.Include("(" + pan.QueryList(fields) + ")") 1.145 + query.Include("VALUES") 1.146 + query.Include("("+pan.VariableList(len(values))+")", values...) 1.147 + return query.FlushExpressions(" ") 1.148 +} 1.149 + 1.150 +func (p *postgres) addLogin(login Login) error { 1.151 + query := p.addLoginSQL(login) 1.152 + _, err := p.db.Exec(query.String(), query.Args...) 1.153 + return err 1.154 +} 1.155 + 1.156 +func (p *postgres) removeLoginSQL(value string, profile uuid.ID) *pan.Query { 1.157 + var login Login 1.158 + query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login)) 1.159 + query.IncludeWhere() 1.160 + query.Include(pan.GetColumn(login, "Value")+"= ? AND "+pan.GetColumn(login, "ProfileID")+"= ?", value, profile) 1.161 + return query.FlushExpressions(" ") 1.162 +} 1.163 + 1.164 +func (p *postgres) removeLogin(value string, profile uuid.ID) error { 1.165 + query := p.removeLoginSQL(value, profile) 1.166 + _, err := p.db.Exec(query.String(), query.Args...) 1.167 + return err 1.168 +} 1.169 + 1.170 +func (p *postgres) recordLoginUseSQL(value string, when time.Time) *pan.Query { 1.171 + var login Login 1.172 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(login)+" SET ") 1.173 + query.Include(pan.GetColumn(login, "LastUsed")+"= ?", when) 1.174 + query.IncludeWhere() 1.175 + query.Include(pan.GetColumn(login, "Value")+"= ?", value) 1.176 + return query.FlushExpressions(" ") 1.177 +} 1.178 + 1.179 +func (p *postgres) recordLoginUse(value string, when time.Time) error { 1.180 + query := p.recordLoginUseSQL(value, when) 1.181 + _, err := p.db.Exec(query.String(), query.Args...) 1.182 + return err 1.183 +} 1.184 + 1.185 +func (p *postgres) listLoginsSQL(profile uuid.ID, num, offset int) *pan.Query { 1.186 + var login Login 1.187 + fields, _ := pan.GetQuotedFields(login) 1.188 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(login)) 1.189 + query.IncludeWhere() 1.190 + query.Include(pan.GetColumn(login, "ProfileID")+"= ?", profile) 1.191 + query.IncludeLimit(int64(num)) 1.192 + query.IncludeOffset(int64(offset)) 1.193 + return query.FlushExpressions(" ") 1.194 +} 1.195 + 1.196 +func (p *postgres) listLogins(profile uuid.ID, num, offset int) ([]Login, error) { 1.197 + query := p.listLoginsSQL(profile, num, offset) 1.198 + rows, err := p.db.Query(query.String(), query.Args...) 1.199 + if err != nil { 1.200 + return []Login{}, err 1.201 + } 1.202 + var logins []Login 1.203 + for rows.Next() { 1.204 + var login Login 1.205 + err = pan.Unmarshal(rows, &login) 1.206 + if err != nil { 1.207 + return logins, err 1.208 + } 1.209 + logins = append(logins, login) 1.210 + } 1.211 + if err := rows.Err(); err != nil { 1.212 + return logins, err 1.213 + } 1.214 + return logins, nil 1.215 +}