{
    "content": [
        {
            "type": "text",
            "text": "# SQL::Statement::Embed (info)\n\n## NAME\n\nSQL::Statement::Embed - embed a SQL engine in a DBD or module\n\n## DESCRIPTION\n\nSQL::Statement is designed to be easy to embed in other modules and to\nbe especially easy to embed in DBI drivers.  It provides a SQL Engine\nand the other module needs to then provide a data source and a storage\nmechanism.  For example, the DBD::CSV module uses SQL::Statement as an\nembedded SQL engine by implementing a file-based data source and by\nusing DBI as the user interface.  Similarly DBD::Amazon uses\nSQL::Statement as its SQL engine, provides its own extensions to the\nsupported SQL syntax, and uses on-the-fly searches of Amazon.com as its\ndata source.\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **DESCRIPTION**\n- **The role of SQL::Statement subclasses**\n- **Creating a ::Statement object**\n- **Creating a ::Table object**\n- **Trying out our new subclass**\n- **Developing a new DBD**\n- **Expanding the DBD**\n- **Getting help with a new DBD**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n"
        }
    ],
    "structuredContent": {
        "command": "SQL::Statement::Embed",
        "section": "",
        "mode": "info",
        "summary": "SQL::Statement::Embed - embed a SQL engine in a DBD or module",
        "synopsis": "",
        "tldr_summary": null,
        "tldr_examples": [],
        "tldr_source": null,
        "flags": [],
        "examples": [],
        "see_also": [],
        "section_outline": [
            {
                "name": "NAME",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "SYNOPSIS",
                "lines": 1,
                "subsections": []
            },
            {
                "name": "DESCRIPTION",
                "lines": 19,
                "subsections": []
            },
            {
                "name": "The role of SQL::Statement subclasses",
                "lines": 54,
                "subsections": []
            },
            {
                "name": "Creating a ::Statement object",
                "lines": 28,
                "subsections": []
            },
            {
                "name": "Creating a ::Table object",
                "lines": 40,
                "subsections": []
            },
            {
                "name": "Trying out our new subclass",
                "lines": 35,
                "subsections": []
            },
            {
                "name": "Developing a new DBD",
                "lines": 87,
                "subsections": []
            },
            {
                "name": "Expanding the DBD",
                "lines": 24,
                "subsections": []
            },
            {
                "name": "Getting help with a new DBD",
                "lines": 14,
                "subsections": []
            }
        ],
        "sections": {
            "NAME": {
                "content": "SQL::Statement::Embed - embed a SQL engine in a DBD or module\n",
                "subsections": []
            },
            "SYNOPSIS": {
                "content": "",
                "subsections": []
            },
            "DESCRIPTION": {
                "content": "SQL::Statement is designed to be easy to embed in other modules and to\nbe especially easy to embed in DBI drivers.  It provides a SQL Engine\nand the other module needs to then provide a data source and a storage\nmechanism.  For example, the DBD::CSV module uses SQL::Statement as an\nembedded SQL engine by implementing a file-based data source and by\nusing DBI as the user interface.  Similarly DBD::Amazon uses\nSQL::Statement as its SQL engine, provides its own extensions to the\nsupported SQL syntax, and uses on-the-fly searches of Amazon.com as its\ndata source.\n\nSQL::Statement is the basis for at least eight existing DBDs (DBI\ndatabase drivers).  If you have a new data source, you too can create a\nDBD without having to reinvent the SQL wheel.  It is fun and easy so\nbecome a DBD author today!\n\nSQL::Statement can be also be embedded without DBI.  We will explore\nthat first since developing a DBD uses most of the same methods and\ntechniques.\n",
                "subsections": []
            },
            "The role of SQL::Statement subclasses": {
                "content": "SQL::Statement provides a SQL parsing and execution engine.  It neither\nprovides a data source nor storage mechanism other than in-memory\ntables.  The DBI::DBD::SqlEngine contains a subclass of SQL::Statement\nto abstract from embedding SQL::Statement into a DBD and lets you\nconcentrate on the extensions you need to make. DBD::File extends\nDBI::DBD::SqlEngine by providing access to file-based storage\nmechanisms.  It is quite possible to use things other than files as\ndata sources, in which case you would not use DBD::File, instead you\nwould replace DBD::File's methods with your own.  In the examples\nbelow, we use DBD::File, replacing only a few methods.\n\nSQL::Statement provides SQL parsing and evaluation and\nDBI::DBD::SqlEngine provides DBI integration.  The only thing missing\nis a data source - what we actually want to store and query.   As an\nexample suppose we are going to create a subclass called 'Foo' that\nwill provide as a data source the in-memory storage which is used in\nSQL::RAM to provide the \"TEMP\" tables in SQL::Statement, but the rows\nare stored as a string using a serializer (Storable).\n\nConsider what needs to happen to perform a SELECT query on our 'Foo'\ndata:\n\n* receive a SQL string\n* parse the SQL string into a request structure\n* open the table(s) specified in the request\n* define column names and positions for the table\n* read rows from the table\n* convert the rows from colon-separated format into perl arrays\n* match the columns and rows against the requested selection criteria\n* return requested rows and columns to the user\n\nTo perform operations like INSERT and DELETE, we also need to:\n\n* convert rows from perl arrays into colon-separated format\n* write rows\n* delete rows\n\nSQL::Statement takes care of all of the SQL parsing and evaluation.\nDBD::File takes care of file opening, reading, writing, and deleting.\nSo the only things 'Foo' is really responsible for are:\n\n* define column names and positions for the table\n* convert rows from colon-separated format into perl arrays\n* convert rows from perl arrays into colon-separated format\n\nIn SQL::Statement subclasses these responsibilities are assigned to two\nobjects.  A ::Statement object is responsible for opening the table by\ncreating new ::Table objects.  A ::Table object is responsible for\ndefining the column names and positions, opening data sources, reading,\nconverting, writing and deleting data.\n\nThe real work is therefore done in the ::Table object, the ::Statement\nsubclass is required to deliver the right ::Table object.\n",
                "subsections": []
            },
            "Creating a ::Statement object": {
                "content": "A subclass of SQL::Statement must provide at least one method called\nopentable().  The method should open a new Table object and define the\ntable's columns.  For our 'Foo' module, here is the complete object\ndefinition:\n\npackage Foo;\n\npackage Foo::Statement;\nuse DBD::File;\nuse base qw(DBI::DBD::SqlEngine::Statement);\n\nsub opentable {\nmy ($self, $sth, $table, $createMode, $lockMode) = @;\n\nmy $class = ref $self;\n$class =~ s/::Statement/::Table/;\n\nreturn $class->new ($sth, $table, $createMode, $lockMode);\n}\n\nSince 'Foo' is an in-memory data source, we subclass SQL::Statement\nindirectly through DBD::File::Statement.  The opentable() method lets\nDBD::File do the actual table opening.  All we do is define the files\ndirectory (fdir), the names of the columns (colnames) and the\npositions of the columns (colnums).  DBD::File creates and returns a\n$tbl object.  It names that object according to the module that calls\nit, so in our case the object will be a Foo::Table object.\n",
                "subsections": []
            },
            "Creating a ::Table object": {
                "content": "Table objects are responsible for reading, converting, writing, and\ndeleting data. Since DBD::File provides most of those services, our\n'Foo' subclass only needs to define three methods - fetchrow() to read\ndata, pushrow() to write data, and pushnames() to store column names.\nWe will leave deleting to DBD::File, since deleting a record in the\n'Foo' format is the same process as deleting a record in any other\nsimple file-based format.  Here is the complete object definition:\n\npackage Foo::Table;\nuse base qw(DBD::File::Table);\n\nsub fetchrow {\nmy($self, $data) = @;\nmy $fieldstr = $self->{fh}->getline;\nreturn undef unless $fieldstr;\nchomp $fieldstr;\nmy @fields   = split /:/,$fieldstr;\n$self->{row} = (@fields ? \\@fields : undef);\n}\nsub pushrow {\nmy($self, $data, $fields) = @;\nmy $str = join ':', map { defined $ ? $ : '' } @$fields;\n$self->{fh}->print( $str.\"\\n\");\n1;\n}\nsub pushnames {}\n1;\n\nThe fetchrow() method uses DBD::File's getline() method to physically\nread a row of data, then we convert it from native colon-separated\nformat into a perl arrayref.\n\nThe pushrow() method converts from a perl arrayref back to colon-\nseparated format then uses DBD::File's print() method to print it to\nfile.\n\nThe pushnames method does nothing because it's purpose is to store\ncolumn names in a file and in our 'Foo' subclass, we are defining the\ncolumn names ourselves, not storing them in a file.\n",
                "subsections": []
            },
            "Trying out our new subclass": {
                "content": "Here is a script which should create and query a file in our 'Foo'\nformat.  It assumes you have saved the Foo, Foo::Statement, and\nFoo::Table classes shown above into a file called Foo.pm.\n\n#!perl -w\nuse strict;\nuse Foo;\nmy $parser = SQL::Parser->new();\n$parser->{RaiseError}=1;\n$parser->{PrintError}=0;\nfor my $sql(split /\\n/,\n\"  DROP TABLE IF EXISTS groupid\nCREATE TABLE groupid (username CHAR,uid INT, gid INT)\nINSERT INTO groupid VALUES('joe',1,1)\nINSERT INTO groupid VALUES('sue',2,1)\nINSERT INTO groupid VALUES('bob',3,2)\nSELECT * FROM groupid             \"\n){\nmy $stmt = Foo::Statement->new($sql,$parser);\n$stmt->execute;\nnext unless $stmt->command eq 'SELECT';\nwhile (my $row=$stmt->fetch) {\nprint \"@$row\\n\";\n}\n}\n\nThis is the same script as shown in the section on executing and\nfetching in SQL::Statement::Structure except that instead of\nSQL::Statement->new(), we are using Foo::Statement->new().   The other\ndifference is that the execute/fetch example was using in-memory\nstorage while this script is using file-based storage and the 'Foo'\nformat we defined.  When you run this script, you will be creating a\nfile called \"groupid\" and it will contain the specified data in colon-\nseparated format.\n",
                "subsections": []
            },
            "Developing a new DBD": {
                "content": "Moving from a subclass to a DBD\nA DBD based on SQL::Statement uses the same two subclasses that are\nshown above.  They should be called DBD::Foo::Statement and\nDBD::Foo::Table, but would otherwise be identical to the non-DBD\nsubclass illustrated above.  To turn it into a full DBD, you have to\nsubclass DBD::File, DBD::File::dr, DBD::File::db, and DBD::File::st.\nIn many cases a simple subclass with few or no methods overridden is\nsufficient.\n\nHere is a working DBD::Foo:\n\npackage DBD::Foo;\nuse base qw(DBD::File);\n\npackage DBD::Foo::dr;\n$DBD::Foo::dr::impdatasize = 0;\nuse base qw(DBD::File::dr);\n\npackage DBD::Foo::db;\n$DBD::Foo::db::impdatasize = 0;\nuse base qw(DBD::File::db);\n\npackage DBD::Foo::st;\n$DBD::Foo::st::impdatasize = 0;\nuse base qw(DBD::File::st);\n\npackage DBD::Foo::Statement;\nuse base qw(DBD::File::Statement);\n\nsub opentable {\nmy $self = shift @;\nmy $data = shift @;\n$data->{Database}->{fdir} = './';\nmy $tbl  = $self->SUPER::opentable($data,@);\n$tbl->{colnames} = [qw(username uid gid)];\n$tbl->{colnums}  = {username=>0,uid=>1,gid=>2};\nreturn $tbl;\n}\n\npackage DBD::Foo::Table;\nuse base qw(DBD::File::Table);\n\nsub fetchrow {\nmy($self, $data) = @;\nmy $fieldstr = $self->{fh}->getline;\nreturn undef unless $fieldstr;\nchomp $fieldstr;\nmy @fields   = split /:/,$fieldstr;\n$self->{row} = (@fields ? \\@fields : undef);\n}\nsub pushrow {\nmy($self, $data, $fields) = @;\nmy $str = join ':', map { defined $ ? $ : '' } @$fields;\n$self->{fh}->print( $str.\"\\n\");\n1;\n}\nsub pushnames {}\n1;\n\nA sample script to test our new DBD\nAssuming you saved the DBD::Foo shown above as a file called \"Foo.pm\"\nin a directory called \"DBD\", this script will work, so will most other\nDBI methods such as selectallarrayref, fetchrowhashref, etc.\n\n#!perl -w\nuse strict;\nuse lib qw(/home/jeff/data/module/lib); # or wherever you stored DBD::Foo\nuse DBI;\nmy $dbh=DBI->connect('dbi:Foo:');\n$dbh->{RaiseError}=1;\n$dbh->{PrintError}=0;\nfor my $sql(split /\\n/,\n\"  DROP TABLE IF EXISTS groupid\nCREATE TABLE groupid (username CHAR,uid INT, gid INT)\nINSERT INTO groupid VALUES('joe',1,1)\nINSERT INTO groupid VALUES('sue',2,1)\nINSERT INTO groupid VALUES('bob',3,2)\nSELECT * FROM groupid             \"\n){\nmy $stmt = $dbh->prepare($sql);\n$stmt->execute;\nnext unless $stmt->{NUMOFFIELDS};\nwhile (my $row=$stmt->fetch) {\nprint \"@$row\\n\";\n}\n}\n",
                "subsections": []
            },
            "Expanding the DBD": {
                "content": "Now that we have a basic DBD operational, there are several directions\nfor expansion.  In the first place, we might want to override some or\nall of DBD::File::Table to provide alternate means of reading, writing,\nand deleting from our data source.  We might want to override the\nopentable() method to provide a different means of identifying column\nnames (e.g.  reading them from the file itself) or to provide other\nkinds of metadata.  See SQL::Eval for documentation of the API for\n::Table objects and see DBD::File for an example subclass.\n\nWe might want to create extensions to the SQL syntax specific to our\nDBD.  See the section on extending SQL syntax in\nSQL::Statement::Syntax.\n\nWe might want to provide a completely different kind of data source.\nSee DBD::DBM (whose source code includes documentation on subclassing\nSQL::Statement and DBD::File), and other DBD::File subclasses such as\nDBD::CSV.\n\nWe might also want to provide a completely different storage mechanism,\nsomething not based on files at all.  See DBD::Amazon and DBD::AnyData.\n\nAnd we will almost certainly want to fine-tune the DBI interface, see\nDBI::DBD.\n",
                "subsections": []
            },
            "Getting help with a new DBD": {
                "content": "The dbi-devATperl.org mailing list should be your first stop in\ncreating a new DBD.  Tim Bunce, the author of DBI and many DBD authors\nhang out there.  Tell us what you are planning and we will offer\nsuggestions about similar modules or other people working on similar\nissues, or on how to proceed.\n\nAUTHOR & COPYRIGHT\nCopyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.\nCopyright (c) 2010-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.\n\nThis document may be freely modified and distributed under the same\nterms as Perl itself.\n\nperl v5.30.3                      2020-10-23        SQL::Statement::Embed(3pm)",
                "subsections": []
            }
        }
    }
}