-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathEAN_MySQL_refresh.sh
executable file
·286 lines (273 loc) · 12.6 KB
/
EAN_MySQL_refresh.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
#!/bin/bash
#########################################################################
## Process tested in Windows, using Cygwin ##
## other than the default of the instalation you will need to install: ##
## -> wget ##
## -> unzip ##
## -> md5deep (as there is no md5sum or cksum on Cygwin) ##
## -> database client MySQL ##
## you can select by searching for them in the Cygwin packages during ##
## the install. ##
#########################################################################
### Environment ###
STARTTIME=$(date +%s)
## for Linux: CHKSUM_CMD=md5sum
## for Windows Cygwin you may use alternate: http://md5deep.sourceforge.net
## cksum should be available in all Unix versions, leave it blank to force a refresh everytime it runs
## but that will avoid the overhead and I/O generated by this functionality
CHKSUM_CMD=md5sum
MYSQL_DIR=/usr/bin/
# for simplicity I added the MYSQL bin path to the Windows
# path environment variable, for Windows set it to ""
#MYSQL_DIR=""
# after MySQL 5.6+ we depend on the mysql_config_editor to
# save the connections credentials (host, user, password)
# then we can use: --login-path={name of your connection}
MYSQL_LOGINPATH=local
#MySQL user, password, host (Server)
MYSQL_USER=eanuser
MYSQL_PASS=Passw@rd1
MYSQL_HOST=localhost
MYSQL_DB=eanprod
CID=
APIKEY=
APIKEYSECRET=
# home directory of the user (in our case "eanuser")
HOME_DIR=/home/eanuser
# protocol TCP All, SOCKET Unix only, PIPE Windows only, MEMORY Windows only
MYSQL_PROTOCOL=SOCKET
# 3336 as default,MAC using MAMP is 8889
MYSQL_PORT=3306
## directory under HOME_DIR
FILES_DIR=eanfiles
## retention period in DAYS for the log of ActivePropertyList changes
LOG_DAYS=30
## Import files ###
#####################################
# the list should match the tables ##
# created by create_ean.sql script ##
#####################################
#LANG=es_ES
FILES=(
ActivePropertyList
AirportCoordinatesList
AreaAttractionsList
AttributeList
ChainList
CityCoordinatesList
CountryList
DiningDescriptionList
HotelImageList
NeighborhoodCoordinatesList
ParentRegionList
PointsOfInterestCoordinatesList
PolicyDescriptionList
PropertyAttributeLink
PropertyDescriptionList
PropertyTypeList
RecreationDescriptionList
RegionCenterCoordinatesList
RegionEANHotelIDMapping
RoomTypeList
SpaDescriptionList
WhatToExpectList
#
# minorRev=25 added files
#
PropertyLocationList
PropertyAmenitiesList
PropertyRoomsList
PropertyBusinessAmenitiesList
PropertyNationalRatingsList
PropertyFeesList
PropertyMandatoryFeesList
PropertyRenovationsList
### Special File for Authorized Partners ONLY
#ActivePropertyBusinessModel
## <BusinessModelMask> <Availability Offered>
## 1 Expedia Collect only
## 2 Hotel Collect only
## 3 Both (ETP)
##########################
#
# To Add a language set, use this as a reference
#
#ActivePropertyList_es_ES
#AliasRegionList_es_ES
#AreaAttractionsList_es_ES
#AttributeList_es_ES
#CountryList_es_ES
#DiningDescriptionList_es_ES
#PolicyDescriptionList_es_ES
#PropertyAttributeLink_es_ES
#PropertyDescriptionList_es_ES
#PropertyTypeList_es_ES
#RecreationDescriptionList_es_ES
#RegionList_es_ES
#RoomTypeList_es_ES
#SpaDescriptionList_es_ES
#WhatToExpectList_es_ES
#PropertyLocationList_es_ES
#PropertyAmenitiesList_es_ES
#PropertyRoomsList_es_ES
#PropertyBusinessAmenitiesList_es_ES
#PropertyNationalRatingsList_es_ES
#PropertyFeesList_es_ES
#PropertyMandatoryFeesList_es_ES
#PropertyRenovationsList_es_ES
### file for Authorized Partners ONLY
#ActivePropertyBusinessModel_es_ES
)
## home where the process will execute
#cd C:/data/EAN/DEV/database
## this will be CRONed so it needs the working directory absolute path
## change to your user home directory
cd ${HOME_DIR}
echo "Starting at working directory..."
pwd
## create subdirectory if required
if [ ! -d ${FILES_DIR} ]; then
echo "creating download files directory..."
mkdir ${FILES_DIR}
fi
## all clear, move into the working directory
cd ${HOME_DIR}
echo "Starting at working directory..."
pwd
## create subdirectory if required
if [ ! -d ${FILES_DIR} ]; then
echo "creating download files directory..."
mkdir ${FILES_DIR}
fi
## all clear, move into the working directory
cd ${FILES_DIR}
### Parameters that you may need:
### If you use LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table.
CMD_MYSQL="${MYSQL_DIR}mysql --local-infile=1 --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST} --database=${MYSQL_DB}"
# for version 5.6.6+ you will need this line instead to use stored credentials
#CMD_MYSQL="${MYSQL_DIR}mysql --login-path=${MYSQL_LOGINPATH} --local-infile=1 --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --database=${MYSQL_DB}"
#
### Download Data ###
echo "Downloading files using wget..."
for FILE in ${FILES[@]}
do
## capture the current file checksum
if [ -e ${FILE}.txt ] && [ -n "${CHKSUM_CMD}" ] ; then
echo "File exist $FILE.txt and using chksum command $CHKSUM_CMD... saving checksum for comparison..."
CHKSUM_PREV=`$CHKSUM_CMD $FILE.txt | cut -f1 -d' '`
else
CHKSUM_PREV=0
fi
## download the files via HTTP (no need for https), using time-stamping, -nd no host directories
time=`date +%s`
sig=`echo -n $APIKEY$APIKEYSECRET$time | md5sum -t | awk '{print $1}'`
wget -O$FILE.zip -t 30 --no-verbose -r -N -nd "http://api.ean.com/ean-services/rs/hotel/v3/files?filename=$FILE&cid=$CID&apiKey=$APIKEY&sig=$sig"
## unzip the files, save the exit value to check for errors
## BSD does not support same syntax, but there is no need in MAC OS as Linux (unzip -L `find -iname $FILE.zip`)
unzip -L -o $FILE.zip
ZIPOUT=$?
## rename files to CamelCase format
mv `echo $FILE | tr \[A-Z\] \[a-z\]`.txt $FILE.txt
## special fix for DiningDescriptionLIst naming error
if [ $FILE = "DiningDescriptionList" ] && [ -f "DiningDescriptionLIst.txt" ]; then
mv -f DiningDescriptionLIst.txt diningdescriptionlist.txt
fi
# Temporary FIX for "|" inside data
if [ $FILE = "ActivePropertyList" ] && [ -f "ActivePropertyList.txt" ]; then
sed -i "s/Off Langata | Karen Road/Off Langata - Karen Road/" ActivePropertyList.txt
fi
# Temporary FIX for "|" inside data
if [ $FILE = "ActivePropertyBusinessModel" ] && [ -f "ActivePropertyBusinessModel.txt" ]; then
sed -i "s/\"Off Langata | Karen Road\"/Off Langata - Karen Road/" ActivePropertyBusinessModel.txt
fi
## some integrity tests to avoid processing 'bad' files
if [ -n "${CHKSUM_CMD}" ] ; then
CHKSUM_NOW=`$CHKSUM_CMD $FILE.txt | cut -f1 -d' '`
else
CHKSUM_NOW=1
fi
records=`wc -l < $FILE.txt | tr -d ' '`
(( records-- ))
## check if we need to update or not based on file changed, file contains at least 1x record
## file is readeable, file NOT empty, file unzipped w/o errors
if [ "$ZIPOUT" -eq 0 ] && [ "$CHKSUM_PREV" != "$CHKSUM_NOW" ] && [ "$records" -gt 0 ] && [ -s ${FILE}.txt ] && [ -r ${FILE}.txt ]; then
echo "Updating as integrity is ok & checksum change ($CHKSUM_PREV) to ($CHKSUM_NOW) on file ($FILE.txt)..."
## table name are lowercase
tablename=`echo $FILE | tr "[[:upper:]]" "[[:lower:]]"`
## checking if working with activepropertylist to make a backup of it before changes
if [ $tablename = "activepropertylist" ]; then
echo "Running a backup of ActivePropertyList..."
### Run stored procedures as required for extra functionality ###
### you can use this section for your own stuff ###
CMDSP_MYSQL="${MYSQL_DIR}mysql --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST} --database=eanprod"
# for version 5.6.6+ you will need this line instead to use stored credentials
#CMDSP_MYSQL="${MYSQL_DIR}mysql --login-path=${MYSQL_LOGINPATH} --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --database=eanprod"
$CMDSP_MYSQL --execute="CALL eanprod.sp_log_createcopy();"
echo "ActivePropertyList backup done."
fi
### Update MySQL Data ###
echo "Uploading ($FILE.txt) to ($MYSQL_DB.$tablename) with REPLACE option..."
## if you have limited resources use this line instead to avoid creating the transaction log
## you will need to give grant SUPER to the user, for it to work
## $CMD_MYSQL --execute="set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; LOAD DATA LOCAL INFILE '$FILE.txt' REPLACE INTO TABLE $tablename CHARACTER SET utf8 FIELDS TERMINATED BY '|' IGNORE 1 LINES;"
$CMD_MYSQL --execute="LOAD DATA LOCAL INFILE '$FILE.txt' REPLACE INTO TABLE $tablename CHARACTER SET utf8 FIELDS TERMINATED BY '|' IGNORE 1 LINES;"
## we need to erase the records, NOT updated today
echo "erasing old records from ($tablename)..."
$CMD_MYSQL --execute="DELETE FROM $tablename WHERE datediff(TimeStamp, now()) < 0;"
## checking if working with activepropertylist to fill the changed log table
if [ $tablename = "activepropertylist" ]; then
echo "Creating log of changes for ActivePropertyList..."
### Run stored procedures as required for extra functionality ###
### you can use this section for your own stuff ###
CMDSP_MYSQL="${MYSQL_DIR}mysql --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST} --database=eanprod"
# for version 5.6.6+ you will need this line instead to use stored credentials
#CMDSP_MYSQL="${MYSQL_DIR}mysql --login-path=${MYSQL_LOGINPATH} --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --database=eanprod"
$CMDSP_MYSQL --execute="CALL eanprod.sp_log_addedrecords();"
$CMDSP_MYSQL --execute="CALL eanprod.sp_log_erasedrecords();"
$CMDSP_MYSQL --execute="CALL eanprod.sp_log_erase_common();"
$CMDSP_MYSQL --execute="CALL eanprod.sp_log_erase_deleted();"
$CMDSP_MYSQL --execute="CALL eanprod.sp_log_changedrecords();"
### erase records before retention period
$CMDSP_MYSQL --execute="DELETE FROM log_activeproperty_changes WHERE TimeStamp < DATE_SUB(NOW(), INTERVAL $LOG_DAYS DAY);"
echo "Log for ActivePropertyList done."
fi
fi
done
echo "Updates done."
echo "Running Extras ... Stored Procedures..."
### Run stored procedures as required for extra functionality ###
### you can use this section for your own stuff ###
CMD_MYSQL="${MYSQL_DIR}mysql --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST} --database=eanextras"
# for version 5.6.6+ you will need this line instead to use stored credentials
#CMD_MYSQL="${MYSQL_DIR}mysql --login-path=${MYSQL_LOGINPATH} --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --database=eanextras"
#$CMD_MYSQL --execute="CALL eanextras.sp_fill_fasttextsearch();"
echo "Extras Stored Procedures done."
echo "Verify database against files..."
### Verify entries in tables against files ###
CMD_MYSQL="${MYSQL_DIR}mysqlshow --count ${MYSQL_DB} --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST}"
# for version 5.6.6+ you will need this line instead to use stored credentials
#CMD_MYSQL="${MYSQL_DIR}mysqlshow --login-path=${MYSQL_LOGINPATH} --count ${MYSQL_DB} --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST}"
$CMD_MYSQL
### find the amount of records per datafile
### should match to the amount of database records
echo "+---------------------------------+----------+------------+"
echo "| File | Records |"
echo "+---------------------------------+----------+------------+"
for FILE in ${FILES[@]}
do
## to count the number of output records minus the header
## records=$(($(wc -l $FILE.txt | awk '{print $1}')-1))
records=`wc -l < $FILE.txt | tr -d ' '`
(( records-- ))
{ printf "|" && printf "%33s" $FILE && printf "|" && printf "%23d" $records && printf "|\n"; }
done
echo "+---------------------------------+----------+------------+"
echo "Verify done."
## display endtime for the script
ENDTIME=$(date +%s)
secs=$(( $ENDTIME - $STARTTIME ))
h=$(( secs / 3600 ))
m=$(( ( secs / 60 ) % 60 ))
s=$(( secs % 60 ))
printf "total script time: %02d:%02d:%02d\n" $h $m $s
echo "script (import_db.sh) done."