Class: Vnews::Sql

Inherits:
Object
  • Object
show all
Defined in:
lib/vnews/sql.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config = {}) ⇒ Sql

Returns a new instance of Sql.



27
28
29
30
31
32
 
# File 'lib/vnews/sql.rb', line 27

def initialize(config = {})
  config = Vnews::Sql.symbolize_config(config)
  defaults = {:host => 'localhost',  :database => 'vnews', :username => 'root', :password => nil}
  @config = defaults.update(config)
  @client = Mysql2::Client.new @config
end

Instance Attribute Details

#client ⇒ Object

Returns the value of attribute client.



4
5
6
 
# File 'lib/vnews/sql.rb', line 4

def client
  @client
end

#config ⇒ Object

Returns the value of attribute config.



4
5
6
 
# File 'lib/vnews/sql.rb', line 4

def config
  @config
end

Class Method Details

.create_db(config) ⇒ Object 



11
12
13
14
15
16
17
 
# File 'lib/vnews/sql.rb', line 11

def self.create_db(config)
  config = symbolize_config(config)
  create_sql = File.join(File.dirname(__FILE__), '..', 'create.sql')
  passwordarg = config[:password] ? ("-p #{shell_escape(config[:password])}") : ''
  puts `mysqladmin -h #{shell_escape config[:host]} -u #{shell_escape config[:username]} #{passwordarg} create #{shell_escape config[:database]}`
  puts `mysql -h #{shell_escape config[:host]} -D #{shell_escape config[:database]} -u #{shell_escape config[:username]} #{passwordarg} < #{create_sql}`
end

.shell_escape(string) ⇒ Object 



6
7
8
9
 
# File 'lib/vnews/sql.rb', line 6

def self.shell_escape(string)
  require 'shellwords'
  Shellwords.shellescape(string)
end

.symbolize_config(config) ⇒ Object 



19
20
21
22
23
24
25
 
# File 'lib/vnews/sql.rb', line 19

def self.symbolize_config(config)
  config = config.inject({}) do |memo, (key, value)|
    memo[key.to_sym] = value
    memo
  end
  config
end

Instance Method Details

#configured_feeds_folders ⇒ Object 



91
92
93
 
# File 'lib/vnews/sql.rb', line 91

def configured_feeds_folders
  @client.query("SELECT feed,folder from feeds_folders order by folder asc").map {|x| [x['feed'], x['folder']]}
end

#configured_folders ⇒ Object 



87
88
89
 
# File 'lib/vnews/sql.rb', line 87

def configured_folders
  folders = @client.query("SELECT distinct(folder) from feeds_folders order by folder asc")
end

#delete_feed(feed_url) ⇒ Object 



48
49
50
51
52
 
# File 'lib/vnews/sql.rb', line 48

def delete_feed(feed_url)
  @client.query "DELETE from feeds where feed_url = '#{e feed_url}'"
  # Delete all unstarred items from these feeds
  @client.query "DELETE from items where feed = '#{e feed_url}' and starred = false"
end

#delete_feed_folder(feed, folder) ⇒ Object 



54
55
56
 
# File 'lib/vnews/sql.rb', line 54

def delete_feed_folder(feed, folder)
  @client.query "DELETE from feeds_folders where feed = '#{e feed}' and folder = '#{e folder}'"
end

#delete_feed_items(feed_url) ⇒ Object 



44
45
46
 
# File 'lib/vnews/sql.rb', line 44

def delete_feed_items(feed_url)
  puts @client.query("DELETE from items where feed = '#{e feed_url}'")
end

#delete_item(guid) ⇒ Object 



172
173
174
 
# File 'lib/vnews/sql.rb', line 172

def delete_item(guid)
  @client.query "DELETE from items where guid = '#{e guid}'" 
end

#e(value) ⇒ Object 



181
182
183
184
 
# File 'lib/vnews/sql.rb', line 181

def e(value)
  return unless value
  @client.escape(value)
end

#feed_by_title(feed_title) ⇒ Object 



128
129
130
131
 
# File 'lib/vnews/sql.rb', line 128

def feed_by_title(feed_title)
  query = "SELECT * from feeds where title = '#{e feed_title}'"
  @client.query(query).first["feed_url"]
end

#feed_items(feed_title) ⇒ Object

Not perfect because some feeds may have dup titles, but ok for now



121
122
123
124
125
126
 
# File 'lib/vnews/sql.rb', line 121

def feed_items(feed_title) 
  # update last_viewed_at 
  @client.query "UPDATE feeds SET last_viewed_at = now() where title = '#{e feed_title}'"
  query = "SELECT items.title, guid, feed, feed_title, pub_date, word_count, starred, unread from items where items.feed_title = '#{e feed_title}' order by pub_date asc"
  @client.query(query)
end

#feeds(order) ⇒ Object 



96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/vnews/sql.rb', line 96

