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
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.
Create a Database
Create a User
Add a User to the Database
Assign the User to the Database
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
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
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.