SQL::S...::Embed(3pm)

💖 SQL::Statement::Embed 💖 (aka SQL::S...::Embed(3pm)) ✨

okay bestie, welcome to the User Contributed Perl Documentation for SQL::Statement::Embed — this one's literally about embedding a whole SQL engine inside a DBD or any other module. hydrate, sit down, let's go 🫶

✨ NAME ✨

SQL::Statement::Embed — embed a SQL engine in a DBD or module 💅

🌸 SYNOPSIS 🌸

ngl bestie, the synopsis is giving nothing. it's empty. no cap. the whole vibe lives down in the DESCRIPTION so keep scrolling 👑

🦋 DESCRIPTION 🦋

okay so SQL::Statement is designed to be easy to embed in other modules and to be especially easy to embed in DBI drivers. the way it works: it provides a SQL Engine, and then the other module has to bring the other two things — a data source and a storage mechanism. that's the deal ✨

like for example, DBD::CSV uses SQL::Statement as an embedded SQL engine by implementing a file-based data source and by using DBI as the user interface. and DBD::Amazon? it uses SQL::Statement as its SQL engine, provides its own extensions to the supported SQL syntax, and uses on-the-fly searches of Amazon.com as its data source. iconic behaviour honestly 💅

SQL::Statement is the basis for at least eight existing DBDs (DBI database drivers). so if you have a new data source, you too can create a DBD without having to reinvent the SQL wheel. it is fun and easy so become a DBD author today! 🔥

SQL::Statement can be also be embedded without DBI. we will explore that first since developing a DBD uses most of the same methods and techniques. easy one first, that's just self care 🫶

---

👑 The role of SQL::Statement subclasses 👑

SQL::Statement provides a SQL parsing and execution engine. it neither provides a data source nor storage mechanism other than in-memory tables. periodt.

DBI::DBD::SqlEngine contains a subclass of SQL::Statement to abstract from embedding SQL::Statement into a DBD and lets you concentrate on the extensions you need to make. DBD::File extends DBI::DBD::SqlEngine by providing access to file-based storage mechanisms. and here's the tea: it is quite possible to use things other than files as data sources, in which case you would not use DBD::File — instead you would replace DBD::File's methods with your own. in the examples below, we use DBD::File, replacing only a few methods. keeping it light ✨

so SQL::Statement provides SQL parsing and evaluation and DBI::DBD::SqlEngine provides DBI integration. the only thing missing is a data source — what we actually want to store and query. as an example suppose we are going to create a subclass called 'Foo' that will provide as a data source the in-memory storage which is used in SQL::RAM to provide the "TEMP" tables in SQL::Statement, but the rows are stored as a string using a serializer (Storable).

now consider what needs to happen to perform a SELECT query on our 'Foo' data:

• receive a SQL string
• parse the SQL string into a request structure
• open the table(s) specified in the request
• define column names and positions for the table
• read rows from the table
• convert the rows from colon-separated format into perl arrays
• match the columns and rows against the requested selection criteria
• return requested rows and columns to the user

to perform operations like INSERT and DELETE, we also need to:

• convert rows from perl arrays into colon-separated format
• write rows
• delete rows

SQL::Statement takes care of all of the SQL parsing and evaluation. DBD::File takes care of file opening, reading, writing, and deleting. so the only things 'Foo' is really responsible for are:

• define column names and positions for the table
• convert rows from colon-separated format into perl arrays
• convert rows from perl arrays into colon-separated format

look at that tiny little list. we love a low-effort main character moment 💖

in SQL::Statement subclasses these responsibilities are assigned to two objects. a ::Statement object is responsible for opening the table by creating new ::Table objects. a ::Table object is responsible for defining the column names and positions, opening data sources, reading, converting, writing and deleting data.

the real work is therefore done in the ::Table object, the ::Statement subclass is required to deliver the right ::Table object. it's giving delegation, it's giving teamwork 🤝

💅 Creating a ::Statement object 💅

a subclass of SQL::Statement must provide at least one method called open_table(). the method should open a new Table object and define the table's columns. for our 'Foo' module, here is the complete object definition:

package Foo;

package Foo::Statement;
use DBD::File;
use base qw(DBI::DBD::SqlEngine::Statement);

