SQL setup for local database

Support on setups where you copy the hostip.info database locally.

Moderator: Moderators

SQL setup for local database

Postby getalife on Thu Apr 14, 2005 8:14 am

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.
getalife
n00b
 
Posts: 5
Joined: Thu Apr 14, 2005 7:59 am
Location: Arbuckle, CA

Re: SQL setup for local database

Postby bfolkens on Thu Apr 14, 2005 10:21 am

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.
bfolkens
Site Admin
 
Posts: 154
Joined: Tue Feb 22, 2005 8:09 am

Postby getalife on Thu Apr 14, 2005 11:47 am

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. :)
getalife
n00b
 
Posts: 5
Joined: Thu Apr 14, 2005 7:59 am
Location: Arbuckle, CA

Postby bfolkens on Thu Apr 14, 2005 12:35 pm

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
bfolkens
Site Admin
 
Posts: 154
Joined: Tue Feb 22, 2005 8:09 am

Postby getalife on Thu Apr 14, 2005 12:44 pm

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.
getalife
n00b
 
Posts: 5
Joined: Thu Apr 14, 2005 7:59 am
Location: Arbuckle, CA

Postby bfolkens on Thu Apr 14, 2005 2:24 pm

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.
bfolkens
Site Admin
 
Posts: 154
Joined: Tue Feb 22, 2005 8:09 am

Postby getalife on Thu Apr 14, 2005 2:49 pm

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.
getalife
n00b
 
Posts: 5
Joined: Thu Apr 14, 2005 7:59 am
Location: Arbuckle, CA

Postby getalife on Thu Apr 14, 2005 4:36 pm

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.
getalife
n00b
 
Posts: 5
Joined: Thu Apr 14, 2005 7:59 am
Location: Arbuckle, CA


Return to Hostip.info database local copy support

Who is online

Users browsing this forum: No registered users and 4 guests

cron