Database

Since most plugins will install webman-admin, it is recommended to directly reuse the database configuration of webman-admin.

The model base class uses plugin\admin\app\model\Base which will automatically use the database configuration of webman-admin.

<?php

namespace plugin\foo\app\model;

use plugin\admin\app\model\Base;

class Orders extends Base
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'foo_orders';

    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'id';

}

You can also operate on webman-admin's database through plugin.admin.mysql, for example

Db::connection('plugin.admin.mysql')->table('user')->first();

Using Your Own Database

Plugins can choose to use their own databases, for example, the content of plugin/foo/config/database.php is as follows:

return  [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [ // mysql is the connection name
            'driver'      => 'mysql',
            'host'        => '127.0.0.1',
            'port'        => 3306,
            'database'    => 'database',
            'username'    => 'username',
            'password'    => 'password',
            'charset'     => 'utf8mb4',
            'collation'   => 'utf8mb4_general_ci',
        ],
        'admin' => [ // admin is the connection name
            'driver'      => 'mysql',
            'host'        => '127.0.0.1',
            'port'        => 3306,
            'database'    => 'database',
            'username'    => 'username',
            'password'    => 'password',
            'charset'     => 'utf8mb4',
            'collation'   => 'utf8mb4_general_ci',
        ],
    ],
];

The reference method is Db::connection('plugin.{plugin}.{connection_name}');, for example:

use support\Db;
Db::connection('plugin.foo.mysql')->table('user')->first();
Db::connection('plugin.foo.admin')->table('admin')->first();

If you want to use the main project's database, just use it directly, for example:

use support\Db;
Db::table('user')->first();
// Assuming the main project has also configured an admin connection
Db::connection('admin')->table('admin')->first();

Configuring Database for Model

We can create a Base class for the Model, and the Base class specifies the plugin's own database connection with $connection, for example:

<?php

namespace plugin\foo\app\model;

use DateTimeInterface;
use support\Model;

class Base extends Model
{
    /**
     * @var string
     */
    protected $connection = 'plugin.foo.mysql';

}

This way, all Models in the plugin that inherit from Base will automatically use the plugin's own database.

Automatic Database Import

Running php webman app-plugin:create foo will automatically create the foo plugin, which includes plugin/foo/api/Install.php and plugin/foo/install.sql.

Tip
If the install.sql file is not generated, please try upgrading webman/console, the command is composer require webman/console ^1.3.6

Import Database When Installing Plugin

When installing the plugin, the install method in Install.php will be executed, which will automatically import the SQL statements in install.sql, thus achieving the purpose of automatically importing the database tables.

The content of install.sql is to create database tables and the SQL statements for historical modifications to the tables. Note that each statement must end with ;, for example:

CREATE TABLE `foo_orders` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `order_id` varchar(50) NOT NULL COMMENT 'Order ID',
  `user_id` int NOT NULL COMMENT 'User ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT 'Amount to be paid',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='Orders';

CREATE TABLE `foo_goods` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `name` varchar(50) NOT NULL COMMENT 'Name',
  `price` int NOT NULL COMMENT 'Price',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='Goods';

Changing Database Connection

The target database for the import from install.sql defaults to the webman-admin database. If you want to import to another database, you can modify the $connection attribute in Install.php, for example:

<?php

class Install
{
    // Specify the plugin's own database
    protected static $connection = 'plugin.admin.mysql';

    // ...
}

Testing

Execute php webman app-plugin:install foo to install the plugin, and then check the database. You will find that the foo_orders and foo_goods tables have been created.

Changing Table Structure During Plugin Upgrade

Sometimes a plugin upgrade requires creating new tables or changing table structures, and you can directly append the corresponding statements after install.sql. Note that each statement must also end with ;, for example, to add a foo_user table and to add a status field to the foo_orders table:

CREATE TABLE `foo_orders` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `order_id` varchar(50) NOT NULL COMMENT 'Order ID',
  `user_id` int NOT NULL COMMENT 'User ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT 'Amount to be paid',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='Orders';

CREATE TABLE `foo_goods` (
 `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
 `name` varchar(50) NOT NULL COMMENT 'Name',
 `price` int NOT NULL COMMENT 'Price',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='Goods';

CREATE TABLE `foo_user` (
 `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
 `name` varchar(50) NOT NULL COMMENT 'Name'
 PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='Users';

ALTER TABLE `foo_orders` ADD `status` tinyint NOT NULL DEFAULT 0 COMMENT 'Status';

During the upgrade, the update method in Install.php will be executed, which will execute the statements in install.sql. If there are new statements, they will be executed, and if the statements are old, they will be skipped, thereby achieving modifications to the database during the upgrade.

Deleting Database When Uninstalling Plugin

When uninstalling the plugin, the uninstall method in Install.php will be called. It will automatically analyze which table creation statements are in install.sql and will automatically delete those tables, achieving the purpose of deleting database tables when the plugin is uninstalled.
If you want to execute only your own uninstall.sql during uninstallation and not perform the automatic delete operation, just create plugin/{plugin_name}/uninstall.sql. In this way, the uninstall method will only execute the statements in the uninstall.sql file.