def feeds(order)
  if order == 0 
    # "feeds.title asc" 
    @client.query("SELECT feeds.*, count(i.unread) as item_count from feeds 
                  left outer join items i on i.feed = feeds.feed_url
                  group by feeds.feed_url
                  order by feeds.title asc") 
  else
    @client.query("SELECT feeds.*, feeds.num_items_read as item_count from feeds 
                  order by num_items_read desc, title asc") 
  end
end

#feeds_in_folder(folder) ⇒ Object 



109
110
111
112
113
114
115
116
117
118
 
# File 'lib/vnews/sql.rb', line 109

def feeds_in_folder(folder)
  case folder
  when "All"
    @client.query("SELECT feed_url from feeds order by title asc").map {|x| x['feed_url']}
  when "Starred"
    return []
  else
    @client.query("SELECT feed from feeds_folders ff where ff.folder = '#{e folder}'").map {|x| x['feed']}
  end
end

#folder_items(folder) ⇒ Object 



133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/vnews/sql.rb', line 133

def folder_items(folder) 
  query = case folder 
          when 'Starred' 
            "SELECT items.title, items.guid, items.feed, 
            items.feed_title, items.pub_date, items.word_count, items.starred, items.unread from items 
                  where items.starred = true order by items.starred_at, items.pub_date asc"
          when 'All' 
            "SELECT items.title, items.guid, items.feed, 
            items.feed_title, items.pub_date, items.word_count, items.starred, items.unread from items 
                  order by items.pub_date asc limit 10000"
          else 
            # update last_viewed_at 
            @client.query "UPDATE feeds_folders SET last_viewed_at = now() where folder = '#{e folder}'"

            "SELECT items.title, items.guid, items.feed, 
            items.feed_title, items.pub_date, items.word_count, items.starred, items.unread from items 
                  left join feeds_folders ff on  ff.feed = items.feed
                  where ff.folder = '#{e folder}' order by items.pub_date asc"
          end
  @client.query query
end

#folders ⇒ Object

queries:



77
78
79
80
81
82
83
84
85
 
# File 'lib/vnews/sql.rb', line 77

def folders
  all = @client.query("SELECT 'All' as folder, count(*) as count from items").first
  starred = @client.query("SELECT 'Starred' as folder, count(*) as count from items where items.starred = true").first
  folders = @client.query("SELECT folder, count(*) as count from feeds_folders 
                inner join items i on i.feed = feeds_folders.feed
                group by folder order by folder")
  folders = [all, starred] + folders.to_a 
  folders
end

#insert_feed(title, feed_url, link, folder = nil) ⇒ Object 



34
35
36
37
38
39
40
41
42
 
# File 'lib/vnews/sql.rb', line 34

def insert_feed(title, feed_url, link, folder=nil)
  if folder.nil?
    folder = 'Misc'
  end
  @client.query "INSERT IGNORE INTO feeds (title, feed_url, link) VALUES ('#{e title}', '#{e feed_url}', '#{e link}')"
  if folder
    @client.query "INSERT IGNORE INTO feeds_folders (feed, folder) VALUES ('#{e feed_url}', '#{e folder}')"
  end
end

#insert_item(item) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/vnews/sql.rb', line 58

def insert_item(item)
  # not sure if this is efficient
  @client.query "DELETE from items WHERE guid = '#{e item[:guid]}' and feed = '#{e item[:feed_title]}'"
  @client.query "INSERT IGNORE INTO items (guid, feed, feed_title, title, link, pub_date, author, text, word_count) 
    VALUES (
    '#{e item[:guid]}', 
    '#{e item[:feed]}', 
    '#{e item[:feed_title]}', 
    '#{e item[:title]}', 
    '#{e item[:link]}', 
    '#{item[:pub_date]}',  
    '#{e item[:author]}',  
    '#{e item[:content][:text]}',
    '#{item[:content][:text].scan(/\S+/).size}'
    )"
end

#search_items(term) ⇒ Object 



176
177
178
179
 
# File 'lib/vnews/sql.rb', line 176

def search_items(term)
  query = "select items.*, MATCH(title, text) against('#{e term}') as score from items where MATCH(title, text) against('#{e term}') order by pub_date asc"
  @client.query query
end

#show_item(guid, inc_read_count = false) ⇒ Object 



155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/vnews/sql.rb', line 155

def show_item(guid, inc_read_count=false)
  # mark item as read
  @client.query "UPDATE items set unread = false where guid = '#{e guid}'"

  if inc_read_count
    # increment the read count for the feed 
    @client.query "UPDATE feeds set num_items_read = num_items_read + 1 where feed_url = (select feed from items where items.guid = '#{e guid}' limit 1)"
  end

  query = "SELECT items.* from items where guid = '#{e guid}'"
  @client.query query
end

#star_item(guid, star = true) ⇒ Object 



168
169
170
 
# File 'lib/vnews/sql.rb', line 168

def star_item(guid, star=true)
  @client.query "UPDATE items set starred = #{star}, starred_at = now() where guid = '#{e guid}'"
end