Database management

From ISPWiki
Jump to: navigation, search

Introduction

COREmanager delivers a number of classes that can be used to work with different databases Group_mgr_db. The classes allow to perform calls and create/edit a database structure. This article will help you better understand the database management process.

Connecting to the database

Сonnect to a database. A database type doesn't matter.

#include <mgr/mgrdb_struct.h>
mgr_db::Cache *db_cache;

MODULE_INIT(db, "") {
  mgr_db::ConnectionParams params;
  ...
  db_cache = new mgr_db::Cache(params);
}

Provide required parameters to connect, and create a database.

SqLite

The easiest way is to use the built-in SqLite base. Enter a path to the file where your data will be kept:

  params.type = "sqlite";
  params.db = "etc/mydb.db";

MySQL

There are some differences in the code:

  params.type = "mysql";
  params.db = "my_database";
  params.user = "my_user";
  params.password = "my_password";
  params.client = "my_panel";

Server configuration - we use the ODBC to work with MySQL, PostgreSQL, etc. Thus, you need to create a database and a database user with permissions to modify the database structure, and configure the ODBC driver.

For example, if you run Debian, install the libmyodbc packet.

Add the following strings into the /etc/odbc.ini file:

[MySQL]
Description = MySQL
Driver = MySQL
Server = localhost
Database =
Port =
Socket = /var/run/mysqld/mysqld.sock
Option =
Stmt =

Into the /etc/odbcinst.ini file:

[MySQL]
Description = MySQL ODBC driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount = 1
CPTimeout = 0
CPTimeToLive = 0
DisableGetFunctions = 0
DontDLCLose = 1
ExFetchMapping = 1
Threading = 0
FakeUnicode = 0
IconvEncoding =
Trace =
TraceFile =
TraceLibrary =

Pats that you need to specify in the Socket, Driver and Setup parameters may vary depending on the distribution.

Creating a table

The following example shows how to create the "users" table with three fields: id, name, password:

#include <mgr/mgrdb_struct.h>
extern mgr_db::Cache *db_cache;
...
class Users : public mgr_db::Table {
public:
  mgr_db::StringField Password;
  Users(): mgr_db::Table("users", 32), Password(this, "password", 128) {}
};
...
MODULE_INIT(users, "db") {
  db_cache->Register<Users>();
}

class Users - this class describes a structure of the table we want to create. Its fields are described by properties of the class - objects of the Class_mgr_db::Field inheritance class. Make sure that the class contains a constructor without parameters.

Calling a method of the Class_mgr_db::Cache class Register will create a table in the database, or modify its structure, if it already exists, but doesn't correspond to its description.

You may ask, why the users table contains only 3 fields whereas we have described only one. The Class_mgr_db::Table class is a descendant of Class_mgr_db::CustomTable already contains two fields: the primary key - id, and the unique field - name. If you don't need these fields, you can inherit from Class_mgr_db::CustomTable or Class_mgr_db::IdTable.

Creating a list

Now we can use the users table in our control panel.

MODULE_INIT(action_user, "users") {
  new iso_api::TableIdListAction<Users>("user", isp_api::AccessAdmin, *cache);
}

We have a number of functions to work with the users table:

  • user - display the list of records
  • user.edit - create/edit records
  • user.delete - delete records

The key is the record's id. If you want to allow your client to use native keys, you can re-write the code as follows:

MODULE_INIT(action_user, "users") {
  new iso_api::TableNameListAction<Users>("user", isp_api::AccessAdmin, *cache);
}

In the example above, the key is a username. If you want to use more advanced logic, you will need to write your own inheritance class, for example Class_isp_api::TableNameListAction.