Laravel SQL Entities

https://github.com/calebdw/laravel-sql-entities
Admin
Manage SQL entities like views, functions, and triggers in Laravel using class-based definitions for...
Tokens:3,974
Snippets:15
Trust Score:9.3
License:MIT
Update:1 year ago
Show doc for...
Tokens:
Raw
### Project Setup and Testing Commands

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/CONTRIBUTING.md

Commands for setting up the project by installing dependencies and running various tests including linting, static analysis, and PHPUnit coverage.

```bash
composer install
```

```bash
composer test
```

```bash
composer test:lint
```

```bash
composer test:static
```

```bash
composer test:coverage
```

--------------------------------

### Install laravel-sql-entities via Composer

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

This command installs the laravel-sql-entities package using Composer, a dependency manager for PHP.

```Bash
composer require calebdw/laravel-sql-entities
```

--------------------------------

### Console Commands for SQL Entities

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Provides examples of using Artisan console commands to create and drop SQL entities. Covers creating all entities, specific entities, and filtering by connection.

```bash
php artisan sql-entities:create [entities] [--connection=CONNECTION ...]

# Create all entities
php artisan sql-entities:create
# Create a specific entity
php artisan sql-entities:create 'Database\Entities\Views\RecentOrdersView'
# Create all entities on a specific connection
php artisan sql-entities:create -c reporting

# Similarly, drop all entities
php artisan sql-entities:drop

```

--------------------------------

### SqlEntityManager Usage

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Demonstrates how to create and drop SQL entities using the SqlEntityManager and SqlEntity facade. Includes examples for single entities, all entities, and filtering by type or connection.

```php
<?php
use CalebDW\SqlEntities\Facades\SqlEntity;
use CalebDW\SqlEntities\SqlEntityManager;
use CalebDW\SqlEntities\View;

// Create a single entity by class or instance
SqlEntity::create(RecentOrdersView::class);
resolve(SqlEntityManager::class)->create(RecentOrdersView::class);
resolve('sql-entities')->create(new RecentOrdersView());

// Similarly, you can drop a single entity using the class or instance
SqlEntity::drop(RecentOrdersView::class);

// Create or drop all entities
SqlEntity::createAll();
SqlEntity::dropAll();

// You can also filter by type or connection
SqlEntity::createAll(types: View::class, connections: 'reporting');
SqlEntity::dropAll(types: View::class, connections: 'reporting');

```

--------------------------------

### Automatic Syncing with Migrations

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Demonstrates how to register the `SyncSqlEntities` listener in a service provider to automatically sync SQL entities with database migrations, ensuring entities are dropped before and recreated after migrations.

```php
<?php
use CalebDW\SqlEntities\Listeners\SyncSqlEntities;
use Illuminate\Support\Facades\Event;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        Event::subscribe(SyncSqlEntities::class);
    }
}

```

--------------------------------

### PHP View Configuration Options

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Illustrates how to configure database views using properties like `$recursive` for recursive views, `$checkOption` for `WITH CHECK OPTION` clauses, and `$columns` for explicit column listing.

```php
<?php

class RecentOrdersView extends View
{
    // to create a recursive view
    protected bool $recursive = true;
    // adds a `WITH CHECK OPTION` clause to the view
    protected string|true|null $checkOption = 'cascaded';
    // can provide explicit column listing
    protected ?array $columns = ['id', 'customer_id', 'status', 'created_at'];
}

```

--------------------------------

### PHP Entity Dependencies Declaration

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Shows how to declare dependencies between database entities using the `dependencies()` method. The package ensures entities are created in the correct order based on these declared dependencies.

```php
<?php

class RecentOrdersView extends View
{
    #[Override]
    public function dependencies(): array
    {
        return [OrdersView::class];
    }
}

```

--------------------------------

### Temporarily Drop Entities with withoutEntities()

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Shows how to use the `withoutEntities()` method to temporarily drop specified SQL entities, execute a callback function, and then recreate them. Supports schema transactions and filtering by type or connection.

```php
<?php
use CalebDW\SqlEntities\Facades\SqlEntity;
use Illuminate\Database\Connection;

SqlEntity::withoutEntities(function (Connection $connection) {
    $connection->getSchemaBuilder()->table('orders', function ($table) {
        $table->renameColumn('old_customer_id', 'customer_id');
    });
});

```

```php
<?php
use CalebDW\SqlEntities\Facades\SqlEntity;
use Illuminate\Database\Connection;

SqlEntity::withoutEntities(
    callback: function (Connection $connection) {
        $connection->getSchemaBuilder()->table('orders', function ($table) {
            $table->renameColumn('old_customer_id', 'customer_id');
        });
    },
    types: [RecentOrdersView::class, RecentHighValueOrdersView::class],
    connections: ['reporting'],
);

```

--------------------------------

### PHP Lifecycle Hooks for Database Entities

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Demonstrates how to implement lifecycle hooks (creating, created, dropping, dropped) for database entities. Returning false from 'creating' or 'dropping' can prevent the operation. The 'created' hook can execute SQL statements after creation.

