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 a LiveCode Hosting 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 cPanel
The first step is to log in to your hosting 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 hosting account, you will find a "Hosting" tab in your LiveCode account down the lefthand navigation, which contains a login link and instructions. If you do not see this tab, then you do not have an account with us, this can be purchased here:
Once logged into cPanel it is possible to create a MySQL database. Scroll down to the Databases panel and select MySQL Databases (1) as shown above.
Create a Database
A new screen opens that prompts for the name of a new database. After specifying the name, select Create Database (1).
Create a User
The next screen prompts you to create a user for your new database. 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
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
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.
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", "yourusername.livecodehosting.com", "yourusername_mydatabase", "yourusername_myuser", "yourpasswordhere") into tDatabaseID
The first parameter specifies the database type. LiveCode Hosting 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.