YnZUWhSQ

1#! /bin/bash
2function charge_files ()
3{
4  database="test.db"
5  file="Prueba"
6  file2="def"
7  schema="esquema" #schema sqlite3
8  XML="Telegramas.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=`expr $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  `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>