#!/usr/local/bin/perl
####################################################################################
# mysqlwdb Version 1.3.0
#
# This is a CGI script written in Perl5, easy to configure and to install,
# intended to manage MySQL database tables through a web interface.
# Just edit and configure THIS UNIQUE file.
#
####################################################################################
####################################################################################
#
# Copyright (C) 2000,2002 Andrea Maestrutti aka Dree Mistrut in friulian language
# http://www.solution4web.com/mysqlwdb --- mysqlwdb@solution4web.com
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License version 2 as
# published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
# You may also find the license at http://www.gnu.org/copyleft/gpl.html
#
####################################################################################
#use strict; # to avoid scope variables errors: useful for developers
$|=1; # to avoid output buffering
close STDERR; # to avoid DBI error output on html with some web servers
#==================================================================================
#
# VARIABLE CONFIGURATION SECTION
#
# PLEASE READ THE INSTALLATION DOCUMENT AND CHANGE UNDER THIS COMMENT THE
# VARIABLES YOU NEED, TO REFLECT TO YOUR PURPOSES
#
#==================================================================================
#=========================================================
# Name of this CGI script
#=========================================================
my $thiscgi= 'mysqlwdb.pl';
#=========================================================
# Database(s) & user(s) variables
#=========================================================
#
# It is possible to manage more than 1 database.
# If you put data for only 1 database, you will not prompt for databases choice.
# If you put data for more than 1 database, a menu of choice will appear.
#
my %DATABASES= (
1 => {
host => "your server name",
port => "3306", # 3306 is the standard MySQL port
database => "builds",
user => "admin",
password => "",
db_description => "CABIE Database"
},
#
# Example for Database2.
#
# If you need to manage a second database, please remove all # from 2 to },
# If you need to manage others databases, please copy & paste the example and change the order number
#
#
# 2 => {
#
# host => "host for Database2",
# port => "port for Database2, usually 3306",
# database => "Database2",
# user => "User for Database2",
# password => "Password for Database2",
# db_description => "Description for Database 2"
#
# },
);
#=========================================================
# Login variables
#=========================================================
# It is possible to prompt user for login.
#
# If you WANT to manage user(s), please:
# a) remove the # before user(s) id and password that you need
# b) change the user(s) id and password according to your needs
# c) set $login_prompt variable to 1
my %LOGIN=(
"build" => "password",
# "user2" => "pwd2",
);
my $login_prompt= 1; # 1 means YES, 0 means NO
#=========================================================
# DBI driver variable
#=========================================================
my $DBI_driver= 'mysql';
#=========================================================
# DBD::mysql version variables
#=========================================================
#
# 1 means YES, 0 means NO
my $recent_DBD_is_pri_key= 1; # 'recent' means Msql-Mysql-modules version 1.21_07 or greater (1998-11-08)
# default value 1
my $recent_DBD_dsn_syntax= 1; # 'recent' means Msql-Mysql-modules version 1.19_03 or greater (1998-02-06)
# default value 1
my $obsolete_DBD_ListTables= 0; # 'obsolete' means Msql-Mysql-modules version prior to 1.21_07 (1998-11-08)
# default value 0 (as a precaution due to a problem of $dbh->tables()
# with empty databases in old drivers)
#=========================================================
# MySQL specific variable
#=========================================================
my $mysql_specific= 1; # 1 means YES, 0 means NO
#=========================================================
# Delimiters
#=========================================================
my $delimiter= '@@';
my $pri_key_delimiter= '&&';
#=========================================================
# Configurable actions
#=========================================================
#=======DBMS actions
#
# 1 means YES, 0 means NO
my $drop_enabled= 0; # to be able to drop table(s)
my $drop_check= 0; # to be prompted before drop table(s)
my $delete_enabled= 0; # to be able to delete field(s)
my $prepare_create_enabled=0; # to be able to define a new table
my $create_enabled= 0; # to be able to definitevely create a table
my $create_check= 0; # to be prompted before definitevely create a table
my $delete_check= 0; # to be prompted before delete field(s)
my $select_enabled= 1; # to be able to select field(s)
my $edititem_enabled= 1; # to be able to edit field(s) for update
my $new_enabled= 1; # to be able to edit fields for create new record(s)
my $prepare_enabled= 1; # to be able to prepare select on checked field(s)
my $describe_enabled= 0; # to be able to describe table(s)
my $selectfields_enabled= 1; # to be able to select checked field(s)
my $insertrecord_enabled= 1; # to be able to insert records(s)
my $insertrecord_check= 1; # to be prompted before insert record(s)
my $updaterecord_enabled= 1; # to be able to update field(s)
my $updaterecord_check= 1; # to be prompted before update table(s)
my $fquery_enabled= 0; # to be able to make free query
my $fquery_check= 0; # to be prompted before make free query
my $refresh_enabled= 1; # to be able to refresh table(s) list
my $max_selected_rows= 500; # to provide an overall limit to the number of ALL selected records
#=======Warnings '0 rows affected' happened
#
# 1 means YES, 0 means NO
my $wrng_enabled_4_crate= 0; # to be warned if '0 rows affected' happened for create table action
my $wrng_enabled_4_drop= 0; # to be warned if '0 rows affected' happened for drop table action
my $wrng_enabled_4_delete= 1; # to be warned if '0 rows affected' happened for delete action
my $wrng_enabled_4_update= 1; # to be warned if '0 rows affected' happened for update action
my $wrng_enabled_4_insert= 1; # to be warned if '0 rows affected' happened for insert action
#======Upper box
#
# 1 means YES, 0 means NO
my $upper_box_enabled= 1; # 1 means that the upper box will appear
#=======javascript check
#
# 1 means YES, 0 means NO
my $javascript_enabled= 1; # 1 means that javascript code will (0 means NOT) appear to check whether
# tables/records/fields have been correctly checked or not
#=========================================================
# Create table specific
#=========================================================
#
# NOTE: For configure types & specifiers please go to the end of this file
#
#=======create table specifiers order
#
# Specifiers order from left to right. Numbers are the positions.
# It is possible to remove some specifiers (i.e. a line) but 'field_name' and 'field_type' are required.
# Please do not change names, but only the numbers. See "create table specifiers messages" section to change names.
my %SPECIFIERS_ORDER= (
"field_name" => "1",
"field_type" => "2",
"length" => "3",
"decimals" => "4",
"unsigned" => "5",
"zerofill" => "6",
"binary" => "7",
"not_null" => "8",
"default" => "9",
"auto_increment"=> "10",
"index" => "11",
"unique" => "12",
"primary_key" => "13"
);
#=======characters not allowed for create table names
#
# _SOME_ characters not allowed for table and fields names
# This is for names check purpose. Space is already included.
my @characters_not_allowed=qw(
"
'
@
.
&
%
!
?
/
\
);
#=========================================================
# Messages
#=========================================================
#
# NOTE: if you want to add a " in a message, please write it as \"
#=======buttons & interface_messages
my $button_discard= "Discard";
my $button_proceed= "Proceed";
my $button_drop= "Drop table(s)";
my $button_prepare_create= "Create table";
my $button_create= "Create";
my $button_describe= "Describe table(s)";
my $button_refresh= "Refresh table(s) list";
my $button_edit= "Edit record(s)";
my $button_delete= "Delete record(s)";
my $button_update= "Update record(s)";
my $button_insert= "Insert record(s)";
my $button_new= "New record(s)";
my $button_prepare= "Prepare select";
my $button_select_all= "Select all fields";
my $button_select_chk= "Select checked field(s)";
my $button_js_check_tab= "Check all table(s)";
my $button_js_unchk_tab= "Uncheck all table(s)";
my $button_js_check_fld= "Check all field(s)";
my $button_js_unchk_fld= "Uncheck all field(s)";
my $button_js_check_rec= "Check all record(s)";
my $button_js_unchk_rec= "Uncheck all record(s)";
my $button_db_choice= "Go to";
my $button_go_to_db_choice="Databases choice";
my $button_fquery= "Submit";
my $databases_choice= "Databases choice";
my $user_login= "CABIE Admin login";
my $user_login_failed= "CABIE login failed";
my $user_login_fail_prompt="Please check your id or password";
my $button_user_login= "Submit";
my $button_user_logout= "Logout";
my $user_id= "Id:";
my $user_pwd= "Pwd:";
my $current_user_msg= "";
my $number_of_fields= "number of field(s)";
my $query_limit= "limit";
my $query_offset= "offset";
my $query_where= "where";
my $query_order_by= "order by";
my $fquery_no_output= "No output from free query";
#=======operations
my $operation_on_user= "Current user";
my $operation_on_dbs= "Operation(s) on database(s)";
my $operation_on_tables= "Operation(s) on table(s)";
my $operation_on_fields= "Operation(s) on field(s)";
my $operation_on_records= "Operation(s) on record(s)";
my $operations_on_query= "Query(ies)";
my $fquery= "Free query";
#=======questions
my $ask_drop_tables= "Do you really want to drop";
my $ask_create_table= "Do you really want to create table?";
my $ask_delete_records= "Do you really want to delete selected record(s)?";
my $ask_insert_records= "Do you really want to insert record(s)?";
my $ask_update_records= "Do you really want to update record(s)?";
my $ask_fquery= "Do you really want to submit the free query?";
#=======javascript alerts
my $js_no_checked_tables= "No checked table(s)";
my $js_no_checked_fields= "No checked field(s)";
my $js_tab_check_wo_exist= "Table(s) checked without existing record(s) or field(s)";
my $js_tab_check_wo_check= "Table(s) checked without checked record(s) or field(s)";
my $js_tab_no_check_with= "Table(s) no checked with checked record(s) or field(s)";
my $js_no_field_name= "No field name";
my $js_no_table_name= "No table name";
my $js_in_table_name= "in table name";
my $js_in_field_name= "in field name";
my $js_ai_must_be_pkey= "field must be a";
my $js_pk_must_be_nn= "must be";
my $js_space_character= "'space'";
my $js_for_type= "for type";
my $js_not_allowed= "is not allowed";
my $js_required= "is required";
my $js_with= "with";
my $js_same_name_fields= "Same name for fields";
my $js_name_not_number= "Name can not consist only of numbers";
#=======DBMS operations messages
my $ok_insert= "Insert operation successfully completed";
my $ko_insert= "insert operation NOT completed";
my $ok_create= "Create operation successfully completed";
my $ko_create= "create operation NOT completed";
my $ok_update= "Update operation successfully completed";
my $ko_update= "update operation NOT completed";
my $ok_delete= "Delete record(s) operation successfully completed";
my $ko_delete= "delete record(s) operation NOT completed";
my $ok_drop= "Drop operation successfully completed";
my $ko_drop= "drop operation NOT completed";
my $ok_fquery= "Free query successfully completed";
my $ko_fquery= "free query operation NOT completed";
#=======create table specifiers messages
#
# It is possible to remove some specifiers (i.e. a line) but 'field_name' and 'field_type' are required.
# Please do not change names on the left, but only messages on the right.
# See "create table specifiers messages" section to change names on the left.
my %SPECIFIERS_MESSAGES= (
"table_name" => "Table name",
"field_name" => "Field name",
"field_type" => "Field type",
"length" => "Length",
"decimals" => "Decimals",
"unsigned" => "Unsigned",
"zerofill" => "Zerofill",
"binary" => "Binary",
"not_null" => "Not null",
"default" => "Default",
"auto_increment"=> "Auto increment",
"index" => "Index",
"unique" => "Unique",
"primary_key" => "Primary key"
);
#=======interface errors and warnings
my $err_error_word= "ERROR";
my $err_warning_word= "WARNING";
my $err_details_word= "details";
my $err_connect_db= "Can not connect to the database";
my $err_db_empty= "Database empty";
my $err_not_present= "NOT PRESENT";
my $err_no_prikey= "PRIMARY KEY IS NOT PRESENT";
my $err_0_rows_happened= "'0 rows affected' happened";
my $err_0_new_records= "0 new records";
my $err_0_fields= "0 fields";
my $err_no_checked_tables= "No checked table(s)";
my $err_no_check_rec_tab= "No checked record(s) or table(s)";
my $err_tab_no_check_with= "table(s) no checked with checked record(s) or field(s)";
my $err_tab_check_wo_exist="table(s) checked without existing record(s) or field(s)";
my $err_tab_check_wo_check="table(s) checked without checked record(s) or field(s)";
my $err_no_table_name= "No table name";
my $err_no_field_name= "No field name";
my $err_name_not_number= "Name can not consist only of numbers";
my $err_no_checked_fields= "No checked field(s)";
my $err_same_name_fields= "Same name for fields";
my $err_in_table_name= "in table name";
my $err_in_field_name= "in field name";
my $err_required= "is required";
my $err_not_allowed= "is not allowed";
my $err_with= "with";
my $err_for_type= "for type";
my $err_space_character= "'space'";
#=========================================================
# HTML layout
#=========================================================
#=======upper box
my $upper_box_perc= "40%";
#=======font face
my $font_face= 'Verdana, Arial, Helvetica, sans-serif'; # font for general text
my $button_font_face= 'Verdana, Arial, Helvetica, sans-serif'; # font for buttons
my $numb_table_checkb_ff= 'Verdana, Arial, Helvetica, sans-serif'; # font for numbers before the checkbox of tables
my $numb_rec_checkb_ff= 'Verdana, Arial, Helvetica, sans-serif'; # font for numbers before the checkbox of records
my $tablename_font_face= 'Verdana, Arial, Helvetica, sans-serif'; # font for tables names
my $box_font_face= 'Verdana, Arial, Helvetica, sans-serif'; # font for text in upper box
#=======font size
my $font_size= '2'; # font size for general text
my $button_font_size= '2'; # font size for buttons
my $numb_table_checkb_fs= '2'; # font size for numbers before the checkbox of tables
my $numb_rec_checkb_fs= '2'; # font size for numbers before the checkbox of records
my $tablename_font_size= '2'; # font size for tables names
my $box_font_size= '3'; # font size for text in upper box
#=======colors
my $body_bgcolor= '#ffffff'; # color for page background
my $font_color= '#000000'; # color font for general text
my $font_color_log_fail= 'red'; # color font for user login fail text
my $button_font_color= '#000000'; # color for buttons
my $table_color= '#f0f0f0'; # color for tables background
my $table_td_color= '#a0b8c8'; # color for tables columns
my $tablename_font_color= '#000000'; # color font for tables names
my $table_checkb_td_color= '#a0b8c8'; # color for columns with the checkbox of tables
my $numb_table_checkb_col= '#000000'; # color font for numbers before the checkbox of tables
my $numb_rec_checkb_col= '#000000'; # color font for numbers before the checkbox of records
my $rec_checkb_td_color= '#a0b8c8'; # color for columns with the checkbox of records
my $pri_key_color= '#ea6467'; # color for columns with primary key field
my $field_color= '#00b8c8'; # color for columns with records fields
my $label_color= '#ea6467'; # color for labels
my $box_bg_color= '#f0f0f0'; # color for upper box background
my $box_font_color= '#000000'; # color font for text in upper box
#=======form
my $input_text_size= 15; # text size of the input control for insert & update fields
my $text_size_4_create= 15; # text size of the input control for table and field names
my $maxlength_4_create= 64; # maxlength of the input control for table and field names (would be 64 characters in MySQL)
my $text_size_4_specif= 5; # text size of the input control for type specifiers
my $textarea_enabled= 1; # 1 means YES, 0 means NO
my $max_length4text= 40; # if the number of characters of the field is higher than $max_length4text,
# a textarea appears (if enabled) instead of the text input control when update
my $cols_textarea= 35; # number of columns of the textarea
my $rows_textarea= 4; # number of rows of the textarea
my $cols_fquery_textarea= 45; # number of columns of the textarea for the free query
my $rows_fquery_textarea= 4; # number of rows of the textarea for the free query
my $cols_fquery_texta_out= 55; # number of columns of the textarea for the free query output
my $rows_fquery_texta_out= 7; # number of rows of the textarea for the free query output
my $new_textarea_enabled= 0; # 1 means YES, 0 means NO
# if 1 a textarea appears instead of the text input control when create a new record
my $cols_new_textarea= 25; # number of columns of the textarea
my $rows_new_textarea= 3; # number of rows of the textarea
my $where_text_size= 10; # text size of input control for the SQL where
my $limit_values= q{