How do I Create and set up a Database?

This lesson describes a number of aspects relating to creating, setting up, accessing and securing a database in an On-Rev account. Examples are provided that demonstrate how to connect to the newly created database using LiveCode from within an application. A brief coverage of security measures are given that demonstrate the use of access control to the database.

Log into On-Rev cPanel

Log into On-Rev cPanel

The first step is to log in to an On-Rev account using cPanel. cPanel provides access to and control of various settings and properties of the account, but this lesson looks exclusively at database management.

Note: If you are unsure of how to log into your On-Rev account, there is a link in the confirmation e-mail you received when you signed up to On-Rev.

Manage Databases

Manage Databases

Once logged into cPannel it is possible to create a MySQL databases. Scroll down to the Databases panel and select MySQL Databases (1) as shown above.

Create a Database

Create a Database

A new screen opens that prompts for the name of a new database. After specifying the name, select Create Database (1).

Go Back

Go Back

The new database has now been created. Select Go Back (1) to navigate to the MySQL Users screen. This allows users to be added to the new database.

Create a User

Create a User

Enter the new user name (1) and the password (2), (3), then select Create User. Steps (2) and (3) ensure that the password is spelt correctly. The password strength indicator determines how secure the chosen password is.

Add a User to the Database

Add a User to the Database

Once the users has been created, select Go Back (1). This opens a new screen from where the user can be assigned to the database that was created earlier.

Assign the User to the Database

Assign the User to the Database

Scroll to the section Add User to Database. This allows you to assign the user to the database. The database name and the user name are prefixed with your account user name, in this example: jjacant.

Select both the user (1) and the database (2) that have just been created and select Add (3).

Connect to the Database

Now that the database is set up and a users has been assigned, it is possible to connect to the database from within an application. The connection can be established using a single line of LiveCode:

put revOpenDatabase("mysql", "jjacant.on-rev.com", "jjacant_mydatabase", "jjacant_myuser", "tr3sTEtha6Hu") into tDatabaseID

The first parameter specifies the database type. On-Rev uses MySQL databases, which means that type has to be set to mysql. A full list of the other supported database types can be found in the documentation set that accompanies LiveCode.

The second parameter specifies the host. This is the internet protocol (IP) address or domain name of the server hosting the database. If the host is set to localhost or is left blank, the command assumes that the database is running on the local machine.

The third parameter is the database name. In this case, the one that was just created.

The fourth parameter is the user name. In this case, the one that was just assigned to the database.

The sixth parameter is the password. In this case, the one that was just created for the new user.

Note: More detailed documentation for revOpenDatabase can be found here.

Restricting Database Access

Restricting Database Access

By default, it is possible to connect to the database from anywhere, providing the username and password are available. This behavior is desirable for a number of applications, but in some cases it is more appropriate to restrict access from only specific machines. Such restrictions can be controlled from cPanel.

Log into cPanel and select Remote MySQL (1)

Managing Access Hosts

Managing Access Hosts

A new screen is opened that provides control over who is to have access to the On-Rev database.

By default the access host is set to %. This wild card indicates that there are no restrictions on the location from where the database can be accessed. In order to restrict the access to certain IP addresses, it is first necessary to remove the wild card. This can be removed by selecting the X under Remove (1). This raises a prompt, asking if we are sure about this action. Select Yes to remove the wild card and return us to the above screen again.

After these steps have been completed, it is possible to add specific IP addresses that are allowed to access the database. Enter the domain or IP address in the host field (2) and select Add Host (3). It is possible to add an arbitrary number of hosts to the list.

0 Comments

Add your comment

E-Mail me when someone replies to this comment