TT380 - A bluffer's guide

Tonight is the our New Entrepreneur Scholarship graduation. The event is being held at the Ramside Hall Hotel in Durham. I have been elected by my fellow entrepreneurs to give a short speech. I am looking forward to the whole evening and would like to thank those who were involved in the programme, namely Eric Smalley, Neil Hannah, Katy Rain and Julia Gilliam from the University of Teesside.

What this blog is for

This blog is a set of pages pretending to be a blog. Actually it is the bluffer's guide to TT380. To help you bluff your way through this course start at the beginning, or not; it's up to you!!

If you start at the beginning then the next article is linked at the end of each page.

Setting up local services for TT380
STAGE 1: Install Apache and ColdFusion Express
STAGE 2: Install mySQL and myODBC
mySQL Hour 4
mySQL Hour 5
mySQL Hour 6
mySQL Hour 7
mySQL Hour 7
Securing a MySQL Server on Windows
Time to do stuff
1. Sort out the password for the root user
2. create a new user to mimic the settings on SSH / OU server
3. Create the contactDB database...
4. Define user privileges
5. create tables in contactDB
Cool resources
Installing ColdFusion MX 7 onto a Windows XP Pro SP2 system

Whenever this article says it was posted, it wasn't. It probably isn't even 2006 yet!

Using the INSERT method

Okay this is the exact procedure that I used to perform this data insert. Using SSH secure shell open a terminal window to the OU server. Change to your web directory using the following command syntax:

cd web

Log into the MySQL monitor using the following syntax, changing the jg3723 to your OU username:

mysql -u jg3723 -p

When prompted enter you OU PI number remember letters should be in CAPITALS. Select the database to be used, in this case it should be the same name as you OU username, using this command syntax:

USE jg3723;

Now its time to write the INSERT statement, for test purposes you could use the following, which is what I entered:

INSERT INTO master_name
VALUES ('',now(),now(),'Amanda','Gurney');

Hopefully, you should see the following message after pressing the ENTER key.

Query OK, 1 row affected (0.00 sec)

Well that's the INSERT method successfull used, so it's now onto the LOAD DATA method next.

Using the LOAD DATA Method

With this method I pre wrote a DDL script and a plain text file that contained the data to be imported, these two files were then uploaded to my OU web directory If you would like to use my DDL script and data file click here to down load them.

Hopefully you will still have your SSH terminal window open and logged into your MySQL database. If not please connect and login to your database again.

You now need to change the DDL file to point to your own database; using Notepad open the loaddata.sql file and change the first line so it reads the name of your database. Then save the file and close it.

Now upload the DDL script and data file to your OU web directory. To check that they are there use the following syntax:

ls

Hopefully you will see the a list similar to mine below:

[jg3723@ttcfm web]$ ls
all_names.cfm  createtables.sql  loaddata.sql     mytidy.sql
contactDB.sql  eca               master_name.txt
[jg3723@ttcfm web]$ 

Okay the files are there so let's import the data. This is done by using the following syntax:

source loaddata.sql

Hopefully you will see the following messages displayed after pressing the ENTER key.

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

Query OK, 4 rows affected (0.00 sec)

Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

My Conclusion

Well that's the end of my LOAD DATA and INSERT summary, you should now be able to alter the various commands and files to import any data that you wish to insert into your database. Have fun!

All the best Jayson

The aim of this installation guide is to give you a clear and easy to understand guide for installing a local version of ColdFusion MX 7 Developer Edition onto a Windows XP Pro SP2 platform.

Step 1: Download the required installation files
Step 2: Prepare system for installation
Step 3: Install ColdFusion MX7 Developer Edition
Step 4: Install and Configure MySQL ODBC data source
Step 5: Test your installation and configuration
Step 6: Congratulations!

STEP 1: OBTAIN THE REQUIRED INSTALL FILES

The ColdFusion Express version 4.0 that is on the Cert in Web Apps Development CD-ROM has caused me numerous nightmares when it came to configuring my MySQL ODBC data source. My answer was to install a copy of the latest release ColdFusion MX 7 Developer Edition. This version is as far as I am currently aware free for development use so there is no time limit on its usage.

Okay let’s get the download! Using you web-browser, enter the following URL:

http://www.macromedia.com/cfusion/tdrc/index.cfm?product=coldfusion

