statement handle

A statement handle is a data object that describes on SQL statement and tracks its execution. You can execute a statement only by allocating a statement handle.

SQLAllocHandle() (with HandleType set to SQL_HANDLE_STMT) allocates a statement handle to describe an SQL statement. Descriptions of SQL statements include information such as statement attributes, SQL statement text, dynamic parameters, cursor information, dynamic arguments and column bindings, result values, and status information. Each statement handle associates a connection with statement that the handle describes.

What is bind?

Bind variables are variables used in SQL statements, which are languages that perform writing and query to a database.

When making similar processing requests repeatedly while changing only a certain number to the database, if you re-set the number of the SQL statement each time, many types of SQL statements will be processed, which is inefficient. In such a case, if the bind variable is used, the database recognizes that “the same SQL statement is repeated processed under another condition”, so that the processing time can be efficiently improved and the processing time can be shortened.

In the case of Oracle Database, bind variables are represented by adding a colon(:) to the beginning of the variable name(e.g :number)


$sql = ‘select name, breed, weight from animals where weight > ? and weight < ?'; $conn = db2_connect($database, $user, $password); $stmt = db2_prepare($conn, $sql); $lower_limit = 1; db2_bind_param($stmt, 1, "lower_limit", DB2_PARAM_IN); db2_bind_param($stmt, 2, "upper_limit", DB2_PARAM_IN); $upper_limit = 15.0; if(db2_execute($stmt)){ while ($row = db2_fetch_array($stmt)){ print "{$row[0]}, {$row[1]}, {$row[2]}\n"; } } [/php]

Database Handles

Database handle are fully encapsulated objects. Transactions from one database handle do not cross over or leak with one another.

$rc = $dbh->do($statement) || die $dbh->errstr;
$rc = $dbh->do($statement, \%attr) ||die $dbh->errstr;

$rv = $dbh->do($statement, \%attr,@bind_values) || ...

my $rows =$dbh->do(q{
	DELETE FROM table
},undef,'DONE') || die $dbh->errstr;

$art_ref =$dbh->selectall_arrayref($statement);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);

$sth = $dbh->prepare($statement) || die $dbh->errstr;
$sth = $dbh->prepare($statement, \%attr) ||die $dbh->errstr;

my $sth =$dbh->@prepare("SELECT * from mytable where name like ?");
$sth->bind_param(1, 'J%');

$sth = $dbh->prepare_cached($statement) || die $dbh->errstr;
$sth = $dbh->prepare_cached($statement, \%attr) || die $dbh->errstr;

すげー、相当頑張らねーと。。。

Window function

The window function performs calculations across a set of table rows that are somehow related to the current row. This is similar to the form of calculation performed by aggregate functions. However, unlike regular aggregation functions, the use of window functions does not group rows into a single output row. Each row maintains its own identity.

Windows functions perform aggregation processing(ranking, etc) on acquired(SELECT) data. It can be said that it is an essential function in the recent trend of big data and OLAP.

SELECT
  Start_station_name,
  Duration,
  SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
 FROM
  trip_histories
 WHERE
  Start_time < '2019-01-30'
 LIMIT 10;

SELECT
 Start_station_name,
 Duration,
 SUM(Duration) OVER (PARTITION BY Start_station_name) AS running_total,
 COUNT(Duration) OVER (PARTITION BY Start_station_name) AS running_count,
 AVG(Duration) OVER (PARTITION BY Start_station_name) AS running_avg
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

SELECT
 ROW_NUMBER() OVER (order by Start_time DESC) AS row_num,
 Start_station_name,
 Start_time,
 Duration
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

なるほどー、これは数こなさないとな~

What is CTE?

What is CTE?
– CTE is a so-called “recursive query”
– The WOTH clause has become available
 → Implemented on other RDMS(PostgreSQL, Oracle etc)
– Hierarchical queries can be written very easily
 → In the past, it had to be nested using subueries.

WITH RECURSIVE Hatoyama AS
(
 select id, last_name, first_name from family
   where last_name= "鳩山" and first_name="和夫"
 union all
   select child.id, child.last_name, child.first_name
   from family as child, Hatoyama
    where Hatoyama.id = child.parent_id
)
select * from Hatoyama;

