// 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
}
