Dear module maintainers,
I would like to become a contributor.
My name is Giuseppe Maxia
The username I would like to use is GMAX
My e-mail address is [EMAIL PROTECTED]
I have written a module to facilitate the normalization of database tables.
In short, it is an object that, given a short description for the normalization task to
accomplish, will generate the necessary SQL code for getting the job done.
Example: Given a table MP3 with fields
+----------+-------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+----------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| title | varchar(40) | | MUL | | |
| artist | varchar(20) | | MUL | | |
| album | varchar(30) | | MUL | | |
| duration | time | | | 00:00:00 | |
| size | int(11) | | | 0 | |
| genre | varchar(10) | | MUL | | |
+----------+-------------+------+-----+----------+----------------+
We can transform from 1st to 2nd normal from using these instructions:
use Normalizer;
# passing username and password explicitly
my $norm = Normalizer->new ({
DSN => "DBI:mysql:music;host=localhost"
username => "itsme",
password => "secret",
src_table => "MP3",
index_field => "album_id",
lookup_fields => "artist,album,genre",
lookup_table => "tmp_albums",
dest_table => "songs",
copy_indexes => 1,
simulate => 1
});
$norm->do();
The above instructions will generate this SQL code (MySQL specific):
DROP TABLE IF EXISTS tmp_albums;
CREATE TABLE tmp_albums (album_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
artist varchar(20) not null,
album varchar(30) not null,
genre varchar(10) not null,
KEY artist (artist), KEY album (album), KEY genre (genre));
INSERT INTO tmp_albums
SELECT DISTINCT NULL, artist,album,genre FROM MP3;
DROP TABLE IF EXISTS songs;
CREATE TABLE songs (ID int(11) not null auto_increment,
title varchar(40) not null,
duration time not null default '00:00:00',
size int(11) not null,
album_id INT(11) NOT NULL,
PRIMARY KEY (ID), KEY title (title), KEY album_id (album_id));
INSERT INTO songs SELECT src.ID, src.title, src.duration, src.size,
album_id
FROM MP3 src INNER JOIN tmp_albums lkp
ON (src.artist =lkp.artist and src.album =lkp.album
and src.genre =lkp.genre);
The table tmp_albums can be further normalized using the same technique.
The module is working, has been tested with huge tables (> 1_000_000 records),
it is already fully documented and the only thing it is missing is a reliable name.
I am not sure if I should ask for a new namespace on its own or a namespace under DBI
or even under DBD::mysql.
I hope to find out soon with some user group advice.
Thanks in advance
Best regards
Giuseppe Maxia