```php
<?php
use Illuminate\Database\Connection;

class RecentOrdersView extends View
{
    // ...

    #[Override]
    public function creating(Connection $connection): bool
    {
        if (/** should not create */) {
            return false;
        }

        /** other logic */

        return true;
    }

    #[Override]
    public function created(Connection $connection): void
    {
        $this->connection->statement(<<<'SQL'
            GRANT SELECT ON TABLE {$this->name()} TO other_user;
            SQL);
    }

    #[Override]
    public function dropping(Connection $connection): bool
    {
        if (/** should not drop */) {
            return false;
        }

        /** other logic */

        return true;
    }

    #[Override]
    public function dropped(Connection $connection): void
    {
        /** logic */
    }
}

```

--------------------------------

### PHP Loadable SQL Function Definition

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Defines a loadable database function, specifying its arguments, return type, language (e.g., 'c'), and setting `$loadable` to true. The `definition()` method returns the name of the loadable function.

```php
<?php

namespace Database\Entities\Functions;

use CalebDW\SqlEntities\Function_;

class Add extends Function_
{
    protected array $arguments = [
        'integer',
        'integer',
    ];

    /** The language the function is written in. */
    protected string $language = 'c';

    protected bool $loadable = true;

    /** The function return type. */
    protected string $returns = 'integer';

    #[Override]
    public function definition(): string
    {
        return 'c_add';
    }
}

```

--------------------------------

### PHP Trigger Definition for Database Events

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Shows how to define a database trigger using the `Trigger` class. It includes properties for trigger timing, events (e.g., UPDATE), the target table, and the trigger's SQL definition. It also supports constraint triggers for PostgreSQL.

```php
<?php

namespace Database\Entities\Triggers;

use CalebDW\SqlEntities\Trigger;

class AccountAuditTrigger extends Trigger
{
    // if the trigger is a constraint trigger
    // PostgreSQL only
    protected bool $constraint = false;

    protected string $timing = 'AFTER';

    protected array $events = ['UPDATE'];

    protected string $table = 'accounts';

    #[Override]
    public function definition(): string
    {
        return $this->definition ?? <<<'SQL'
            EXECUTE FUNCTION record_account_audit();
            SQL;
    }
}

```

--------------------------------

### Configure Composer Autoload for Entities

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

This diff shows how to update the composer.json file to include a PSR-4 autoloader for the Database\Entities namespace, pointing to the database/entities/ directory.

```Diff
{
  "autoload": {
    "psr-4": {
      "App\": "app/",
+     "Database\Entities\": "database/entities/",
      "Database\Factories\": "database/factories/",
      "Database\Seeders\": "database/seeders/"
    }
  }
}
```

--------------------------------

### PHP SQL Function Definition

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

Defines a database function using the `Function_` class. It includes properties for arguments, return type, language, and the function's SQL definition. The class name is `Function_` to avoid PHP keyword conflicts.

```php
<?php

namespace Database\Entities\Functions;

use CalebDW\SqlEntities\Function_;

class Add extends Function_
{
    /** If the function aggregates. */
    protected bool $aggregate = false;

    protected array $arguments = [
        'integer',
        'integer',
    ];

    /** The language the function is written in. */
    protected string $language = 'SQL';

    /** The function return type. */
    protected string $returns = 'integer';

    #[Override]
    public function definition(): string
    {
        return <<<'SQL'
            RETURN $1 + $2;
            SQL;
    }
}

```

--------------------------------

### Define a SQL View in Laravel

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

This PHP code defines a SQL view named 'recent_orders_view' using the `View` entity class. It specifies the definition using a Laravel Query Builder instance or raw SQL.

```PHP
<?php

namespace Database\Entities\Views;

use App\Models\Order;
use CalebDW\SqlEntities\View;
use Illuminate\Database\Query\Builder;
use Override;

// will create a view named `recent_orders_view`
class RecentOrdersView extends View
{
    #[Override]
    public function definition(): Builder|string
    {
        return Order::query()
            ->select(['id', 'customer_id', 'status', 'created_at'])
            ->where('created_at', '>=', now()->subDays(30))
            ->toBase();

        // could also use raw SQL
        return <<<'SQL'
            SELECT id, customer_id, status, created_at
            FROM orders
            WHERE created_at >= NOW() - INTERVAL '30 days'
            SQL;
    }
}
```

--------------------------------

### Override View Name and Connection

Source: https://github.com/calebdw/laravel-sql-entities/blob/master/README.md

This PHP code demonstrates how to override the default name and database connection for a SQL entity definition. You can specify a custom name, including schema, or a different connection.

```PHP
<?php
class RecentOrdersView extends View
{
    protected ?string $name = 'other_name';
    // also supports schema
    protected ?string $name = 'other_schema.other_name';

    protected ?string $connection = 'other_connection';
}
```
Tokens:
Raw