Shawn,
Your correct about my algorithm for IPv4, I was not paying attention (too
many things at once). It should have read:
AAA.BBB.CCC.DDD
\ \ \ \
\ \ \ DDD
\ \ CCC x 256
\ BBB x 256 x 256
AAA x 256 x 256 x 256
However, the 'SELECT' statement was conceptual, not literal and did in fact
state that it would need the appropriate 'CREATE TABLE' options as well as
an 'INTO OUTFILE' clause. This was left as an exercise for the user to
construct. It is not intended as Dynamic SQL. It was intended to create a
file of SQL statements that could be executed 'ad hoc'. It is also just one
of many ways to accomplish the goal. The literal and now complete 'SELECT'
with the same stated assumptions would look like this:
SELECT "CREATE TABLE ",REPLACE(ip_address,'.','_'), " ( ip CHAR (16),
last_access TIMESTAMP " INTO OUTFILE "/tmp/createtable.sql" FROM
IP_Addresses WHERE status ="ACTIVE"
This creates a file of SQL statements that can then be executed on the
command line or in a cron with the appropriate redirect.
I did, however, miss the INET_ATON() and INET_ATOA() functions added in
v3.23.30. Eliminating any need for a UDF.
Now that I have embarrased myself with a bad algorithm, defended my 'SELECT'
construction, and missed a very important pre-built function, I must say, I
do agree that superficially Chance's concept of creating a table for each IP
is not one I would personally embrace, but then again, I do not know what he
is trying to accomplish and he elected not to make the list privy to his
design. He did state early on in the thread, that he wasn't looking for
help in db design, just a solution to the punctuation issue.
Pat...
----- Original Message -----
From: [EMAIL PROTECTED]
To: Patrick
Cc: Chance Ellis ; mysql@lists.mysql.com
Sent: Monday, October 03, 2005 4:30 PM
Subject: Re: Table names with periods
Replies embedded:
"Patrick" <[EMAIL PROTECTED]> wrote on 10/03/2005 03:43:20 PM:
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
I am not sure of your algorithm. Perhaps I am just not understanding your
notation.
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"
Dynamic SQL? Not with that statement. He is going to need to create his SQL
statement client-side and send it pre-formatted to the server. MySQL 5.0 has
the beginnings of dynamic SQL and I am not 100% sure it would accept what
you typed.
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...
[EMAIL PROTECTED]
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.
You are mixing purposes. MySQL has a CREATE TABLE statement but it requires
a string literal. You cannot build a CREATE TABLE statement on the
fly -inside- MySQL. You have to build your statement client-side and send it
(as a complete statemnt) to MySQL to process.
MySQL has a function that converts IP addresses into numbers (see above) but
you cannot combine that with a CREATE TABLE statement.
Is this possible or am I thinking about this all wrong? The input
Yes, I think you are all wrong. You are being too "literal" in your design
choices. Generally if your data storage design requires you to add tables
whenever you add a new "whatever", that is a bad design. The better thing to
do is to create one table that can hold the entire class of "whatevers" and
differentiate between them with data markers. I assume that each of these
IP-named tables would look identical to every other (same column names, same
column types, ...)? The preferred method of modelling this is to create one
table (that looks just like each IP table was going to look) and adding a
column to it for the IP address. I know I am not the first person to
recommend this design (I can think of at least two others that have also
tried).
Just so that we aren't all telling you to possibly do the wrong thing: Why
do you feel that individual IP tables is a correct DATABASE design? It may
be an acceptable PROGRAMMING design (one IP list object per address) but
this is probably one of those points where good DB design and good OO design
diverge.
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.
The reason it has been so hard to do what you wanted to do is because what
you wanted to do is hard to maintain. Either trust us on our design
recommendation or tell us more about your problem. Depending on what you
tell us, perhaps neither design is appropriate. Please, and this goes for
others reading this later, never be afraid of getting into "too many
details". The details make all of the difference!! The better you can
describe your situation, the more likely you are to get a useful response.
<remainder snipped>
Kindest regards,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
