// Attempts to populate series bundle by name. // Otherwise this series bundle will be inserted. func (sb *SeriesBundle) SelectByName(seriesName string) error { sbByName := ` SELECT bundle FROM attributes_bundle WHERE category = ? AND bundle->'name' = ? ` dba := func(dbConn *sql.DB) error { row := dbConn.QueryRow(sbByName, BUNDLE_SERIES, seriesName) var bundleStore hstore.Hstore if err := row.Scan(&bundleStore); err != nil { return err } if err := sb.FromBundle(bundleStore.Map); err != nil { return err } return nil } if err := db.ExecuteFn(dba); err != nil { // Reinit; they can do whatever I don't care. sb = &SeriesBundle{} return err } else { return nil } }
// Selects an excerpt of torrents. Only fetches an ID, InfoHash, and CreatedBy func (t *Torrent) SelectSummaryPage() ([]*Torrent, error) { summaryList := make([]*Torrent, 0, 100) torrents := codex.Table("torrents") torrentsProjection := torrents.Project( "torrent_id", "name", "info_hash", "created_by", ) torrentsFilter, err := torrentsProjection.Limit(100).ToSql() if err != nil { return nil, err } dba := func(dbConn *sql.DB) error { rows, err := dbConn.Query(torrentsFilter) if err != nil { return err } for rows.Next() { t := &Torrent{isInit: true} _ = rows.Scan(&t.ID, &t.Name, &t.InfoHash, &t.CreatedBy) summaryList = append(summaryList, t) } return nil } return summaryList, db.ExecuteFn(dba) }
// Looks up a torrent based on its info hash, // this is a 20-byte SHA which is encoded as a string [2-chars per byte.] func (t *Torrent) SelectHash(hash string) error { torrents := codex.Table("torrents") torrentsProjection := torrents.Project( "torrent_id", "name", "info_hash", "created_by", "creation_date", "encoding", "info_bencoded", ) torrentsFilter, err := torrentsProjection.Where( torrents("info_hash").Eq(hash)).ToSql() if err != nil { return err } dba := func(dbConn *sql.DB) error { row := dbConn.QueryRow(torrentsFilter) err := row.Scan(&t.ID, &t.Name, &t.InfoHash, &t.CreatedBy, &t.CreationDate, &t.Encoding, &t.EncodedInfo) if err == nil { t.isInit = true } return err } return db.ExecuteFn(dba) }
// Selects a user by their secret key. This is used by the tracker // to authorize a user. // // The secret is expected to be a UTF8 string representing a byte array // using 2-characters per byte. (As per the standard encoding/hex package.) func (u *User) SelectSecret(secret string) error { selectUserBySecret := `SELECT user_id,username,passwordhash,passwordsalt,secret,secret_hash FROM "users" WHERE secret = $1` secretHex, err := hex.DecodeString(secret) if err != nil { return errors.New("The user's secret was not in the expected format.") } dba := func(dbConn *sql.DB) error { row := dbConn.QueryRow(selectUserBySecret, secretHex) err := row.Scan(&u.UserId, &u.Username, &u.passwordHash, &u.passwordSalt, &u.Secret, &u.SecretHash) if err != nil { return err } u.isInit = true return nil } err = db.ExecuteFn(dba) if err != nil { return err } return nil //safe to use pointer. }
// Returns -1 if user count cannot be established. // Returns 0 or 1 if user exists. // Can return > 1 but schema would prevent it. func CountUsers(username string) int { var userCount int = -1 fn := func(database *sql.DB) error { stmt, err := database.Prepare( "SELECT COUNT(username) FROM users WHERE username = $1") if err != nil { return errors.New(fmt.Sprintf("error preparing statement: %s", err.Error())) } record := stmt.QueryRow(username) if err != nil { return errors.New(fmt.Sprintf("error running statement: %s", err.Error())) } err = record.Scan(&userCount) if err != nil { return errors.New(fmt.Sprintf("error returning results %s", err.Error())) } return nil } err := db.ExecuteFn(fn) if err != nil { fmt.Printf("Error executing db action Users#Count: %s \n -- \n", err) } return userCount }
func (s *Session) WriteFor(user *User, ipAddr string) error { //user.lazyLoad() //ensure user is populated or able to be populated //check if session exists; if so: update with last_seen_at and last_seen_ip updateSession := `UPDATE "` + SESSIONS_TABLE + `" SET login_ip = $2 WHERE user_id = $1` insertSession := `INSERT INTO "` + SESSIONS_TABLE + `"(user_id, login_ip) VALUES($1, $2)` // try parse IP host, _, err := net.SplitHostPort(ipAddr) if err != nil { fmt.Printf("Error parsing IP from remoteAddr: %s", err.Error()) return err } ip := net.ParseIP(host) if ip == nil { ipAddr = "::1" } else { ipAddr = ip.String() } dba := func(dbConn *sql.DB) error { result, err := dbConn.Exec(updateSession, user.UserId, ipAddr) if err != nil { fmt.Printf("error updating user's sessions: %s \n", err.Error()) return err } rowsUpdated, err := result.RowsAffected() if err != nil { fmt.Printf("Error checking # rows affected by user session update: %s", err.Error()) return err } // First update previous session record if it can be found. if rowsUpdated > 0 { fmt.Printf("session update be OK <3 \n") return nil } // Then insert a new one. fmt.Printf("session can no be update. hmm. maybe ok maybe i insert.") result, err = dbConn.Exec(insertSession, user.UserId, "::1") if err != nil { fmt.Printf("error inserting user's sessions: %s", err.Error()) return err } return nil } err = db.ExecuteFn(dba) return err }
// save writes encoded session.Values to a database record. // writes to http_sessions table by default. func (db *DatabaseStore) save(session *sessions.Session) error { encoded, err := securecookie.EncodeMulti(session.Name(), session.Values, db.Codecs...) if err != nil { return err } fn := func(dbConn *sql.DB) error { // Write record to sessions table. var sessionCount int = -1 // Session exists? row := dbConn.QueryRow("SELECT COUNT(key) AS count FROM \""+SESSIONS_TABLE+"\" WHERE key = $1", session.ID) err := row.Scan(&sessionCount) if err != nil { return err } tx, err := dbConn.Begin() if err != nil { return err } if sessionCount > 0 { // update _, err = tx.Exec("UPDATE \""+SESSIONS_TABLE+"\" SET data = $1 WHERE key = $2", encoded, session.ID) if err != nil { return err } } else if sessionCount == 0 { // insert _, err = tx.Exec("INSERT INTO \""+SESSIONS_TABLE+"\" (key, data) VALUES($1,$2)", session.ID, encoded) if err != nil { return err } } else { // error err = errors.New("There was an error while trying to lookup a previous session.") return err } if err = tx.Commit(); err != nil { return err } return nil } return dbLib.ExecuteFn(fn) }
func (u *User) Delete() error { deleteUserById := `DELETE FROM "users" WHERE user_id = $1` dba := func(dbConn *sql.DB) error { _, err := dbConn.Exec(deleteUserById, u.UserId) if err != nil { return err } return nil } return db.ExecuteFn(dba) }
// TODO: Pagination // Select the latest episodes, regardless of series. func LatestEpisodes() []*EpisodeBundle { episodes := make([]*EpisodeBundle, 0) loadBundles := ` SELECT bundle FROM attributes_bundle WHERE category = 'episode' ORDER BY modified DESC LIMIT 100 ` dba := func(dbConn *sql.DB) error { rows, err := dbConn.Query(loadBundles) if err != nil { return err } for rows.Next() { // Scan hstore bundle var mappedBundle hstore.Hstore eb := &EpisodeBundle{} if err := rows.Scan(&mappedBundle); err != nil { return err } // Parse hstore into episode bundle if err := eb.FromBundle(mappedBundle.Map); err != nil { return err } // Append episode bundle to final list episodes = append(episodes, eb) } return nil } // Trouble fetching bundles? err := db.ExecuteFn(dba) if err != nil { log.Printf("Error fetching latest episodes: %s \n", err.Error()) } return episodes }
func (eb *EpisodeBundle) PersistWithSeries(series *SeriesBundle) error { insertEb := ` INSERT INTO attributes_bundle (parent_id, category, bundle, modified) VALUES (?, ?, ?, ?) RETURNING attributes_bundle_id ` dba := func(dbConn *sql.DB) error { txn, err := dbConn.Begin() if err != nil { return err } // Persist series and retrieve ID if err = series.PersistWith(txn); err != nil { _ = txn.Rollback() return err } row := txn.QueryRow( insertEb, series.ID, BUNDLE_EPISODE, eb.ToBundle(), time.Now()) err = row.Scan(&eb.ID) if err != nil { _ = txn.Rollback() return err } if err = txn.Commit(); err != nil { return err } return nil } return db.ExecuteFn(dba) }
// Creates a new user record in the database. // The first return parameter is an error-code that represents a non-fatal // problem that could be presented to the user. // // The second return parameter is a fatal error passed up from the database layer. func NewUser(username, password string) (UserModelError, error) { var outStatus UserModelError fn := func(database *sql.DB) error { if CountUsers(username) > 0 { outStatus = USERNAME_TAKEN return nil } passwordHash, passwordSalt, err := genHash(password) if err != nil { return err } announceSecret, announceHash, err := genSecret() if err != nil { return err } stmt, err := database.Prepare("INSERT INTO users(username,passwordhash,passwordsalt,secret,secret_hash) VALUES($1,$2, $3, $4, $5)") fmt.Printf("user registered; [DEBUG] \n secret: %s \n hash: %s \n\n", fmt.Sprintf("%x", announceSecret), fmt.Sprintf("%x", announceHash)) if err != nil { return errors.New(fmt.Sprintf("Error preparing statement: %s", err.Error())) } res, err := stmt.Exec(username, string(passwordHash), passwordSalt, announceSecret, announceHash) if err != nil { return errors.New(fmt.Sprintf("Error executing statement: %s", err.Error())) } fmt.Printf("result was: %s", res) //use result to silence compiler for now. return nil } err := db.ExecuteFn(fn) if err != nil { fmt.Printf("Error executing db action Users#New: %s \n -- \n", err) return outStatus, err } return outStatus, err }
func (elem *Attribute) SelectTorrent(torrentId int) error { selectAttributes := `SELECT attribute_id, torrent_id, name, artist_name, album_name, release_year, music_format, disc_num, total_discs, album_description,release_description FROM attributes WHERE torrent_id = $1` dba := func(dbConn *sql.DB) error { row := dbConn.QueryRow(selectAttributes, torrentId) var artistBytes []byte err := row.Scan(&elem.id, &elem.TorrentId, &elem.Name, &artistBytes, &elem.AlbumName, &elem.ReleaseYear, &elem.MusicFormat, &elem.DiscNumber, &elem.Discs, &elem.AlbumDescription, &elem.ReleaseDescription, ) fmt.Printf("len artistNames bytes: %d \n", len(artistBytes)) if err != nil { return err } artistBuf := bytes.NewBuffer(artistBytes) decoder := gob.NewDecoder(artistBuf) err = decoder.Decode(&elem.ArtistName) if err != nil { return err } return nil } err := db.ExecuteFn(dba) if err != nil { return err } return nil //safe to use returned list }
func (s *Session) DeleteFor(user *User) error { if user == nil { return errors.New("No user to delete a session for") } deleteSession := `DELETE FROM "` + SESSIONS_TABLE + `" WHERE user_id = $1` dba := func(dbConn *sql.DB) error { _, err := dbConn.Exec(deleteSession, user.UserId) if err != nil { return err } // ignore affected rows. return nil } return db.ExecuteFn(dba) }
func AllUsers() ([]*User, error) { usersList := make([]*User, 0) selectUsers := `SELECT user_id, username, email, passwordhash, passwordsalt, secret, secret_hash FROM "users"` dba := func(dbConn *sql.DB) error { rows, err := dbConn.Query(selectUsers) if err != nil { return err } for rows.Next() { u := &User{} err := rows.Scan( &u.UserId, &u.Username, &u.emailSql, &u.passwordHash, &u.passwordSalt, &u.Secret, &u.SecretHash) if err != nil { return err } u.Email = u.emailSql.String usersList = append(usersList, u) } return nil } err := db.ExecuteFn(dba) if err != nil { return usersList, err } return usersList, err //safe to use pointer. }
//load fetches a session by ID from the database and decodes its content into session.Values func (db *DatabaseStore) load(session *sessions.Session) error { fn := func(dbConn *sql.DB) error { // Write record to sessions table. row := dbConn.QueryRow("SELECT http_session_id, key, data FROM \""+SESSIONS_TABLE+"\" WHERE key = $1", session.ID) var id int var key, data string if err := row.Scan(&id, &key, &data); err != nil { return err } if err := securecookie.DecodeMulti(session.Name(), string(data), &session.Values, db.Codecs...); err != nil { return err } return nil } return dbLib.ExecuteFn(fn) }
func (sb *SeriesBundle) Persist() error { sbInsert := ` INSERT INTO attributes_bundle(category, bundle, modified) VALUES ($1, $2, $3) RETURNING attributes_bundle_id` dba := func(dbConn *sql.DB) error { hBundle := &hstore.Hstore{Map: sb.ToBundle()} row := dbConn.QueryRow(sbInsert, string(BUNDLE_SERIES), hBundle, time.Now()) if err := row.Scan(&sb.ID); err != nil { return err } return nil } return db.ExecuteFn(dba) }
func (attributes *Attribute) WriteFor(torrentId int) error { insert := `INSERT INTO attributes ( torrent_id, name, artist_name, album_name, release_year, music_format, disc_num, total_discs, album_description, release_description ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10 )` dba := func(dbConn *sql.DB) error { insertStmt, err := dbConn.Prepare(insert) if err != nil { return err } encodeBuf := bytes.NewBuffer(make([]byte, 0)) encoder := gob.NewEncoder(encodeBuf) encoder.Encode(attributes.ArtistName) _, err = insertStmt.Exec(torrentId, attributes.Name, encodeBuf.Bytes(), attributes.AlbumName, attributes.ReleaseYear, attributes.MusicFormat, attributes.DiscNumber, attributes.Discs, attributes.AlbumDescription, attributes.ReleaseDescription, ) if err != nil { return err } return nil } return db.ExecuteFn(dba) }
// Select user by ID number and populate the current `user` struct with the record data. // Returns an error if there was a problem. fetching the user information from the database. func (u *User) SelectId(id int) error { selectUserById := `SELECT user_id, username, is_admin, passwordhash, passwordsalt, secret, secret_hash FROM "users" WHERE user_id = $1` dba := func(dbConn *sql.DB) error { row := dbConn.QueryRow(selectUserById, id) err := row.Scan(&u.UserId, &u.Username, &u.IsAdmin, &u.passwordHash, &u.passwordSalt, &u.Secret, &u.SecretHash) if err != nil { return err } u.isInit = true return nil } err := db.ExecuteFn(dba) if err != nil { return err } return nil //safe to use pointer. }
func (t *Torrent) Write() error { torrents := codex.Table("torrents") torrentsInsert, err := torrents.Insert( t.Name, t.InfoHash, t.CreatedBy, t.CreationDate, t.Encoding, encodeBytesForPG(t.EncodedInfo), ).Into( "name", "info_hash", "created_by", "creation_date", "encoding", "info_bencoded", ).Returning("torrent_id").ToSql() if err != nil { return err } updateTorrent, err := torrents.Set( "name", "info_hash", "created_by", "creation_date", "encoding", "info_bencoded", ).To( t.Name, t.InfoHash, t.CreatedBy, t.CreationDate, t.Encoding, encodeBytesForPG(t.EncodedInfo), ).Where(torrents("torrent_id").Eq(t.ID)).ToSql() if err != nil { return err } dba := func(dbConn *sql.DB) error { noRowsUpdated := true //try update then insert if t.ID > 0 { res, err := dbConn.Exec(updateTorrent) if err != nil { return err } rowsAffected, err := res.RowsAffected() if err != nil { return err } if rowsAffected > 0 { noRowsUpdated = false } } //row not updated; do an insert. if noRowsUpdated { fmt.Printf("performing insert for torrent \n") err := dbConn.QueryRow(torrentsInsert).Scan(&t.ID) if err != nil { return err } if t.lazyAttributes == nil { //TODO: attributes not supplied? guess from filenames? t.lazyAttributes = &Attribute{} } err = t.lazyAttributes.WriteFor(t.ID) if err != nil { return err } } return nil } return db.ExecuteFn(dba) }
// Selects the latest series' of television. // // This will select 100 series at a time which have a torrent or episode(s) // associated with them. // func LatestSeries() []*SeriesBundle { seriesByID := make(map[int]*SeriesBundle) seriesList := make([]*SeriesBundle, 0) // Selects all episodes from series // If no episodes are avail., the series itself is selected. // (This would happen if, for e.g, the series is related to a multi-file torrent.) loadSeriesBundles := ` SELECT episode.parent_id, series.attributes_bundle_id, tor.torrent_id, series.bundle, episode.bundle FROM attributes_bundle AS series LEFT JOIN attributes_bundle AS episode ON series.attributes_bundle_id = episode.parent_id INNER JOIN torrents AS tor ON tor.attributes_bundle_id = episode.attributes_bundle_id OR tor.attributes_bundle_id = series.attributes_bundle_id WHERE series.category = 'series' ORDER BY series.modified DESC LIMIT 100 ` dba := func(dbConn *sql.DB) error { rows, err := dbConn.Query(loadSeriesBundles) if err != nil { return err } for rows.Next() { var epIdN, serIdN sql.NullInt64 var seriesId, episodeId, torrentId int var seriesBundle, episodeBundle hstore.Hstore err := rows.Scan( &epIdN, &serIdN, &torrentId, &seriesBundle, &episodeBundle) if err != nil { return err } seriesId = int(serIdN.Int64) // TODO: loss of precision. episodeId = int(epIdN.Int64) // Create a map-entry for the series if we haven't seen it yet. // Add series if not exist var seriesIdx int if !epIdN.Valid { // This is a series, set the series index // to the series' attribute_bundle identifier seriesIdx = seriesId } else { // This is an episode, set the series index // to the episodes' parent ID. // (The series' attribute_bundle identifier.) seriesIdx = episodeId } // If we haven't seen this series before // add it to our in-mem structure if _, ok := seriesByID[seriesIdx]; !ok { series := &SeriesBundle{Episodes: make([]*EpisodeBundle, 0)} series.TorrentID = torrentId series.FromBundle(seriesBundle.Map) seriesByID[seriesId] = series seriesList = append(seriesList, series) } // If this has an episodes' bundle associated with it // load that bundle into its related series. if episodeBundle.Map != nil { // Attach episode bundle if applicable. episode := &EpisodeBundle{} episode.TorrentID = torrentId episode.FromBundle(episodeBundle.Map) seriesByID[seriesId].Episodes = append( seriesByID[seriesId].Episodes, episode, ) } } return nil } err := db.ExecuteFn(dba) if err != nil { log.Printf("Fetching latest series error: %s \n", err.Error()) } return seriesList }