· 9 years ago · May 03, 2017, 07:44 PM
1#Save as GPhoneImporter.rb
2require "CSVReader"
3require "mysql"
4require "cgi"
5class GPhoneImporter
6 include CSVReader
7 def initialize(u,p)
8 @user = u
9 @pass = p
10 con = _connect(u, p)
11
12 str = "USE warmapper;"
13 con.query(str)
14 str = %{
15 CREATE TABLE IF NOT EXISTS gphone_wifitracker (
16 pkey BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
17 ssid VARCHAR(128) NOT NULL,
18 bssid VARCHAR(64) NOT NULL UNIQUE,
19 longitude DOUBLE NOT NULL,
20 latitude DOUBLE NOT NULL,
21 max_signal DOUBLE NOT NULL,
22 capabilities VARCHAR(256),
23 ctime BIGINT UNSIGNED NOT NULL,
24 frequency INT NOT NULL,
25 gps_accuracy DOUBLE,
26 PRIMARY KEY (pkey)
27 );
28 }
29 con.query(str)
30 con.close
31 end
32 def import(file, u, p)
33 con = _connect(u, p)
34 str = "USE warmapper;"
35 con.query(str)
36 csv_each(file){|x|
37 str = %Q{
38 INSERT INTO gphone_wifitracker (
39 ssid,
40 bssid,
41 longitude,
42 latitude,
43 max_signal,
44 capabilities,
45 ctime,
46 frequency,
47 gps_accuracy
48 ) VALUES (
49 '#{CGI.escape(x["SSID"])}',
50 '#{CGI.escape(x["BSSID"])}',
51 #{x["Longitude"]},
52 #{x["Latitude"]},
53 #{x["Signal strength(-dBm)"]},
54 '#{CGI.escape(x["AP Capabilities"])}',
55 #{x["Unix time"]},
56 #{x["Frequency"]},
57 #{x["GPS Accuracy"]}
58 ) ON DUPLICATE KEY UPDATE pkey=pkey;
59 }
60 puts str
61 con.query(str)
62 }
63 con.close
64 end
65 private
66 def _connect(u, p)
67 con = Mysql.connect("127.0.0.1", u, p)
68 end
69end
70puts ARGV[1]
71a = GPhoneImporter.new(ARGV[1], ARGV[2])
72a.import(ARGV[0], ARGV[1], ARGV[2])
73
74
75#save as CSVReader.rb
76module CSVReader
77 def csv_each(file, sep=",")
78 fields = nil
79 if File.exists?(file) then
80 File.open(file, "r"){|fo|
81 if first_line = fo.gets then
82 fields = first_line.split(sep).collect{|x| x.strip}
83 else
84 raise "#{self.class}: File error on file: #{file}"
85 end
86 while inline = fo.gets
87 hsh = {}
88 linearr = inline.split(",").collect{|x| x.strip}
89 fields.each_with_index{|x, i|
90 hsh[x] = linearr[i].strip
91 }
92 yield hsh
93 end
94 }
95 return nil
96 else
97 raise "#{self.class}: File not found: #{file}"
98 end
99 return nil
100 end
101 def read_csv(file, sep=",")
102 out = []
103 csv_each(file, sep){|x|
104 out.push x
105 }
106 return out
107 end
108end