sub open_table {
    my ($self, $sth, $table, $createMode, $lockMode) = @_;

    my $class = ref $self;
    $class =~ s/::Statement/::Table/;

    return $class->new ($sth, $table, $createMode, $lockMode);
}

since 'Foo' is an in-memory data source, we subclass SQL::Statement indirectly through DBD::File::Statement. the open_table() method lets DBD::File do the actual table opening. all we do is define the files directory (f_dir), the names of the columns (col_names) and the positions of the columns (col_nums). DBD::File creates and returns a $tbl object. it names that object according to the module that calls it, so in our case the object will be a Foo::Table object. it named itself. main character energy fr 👑

🌸 Creating a ::Table object 🌸

Table objects are responsible for reading, converting, writing, and deleting data. since DBD::File provides most of those services, our 'Foo' subclass only needs to define three methods — fetch_row() to read data, push_row() to write data, and push_names() to store column names. we will leave deleting to DBD::File, since deleting a record in the 'Foo' format is the same process as deleting a record in any other simple file-based format. why do extra work when someone already did it 🫶

here is the complete object definition:

package Foo::Table;
use base qw(DBD::File::Table);

sub fetch_row {
   my($self, $data) = @_;
   my $fieldstr = $self->{fh}->getline;
   return undef unless $fieldstr;
   chomp $fieldstr;
   my @fields   = split /:/,$fieldstr;
   $self->{row} = (@fields ? \@fields : undef);
}
sub push_row {
   my($self, $data, $fields) = @_;
   my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
   $self->{fh}->print( $str."\n");
   1;
}
sub push_names {}
1;

the fetch_row() method uses DBD::File's getline() method to physically read a row of data, then we convert it from native colon-separated format into a perl arrayref. glow up ✨

the push_row() method converts from a perl arrayref back to colon-separated format then uses DBD::File's print() method to print it to file.

the push_names method does nothing because it's purpose is to store column names in a file and in our 'Foo' subclass, we are defining the column names ourselves, not storing them in a file. queen of doing nothing on purpose 💅

🎀 Trying out our new subclass 🎀

here is a script which should create and query a file in our 'Foo' format. it assumes you have saved the Foo, Foo::Statement, and Foo::Table classes shown above into a file called Foo.pm.

#!perl -w
use strict;
use Foo;
my $parser = SQL::Parser->new();
$parser->{RaiseError}=1;
$parser->{PrintError}=0;
for my $sql(split /\n/,
"  DROP TABLE IF EXISTS group_id
   CREATE TABLE group_id (username CHAR,uid INT, gid INT)
   INSERT INTO group_id VALUES('joe',1,1)
   INSERT INTO group_id VALUES('sue',2,1)
   INSERT INTO group_id VALUES('bob',3,2)
   SELECT * FROM group_id             "
){
   my $stmt = Foo::Statement->new($sql,$parser);
   $stmt->execute;
   next unless $stmt->command eq 'SELECT';
   while (my $row=$stmt->fetch) {
       print "@$row\n";
   }
}

this is the same script as shown in the section on executing and fetching in SQL::Statement::Structure except that instead of SQL::Statement->new(), we are using Foo::Statement->new(). the other difference is that the execute/fetch example was using in-memory storage while this script is using file-based storage and the 'Foo' format we defined. when you run this script, you will be creating a file called "group_id" and it will contain the specified data in colon-separated format. you literally just made a database from scratch, we're obsessed 😭💖

---

🌈 Developing a new DBD 🌈

⭐ Moving from a subclass to a DBD ⭐

a DBD based on SQL::Statement uses the same two subclasses that are shown above. they should be called DBD::Foo::Statement and DBD::Foo::Table, but would otherwise be identical to the non-DBD subclass illustrated above. to turn it into a full DBD, you have to subclass DBD::File, DBD::File::dr, DBD::File::db, and DBD::File::st. in many cases a simple subclass with few or no methods overridden is sufficient. lowkey the easiest glow-up ever 🌟

here is a working DBD::Foo:

package DBD::Foo;
use base qw(DBD::File);

package DBD::Foo::dr;
$DBD::Foo::dr::imp_data_size = 0;
use base qw(DBD::File::dr);

package DBD::Foo::db;
$DBD::Foo::db::imp_data_size = 0;
use base qw(DBD::File::db);