WITH Yoshida as
(
	select id, last_name, first_name from family
	  where last_name="吉田" and parent id is null
	union all
	  select child.id, child.last_name, child.first_name
	  from family as child, Yoshida
	  where yoshida.id= child.parent_id
)
select * from Yoshida;

select文が階層化しないようにできるわけね。

mysqlのアップグレード

[vagrant@localhost ~]$ sudo yum remove -y mysql-community-release-el7-5.noarch
読み込んだプラグイン:fastestmirror
削除処理の設定をしています
引数に一致しません: mysql-community-release-el7-5.noarch
Determining fastest mirrors
* base: ftp.yz.yamagata-u.ac.jp
* extras: ftp.yz.yamagata-u.ac.jp
* remi-safe: mirror.xeonbd.com
* updates: mirror.vodien.com
base | 3.7 kB 00:00
extras | 3.4 kB 00:00
extras/primary_db | 29 kB 00:00
jenkins | 2.9 kB 00:00
jenkins/primary_db | 127 kB 00:01
mariadb | 2.9 kB 00:00
mysql-connectors-community | 2.5 kB 00:00
mysql-tools-community | 2.5 kB 00:00
mysql56-community | 2.5 kB 00:00
nginx | 2.9 kB 00:00
nodesource | 2.5 kB 00:00
remi-safe | 3.0 kB 00:00
remi-safe/primary_db | 1.2 MB 00:02
updates | 3.4 kB 00:00
updates/primary_db | 3.7 MB 00:08

なんかものすごく怖いな。。

[vagrant@localhost ~]$ sudo yum localinstall -y http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
読み込んだプラグイン:fastestmirror
ローカルパッケージ処理の設定をしています
mysql57-community-release-el7-7.noarch.rpm | 8.8 kB 00:00
/var/tmp/yum-root-w9oAJm/mysql57-community-release-el7-7.noarch.rpm を調べています: mysql57-community-release-el7-7.noarch
/var/tmp/yum-root-w9oAJm/mysql57-community-release-el7-7.noarch.rpm をインストール済みとして設定しています
Loading mirror speeds from cached hostfile
* base: ftp.yz.yamagata-u.ac.jp
* extras: ftp.yz.yamagata-u.ac.jp
* remi-safe: mirror.xeonbd.com
* updates: mirror.vodien.com
依存性の解決をしています
–> トランザクションの確認を実行しています。
—> Package mysql57-community-release.noarch 0:el7-7 will be インストール
–> 衝突を処理しています: mysql57-community-release-el7-7.noarch は mysql-community-release と衝突しています
No package matched to upgrade: mysql57-community-release
–> 依存性解決を終了しました。
エラー: mysql57-community-release conflicts with mysql-community-release-el6-5.noarch
問題を回避するために –skip-broken を用いることができません
これらを試行できます: rpm -Va –nofiles –nodigest

なんだ。。

[vagrant@localhost ~]$ sudo yum swap -y — remove mysql-community-release — install http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
読み込んだプラグイン:fastestmirror
コマンド「swap」が見つかりません。「/usr/bin/yum –help」を実行してください。

あああああああ、ちょっとあんまりやりたくないなー

MySQL 8.0 Features

Document Store: Both relational and NoSQL applications can be developed in one DB.
Transactional data directory: implemented as a series of SQL tables stored in a single InnoDB tablespace.
SQL Role: Allows you to grant / remove privileges to groups of users, significantly reducing the burden of privilege management.
Default character set is utf8mb4: richer mobile applications and character set internalization
CTE(Common Table Expressions): Add WITH clause
Window function: Reduce code complexity and improve development productivity
Invisible Index: Better management of application upgrades and database changes running 24/7
Descending Index: Improves performance by eliminating the need to sort descending results
Json support: Add many functions such as JSON_TABLE function that receives JSON data and returns it as a relational table
Up to 2x faster: New benchmarks record up to 1.8 milion queries per second
OpenSSL: MySQL default TLS/SSL library
Performance schema: query execution time up to 30 times faster
Information schema: Query execution time is up to 100 times faster
GIS: supports SRS(Spatial Reference Systems)
Replication and InnoDB Cluster: Improve Scale Out of Large applilcations.

