Monday, October 21, 2024

Web services with MariaDB

Create a directory for your project, it'll be where this example takes place. Also create Gliimly application "stock":
mkdir -p stock-app
cd stock-app
sudo mgrg -i -u $(whoami) stock

Start MariaDB command line interface:
sudo mysql

Create an application user, database and a stock table (with stock name and price):
create user stock_user;
create database stock_db;
grant all privileges on stock_db.* to stock_user@localhost identified by 'stock_pwd';
use stock_db
create table if not exists stock (stock_name varchar(100) primary key, stock_price bigint);

Gliimly wants you to describe the database: the user name and password, database name, and the rest is the default setup for MariaDB database connection. So create a file "db_stock" (which is your database configuration file, one per each you use):
vi db_stock

and copy and paste this:
[client]
user=stock_user
password=stock_pwd
database=stock_db
protocol=TCP
host=127.0.0.1
port=3306

Now to the code. Here's the web service to insert stock name and price into the stock table - create file "add-stock.gliim":
vi add-stock.gliim

and copy and paste this:
 %% /add-stock public
     @<html>
         @<body>
         get-param name
         get-param price
         // Add data to stock table, update if the stock exists
         run-query @db_stock = "insert into stock (stock_name, stock_price) values ('%s', '%s') on duplicate key update stock_price='%s'" \
             input name, price, price error err no-loop
         if-true err not-equal "0"
             report-error "Cannot update stock price, error [%s]", err
         end-if
         @<div>
             @Stock price updated!
         @</div>
         @</body>
     @</html>
 %%

Next is the web service to display a web page with all stock names and prices from the stock table - create file "show-stock.gliim":
vi show-stock.gliim

and copy and paste this:
 %% /show-stock public
     @<html>
         @<body>
             @<table>
                 @<tr>
                     @<td>Stock name</td>
                     @<td>Stock price</td>
                 @</tr>
                 run-query @db_stock = "select stock_name, stock_price from stock" output stock_name, stock_price
                     @<tr>
                         @<td>
                         p-out stock_name
                         @</td>
                         @<td>
                         p-out  stock_price
                         @</td>
                     @</tr>
                 end-query
             @</table>
         @</body>
     @</html>
 %%

You're done! Now it's time to make your application. You need to tell Gliimly that your database configuration file "db_stock" is MariaDB (because you could use PostgreSQL or SQLite for instance):
gg -q --db="mariadb:db_stock"

Test your web service. Here you'd run it as a command line program. That's neat because you can test your web services without even using a web server or a browser:
gg -r --req="/add-stock/name=ABC/price=882" --exec
gg -r --req="/add-stock/name=XYZ/price=112" --exec

The result for each:
Content-Type: text/html;charset=utf-8
Cache-Control: max-age=0, no-cache
Pragma: no-cache
Status: 200 OK

<html>
<body>
<div>
Stock price updated!
</div>
</body>
</html>

And to test the list of stocks:
gg -r --req="/show-stock" --exe

The result:
Content-Type: text/html;charset=utf-8
Cache-Control: max-age=0, no-cache
Pragma: no-cache
Status: 200 OK

<html>
<body>
<table>
<tr>
<td>Stock name</td>
<td>Stock price</td>
</tr>
<tr>
<td>
ABC</td>
<td>
882</td>
</tr>
<tr>
<td>
XYZ</td>
<td>
112</td>
</tr>
</table>
</body>
</html>

You can see the actual response, the way it would be sent to a browser, or an API web client, or any other kind of web client.