Class: Termtter::Storage::SQLite3
- Inherits:
-
Object
- Object
- Termtter::Storage::SQLite3
- 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
- #create_table ⇒ Object
- #find_id(id) ⇒ Object
- #find_text(text = '') ⇒ Object
- #find_user(user = "") ⇒ Object
- #find_user_id(user_id) ⇒ Object
-
#initialize(file = Termtter::CONF_DIR + '/storage.db') ⇒ SQLite3
constructor
A new instance of SQLite3.
- #insert(status) ⇒ Object
- #name ⇒ Object
- #size ⇒ Object
- #update(status) ⇒ Object
- #update_user(user_id, screen_name) ⇒ Object
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 |