現状のバージョンを見てみると…

[vagrant@localhost ~]$ rpm -qa | grep -i mysql
php-mysqlnd-7.1.21-1.el6.remi.x86_64
mysql-community-common-5.6.41-2.el6.x86_64
mysql-community-devel-5.6.41-2.el6.x86_64
mysql-community-libs-5.6.41-2.el6.x86_64
mysql-connector-python-2.1.7-1.el6.x86_64
mysql-community-libs-compat-5.6.41-2.el6.x86_64
mysql-community-release-el6-5.noarch
mysql-community-client-5.6.41-2.el6.x86_64
mysql-utilities-1.6.5-1.el6.noarch
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-community-server-5.6.41-2.el6.x86_64
[vagrant@localhost ~]$ mysql –version
mysql Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using EditLine wrapper
[vagrant@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> Ctrl-C — exit!
5.7か~

mysqlのアップグレードには
・mysql_upgradeを用いたアップグレード(インプレースアップグレード)
・レプリケーションを用いたアップグレード

CHARACTER_OCTET_LENGTH

CHARACTER_OCTET_LENGTH must be the same as CHARACTER_MAXIMUM_LENGTH except for multibyte character set.

mysql> select * from `information_schema`.`columns` where `table_schema` = “test”;
+—————+————–+————+————-+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————–+————+—————-+———————————+—————-+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+—————+————–+————+————-+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————–+————+—————-+———————————+—————-+
| def | test | items | item_id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | test | items | name | 2 | NULL | YES | text | 65535 | 65535 | NULL | NULL | NULL | utf8 | utf8_general_ci | text | | | select,insert,update,references | |
| def | test | items | price | 3 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | | | select,insert,update,references | |
| def | test | name | name | 1 | NULL | YES | varchar | 255 | 765 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(255) | | | select,insert,update,references | |
| def | test | name | password | 2 | NULL | YES | varchar | 255 | 765 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(255) | | | select,insert,update,references | |
| def | test | news | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | test | news | content | 2 | NULL | YES | varchar | 100 | 300 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(100) | | | select,insert,update,references | |
| def | test | user | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | MUL | auto_increment | select,insert,update,references | |
| def | test | user | name | 2 | NULL | YES | varchar | 20 | 60 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(20) | | | select,insert,update,references | |
+—————+————–+————+————-+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————–+————+—————-+———————————+—————-+
9 rows in set (0.15 sec)

なんじゃーこりゃーーー

Config::Simple

Config::Simple – 簡単な設定ファイルクラス

use Config::Simple;
Config::Simple->import_from(‘app.ini’, \%Config);
$cfg = new Config::Simple(‘app.ini’);
$user = $cfg->param(‘User’);
%Config = $cfg->vars();
$cfg->param(‘User’, ‘sherzodR’);
$cfg->param(‘Users’, [‘sherzodR’, ‘geek’, ‘merlyn’]);
$cfg->param(-block=>’last-access’, -value=>{‘time’=>time()});
$mysql = $cfg->param(-block=>’mysql’);
$cfg->save();

mysqldumpでテーブルをバックアップ

command

$ mysqldump -u root -p db table --single-transaction > hoge.dump

test

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| items          |
| name           |
| news           |
| user           |
+----------------+
4 rows in set (0.00 sec)

[vagrant@localhost ~]$ mysqldump –single-transaction -u root -p test items > /tmp/dump/items.sql
Enter password:

ほーーーーーーー

複数テーブルの場合
[vagrant@localhost ~]$ mysqldump –single-transaction -u root -p test items name > /tmp/dump/some.dump
Enter password:

テーブルを複数並べればいいだけ。

オプション
–skip-lock-tables
–lock-tablesオプションを無効にする。–optが–lock-tablesを有効にするので、それを打ち消す為に使用する。

なるほどーーーーーー