Examine table and column information

DBIx::Custom
here

I will introduce a method to check the information of "table" and "column" of DBIx::Custom.

Check the information of all tables in order each_table

Use the each_table method to look up the information in all the tables in order.

$dbi->each_table(
  sub {
    my ($dbi, $table, $table_info) = @_;
    
    my $table_name = $table_info->{TABLE_NAME};
  }
);

Follows the information in all the tables in the database in order. In the argument, specify the callback function that is actually tableed for each table. The callback function takes three arguments:

DBIx::Custom object
table name
Table information

Speeding up each_table

The each_table method is slow. It's pretty slow, especially if you have a large number of system tables in your database.

If user_table_info is set, each_table will use this as table information. You can speed up each_table as follows:

my $table_infos = $dbi->get_table_info(exclude => qr / ^ system_ /);
$dbi->user_table_info($table_info);
$dbi->each_table(sub {...});

Get table information get_table_info

Use the get_table_info method to get the table information.

my $table_infos = $dbi->get_table_info(exclude => qr / ^ system_ /);

With the exclude option, you can specify the table name to exclude with a regular expression.

Set database table information --user_table_info

Normally, the each_table method is used to get the table information of the database, but in the case of a database that holds a large number of system tables, it is very slow to get the table information every time it is accessed. Become.

The user_table_info method allows you to set the table information so that each_table can use the table information.

$dbi->user_table_info($user_table_info)

The each_table method is very fast.

The data structure of the information to be set is as follows.

[
  {table =>'book', info => {...}},
  {table =>'author', info => {...}}
]

Normally, the information acquired in advance by get_table_info is set in user_table_info.

my $user_table_info = $dbi->get_table_info(exclude => qr / ^ system /);
$dbi->user_table_info($user_table_info);

Get column information --get_column_info

Use the get_column_info method to get the column information.

my $column_infos = $dbi->get_column_info(exclude_table => qr / ^ system_ /);

With the exclude_table option, you can specify the table name to exclude with a regular expression.

Examine all column information in order --each_column

Use the each_column method to look up the information in all columns in order.

$dbi->each_column(
  sub {
    my ($dbi, $table, $column, $column_info) = @_;
    
    my $type = $column_info->{TYPE_NAME};
    
    if ($type eq'DATE') {
      # ...
    }
  }
);

Follows the information in all columns in the database in order. The argument specifies the callback function to be executed for each column. The callback function takes four arguments:

DBIx::Custom object
table name
Column name
Column information

Speeding up each_column

The each_column method is slow. It's pretty slow, especially if you have a large number of system columns in your database.

If user_column_info is set, each_column will use this as column information. You can speed up each_column as follows:

my $column_infos = $dbi->get_column_info(exclude_table => qr / ^ system_ /);
$dbi->user_column_info($column_info);
$dbi->each_column(sub {...});

This method also speeds up setup_model and type_rule, which use each_column internally.

Set database column information --user_column_info

Normally, the each_column method is used to get the column information of the database, but in the case of a database that holds a large number of system tables, it is very slow to get the column information every time it is accessed. ..

You can use the user_column_info method to set the column information and make this column information available to each_column.

$dbi->user_column_info($user_column_info)

The each_column method is very fast, and the methods that use each_column internally, such as type_rule and setup_model, are also faster.

The data structure of the information to be set is as follows.

[
  {table =>'book', column =>'title', info => {...}},
  {table =>'author', column =>'name', info => {...}}
]

Normally, the information acquired in advance by get_column_info is set in user_column_info.

my $user_column_info = $dbi->get_column_info(exclude_table => qr / ^ system /);
$dbi->user_column_info($user_column_info);

Specify a table to display the type names of all columns show_typename

Use the show_typename method to specify a table to display the type names of all columns.

$dbi->show_typename($table);

The table name is displayed in the first row, and the column name and type name are displayed in the second and subsequent rows.

book
title: varchar
issue_date: date

This type name can be used in into1 and into2 of type_rule.

Show data types for all columns in the specified table --show_datatype

Use the show_datatype method to display the data types of all columns in the specified table.

$dbi->show_datatype($table);

The table name is displayed in the first row, and the column name and data type are displayed in the second and subsequent rows.

book
title: 5
issue_date: 91

This data type is available in type_rule from1 and from2.

Get list of type names available_typename

Use the available_typename method to get a list of the type names available in the database.

print $dbi->available_typename;

Note that the type name roughly matches the type you specify when defining the database table, but not exactly.

Use the show_typename method to find out the correct type name.

Get a list of data types available_datatype

Use the available_datatype method to get a list of the data types available in the database.

print $dbi->available_datatype;

The data types enumerated by this method are obtained by the following logic.

for my $i (-1000 .. 1000) {
  $dbh->type_info($i);
  my $data_type = $type_info->{DATA_TYPE};
}

Note that it is similar to the value you can get with the TYPE attribute of the statement handle, but it doesn't seem to match exactly. Also, SQLite doesn't list anything.

$sth->{TYPE}

Use the show_datatype method to find out the correct data type.

Data types available in PostgreSQL

DataType available in PostgreSQL that can be obtained by DBI.

Data Type (Type name)
-3 (bytea)
0 (unknown)

1 (bpchar)

2 (numeric)

3 (numeric)

4 (int4)

5 (int2)

6 (float4)

7 (float8)

8 (int8)

9 (date) # This may be wrong

10 (tinterval)

11 (timestamp)

12 (text)

16 (bool)

50 (array)

91 (date) #date thinks this is correct.

92 (time)

93 (timestamp)

94 (timetz)

95 (timestamptz)

Data types available in Microsoft SQL Server 2008 R2

A list of data types returned by Microsoft SQL Server 2008 R2 that can be retrieved by DBI. The value of available_datatype. It is when accessed via DBD::ODBC.

Data Type (Type name)
-150 (sql_variant)
-11 (unique identifier)
-10 (ntext)
-9 (nvarchar)
-8 (nchar)
-7 (bit)
-6 (tinyint)
-5 (bigint)
-4 (image)
-3 (varbinary)
-2 (binary)
-1 (text)
-150 (sql_variant)
1 (char)
2 (numeric)
3 (decimal)
4 (int)
5 (smallint)
6 (float)
7 (real)
12 (varchar)
93 (datetime)

I can't find the date type, but in SQL Server, the Date type seems to be returned as -9 (nvarchar). In the case of date type, in the case of SQL server, it seems that automatic judgment cannot be performed.

Exclude a specific table from the table to be searched --exclude_table

DBIx::Custom has a method to retrieve a table in the database. If you want to exclude a specific table from the search target, use exclude_table .

$dbi->exclude_table(qr / pg_ /);

The value specifies a regular expression reference.

The four methods each_column, each_table, type_rule, setup_model go to get the table information of the database, but exclude the table that matches the regular expression specified by exclude_table from the search target.

Microsoft SQL Server and Oracle have a large number of system tables internally, so if set up properly, performance will improve.

DBIx::Custom