· 8 years ago · Nov 21, 2017, 08:10 PM
1#! /bin/bash
2function charge_files ()
3{
4 database="test.db"
5 file="Prueba"
6 file2="def"
7 schema="esquema" #schema sqlite3
8 XML="Prueba.xml" #telegramas.xml
9 tempschema="tempschema"
10 if [ -f $XML ]; then
11 echo "============================="
12 echo "| XML CHARGED |"
13 echo "============================="
14 else
15 echo "============================="
16 echo "| XML NOT CHARGED |"
17 echo "============================="
18 exit 1
19 fi
20
21}
22
23function extract ()
24{
25 host=''
26 i=0
27
28 while IFS= read -r line; do
29 # Find if it is a RecordtStart line
30 if [ "$(echo "$line" | grep -c "RecordStart")" -eq 1 ]
31 then
32 # If host == '', then it is the first host we see.
33 # Otherwise, we are changing host, so print an empty line
34 if [ "$host" != '' ]
35 then
36 echo ""
37 fi
38
39 # Collect the host information
40 connectioname=$(echo "$line" | awk '{print $5}' | cut -d'=' -f2)
41
42 # Collect the ConnectorType information
43 connectortype=$(echo "$line" | awk '{print $7}' | cut -d";" -f2 | cut -d"=" -f2)
44
45 # Done with this loop in the while, move on to the next
46 continue
47 fi
48
49 # Find if it is a Telegram line
50 if [ "$(echo "$line" | grep -c "Telegram")" -eq 1 ]
51 then
52 # Collect the Timestamp information
53 timestamp=$(echo "$line" | awk '{print $2}' | cut -d"." -f1 | cut -d"=" -f2)
54
55 # Collect the service information
56 service=$(echo "$line" | awk '{print $3}' | cut -d"=" -f2)
57
58 # Collect the FrameFormat information
59 frameformat=$(echo "$line" | awk '{print $4}' | cut -d"=" -f2)
60
61 # Collect the RawData information
62 RawDatahost=$(echo "$line" | awk '{print $5}' | cut -c 36-39)
63
64 #Collect the RawDate information2
65 RawDatahost3=$(echo "$line" | awk '{print $5}' | cut -c 50-53)
66
67 # Print the information
68 i=$((i + 1))
69 echo "$connectioname $connectortype $timestamp $service $frameformat $((16#$RawDatahost)) $((16#$RawDatahost3))" >> $file
70
71 # Done with this loop in the while, move on to the next
72 continue
73 fi
74
75 done <$XML
76}
77function clean() {
78 #Clean the file
79 cat $file | tr -d '"' | tr -s " ">> $file2
80 cat $file2 | tr ' ' ',' >> definitivo
81}
82function create_schema(){
83
84 if [ -f "$schema" ]; then
85 echo "============================="
86 echo "| LOADED SCHEMA |"
87 echo "============================="
88 else
89 echo 'CREATE TABLE test (
90 KKID INTEGER PRIMARY KEY,
91 conection VARCHAR(20) NOT NULL,
92 ip VARCHAR(20) NOT NULL,
93 time DATETIME NOT NULL DEFAULT (strftime("%Y-%m-%d %H:%M:%S")),
94 service VARCHAR(20) NOT NULL,
95 frameformat VARCHAR(20) NOT NULL,
96 id_dispositivo VARCHAR(20) NOT NULL,
97 id_valor VARCHAR(20) NOT NULL
98 );' >> $schema
99 fi
100
101 if [ -f "$tempschema" ]; then
102 echo "============================="
103 echo "| LOADED TEMPSCHEMA |"
104 echo "============================="
105 else
106 echo 'create table temp (
107conection VARCHAR(20) NOT NULL,
108ip VARCHAR(20) NOT NULL,
109time DATETIME NOT NULL DEFAULT (strftime("%Y-%m-%d %H:%M:%S")),
110service VARCHAR(20) NOT NULL,
111frameformat VARCHAR(20) NOT NULL,
112id_dispositivo VARCHAR(20) NOT NULL,
113id_valor VARCHAR(20) NOT NULL
114);
115.separator ","
116.import ./definitivo temp
117.exit' >> $tempschema
118 fi
119
120}
121
122function upload() {
123 #upload the schema to sqlite3 database
124 echo "$(sqlite3 "$database" < "$schema")"
125
126 #Create a temp table with the script
127 echo "$(sqlite3 $database < $tempschema)"
128
129 #upload the csv to a temp table
130 echo -e ".separator ","\n.import definitivo temp" | sqlite3 $database
131
132 #make an insert from the temp to the database to get the atribute autoincrement
133 echo "$(sqlite3 $database "insert into test (conection, ip, time, service, frameformat, id_dispositivo, id_valor)SELECT * FROM temp;")"
134
135 #delate de table temp
136 echo "$(sqlite3 $database "drop table if exists temp;")"
137
138 #remove duplicated fields
139 echo "$(sqlite3 $database "DELETE FROM test WHERE oid NOT IN (SELECT min(oid) FROM test GROUP BY conection, ip, time, service, frameformat, id_dispositivo, id_valor)")"
140
141 rm definitivo
142 rm "$file"
143 rm "$file2"
144
145}
146charge_files
147extract
148clean
149create_schema
150upload
151
152<CommunicationLog xmlns="http:telegrams">
153<RecordStart Timestamp="" Mode="" Host="" ConnectionName="" ConnectionOptions="" ConnectorType="" MediumType="" />
154<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
155<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
156
157<RecordStop Timestamp="" />
158<RecordStart Timestamp="" Mode="" Host="" ConnectionName="" ConnectionOptions="" ConnectorType="" MediumType="" />
159<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
160<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
161<RecordStop Timestamp="" />
162</CommunicationLog>
163
164"$(echo "$line" | grep -c "RecordStart")" -eq 1
165
166for ((i=0;i<999;i++)); do echo RecordStart; done > data
167
168while IFS= read -r line; do
169 if [ "$(echo "$line" | grep -c "RecordStart")" -eq 1 ]
170 then
171 echo true
172 fi
173done
174
175$ time ./slow.sh < data | wc -l
176999
177
178real 0m2.311s
179user 0m0.036s
180sys 0m0.240s
181
182while IFS= read -r line; do
183 case $line in
184 *RecordStart*)
185 echo true
186 ;;
187 esac
188done
189
190$ time ./fast.sh < data | wc -l
191999
192
193real 0m0.041s
194user 0m0.028s
195sys 0m0.008s
196
197#! /usr/bin/perl
198use strict;
199use warnings;
200
201my ($name, $type);
202
203while (<>)
204{
205 if (/bRecordStartb.*bConnectionName="([^"]*)".*bConnectorType="([^"]*)"/) {
206 ($name, $type) = ($1, $2);
207 }
208 if (/bTelegramb.*bTimestamp="([^"]*)".*bService="([^"]*)".*bFrameFormat="([^"]*)".*bRawData="([^"]*)/) {
209 print "$name $type $1 $2 $3 ", hex("0x$4"), "n";
210 }
211}