// GetOCSP retrieves a certdb.OCSPRecord from db by serial.
func (d *Accessor) GetOCSP(serial, aki string) (ors []certdb.OCSPRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
err = d.db.Select(&ors, fmt.Sprintf(d.db.Rebind(selectOCSPSQL), sqlstruct.Columns(certdb.OCSPRecord{})), serial, aki)
if err != nil {
return nil, wrapSQLError(err)
}
return ors, nil
}
//Public Methods
func FetchBot(id string) (Bot, error) {
rows, err := config.DB.Query(fmt.Sprintf("SELECT %s FROM bots WHERE group_id = $1", sqlstruct.Columns(Bot{})), id)
if err != nil {
return Bot{}, err
}
defer rows.Close()
rows.Next()
var bot Bot
err = sqlstruct.Scan(&bot, rows)
return bot, err
}
// GetUnexpiredOCSPs retrieves all unexpired certdb.OCSPRecord from db.
func (d *Accessor) GetUnexpiredOCSPs() (ors []certdb.OCSPRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
err = d.db.Select(&ors, fmt.Sprintf(d.db.Rebind(selectAllUnexpiredOCSPSQL), sqlstruct.Columns(certdb.OCSPRecord{})))
if err != nil {
return nil, wrapSQLError(err)
}
return ors, nil
}
// GetUnexpiredCertificates gets all unexpired certificate from db.
func (d *Accessor) GetUnexpiredCertificates() (crs []certdb.CertificateRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
err = d.db.Select(&crs, fmt.Sprintf(d.db.Rebind(selectAllUnexpiredSQL), sqlstruct.Columns(certdb.CertificateRecord{})))
if err != nil {
return nil, wrapSQLError(err)
}
return crs, nil
}
// GetCertificate gets a CertificateRecord indexed by serial.
func GetCertificate(db *sql.DB, serial string) (*CertificateRecord, error) {
cr := new(CertificateRecord)
rows, err := db.Query(fmt.Sprintf(selectSQL, sqlstruct.Columns(*cr)), serial)
if err != nil {
return nil, wrapCertStoreError(err)
}
defer rows.Close()
if rows.Next() {
return cr, wrapCertStoreError(sqlstruct.Scan(cr, rows))
}
return nil, nil
}
// GetCertificate gets a certdb.CertificateRecord indexed by serial.
func (d *Accessor) GetCertificate(serial, aki string) (crs []certdb.CertificateRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
err = d.db.Select(&crs, fmt.Sprintf(d.db.Rebind(selectSQL), sqlstruct.Columns(certdb.CertificateRecord{})), serial, aki)
if err != nil {
return nil, wrapSQLError(err)
}
return crs, nil
}
// GetOCSP retrieves a OCSPRecord from db by serial.
func GetOCSP(db *sql.DB, serial string) (rr *OCSPRecord, err error) {
rr = new(OCSPRecord)
rows, err := db.Query(fmt.Sprintf(selectOCSPSQL, sqlstruct.Columns(*rr)), serial)
if err != nil {
return nil, wrapCertStoreError(err)
}
defer rows.Close()
if rows.Next() {
return rr, sqlstruct.Scan(rr, rows)
}
return nil, nil
}
// GetOCSP retrieves a certdb.OCSPRecord from db by serial.
func (d *Accessor) GetOCSP(serial string) (rr *certdb.OCSPRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
rr = new(certdb.OCSPRecord)
rows, err := d.db.Query(fmt.Sprintf(selectOCSPSQL, sqlstruct.Columns(*rr)), serial)
if err != nil {
return nil, wrapSQLError(err)
}
defer rows.Close()
if rows.Next() {
return rr, sqlstruct.Scan(rr, rows)
}
return nil, nil
}
// GetCertificate gets a certdb.CertificateRecord indexed by serial.
func (d *Accessor) GetCertificate(serial string) (*certdb.CertificateRecord, error) {
err := d.checkDB()
if err != nil {
return nil, err
}
cr := new(certdb.CertificateRecord)
rows, err := d.db.Query(fmt.Sprintf(selectSQL, sqlstruct.Columns(*cr)), serial)
if err != nil {
return nil, wrapSQLError(err)
}
defer rows.Close()
if rows.Next() {
return cr, wrapSQLError(sqlstruct.Scan(cr, rows))
}
return nil, nil
}
// GetUnexpiredOCSPs retrieves all unexpired OCSPRecord from db.
func GetUnexpiredOCSPs(db *sql.DB) (rrs []*OCSPRecord, err error) {
rr := new(OCSPRecord)
rows, err := db.Query(fmt.Sprintf(selectAllUnexpiredOCSPSQL, sqlstruct.Columns(*rr)))
if err != nil {
return nil, wrapCertStoreError(err)
}
defer rows.Close()
for rows.Next() {
err = sqlstruct.Scan(rr, rows)
if err != nil {
return nil, wrapCertStoreError(err)
}
rrs = append(rrs, rr)
}
return rrs, nil
}
// GetUnexpiredCertificates gets all unexpired certificate from db.
func GetUnexpiredCertificates(db *sql.DB) (crs []*CertificateRecord, err error) {
cr := new(CertificateRecord)
rows, err := db.Query(fmt.Sprintf(selectAllUnexpiredSQL, sqlstruct.Columns(*cr)))
if err != nil {
return nil, wrapCertStoreError(err)
}
defer rows.Close()
for rows.Next() {
err = sqlstruct.Scan(cr, rows)
if err != nil {
return nil, wrapCertStoreError(err)
}
crs = append(crs, cr)
}
return crs, nil
}
func cacheFetch(whereStr string, values []interface{}) ([]Cached, error) {
queryStr := fmt.Sprintf("SELECT %s FROM cached", sqlstruct.Columns(Cached{}))
rows, err := config.DB.Query(queryStr+" "+whereStr, values...)
if err != nil {
return []Cached{}, err
}
defer rows.Close()
actions := []Cached{}
for rows.Next() {
var act Cached
err := sqlstruct.Scan(&act, rows)
if err == nil {
actions = append(actions, act)
}
}
return actions, nil
}
// GetOCSP retrieves a certdb.OCSPRecord from db by serial.
func (d *Accessor) GetOCSP(serial, aki string) (rrs []certdb.OCSPRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
rr := certdb.OCSPRecord{}
rows, err := d.db.Query(fmt.Sprintf(selectOCSPSQL, sqlstruct.Columns(rr)), serial, aki)
if err != nil {
return nil, wrapSQLError(err)
}
defer rows.Close()
if rows.Next() {
err = sqlstruct.Scan(&rr, rows)
if err != nil {
return nil, wrapSQLError(err)
}
rrs = append(rrs, rr)
}
return rrs, nil
}
// GetUnexpiredOCSPs retrieves all unexpired certdb.OCSPRecord from db.
func (d *Accessor) GetUnexpiredOCSPs() (rrs []*certdb.OCSPRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
rr := new(certdb.OCSPRecord)
rows, err := d.db.Query(fmt.Sprintf(selectAllUnexpiredOCSPSQL, sqlstruct.Columns(*rr)))
if err != nil {
return nil, wrapSQLError(err)
}
defer rows.Close()
for rows.Next() {
err = sqlstruct.Scan(rr, rows)
if err != nil {
return nil, wrapSQLError(err)
}
rrs = append(rrs, rr)
}
return rrs, nil
}
// GetUnexpiredCertificates gets all unexpired certificate from db.
func (d *Accessor) GetUnexpiredCertificates() (crs []*certdb.CertificateRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
cr := new(certdb.CertificateRecord)
rows, err := d.db.Query(fmt.Sprintf(selectAllUnexpiredSQL, sqlstruct.Columns(*cr)))
if err != nil {
return nil, wrapSQLError(err)
}
defer rows.Close()
for rows.Next() {
err = sqlstruct.Scan(cr, rows)
if err != nil {
return nil, wrapSQLError(err)
}
crs = append(crs, cr)
}
return crs, nil
}
// GetCertificate gets a certdb.CertificateRecord indexed by serial.
func (d *Accessor) GetCertificate(serial, aki string) (crs []certdb.CertificateRecord, err error) {
err = d.checkDB()
if err != nil {
return nil, err
}
cr := certdb.CertificateRecord{}
rows, err := d.db.Query(fmt.Sprintf(selectSQL, sqlstruct.Columns(cr)), serial, aki)
if err != nil {
return nil, wrapSQLError(err)
}
defer rows.Close()
if rows.Next() {
err = sqlstruct.Scan(&cr, rows)
if err != nil {
return nil, wrapSQLError(err)
}
crs = append(crs, cr)
}
return crs, nil
}
func updateLikes() {
queryStr := fmt.Sprintf("SELECT %s FROM groupme_posts WHERE posted_at >= NOW() - '1 day'::INTERVAL", sqlstruct.Columns(gmPost{}))
rows, err := config.DB.Query(queryStr)
if err != nil {
return
}
defer rows.Close()
groupedPosts := make(map[string][]gmPost)
for rows.Next() {
var post gmPost
err := sqlstruct.Scan(&post, rows)
if err == nil {
slice := groupedPosts[post.GroupID]
slice = append(slice, post)
groupedPosts[post.GroupID] = slice
}
}
token := os.Getenv("groupMeID")
updated := make(map[int]int)
for key, group := range groupedPosts {
getURL := "https://api.groupme.com/v3/groups/" + key + "/likes?period=day&token=" + token
resp, _ := http.Get(getURL)
body, _ := ioutil.ReadAll(resp.Body)
var wrapper gmMessageWrapper
json.Unmarshal(body, &wrapper)
for _, message := range wrapper.Response.Messages {
for _, post := range group {
if post.MessageID == message.MessageID() {
updated[post.ID] = len(message.FavoritedBy)
}
}
}
}
tx, err := config.DB.Begin()
if err != nil {
return
}
stmt, _ := config.DB.Prepare("UPDATE groupme_posts SET likes=$1 WHERE id=$2")
for updateID, likeCount := range updated {
stmt.Exec(likeCount, updateID)
}
tx.Commit()
}