NAME

DBIx::EAV::Schema - Describes the physical EAV database schema.

SYNOPSIS

    my $schema = DBIx:EAV::Schema->new(
        dbh          => $dbh,               # required
        tables       => \%tables            # required
        tenant_id    => $tenant_id,         # default undef
        table_prefix => 'my_eav_',          # default 'eav_'
    );

DESCRIPTION

This class represents the physical eav database schema. Will never need to instantiate an object of this class directly.

CONSTRUCTOR OPTIONS

data_types

Default: [qw/ int decimal varchar text datetime bool /]

Arrayref of SQL data types that will be available to entity attributes. DBIx::EAV uses one value table for each data type listed here. See "values" and "deploy".

static_attributes

Default: []

Arrayref of column definitions which will be available as static attributes for all entities. A column definition is a string in the form of "$col_name:$data_type:$data_size:$default_value" or a hashref suitable for "add_field" in SQL::Translator::Schema::Table.

Example defining a slug VARCHAR(255) and a is_deleted BOOL DEFAULT 0 attributes. Note that in the definition of is_deleted we wanted to specify the $default_value but not the $data_size field.

    static_attributes => [qw/ slug:varchar:255 is_deleted:bool::0 /]

table_prefix

Default: "eav_"

Prefix added to our tables names to form the real database table name. See "TABLES".

database_cascade_delete

Default: 1

When enabled, entities delete operations (via "delete" in DBIx::EAV::Entity or "delete" in DBIx::EAV::ResultSet) are accomplished through a single DELETE SQL command. Also instructs "deploy" to create the proper ON DELETE CASCADE constraints. See "CASCADE DELETE".

tenant_id

Default: undef

Setting this parameter enables the multi-tenancy feature.

id_type

Default: "int"

Data type used by "deploy" for the PRIMARY KEY ('id') and FOREIGN KEY ('*_id') columns.

TABLES

This section describes the tables used by DBIx::EAV.

entity_types

Columns: id, tenant_id?, name:varchar:255

Primary Key: id

Index: tenant_id?

Unique: name

attributes

Columns: id, entity_type_id, name:varchar:255, data_type:varchar:64

Primary Key: id

Foreign Key: entity_type_id -> "entity_types"

relationships

Columns: id, name:varchar:255, left_entity_type_id, right_entity_type_id, is_has_one:bool::0, is_has_many:bool::0, is_many_to_many:bool::0

Primary Key: id

Foreign Key: left_entity_type_id -> "entity_types"

Foreign Key: right_entity_type_id -> "entity_types"

Unique: left_entity_type_id, name

Stores the relationships definition between entity types. See "RELATIONSHIPS" in DBIx::EAV::Manual.

type_hierarchy

Columns: parent_type_id, child_type_id

Primary Key: parent_type_id, child_type_id

Foreign Key: parent_type_id -> "entity_types"

Foreign Key: child_type_id -> "entity_types"

Stores the type -> subtype relationship. See "TYPE INHERITANCE".

entities

Columns: id, entity_type_id, ("static_attributes")?

Primary Key: id

Foreign Key: entity_type_id -> "entity_types"

Stores the main entities rows, which by default contain only the id and entity_type_id columns. Any defined "static_attributes" are also added as real columns of this table. This is a very "tall and skinny" table, tipical of EAV systems.

entity_relationships

Columns: relationship_id, left_entity_id, right_entity_id

Primary Key: id

Foreign Key: left_entity_id -> "entitites" (ON DELETE CASCADE)

Foreign Key: right_entity_id -> "entitites" (ON DELETE CASCADE)

Stores the actual relationship links between "entities".

values

Columns: entity_id, attribute_id, value

Primary Key: entity_id, attribute_id

Foreign Key: entity_id -> "entitites" (ON DELETE CASCADE)

Foreign Key: attribute_id -> "attributes"

Stores the actual attributes values. One table named $table_prefix . $data_type . "_value" is created for each data type listed in "data_types".

METHODS

table

    my $table = $schema->table($name);

Returns a DBIx::EAV::Table representing the table $name.

dbh_do

has_data_type

deploy

Create the eav database tables.

    $eav->schema->deploy( add_drop_table => 1 );

get_ddl

Returns the eav schema DDL in any of the supported SQL::Translator producers. If no argument is passed a producer for the current driver is used.

    my $mysql_ddl = $eav->schema->get_ddl('MySQL');

db_driver_name

Shortcut for $self->dbh->{Driver}{Name}.

CASCADE DELETE

Since a single entity's data is spread over several value tables, we can't just delete the entity in a single SQL DELETE command. We must first send a DELETE for each of those value tables, and one more for the "entity_relationships" table. If an entity has attributes of 4 data types, and has any relationship defined, a total of 6 (six!!) DELETE commands will be needed to delete a single entity. Four to the "values" tables, one to the "entity_relationships" and one for the actual "entities" table).

Those extra DELETE commands can be avoided by using database-level ON DELETE CASCADE for the references from the values and entity_relationships tables to the entities table.

The current DBIx::EAV implementation can handle both situations, but defaults to database-level cascade delete. See "database_cascade_delete" option.

I'll probably drop support for no database-level cascade delete in the future... if no one points otherwise.

LICENSE

Copyright (C) Carlos Fernando Avila Gratz.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

AUTHOR

Carlos Fernando Avila Gratz <cafe@kreato.com.br>

cpanm DBIx::EAV

perl -MCPAN -e shell
install DBIx::EAV