Using a database in Overcast (part 1)

In this new article in the series dedicated to Overcast, the cloud solution for SmartFoxServer 2X, we will describe how to use a database server alongside SmartFoxServer.

In this first part of the article we will focus on launching the database, populating it with some test data and configuring SmartFoxServer to connect to it. In the second and third parts we will deploy an Extension in SmartFoxServer to test the connection with a query.

Before proceeding, if you are new to Overcast (and possibly to SmartFoxServer too), we strongly recommend that you check the previous articles in this series:

In particular the second article, Launching SmartFoxServer in the cloud, sets the stage to follow this article in its entirety, as we will show how to configure a SmartFoxServer instance to later establish a connection from your server side Extension.

Creating the database server

In Overcast you can launch a MySQL instance as a dedicated data storage solution for your game/application, directly in the cloud, from a convenient web interface and without having to manually set up the hosting and server yourself.

A database instance is only accessible by the SmartFoxServer instances belonging to the same application and region. A PhpMyAdmin interface is also available to administer your database, as described in part 2 of this article.

Access your Overcast account and select the application you want to work with by clicking its name in the Overcast opening view. You can then proceed by creating a new server via the “+” icon on the right side.
If you followed the previous article in this series describing how to launch SmartFoxServer in Overcast, you should already have an application available and a running SmartFoxServer instance.

Step 1: choose the target Region and MySQL server type.

Step 2: assign a server name and choose the size of the hardware that will run the server.

Step 3: choose the MySQL version you want to install and assign an Admin password.

Step 4: choose a billing plan.

Finally double check the summary and confirm the launch of the instance. After the end of the procedure, which can take some minutes to complete, your database server will be up and running.

Managing and populating the database

With the database server up and running, you can now access its administration interface and populate it with some test data which will be later read (or written) by the SmartFoxServer server-side Extension (see part 2).

By clicking the toolbox icon of the MySQL instance you can launch the a PhpMyAdmin web interface to administer the database. Login with username “root” and the password you specified in step 3 above.

You can now create your actual database by clicking New in the navigation panel on the left. Give it the name MyGameDB and click the Create button.

You can now add a table. In this example a very basic table containing users and their highscores at your game should do. Click on the database name in the navigation panel and enter the table name in the main view with two columns. You can simply call the table highscores and create columns username and score. The username column may have a primary index assigned, assuming only the highest user score will be saved at any time.

The following SQL will create the highscores table and populate it with some test data.

CREATE TABLE `highscores` (
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `score` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `highscores`
  ADD PRIMARY KEY (`username`);
COMMIT;

INSERT INTO `highscores` (`username`, `score`) VALUES
('animal', 17),
('bunsen', 661),
('floyd', 542),
('fozzie', 366),
('gonzo', 1973),
('kermit', 231),
('pepe', 941),
('piggy', 567),
('rizzo', 106),
('rowlf', 892),
('sam', 885),
('scooter', 340);

Connecting SFS2X to the database

Once you have created your database, you can connect one or more SmartFoxServer instances to it by configuring the details in your Zone, via the AdminTool.
If you don’t have a SmartFoxServer instance running, you can launch it now. Follow the same steps above (but selecting the server type “SmartFoxServer”) or check this article.

Via the toolbox icon of the SmartFoxServer instance you can open the server’s AdminTool and login with username “sfsadmin” and the password entered at the time of server creation.

Access your game Zone (or create one if you haven’t already done it — you can call it MyGameZone) using the AdminTool’s Zone Configurator module. Then click on the Database Manager tab in the main view to edit the connection details.

Activate the Database Manager using the switch at the top, then enter the following connection settings:

  • Database driver class: org.mariadb.jdbc.Driver
  • Connection string: jdbc:mariadb://10.0.5.215:3306/MyGameDB
  • Username: root
  • Password: 1234abCD$
  • Test SQL: SELECT MAX(score) FROM highscores
  • Exhausted pool action: GROW

A few notes on the above settings:

  • The MariaDB JDBC driver is preinstalled in your SmartFoxServer instance and it’s an alternative JDBC driver to the official MySQL connector which we adopted as it comes with an LGPL license. More information on this driver and how to change it is available in the Overcast documentation.
  • The connection string requires the private IP address of your database server and the name of the database you created using PhpMyAdmin (MyGameDB in this example). The private IP address can be found in the server’s detailed view in the Overcast web interface:
  • The password is the one entered during the database server launch procedure.
  • The test SQL must be a valid SQL statement which SmartFoxServer uses to check the connection state. In this example we retrieve the top score in the highscores table. This could be any SQL statement; even a simple SELECT 1 would be enough.
  • The exhausted pool action sets the policy SmartFoxServer should apply when no more connection slots are available.

After filling in the connection details, click on the Submit button and restart SmartFoxServer by clicking the designated button in the AdminTool interface.

In order to make sure the connection can be established successfully, after the restart enter the AdminTool again and open the Log Viewer module. In the Runtime Log tab click the the Load button to retrieve the last log entries, then locate the section of the log where the Zone initialization is displayed (MyGameZone in our example), before the big SFS2X READY message. If a connection issue exists, an error will be reported here.

In the example below we entered a wrong database name in the connection string (MygameDB instead of MyGameDB), and an error was logged during the startup process:

What’s next?

In part 2 of this article we will complete the overview on deploying and using a database in Overcast by showing how to access the database from a SmartFoxServer server-side Extension. (Go to part 2)

In part 3 we will take look at more advanced ways of working with databases using the standard Java JDBC API and provide some tips on local and remote deployment and testing.