Saturday 13 August 2011

MySQL setup

Now we need to setup  MySQL, first we are going to create a user for the java rest stack to connect to MySQL with. Select Server Administration, by double clicking your DB you setup.


Under security click on "Users and Privileges" then to create an account add account on the bottom of the page.
Give them a name and password and remember them, we will need them shortly. When done select apply. This will create a new user in the MySQL.user table, but it will not give the new user any privileges. To give privileges we select "Schema Privileges" tab and highlight the new user. Then click the "Add Entry..." button, use the default settings and select ok. 
I gave the user SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW, CREATE TEMPORARY TABLES and LOCK TABLES privileges. 

This is so they can manipulate the date in the tables, but do not have the ability to change the tables structure (such as ALTER or DROP). That isn't to say that a user can't destroy a database with the UPDATE ability, it is just harder.
When ready save the changes.
*Note that these settings are intended for local use in a tutorial, for a production database you would restrict the user even more.

Now we need to build the DB, that the rest server will use. Go back to the main page and select your DB under SQL Development.
Open the provided SQL files and execute them in the following order. They need to be in this order because some of the tables have dependencies on other tables. (ie one table has a foreign key constraint that references a primary key of a different table. )
1.      createdb.sql
2.      customer.sql
3.      role.sql
4.      customerRoles.sql
5.      session.sql
6.      ServerConfigTypes.sql
7.      serverConfig.sql
8.      createCustomers.sql

Now we need to setup the the rest server stack so it can communicate with mysql db to save our customers in.
The java file com.cred.industries.platform.database.DBConfig it stores the config information we need to connect to the SQL database. Usually it will load the config values from an xml file
            scr/main/resources/dbconfig.xml
But it is also hard-coded with some setup values. (I could not figure out how to get it to load the xml file when running unit tests.)  Change the DBUserName and DBPassword in both the .java and .xml file, to match the names of the user you just created in MySQL. You should not use the default setup, as they are not secure user names and passwords.

Congratulations, you should now have a working rest server stack. To test it either use the Flex client or run the unit tests under
            com.cred.industries.platform.test.CustomerTest
To do this, click on the debug drop-down and select CustomerTest unit test in eclipse.

No comments:

Post a Comment