/ 
SQLite-VirtualTable-Pivot-0.02
River stage zero No dependents
/ SQLite::VirtualTable::Pivot

NAME

SQLite::VirtualTable::Pivot -- use SQLite's virtual tables to represent pivot tables.

SYNOPSIS

$ export SQLITE_CURRENT_DB=/tmp/foo.db
sqlite3 $SQLITE_CURRENT_DB
sqlite> .load perlvtab.so
sqlite> create table object_attributes (id integer, name varchar, value integer);
sqlite> insert into object_attributes values ( 1, "length", 20 );
sqlite> insert into object_attributes values ( 1, "color", "red" );
sqlite> create virtual table object_pivot using perl
          ("SQLite::VirtualTable::Pivot", "object_attributes" );
sqlite> select * from object_pivot;
   id|color|length
   1|red|20

DESCRIPTION

A pivot table is a table in which the distinct row values of a column in one table are used as the names of the columns in another table.

Here's an example:

Given this table :

Student Subject    Grade
------- -------    -----
Joe     Reading    A
Joe     Writing    B
Joe     Arithmetic C
Mary    Reading    B-
Mary    Writing    A+
Mary    Arithmetic C+

A pivot table created using the columns "Student" and "Subject" and the value "Grade" would yield :

Student Arithmetic Reading Writing
------- ---------- ------- ----------
Joe     C          A       B
Mary    C+         B-      A+

To create a table, use the following syntax :

create virtual table object_pivot using perl
           ("SQLite::VirtualTable::Pivot", "base_table" );

To specify the three columns, use :

create virtual table object_pivot using perl
           ("SQLite::VirtualTable::Pivot", "base_table",
            "pivot_row", "pivot_column", "pivot_value" );

where pivot_row, pivot_column and pivot_value are three columns in the base_table. The distinct values of pivot_column will be the names of the new columns in the pivot table. (The values may be sanitized to create valid column names.)

If any of the three columns are foreign keys, these may be collapsed in the pivot table, as described below.

The list of distinct columns is calculated the first time a pivot table is used (or created) in a database session. So, if the list changes, you may need to re-connect.

Entity-Atribute-Value models

The Entity-Attribute-Value model is a representation of data in a table containing three columns representing an entity, an attribute, and a value. For instance :

Entity Attribute Value
------ --------- -----
1       color    red
1       length   20
2       color    blue

To reduce redundancy or to constrain the possible attributes/values, some or all of the three columns may be foreign keys. Consider for instance, the following :

create table entities (
     id integer primary key,
     entity varchar, 
     unique (entity) );

create table attributes (
    id integer primary key,
    attribute varchar,
    unique (attribute) );

create table value_s (
    id integer primary key,
    value integer, -- nb: "integer" is only the column affinity
    unique (value) );

create table eav (
    entity    integer references entities(id),
    attribute integer references attributes(id),
    value     integer references value_s(id),
    primary key (entity,attribute)
);

Then the foreign keys may be "flattened" into the pivot table by using this SQL :

create virtual table
    eav_pivot using perl ("SQLite::VirtualTable::Pivot",
       "eav",
       "entity->entity(id).entity",
       "attribute->attributes(id).attribute",
       "value->value_s(id).value"
       );

Then the columns in eav_pivot would be the entries in attributes.attribute corresponding to the distinct values in eav.attribute.

Moreover, queries against the pivot table will do the right thing, in the sense that restrictions will use the values in the value_s table, not in the eav table.

EXAMPLE

create table students (student, subject, grade, primary key (student,subject)); 
insert into students values ("Joe", "Reading", "A");
insert into students values ("Joe", "Writing", "B");
insert into students values ("Joe", "Arithmetic", "C");
insert into students values ("Mary", "Reading", "B-");
insert into students values ("Mary", "Writing", "A+");
insert into students values ("Mary", "Arithmetic", "C+");

select load_extension("perlvtab.so");
create virtual table roster using perl ("SQLite::VirtualTable::Pivot", "students", "student", "subject", "grade");
select * from roster;

Student Reading Writing Arithmetic
------- ------- ------- ----------
Joe     A       B       C
Mary    B-      A+      C+

select student from roster where writing = "A+";
Mary

FUNCTIONS (called by sqlite, see SQLite::VirtualTable)

CREATE (constructor)

Arguments : module : "perl", caller : "main" virtual_table : the name of the table being created base_table : the table being pivoted @pivot_columns (optional) : entity, attribute, value

Returns : A new SQLite::VirtualTable::Pivot object.

Description : Create a new SQLite::VirtualTable::Pivot object. The base_table is the table to be pivoted. If this table contains only three columns, then they will be used in order as the pivot_row, pivot_column, and pivot_value columns (aka entity, attribute, value). Alternatively, these columns may be specified in the create statement by passing them as parameters. If one of the values is a foreign key and the pivot table should instead use a column in the child table, that may be specified using the following notation :

   base_table_column->child_table(child_key).child_column_to_use

If a column name contains a space, then the portion after the
space should be the column affinity.

Examples :

CREATE VIRTUAL TABLE pivot_table USING perl
   ("SQLite::VirtualTable::Pivot","base_table" );

CREATE VIRTUAL TABLE pivot_table USING perl
   ("SQLite::VirtualTable::Pivot","base_table",
   "entity","attribute","value");

CREATE VIRTUAL TABLE pivot_table USING perl
   ("SQLite::VirtualTable::Pivot","base_table",
   "entity integer","attribute varchar","value integer");

CREATE VIRTUAL TABLE pivot_table USING perl
   ("SQLite::VirtualTable::Pivot","base_table",
     "entty",
     "attribute->attribute_lookup(id).attr",
     "value->value_lookup(id).value" );
DECLARE_SQL

Arguments: none Returns: a CREATE TABLE statement that specifies the columns of the virtual table.

OPEN

Create and return a new cursor. This returns a new SQLite::VirtualTable::Pivot::Cursor object.

This is called before BEST_INDEX or FILTER, just to create the new empty object.

BEST_INDEX

Given a set of constraints and an order, return the name (and number) of the best index that should be used to run this query, and the cost of using this index.

See SQLite::VirtualTable for a more complete description of the incoming and outgoing parameters.

FILTER

Given a cursor and an index number (created dynamically in BEST_FILTER) and the @args to pass to the index, run the query on the base table, joining as necessary to filter the results.

EOF

Are there any more rows left?

NEXT

Advance the cursor one row.

COLUMN

Get a piece of data from a given column (and the current row).

ROWID

Generate a unique id for this row.

CLOSE

Close the cursor.

DROP

Drop the virtual table.

DISCONNECT

Disconnect from the database.

TODO

- re-use the existing database handle (requires changes
  to SQLite::VirtualTable and DBD::SQLite)
- allow modification of the data in the virtual table
- allow value column to not have integer affinity
- more optimization

SEE ALSO

SQLite::VirtualTable::Pivot::Cursor

SQLite::VirtualTable