-
Notifications
You must be signed in to change notification settings - Fork 1
05. Configuration
In addition to the Jaxon library config file, which provides options for Ajax requests, cache, UI templates and views, assets, dialogs, file storage and upload, Jaxon DbAdmin requires its own config files to be provided in specific subdir.
For the ready-to-use applications, it is located at config/dbadmin.
The config options are separated into three files, app.php, servers.phpandforeigns.php`.
This config file defines the main options for the database admin and audit logs features.
This is the default provided in the Slim application.
use App\Facade\RouteParser;
use App\Facade\Session;
use Lagdo\DbAdmin\Support\Facade\Auth;
use Lagdo\DbAdmin\Support\Provider;
use Lagdo\DbAdmin\Support\Service;
use Pharaonic\Slugify\Facades\Slugify;
return [
'admin' => [
'ui' => [
'toast' => [
'lib' => 'butterup',
],
'query' => [
// 'cm' for CodeMirror or 'ace' for Ace Editor.
'editor' => 'cm',
],
],
'queries' => [
'save' => [
'editor' => false,
'builder' => false,
'library' => false,
],
'enable' => [
'preferences' => false,
'history' => false,
'favorite' => false,
],
'history' => [
'distinct' => false,
'limit' => 10,
],
'favorite' => [
'limit' => 10,
],
],
],
'audit' => [
'enabled' => false,
'users' => [
// The emails of users that are allowed to access the audit page.
],
'queries' => [
'database' => [
// Same as the "servers" items, but "name" is the database name.
],
'pagination' => [
'limit' => 10,
],
],
],
// 'auth' => null, // No auth.
'auth' => fn() => new class implements Provider\AuthInterface {
public function userId(): string
{
return Session::get('user')?->email ?? '';
}
public function name(): string
{
return Session::get('user')?->name ?? '';
}
public function roles(): array
{
return [];
}
public function audit(): string
{
return RouteParser::urlFor('dbaudit_page');
}
public function logout(): string
{
return RouteParser::urlFor('auth_logout');
}
},
// 'export' => null, // No export.
'export' => fn() => new class extends Service\Export\AbstractFileSystem {
protected function storage(): string
{
return 'exports';
}
protected function path(string $filename): string
{
// Use the slugified username to customize the path.
$userId = Auth::userId();
$userDir = $userId === '' ? 'users' : 'users/' . Slugify::get($userId);
return "$userDir/$filename";
}
protected function url(string $filename): string
{
return RouteParser::urlFor('export', ['filename' => $filename]);;
}
},
// Comment all to use the default secret config provider, which reads secret from the .env.dbadmin file.
'secret' => [
],
];The admin entry provides options to setup the UI, and decides which queries are saved in the audit logs.
The audit entry provides options to enable the audit logs feature, define the audit database credentials, and define which users have access to the audit logs page.
The auth and export entries provide options respectively for the authentication and database export features.
They define a closure which returns an instance of an anonymous class implementing the required interface.
The secret entry optionally defines the secret management server in use and the corresponding key builder.
This config file defines options for the managed database servers, including their credentials.
This is an example of a servers.php config file.
[
'common' => [
'access' => [
'server' => false,
'system' => false
],
'servers' => [
'db-postgresql' => [
'driver' => 'pgsql',
'name' => 'PostgreSQL 14',
'host' => env('DBA_PGSQL_HOST'),
'port' => env('DBA_PGSQL_PORT')
],
'db-mariadb' => [
'driver' => 'mysql',
'name' => 'MariaDB 10',
'host' => env('DBA_MARIA_HOST'),
'port' => env('DBA_MARIA_PORT')
],
'db-mysql' => [
'driver' => 'mysql',
'name' => 'MySQL 8',
'host' => env('DBA_MYSQL_HOST'),
'port' => env('DBA_MYSQL_PORT')
]
]
],
'fallback' => [],
'users' => [[
'id' => [
'users' => [
'user1@company.com',
'user2@company.com'
]
],
'servers' => [
'db-postgresql' => [
'username' => env('DBA_PGSQL_USERNAME'),
'password' => env('DBA_PGSQL_PASSWORD')
],
'db-mysql' => [
'username' => env('DBA_MYSQL_USERNAME'),
'password' => env('DBA_MYSQL_PASSWORD')
],
'laravel' => [
'driver' => 'sqlite',
'name' => 'Laravel',
'directory' => env('SQLITE_LARAVEL_DIR')
]
]
], [
'id' => [
'user' => 'admin@company.com'
],
'access' => [
'server' => true,
'system' => true
],
'servers' => [
'db-mariadb' => [
'username' => env('DBA_MARIA_USERNAME'),
'password' => env('DBA_MARIA_PASSWORD')
],
'db-mysql' => [
'username' => env('DBA_MYSQL_USERNAME'),
'password' => env('DBA_MYSQL_PASSWORD')
],
'sqlite-3' => [
'driver' => 'sqlite',
'name' => 'Sqlite 3',
'directory' => env('SQLITE_DEFAULT_DIR')
]
]
]]
]The Jaxon DbAdmin config file can contain 3 sections, all of which are optional.
This section contains options that are shared for all users. The options in this section will be merged with the user options found. Which also means that if no entry is found for the user, these options are not returned.
The options in this section will be returned if no specific entry exists for the authenticated user in the users section.
These options will be merged with the common options.
This section must contain an array of options, each for a given user or group of users.
Each entry in the array must have an attribute with id key, which itself is an object with 4 possible attributes to identify the corresponding users:
-
user: a single user email. -
users: an array of user emails. -
role: a single user role. -
roles: an array of user roles.
The other attributes are the database options, described in the following paragraph.
If any entry is found here for the current user, its value will be merged with the common options.
The common, fallback and each entry in users array contain the same options, excepted the id option in the servers array items.
The servers option lists the database servers to be managed.
For each entry, the key is the unique identifier used in requests to the Jaxon DbAdmin application.
The driver option indicate the corresponding DBMS: pgsql for PostgreSQL, mysql for MySQL or MariaDB, and sqlite for SQLite.
The name option is the name to be displayed in the Jaxon DbAdmin UI.
The other options depend on the DBMS.
For SQLite, the directory option is a directory where to look for database files.
Each file in the directory with the db, sdb or sqlite extension is listed as a database.
For the other DBMS, the host, port, username and password options will be used to connect to the database server. Only the port option optional.
Except for driver and name, the values for all the other options can be loaded from env vars.
In this case, the option need to be set in a specific format like env(DBA_PGSQL_HOST), where the value in the parenthesis is the env var name.
In addition to the default .env, the application also loads the .env.dbadmin file, which can be used to define the Jaxon DbAdmin specific env vars.
After the merge with the options in the common section, the entries in the servers options are filtered on valid values.
As a consequence, only the entries for which all the required options (except port) are provided will be returned in the final list.
The default option defines a server the application will connect to when the web page is loaded or refreshed.
The access option is an object that contains multiple options to define to which databases and to which part of the application the user will have access.
The access option can be defined at top level, in this case it applies to all the database servers, or it can be defined in a specific server options, to be applied only to that server.
In the access object, the system option defines if the user has access to system databases and schemas. If set to false, which is the default, the system databases will not be listed in the user account.
The server option defines if the user has access to server specific pages. If set to false, which is the default, the user will not have access to the Databases, Process list and Variables pages, as well as the server-related Query, Import and Export pages.
The corresponding menu entries will not be displayed in the sidebar menu.
The databases and schemas options restrict the user access to the listed databases and schemas.
This config file defines options for displaying fields from foreign tables in lieu of the foreign key values when listing the entries in a database table.
This is an example of a foreigns.php config file, defined for foreign keys in the sample pagila and sakila databases.
// The SQL SELECT clauses to get labels for foreign key columns.
return [
'dbadmin-pgsql-14' => [
'pagila' => [
'public' => [
'actor' => [
'actor_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
],
],
'store' => [
'store_id' => [
'select' => fn(int $textLength) => "SUBSTR(address.address, 1, $textLength)",
'search' => fn(string $search) => "address.address ILIKE $search",
'joins' => ["INNER JOIN address ON store.address_id=address.address_id"],
],
],
'customer' => [
'customer_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
],
],
'staff' => [
'staff_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
],
],
],
],
'sakila' => [
'public' => [
'actor' => [
'actor_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
],
],
'store' => [
'store_id' => [
'select' => fn(int $textLength) => "SUBSTR(address.address, 1, $textLength)",
'search' => fn(string $search) => "address.address ILIKE $search",
'joins' => ["INNER JOIN address ON store.address_id=address.address_id"],
],
],
'customer' => [
'customer_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
],
],
'staff' => [
'staff_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
],
],
],
],
],
'dbadmin-mariadb' => [
'sakila' => [
'actor' => [
'actor_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
'store' => [
'store_id' => [
'select' => fn(int $textLength) => "SUBSTR(address.address, 1, $textLength)",
'search' => fn(string $search) => "LOWER(address.address) LIKE $search",
'joins' => ["INNER JOIN address ON store.address_id=address.address_id"],
],
],
'customer' => [
'customer_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
'staff' => [
'staff_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
],
'employees' => [
'employees' => [
'emp_no' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
],
],
'dbadmin-mysql' => [
'sakila' => [
'actor' => [
'actor_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
'store' => [
'store_id' => [
'select' => fn(int $textLength) => "SUBSTR(address.address, 1, $textLength)",
'search' => fn(string $search) => "LOWER(address.address) LIKE $search",
'joins' => ["INNER JOIN address ON store.address_id=address.address_id"],
],
],
'customer' => [
'customer_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
'staff' => [
'staff_id' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
],
'employees' => [
'employees' => [
'emp_no' => [
'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
],
],
],
],
];