UdgerSqlQuery.cs
1/*
2 UdgerParser - Local parser lib
3
4 UdgerParser class parses useragent strings based on a database downloaded from udger.com
5
6
7 author The Udger.com Team (info@udger.com)
8 copyright Copyright (c) Udger s.r.o.
9 license GNU Lesser General Public License
10 link https://udger.com/products/local_parser
11 */
12
13using System;
14
15namespace Udger.Parser
16{
17 class UdgerSqlQuery
18 {
19 public static readonly String SQL_CRAWLER =
20 "SELECT " +
21 "NULL AS client_id, " +
22 "NULL AS class_id, " +
23 "'Crawler' AS ua_class, " +
24 "'crawler' AS ua_class_code, " +
25 "name AS ua, " +
26 "NULL AS ua_engine, " +
27 "ver AS ua_version, " +
28 "ver_major AS ua_version_major, " +
29 "last_seen AS crawler_last_seen, " +
30 "respect_robotstxt AS crawler_respect_robotstxt, " +
31 "crawler_classification AS crawler_category, " +
32 "crawler_classification_code AS crawler_category_code, " +
33 "NULL AS ua_uptodate_current_version, " +
34 "family AS ua_family, " +
35 "family_code AS ua_family_code, " +
36 "family_homepage AS ua_family_homepage, " +
37 "family_icon AS ua_family_icon, " +
38 "NULL AS ua_family_icon_big, " +
39 "vendor AS ua_family_vendor, " +
40 "vendor_code AS ua_family_vendor_code, " +
41 "vendor_homepage AS ua_family_vendor_homepage, " +
42 "'https://udger.com/resources/ua-list/bot-detail?bot=' || REPLACE(family, ' ', '%20') || '#id' || udger_crawler_list.id AS ua_family_info_url " +
43 "FROM " +
44 "udger_crawler_list " +
45 "LEFT JOIN " +
46 "udger_crawler_class ON udger_crawler_class.id = udger_crawler_list.class_id " +
47 "WHERE " +
48 "ua_string = '{0}'";
49
50 public static readonly String SQL_CLIENT =
51 "SELECT " +
52 "ur.rowid, " +
53 "client_id AS client_id, " +
54 "class_id AS class_id, " +
55 "client_classification AS ua_class, " +
56 "client_classification_code AS ua_class_code, " +
57 "name AS ua, " +
58 "engine AS ua_engine, " +
59 "NULL AS ua_version, " +
60 "NULL AS ua_version_major, " +
61 "NULL AS crawler_last_seen, " +
62 "NULL AS crawler_respect_robotstxt, " +
63 "NULL AS crawler_category, " +
64 "NULL AS crawler_category_code, " +
65 "uptodate_current_version AS ua_uptodate_current_version, " +
66 "name AS ua_family, " +
67 "name_code AS ua_family_code, " +
68 "homepage AS ua_family_homepage, " +
69 "icon AS ua_family_icon, " +
70 "icon_big AS ua_family_icon_big, " +
71 "vendor AS ua_family_vendor, " +
72 "vendor_code AS ua_family_vendor_code, " +
73 "vendor_homepage AS ua_family_vendor_homepage, " +
74 "regstring, " +
75 "'https://udger.com/resources/ua-list/browser-detail?browser=' || REPLACE(name, ' ', '%20') AS ua_family_info_url " +
76 "FROM " +
77 "udger_client_regex ur " +
78 "JOIN " +
79 "udger_client_list ON udger_client_list.id = ur.client_id " +
80 "JOIN " +
81 "udger_client_class ON udger_client_class.id = udger_client_list.class_id " +
82 "WHERE " +
83 "ur.rowid={0}";
84
85 private static readonly String OS_COLUMNS =
86 "family AS os_family, " +
87 "family_code AS os_family_code, " +
88 "name AS os, " +
89 "name_code AS os_code, " +
90 "homepage AS os_home_page, " +
91 "icon AS os_icon, " +
92 "icon_big AS os_icon_big, " +
93 "vendor AS os_family_vendor, " +
94 "vendor_code AS os_family_vendor_code, " +
95 "vendor_homepage AS os_family_vedor_homepage, " +
96 "'https://udger.com/resources/ua-list/os-detail?os=' || REPLACE(name, ' ', '%20') AS os_info_url ";
97
98 public static readonly String SQL_OS =
99 "SELECT " +
100 "ur.rowid, " +
101 OS_COLUMNS +
102 "FROM " +
103 "udger_os_regex ur " +
104 "JOIN " +
105 "udger_os_list ON udger_os_list.id = ur.os_id " +
106 "WHERE " +
107 "ur.rowid={0}";
108
109 public static readonly String SQL_CLIENT_OS =
110 "SELECT " +
111 OS_COLUMNS +
112 "FROM " +
113 "udger_client_os_relation " +
114 "JOIN " +
115 "udger_os_list ON udger_os_list.id = udger_client_os_relation.os_id " +
116 "WHERE " +
117 "client_id = {0}";
118
119 private static readonly String DEVICE_COLUMNS =
120 "name AS device_class, " +
121 "name_code AS device_class_code, " +
122 "icon AS device_class_icon, " +
123 "icon_big AS device_class_icon_big, " +
124 "'https://udger.com/resources/ua-list/device-detail?device=' || REPLACE(name, ' ', '%20') AS device_class_info_url ";
125
126 public static readonly String SQL_DEVICE =
127 "SELECT " +
128 "ur.rowid, " +
129 DEVICE_COLUMNS +
130 "FROM " +
131 "udger_deviceclass_regex ur " +
132 "JOIN " +
133 "udger_deviceclass_list ON udger_deviceclass_list.id = ur.deviceclass_id " +
134 "WHERE " +
135 "ur.rowid={0}"
136 ;
137
138 public static readonly String SQL_CLIENT_CLASS =
139 "SELECT " +
140 DEVICE_COLUMNS +
141 "FROM " +
142 "udger_deviceclass_list " +
143 "JOIN " +
144 "udger_client_class ON udger_client_class.deviceclass_id = udger_deviceclass_list.id " +
145 "WHERE " +
146 "udger_client_class.id = {0}";
147
148 private static readonly String IP_COLUMNS =
149 "ip_classification AS ip_classification, " +
150 "ip_classification_code AS ip_classification_code, " +
151 "ip_last_seen AS ip_last_seen, " +
152 "ip_hostname AS ip_hostname, " +
153 "ip_country AS ip_country, " +
154 "ip_country_code AS ip_country_code, " +
155 "ip_city AS ip_city, " +
156 "name AS crawler_name, " +
157 "ver AS crawler_ver, " +
158 "ver_major AS crawler_ver_major, " +
159 "family AS crawler_family, " +
160 "family_code AS crawler_family_code, " +
161 "family_homepage AS crawler_family_homepage, " +
162 "vendor AS crawler_family_vendor, " +
163 "vendor_code AS crawler_family_vendor_code, " +
164 "vendor_homepage AS crawler_family_vendor_homepage, " +
165 "family_icon AS crawler_family_icon, " +
166 "'https://udger.com/resources/ua-list/bot-detail?bot=' || REPLACE(family, ' ', '%20') || '#id' || udger_crawler_list.id AS crawler_family_info_url, " +
167 "last_seen AS crawler_last_seen, " +
168 "crawler_classification AS crawler_category, " +
169 "crawler_classification_code AS crawler_category_code, " +
170 "respect_robotstxt AS crawler_respect_robotstxt ";
171
172 public static readonly String SQL_IP =
173 "SELECT " +
174 IP_COLUMNS +
175 "FROM " +
176 "udger_ip_list " +
177 "JOIN " +
178 "udger_ip_class ON udger_ip_class.id=udger_ip_list.class_id " +
179 "LEFT JOIN " +
180 "udger_crawler_list ON udger_crawler_list.id=udger_ip_list.crawler_id " +
181 "LEFT JOIN " +
182 "udger_crawler_class ON udger_crawler_class.id=udger_crawler_list.class_id " +
183 "WHERE " +
184 "ip = {0} " +
185 "ORDER BY " +
186 "sequence";
187
188 private static readonly String DATACENTER_COLUMNS =
189 "name AS datacenter_name, " +
190 "name_code AS datacenter_name_code, " +
191 "homepage AS datacenter_homepage ";
192
193 public static readonly String SQL_DATACENTER =
194 "SELECT " +
195 DATACENTER_COLUMNS +
196 "FROM " +
197 "udger_datacenter_range " +
198 "JOIN " +
199 "udger_datacenter_list ON udger_datacenter_range.datacenter_id = udger_datacenter_list.id " +
200 "WHERE " +
201 "iplong_from <= {0} AND iplong_to >= {1}";
202
203 public static readonly String SQL_DATACENTER_RANGE6 =
204 "SELECT " +
205 DATACENTER_COLUMNS +
206 "FROM " +
207 "udger_datacenter_range6 " +
208 "JOIN " +
209 "udger_datacenter_list ON udger_datacenter_range6.datacenter_id=udger_datacenter_list.id " +
210 "WHERE " +
211 "iplong_from0 <= {0} AND iplong_to0 >= {1} AND " +
212 "iplong_from1 <= {2} AND iplong_to1 >= {3} AND " +
213 "iplong_from2 <= {4} AND iplong_to2 >= {5} AND " +
214 "iplong_from3 <= {6} AND iplong_to3 >= {7} AND " +
215 "iplong_from4 <= {8} AND iplong_to4 >= {9} AND " +
216 "iplong_from5 <= {10} AND iplong_to5 >= {11} AND " +
217 "iplong_from6 <= {12} AND iplong_to6 >= {13} AND " +
218 "iplong_from7 <= {14} AND iplong_to7 >= {15}";
219
220 public static readonly String SQL_DEVICE_REGEX =
221 "SELECT " +
222 "id," +
223 "regstring " +
224 "FROM " +
225 "udger_devicename_regex " +
226 "WHERE " +
227 "os_family_code='{0}' AND (os_code='-all-' OR os_code='{1}') " +
228 "ORDER BY sequence";
229
230 public static readonly String SQL_DEVICE_NAME_LIST =
231 "SELECT " +
232 "marketname," +
233 "brand_code," +
234 "brand," +
235 "brand_url," +
236 "icon," +
237 "icon_big " +
238 "FROM " +
239 "udger_devicename_list " +
240 "JOIN " +
241 "udger_devicename_brand ON udger_devicename_brand.id=udger_devicename_list.brand_id " +
242 "WHERE " +
243 "regex_id = '{0}' AND code = '{1}'";
244 }
245}