Beispiel #1
0
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
}
Beispiel #2
0
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
}
Beispiel #3
0
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
}
Beispiel #4
0
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
}
Beispiel #5
0
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
}
Beispiel #6
0
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
}
Beispiel #7
0
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)
}
Beispiel #8
0
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

}
Beispiel #9
0
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
}
Beispiel #10
0
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
}
Beispiel #11
0
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
}
Beispiel #12
0
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
}
Beispiel #13
0
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
}
Beispiel #14
0
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

}
Beispiel #15
0
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

}
Beispiel #16
0
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
}