Recordset(3pm)
🌸 DBIx::Recordset 💅 — the Recordset(3pm) era
ok so bestie. this is DBIx::Recordset, a Perl extension for DBI recordsets ✨ it lives in the User Contributed Perl Documentation section, filed under Recordset(3pm), and it's genuinely about to make your database life so much cuter. 🎀
✨ NAME ✨
DBIx::Recordset — Perl extension for DBI recordsets 🫶
🎀 SYNOPSIS 🎀
ok watch, this is the whole vibe in one code block:
use DBIx::Recordset;
# Setup a new object and select some recods...
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:Oracle:....',
'!Table' => 'users',
'$where' => 'name = ? and age > ?',
'$values' => ['richter', 25] }) ;
# Get the values of field foo ...
print "First Records value of foo is $set[0]{foo}\n" ;
print "Second Records value of foo is $set[1]{foo}\n" ;
# Get the value of the field age of the current record ...
print "Age is $set{age}\n" ;
# Do another select with the already created object...
$set -> Search ({name => 'bar'}) ;
# Show the result...
print "All users with name bar:\n" ;
while ($rec = $set -> Next)
{
print $rec -> {age} ;
}
# Setup another object and insert a new record
*set2 = DBIx::Recordset -> Insert ({'!DataSource' => 'dbi:Oracle:....',
'!Table' => 'users',
'name' => 'foo',
'age' => 25 }) ;
# Update this record (change age from 25 to 99)...
$set -> Update ({age => 99}, {name => 'foo'}) ;like… it's giving effortless 😭💖
🦋 DESCRIPTION 🦋
DBIx::Recordset is a perl module for abstraction and simplification of database access. slay.
the goal?? make standard database access (select/insert/update/delete) easier to handle and independent of the underlying DBMS. ✨ special attention is made on web applications, so it's possible to handle state-less access and to process the posted data of formfields — but no cap, DBIx::Recordset is not limited to web applications. she does it all. 👑
DBIx::Recordset uses the DBI API to access the database, so it should work with every database for which a DBD driver is available (see also DBIx::Compat). iykyk 💅
most public functions take a hash reference as parameter, which makes it simple to supply various different arguments to the same function. the parameter hash can also be taken from a hash containing posted formfields like those available with CGI.pm, mod_perl, HTML::Embperl and others. periodt.
before using a recordset it is necessary to setup an object. of course the setup step can be made with the same function call as the first database access, but it can also be handled separately. your call bestie 🫶
most functions which set up an object return a typglob. a typglob in Perl is an object which holds pointers to all datatypes with the same name. therefore a typglob must always have a name and can't be declared with my. you can only use it as a global variable or declare it with local. the trick for using a typglob is that setup functions can return a reference to an object, an array and a hash at the same time. it's giving main character energy fr fr 🌈
- the object is used to access the object's methods
- the array is used to access the records currently selected in the recordset
- the hash is used to access the current record
if you don't like the idea of using typglobs you can also set up the object, array and hash separately, or just set the ones you need. no pressure 💖
💖 ARGUMENTS 💖
since most methods take a hash reference as argument, here's a description of the valid arguments first. (yes we're doing the lore drop first, it's necessary 🙈)
🌈 Setup Parameters 🌈
all parameters starting with an ! are only recognized at setup time. if you specify them in later function calls they will be ignored. you can also preset these parameters with the TableAttr method of DBIx::Database. this allows you to presetup most parameters for the whole database and they will be use every time you create a new DBIx::Recordset object, without specifying it every time. obsessed. ✨
💅 !DataSource
specifies the database to which to connect. this information can be given in the following ways:
- Driver/DB/Host. — same as the first parameter to the DBI connect function.
DBIx::Recordsetobject — takes the same database handle as the givenDBIx::Recordsetobject.DBIx::Databaseobject — takes Driver/DB/Host from the given database object. seeDBIx::Databasefor details aboutDBIx::Databaseobject. when using more then one Recordset object, this is the most efficient method.DBIx::Datasbaseobject name — takes Driver/DB/Host from the database object which is saved under the given name ($saveasparameter toDBIx::Database -> new).- an DBI database handle — uses given database handle.
🎀 !Table
Tablename. multiple tables are comma-separated.
🌸 !Username
Username. same as the second parameter to the DBI connect function.
🔐 !Password
Password. same as the third parameter to the DBI connect function.
⭐ !DBIAttr
reference to a hash which holds the attributes for the DBI connect function. see perldoc DBI for a detailed description.
🦋 !Fields
fields which should be returned by a query. if you have specified multiple tables the fieldnames should be unique. if the names are not unique you must specify them along with the tablename (e.g. tab1.field).
- NOTE 1: fieldnames specified with
!Fieldscan't be overridden. if you plan to use other fields with this object later, use$Fieldsinstead. - NOTE 2: the keys for the returned hash normally don't have a table part. only the fieldname part forms the key. (see
!LongNamesfor an exception.) - NOTE 3: because the query result is returned in a hash, there can only be one out of multiple fields with the same name fetched at once. if you specify multiple fields with the same name, only one is returned from a query. which one this actually is depends on the DBD driver. (see
!LongNamesfor an exception.) - NOTE 4: some databases (e.g. mSQL) require you to always qualify a fieldname with a tablename if more than one table is accessed in one query.
💜 !TableFilter
the TableFilter parameter specifies which tables should be honoured when DBIx::Recordset searches for links between tables (see below). when given as parameter to DBIx::Database it filters for which tables DBIx::Database retrieves metadata. only thoses tables are used which starts with prefix given by "!TableFilter". also the DBIx::Recordset link detection tries to use this value as a prefix of table names, so you can leave out this prefix when you write a fieldname that should be detected as a link to another table. 🧚
👑 !LongNames
when set to 1, the keys of the hash returned for each record not only consist of the fieldnames, but are built in the form table.field.
🌈 !Order
fields which should be used for ordering any query. if you have specified multiple tables the fieldnames should be unique. if the names are not unique you must specify them among with the tablename (e.g. tab1.field).
- NOTE 1: fieldnames specified with
!Ordercan't be overridden. if you plan to use other fields with this object later, use$orderinstead.
🔥 !TabRelation
condition which describes the relation between the given tables (e.g. tab1.id = tab2.id) (see also !TabJoin.)
Example
'!Table' => 'tab1, tab2',
'!TabRelation' => 'tab1.id=tab2.id',
'name' => 'foo'this will generate the following SQL statement:
SELECT * FROM tab1, tab2 WHERE name = 'foo' and tab1.id=tab2.id ;🫶 !TabJoin
!TabJoin allows you to specify an INNER/RIGHT/LEFT JOIN which is used in a SELECT statement. (see also !TabRelation.)
Example
'!Table' => 'tab1, tab2',
'!TabJoin' => 'tab1 LEFT JOIN tab2 ON (tab1.id=tab2.id)',
'name' => 'foo'this will generate the following SQL statement:
SELECT * FROM tab1 LEFT JOIN tab2 ON (tab1.id=tab2.id) WHERE name = 'foo' ;🔑 !PrimKey
name of the primary key. when this key appears in a WHERE parameter list (see below), DBIx::Recordset will ignore all other keys in the list, speeding up WHERE expression preparation and execution. note that this key does NOT have to correspond to a field tagged as PRIMARY KEY in a CREATE TABLE statement.
🍓 !Serial
name of the primary key. in contrast to "!PrimKey" this field is treated as an autoincrement field. if the database does not support autoincrement fields, but sequences the field is set to the next value of a sequence (see "!Sequence" and "!SeqClass") upon each insert. if a "!SeqClass" is given the values are always retrived from the sequence class regardless if the DBMS supports autoincrement or not. the value from this field from the last insert could be retrieved by the function "LastSerial". ✨
💀 !Sequence
name of the sequence to use for this table when inserting a new record and "!Serial" is defind. defaults to <tablename>_seq.
🧚 !SeqClass
name and Parameter for a class that can generate unique sequence values. this is a string that holds comma separated values. the first value is the class name and the following parameters are given to the new constructor. see also DBIx::Recordset::FileSeq and DBIx::Recordset::DBSeq.
Example: '!SeqClass' => 'DBIx::Recordset::FileSeq, /tmp/seq'
⚠️ !WriteMode
!WriteMode specifies which write operations to the database are allowed and which are disabled. you may want to set !WriteMode to zero if you only need to query data, to avoid accidentally changing the content of the database. 🙈
NOTE: the!WriteModeonly works for theDBIx::Recordsetmethods. if you disable!WriteMode, it is still possible to usedoto send normal SQL statements to the database engine to write/delete any data. like… be careful bestie, that's the loophole 😭
!WriteMode consists of some flags, which may be added together:
DBIx::Recordset::wmNONE(0) — allow no write access to the table(s)DBIx::Recordset::wmINSERT(1) — allowINSERTDBIx::Recordset::wmUPDATE(2) — allowUPDATEDBIx::Recordset::wmDELETE(4) — allowDELETEDBIx::Recordset::wmCLEAR(8) — to allowDELETEfor the whole table,wmDELETEmust be also specified. this is necessary for assigning a hash to a hash which is tied to a table. (Perl will first erase the whole table, then insert the new data.) 💀DBIx::Recordset::wmALL(15) — allow every access to the table(s)
Default is wmINSERT + wmUPDATE + wmDELETE 👑
!StoreAll— okay so if you turn this on, DBIx::Recordset hoards every row it fetches between two consecutive accesses, which means you can grab rows in whatever chaotic order your heart desires (row 5, then 2, then 7, then 1, go off bestie 💅). If you don't set it, rows only get pulled into memory when you actually ask for them, which means you're stuck going in ascending order — like 1, 2, 3. Try 3, 2, 4 and row 2 hands you back anundefwhile 3 and 4 are totally fine. Also peep DATA ACCESS below 👀!HashAsRowKey— so by default, the hash the setup function gives back is tied to the current record, and you use it to poke at the fields of that record. But set this to true and the hash gets tied to the whole database instead 🫶. That means the key of the hash becomes the primary key in the table, used to select a single row. (heads up: this one only does anything on functions that return a typglob, iykyk)!IgnoreEmpty— this lil param decides how empty and undefined values get treated. Values1and2are lowkey iconic when you're running DBIx::Recordset inside a CGI script, because browsers send empty form fields as empty strings. 🙈0(default) — an undefined value is treated as SQLNULL, and an empty string stays an empty string.1— every field with an undefined value gets ignored when building theWHEREexpression.2— every field with an undefined value or an empty string gets ignored when building theWHEREexpression.
NOTE: the default for versions before0.18was2. not me being nostalgic for the old days 😭
!Filter— filters are the glow-up station for your data 💖 they pre/post-process everything that gets read from or written to the database. The!Filterparameter takes a hash reference stuffed with filter functions. If the key is numeric, it's treated as a type value and the filter gets applied to all fields of that type. If the key is alphanumeric, the filter only touches the named field. Every filter description is an array with at least two elements: the first element must hold the input function, the second must hold the output function. Either one can beundefif you only need the other. Data gets passed to the input function before it's written to the database, and that input function has to return the value in the correct format for the database. The output function gets applied to data read from the database before it's handed back to you. ✨
Example:
'!Filter' =>
{
DBI::SQL_DATE =>
[
sub { shift =~ /(\d\d)\.(\d\d)\.(\d\d)/ ; "19$3$2$1"},
sub { shift =~ /\d\d(\d\d)(\d\d)(\d\d)/ ; "$3.$2.$1"}
],
'datefield' =>
[
sub { shift =~ /(\d\d)\.(\d\d)\.(\d\d)/ ; "19$3$2$1"},
sub { shift =~ /\d\d(\d\d)(\d\d)(\d\d)/ ; "$3.$2.$1"}
],
}Both of these filters convert a date in the format dd.mm.yy to the database format 19yymmdd and back again. The first one does it for every field of the type SQL_DATE, the second one does it for the fields named datefield. 🗓️
You can also pass the !Filter parameter to the function TableAttr of the DBIx::Database object. When you do that, it applies to all DBIx::Recordset objects that use those tables. slay, inheritance.
A third parameter can optionally be specified, and it can be set to "DBIx::Recordset::rqINSERT", "DBIx::Recordset::rqUPDATE", or the sum of both. If it's set, the InputFunction (which gets called during UPDATE or INSERT) is always called for this field in updates and/or inserts, depending on the value. And if there's no data specified for this field as an argument to a function that causes an UPDATE/INSERT, the InputFunction gets called with an argument of undef.
During UPDATE and INSERT the input function gets either the string 'insert' or 'update' passed as the second parameter. 👑
!LinkName— this is how you get a clear text description of a linked table instead of (or in addition to) the!LinkField. Picture it: you've got a record with all your bills, and every record contains a customer number — set!LinkNameandDBIx::Recordsetcan automatically pull the customer's name instead of (or in addition to) the bill record itself. 💖- select additional fields — this additionally selects all fields given in
!NameFieldof the Link or the table attributes (seeTableAttr). - build name in uppercase of
!MainField— this takes the values of!NameFieldof the Link or the table attributes (seeTableAttr) and joins the content of those fields together into a new field, which has the same name as the!MainField, but in uppercase. - replace
!MainFieldwith the contents of!NameField— same as 2, except the!MainFieldgets replaced with the "name" of the linked record.
- select additional fields — this additionally selects all fields given in
See also !Links and WORKING WITH MULTIPLE TABLES below 🌈
!Links— bestie this is the group-project parameter 🫂 it links multiple tables together. It takes a reference to a hash, which has — as keys — names for a special "linkfield", and — as values — a parameter hash. That parameter hash can contain all the Setup parameters, and those setup parameters get used to construct a new recordset object to access the linked table. If!DataSourceis omitted (which, as a general rule, it should be), the same DataSource (and database handle) as the main object is used. There are special parameters that can only show up in a link definition (see the next paragraph). For the full tea on how links are handled, see WORKING WITH MULTIPLE TABLES below. 🦋
🎀 Link Parameters 🎀
!MainField— the!MailFieldparameter holds a fieldname which is used to retrieve a key value for the search in the linked table from the main table. If it's omitted, it gets set to the same value as!LinkedField. 💫!LinkedField— the fieldname which holds the key value in the linked table. If it's omitted, it gets set to the same value as!MainField.!NameField— this specifies the field or fields that will be used as a "name" for the destination table. It can be a string or a reference to an array of strings. So like, if you link to an address table, you might specify the field"nickname"as the name field for that table, or you might use['name', 'street', 'city']. 🌸
Look at !LinkName for more info.
!DoOnConnect— you can give an SQL Statement (or an array reference of SQL statements) that gets executed every single time, right after a connect to the db. As a third possibility you can give a hash reference. After every successful connect,DBIx::Recordsetexecutes the statements in the element which corresponds to the name of the driver.'*'gets executed for all drivers. 🔥!Default— specifies default values for new rows that get inserted via hash or array access. TheInsertmethod ignores this parameter. 💅!TieRow— set this parameter to zero andDBIx::Recordsetwill not tie the returned rows to aDBIx::Recordset::Rowobject, and instead hands back a simple hash. The upside? It speeds things up. The downside? You can't write to a row like that, and you can't use the link feature with it either. trade-offs, bestie 😭!Debug— sets the debug level. See DEBUGGING.!PreFetch— only for tieing a hash! Gives a where expression (either as a string or as a hashref) that's used to prefetch records from that database. Every following access to the tied hash only touches this prefetched data and doesn't execute any database queries at all. See"!Expires"for how to force a refetch. Give'*'as the value to"!PreFetch"and it fetches the whole entire table into memory. 🧠✨
The following example prefetches all record with id < 7:
tie %dbhash, 'DBIx::Recordset::Hash', {'!DataSource' => $DSN,
'!Username' => $User,
'!Password' => $Password,
'!Table' => 'foo',
'!PreFetch' => {
'*id' => '<',
'id' => 7
},
'!PrimKey' => 'id'} ;The following example prefetches all records:
tie %dbhash, 'DBIx::Recordset::Hash', {'!DataSource' => $DSN,
'!Username' => $User,
'!Password' => $Password,
'!Table' => 'bar',
'!PreFetch' => '*',
'!PrimKey' => 'id'} ;!Expires— only for tieing a hash! If the value is numeric, the prefetched data gets refetched if it's older than the given number of seconds. If the value is a CODEREF, the function gets called and the data gets refetched if the function returns true. ⏰!MergeFunc— only for tieing a hash! Gives a reference to a function that gets called when more than one record for a given hash key is found, to merge the records into one. The function receives a reference to both records as arguments. If more than two records are found, the function gets called again for each following record, which is already merged data as the first parameter. 💜
The following example sets up a hash, that, when more then one record with the same id is found, the field sum is added and the first record is returned, where the sum field contains the sum of all found records:
tie %dbhash, 'DBIx::Recordset::Hash', {'!DataSource' => $DSN,
'!Username' => $User,
'!Password' => $Password,
'!Table' => 'bar',
'!MergeFunc' => sub { my ($a, $b) = @_ ; $a->{sum} += $b->{sum} ; },
'!PrimKey' => 'id'} ;🌟 Where Parameters 🌟
The following parameters are used to build an SQL WHERE expression
$where— bestie, this is the literal SQL WHERE expression drop. You hand it over raw and it goes in exactly as-is. If$whereis set, every single other where parameter listed below gets ignored — like, ghosted, no cap. The only exception is$values, which you can still use to hand over the values that get bound to the placeholders inside your$where. 💖$values— the values that get bound to the placeholders you put in$where. Slay.
Example:
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:Oracle:....',
'!Table' => 'users',
'$where' => 'name = ? and age > ?',
'$values' => ['richter', 25] }) ;NOTE: filters you set up with"!Filter"are not applied to these values, becauseDBIx::Recordsethas literally no way to know which value belongs to which field. It's not being shady, it just can't tell. 😭
{fieldname}— the value for that field. It gets quoted automatically if it needs to be, don't worry. The value can also be an array ref, in which case the values get glued together with the operator you passed via$valueconj(default:or).
Example:
'name' => [ 'mouse', 'cat'] will expand to name='mouse' or name='cat'
'{fieldname}— value for the field, and it will always be quoted. You only need this ifDBIx::Recordsetcan't figure out the correct type for the field itself. Backup plan energy. ✨#{fieldname}— value for the field, and it will never be quoted, but it will be converted to a number. Again, only needed ifDBIx::Recordsetcan't determine the correct type for a field.\{fieldname}— value for the field, and it will not be converted in any way, like at all. Which means you have to quote it before handing it toDBIx::Recordsetif quoting is needed. All on you, babe. 💅+{fieldname}|{fieldname}..— values for multiple fields. The value must be in one/all of the fields, depending on$compconj.
Example:
'+name|text' => 'abc' will expand to name='abc' or text='abc'
$compconj—'or'or'and'(default is'or'). Sets the conjunction between multiple fields. (see above, iykyk)$valuesplit— a regular expression for splitting a field value into multiple values (default is'\t'). The conjunction for multiple values can be set with$valueconj. By default, only one of the values has to match the field.
Example:
'name' => "mouse\tcat" will expand to name='mouse' or name='cat'
NOTE: the example above can also be written as 'name' => [ 'mouse', 'cat'] — same vibe, different font. 🦋
$valueconj—'or'or'and'(default is'or'). Sets the conjunction for multiple values.$conj—'or'or'and'(default is'and') — the conjunction between fields.$operator— the default operator when you didn't specify one for a field. (default is'=')*{fieldname}— the operator for that named field.
Example:
'value' => 9, '*value' => '>' expand to value > 9
It can also be an array ref, so you can pass different operators for the values. This is mainly handy when you need to select a range. 👑
Example:
$set -> Search ({id => [5, 7 ],
'*id' => ['>=', '<='],
'$valueconj' => 'and'}) ;This will expand to "id >= 5 and id <= 7"
NOTE: to actually get a range you need to set the$valueconjparameter to"and", because it defaults to"or". Don't forget or you'll get something cursed. 💀
$expr—$exprlets you group parts of the where expression so the priority is correct, which is very main character of it. To specify more than one sub-expression, add a numerical index to$expr(e.g.$expr1,$expr2).
Example:
$set -> Search ({id => 5,
'$expr' =>
{
'name' => 'Richter',
'country' => 'de',
'$conj' => 'or'
}
}) ;This will expand to
(name = 'Richter' or country = 'de') and id = 5🔍 Search parameters
$start— first row to fetch. The row you name here will show up as index 0 in the data array. No cap. ✨$max— maximum number of rows to fetch. Every attempt to fetch more rows than you specified here returnsundef, even if the select itself returns more rows. It's a hard limit, periodt.$next— adds the number you supply with$maxto$start. This exists so you can build a next button. 🎀$prev— subtracts the number you supply with$maxfrom$start. This one's for your previous button. 🫶$order— fieldname(s) for ordering (ORDER BY). Must be comma-separated, and it can also containUSING.$group— fieldname(s) for grouping (GROUP BY). Must be comma-separated, and it can also containHAVING.$append— a string that gets appended to the end of aSELECTstatement. It can contain literally any data. Anything. 🌈$fields— the fields that should be returned by a query. If you've specified multiple tables, the fieldnames should be unique. If the names are not unique, you must specify them along with the tablename (e.g.tab1.field).
NOTE 1: if!fieldswas supplied at setup time, this cannot be overridden by$fields.
NOTE 2: the keys for the returned hash normally don't have a table part. Only the fieldname part forms the key. (See !LongNames for an exception.)
NOTE 3: because the query result is returned in a hash, there can only be one out of multiple fields with the same name fetched at once. If you specify multiple fields with the same name, only one is returned from a query. Which one it actually is depends on the DBD driver. (See !LongNames for an exception.) It's giving chaos, but that's just how it is. 🙈
$primkey— name of the primary key.DBIx::Recordsetassumes that if you specify it, it's a unique key to the given table(s).DBIx::Recordsetcannot verify this. You are responsible for specifying the right key — that's on you, bestie. If such a primary key exists in your table, you should specify it here, because it helpsDBIx::Recordsetoptimize how it buildsWHEREexpressions. 🔥
See also !PrimKey
⚡ Execute parameters
The following parameters specify which action is to be executed:
=search— search data=update— update record(s)=insert— insert record=delete— delete record(s)=empty— setup empty object
💅 METHODS
*set = DBIx::Recordset -> Setup (\%params)— sets up a new object and connects it to a database and table(s). Collects information about the tables that'll be needed later. Returns a typglob which you can use to access the object ($set), an array (@set) and a hash (%set).
params: setup
$set = DBIx::Recordset -> SetupObject (\%params)— same as above, but it only sets up the object; it does not tie anything (no array, no hash). Minimalist. 🦋
params: setup
$set = tie @set, 'DBIx::Recordset', $set$set = tie @set, 'DBIx::Recordset', \%params— ties an array to a recordset object. The result of a query executed by the returned object can be accessed via the tied array. If the array contents are modified, the database is updated accordingly (see Data access below for more details). The first form ties the array to an already existing object; the second one sets up a new object.
params: setup
$set = tie %set, 'DBIx::Recordset::Hash', $set$set = tie %set, 'DBIx::Recordset::Hash', \%params— ties a hash to a recordset object. The hash can be used to access/update/insert single rows of a table: the hash key is identical to the primary key value of the table. (see Data access below for more details)
The first form ties the hash to an already existing object; the second one sets up a new object.
params: setup
$set = tie %set, 'DBIx::Recordset::CurrRow', $set$set = tie %set, 'DBIx::Recordset::CurrRow', \%params— ties a hash to a recordset object. The hash can be used to access the fields of the current record of the recordset object. (See Data access below for more details.) 👑
The first form ties the hash to an already existing object; the second one sets up a new object.
params: setup
*set = DBIx::Recordset -> Select (\%params, $fields, $order)$set -> Select (\%params, $fields, $order)$set -> Select ($where, $fields, $order)— selects records from the recordset's table(s).
The first syntax sets up a new DBIx::Recordset object and does the select.
The second and third syntax select from an existing DBIx::Recordset object.
params: setup (only syntax 1), where (without $order and $fields)
where: (only syntax 3) string for SQL WHERE expression
fields: comma separated list of fieldnames to select
order: comma separated list of fieldnames to sort on
*set = DBIx::Recordset -> Search (\%params)set -> Search (\%params)— does a search on the given tables and prepares data so you can access it via@setor%set. The first syntax also sets up a new object.
params: setup (only syntax 1), where, search
*set = DBIx::Recordset -> Insert (\%params)$set -> Insert (\%params)— inserts a new record in the recordset table(s). Params should contain one entry for every field you want to insert a value into.
Fieldnames may be prefixed with a '\', in which case they are not processed (quoted) in any way.
params: setup (only syntax 1), fields
*set = DBIx::Recordset -> Update (\%params, $where)*set = DBIx::Recordset -> Update (\%params, $where)set -> Update (\%params, $where)set -> Update (\%params, $where)— updates one or more records in the recordset table(s). Parameters should contain one entry for every field you want to update. The$wherecontains the SQL WHERE condition as a string or as a reference to a hash. If$whereis omitted, the where conditions are built from the parameters. If!PrimKeyis given for the table, only that!PrimKeyis used for theWHEREclause.
Fieldnames may be prefixed with a '\', in which case they are not processed (quoted) in any way.
params: setup (only syntax 1+2), where (only if $where is omitted), fields
*set = DBIx::Recordset -> Delete (\%params)$set -> Delete (\%params)— deletes one or more records from the recordset's table(s). 🗑️
params: setup (only syntax 1), where
*set = DBIx::Recordset -> DeleteWithLinks (\%params)and$set -> DeleteWithLinks (\%params)— bestie this one deletes one or more records from the recordset's table(s) 💅 and it's lowkey iconic because it also handles the link records — every link record that has!OnDeleteset gets either deleted or its corresponding field gets set toundef. Which one happens is decided by the constantsodDELETEandodCLEAR. This is so helpful for keeping your database integrity from falling apart, no cap.
params: setup (only syntax 1), where
*set = DBIx::Recordset -> Execute (\%params)and$set -> Execute (\%params)— runs one of the methods above, depending on the args you hand it 🫶. If you specify multiple execute params, here's the priority order, and yes it matters:
=search
=update
=insert
=delete
=emptyIf none of those params are given, a search happens. And a search is always performed anyway. On an =update, the !PrimKey, if given, is looked at and used for the where part of the SQL statement, while all the other parameters get updated.
params: setup (only syntax 1), execute, where, search, fields
$set -> do ($statement, $attribs, \%params)— same as DBI fr fr. Executes a single SQL statement on the open database.$set -> Reset ()— sets the record pointer back to the initial state, so the next call to"Next"returns the first row. Rewind energy 🎀.$set -> First ()— puts the record pointer on the first row and returns it.$set -> Next ()— puts the record pointer on the next row and returns it.$set -> Prev ()— puts the record pointer on the previous row and returns it.$set -> Curr ()— returns the current row.$set -> AllNames ()— returns a reference to an array of all fieldnames of all tables used by the object.$set -> Names ()— returns a reference to an array of the fieldnames from the last query.$set -> AllTypes ()— returns a reference to an array of all fieldtypes of all tables used by the object.$set -> Types ()— returns a reference to an array of the fieldtypes from the last query.$set -> Add ()and$set -> Add (\%data)— adds a new row to a recordset 🌸. The first form adds an empty row, the second one assigns initial data to it.Addreturns an index into the array where the new record lives.
Example:
# Add an empty record
$i = $set -> Add () ;
# Now assign some data
$set[$i]{id} = 5 ;
$set[$i]{name} = 'test' ;
# and here it is written to the database
# (without Flush it is written, when the record goes out of scope)
$set -> Flush () ;Add will also set the current record to the newly created empty record. So you can just assign the data by using the current record, easy peasy:
# Add an empty record
$set -> Add () ;
# Now assign some data to the new record
$set{id} = 5 ;
$set{name} = 'test' ;$set -> MoreRecords ([$ignoremax])— returns true if there are more records to fetch from the current recordset ✨. If the$ignoremaxparameter is given and is true,MoreRecordsignores the$maxparameter of the lastSearch.
To tell you whether there are more records, More actually fetches the next record from the database and stores it in memory. It does not change the current record, so don't panic.
$set -> PrevNextForm ($prevtext, $nexttext, \%fdat)and$set -> PrevNextForm (\%param, \%fdat)— returns an HTML form containing a previous button, a next button, and all the data from%fdatas hidden fields 🦋. When you call theSearchmethod, you must set the$maxparameter to the number of rows you want to see at once. After the search and the retrieval of the rows, you can callPrevNextFormto generate the buttons you need for scrolling through the recordset.
The second form lets you specify additional parameters, which creates first, previous, next, last and goto buttons. Example:
$set -> PrevNextForm ({-first => 'First', -prev => '<<Back',
-next => 'Next>>', -last => 'Last',
-goto => 'Goto #'}, \%fdat)The goto button lets you jump to a random record number. If you omit any of the parameters, the corresponding button won't be shown. Customize your little button squad, go off.
$set -> Flush— flushes all data to the database and therefore makes sure the db is up-to-date 💖. NormallyDBIx::Recordsetholds the update in memory until the row is destroyed, either by a newSelect/Searchor by the Recordset object itself being destroyed. With this method you can make sure every update is really written to the db.$set -> Dirty ()— returns true if there's at least one dirty row containing unflushed data. She's messy, she's honest.DBIx::Recordset::Undef ($name)—Undeftakes the name of a typglob and will destroy the array, the hash, and the object 💀. All unwritten data is written to the db. All db connections are closed and all memory is freed.
Example:
# this destroys $set, @set and %set
DBIx::Recordset::Undef ('set') ;$set -> Begin— starts a transaction. Calls the DBI methodbegin.$set -> Rollback— rolls back a transaction. Calls the DBI methodrollbackand makes sure all internal buffers ofDBIx::Recordsetare flushed.$set -> Commit— commits a transaction. Calls the DBI methodcommitand makes sure all internal buffers ofDBIx::Recordsetare flushed.$set -> DBHdl ()— returns the DBI database handle 🔥.$set -> StHdl ()— returns the DBI statement handle of the last select.$set -> TableName ()— returns the name of the table of the recordset object.$set -> TableNameWithOutFilter ()— returns the name of the table of the recordset object, but removes the string given with!TableFilterif it is the prefix of the table name.$set -> PrimKey ()— returns the primary key given in the!PrimKeyparameter.$set -> TableFilter ()— returns the table filter given in the!TableFilterparameter.$set -> StartRecordNo ()— returns the record number of the record which will be returned for index 0.$set -> LastSQLStatement ()— returns the last executed SQL statement 👑.$set -> LastSerial ()— returns the last value of the field defined with!Serial.$set -> Disconnect ()— closes the connection to the database.$set -> Link($linkname)— if$linknameisundef, returns a reference to a hash of all links of the object. Otherwise it returns a reference to the link with the given name.$set -> Links()— returns a reference to a hash of all links of the object.$set -> Link4Field($fieldname)— returns the name of the link for that field, or<undef>if there is no link for that field.$set -> TableAttr ($key, $value, $table)— get and/or set an attribute of the table 🧚.$key— key to set/get$value— if present, set$keyto this value$table— optional, lets you specify another table than the one used by the recordset object.
$set -> Stats ()— returns a hash ref with some statistical values. Numbers, but make it cute.$set -> LastError ()andDBIx::Recordset -> LastError ()— returns the last error message, if any. If called in an array context, the first element receives the last error message and the second receives the last error code 🙈.
💾 DATA ACCESS 💾
The data returned by a Select or a Search can be accessed in two ways:
- Through an array 📚. Each item of the array corresponds to one of the selected records. Each array item is a reference to a hash containing an entry for every field.
Example:
$set[1]{id} access the field 'id' of the second record found
$set[3]{name} access the field 'name' of the fourth record foundThe record is fetched from the DBD driver when you access it the first time, and is stored by DBIx::Recordset for later access. If you don't access the records one after each other, the skipped records are not stored and therefore can't be accessed anymore — unless you specify the !StoreAll parameter. iykyk, skip at your own risk.
DBIx::Recordsetholds a current record which can be accessed directly via a hash 💜. The current record is the one you last accessed via the array. After aSelectorSearch, it's reset to the first record. You can change the current record via the methodsNext,Prev,First,Add.
Example:
$set{id} access the field 'id' of the current record
$set{name} access the field 'name' of the current recordInstead of doing a Select or Search, you can directly access one row of a table when you've tied a hash to DBIx::Recordset::Hash or have specified the !HashAsRowKey parameter 🎀. The hashkey will work as primary key to the table. You must specify the !PrimKey as a setup parameter.
Example:
$set{4}{name} access the field 'name' of the row with primary key = 4✏️ MODIFYING DATA DIRECTLY ✏️
One way to update/insert data into the database is by using the Update, Insert or Execute method of the DBIx::Recordset object. A second way is to directly assign new values to the result of a previous Select/Search 🌈.
Example:
# setup a new object and search all records with name xyz
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:db:tab',
'!PrimKey => 'id',
'!Table' => 'tabname', 'name' => 'xyz'}) ;
#now you can update an existing record by assigning new values
#Note: if possible, specify a PrimKey for update to work faster
$set[0]{'name'} = 'zyx' ;
# or insert a new record by setting up an new array row
$set[9]{'name'} = 'foo' ;
$set[9]{'id'} = 10 ;
# if you don't know the index of a new row you can obtain
# one by using Add
my $i = $set -> Add () ;
$set[$i]{'name'} = 'more foo' ;
$set[$i]{'id'} = 11 ;
# or add an empty record via Add and assign the values to the current
# record
$set -> Add () ;
$set{'name'} = 'more foo' ;
$set{'id'} = 11 ;
# or insert the data directly via Add
$set -> Add ({'name' => 'even more foo',
'id' => 12}) ;
# NOTE: up to this point, NO data is actually written to the db!
# we are done with that object, Undef will flush all data to the db
DBIx::Recordset::Undef ('set') ;IMPORTANT: the data is not written to the database until you explicitly call flush, or a new query is started, or the object is destroyed 💀. This is to keep the actual writes to the database to a minimum.
🌈 WORKING WITH MULTIPLE TABLES 🌈
okay bestie, this is the part where DBIx::Recordset really shows off, because it has some genuinely cute features that make dealing with multiple tables and their relationships so much easier. 💅
🔗 Joins
so first thing: you can totally hand more than one table to the !Table parameter. but bestie, if you do that, you have to tell it how those tables are related — that's what the !TabRelation parameter is for. this method accesses all the tables you specified at the same time. slay. ✨
🌸 Join Example:
say you've got these two tables, where the field street_id is a pointer to the table street:
table name
name char (30),
street_id integer
table street
id integer,
street char (30),
city char (30)then you can do a search like this:
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
'!Table' => 'name, street',
'!TabRelation'=> 'name.street_id = street.id'}) ;the result? you get a set containing the fields name, street_id, street, city and id, where id is always equal to street_id. 🫶
⚠️ but heads up: if there are multiple streets for one name, you'll get as many records for that name as there are streets for it. that's exactly why this approach works best when you have a 1:1 relation. no cap.
you can also specify JOINs, like this:
*set = DBIx::Recordset -> Search ({
'!DataSource' => 'dbi:drv:db',
'!Table' => 'name, street',
'!TabJoin' => 'name LEFT JOIN street ON (name.street_id=street.id)'}) ;the difference between this and the first example is that this version also returns a record even if neither table contains a record for the given id. how it's done depends on the JOIN you give it (LEFT/RIGHT/INNER) — go check your SQL documentation for the deets on JOINs. 📚
💖 Links
if you've got 1:n relations between two tables, this next one might be a better way to handle it:
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
'!Table' => 'name',
'!Links' => {
'-street' => {
'!Table' => 'street',
'!LinkedField' => 'id',
'!MainField' => 'street_id'
}
}
}) ;after that query, every record will contain the fields name and street_id. plus — and this is the iconic part — there's a pseudofield named -street, which you can use to access another recordset object. that object is the result of a query where street_id = id. 🔥
use it like this:
$set{name} to access the name field
$set{-street}{street} to access the first street (as long as the
current record of the subobject isn't
modified)
$set{-street}[0]{street} first street
$set{-street}[1]{street} second street
$set{-street}[2]{street} third street
$set[2]{-street}[1]{street} to access the second street of the
third nameyou can have multiple linked tables in one recordset, you can nest linked tables, and yes bestie, you can even link a table to itself. main character energy fr fr. 👑
NOTE: if you select only some fields and not all, the field specified by'!MainField'must also be given in the'!Fields'or'$fields'parameter.
NOTE: see also Automatic detection of links below 👀
🎀 LinkName
with the LinkName feature you can specify a "name" for every table. a name is one or more fields that gives a human-readable "key" of that record. for example, in the example above, id is the key of the record, but the human-readable form is street. 💖
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
'!Table' => 'name',
'!LinkName' => 1,
'!Links' => {
'-street' => {
'!Table' => 'street',
'!LinkedField' => 'id',
'!MainField' => 'street_id',
'!NameField' => 'street'
}
}
}) ;for every record in the table, this example will return the fields:
name street_id streetif you've got more complex records, you can also specify more than one field in !NameField and pass it as a reference to an array, e.g. ['street', 'city']. in that case the result will contain:
name street_id street cityif you set !LinkName to 2, the result will contain the fields:
name street_id STREET_IDwhere STREET_ID contains the values of the street and city fields joined together. and if you set !LinkName to 3, you'll get only:
name street_idwhere street_id contains the values of the street and city fields joined together. ✨
NOTE: the!NameFieldcan also be specified as a table attribute with the functionTableAttr. in that case you don't need to specify it in every link. and when a!NameFieldis given in a link description, it overrides the table attribute.
🦋 Automatic detection of links
DBIx::Recordset and DBIx::Database will try to automatically detect links between tables based on the field and table names. for this feature to work, the field that points to another table must consist of the table name and the field name of the destination joined together with an underscore (like name.street_id in the example above). then it'll automatically be recognized as a pointer to street.id. 🪄
*set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
'!Table' => 'name') ;and that's literally enough, bestie. DBIx::Recordset will automatically add the !Links attribute. 💅
on top of that, DBIx::Recordset adds a backlink (which starts with a star, '*'), so for the table street in our example above, there'll be a link named *name, which is a pointer from table street to all records in the table name where street.id is equal to name.street_id. 🌟
you can use the !Links attribute to specify links that can't be automatically detected.
NOTE: to specify more than one link from one table to another table, you can prefix the field name with a specifier followed by two underscores. Example:first__street_id,second__street_id. the link (and backlink) names are named with the prefix, e.g.-first__streetand the backlink*first__name. 🎀
🗄️ DBIx::Database
The DBIx::Database object is literally the info-gatherer bestie of the datasource 💖. Its whole main character purpose? Build an object at startup that pulls every necessary deet out of the database. This object sniffs out the links between tables and stashes that info so the DBIx::Recordset objects can use it later. And bc sometimes you know stuff the database doesn't, there are extra methods that let you add kinds of information that can't be grabbed automatically. 🫶
Example, bestie:
$db = DBIx::Database -> new ({'!DataSource' => $DSN,
'!Username' => $User,
'!Password' => $Password,
'!KeepOpen' => 1}) ;
*set = DBIx::Recordset -> Search ({'!DataSource' => $db,
'!Table' => 'foo',
}) ;✨ new ($data_source, $username, $password, \%attr, $saveas, $keepopen)
$data_source— tells you which database to connect to. Format is Driver/DB/Host. Same energy as the first parameter you'd hand to the DBI connect function.$username— username (optional, no pressure 💅)$password— password (also optional)\%attr— attributes (optional) Same as the attribute parameter to the DBI connect function.$saveas— the name this DBIx::Database object saves itself as. You can use that name inDBIx::Database::Get, or as the!DataSourceparameter when you call the DBIx::Recordset object.
This is intended as mechanism to retrieve the necessary metadata; for example, when your web server starts (e.g. in the startup.pl file of mod_perl). Here you can give the database object a name. Later in your mod_perl or Embperl scripts, you can use this metadata by specifying this name. This will speed up the setup of DBIx::Recordset object without the need to pass a reference to the DBIx::Database object. (Translation: name it once, reuse it forever, so iconic ✨)
$keepopen— Normaly the database connection will be closed after the metadata has been retrieved from the database. This makes sure you don't get trouble when using the new method in a mod_perl startup file. You can keep the connection open to use them in further setup calls to DBIx::Recordset objects. When the database is not kept open, you must specify the"!Password"parameter each time the recordset has to be reopend. 🫠$tabfilter— same as setup parameter!TableFilter$doonconnect— same as setup parameter!DoOnConnect$reconnect— If set, forces DBIx::Database to"undef"any preexisting database handle and call connect in any case. This is useful in together with Apache::DBI. While the database connection are still kept open by Apache::DBI, Apache::DBI preforms a test if the handle is still vaild (which DBIx::Database itself wouldn't). 🔁
You also can specify a hashref which can contain the following parameters:
!DataSource, !Username, !Password, !DBIAttr, !SaveAs, !KeepOpen, !TableFilter, !DoOnConnect, !Reconnect
💅 $db = DBIx::Database -> DBHdl
returns the database handle (only if you specify !KeepOpen when calling "new").
💅 $db = DBIx::Database -> Get ($name)
$name = The name of the DBIx::Database object you wish to retrieve
Get a DBIx::Database object which has already been set up based on the name.
💅 $db -> TableAttr ($table, $key, $value)
get and/or set an attribute for an specfic table.
$table— Name of table(s). You may use'*'instead of the table name to specify a default value which applies to all tables for which no other value is specified. (a wildcard default, we love a catch-all 🌈)$key— key to set/get$value— if present, set key to this value
💅 $db -> TableLink ($table, $linkname, $value)
Get and/or set a link description for an table. If no $linkname is given, returns all links for that table.
$table— Name of table(s)$linkname— Name of link to set/get$value— if present, this must be a reference to a hash with the link decription. See!Linksfor more information.
💅 $db -> MetaData ($table, $metadata, $clear)
Get and/or set the meta data for the given table.
$table— Name of table(s)$metadata— If present, this must be a reference to a hash with the new metadata. You should only use this if you really know what you are doing. 👀$clear— Clears the metadata for the given table, The next call toDBIx::Database -> newwill recreate the metadata. Useful if your table has changed (e.g. byALTER TABLE).
💅 $db -> AllTables
This returns a reference to a hash of the keys to all the tables of the datasource.
💅 $db -> AllNames ($table)
Returns a reference to an array of all fieldnames for the given table.
💅 $db -> AllTypes ($table)
Returns a reference to an array of all fieldtypes for the given table.
💅 $db -> do ($statement, $attribs, \%params)
Same as DBI. Executes a single SQL statement on the open database.
👑 $db -> CreateTables ($dbschema, $schemaname, $user, $setpriv, $alterconstraints)
The CreateTables method is used to create an modify the schema of your database. The idea is to define the schema as a Perl data structure and give it to this function, it will compare the actual schema of the database with the one provided and creates new tables, new fields or drop fields as necessary. It also sets the permission on the tables and is able to create indices for the tables.
It will never drop a whole table! 🛑
NOTE: Create tables cannot deteminate changes of the datatype of a fields, because DBI is not able to provide this information in a standard way.
$dbschema— Either the name of a file which contains the schema or a array ref. See below how this schema must look like.$schemaname— schemaname (only used for Oracle)$user— User that should be granted access. See"!Grant"parameter.$setpriv— If set to true, access privilegs are revoked and granted again for already existing tables. That is necessary when$userchanges.$alterconstraints— If set to true contrains are cleared/set for already existing fields. DBI doesn't provide a database independ way to check which contrains already exists.
📐 Schema definition
If give as a filename, the file must contain an hash %DBDefault and an array @DBSchema. The first gives default and the second is an array of hashs. Every of this hash defines one table.
Example:
%DBDefault =
(
'!Grant' =>
[
'select',
'insert',
'update',
'delete',
],
)
;
@DBSchema = (
{
'!Table' => 'language',
'!Fields' =>
[
'id' => 'char (2)',
'directory' => 'varchar(40)',
'name' => 'varchar(40)',
'europe' => 'bool',
],
'!PrimKey' => 'id',
'!Default' =>
{
'europe' => 1,
},
'!Init' =>
[
{'id' => 'de', 'directory' => 'html_49', 'name' => 'deutsch'},
{'id' => 'en', 'directory' => 'html_76', 'name' => 'english'},
{'id' => 'fr', 'directory' => 'html_31', 'name' => 'french'},
],
'!Index' =>
[
'directory' => '',
]
},
);The hash which defines a table can have the following keys:
!Table— Gives the table name!Fields— Array with field names and types. There a some types which a translated database specifc. You can define more database specific translation inCompat.pm.bitbooleancounter— If an autoincrementing integer. For databases (like Oracle) that doesn't have such a datatype a sequence is generated to provide the autoincrement value and the fields will be of type integer.tinytext— variables length text with up to 255 characterstext— variables length text
!PrimKey— Name of the primary key!For— Can contain the same key as the table definintion, but is only executed for a specifc database.
Example:
'!For' => {
'Oracle' => {
'!Constraints' =>
{
'web_id' => ['foreign key' => 'REFERENCES web (id)'],
'prim__menu_id' => ['!Name' => 'web_prim_menu_id',
'foreign key' => 'REFERENCES menu (id)',
'not null' => ''],
}
},
},!Contraints— Used to define constraints. See example under"!For".!Name => <name><constraint> => <second part>
!Init— Used to initialy populate the table.!Default— Used to set a default value for a field, when the table is created. This doesn't have any affect for furtherINSERTs/UPDATEs. 🙈!Grant— Give the rights that should be grant to$user!Index— Gives the names for the fields for which indices should be created. If the second parameter for an index is not empty, it gives the index name, otherwise a default name is used.
💀 $db -> DropTables ($schemaname, $user)
Drops all tables. Use with care! 💀
$schemaname— schemaname (only used for Oracle)$user— User that should be revoked access. See"!Grant"parameter.
🔤 Casesensitive/insensitiv
In SQL all names (field/tablenames etc.) should be case insensitive. Various DBMS handle the case of names differently. For that reason DBIx::Recordset translates all names to lower case, ensuring your application will run with any DBMS, regardless of whether names are returned in lower/uppercase by the DBMS. Some DBMS are case-sensitive (I know at least Sybase, depending on your collate settings). To use such a case-sensitive DBMS, it is best to create your database with all names written in lowercase. In a situation where this isn't possible, you can set $PreserveCase to 1. In this case DBIx::Recordset will not perform any case translation.
NOTE: $PreserveCase is still experimental and may change in future releases. 🧪✨
🌸 FETCHSIZE / $FetchsizeWarn 🌸
okay bestie, listen up, this one's important 💅
So like, some operations in Perl — think foreach, or assigning arrays — literally need to know the size of the whole array, right? And when Perl wants that size, it calls the method FETCHSIZE. The problem? Not all DBD drivers/DBMS hand back the number of selected rows after an SQL SELECT. So the only way to actually figure out how many rows got selected would be to fetch every single one of them from the DBMS. And bestie, that is a LOT of work, so it can be super inefficient. 😭
So that's why DBIx::Recordset, by default, calls die() when Perl calls FETCHSIZE. Yeah, dramatic, but it's protecting you.
Now — if you know your DBD driver returns the correct value in $sth -> "rows" after executing a SELECT, you can set $FetchsizeWarn to zero, and then FETCHSIZE will just return the value from $sth -> "rows". Slay. ✨
And if you set it to 1, DBIx::Recordset will only issue a warning but still perform the operation. Casual.
NOTE: Since I don't have enough experience with the behaviour of this feature with different DBMS, this is considered experimental. 🙈
💖 DEBUGGING 💖
DBIx::Recordset can write a logfile so you can literally see what's happening inside. There are two public variables and the !Debug parameter doing all the work here:
$DBIx::Recordset::Debugor!Debug— the debuglevel, and here's the breakdown, no cap:0= off1= log only errors2= show connect, disconnect and SQL Statements3= some more infos4= much infos
$DBIx::Recordset::Debug sets the default debug level for new objects, while !Debug can be used to set the debuglevel on a per-object basis. 👑
DBIx::Recordset::LOG— the filehandle used for logging. The default isSTDOUT, unless you're running under HTML::Embperl, in which case the default is the Embperl logfile. 🌈
Example:
# open the log file
open LOG, ">test.log" or die "Cannot open test.log" ;
# assign filehandle
*DBIx::Recordset::LOG = \*LOG ;
# set debugging level
$DBIx::Recordset::Debug = 2 ;
# now you can create a new DBIx::Recordset object🎀 SECURITY 🎀
Since one possible application of DBIx::Recordset is its use in a web-server environment, some attention should paid to security issues. (Yes, that sentence has a typo in the original and I am keeping it, bestie. Accuracy first. 💅)
The current version of DBIx::Recordset does not include extended security management, but some features can be used to make your database access safer. (More security features will come in future releases. ⭐)
First of all, use the security feature of your database. Assign the web server process as few rights as possible. Periodt.
The greatest security risk is when you feed DBIx::Recordset a hash which contains the formfield data posted to the web server. Somebody who knows DBIx::Recordset can post other parameters than those you would expect a normal user to post. 😱 For this reason, a primary issue is to override all parameters which should never be posted by your script.
Example:
*set = DBIx::Recordset -> Search ({%fdat,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table")}) ;(assuming your posted form data is in %fdat). The above call will make sure that nobody from outside can override the values supplied by $Driver, $DB and $Table. 🫶
It is also wise to initialize your objects by supplying parameters which can not be changed.
Somewhere in your script startup (or at server startup time) add a setup call:
*set = DBIx::Recordset-> Setup ({'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table",
'!Fields' => "a, b, c"}) ;Later, when you process a request you can write:
$set -> Search (\%fdat) ;This will make sure that only the database specified by $Driver, $DB, the table specified by $Table and the Fields a, b, and c can be accessed. Iconic behaviour honestly. ✨
🦋 Compatibility with different DBD drivers 🦋
I have put a great deal of effort into making DBIx::Recordset run with various DBD drivers. The problem is that not all necessary information is specified via the DBI interface (yet). So I have made the module DBIx::Compat which gives information about the difference between various DBD drivers and their underlying database systems. Currently, there are definitions for:
DBD::mSQLDBD::mysqlDBD::PgDBD::SolidDBD::ODBCDBD::CSVDBD::Oracle(requiresDBD::Oracle0.60 or higher)DBD::SysbaseDBD::InformixDBD::InterBase
DBIx::Recordset has been tested with all those DBD drivers (on Linux 2.0.32, except DBD::ODBC, which has been tested on Windows '95 using Access 7 and with MS SQL Server). 🔥
If you want to use another DBD driver with DBIx::Recordset, it may be necessary to create an entry for that driver. See perldoc DBIx::Compat for more information. 💜
okay bestie, the actual receipts 💖
The following are some examples of how to use DBIx::Recordset. They're pulled straight out of test.pl, and each one shows the DBIx::Recordset call first, followed by the SQL command it generates. iykyk, this is the part where it all clicks. 🔥
Setting up a set, first things first:
*set = DBIx::Recordset-> Setup ({'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table"}) ;That right there sets up a DBIx::Recordset for driver $Driver, database $DB, to access table $Table. slay. 💅
$set -> Select () ;SELECT * from <table> ;$set -> Select ({'id'=>2}) ;is literally the same as
$set1 -> Select ('id=2') ;and both of them give you
SELECT * from <table> WHERE id = 2 ;$set -> Search({ '$fields' => 'id, balance AS paid - total ' }) ;SELECT id, balance AS paid - total FROM <table>$set -> Select ({name => "Second Name\tFirst Name"}) ;SELECT * from <table> WHERE name = 'Second Name' or name = 'First Name' ;$set1 -> Select ({value => "9991 9992\t9993",
'$valuesplit' => ' |\t'}) ;SELECT * from <table> WHERE value = 9991 or value = 9992 or value = 9993 ;$set -> Select ({'+name&value' => "9992"}) ;SELECT * from <table> WHERE name = '9992' or value = 9992 ;$set -> Select ({'+name&value' => "Second Name\t9991"}) ;SELECT * from <table> WHERE (name = 'Second Name' or name = '9991) or
(value = 0 or value = 9991) ;$set -> Search ({id => 1,name => 'First Name',addon => 'Is'}) ;SELECT * from <table> WHERE id = 1 and name = 'First Name' and addon = 'Is' ;okay so now the paging ones, watch this 👀
$set1 -> Search ({'$start'=>0,'$max'=>2, '$order'=>'id'}) or die "not ok ($DBI::errstr)" ;SELECT * from <table> ORDER BY id ;Note: Because of the start and max only records 0,1 will be returned
$set1 -> Search ({'$start'=>0,'$max'=>2, '$next'=>1, '$order'=>'id'}) or die "not ok ($DBI::errstr)" ;SELECT * from <table> ORDER BY id ;Note: Because of the start, max and next only records 2,3 will be returned
$set1 -> Search ({'$start'=>2,'$max'=>1, '$prev'=>1, '$order'=>'id'}) or die "not ok ($DBI::errstr)" ;SELECT * from <table> ORDER BY id ;Note: Because of the start, max and prev only records 0,1,2 will be returned
$set1 -> Search ({'$start'=>5,'$max'=>5, '$next'=>1, '$order'=>'id'}) or die "not ok ($DBI::errstr)" ;SELECT * from <table> ORDER BY id ;Note: Because of the start, max and next only records 5-9 will be returned
Two tables, one vibe check 🦋
*set6 = DBIx::Recordset -> Search ({ '!DataSource' => "dbi:$Driver:$DB",
'!Table' => "t1, t2",
'!TabRelation' => "t1.value=t2.value",
'!Fields' => 'id, name, text',
'id' => "2\t4" }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (id=2 or id=4) and t1.value=t2.value ;$set6 -> Search ({'name' => "Fourth Name" }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (name = 'Fourth Name') and t1.value=t2.value
;$set6 -> Search ({'id' => 3,
'$operator' => '<' }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (id < 3) and t1.value=t2.value ;$set6 -> Search ({'id' => 4,
'name' => 'Second Name',
'*id' => '<',
'*name' => '<>' }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (id<4 and name <> 'Second Name') and t1.value=t2.value ;$set6 -> Search ({'id' => 2,
'name' => 'Fourth Name',
'*id' => '<',
'*name' => '=',
'$conj' => 'or' }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (id<2 or name='Fourth Name') and t1.value=t2.value ;$set6 -> Search ({'+id|addon' => "7\tit",
'name' => 'Fourth Name',
'*id' => '<',
'*addon' => '=',
'*name' => '<>',
'$conj' => 'and' }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (t1.value=t2.value) and ( ((name <> Fourth
Name)) and ( ( id < 7 or addon = 7) or ( id < 0 or addon = 0)))$set6 -> Search ({'+id|addon' => "6\tit",
'name' => 'Fourth Name',
'*id' => '>',
'*addon' => '<>',
'*name' => '=',
'$compconj' => 'and',
'$conj' => 'or' }) or die "not ok ($DBI::errstr)" ;SELECT id, name, text FROM t1, t2 WHERE (t1.value=t2.value) and ( ((name = Fourth
Name)) or ( ( id > 6 and addon <> 6) or ( id > 0 and addon <> 0))) ;*set7 = DBIx::Recordset -> Search ({ '!DataSource' => "dbi:$Driver:$DB",
'!Table' => "t1, t2",
'!TabRelation' => "t1.id=t2.id",
'!Fields' => 'name, typ'}) or die "not ok ($DBI::errstr)" ;SELECT name, typ FROM t1, t2 WHERE t1.id=t2.id ;inserting, updating, deleting — the whole character arc 💀
%h = ('id' => 22,
'name2' => 'sqlinsert id 22',
'value2'=> 1022) ;
*set9 = DBIx::Recordset -> Insert ({%h,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]")}) or die "not ok ($DBI::errstr)" ;INSERT INTO <table> (id, name2, value2) VALUES (22, 'sqlinsert id 22', 1022) ;%h = ('id' => 22,
'name2' => 'sqlinsert id 22u',
'value2'=> 2022) ;
$set9 -> Update (\%h, 'id=22') or die "not ok ($DBI::errstr)" ;UPDATE <table> WHERE id=22 SET id=22, name2='sqlinsert id 22u', value2=2022 ;%h = ('id' => 21,
'name2' => 'sqlinsert id 21u',
'value2'=> 2021) ;
*set10 = DBIx::Recordset -> Update ({%h,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]",
'!PrimKey' => 'id')}) or die "not ok ($DBI::errstr)" ;UPDATE <table> WHERE id=21 SET name2='sqlinsert id 21u', value2=2021 ;%h = ('id' => 21,
'name2' => 'Ready for delete 21u',
'value2'=> 202331) ;
*set11 = DBIx::Recordset -> Delete ({%h,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]",
'!PrimKey' => 'id')}) or die "not ok ($DBI::errstr)" ;DELETE FROM <table> WHERE id = 21 ;and now Execute, the multitasker ✨
*set12 = DBIx::Recordset -> Execute ({'id' => 20,
'*id' => '<',
'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]",
'!PrimKey' => 'id'}) or die "not ok ($DBI::errstr)" ;SELECT * FROM <table> WHERE id<20 ;*set13 = DBIx::Recordset -> Execute ({'=search' => 'ok',
'name' => 'Fourth Name',
'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[0]",
'!PrimKey' => 'id'}) or die "not ok ($DBI::errstr)" ;SELECT * FROM <table> WHERE ((name = Fourth Name))$set12 -> Execute ({'=insert' => 'ok',
'id' => 31,
'name2' => 'insert by exec',
'value2' => 3031,
# Execute should ignore the following params, since it is already setup
'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "quztr",
'!PrimKey' => 'id99'}) or die "not ok ($DBI::errstr)" ;SELECT * FROM <table> ;$set12 -> Execute ({'=update' => 'ok',
'id' => 31,
'name2' => 'update by exec'}) or die "not ok ($DBI::errstr)" ;UPDATE <table> SET name2=update by exec,id=31 WHERE id=31 ;$set12 -> Execute ({'=insert' => 'ok',
'id' => 32,
'name2' => 'insert/upd by exec',
'value2' => 3032}) or die "not ok ($DBI::errstr)" ;INSERT INTO <table> (name2,id,value2) VALUES (insert/upd by exec,32,3032) ;$set12 -> Execute ({'=delete' => 'ok',
'id' => 32,
'name2' => 'ins/update by exec',
'value2' => 3032}) or die "not ok ($DBI::errstr)" ;DELETE FROM <table> WHERE id=32 ;🫶 SUPPORT 🫶
As far as possible for me, support will be available via the DBI Users' mailing list. (dbi-user@fugue.com)
like, come through and ask, bestie, that's what it's there for 💜
👑 AUTHOR 👑
G.Richter (richter@dev.ecos.de)
🌈 SEE ALSO 🌈
Perl(1)DBI(3)DBIx::Compat(3)HTML::Embperl(3)http://perl.apache.org/embperl/Tie::DBI(3)http://stein.cshl.org/~lstein/Tie-DBI/
and that's a wrap on the whole page, no cap 🎀 this one's perl v5.34.0, dated 2022-06-13, filed under Recordset(3pm) — go forth and query, bestie, you've got this 🌸💅✨
📦 where this page comes from
- debian bookworm: libdbix-recordset-perl 0.26-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
- debian trixie: libdbix-recordset-perl 0.26-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
- ubuntu noble: libdbix-recordset-perl 0.26-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
- ubuntu questing: libdbix-recordset-perl 0.26-4
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms