[Stdair-svn] SF.net SVN: stdair:[114] trunk/stdair

[<den...@us...>]

Revision: 114
          http://stdair.svn.sourceforge.net/stdair/?rev=114&view=rev
Author:   denis_arnaud
Date:     2010-02-06 13:33:54 +0000 (Sat, 06 Feb 2010)

Log Message:
-----------
[DB] Added support for database administration, as well as a sample table (for airline description).

Added Paths:
-----------
    trunk/stdair/db/
    trunk/stdair/db/admin/
    trunk/stdair/db/admin/clean_a_table.sh
    trunk/stdair/db/admin/clean_all_tables.sh
    trunk/stdair/db/admin/count_a_table.sh
    trunk/stdair/db/admin/count_all_tables.sh
    trunk/stdair/db/admin/create_dsim_db.sh
    trunk/stdair/db/admin/create_dsim_tables.sql
    trunk/stdair/db/admin/create_dsim_user.sh
    trunk/stdair/db/admin/create_dsim_user.sql
    trunk/stdair/db/admin/dump_dsim_db_structure.sh
    trunk/stdair/db/admin/fill_dsim_tables.sql
    trunk/stdair/db/admin/load_dsim_data.sh
    trunk/stdair/db/data/
    trunk/stdair/db/data/airlines.csv

Added: trunk/stdair/db/admin/clean_a_table.sh
===================================================================
--- trunk/stdair/db/admin/clean_a_table.sh	                        (rev 0)
+++ trunk/stdair/db/admin/clean_a_table.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,35 @@
+#!/bin/sh
+# Parameters required
+# - name of user
+# - name of database
+# - name of table
+# - host 
+# - port
+
+# if [ "$1" = "" -o "$2" = "" ];
+if [ $# != 5 ]
+then
+	echo "Usage: $0 <Username> <Database name> <table> <Database Host> <Port>"
+	echo ""
+	exit -1
+fi
+						
+DB_USER="$1"
+DB_PASSWD="${DB_USER}"
+DB_NAME="$2"
+DB_TABLE="$3"
+DB_HOST="$4"
+DB_PORT="$5"
+
+QUERY_DELETE="delete from ${DB_TABLE}"
+QUERY_COUNT="select count(*) from ${DB_TABLE}"
+
+echo "Before:"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${QUERY_COUNT}" ${DB_NAME}
+
+echo "Deleting all the rows from ${DB_NAME}.${DB_TABLE}"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${QUERY_DELETE}" ${DB_NAME}
+
+echo "Result:"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${QUERY_COUNT}" ${DB_NAME}
+

Added: trunk/stdair/db/admin/clean_all_tables.sh
===================================================================
--- trunk/stdair/db/admin/clean_all_tables.sh	                        (rev 0)
+++ trunk/stdair/db/admin/clean_all_tables.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,80 @@
+#!/bin/sh
+#
+# Two parameters are optional:
+# - the host server of the database
+# - the port of the database
+#
+
+if [ "$1" = "-h" -o "$1" = "--help" ];
+then
+	echo "Usage: $0 [<Database Server Hostname> [<Database Server Port>]]"
+	echo ""
+	exit -1
+fi
+
+##
+# Database Server Hostname
+DB_HOST="localhost"
+if [ "$1" != "" ];
+then
+	DB_HOST="$1"
+fi
+
+# Database Server Port
+DB_PORT="3306"
+if [ "$2" != "" ];
+then
+	DB_PORT="$2"
+fi
+
+# Database User
+DB_USER="dsim"
+
+# Database Password
+DB_PASSWD="${DB_USER}"
+
+# Database Name
+DB_NAME="dsim"
+
+# Count the number of elements of a given database table
+function countElements() {
+	echo
+	echo "Number of elements for the '${DB_TABLE}' table"
+	SQL_QUERY="select count(*) from ${DB_TABLE}"
+	mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${SQL_QUERY}" ${DB_NAME}
+}
+
+# Delete all the elements of a given database table
+function deleteAllElements() {
+	SQL_QUERY="delete from ${DB_TABLE}"
+	mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${SQL_QUERY}" ${DB_NAME}
+}
+
+#
+TABLES="airlines"
+
+# Count rows
+echo "Before:"
+for table_name in ${TABLES}
+do
+	DB_TABLE="${table_name}"
+	countElements
+done
+
+# Delete tables
+echo "Deletion..."
+for table_name in ${TABLES}
+do
+	DB_TABLE="${table_name}"
+	deleteAllElements
+done
+echo "Done"
+
+## After deleting elements (sanity check)
+echo
+echo "After:"
+for table_name in ${TABLES}
+do
+	DB_TABLE="${table_name}"
+	countElements
+done


Property changes on: trunk/stdair/db/admin/clean_all_tables.sh
___________________________________________________________________
Added: svn:executable
   + *

Added: trunk/stdair/db/admin/count_a_table.sh
===================================================================
--- trunk/stdair/db/admin/count_a_table.sh	                        (rev 0)
+++ trunk/stdair/db/admin/count_a_table.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,26 @@
+#!/bin/sh
+# Parameters required
+# - name of user
+# - name of database
+# - name of table
+# - host 
+# - port
+
+# if [ "$1" = "" -o "$2" = "" ];
+if [ $# != 5 ]
+then
+	echo "Usage: $0 <Username> <Database name> <table> <Database Host> <Port>"
+	echo ""
+	exit -1
+fi
+
+DB_USER="$1"
+DB_PASSWD="${DB_USER}"
+DB_NAME="$2"
+DB_TABLE="$3"
+DB_HOST="$4"
+DB_PORT="$5"
+QUERY_COUNT="select count(*) from ${DB_TABLE}"
+
+echo "The ${DB_NAME}.${DB_TABLE} table contains that many rows:"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${QUERY_COUNT}" ${DB_NAME}
\ No newline at end of file

Added: trunk/stdair/db/admin/count_all_tables.sh
===================================================================
--- trunk/stdair/db/admin/count_all_tables.sh	                        (rev 0)
+++ trunk/stdair/db/admin/count_all_tables.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,55 @@
+#!/bin/sh
+#
+# Two parameters are optional:
+# - the host server of the database
+# - the port of the database
+#
+
+if [ "$1" = "-h" -o "$1" = "--help" ];
+then
+	echo "Usage: $0 [<Database Server Hostname> [<Database Server Port>]]"
+	echo ""
+	exit -1
+fi
+
+##
+# Database Server Hostname
+DB_HOST="localhost"
+if [ "$1" != "" ];
+then
+	DB_HOST="$1"
+fi
+
+# Database Server Port
+DB_PORT="3306"
+if [ "$2" != "" ];
+then
+	DB_PORT="$2"
+fi
+
+# Database User
+DB_USER="dsim"
+
+# Database Password
+DB_PASSWD="${DB_USER}"
+
+# Database Name
+DB_NAME="dsim"
+
+# Count the number of elements of a given database table
+function countElements() {
+	echo
+	echo "Number of elements for the '${DB_TABLE}' table"
+	SQL_QUERY="select count(*) from ${DB_TABLE}"
+	mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "${SQL_QUERY}" ${DB_NAME}
+}
+
+#
+TABLES="airlines"
+
+# Count rows
+for table_name in ${TABLES}
+do
+	DB_TABLE="${table_name}"
+	countElements
+done


Property changes on: trunk/stdair/db/admin/count_all_tables.sh
___________________________________________________________________
Added: svn:executable
   + *

Added: trunk/stdair/db/admin/create_dsim_db.sh
===================================================================
--- trunk/stdair/db/admin/create_dsim_db.sh	                        (rev 0)
+++ trunk/stdair/db/admin/create_dsim_db.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,60 @@
+#!/bin/sh
+#
+# Three parameters are optional:
+# - the name of the database (on the MySQL server)
+# - the host server of the database
+# - the port of the database
+#
+
+# Database Name
+DB_NAME="dsim"
+
+# Database server (host)
+DB_HOST="localhost"
+
+# Database server port
+DB_PORT="3306"
+
+if [ "$1" != "" ]; then
+	DB_NAME=$1
+fi
+
+if [ "$2" != "" ]; then
+	DB_HOST=$2
+fi 
+
+if [ "$3" != "" ]; then
+	DB_PORT=$3
+fi
+
+
+if [ "$1" = "--help" -o  "$1" = "-h" -o "$1" = "-H" ]; then
+	echo "Usage: $0 [ <Name of Database> <Host> <Port> ]"
+	echo ""
+	exit -1
+fi
+
+#
+echo "Accessing MySQL database hosted on $DB_HOST:$DB_PORT to create database '${DB_NAME}'."
+echo "To create a database, username and password of an administrator-like MySQL account"
+echo "are required. On most of MySQL databases, the 'root' MySQL account has all"
+echo "the administrative rights, but you may want to use a less-privileged MySQL"
+echo "administrator account. Type the username of administrator followed by "
+echo "[Enter]. To discontinue, type CTRL-C."
+read userinput_adminname
+
+echo "Type $userinput_adminname's password followed by [Enter]"
+read -s userinput_pw
+
+# Database user
+DB_USER=${userinput_adminname}
+
+# Database password
+DB_PASSWD=${userinput_pw}
+
+#
+SQL_STATEMENT="create database if not exists ${DB_NAME} default character set utf8 collate utf8_unicode_ci"
+
+#
+echo "The database '${DB_NAME}' will be created:"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} mysql -e "${SQL_STATEMENT}"


Property changes on: trunk/stdair/db/admin/create_dsim_db.sh
___________________________________________________________________
Added: svn:executable
   + *

Added: trunk/stdair/db/admin/create_dsim_tables.sql
===================================================================
--- trunk/stdair/db/admin/create_dsim_tables.sql	                        (rev 0)
+++ trunk/stdair/db/admin/create_dsim_tables.sql	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,41 @@
+-- MySQL dump 10.11
+--
+-- Host: localhost    Database: dsim
+-- ------------------------------------------------------
+-- Server version	5.0.67
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `airlines`
+--
+
+DROP TABLE IF EXISTS `airlines`;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+CREATE TABLE `airlines` (
+  `iata_code` char(2) NOT NULL,
+  `name` varchar(100) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+SET character_set_client = @saved_cs_client;
+
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2009-02-09  0:52:34

Added: trunk/stdair/db/admin/create_dsim_user.sh
===================================================================
--- trunk/stdair/db/admin/create_dsim_user.sh	                        (rev 0)
+++ trunk/stdair/db/admin/create_dsim_user.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,65 @@
+#!/bin/sh
+#
+# Three parameters are optional:
+# - the name of the database (on the MySQL server)
+# - the host server of the database
+# - the port of the database
+#
+
+# Database Name
+DB_NAME="mysql"
+
+# Database server (host)
+DB_HOST="localhost"
+
+# Database server port
+DB_PORT="3306"
+
+if [ "$1" != "" ]; then
+	DB_NAME=$1
+fi
+
+if [ "$2" != "" ]; then
+	DB_HOST=$2
+fi 
+
+if [ "$3" != "" ]; then
+	DB_PORT=$3
+fi
+
+
+if [ "$1" = "--help" -o  "$1" = "-h" -o  "$1" = "-H" ]; then
+	echo "Usage: $0 [ <Name of Database> <Host> <Port> ]"
+	echo ""
+	exit -1
+fi
+
+#
+echo "Accessing MySQL database hosted on $DB_HOST:$DB_PORT to create database '${DB_NAME}'."
+echo "To create a database, username and password of an administrator-like MySQL account"
+echo "are required. On most of MySQL databases, the 'root' MySQL account has all"
+echo "the administrative rights, but you may want to use a less-privileged MySQL"
+echo "administrator account. Type the username of administrator followed by "
+echo "[Enter]. To discontinue, type CTRL-C."
+read userinput_adminname
+
+echo "Type $userinput_adminname's password followed by [Enter]"
+read -s userinput_pw
+
+# Database user
+DB_USER=${userinput_adminname}
+
+# Database password
+DB_PASSWD=${userinput_pw}
+
+#
+createDSimUser() {
+	echo "Creating the DSim user within the database:"
+	mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} ${DB_NAME} < ${SQL_FILE}
+	mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} -e "flush privileges"
+}
+
+# Creating the DSim user
+SQL_FILE="create_dsim_user.sql"
+createDSimUser
+


Property changes on: trunk/stdair/db/admin/create_dsim_user.sh
___________________________________________________________________
Added: svn:executable
   + *

Added: trunk/stdair/db/admin/create_dsim_user.sql
===================================================================
--- trunk/stdair/db/admin/create_dsim_user.sql	                        (rev 0)
+++ trunk/stdair/db/admin/create_dsim_user.sql	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,7 @@
+
+insert into `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) values
+('%', 'dsim', '6b05771f692d78a8', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'N', 'N', 'Y', 'Y', 'Y', 'Y', 'N', '', '', '', '', 0, 0, 0, 0),
+('localhost', 'dsim', '6b05771f692d78a8', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'N', 'N', 'Y', 'Y', 'Y', 'Y', 'N', '', '', '', '', 0, 0, 0, 0);
+
+flush privileges;
+

Added: trunk/stdair/db/admin/dump_dsim_db_structure.sh
===================================================================
--- trunk/stdair/db/admin/dump_dsim_db_structure.sh	                        (rev 0)
+++ trunk/stdair/db/admin/dump_dsim_db_structure.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,51 @@
+#!/bin/sh
+#
+# Two parameters are required for this script:
+# - the administrator username
+# - the administrator password
+#
+# Two parameters are optional:
+# - the host server of the database
+# - the port of the database
+#
+
+if [ "$1" = "" -o "$2" = "" -o "$1" = "-h" -o "$1" = "--help" ];
+then
+	echo "Usage: $0 <Admin Username> <Admin password> [<Database Server Hostname> [<Database Server Port>]]"
+	echo ""
+	exit -1
+fi
+
+##
+# Database Server Hostname
+DB_HOST="localhost"
+if [ "$3" != "" ];
+then
+	DB_HOST="$3"
+fi
+
+# Database Server Port
+DB_PORT="3306"
+if [ "$4" != "" ];
+then
+	DB_PORT="$4"
+fi
+
+# Database User
+DB_USER="$1"
+
+# Database Password
+DB_PASSWD="$2"
+
+# Database Name
+DB_NAME="geo_geonames"
+
+# Export Tool
+EXPORTER=mysqldump
+
+# Dump file
+DUMP_FILE="geo_geonames.sql"
+
+#
+${EXPORTER} -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} \
+	--no-data ${DB_NAME} > ${DUMP_FILE}


Property changes on: trunk/stdair/db/admin/dump_dsim_db_structure.sh
___________________________________________________________________
Added: svn:executable
   + *

Added: trunk/stdair/db/admin/fill_dsim_tables.sql
===================================================================
--- trunk/stdair/db/admin/fill_dsim_tables.sql	                        (rev 0)
+++ trunk/stdair/db/admin/fill_dsim_tables.sql	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,13 @@
+--
+-- Note: that file is expected to be launched from the 
+--       $(top_srcdir)/db/admin sub-directory, as the CSV files are 
+--       to be found in $(top_srcdir)/db/data sub-directory
+--
+
+--
+-- Load the Airport and City geographical details into the MySQL table
+--
+load data local infile '../data/airlines.csv' ignore 
+into table airlines
+fields terminated by ',' enclosed by '' escaped by '\\' 
+ignore 1 lines;

Added: trunk/stdair/db/admin/load_dsim_data.sh
===================================================================
--- trunk/stdair/db/admin/load_dsim_data.sh	                        (rev 0)
+++ trunk/stdair/db/admin/load_dsim_data.sh	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,47 @@
+#!/bin/sh
+#
+# Two parameters are optional:
+# - the host server of the database
+# - the port of the database
+#
+
+if [ "$1" = "-h" -o "$1" = "--help" ];
+then
+	echo "Usage: $0 [<Database Server Hostname> [<Database Server Port>]]"
+	echo ""
+	exit -1
+fi
+
+##
+# Database Server Hostname
+DB_HOST="localhost"
+if [ "$1" != "" ];
+then
+	DB_HOST="$1"
+fi
+
+# Database Server Port
+DB_PORT="3306"
+if [ "$2" != "" ];
+then
+	DB_PORT="$2"
+fi
+
+# Database User
+DB_USER="dsim"
+
+# Database Password
+DB_PASSWD="dsim"
+
+# Database Name
+DB_NAME="dsim"
+
+# Create the tables
+SQL_FILE="create_dsim_tables.sql"
+echo "Creating the tables for DSim:"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} ${DB_NAME} < ${SQL_FILE}
+
+# Load the data into the tables
+SQL_FILE="fill_dsim_tables.sql"
+echo "Load data into the tables of DSim:"
+mysql -u ${DB_USER} --password=${DB_PASSWD} -P ${DB_PORT} -h ${DB_HOST} ${DB_NAME} < ${SQL_FILE}


Property changes on: trunk/stdair/db/admin/load_dsim_data.sh
___________________________________________________________________
Added: svn:executable
   + *

Added: trunk/stdair/db/data/airlines.csv
===================================================================
--- trunk/stdair/db/data/airlines.csv	                        (rev 0)
+++ trunk/stdair/db/data/airlines.csv	2010-02-06 13:33:54 UTC (rev 114)
@@ -0,0 +1,10 @@
+// Airlines: IATA Code, Full name,
+BA, British Airways,
+LH, Lufthansa,
+AF, Air France,
+KL, KLM,
+IB, Iberia,
+AA, American Airlines,
+UA, United Airlines,
+DL, Delta Airlines,
+CO, Continental Airlines,


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.