From 0eb36b8f858c690616e552978cc1724d6bb130ff Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Tue, 9 Jan 2018 13:31:22 +0000 Subject: [PATCH] lib/postgres/useragent.sql --- lib/postgres/useragent.sql | 53 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 53 insertions(+) create mode 100644 lib/postgres/useragent.sql diff --git a/lib/postgres/useragent.sql b/lib/postgres/useragent.sql new file mode 100644 index 00000000..3322d9c8 --- /dev/null +++ b/lib/postgres/useragent.sql @@ -0,0 +1,53 @@ +CREATE TEMPORARY TABLE _useragent ( + id INT PRIMARY KEY, + string VARCHAR(500), + description VARCHAR(2000), + type VARCHAR(20), + comment VARCHAR(100), + link1 VARCHAR(1000), + link2 VARCHAR(1000) +); + +DROP TABLE IF EXISTS useragent; +CREATE TABLE IF NOT EXISTS useragent ( + string VARCHAR(500), + description VARCHAR(2000), + comment VARCHAR(100), + link VARCHAR(1000), + is_browser BOOLEAN, + is_link BOOLEAN, + is_download BOOLEAN, + is_proxy BOOLEAN, + is_robot BOOLEAN, + is_spam BOOLEAN +); + +CREATE INDEX useragent_string_idx ON `useragent` (string); +CREATE INDEX useragent_comment_idx ON `useragent` (comment); +CREATE INDEX useragent_is_browser_idx ON `useragent` (is_browser); +CREATE INDEX useragent_is_link_idx ON `useragent` (is_link); +CREATE INDEX useragent_is_download_idx ON `useragent` (is_download); +CREATE INDEX useragent_is_proxy_idx ON `useragent` (is_proxy); +CREATE INDEX useragent_is_robot_idx ON `useragent` (is_robot); +CREATE INDEX useragent_is_spam_idx ON `useragent` (is_spam); + +BEGIN TRANSACTION; +DELETE FROM useragent; +COPY useragent FROM PROGRAM 'xml2csv http://www.user-agents.org/allagents.xml \ +' WITH (format 'text', NULL ''); +-- Type: +-- B = Browser +-- C = Link-, bookmark-, server- checking +-- D = Downloading tool +-- P = Proxy server, web filtering +-- R = Robot, crawler, spider +-- S = Spam or bad bot +INSERT INTO useragent SELECT string,description,comment,link1 + ,CASE WHEN type LIKE '%B%' THEN 1 ELSE 0 END + ,CASE WHEN type LIKE '%C%' THEN 1 ELSE 0 END + ,CASE WHEN type LIKE '%D%' THEN 1 ELSE 0 END + ,CASE WHEN type LIKE '%P%' THEN 1 ELSE 0 END + ,CASE WHEN type LIKE '%R%' THEN 1 ELSE 0 END + ,CASE WHEN type LIKE '%S%' THEN 1 ELSE 0 END +FROM _useragent; +COMMIT; -- 2.47.3