The EmployeeModel class

A data-entry form in Vibe.d needs a struct data type from which it can extract the fields for saving into the database table. It makes things simpler if the database table, the struct and the form have a one-to-one correspondence in field names.

We will create a struct data structure that mimics an employee record. We will call it Employee.

Then we create a class that contains the methods to manipulate that struct, such as add, edit, get and delete, in short, all the CRUD operations on the MySQL server table. It is a model that represents the employees table on the MySQL server, so we will call that class EmployeeModel.

We are going to presume that this app will only be deployed in an intranet setting and only a few people have access, and that there is a rare chance of more than one person using the app at the same time, so we won’t have to deal with security, HTTPS encryption, multiple simultaneous users, etc.

Let us create the EmployeeModel class inside the empmodel module. By convention, modules and filenames have the same name for convenience. Create source\empmodel.d with this contents:

module empmodel;
import mysql;
import std.conv;
import std.array;

struct Employee
{
  int id; //row id or record id
  string empid; //employee number
  string deprt; //department
  string paygd; //salary grade
  string email; //email address
  string pword; //password
  string fname; //first name
  string lname; //last name
  string phone; //phone number
  string photo; //ID photo
  string street; //street address
  string city; //city name
  string province; //province name
  string postcode; //postal code
}

struct Admin
{
  string email; //email address
  string pword; //passsword
}

string[] departments =
[
  "Management and Admin",
  "Accounting and Finance",
  "Production",
  "Maintenance",
  "Shipping and Receiving",
  "Purchasing and Supplies",
  "IT Services",
  "Human Resources",
  "Marketing"
];

string[][] provinces =
[
  ["AB", "Alberta"],
  ["BC", "British Columbia"],
  ["MB", "Manitoba"],
  ["NB", "New Brunswick"],
  ["NL", "Newfoundland and Labrador"],
  ["NS", "Nova Scotia"],
  ["NT", "Northwest Territories"],
  ["NU", "Nunavut"],
  ["ON", "Ontario"],
  ["PE", "Prince Edward Island"],
  ["QC", "Quebec"],
  ["SK", "Saskatchewan"],
  ["YT", "Yukon Territory"]
];

string[] paygrades =
[
  "A100", "A200", "A300", "A400",
  "B100", "B200", "B300", "B400",
  "C100", "C200", "C300", "C400",
  "D100", "D200", "D300", "D400",
  "E100", "E200", "E300", "E400",
  "F100", "F200", "F300", "F400"
];

class EmployeeModel
{
  Connection conn;
  
  this()
  {
    string url = "host=localhost;port=3306;user=owner;pwd=qwerty;db=empdb";
    conn = new Connection(url);
    scope(exit) conn.close;
    insertIntoAdmins(); // be sure to remove this after the first run!
  }
  
  void insertIntoAdmins()
  {
    import vibe.http.auth.digest_auth;
    string email1 = "[e-mail]";
    string email2 = "[e-mail]";
    string email3 = "[e-mail]";
    string realm = "The Lorem Ipsum Company";
    string pass1 = createDigestPassword(realm, email1, "secret");
    string pass2 = createDigestPassword(realm, email2, "secret");
    string pass3 = createDigestPassword(realm, email3, "secret");
    string sql = "insert into admins(email, pword)
values ('" ~ email1 ~ "','" ~ pass1 ~ "')";
    conn.exec(sql);
    sql = "insert into admins(email, pword)
values ('" ~ email2 ~ "','" ~ pass2 ~ "')";
    conn.exec(sql);
    sql = "insert into admins(email, pword)
values ('" ~ email3 ~ "','" ~ pass3 ~ "')";
    conn.exec(sql);
  }
  
  ulong addEmployee(Employee e)
  {
    string sql =
      "insert into employees
      (
        empid,
        deprt, paygd, email, pword,
        fname, lname, phone, photo,
        street, city, province, postcode
      )
      values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs
    (
      to!string(e.empid),
      to!string(e.deprt),
      to!string(e.paygd),
      to!string(e.email),
      to!string(e.pword),
      to!string(e.fname),
      to!string(e.lname),
      to!string(e.phone),
      to!string(e.photo),
      to!string(e.street),
      to!string(e.city),
      to!string(e.province),
      to!string(e.postcode)
    );
    return conn.exec(pstmt);
  }
}

We created the departments, the provinces and the paygrades array variables for forms that require drop-down options for departments, provinces and salary grades.

Also, there is an insertIntoAdmins() method that adds three records to the admins table as our test administrators. Be sure to comment this out or erase after the first compilation and run.

In the this() constructor of the EmployeeModel class, we get a connection to the MySQL server running at localhost (127.0.0.1), meaning, our own local machine where we installed MySQL.

The connection string we use for connecting to the MySQL server with mysq-native is

string url = "host=localhost;port=3306;user=owner;pwd=qwerty;db=empdb";

Of course, replace the user and the password fields with you own username and password for MySQL.

After that, we can connect to the empdb database through the conn variable.

The line

scope(exit) conn.close;

means that, before the conn variable gets garbage-collected, its close() method should be called. The addEmployee() method shows how to insert a record in mysql-native syntax.

We need to import std.conv

import std.conv;

so we can use the conversion functions of the D standard library, such as

to!string(e.deprt),

which converts the data in e.deprt (which we will get from the web form, which we haven’t created yet) into a regular string before we save it to the database.

We need to add new records to the empty employees table, so we should create an employee data-entry form. Let’s do that next.