List:General Discussion« Previous MessageNext Message »
From:Patrick Date:October 3 2005 7:43pm
Subject:Re: Table names with periods
View as plain text  
There are many ways to approach this.  How are you receiving the IP data?  Are you reading
a file or other stream or are you trying to process the table creation by reading a column
from a previously populated table through a select statement?

The functions, inet_ntoa() and inet_addr(), are part of most networking libraries. These
are the common functions to convert dotted quad notation. If you wanted to write you own
function, an IPv4 address is broken down as follows:

AAA.BBB.CCC.DDD
  \   \   \   \
    \   \   \   DDD
      \   \   CCC x CCC
        \   BBB x BBB x BBB
          AAA x AAA x AAA x AAA

Add the results and you have your unique 32bit number. 

eg. 10.10.10.1 = 10,000 + 1,000 + 100 + 1
               = 11,101


If you are not able to pre-process (scrub) the incoming data programmatically, you would
need to create a UDF in MySQL to perform the conversion, or, alternatively, if you want
to use MySQL SELECT statement as-is could replace the 'period' with an 'underscore' using
MySQL's built-in string functions like so:

Assumptions: Reading IP address from an existing table named IP_Addresses with a column
named ip_address and a column named status.

    SELECT  "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert create options here]"
FROM IP_Addresses WHERE status ="ACTIVE"

You would obviously add your "CREATE TABLE" options and "INTO OUTFILE" options as needed.
This would be an alternative to converting IPv4 to 32bit Integer.

I hope this helps...

If at all possible, it is probably best to continue in the MySQL list, there are some
pretty clever people out there

Pat...

patrick@stripped
CocoNet Corporation
SW Florida's First ISP
825 SE 47th Terrace
Cape Coral, FL 33904

  ----- Original Message ----- 
  From: Chance Ellis 
  To: Patrick 
  Sent: Monday, October 03, 2005 2:22 PM
  Subject: Re: Table names with periods


  Patrick,

  I have been trying to figure out how I can convert an IP address to a 32bit integer
within a SQL create statement.

  Is this possible or am I thinking about this all wrong? The input I am given is a
straight IP address. I have no way of modifying it other than some option in the SQL
create statement is possible. I want to create a new table for each IP address. Without
getting too much into the details, these are my requirements and I have been wasting alot
of time trying to figure out how to change this string in the create statement. 

  Any help you can provide is greatly appreciated.

  Thanks!

  Chance


   
  On 9/28/05, Patrick <patrick@stripped> wrote: 
    Historically any form of punctuation, parameter delimiter, or filepath
    delimiter in either a database name, field or column name, file or table 
    name would not be recommended; even if the RDBMS or File Handler allows it.

    If you are able to stick to alphanumeric characters using underscores
    characters if needed for clarity, you go a long way for portability across 
    various operating systems. Also, IPv4 addresses are readily converted to
    single 32bit integers that minimize the need for dotted quartets.

    Early versions of MySQL allowed periods.  This caused OS incompatibility 
    issues.  To my knowledge this was fixed prior to version 3 and you are no
    longer allowed periods in database or table names.  This, in my thinking, is
    a good thing by assuring greater portability and easier migration. 

    I hope this helps.

    Pat...

    patrick@stripped
    CocoNet Corporation
    SW Florida's First ISP
    825 SE 47th Terrace
    Cape Coral, FL 33904



Thread
Table names with periodsChance Ellis28 Sep
  • Re: Table names with periodsSGreen28 Sep
  • Re: Table names with periodsMartijn Tonies28 Sep
    • Re: Table names with periodsChance Ellis28 Sep
  • Re: Table names with periodsMartijn Tonies28 Sep
  • Re: Table names with periodsMartijn Tonies28 Sep
    • Re: Table names with periodsChance Ellis28 Sep
  • Re: Table names with periodsPatrick28 Sep
    • Re: Table names with periodsChance Ellis28 Sep
  • Re: Table names with periodsMartijn Tonies28 Sep
  • Re: Table names with periodsOctavian Rasnita28 Sep
  • Re: Table names with periodsPatrick3 Oct
    • Re: Table names with periodsSGreen3 Oct
      • Re: Table names with periodsChance Ellis3 Oct
        • Re: Table names with periodsJasper Bryant-Greene3 Oct
        • Re: Table names with periodsJerl Simpson3 Oct
        • Re: Table names with periodsBastian Balthazar Bux3 Oct
          • Re: Table names with periodsChance Ellis4 Oct
      • Re: Table names with periodsPatrick4 Oct