If you are not already registered with Macromedia, you will need to do this and then revisit the above URL. Registration is FREE, simple and rather painless.

When you return to the download page select the option to download ColdFusion MX 7 Developer Edition, which should be at the top of the page, then select the English Windows 275.79 MB option using the select button, then click on download. Save the file a suitable location on your system, so that you can easier retrieve it later.

BE WARNED that this download can take some time depending on you particular internet connection.


STEP 2: PREPARE SYSTEM FOR INSTALLATION

Whilst the installation file is downloading, it’s a good idea to gather the necessary information ready for your installation. Please remember that this installation brief is for use with an OpenSA web-server (Apache), which is distributed on the Cert in Web Apps Development CD-ROM. Note of the following information which is what I personally used for my installation, I also used the default installation for OpenSA, so if you decided to install in any other manner you will need to alter the information below to mirror your own installations:

What is the serial number for ColdFusion MX 7? USE DEVELOPER EDITION

What is the type of installation? SERVER CONFIGURATION

ColdFusion MX 7 installation directory? C:\CFusionMX7

Configure web server or use built-in web server? CONFIGURE

Choose web server to configure? APACHE

Apache configuration directory? C:\OpenSA\Apache\Conf

Apache path to server binary? C:\OpenSA\Apache

ColdFusion MX Administrator password? YOU CHOOSE THIS AND KEEP IT A SECRET

Do you want to enable RDS? NO


STEP 3: INSTALL COLDFUSION MX7 DEVELOPER EDITION

Okay you have the downloaded installation file and the required installation notes. Let’s get on with the main installation then. Ensure that the Apache web server is running you can do this by checking the service via the Windows XP Administrators control panel. Shut down all previous ColdFusion services if you have previously installed them – again this can be performed via the Windows XP Administrators control panel.

Now double click on your downloaded installation file and follow the on screen instructions. Be sure to use the information that we gathered in Step 2, to ensure that the correct information is given to the installation routine.

Hopefully all went well for you and you now have an installed ColdFusion MX 7 server.


STEP 4: INSTALL AND CONFIGURE MYSQL ODBC DATA SOURCE

Using the ColdFusion Administrator which is accessed via your web-browser and is delivered to you via your local web-server we are now going to configure your MySQL data source.

Open your web-browser and enter the following address:

http://localhost/cfide/administrator/index.cfm

Enter the password that you set during the setup routine then click on the login button. You will then be in the ColdFusion Administrator, so now on the left you will see a link to Data & Services, click on this and then click on the Data Source link.

We are now going to add a new data source, so enter the name of the data source, for the purposes of this installation guide enter the following: chelsea then using the selection button below select MySQL (3.x) as the driver. Then click the add button.

The next page is where we are going to configure this new data source. Enter the following information into their respective boxes:

Database contactDB
Server localhost
Username root
Password your MySQL root password

Then click the submit button, if all was ok with the data that you have just entered, the screen will change back to the original Data Source screen, BUT the newly created data source named chelsea will be verified as OK.


STEP 5: TEST YOUR INSTALLATION AND CONFIGURATION

Using your HTML editor or Notepad, enter the following test code and then save then save it as odbctest.cfm


<html>
<head>
<title>ColdFusion ODBC Test</title>
</head>
<body>
<cfquery name="databases" datasource="chelsea" debug="yes">
show databases;
</cfquery>
<cfoutput>
<p>Today is #DateFormat(now())#</p>
</cfoutput>
</body>

Once the above has been created and saved it to your root web directory, open up your web-browser and request the page via you local web-server (setting this up has been discussed and documented in a previous BLOG)

Calling the file is simple – enter the following into your web-browser address bar:

http://localhost/odbctest.cfm

If all is working correctly then the page will display the current date in your browser. Please note that the script only attempts to make a connection to your data source, and that no database data will be displayed. If the current date is not displayed then unfortunately, something is a miss. But if all goes well this will not be the case.


STEP 6: CONGRATULATIONS!

If you managed to get the current date displayed in your web-browser, then it looks like you have successfully installed and configured ColdFusion MX 7 Developer Edition to connect and access your MySQL data source.

WELL DONE!

So what next I hear you say – well the world is your oyster as they say. Now that this has been configured and tested, you can start to develop your own ColdFusion pages.

