Class: Termtter::Storage::SQLite3

Inherits:
Object
  • Object
show all
Defined in:
lib/plugins/storage/sqlite3.rb

Constant Summary collapse

CREATE_TABLE = 
<<-SQL
CREATE TABLE IF NOT EXISTS user (
id          int NOT NULL,
screen_name text,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS post (
post_id          int NOT NULL,  -- twitter側のpostのid
created_at	     int,    	    -- 日付(RubyでUNIX時間に変換)
in_reply_to_status_id int, 	    -- あったほうがよいらしい
in_reply_to_user_id int,  	    -- あったほうがよいらしい
post_text text,
user_id int NOT NULL,
PRIMARY KEY (post_id)
);
SQL
FIND_USER_ID = 
<<-EOS
select id, screen_name
 from user where id = ?
EOS
FIND_ID = 
<<-EOS
select created_at, screen_name, post_text, in_reply_to_status_id, post_id, user_id
 from post inner join user on post.user_id = user.id where post_id = ?
EOS
FIND = 
<<-EOS
select created_at, screen_name, post_text, in_reply_to_status_id, post_id, user_id
 from post inner join user on post.user_id = user.id where post_text like '%' || ? || '%'
EOS
FIND_USER = 
<<-EOS
select created_at, screen_name, post_text, in_reply_to_status_id, post_id, user_id 
from post inner join user on post.user_id = user.id where 
EOS

Instance Method Summary collapse

Constructor Details

#initialize(file = Termtter::CONF_DIR + '/storage.db') ⇒ SQLite3

Returns a new instance of SQLite3.



9
10
11
12
13
 
# File 'lib/plugins/storage/sqlite3.rb', line 9

def initialize(file = Termtter::CONF_DIR + '/storage.db')
  @db = ::SQLite3::Database.new(file)
  @db.type_translation = true
  create_table
end

Instance Method Details

#create_table ⇒ Object 



35
36
37
 
# File 'lib/plugins/storage/sqlite3.rb', line 35

def create_table
  @db.execute_batch(CREATE_TABLE)
end

#find_id(id) ⇒ Object 



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/plugins/storage/sqlite3.rb', line 86

def find_id(id)
  result = nil
  @db.execute(FIND_ID, id) do |created_at, screen_name, post_text, in_reply_to_status_id, post_id, user_id|
    result = Termtter::ActiveRubytter.new({
        :id => post_id,
        :created_at => created_at,
        :text => post_text,
        :in_reply_to_status_id => in_reply_to_status_id,
        :in_reply_to_user_id => nil,
        :user => {
          :id => user_id,
          :screen_name => screen_name
        }
      })
  end
  result
end

#find_text(text = '') ⇒ Object 



108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/plugins/storage/sqlite3.rb', line 108

def find_text(text = '')
  result = []
  @db.execute(FIND, text) do |created_at, screen_name, post_text, in_reply_to_status_id, post_id, user_id|
    created_at = Time.at(created_at).to_s
    result << Termtter::ActiveRubytter.new({
        :id => post_id,
        :created_at => created_at,
        :text => post_text,
        :in_reply_to_status_id => in_reply_to_status_id,
        :in_reply_to_user_id => nil,
        :user => {
          :id => user_id,
          :screen_name => screen_name
        }
      })
  end
  result
end

#find_user(user = "") ⇒ Object 



131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/plugins/storage/sqlite3.rb', line 131

def find_user(user = "")
  result = []
  sql = FIND_USER + user.split(' ').map!{|que| que.gsub(/(\w+)/, 'screen_name like \'%\1%\'')}.join(' or ')
  @db.execute(sql) do |created_at, screen_name, post_text, in_reply_to_status_id, post_id, user_id|
    created_at = Time.at(created_at).to_s
    result << Termtter::ActiveRubytter.new({
        :id => post_id,
        :created_at => created_at,
        :text => post_text,
        :in_reply_to_status_id => in_reply_to_status_id,
        :in_reply_to_user_id => nil,
        :user => {
          :id => user_id,
          :screen_name => screen_name
        }
      })
  end
  result
end

#find_user_id(user_id) ⇒ Object 



61
62
63
64
65
66
67
 
# File 'lib/plugins/storage/sqlite3.rb', line 61

def find_user_id(user_id)
  result = nil
  @db.execute(FIND_USER_ID, user_id) do |id, screen_name|
    result = { :id => id, :screen_name => screen_name}
  end
  result
end

#insert(status) ⇒ Object 



69
70
71
72
73
74
75
76
77
78
79
80
 
# File 'lib/plugins/storage/sqlite3.rb', line 69

def insert(status)
  return nil unless status[:text]
  @db.execute(
    "insert into post values(?,?,?,?,?,?)",
    status[:post_id],
    status[:created_at],
    status[:in_reply_to_status_id],
    status[:in_reply_to_user_id],
    status[:text],
    status[:user_id])
  update_user(status[:user_id], status[:screen_name])
end

#name ⇒ Object 



15
16
17
 
# File 'lib/plugins/storage/sqlite3.rb', line 15

def name
  "sqlite3"
end

#size ⇒ Object 



151
152
153
 
# File 'lib/plugins/storage/sqlite3.rb', line 151

def size
  @db.get_first_value("select count(*) from post").to_i
end

#update(status) ⇒ Object 



39
40
41
42
43
44
45
46
47
 
# File 'lib/plugins/storage/sqlite3.rb', line 39

def update(status)
  @db.transaction
  begin
    return nil if find_id(status[:post_id])
    insert(status)
  ensure
    @db.commit
  end
end

#update_user(user_id, screen_name) ⇒ Object 



49
50
51
52
53
54
55
 
# File 'lib/plugins/storage/sqlite3.rb', line 49

def update_user(user_id, screen_name)
  return nil if find_user_id(user_id)
  @db.execute(
    "insert into user values(?,?)",
    status[:user_id],
    status[:screen_name])
end