func Query(start, limit int) ([]User, error) {
var (
err error
userList []User
)
db, err := da.Connect()
if err != nil {
return userList, err
}
// Prepare statement for reading data
stmt, err := db.Prepare(QUERY_USERS)
if err != nil {
return userList, err
}
defer stmt.Close()
rows, err := stmt.Query(start, limit)
for rows.Next() {
u := User{}
err = rows.Scan(&u.Id, &u.Name, &u.Privilege)
if err != nil {
panic(err)
}
userList = append(userList, u)
}
return userList, nil
}
func CreateUser() error {
var (
uid int = 1
username string = "HJin"
createTS int64 = time.Now().Unix()
)
pwd := ""
privilege := 0xFFFF
db, err := da.Connect()
if err != nil {
return err
}
insertStmt, err := db.Prepare(insertSQL)
if err != nil {
log.Println("insert")
return err
}
defer insertStmt.Close()
db.Exec("delete from " + targetTableName)
_, err = insertStmt.Exec(uid, username, user.Hash(pwd), privilege, createTS)
if err != nil {
return err
}
return nil
}
func CreateCategories() error {
var (
insertSQL = "INSERT INTO blog_categories ( id, name, alias) VALUES (?,?,?)"
)
db, err := da.Connect()
if err != nil {
return err
}
insertStmt, err := db.Prepare(insertSQL)
if err != nil {
log.Println("insert")
return err
}
defer insertStmt.Close()
_, err = db.Exec("delete from blog_categories")
if err != nil {
return err
}
_, err = insertStmt.Exec(1, "it", "技术")
if err != nil {
return err
}
_, err = insertStmt.Exec(2, "chat", "扯淡")
if err != nil {
return err
}
_, err = insertStmt.Exec(3, "live", "生活")
if err != nil {
return err
}
return nil
}
func Check(username, pwd string) (bool, string, error) {
db, err := da.Connect()
if err != nil {
return false, "", err
}
// Prepare statement for reading data
stmt, err := db.Prepare(VALIDATE_USER)
if err != nil {
return false, "", err
}
defer stmt.Close()
var count int
// todo load salt form conf
err = stmt.QueryRow(username, Hash(pwd)).Scan(&count)
if err != nil {
return false, "", err
}
log.Println(username, pwd, Hash(pwd), count)
if count == 0 {
return false, "", nil
}
ts := strconv.FormatInt(time.Now().Add(Expires).Unix(), 10)
username = base64.StdEncoding.EncodeToString([]byte(username))
sign := fmt.Sprintf("%s|%s|%s", username, ts, Hash(username+ts+getSalt()))
return true, sign, nil
}
func importCategories() error {
var (
readSQL = "select cid, name, url from content_categories"
insertSQL = "INSERT INTO blog_categories ( id, name, alias) VALUES (?,?,?)"
)
db, err := da.Connect()
if err != nil {
return err
}
readStmt, err := db.Prepare(readSQL)
if err != nil {
return err
}
defer readStmt.Close()
insertStmt, err := db.Prepare(insertSQL)
if err != nil {
log.Println("insert")
return err
}
defer insertStmt.Close()
_, err = db.Exec("delete from blog_categories")
if err != nil {
panic(err)
}
log.Println("hehe")
rows, err := readStmt.Query()
if err != nil {
return err
}
for rows.Next() {
var (
id int
name string
alias string
)
err := rows.Scan(&id, &alias, &name)
log.Println(id, name, alias)
if err != nil {
return err
}
_, err = insertStmt.Exec(id, name, alias)
if err != nil {
return err
}
}
return nil
}
func Query(start, limit int) []Post {
var (
err error
ps []Post
)
db, err := da.Connect()
if err != nil {
panic(err)
}
// Prepare statement for reading data
stmt, err := db.Prepare(QUERY_POSTS)
if err != nil {
panic(err) // proper error handling instead of panic in your app
}
defer stmt.Close()
var (
id int
uid int
content string
category category.Category
pubtime int64
title string
description string
keywords string
)
rows, err := stmt.Query(start, limit)
for rows.Next() {
// "SELECT p.id, p.uid, p.content, p.cid, c.name, c.alias, p.pubtime, p.title, p.description, p.keywords FROM " + TABLE_NAME_POSTS + " as p LEFT JOIN " + TABLE_NAME_CATEGORIES + " as c ON c.id=p.cid WHERE id = ?"
err = rows.Scan(&id, &uid, &content, &category.Id, &category.Name, &category.Alias, &pubtime, &title, &description, &keywords)
if err != nil {
panic(err)
}
var (
p Post
)
p.Id = id
p.Content = template.HTML(content)
p.Category = category
p.PubTime = time.Unix(pubtime, 0)
p.Title = title
p.Description = description
p.Keywords.Parse(keywords)
ps = append(ps, p)
}
return ps
}
func (p *RawPost) Save() error {
db, err := da.Connect()
if err != nil {
return err
}
var (
stmt *sql.Stmt
isInsert = false
)
var (
result sql.Result
)
if p.Id == 0 {
// "INSERT INTO " + TABLE_NAME_POSTS + "(uid, content, cid, pubtime, title, description, keywords) VALUES (?,?,?,?,?,?,?)"
// "UPDATE " + TABLE_NAME_POSTS + " SET content=?, cid=?, pubtime=?, title=?, description=?, keywords=? WHERE id=?"
stmt, err = db.Prepare(INSERT_POSTS) // ? = placeholder
if err != nil {
return err
}
isInsert = true
defer stmt.Close() // Close the statement when we leave main() / the program terminates
result, err = stmt.Exec(p.UserId, string(p.Content), p.Category.Id, p.PubTime.Unix(), p.Title, p.Description, p.Keywords.String())
if err != nil {
return err
}
} else {
stmt, err = db.Prepare(UPDATE_POSTS) // ? = placeholder
if err != nil {
return err
}
defer stmt.Close() // Close the statement when we leave main() / the program terminates
result, err = stmt.Exec(string(p.Content), p.Category.Id, p.PubTime.Unix(), p.Title, p.Description, p.Keywords.String(), p.Id)
if err != nil {
return err
}
}
if isInsert {
id, err := result.LastInsertId()
if err != nil {
return err
}
p.Id = int(id)
}
return nil
// Prepare statement for inserting data
// db.Do("hmset", "post:"+p.Id, "id", p.Id, "title", p.Title, "content", string(p.Content), "keywords", p.Keywords.Marshal(), "description", p.Description, "pubtime", p.PubTime.Unix(), "category", p.Category)
}
func importUsers() error {
db, err := da.Connect()
if err != nil {
return err
}
readStmt, err := db.Prepare(readSQL)
if err != nil {
log.Println("read")
return err
}
defer readStmt.Close()
insertStmt, err := db.Prepare(insertSQL)
if err != nil {
log.Println("insert")
return err
}
defer insertStmt.Close()
db.Exec("delete from " + targetTableName)
rows, err := readStmt.Query()
if err != nil {
return err
}
for rows.Next() {
var (
uid int
username string
createTS int64
)
err := rows.Scan(&uid, &username, &createTS)
if err != nil {
return err
}
pwd := ""
privilege := 0xFFFF
_, err = insertStmt.Exec(uid, username, user.Hash(pwd), privilege, createTS)
if err != nil {
return err
}
}
return nil
}
func QueryByCategory(c string, start, limit int) (ps []Post, err error) {
db, err := da.Connect()
if err != nil {
return
}
// Prepare statement for reading data
stmt, err := db.Prepare(QUERY_CATEGORY_POSTS)
if err != nil {
return
}
defer stmt.Close()
var (
id int
uid int
content string
category category.Category
pubtime int64
title string
description string
keywords string
)
rows, err := stmt.Query(c, start, limit)
for rows.Next() {
err = rows.Scan(&id, &uid, &content, &category.Id, &category.Name, &category.Alias, &pubtime, &title, &description, &keywords)
if err != nil {
return
}
var (
p Post
)
p.Id = id
p.Content = template.HTML(content)
p.Category = category
p.PubTime = time.Unix(pubtime, 0)
p.Title = title
p.Description = description
p.Keywords.Parse(keywords)
ps = append(ps, p)
}
return ps, nil
}
func FindOne(id int) (user User, err error) {
db, err := da.Connect()
if err != nil {
return
}
stmt, err := db.Prepare(FIND_USER)
if err != nil {
return
}
defer stmt.Close()
row := stmt.QueryRow(id)
err = row.Scan(&user.Id, &user.Name, &user.Privilege)
if err != nil {
return
}
return
}
func (c *Category) Save() error {
db, err := da.Connect()
if err != nil {
return err
}
var (
stmt *sql.Stmt
)
stmt, err = db.Prepare(UPDATE_CATEGORY) // ? = placeholder
if err != nil {
return err
}
defer stmt.Close() // Close the statement when we leave main() / the program terminates
_, err = stmt.Exec(c.Name, c.Alias)
if err != nil {
return err
}
return nil
}
func ReadRaw(id int) RawPost {
var (
p RawPost
err error
)
db, err := da.Connect()
if err != nil {
panic(err)
}
// Prepare statement for reading data
stmt, err := db.Prepare(FIND_POSTS)
if err != nil {
panic(err) // proper error handling instead of panic in your app
}
defer stmt.Close()
var (
uid int
content string
category category.Category
pubtime int64
title string
description string
keywords string
)
// "SELECT p.id, p.uid, p.content, p.cid, c.name, c.alias, p.pubtime, p.title, p.description, p.keywords FROM " + TABLE_NAME_POSTS + " as p LEFT JOIN " + TABLE_NAME_CATEGORIES + " as c ON c.id=p.cid WHERE id = ?"
err = stmt.QueryRow(id).Scan(&id, &uid, &content, &category.Id, &category.Name, &category.Alias, &pubtime, &title, &description, &keywords)
if err != nil {
panic(err)
}
p.Id = id
p.Content = content
p.Category = category
p.PubTime = time.Unix(pubtime, 0)
p.Title = title
p.Description = description
p.Keywords.Parse(keywords)
return p
}
func Add(username, pwd string, privilege int) error {
db, err := da.Connect()
if err != nil {
return err
}
// Prepare statement for reading data
stmt, err := db.Prepare(INSERT_USER)
if err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(username, Hash(pwd), privilege, time.Now().Unix())
if err != nil {
return err
}
return nil
}
func Query() ([]Category, error) {
db, err := da.Connect()
if err != nil {
return nil, err
}
var (
stmt *sql.Stmt
)
stmt, err = db.Prepare(QUERY_CATEGORY) // ? = placeholder
if err != nil {
return nil, err
}
defer stmt.Close() // Close the statement when we leave main() / the program terminates
var (
id int
name, alias string
cs []Category
)
rows, err := stmt.Query(0, 10)
if err != nil {
return nil, err
}
for rows.Next() {
err = rows.Scan(&id, &name, &alias)
if err != nil {
panic(err)
}
var (
c Category
)
c.Id = id
c.Name = name
c.Alias = alias
cs = append(cs, c)
}
return cs, nil
}
func Authentication(username string, p int) (bool, error) {
db, err := da.Connect()
if err != nil {
return false, err
}
// Prepare statement for reading data
stmt, err := db.Prepare(FIND_PRIVILEGE)
if err != nil {
return false, err
}
defer stmt.Close()
var privilege int
// todo load salt form conf
err = stmt.QueryRow(username).Scan(&privilege)
if err != nil {
return false, err
}
return privilege&p > 0, nil
}
func importPosts() error {
var (
targetTableName = "blog_posts"
sourceTableName = "huangjin_blog"
readSQL = "SELECT id, alias, content, category, pubtime, title, description, tags FROM " + sourceTableName
insertSQL = "INSERT INTO " + targetTableName + "(id, alias, uid, content, cid, pubtime, title, description, keywords) VALUES (?,?,?,?,?,?,?,?,?)"
)
db, err := da.Connect()
if err != nil {
return err
}
readStmt, err := db.Prepare(readSQL)
if err != nil {
return err
}
defer readStmt.Close()
db.Exec("delete from " + targetTableName)
insertStmt, err := db.Prepare(insertSQL)
if err != nil {
log.Println("insert")
return err
}
defer insertStmt.Close()
rows, err := readStmt.Query()
if err != nil {
return err
}
for rows.Next() {
var (
id, uid, pubtime, cid int64
alias, category, content, title, description, tags string
)
err := rows.Scan(&id, &alias, &content, &category, &pubtime, &title, &description, &tags)
switch category {
case "it":
cid = 1
case "chat":
cid = 2
case "live":
cid = 3
default:
cid = 0
}
uid = 1
if err != nil {
return err
}
_, err = insertStmt.Exec(id, alias, uid, content, cid, pubtime, title, description, tags)
if err != nil {
return err
}
}
return nil
}