I hope that this BLOG helps some of you with you local installation of ColdFusion MX 7, if you have any comments feel free to post them.

BTW - this was written from memory so I have my fingers crossed and hope that there are no errors, but I am certain that you will be the first to tell me if there is - any errors will be investigated and subsequent ammendments will be made.

All the best Jayson

Okay, this is the page dedicated to lots of cool freeware and trialware for doing mySQL and ColdFusion stuff.

1. http://www.microolap.com/products/database/mydesigner/

I've been playing around with a few ER diagramming/database design programs and came across one specifically for MySQL. It has a 30 day trial period, but unlike most of them it has a tutorial section in the help, and doesn't attempt to explain things as if you're a space cadet. It's well worth a look, I think it sells for around $120 US, which is probably a bit steep to justify unless you are doing this for a living, but it is pretty simple to use and the results are good. It can even hook up to your DB and create the database for you saving the job of saving the script and importing the SQL yourself ! (what I particularly like is you can instantly preview the table creation SQL as you are editing the table design, and it also helps that it isn't a generic program designed to work with a multitude of RDBMS)

The only mistake I found in the tutorial was that you need to deselect the table drawing tool before double clicking to define your table columns etc...

well worth a play if you want to experiment with this kind of thing.

Simon.


2. Questions of scalability, security, and connectivity.

The following white paper from Cisco provides a nice introduction to scalability and looks at their LocalDirector product.

http://www.cisco.com/en/US/products/hw/contnetw/ps1894/products_white_paper09186a0080091edf.shtml

It is available as a PDF for download. There is also one of security (also in PDF), but with a network architecture perspective.

http://www.cisco.com/en/US/about/ac123/ac114/ac173/ac166/small_and_midsized_business09186a00800a346b.html

You can find all the current unisys benchmark papers at

http://www.unisys.com/products/es7000__servers/hardware/benchmark__reports/index.htm

The benchmarks quoted are those of the TPC, so follow at the TPC-W link. The executive summary will give you the total cost of this sort of system.



Glyn

3. Horizontal Scaling

http://www.computerworld.com/hardwaretopics/hardware/server/story/0,10801,73688,00.html?from=story_package

If anyone has anymore then please add a comment with a URL. Thanks.

right this'll be it tonight cos I'm v.tired now (ahhh!)

no flannel, just stuff...

1. create the first table at the command prompt

as per the book (hour 8) i create my first table at the command prompt... like this:


C:\mysql\bin>mysql -usupercontact -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use contactDB;
Database changed
mysql> CREATE TABLE master_name (
-> name_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
-> name_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
-> firstname VARCHAR (75),
-> lastname VARCHAR (75),
-> INDEX idx_fn (firstname),
-> INDEX idx_ln (lastname)
-> );
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+---------------------+
| Tables_in_contactdb |
+---------------------+
| master_name |
+---------------------+
1 row in set (0.00 sec)

mysql>

right, done that...

2. type the table definitions into a .sql text file

write the file in notepad, for the practice... like this


USE contactDB;

CREATE TABLE job_function (
job_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
job_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
job_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
jobfunction VARCHAR (100),
INDEX idx_job (jobfunction)
);

CREATE TABLE company (
company_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
company_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
company_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
companyname VARCHAR (100),
INDEX idx_job (companyname)
);

CREATE TABLE name_company_map (
name_id SMALLINT UNSIGNED NOT NULL,
company_id SMALLINT UNSIGNED NOT NULL
);

CREATE TABLE address (
add_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
add_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
add_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
streetaddress VARCHAR (255),
city VARCHAR (50),
state CHAR (2),
zipcode VARCHAR (10),
add_type ENUM ('home','work','other')
);


CREATE TABLE telephone (
tel_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
tel_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
tel_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
tel_countrycode CHAR (3),
tel_number VARCHAR (25),
tel_type ENUM ('home','work','other')
);


CREATE TABLE fax (
fax_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
fax_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
fax_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
fax_countrycode CHAR (3),
fax_number VARCHAR (25),
fax_type ENUM ('home','work','other')
);

CREATE TABLE email (
email_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
email_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
email_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
email_countrycode CHAR (3),
email_number VARCHAR (150),
email_type ENUM ('home','work','other')
);

CREATE TABLE personal_notes (
notes_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
notes_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
notes_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
note TEXT
);

if you do cut and paste my table defns. check it over for syntax errors cos it's late now...

then

3. add all the tables to contactDB

C:\mysql\bin>mysql -usupercontact -pA1234567 < c:\mysqldefs\ab_contactDB.sql

C:\mysql\bin>mysql -usupercontact -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use contactDB;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_contactdb |
+---------------------+
| address |
| company |
| email |
| fax |
| job_function |
| master_name |
| name_company_map |
| personal_notes |
| telephone |
+---------------------+
9 rows in set (0.00 sec)

mysql>

that's all good, time for bed!

Right, in the last segment I created the contactDB database using "ab1234" with password "A1234567". Now I'm going to create some users and give them privileges...

COMMENT: First off I try to define the user permissions using "ab1234"

C:\mysql\bin>mysql -uab1234 -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
Database changed
mysql> GRANT ALL ON contactDB.* TO supercontact@'localhost' IDENTIFIED BY 'A1234567';
ERROR 1044: Access denied for user: 'ab1234@localhost' to database 'contactDB'
mysql> exit
Bye

COMMENT: That doesn't work so maybe we do have to be logged in as root to create users and define privileges? Try again, this time logged in as root...

C:\mysql\bin>mysql -uroot -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
Database changed

mysql> GRANT ALL ON contactDB.* TO 'supercontact'@'localhost' IDENTIFIED BY 'A1234567';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON contactDB.* TO 'simpleuser'@'localhost' IDENTIFIED BY 'A1234567';
Query OK, 0 rows affected (0.00 sec)

mysql>

COMMENT: No complaints, created users and defined privileges successfully as the root user...

Back to DB Manager to check the users are there...yes! (you can make your own screen shot!)5. create tables in contactDB

Bizarrely seconded into a T171 chat room for several minutes... nice talking to you all Roger, Les, Charmaine good luck with the course!!! How unfair is that though, they're only level 1 students and they get a chat room to use, surely there's a pecking order to these things!!! Oh well!!! Back to the case study forum and the tumbleweed blowing through TT380 FirstClass forums...

Where was I? Oh yes, creating a contactDB

So carrying on from before....


mysql> exit
Bye

C:\mysql\bin>mysqladmin -u ab1234 -pA1234567 create contactDB

C:\mysql\bin>


I'm following the book, so I exit from mysql monitor (mysql>) and execute the mysqladmin command at the DOS prompt...

To check to see it has worked I went to DB Manager...



Bingo, I refreshed the window using the lightning strike and found my database....

Onto create the tables....
4. Define user privileges

right, next task...

so taken Alistair's thoughts forward I create a new user:

still logged into the mysql monitor as 'root':

mysql> GRANT ALL ON *.* TO 'ab1234'@'localhost' IDENTIFIED BY 'A1234567';

I'm using the same password for root as my nascent super user...

Time to see if I can check what I've done. It's off to DBManager...

First signs are promising, I type in my new 'root user' password and DBManager accepts it and lets me in.

Right lets see what's going on in DBManager.... Simon Moat said something about this earlier in the case study forum... off to find it...

Found Simon's Screen Shot in the conference and the first thing I notice is that his version looks newer. You can tell by the XP rounded corners I think. I'm running 2.2.0 from the Cert in WAD CD, I'll have to ask Simon what version he's using...

Here's my screen shot...



Anyway I've found my users - it looks like I've got <blank>, root and ab1234 - so we're okay so far!!

What does <blank> do though????

Not to worry too much about <blank> just now let's do something else...

3. Create the contactDB database...

Well I've been all over several forums last night and today trying to understand what this is all about. Basically the installation of mySQL has a "root user", i.e. all powerful user that can do anything in mySQL, and, by default, the root user doesn't have a password. This means that if nasty people gain access to the server that is hosting mySQL then they can mess things up quite badly.

It is easy to get diverted into a discussion about securing the mySQL server but that is a whole other world of pain and additional complication. For the purposes of working through TT380 the smart money is saying that you don't need to worry about it too much (no warranty is implied in that statement!).

So let's stop prevaricating and get on with it.

How do you set a password for the root user?

Okay, most of us (Windows) users will be going into mysql monitor from the DOS window.

It goes something like this:

C:\Documents and Settings\Dell>cd c:\mysql\bin

C:\mysql\bin>mysql -u root -p
Enter password:
COMMENT: if you haven't already set up a password for 'root' then hit enter at the password prompt and you're in
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

COMMENT: this is the code that sets your root password

mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('A1234567');
Query OK, 0 rows affected (0.03 sec)

COMMENT: after you've changed the password FLUSH PRIVILEGES just to make sure

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

COMMENT: I tried logging in without a user name and got straight in. I don't know what type of user you login as as default but right now I don't care. I'm assuming it's not the 'root' user

C:\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

COMMENT: Then tried logging in as 'root' without a password and got bounced...

C:\mysql\bin>mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

COMMENT: Then tried logging in as 'root' with my new password and was let in.

C:\mysql\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Overall, fairly easy to do once you've got the code sorted...what's next...

Next to create some users so I can mimic what is going on in mySQL in 24hrs.

Why am I going to create a new user, why not just use the root user?

Well I'm keeping in mind some tips from Alistair Thomas, just seems using the root user is bad form and something to be avoided. Why that should be will no doubt become clearer as we become more familiar with mySQL:

"Having become familiar with Linux this kind of stuff is fairly similar, i.e. the command line and different user accounts. I have a few guidelines for myself:

- Make sure the root account is secure, i.e. it has a password atleast!

COMMENT: we've just done that!

- Don't use the root account. This is a golden rule which is only broken when:

• you need to add or delete a user,
• change their privileges,
• create or drop a database.

Once you've finished breaking your golden rule log straight out and then back in as a normal user. Hopefully you've given that user sufficient, but only sufficient, privileges to do what they need.

- Break things down into simple testable steps. e.g. if you create a table type do a show table command to see if it has done what you want. After a while you will become more familiar with the set up and won't need the test step as often, but it always seems to help me to know how to check what I've done.

Thanks Alistair.

Alistair is saying above, amongst other things, that only the root user can be used to create and drop databases. I'm just wondering if that is strictly true as i successfully logged in as "default" and I've been able to create and drop tables there before. we'll see...




2. create a new user to mimic the settings on SSH / OU server

Okay, I've hit a couple of stumbling blocks over the past couple of days, now it's time to sort them out.

On the To Do list tonight:

• 1. Sort out the password for the root user


• 2. create a new user to mimic the settings on SSH / OU server


• 3. Create the contactDB database...


• 4. Define user privileges


• 5. create tables in contactDB

That ought to do it for now, if I get throught it all I might do some extra bits as well. Here goes....



1. Sort out the password for the root user

This article tells you how to secure a mySQL in windows. I haven't read it yet, it was recommended to me by someone.

Hope it is of use to someone.

we continue...

Hour 7 promises to teach:

• data types


• table creation syntax


• preliminary statements to create tables

Data Types

Three types of data type:

• numeric


• data and time


• string

This is what they do:

Numeric	Properties	Date and Time	Properties	String	Properties
TINYINT	-128 to 127 or 0 to 255	DATE	YYYY-MM-DD	CHAR
SMALLINT	-32768 to 32767 or 0 to 65535	DATETIME	YYYY-MM-DD HH:MM:SS	VARCHAR
MEDIUMINT	-8388608 to 8388607 or 0 to 16777215	TIMESTAMP	default length is 14 which stores YYYYMMDDHHMMSS less than length 14 stores less	BLOB or TEXT
INT or INTEGER	-2147483648 to 2147483647 or 0 to 4294967295	TIME	HH:MM:SS	TINYBLOB or TINYTEXT
BIGINT	-2147483648 to 2177483647 or 0 to 18446744073709551615	YEAR	YY or YYYY default length is 4	MEDIUMBLOB or MEDIUMTEXT
FLOAT (M,D)	up to 24 d.p.			LONGBLOB or LONGTEXT
DOUBLE (M,D) or REAL (M,D)	up to 53 d.p.			ENUM
DECIMAL (M,D) or NUMERIC(M,D)	each digit is one byte

i'll finish that table off later if i get time...

we continue...

Oh well, I've crashed my user ID in the new forum. Great, I have to wait for Nick to get back from holiday to fix it, because, apparently he's the only one who can.

So, instead of frittering hours away in the conference, I'll fritter hours away on my blog and trying to second guess the typos and ommissions in the various course materials. Just as well we're not all daft doing this course!

Determine you goals

Do it!

• Name


• Address


• Telephone Number


• Fax Number


• Email


• Job


• Company


• Personal notes

Conceptualise the tables

I'm conceptualising them right now!!!

Split it up into "single entry" and "multiple entry" tables:

Single	Multiple
Name Job Company	Address Telephone Number Fax Number Email Personal notes

Look just give everthing an ID alright?

Then add Date Added and Date Modified fields.

Determining Relationships

Well if you know how to do it, it's easy; if you don't, it's still easy!!!

Basically break everything down into objects (entities I think the database terminology calls them), that's right, everything. Then give each thing its own ID.

Once you've done that add an ID for the thing that it's related to. In this example all the things are related to a person. People are stored in the Name table so add a nameID field to all the other tables in your schema (ooo!!! proper database word!).

The book (p.69) shows you the updated field name list for each table. Except that the Company table hasn't got a nameID added to it. This might be what they meant but I'm guessing that it's another TYPO!!!!!!!! We'll see ;)

