Exemple #1
0
// 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
	}
}
Exemple #2
0
// 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)
}
Exemple #3
0
// 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)
}
Exemple #4
0
// 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.

}
Exemple #5
0
// 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
}
Exemple #6
0
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
}
Exemple #7
0
// 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)
}
Exemple #8
0
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)
}
Exemple #9
0
// 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
}
Exemple #10
0
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)

}
Exemple #11
0
// 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
}
Exemple #12
0
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
}
Exemple #13
0
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)
}
Exemple #14
0
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.
}
Exemple #15
0
//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)
}
Exemple #16
0
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)
}
Exemple #17
0
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)
}
Exemple #18
0
// 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.
}
Exemple #19
0
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)

}
Exemple #20
0
// 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
}