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