Apparently we've just cracked the second normal form where the rule is "No non-key attributes depend on a portion of the primary key". So now you know.

Creating the Database

Huzzah!!! We're going to create a database now!!!

1. Create a database. At the c:\mysql\bin> prompt type:mysqladmin -u username -ppassword create contactDB
   
2. Connect to mySQL monitor as the main (root - though I think I replaced that when I installed) user: /usr/local/bin/mysql -h localhost -u root --pyourpass


3. Switch to the mySQL database. At the mysql> prompt type: use mysql.


4. GRANT ALL ON contactDB.* TO supercontact@'yourhost' IDENTIFIED BY 'somepass';


5. GRANT SELECT ON contactDB.* TO simpleusert@'yourhost' IDENTIFIED BY 'somepass';

Summary

Well, we finally got there. Time to revisit my myODBC/mySQL installations and see if I can make them talk.

Soon we'll be off to mySQL Hour 7. Stay tuned.

Is all about security

The first thing to do (for Linux/UNIX users apparently, p.54, not quite sure what Windows users are supposed to do) is check the owner of the "MySQL daemon".

Make sure it's not the "root" user or there could be trouble.

Under Linux / UNIX:

ps auxw | grep mysqld

will reveal who owns "daemon".

Securing your mySQL connection

If you run mySQL on your own workstation protect your mySQL setup with a screensaver password when you leave your PC unattended. Or lock your front door, the choice is yours.

