1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
# web/lib/api/v4/search.rb
class Taginfo < Sinatra::Base
api(4, 'search/by_value', {
:description => 'Search for tags by value.',
:parameters => { :query => 'Value to search for (substring search, required).' },
:sort => %w( count_all key value ),
:paging => :optional,
:result => paging_results([
[:key, :STRING, 'Key'],
[:value, :STRING, 'Value'],
[:count_all, :INT, 'Number of objects in the database with this tag.']
]),
:example => { :query => 'foo', :page => 1, :rp => 10 },
:ui => '/search?q=foo#values'
}) do
query = params[:query]
total = @db.count('search.ftsearch').
condition_if("value MATCH ?", query).
get_first_value().to_i
res = @db.select('SELECT * FROM search.ftsearch').
condition_if("value MATCH ?", query).
order_by(@ap.sortname, @ap.sortorder) { |o|
o.count_all
o.key
o.value
}.
paging(@ap).
execute()
return {
:page => @ap.page,
:rp => @ap.results_per_page,
:total => total,
:data => res.map{ |row| {
:key => row['key'],
:value => row['value'],
:count_all => row['count_all'].to_i,
}}
}.to_json
end
api(4, 'search/by_key_and_value', {
:description => 'Search for tags by key and/or value.',
:parameters => { :query => 'Value to search for (substring search, required).' },
:sort => %w( count_all key value ),
:paging => :optional,
:result => paging_results([
[:key, :STRING, 'Key'],
[:value, :STRING, 'Value'],
[:count_all, :INT, 'Number of objects in the database with this tag.']
]),
:example => { :query => 'highway%3Dresidential', :page => 1, :rp => 10 },
:ui => '/search?q=highway%3Dresidential'
}) do
query = params[:query]
(query_key, query_value) = query.split('=', 2)
if query_key == ''
total = @db.execute('SELECT count(*) FROM search.ftsearch WHERE value MATCH ?', query_value)[0][0].to_i
sel = @db.select('SELECT * FROM search.ftsearch WHERE value MATCH ?', query_value)
elsif query_value == ''
total = @db.execute('SELECT count(*) FROM search.ftsearch WHERE key MATCH ?', query_key)[0][0].to_i
sel = @db.select('SELECT * FROM search.ftsearch WHERE key MATCH ?', query_key)
else
total = @db.execute('SELECT count(*) FROM (SELECT * FROM search.ftsearch WHERE key MATCH ? INTERSECT SELECT * FROM search.ftsearch WHERE value MATCH ?)', query_key, query_value)[0][0].to_i
sel = @db.select('SELECT * FROM search.ftsearch WHERE key MATCH ? INTERSECT SELECT * FROM search.ftsearch WHERE value MATCH ?', query_key, query_value)
end
res = sel.
order_by(@ap.sortname, @ap.sortorder) { |o|
o.count_all
o.key
o.value
}.
paging(@ap).
execute()
return {
:page => @ap.page,
:rp => @ap.results_per_page,
:total => total,
:data => res.map{ |row| {
:key => row['key'],
:value => row['value'],
:count_all => row['count_all'].to_i,
}}
}.to_json
end
api(4, 'search/by_keyword', {
:description => 'Search for keys and tags by keyword in wiki pages.',
:parameters => { :query => 'Value to search for (substring search, required).' },
:sort => %w( count_all key value ),
:paging => :optional,
:result => paging_results([
[:key, :STRING, 'Key'],
[:value, :STRING, 'Value']
]),
:example => { :query => 'fire', :page => 1, :rp => 10 },
:ui => '/search?q=fire#fulltext'
}) do
query = params[:query].downcase
total = @db.count('wiki.words').condition("words LIKE ('%' || ? || '%')", query).get_first_value().to_i
sel = @db.select("SELECT key, value FROM wiki.words WHERE words LIKE ('%' || ? || '%')", query)
res = sel.
order_by(@ap.sortname, @ap.sortorder) { |o|
o.key
o.value
}.
paging(@ap).
execute()
return {
:page => @ap.page,
:rp => @ap.results_per_page,
:total => total,
:data => res.map{ |row| {
:key => row['key'],
:value => row['value']
}}
}.to_json
end
end
|