package DBD::Foo::st;
$DBD::Foo::st::imp_data_size = 0;
use base qw(DBD::File::st);

package DBD::Foo::Statement;
use base qw(DBD::File::Statement);

sub open_table {
    my $self = shift @_;
    my $data = shift @_;
    $data->{Database}->{f_dir} = './';
    my $tbl  = $self->SUPER::open_table($data,@_);
    $tbl->{col_names} = [qw(username uid gid)];
    $tbl->{col_nums}  = {username=>0,uid=>1,gid=>2};
    return $tbl;
}

package DBD::Foo::Table;
use base qw(DBD::File::Table);

sub fetch_row {
   my($self, $data) = @_;
   my $fieldstr = $self->{fh}->getline;
   return undef unless $fieldstr;
   chomp $fieldstr;
   my @fields   = split /:/,$fieldstr;
   $self->{row} = (@fields ? \@fields : undef);
}
sub push_row {
    my($self, $data, $fields) = @_;
    my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
    $self->{fh}->print( $str."\n");
    1;
}
sub push_names {}
1;

🍓 A sample script to test our new DBD 🍓

assuming you saved the DBD::Foo shown above as a file called "Foo.pm" in a directory called "DBD", this script will work, so will most other DBI methods such as selectall_arrayref, fetchrow_hashref, etc. so many methods, all of them eating 🔥

#!perl -w
use strict;
use lib qw(/home/jeff/data/module/lib); # or wherever you stored DBD::Foo
use DBI;
my $dbh=DBI->connect('dbi:Foo:');
$dbh->{RaiseError}=1;
$dbh->{PrintError}=0;
for my $sql(split /\n/,
"  DROP TABLE IF EXISTS group_id
   CREATE TABLE group_id (username CHAR,uid INT, gid INT)
   INSERT INTO group_id VALUES('joe',1,1)
   INSERT INTO group_id VALUES('sue',2,1)
   INSERT INTO group_id VALUES('bob',3,2)
   SELECT * FROM group_id             "
){
   my $stmt = $dbh->prepare($sql);
   $stmt->execute;
   next unless $stmt->{NUM_OF_FIELDS};
   while (my $row=$stmt->fetch) {
       print "@$row\n";
   }
}

---

okay bestie that's part 1 wrapped — there's more where this came from, so don't go anywhere 🦋💜

✨ Expanding the DBD ✨

okay so bestie, you have a working DBD now 💖 which means it's time for the fun part — making it do more. and there are like, so many directions you can take this:

• you can override some or all of DBD::File::Table to give yourself alternate ways of reading, writing, and deleting from your data source. slay, your data your rules 💅
• you can override the open_table() method to provide a different way of identifying column names (like, reading them straight from the file itself 🫶) or to hand out other kinds of metadata.
• want the actual API details for ::Table objects? go read SQL::Eval 📖 and for a whole example subclass, peek at DBD::File.

but wait, there's more ✨

• you might wanna create extensions to the SQL syntax that are specific to your DBD. see the section on extending SQL syntax over in SQL::Statement::Syntax.
• you might wanna provide a completely different kind of data source. check out DBD::DBM (its source code even includes documentation on subclassing SQL::Statement and DBD::File), plus other DBD::File subclasses like DBD::CSV.
• you might also wanna provide a completely different storage mechanism — something not based on files at all 👀 see DBD::Amazon and DBD::AnyData.
• and you will almost certainly wanna fine-tune the DBI interface, so go look at DBI::DBD.

---

🫶 Getting help with a new DBD 🫶

the dbi-devATperl.org mailing list should be your first stop when you're creating a new DBD. like, genuinely. Tim Bunce, the author of DBI, hangs out there — along with many DBD authors. it's giving group chat but for DBD devs 💬

tell them what you're planning and they'll offer suggestions about similar modules, or other people working on similar issues, or just how to proceed. no cap, they're nice. go say hi 🌸

---

👑 AUTHOR & COPYRIGHT 👑

Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.
Copyright (c) 2010-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.

this document may be freely modified and distributed under the same terms as Perl itself. iconic behavior, honestly 💜

---

and that's a wrap on part 2, bestie 🎀 this whole thing came from perl v5.30.3, dated 2020-10-23, and it's the SQL::S...::Embed(3pm) page — go build something gorgeous with it, mwah 💅✨