Page 1 of 1

SQL setup for local database

PostPosted: Thu Apr 14, 2005 8:14 am
by getalife
HostIP looks like a great resource which does just what we are looking for.

I downloaded the files in the udpates/2005 directory. When trying to import them into my web site's database I'm having a couple of problems.

1) My hosts seems to require quotes around floating values and doesn't consider more than two - to be comment so it doesn't like the 4th line of the SQL file. I can fix both of these problems by writing a program to edit the files if necessary.

2) I'm not sure what the format of the ip4_add and ip4_xxx tables are and I haven't found a CREATETABLE block for them yet. I'm guessing cron is DATETIME, a, b, c, d are TINYINT, and country and city are INT, but confirmation and information on indexes would be great.

Our goal is to use the latitude and longitude to draws dots on a map to show where all the visitors to our web site have come from.

Thanks in advance for any help.

Re: SQL setup for local database

PostPosted: Thu Apr 14, 2005 10:21 am
by bfolkens
getalife wrote:HostIP looks like a great resource which does just what we are looking for.

I downloaded the files in the udpates/2005 directory. When trying to import them into my web site's database I'm having a couple of problems.

1) My hosts seems to require quotes around floating values and doesn't consider more than two - to be comment so it doesn't like the 4th line of the SQL file. I can fix both of these problems by writing a program to edit the files if necessary.

2) I'm not sure what the format of the ip4_add and ip4_xxx tables are and I haven't found a CREATETABLE block for them yet. I'm guessing cron is DATETIME, a, b, c, d are TINYINT, and country and city are INT, but confirmation and information on indexes would be great.

Our goal is to use the latitude and longitude to draws dots on a map to show where all the visitors to our web site have come from.

Thanks in advance for any help.


You may have more success with the torrent at db.hostip.info - it's a full dump of what you need to setup lookups in a database. It's basically just a mysqldump of the tables you need. Seeing as how we just put this out, I'd like to make sure it works for you too. (you know, making sure I got all the right tables in there, etc) - so try it out ;)

The incremental updates requires you need to run a create script found in the SVN repo, and it's more for developmental purposes, like if you wanted to write code or patch stuff, etc.

PostPosted: Thu Apr 14, 2005 11:47 am
by getalife
How do I get the torrent file?

db.hostip.info doesn't have clickable links.
I also tried db.hostip.info/hostip_042005.sql.gz and db.hostip.info/torrents/hostip_042005.sql.gz

Is there a way to get the SVN scripts without having an SVN client? I have cygwin installed on XP Pro, but it doesn't include SVN and most of its shortcuts in the start menu don't work until I open a bash shell and do some display command. Unfortunately I don't remember what the command is. It may be showing that I'm an embedded C/C++ engineer with limited experience with databases. :)

PostPosted: Thu Apr 14, 2005 12:35 pm
by bfolkens
getalife wrote:How do I get the torrent file?

db.hostip.info doesn't have clickable links.
I also tried db.hostip.info/hostip_042005.sql.gz and db.hostip.info/torrents/hostip_042005.sql.gz

Is there a way to get the SVN scripts without having an SVN client? I have cygwin installed on XP Pro, but it doesn't include SVN and most of its shortcuts in the start menu don't work until I open a bash shell and do some display command. Unfortunately I don't remember what the command is. It may be showing that I'm an embedded C/C++ engineer with limited experience with databases. :)


On the "downloads" page - 1st paragraph - there's a link for the torrent: http://www.hostip.info/dl/index.html

PostPosted: Thu Apr 14, 2005 12:44 pm
by getalife
That's a link to a 2KB file.

I'm trying to comply with your request to not just hammer away at the Simple GET interface. It seems like you have the information I need, but I'm missing something.

PostPosted: Thu Apr 14, 2005 2:24 pm
by bfolkens
getalife wrote:That's a link to a 2KB file.

I'm trying to comply with your request to not just hammer away at the Simple GET interface. It seems like you have the information I need, but I'm missing something.


The torrent file contains instructions for a program like http://azureus.sourceforge.net/ (Azureus is the bittorrent client I use) to download the "big" files that comprise the database.

So get yourself a copy of a bittorrent client (like Azureus) - and then when you click that link it'll startup Azureus, which will begin the download of the database.

PostPosted: Thu Apr 14, 2005 2:49 pm
by getalife
Thanks for the info. Since my last post I found bittorrent at bittorrent.com and downloaded the torrent. Is there some advantage to using torrents instead of one of the more normal zip formats? When I try to upload it using phpMyAdmin it upload for a while and then sits with the progress bar at 100% for a while (probably unzipping during this time) and then goes to a screen that says "No SQL query! Welcome to phpMyAdmin 2.4.0.

I think the version of phpMyAdmin that my host is using doesn't recognize more than 2 '-'s in a row as a comment so it gets confused at the top of your SQL files. I think it also requires quotes around floating values. I don't know if more information about my host's versions would be helpful, but here it is http://ftphelp.secureserver.net/linux-phpinfo.html

I wrote a program to fix the '-'s and quotes and I'm modifying it now to split the large torrent file into separate files for each table since my host seems to work better with smaller uploads.

PostPosted: Thu Apr 14, 2005 4:36 pm
by getalife
By breaking up the torrent into individual table files and fixing the '-'s and adding quotes around the floating values I was able to import the countries and ip4_1 (a small one) tables. I was able to import the cityByCountry table by splitting it into two halves. I couldn't import ip4_0 (a large one), although I didn't try splitting it into a whole bunch of pieces. I also couldn't import the entire torrent as a zipped file.

All of the above actions were done on a web site hosted at GoDaddy. I tried the same things on a web site hosted at IPowerWeb and got the same results except that I didn't have to split cityByCountry in halves to get it to import.

I'm not really sure what to try next. Thanks for your help in getting this far.