/vendor/github.com/depechebot/depechebot/model/sqlite/sqlite.go
Go | 289 lines | 219 code | 54 blank | 16 comment | 57 complexity | 596d5ebc8cf059fff5e78583c3ab013f MD5 | raw file
Possible License(s): BSD-3-Clause
- package sqlite
- import (
- "database/sql"
- "encoding/json"
- "errors"
- dbot "github.com/depechebot/depechebot"
- )
- type Model struct {
- db *sql.DB
- //tableName string
- //chats []*dbot.Chat
- }
- func NewModel(db *sql.DB) Model {
- return Model{db: db}
- }
- // Init initializes model.
- // num is the number of existing chats.
- func (m Model) Init() (chatIDs []dbot.ChatID, err error) {
- err = m.db.Ping()
- if err != nil {
- return nil, err
- }
- // slowing this model down cause of this bug:
- // https://github.com/mattn/go-sqlite3/issues/274
- m.db.SetMaxOpenConns(1)
- err = m.createTable()
- if err != nil {
- return nil, err
- }
- const sqlstr = `SELECT chat_id from ` + `chat`
- q, err := m.db.Query(sqlstr)
- if err != nil {
- return nil, err
- }
- defer q.Close()
- var chatID dbot.ChatID
- chatIDs = []dbot.ChatID{}
- for q.Next() {
- err = q.Scan(&chatID)
- if err != nil {
- return nil, err
- }
- chatIDs = append(chatIDs, chatID)
- }
- return chatIDs, nil
- }
- func (m Model) createTable() error {
- var err error
- const sqlstr = `CREATE TABLE IF NOT EXISTS ` +
- `chat` +
- ` (
- primary_id INTEGER NOT NULL PRIMARY KEY,
- chat_id BIGINT UNIQUE NOT NULL,
- type TEXT NOT NULL,
- abandoned INTEGER NOT NULL,
- user_id INTEGER NOT NULL,
- user_name TEXT NOT NULL DEFAULT '',
- first_name TEXT NOT NULL,
- last_name TEXT NOT NULL,
- open_time DATETIME NOT NULL,
- last_time DATETIME NOT NULL,
- state TEXT NOT NULL,
- params TEXT NOT NULL
- );
- `
- _, err = m.db.Exec(sqlstr)
- return err
- }
- // Exists determines if the Chat exists in the database.
- func (m Model) Exists(c *dbot.Chat) (exists bool, err error) {
- var cnt int
- var sqlstr = `SELECT count(*) as count from ` + `chat` + ` where chat_id = ?`
- err = m.db.QueryRow(sqlstr, c.ChatID).Scan(&cnt)
- return cnt != 0, err
- }
- // Insert inserts chat to the database.
- // Sets c.PrimaryID.
- func (m Model) Insert(c *dbot.Chat) error {
- var err error
- const sqlstr = `INSERT INTO chat (` +
- `chat_id, type, abandoned, user_id, user_name, first_name, last_name, open_time, last_time, state, params` +
- `) VALUES (` +
- `?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?` +
- `)`
- state, err := json.Marshal(c.State)
- if err != nil {
- return err
- }
- params, err := json.Marshal(c.Params)
- if err != nil {
- return err
- }
- res, err := m.db.Exec(sqlstr, c.ChatID, c.Type, c.Abandoned, c.UserID, c.UserName,
- c.FirstName, c.LastName, c.OpenTime, c.LastTime, string(state), string(params))
- if err != nil {
- return err
- }
- id, err := res.LastInsertId()
- if err != nil {
- return err
- }
- c.PrimaryID = int(id)
- return nil
- }
- // Update updates the Chat in the database.
- func (m Model) Update(c *dbot.Chat) error {
- var err error
- const sqlstr = `UPDATE chat SET ` +
- `primary_id = ?, type = ?, abandoned = ?, user_id = ?, user_name = ?, first_name = ?, last_name = ?, open_time = ?, last_time = ?, state = ?, params = ?` +
- ` WHERE chat_id = ?`
- state, err := json.Marshal(c.State)
- if err != nil {
- return err
- }
- params, err := json.Marshal(c.Params)
- if err != nil {
- return err
- }
- _, err = m.db.Exec(sqlstr, c.PrimaryID, c.Type, c.Abandoned, c.UserID, c.UserName,
- c.FirstName, c.LastName, c.OpenTime, c.LastTime, string(state), string(params), c.ChatID)
- return err
- }
- // Save saves the Chat to the database.
- // Prefer Update() if you know that chat exists.
- func (m Model) Save(c *dbot.Chat) error {
- exists, err := m.Exists(c)
- if err != nil {
- return err
- }
- if exists {
- return m.Update(c)
- }
- return m.Insert(c)
- }
- // Delete deletes the Chat from the database.
- func (m Model) Delete(c *dbot.Chat) error {
- var err error
- const sqlstr = `DELETE FROM chat WHERE chat_id = ?`
- _, err = m.db.Exec(sqlstr, c.ChatID)
- return err
- }
- // ChatByPrimaryID retrieves a chat by primaryID.
- func (m Model) ChatByPrimaryID(primaryID int) (*dbot.Chat, error) {
- var err error
- var state, params string
- const sqlstr = `SELECT ` +
- `primary_id, chat_id, type, abandoned, user_id, user_name, first_name, last_name, open_time, last_time, state, params ` +
- `FROM chat ` +
- `WHERE primary_id = ?`
- c := dbot.Chat{}
- err = m.db.QueryRow(sqlstr, primaryID).Scan(&c.PrimaryID, &c.ChatID, &c.Type, &c.Abandoned, &c.UserID, &c.UserName,
- &c.FirstName, &c.LastName, &c.OpenTime, &c.LastTime, &state, ¶ms)
- if err != nil {
- if err == sql.ErrNoRows {
- return nil, errors.New("chat not found (use Model.Exist() before if not sure)")
- } else {
- return nil, err
- }
- }
- err = json.Unmarshal([]byte(state), &c.State)
- if err != nil {
- return nil, err
- }
- err = json.Unmarshal([]byte(params), &c.Params)
- if err != nil {
- return nil, err
- }
- return &c, nil
- }
- // ChatByChatID retrieves a chat by chatID.
- func (m Model) ChatByChatID(chatID dbot.ChatID) (*dbot.Chat, error) {
- var err error
- var state, params string
- const sqlstr = `SELECT ` +
- `primary_id, chat_id, type, abandoned, user_id, user_name, first_name, last_name, open_time, last_time, state, params ` +
- `FROM chat ` +
- `WHERE chat_id = ?`
- c := dbot.Chat{}
- err = m.db.QueryRow(sqlstr, chatID).Scan(&c.PrimaryID, &c.ChatID, &c.Type, &c.Abandoned, &c.UserID, &c.UserName,
- &c.FirstName, &c.LastName, &c.OpenTime, &c.LastTime, &state, ¶ms)
- if err != nil {
- if err == sql.ErrNoRows {
- return nil, errors.New("chat not found (use Model.Exist() before if not sure)")
- } else {
- return nil, err
- }
- }
- err = json.Unmarshal([]byte(state), &c.State)
- if err != nil {
- return nil, err
- }
- err = json.Unmarshal([]byte(params), &c.Params)
- if err != nil {
- return nil, err
- }
- return &c, nil
- }
- // ChatsByParam retrieves chats with chat.Params matching param.
- func (m Model) ChatsByParam(param string) ([]*dbot.Chat, error) {
- var err error
- var state, params string
- const sqlstr = `SELECT ` +
- `primary_id, chat_id, type, abandoned, user_id, user_name, first_name, last_name, open_time, last_time, state, params ` +
- `FROM chat ` +
- `WHERE ` +
- `params like "%" || ? || "%"`
- q, err := m.db.Query(sqlstr, param)
- if err != nil {
- if err == sql.ErrNoRows {
- return []*dbot.Chat{}, nil
- } else {
- return nil, err
- }
- }
- defer q.Close()
- chats := []*dbot.Chat{}
- for q.Next() {
- c := dbot.Chat{}
- err = q.Scan(&c.PrimaryID, &c.ChatID, &c.Type, &c.Abandoned, &c.UserID, &c.UserName,
- &c.FirstName, &c.LastName, &c.OpenTime, &c.LastTime, &state, ¶ms)
- if err != nil {
- return nil, err
- }
- err = json.Unmarshal([]byte(state), &c.State)
- if err != nil {
- return nil, err
- }
- err = json.Unmarshal([]byte(params), &c.Params)
- if err != nil {
- return nil, err
- }
- chats = append(chats, &c)
- }
- return chats, nil
- }