Generate SQL

DBIx::Custom
here

Introducing the method to "generate SQL" with DBIx::Custom.

Easily generate column names for use in select statements --column

Use the column method to easily generate the column name part used in the select statement.

my $column = $dbi->column(book => [qw / author title /]);

A column name similar to the following is generated.

"book". "author" as "book.author", "book". "title" as "book.title"

The column method helps to write less duplication.

You can also change the delimiter used in the column name alias.

$dbi->separator('-');

If you change the delimiter to "-", the following column name will be generated.

"book". "author" as "book-author", "book". "title" as "book-title"

Use column in select method

You can use the column method in the column option of the select method.

my $result = $dbi->select(
  table =>'book',
  column => [
    $dbi->column(book => [qw / title author /])
  ]
);

However, it is troublesome to write a column, so using a hash reference has the same meaning as using the column method, so it is better to use this.

my $result = $dbi->select(
  table =>'book',
  column => [
    {book => [qw / title author /]}
  ]
);

Generate unqualified column name for table name --mycolumn

The mycolumn method makes it easy to generate unqualified column names for table names.

my $mycolumn = $dbi->mycolumn(book => ['author','title']);

A column name similar to the following is generated.

book.author as author,
book.title as title

This is supposed to be used in the column option of the select method.

$dbi->select(column => [
  $dbi->mycolumn(book => ['author','title'])
]);

Dynamically generate the values part in the insert statement --values_clause

Use the values_clause method to dynamically generate the values part of the insert.

my $values_clause = $dbi->values_clause({title =>'a', age => 2});

This will be a string like this:

(title, author) values (title =: title, age =: age);

It can be used to create an insert statement.

my $insert_sql = "insert into book $values_clause";

Dynamically generate the part to assign the value in the update statement --assign_clause

Use the assign_clause method to dynamically generate the part to which the value is assigned in the update statement.

my $param = {id => 1, title =>'Perl'};
my $assign_clause = $dbi->assign_clause($param);

It will be expanded as follows.

id =: id, title =: title

You can create an update statement as follows:

my $update_sql = "update book set $assign_clause";

The SQL created in this way can be executed by the execute method.

$dbi->execute($update_sql, $param);

Change quotes for reserved words --quote

Use the quote method to change the quotes for reserved words.

$dbi->quote('"');
$dbi->quote('[]');

Quoting can be set with a single character or as a pair.

The default value of quote is set automatically according to the database, so it is not usually necessary to set it.

[Database] [value of quote]
MySQL `

Connection via ODBC
(Assuming Microsoft SQL Server and Access) []

other than that                              "

Table names and column names are automatically quoted using the quote value.

For example, in MySQL, select like below

$dbi->select(
  table =>'book',
  column => [
    {book => ['author']}
  ]
);

When you execute, the following SQL is executed.

select book.author as `book.author` from` book`;

Change table name and column name delimiter --separator / DBIx::Custom

Use the separator method to change the table and column name delimiters.

$dbi->separator('-');

The default delimiter is ".".

Impact on column method

The character specified in separator is used as an alias for the column name in the string returned by the column method.

In the case of column method, it will be as follows.

my $column = $dbi->column(book => [qw / title author /]);

The alias of the column name included in the return string will be something like "table name-column name".

"book". "title" as "book-title", "book". "author" as "book-author"

Impact on column option of select method

Even when hash reference is used in the column option of select method, the character specified in separator is used as the delimiter between table name and column name.

my $result = $dbi->select(
  table =>'book',
  column => [
    {book => [qw / title author /]}
  ]
);

The following SQL is executed:

select "book". "title" as "book-title", "book". "author" as "book-author" from "book"

When separator is useful

For example, it is very useful for embedding data obtained from a database in HTML. When working with the DOM, jQuery has become the de facto standard. However, with the default "." Delimiter, jQuery will not be able to handle the string correctly unless you escape the dots.

If you change the delimiter to "-", you can handle it as it is, so you can use it in an affinity with HTML.

generate a value for the like operator like_value

I think that the value when using the like operator is usually sandwiched between "%" on the left and right like "%Perl%". like_value is a function (code reference) that performs conversion to add%to the left and right.

my $like_value = $dbi->like_value->("Perl");

In this example you can get the string "%Perl%".

Registration of a subroutine that generates the current time

The subroutine that generates the current time can be set with the now attribute.

$dbi->now(
  sub {
    my ($sec, $min, $hour, $mday, $mon, $year) = localtime;
    $mon ++;
    $year + = 1900;
    return sprintf("%04d /%02d /%02d%02d:%02d:%02d");
  }
);

By default, a subroutine that returns the current time in the format "2011-10-14 05:05:27" is registered.

You can also use a database function to get the current time, in which case you can specify it in the scalar reference.

$dbi->now(\ "now ()");

This current time is used by the ctime option and mtime option of the insert method and the mtime option of the update method.

DBIx::Custom