czfgmap

Subversion Repositories:
[/] [tools/] [gendb] - Rev 15 Go to most recent revision
Compare with Previous - Blame - Download
#!/bin/bash
#<!-- Copyright (C) 2008  Petr Simandl www.simandl.cz                          -->
#<!-- This file is part of CZFGmap.                                            -->
#<!--                                                                          -->
#<!-- CZFGmap is free software: you can redistribute it and/or modify          -->
#<!-- it under the terms of the GNU General Public License as published by     -->
#<!-- the Free Software Foundation, either version 3 of the License, or        -->
#<!-- (at your option) any later version.                                      -->
#<!--                                                                          -->
#<!-- CZFGmap is distributed in the hope that it will be useful,               -->
#<!-- but WITHOUT ANY WARRANTY; without even the implied warranty of           -->
#<!-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            -->
#<!-- GNU General Public License for more details.                             -->
#<!--                                                                          -->
#<!-- You should have received a copy of the GNU General Public License        -->
#<!-- along with CZFGmap.  If not, see <http://www.gnu.org/licenses/>.         -->

datum=`date '+%Y%m%d%H%M%S'`

outputfilen="out_node.sql"
outputfilel="out_line.sql"

#stahnem nody a linky
if [ ! -e listnodes_${datum}.txt ]
then
#    echo "neni tu ";
     wget http://mapa.czfree.net/listnodes.php -O listnodes_${datum}.txt
fi

if [ ! -e listlines_${datum}.txt ]
then
#    echo "neni tu ";
     wget http://mapa.czfree.net/listlines.php -O listlines_${datum}.txt
fi

if [ -e listnodes_${datum}.txt ]
then
#    echo "nodes do utf";
    iconv -f windows-1250 -t utf8 listnodes_${datum}.txt --output  listnodesutf.txt
fi

if [ -e listnodesutf.txt ]
then
#    echo "delam tabulku node";
cat > $outputfilen <<EOF
DROP TABLE node;
CREATE TABLE node (
  id int(10) unsigned NOT NULL auto_increment,
  lat double NOT NULL default '0',
  lon double NOT NULL default '0',
  ownerid int(10) unsigned NOT NULL default '0',
  networkid int(11) NOT NULL default '0',
  type mediumint(8) unsigned NOT NULL default '0',
  status tinyint(3) unsigned NOT NULL default '0',
  admingroup int(11) default NULL,
  ostype int(11) default NULL,
  osversion int(11) default NULL,
  name varchar(100) default NULL,
  address varchar(120) default NULL,
  urlphotos varchar(90) default NULL,
  urlhomepage varchar(60) default NULL,
  urlthread varchar(60) default NULL,
  visibilitydesc blob,
  changed_by int(10) unsigned default NULL,
  changed_on datetime default NULL,
  align int(11) default '0',
  peoplecount smallint(5) unsigned default NULL,
  machinecount smallint(5) unsigned default NULL,
  peoplehide enum('0','1') NOT NULL default '0',
  machinehide enum('0','1') NOT NULL default '0',
  ownername varchar(100) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY name (name),
  KEY lat_2 (lat),
  KEY lat_3 (lat),
  KEY id (id),
  KEY lat (lat),
  KEY lon (lon),
  KEY ownerid (ownerid),
  KEY Type (type)
) TYPE=MyISAM PACK_KEYS=0;
EOF

cat listnodesutf.txt | \
sed 's/^\[//' | \
sed 's/^\([0-9]*\)\]/\1/' | \
sed 's/\\/\\\\/' | \
sed 's/'\''/\\'\''/' | \
awk -F "\t" '{print "INSERT INTO node VALUES ("$1","$2","$3",0,1,1,"$4",1,1,1,'\''"$6"'\'','\'\'','\'\'','\'\'','\'\'','\'\'',440,'\''2008-01-01 01:01:01'\'',0,NULL,NULL,'\'\'','\'\'','\''"$5"'\'');"}' | \
uniq >> $outputfilen

fi

if [ -e listlines_${datum}.txt ]
then
#    echo "delam tabulku line";
cat > $outputfilel <<EOF
DROP TABLE line;
CREATE TABLE line (
  id1 int(10) unsigned NOT NULL default '0',
  id2 int(10) unsigned NOT NULL default '0',
  type mediumint(8) unsigned zerofill NOT NULL default '00000000',
  status tinyint(1) unsigned NOT NULL default '0',
  perm1 tinyint(1) unsigned NOT NULL default '0',
  perm2 tinyint(1) unsigned NOT NULL default '0',
  changed_by int(10) unsigned default NULL,
  changed_on datetime default NULL,
  backbone tinyint(1) unsigned NOT NULL default '0',
  inplanning tinyint(1) unsigned NOT NULL default '0',
  comment varchar(128) NOT NULL default '',
  PRIMARY KEY  (id1,id2),
  KEY id1 (id1),
  KEY id2 (id2),
  KEY last (changed_on),
  KEY backbone (backbone),
  KEY inplanning (inplanning)
) TYPE=MyISAM;
EOF

cat listlines_${datum}.txt | \
sed 's/\[\([0-9]*\)\]/\1/g' | \
awk -F "\t" '{print "INSERT INTO line VALUES ("$1","$2","$3",0,40,40,88,'\''2008-01-01 01:01:01'\'',0"$4",0,'\'\'');"}' | \
uniq >> $outputfilel

fi