If you run mySQL on a server outside of your network then take precautions to make sure nasty people don't get hold of your login information. You can use, wait for it, SSH to encrypt the traffic you send over the web or, if you are using something browser based like phpMyAdmin, you should use the HTTPS protocol via SSL (Secure Sockets Layers - don't know, don't ask, haven't done TT282 yet, just do it alright!).

Luckily I've already installed SSH, I may blog how at some point...

Introducing the mySQL privilege system

Me: Hello mySQL privelege system. What do you do?

mySQL p.s.: Well, I check

• where you are accessing from (your host)


• who you say you are (username and password)


• what you are allowed to do (your command privileges)

Me: Thanks. Where do you store this information pray tell?

mySQL p.s.: Hmmm, let me think...

• columns_priv - defines user privileges for specific fields in a table


• db - defines permissions for all databases on the server


• func - defines user-created functions


• host - defines the acceptable hosts that can connect to a specific database


• tables_priv - user privileges for specific tables


• user - command privileges for a specific user

You'll need these in a minute when you add a few sample users to mySQL you know.

Me: Will I? Cheers....

2-step authentication process

mySQL checks 3 things during the authentication process, and it does it in 2 stages:

1. the host you are connecting from and the username / password pair you are using


2. whatever command (SELECT, UPDATE yadda yadda yadda) you're using, it makes sure you're allowed

If you can't be verified, you can't get in. There's a bit more to it than that, but remember we're bluffing.

Working with user privileges
Adding Users

The simplest way to add a new user is via the GRANT command. Or you can INSERT into all the relevant tables in the database, sounds like work to me!

So here we go:

GRANT privileges
ON databse.tablename
TO username@host
IDENTIFIED BY 'password';

Privileges that may be GRANTed:

ALL
ALTER
CREATE
DELETE
DROP
FILE
INDEX
INSERT
PROCESS
REFERENCES
RELOAD
SELECT
SHUTDOWN
UPDATE
USAGE

For example:

GRANT SELECT, INSERT
ON myDB.*
TO john@"%"
IDENTIFIED BY '99hjc';

Note two wildcards:

* replaces the entire list of tables
% replaces all known hosts anywhere!

Although GRANTing privileges should be automatically updated, you can FLUSH PRIVILEGES to reload the privilege tables. Super.

Removing Privileges

The opposite of GRANT.

For example:

REVOKE INSERT
ON myDB.*
FROM john@'%';

Will ruin john @ anywhere he damn well chooses' day if he wants to execute INSERT commands on any tables in myDB.

FLUSH PRIVILEGES afterwards, just to make sure.

Summary

Well, very interesting but still a little unsure about this whole setting up users thing.

