repository·main·Indexed 43 days ago
https://github.com/vcian/laravel-db-auditorA database auditing tool for MySQL, SQLite, and PostgreSQL. It provides Artisan commands and a Web UI to scan for standards compliance, audit and manage constraints (primary, foreign, unique, index), track database file history, and generate performance reports. Requires PHP 8.0+ and Laravel 8.0+.
You can access the auditing features through a web interface. To publish the necessary assets and configuration for the Web UI, run the following command and select db-auditor when prompted:
php artisan vendor:publish --tag=publicOnce published, the Web UI is accessible at the following route:
/laravel-db-auditorTo use Laravel DB Auditor, require the package via Composer. This package requires PHP 8.0+ and Laravel 8.0+.
composer require --dev vcian/laravel-db-auditorTo customize the behavior of the auditor, you can publish the configuration file to your application's config directory. This allows you to manage settings for both the general auditor and the specific audit logic.
php artisan vendor:publish --tag=configIf you need to access the package's icons or images in your public directory, you can publish them using the public tag.
php artisan vendor:publish --tag=publicdb:constraint command from suggesting certain tables, you can add them to the audit.skip_tables configuration key. The command uses this list to filter the available tables during the interactive selection process.The db:track command provides a history of database files, including when they were created, the number of fields per table, and the creator (returns the GitHub username if in a Git repo, otherwise the system username).
Requirements:
You must set the DB_DATABASE parameter in your Laravel .env file for this feature to work.
Filters: You can filter the tracking results using the following flags:
--table=--action=--status=php artisan db:trackUse the db:standard command to generate a report of tables and columns that do not follow established database standards. The output provides insights and suggestions for improvement.
php artisan db:standardThe main entry point for the auditor is the db:audit command. This command provides an interactive menu to select features such as checking database standards or checking constraints.
To skip specific tables during the audit, add the table names to the .db-auditor.php configuration file.
php artisan db:auditUse the db:constraint command to view a list of tables along with their primary, foreign, unique, and index constraints. This command also allows you to add new constraints to existing tables via the CLI.
Limitations:
PRIMARY KEY or FOREIGN KEY on an existing table.php artisan db:constraintTo update a constraint on a specific column, send a POST request to the changeConstraint endpoint. The request must include the table name, column name, and the type of constraint to apply.
Required Request Parameters:
table_name: The name of the table.colum_name: The name of the column.constraint: The type of constraint (e.g., using values from Vcian\LaravelDBAuditor\Constants\Constant).Returns Constant::STATUS_TRUE on success.
// Example request payload structure
[
'table_name' => 'users',
'colum_name' => 'email',
'constraint' => 'unique' // or Constant::CONSTRAINT_UNIQUE_KEY
]The DisplayController provides several endpoints used by the web interface to audit database tables, view constraints, and modify schema rules. These endpoints return JSON responses containing table lists, field details, and constraint statuses.
| Method | Endpoint Path (Inferred) | Description |
|---|---|---|
GET | / (index) | Returns the main audit view page. |
GET | /get-audit | Returns a list of all tables with their audit status and size. |
GET | /{tableName}/get-table-data | Returns audit rules for a specific table. |
GET | /{tableName}/get-table-constraint | Returns detailed field information including current primary, unique, foreign, and index constraints. |
POST | /change-constraint | Updates a field's constraint (e.g., adding an index or primary key). |
POST | /add-foreign-key-constraint | Specifically handles the addition of foreign key constraints between tables. |
The DatabaseConstraintClass is used to retrieve information about a specific table's database constraints, including primary keys, unique keys, foreign keys, and indexes. It automatically detects the database driver (MySQL, PostgreSQL, or SQLite) and executes the appropriate queries.
To use it, instantiate the class with the target table name. You can then invoke the instance to get an associative array containing the constraint data.
Returns an array with the following keys:
primary: Primary key information.unique: Unique key information.foreign: Foreign key information.index: Index information.Returns an array with the following keys:
primary: Primary key information (fields where pk is 1).foreign: Foreign key information (fields from, to, and table).index: Index information (field name).use Vcian//
LaravelDBAuditor//
Queries
DatabaseConstraintClass;
// Analyze constraints for the 'users' table
$constraints = new DatabaseConstraintClass('users');
$data = $constraints();
print_r($data);