freenetis-addresses-github/import.sh @ 58269f85
| ec7a7d3d | david | #!/bin/bash
|
|
| 8aa82b90 | david | #
|
|
# This file is part of open source system FreenetIS
|
|||
# and it is released under GPLv3 licence.
|
|||
#
|
|||
# More info about licence can be found:
|
|||
# http://www.gnu.org/licenses/gpl-3.0.html
|
|||
#
|
|||
# More info about project can be found:
|
|||
# http://www.freenetis.org/
|
|||
#
|
|||
#
|
|||
# init enviroment variable
|
|||
function style
|
|||
{
|
|||
| 180fba0a | David Raška | if [[ $cron == FALSE ]]
|
|
then
|
|||
tput $1 $2
|
|||
fi
|
|||
| 8aa82b90 | david | }
|
|
| ec7a7d3d | david | function print_info
|
|
{
|
|||
| 8aa82b90 | david | echo -en "== "
|
|
style bold
|
|||
echo -n $(date '+%D %T')
|
|||
style sgr0
|
|||
echo -n " == "
|
|||
style setaf 2
|
|||
style bold
|
|||
| ec7a7d3d | david | echo $1
|
|
| 8aa82b90 | david | style sgr0
|
|
echo
|
|||
| ec7a7d3d | david | }
|
|
function print_warning
|
|||
{
|
|||
| 8aa82b90 | david | echo -en "!! "
|
|
style bold
|
|||
echo -n $(date '+%D %T')
|
|||
style sgr0
|
|||
echo -n " !! "
|
|||
style setaf 1
|
|||
style bold
|
|||
| ec7a7d3d | david | echo $1
|
|
| 8aa82b90 | david | style sgr0
|
|
echo
|
|||
| ec7a7d3d | david | }
|
|
function control_c
|
|||
{
|
|||
| 8aa82b90 | david | echo -e "\n"
|
|
| ec7a7d3d | david | print_warning "SIGINT? OKAY :'("
|
|
rm -rf $directory
|
|||
exit 1
|
|||
}
|
|||
| 8aa82b90 | david | echo "--------------------------------------------------------------------------------"
|
|
| 180fba0a | David Raška | if [[ "$1" == "--cron" ]] || [[ "$1" == "-c" ]];
|
|
then
|
|||
cron=TRUE
|
|||
else
|
|||
cron=FALSE
|
|||
fi
|
|||
| ec7a7d3d | david | if [ -r /etc/freenetis-addresses.ini ]
|
|
then
|
|||
. /etc/freenetis-addresses.ini
|
|||
else
|
|||
print_warning "Config file not found"
|
|||
exit 1
|
|||
fi
|
|||
# do not modify
|
|||
| 8aa82b90 | david | country_id=55 # ID of Czech republic
|
|
| ec7a7d3d | david | mysql_table="addresses"
|
|
mysql_table_old=$mysql_table"_old"
|
|||
mysql_table_tmp=$mysql_table"_tmp"
|
|||
# get start tim
|
|||
starttime=$(date '+%s')
|
|||
| 642361dd | David Raška | print_info "Trying to get latest database date"
|
|
| ec7a7d3d | david | ||
| 642361dd | David Raška | # download web page
|
|
html=$(wget -O - http://nahlizenidokn.cuzk.cz/StahniAdresniMistaRUIAN.aspx 2>/dev/null)
|
|||
| ec7a7d3d | david | ||
| 642361dd | David Raška | if [ $? != 0 ];
|
|
then
|
|||
print_warning "Cannot get latest database date"
|
|||
rm -rf $directory
|
|||
exit 1
|
|||
fi
|
|||
| ec7a7d3d | david | ||
| 642361dd | David Raška | # read date from html source
|
|
datestamp=$(echo $html | sed -nre "s/.*([0-9]{8})_OB_ADR_csv\.zip.*/\1/p")
|
|||
| ec7a7d3d | david | ||
| 642361dd | David Raška | if [ $? != 0 ];
|
|
then
|
|||
print_warning "Cannot get latest database date"
|
|||
rm -rf $directory
|
|||
exit 1
|
|||
| ec7a7d3d | david | fi
|
|
db_datestamp=$(mysql $mysql_db -u $mysql_user -p$mysql_pass -h $mysql_server -P $mysql_port --silent -e "SELECT value FROM config WHERE name LIKE 'datestamp'")
|
|||
if [ -n "$db_datestamp" ];
|
|||
then
|
|||
| 642361dd | David Raška | if [ "$db_datestamp" -ge "$datestamp" ];
|
|
| ec7a7d3d | david | then
|
|
print_info "Database is up to date"
|
|||
exit 0
|
|||
else
|
|||
print_warning "Database is not up to date and will be updated"
|
|||
fi
|
|||
else
|
|||
print_warning "Database is empty and will be imported"
|
|||
fi
|
|||
# create temp directory
|
|||
directory=$(mktemp -d)
|
|||
# catch SIGINT
|
|||
trap control_c SIGINT
|
|||
# download address database
|
|||
print_info "Downloading address database"
|
|||
| 8aa82b90 | david | wget http://vdp.cuzk.cz/vymenny_format/csv/$datestamp\_OB_ADR_csv.zip -O $directory/addresses.zip 2>/dev/null
|
|
| ec7a7d3d | david | ||
if [ $? != 0 ];
|
|||
then
|
|||
print_warning "Cannot download addresses"
|
|||
rm -rf $directory
|
|||
exit 1
|
|||
fi
|
|||
# unzip
|
|||
print_info "Extracting address database"
|
|||
unzip -qd $directory $directory/addresses.zip
|
|||
if [ $? != 0 ];
|
|||
then
|
|||
print_warning "Cannot extract addresses"
|
|||
rm -rf $directory
|
|||
exit 1
|
|||
fi
|
|||
# prepare database for importing
|
|||
print_info "Preparing address database"
|
|||
FILES=$directory/CSV/*.csv
|
|||
FILE_NUM=$(ls -1 $FILES | wc -l)
|
|||
I=0
|
|||
for f in $FILES
|
|||
do
|
|||
I=$(($I+1))
|
|||
| 58269f85 | jeffraska | if [[ $cron == FALSE ]]
|
|
then
|
|||
echo -en "\rPreparing $I of $FILE_NUM: $f"
|
|||
fi
|
|||
| ec7a7d3d | david | ||
| 8aa82b90 | david | # remove columns #change encoding #create number with orientation number #create number without orientaion number #add country id
|
|
| 58269f85 | jeffraska | cat $f | cut -s -d ";" -f 3-4,7-15 | iconv -f "WINDOWS-1250" -t "UTF-8" | sed -r 's/;(((č\.)(ev\.))|(č\.p\.));([0-9]*);([0-9][0-9]*);(.*)/;\4\3\6\/\7\8/g' | sed -r 's/;(((č\.)(ev\.))|(č\.p\.));([0-9]*);;/;\4\3\6/g' | sed -r "s/(.*)/$country_id;\1/g" > $f.utf8
|
|
| ec7a7d3d | david | ||
if [ $? != 0 ];
|
|||
then
|
|||
| 8aa82b90 | david | echo -e "\n"
|
|
| 58269f85 | jeffraska | print_warning "Cannot prepare addresses - $I of $FILE_NUM: $f"
|
|
| ec7a7d3d | david | rm -rf $directory
|
|
exit 1
|
|||
fi
|
|||
done
|
|||
# import database
|
|||
FILES=$directory/CSV/*.utf8
|
|||
FILE_NUM=$(ls -1 $FILES | wc -l)
|
|||
I=0
|
|||
| 8aa82b90 | david | echo -e "\n"
|
|
| ec7a7d3d | david | print_info "Importing address database"
|
|
| 8aa82b90 | david | mysql $mysql_db -u $mysql_user -p$mysql_pass -h $mysql_server -P $mysql_port --silent -e "TRUNCATE TABLE $mysql_table_tmp"
|
|
| ec7a7d3d | david | if [ $? != 0 ];
|
|
then
|
|||
| 8aa82b90 | david | print_warning "Cannot clean temporary table"
|
|
| ec7a7d3d | david | rm -rf $directory
|
|
exit 1
|
|||
fi
|
|||
for f in $FILES
|
|||
do
|
|||
I=$(($I+1))
|
|||
| 58269f85 | jeffraska | if [[ $cron == FALSE ]]
|
|
then
|
|||
echo -en "\rImporting $I of $FILE_NUM: $f"
|
|||
fi
|
|||
| ec7a7d3d | david | ||
| 8aa82b90 | david | mysql $mysql_db -u $mysql_user -p$mysql_pass -h $mysql_server -P $mysql_port --local-infile -e "LOAD DATA LOCAL INFILE '$f' INTO TABLE $mysql_table_tmp FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES"
|
|
| ec7a7d3d | david | ||
if [ $? != 0 ];
|
|||
then
|
|||
| 8aa82b90 | david | echo -e "\n"
|
|
| 58269f85 | jeffraska | print_warning "Cannot import addresses - $I of $FILE_NUM: $f"
|
|
| ec7a7d3d | david | rm -rf $directory
|
|
exit 1
|
|||
fi
|
|||
done
|
|||
| 8aa82b90 | david | echo -e "\n"
|
|
| ec7a7d3d | david | print_info "Updating database"
|
|
mysql $mysql_db -u $mysql_user -p$mysql_pass -h $mysql_server -P $mysql_port --silent -e "RENAME TABLE $mysql_table TO $mysql_table_old; RENAME TABLE $mysql_table_tmp TO $mysql_table; RENAME TABLE $mysql_table_old TO $mysql_table_tmp; REPLACE INTO config VALUES ('datestamp', '$datestamp'); TRUNCATE TABLE $mysql_table_tmp"
|
|||
if [ $? != 0 ];
|
|||
then
|
|||
print_warning "Cannot update database"
|
|||
rm -rf $directory
|
|||
exit 1
|
|||
fi
|
|||
# clean up
|
|||
rm -rf $directory
|
|||
# count script run time
|
|||
endtime=$(date '+%s')
|
|||
seconds=$(( $endtime - $starttime ))
|
|||
hours=$(($seconds / 3600))
|
|||
seconds=$(($seconds % 3600))
|
|||
minutes=$(($seconds / 60))
|
|||
seconds=$(($seconds % 60))
|
|||
| 180fba0a | David Raška | print_info "Import takes $(printf '%d:%02d:%02d\n' $hours $minutes $seconds)"
|