I think I'll crack on through mySQL Hour 6 and see if that illuminates the issue further.

Okay, mySQL Hours 1 to 3 covered basic databasey stuff. Hour 3 touched on "normalisation", which is something I haven't come across in my many hours playing around with the QBE grid in MS Access!

Hour 4 gets into the detail of how mySQL actually works.

We are introduced to several mySQL applications; most bundled with the distribution, some that need to be got from other sources.

mySQL monitor

First up is mySQL monitor. This is a command line interface (read the book p.40!) with mySQL.

• Start up your DOS prompt (in Windows: Start>Run type in either "dosprompt" or "cmd", dependent on your Windows version).


• Change directory: cd C:\mysql\bin


• Start mysql monitor by typing: mysql

Bingo, you've started mysql monitor and have a mysql> prompt in your DOS window.

If your using Linux then it's a bit different but not much.

Then onto trying out some commands:

use test;

Being about the extent of it for mysql monitor.

Before you move on though, and the book doesn't tell you this, you need to end your mysql monitor session and get back to the c:\mysql\bin> prompt or mysqladmin won't work.

Here's how: type exit at the mysql> prompt.

Handy DOS window tip

Okay, you can probably figure this yourselves but let me save you the bother.

You can't cut and paste windows-styley in the DOS window, it just doesn't do that! Instead right-click your mouse, select Select All and then hit Enter / Return. Strangely enough the contents of the DOS window are copied to your clipboard and you can then paste them using Ctrl + V into your favourite, Notepad.

Means you can print out the scree from mysqladmin --help (see below) and read it at you leisure.

Escaping characters

Veterans of TT281 (aka Javascript) will remember that sometimes you need to "escape characters". You need to do it in SQL too! Luckily the escape character is the good old backslash : "\". So no excuses!

En passant we are told to use either single or double quotes consistently. It doesn't matter, except that single quotes (like this: ') are the wider SQL standard. I'll be using single quotes then.

mySQL administration

Start up DOS, change directories and then start using mySQL administration.

It's not hard, you know when you're using mySQL admin because you type:

mysqladmin

in front of everything you do.

You can:

mysqladmin create test_DB
mysqladmin drop test_DB

and even

mysqladmin status

Go on, you can do it! And it's fun!

mysqldump

While you're there at the c:\mysql\bin> prompt you can mysqldump too!

You can

mysqldump test_DB

if you haven't just "dropped" it (keep up!).

You can

mysqldump test_DB > mydump.sql

which dumps your dbase into an sql file - handy.

When you get stuck you can

mysqldump --help

and it helps you. Complicated isn't it?

What about the ;'s?

Every so often something pops up about commands ending in ;s but they aren't doing it in the book. Why not I wonder? Is it because these are application commands we are issuing here and not propa SQL?????

I think I'll ask....


EDIT: I asked in the Teach forum and this is what Barbara said in reply:

"Just SQL commands must have semi-colons. The command prompts are different - no colons semi or otherwise required!"

Thanks Barbara!


phpMyAdmin

Is a browser based php tool for playing with mySQL. You need to have php installed (see TT381 for further details).

MySQL-Front

IS FREE for Windows users; Linux users, you can't have it, get over it. It's a bit like MSAccess, apparently...

MySQLManager

Is included in the distribution, alledgedly. I'll check later.

Summary

That didn't take an hour did it?

Still haven't found all the bits I think I need about setting up a database and a user so I can verify my myODBC / mySQL install is working okay.

Maybe mySQL Hour 5 will hold the answers?

;0)