Pernahkah anda merasa bingung dan pusing, ketika disodori satu database MySQL
tanpa dilengkapi struktur database-nya ?
Berikut ini ada satu eksperimen bagaimana caranya mengeluarkan seluruh nama field
atau kolom di database MySQL dengan menggunakan skrip perl.
Di MySQL, ada query untuk menampilkan nama field secara per nama tabel, contoh
:
[iko@ikodevel /]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 4.1.12
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 15 rows in set (0.00 sec)
mysql> show columns from db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | | PRI | | | | Db | char(64) | | PRI | | | | User | char(16) | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | | Create_tmp_table_priv | enum('N','Y') | | | N | | | Lock_tables_priv | enum('N','Y') | | | N | | +-----------------------+---------------+------+-----+---------+-------+ 15 rows in set (0.00 sec) mysql> show columns from user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | varchar(60) | | PRI | | | | User | varchar(16) | | PRI | | | | Password | varchar(41) | | | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Reload_priv | enum('N','Y') | | | N | | | Shutdown_priv | enum('N','Y') | | | N | | | Process_priv | enum('N','Y') | | | N | | | File_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | | Show_db_priv | enum('N','Y') | | | N | | | Super_priv | enum('N','Y') | | | N | | | Create_tmp_table_priv | enum('N','Y') | | | N | | | Lock_tables_priv | enum('N','Y') | | | N | | | Execute_priv | enum('N','Y') | | | N | | | Repl_slave_priv | enum('N','Y') | | | N | | | Repl_client_priv | enum('N','Y') | | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | | | | | | ssl_cipher | blob | | | | | | x509_issuer | blob | | | | | | x509_subject | blob | | | | | | max_questions | int(11) unsigned | | | 0 | | | max_updates | int(11) unsigned | | | 0 | | | max_connections | int(11) unsigned | | | 0 | | +-----------------------+-----------------------------------+------+-----+---------+-------+ 31 rows in set (0.00 sec) mysql> exit Bye [iko@ikodevel /]$
Nah....Anda bisa lihat bahwa untuk menampilkan nama field harus diketikkan
tiap-tiap tabel.Kita buat skrip perl yang bisa menampilkan daftar field tiap
tabel secara otomatis, jadi tidak perlu mengetikkan satu-persatu nama tabel.
Skrip ini menggunakan Perl DBI API, dengan driver untuk MySQL. DBI menggunakan
beberapa jenis handle.
Beberapa nama handle variabel yang biasa digunakan oleh DBI adalah :
$dbh : handle untuk database object
$sth : handle untuk statement / query object
$fh : handle untuk membuka file
Beberapa nama non handle variabel yang biasa digunakan oleh DBI adalah :
$rows : nilai balik dari operasi yang menampilkan jumlah baris @ary : daftar
yang menampilkan baris nilai balik dari sebuah query
Berikut adalah kode skrip kita :
*****gunting di sini******* #!/usr/bin/perl # # showcolumns.pl - show columns # Copyright (c) Des 2005 Iko Riyadi <iko_riyadi@yahoo.com>.
use strict; use DBI; my $filelog="/home/iko/dokumen/mysql_kolom.log"; # ganti nama database berikut sesuai dengan pencarian anda my $dtbase="mysql"; unlink $filelog ; sub printlog { open (PHILE, ">>$filelog"); print @_; print PHILE @_; close(PHILE); } ############################## # KONFIGURASI KONEKSI ke mysql my ($dsn) = "DBI:mysql:$dtbase:localhost"; #data source name my ($user_name) = "root"; #user mysql anda my ($password) = "mysql123"; #password mysql anda my ($dbh, $sth); my (@ary); my (@tabel); my (@row); my ($elemen); my $i=1; # KONEK ke database $dbh = DBI->connect($dsn,$user_name,$password, {RaiseError => 1}); # KIRIM query $sth = $dbh->prepare("show tables"); $sth->execute(); # BACA HASIL QUERY dan bersihkan while (@ary = $sth->fetchrow_array()) { #print @ary,"\n"; push (@tabel,@ary); } $sth->finish(); foreach $elemen(@tabel) { my $sth = $dbh->prepare("show columns from $elemen"); $sth->execute(); printlog "Tabel \.\:\:$elemen\:\:\.\n"; while (@row = $sth->fetchrow_array()) { #printlog "$i ",join ("\t",@row[0]),"\n"; printlog "$i ",join ("\t",@row),"\n"; $i++; } printlog "############\n"; } $dbh->disconnect(); exit(0); ############################## *****gunting di sini******* Hasil file log akan tampak seperti berikut :
Tabel .::columns_priv::. 1 Host char(60) PRI 2 Db char(64) PRI 3 User char(16) PRI 4 Table_name char(64) PRI 5 Column_name char(64) PRI 6 Timestamp timestamp YES CURRENT_TIMESTAMP 7 Column_priv set('Select','Insert','Update','References')
############
Tabel .::db::. 8 Host char(60) PRI 9 Db char(64) PRI 10 User char(16) PRI 11 Select_priv enum('N','Y') N 12 Insert_priv enum('N','Y') N 13 Update_priv enum('N','Y') N 14 Delete_priv enum('N','Y') N 15 Create_priv enum('N','Y') N 16 Drop_priv enum('N','Y') N 17 Grant_priv enum('N','Y') N 18 References_priv enum('N','Y') N 19 Index_priv enum('N','Y') N 20 Alter_priv enum('N','Y') N 21 Create_tmp_table_priv enum('N','Y') N 22 Lock_tables_priv enum('N','Y') N ############ Tabel .::func::. 23 name char(64) PRI 24 ret tinyint(1) 0 25 dl char(128) 26 type enum('function','aggregate') function ############ dst, dst...
OK, begitulah kira-kira cara untuk meng-grab nama-nama field dari tabel-tabel
yang ada di sebuah database secara otomatis. Jika ada ide yang lebih bagus,
mohon untuk sharing kepada penulis, karena tak ada gading yang tak retak...
THX TO:
[1] G O D , for everything
[2] my lovely wife
[3] my big family
[4] neoteker crew @dalnet
[5] 1stlink crew @centrin
[6] qq & tiyox
[7] alphacentaury, boeboe
[8] fuzk3, zka
[9] arta crew
[10] perl && mysql && linux developers
Kritik dan saran kirim ke :
iko_riyadi@yahoo.com
|