スキップしてメイン コンテンツに移動

項目可変長の縦持ち属性テーブルを属性毎にviewを作成し横持ちにして扱う

ほぼタイトルの通りですが、メインテーブルのマスターレコードに対し、複数の属性レコードで項目が可変長の属性テーブルがあり、これを、横持ちのデータとして取り扱う必要がありました。最初はPivotを使おうかと思いましたが、項目が可変長なので、うまくいきません。そこで、属性種別ごとにviewを用いてマスターレコード単位でに横持ちにし、さらにマスターレコードidを頼りに、複数のviewをinnerjoinで複数結合させたところ、ほぼ目的通りに動作したので、備忘録代わりに挙げてみることにしました。
属性テーブルは一例として以下の様になっています。 
 select * from art_prop where art_id=1;
+-------------+------------------+--------------+---------------+------------------+----------------+-----------+-----------+--------+
| art_prop_id | prop_cat         | prop_name    | prop_dsc1     | prop_dsc2        | prop_dsc3      | prop_dsc4 | prop_dsc5 | art_id |
+-------------+------------------+--------------+---------------+------------------+----------------+-----------+-----------+--------+
|           1 | Basic spec       | vendor       | ASRock        |                  |                |           |           |      1 |
|           2 | Basic spec       | form factor  | Micro ATX     |                  |                |           |           |      1 |
|           3 | Basic spec       | cpu socket   | Socket AM4    |                  |                |           |           |      1 |
|           4 | Basic spec       | chip set     | AMD           | A520             |                |           |           |      1 |
|           5 | Basic spec       | memory type  | DIMM          | DDR4             |                |           |           |      1 |
|           6 | Basic spec       | memory slots |               |                  |                |           | 2         |      1 |
|           7 | Basic spec       | memory max   | 64            | GB               |                |           |           |      1 |
|           8 | Basic spec       | dimension    | 230mm x 201mm |                  |                |           |           |      1 |
|           9 | Expansion Slot   | pcie         | 3.0           | x16              |                |           | 1         |      1 |
|          10 | Expansion Slot   | pcie         | 3.0           | x1               |                |           | 1         |      1 |
|          11 | Serial bus       | USB          | 2.0           | back panel       | Type A         |           | 2         |      1 |
|          12 | Serial bus       | USB          | 2.0           | header           |                |           | 2         |      1 |
|          13 | Serial bus       | USB          | 3.2 Gen1      | back panel       | Type A         |           | 4         |      1 |
|          14 | Serial bus       | USB          | 3.2 Gen1      | header           |                |           | 1         |      1 |
|          15 | On board graphic | D-Sub        | analog        | 1920x1200@60Hz   |                |           | 1         |      1 |
|          16 | On board graphic | HDMI         | HDMI 2.1      | 4096x2160@60Hz   |                |           | 1         |      1 |
|          17 | On board graphic | DVI-D        | digital       | 1920x1200@60Hz   |                |           | 1         |      1 |
|          18 | On board audio   | audio chip   | ALC887        |                  |                |           | 1         |      1 |
|          19 | Storage          | sata         | sata3         |                  |                |           | 4         |      1 |
|          20 | Storage          | M.2          | M Key         | sata3/pcie3.0 x4 | 2242/2260/2280 |           | 1         |      1 |
|          21 | Network          | wired lan    | 1000BASE-T    | RTL8111H         |                |           | 1         |      1 |
|          22 | Security device  | TPM          | header        |                  |                |           | 1         |      1 |
|          23 | Security device  | TPM          | fTPM          |                  |                |           | 1         |      1 |
|          24 | Serial Port      | PS/2         | back panel    |                  |                |           | 1         |      1 |
|          25 | Serial Port      | COM          | header        |                  |                |           | 1         |      1 |
+-------------+------------------+--------------+---------------+------------------+----------------+-----------+-----------+--------+
フィールド名でいうとart_idがメインテーブルのレコードidです。可変項目にしたのは、属性がいろいろとかわり、横持ちだとメンテが面倒だとおもったからですが、DB設計が悪いといわれればそれまでなんですが...基、さて、これをたとえば以下の様にしたいとします。 
+-----------------+----------------+----------+--------+------------+-------------+-------------+-------------+
| name            | chipset vendor | chipset  | vendor | cpu socket | form factor | memory form | memory type |
+-----------------+----------------+----------+--------+------------+-------------+-------------+-------------+
| A520M-HDV       | AMD            | A520     | ASRock | Socket AM4 | Micro ATX   | DIMM        | DDR4        |
| A320M-HDV R4.0  | AMD            | A320     | ASRock | Socket AM4 | Micro ATX   | DIMM        | DDR4        |
| A520M Pro4      | AMD            | A520     | ASRock | Socket AM4 | Micro ATX   | DIMM        | DDR4        |
| B450M-HDV R4.0  | AMD            | B450     | ASRock | Socket AM4 | Micro ATX   | DIMM        | DDR4        |
| H310CM-HDV/M.2  | Intel          | H310     | ASRock | LGA1151    | Micro ATX   | DIMM        | DDR4        |
| PRO Z690-A WIFI | Intel          | Z690     | MSI    | LGA1700    | ATX         | DIMM        | DDR5        |
+-----------------+----------------+----------+--------+------------+-------------+-------------+-------------+
答えからいうと、この表は以下のようなSQLで作成しました。 
SELECT
	v1.art_name		AS "name",
	v1.`chipset_vendor`	AS "chipset vendor",
	v1.`chipset`		AS "chipset",
	v2.`vendor`		AS "vendor",
	v3.`cpu_socket`		AS "cpu socket",
	v4.`form_factor`	AS "form factor",
	v5.`memory_form`	AS "memory form",
	v5.`memory_type`	AS "memory type"
FROM v1 
INNER JOIN v2 ON (v1.art_id = v2.art_id)
INNER JOIN v3 ON (v1.art_id = v3.art_id)
INNER JOIN v4 ON (v1.art_id = v4.art_id)
INNER JOIN v5 ON (v1.art_id = v5.art_id)
vXが、viewでそれぞれの属性ごとに作成したもですが、それぞれのviewは以下の様に作成しました。 
drop view if exists v1;
create view v1 as
SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as chipset_vendor, `art_prop`.`prop_dsc2` as chipset 
FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) 
WHERE prop_name="chipset";

drop view if exists v2;
create view v2 as
SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as vendor
FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) 
WHERE prop_name="vendor";

drop view if exists v3;
create view v3 as
SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as cpu_socket
FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) 
WHERE prop_name="cpu socket";

drop view if exists v4;
create view v4 as
SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as form_factor
FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) 
WHERE prop_name="form factor";

drop view if exists v5;
create view v5 as
SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as memory_form, `art_prop`.`prop_dsc2` as memory_type
FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) 
WHERE prop_name="memory type";

drop view if exists v6;
create view v6 as
SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as max, `art_prop`.`prop_dsc2` as unit
FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) 
WHERE prop_name="memory max";
みていただければわかると思いますが、今回はマザーボードのデータベースを作成しています。なぜこのような縦持ちのへんてこな属性レコードにしたのかは聞かないでください(笑) 基、理由はたとえば、USB4やThunder Bolt4、DDR6などがこれから出てくるし、これから先どんな規格のものが出てくるかわからないためです。また、拡張スロットにしても、PCIEだけでなく、マザーボードによってはPCIバスがあるものや、M.KeyではないM.2があり、さらにUSB3.2にしてもGen1なのかGen2なのか、TYPE AなのかTYPE Cなのか、また、StorageのNVMe　SSDにしても属性がバラバラなため、横持ちは無理だと思ったからでもあります。もちろんマスターレコードの数が比較的にすくないというのもありますが。。。
もっとも横持ちの様に扱えるかどうかが問題だったのですが、上述の様にすることで、おおよその仕様要求はみたせたので、よしとしています。
今回は以上です。それでは。

コメント

コメントを投稿

このブログの人気の投稿

wsdd を使ってSamba サーバをネットワークに表示

Windows 10のアップデートで、セキュリティー対応のため、smbv1がデフォルトではインストールされなくなり、Samba serverがエクスプローラーのネットワークに表示されなくなってしまいました。そこで、いくつか方法を調べたのですが、linuxでwsdの実装がないか探したところ、 https://github.com/christgau/wsdd が、見つかりましたので、さっそくインストールしてみました。まだパッケージにはないようですが、インストール自身は簡単です。wsdd自体は以下のように取得し、linkを張っておきます。 cd /usr/local/bin/ sudo wget https://raw.githubusercontent.com/christgau/wsdd/master/src/wsdd.py sudo chmod 755 wsdd.py sudo ln -sf wsdd.py wsdd こちらのsambaサーバはDebianなので、/etc/systemd/system/wsdd.serviceは以下のようにしました。 [Unit] Description=Web Services Dynamic Discovery host daemon Requires=network-online.target After=network.target network-online.target multi-user.target [Service] Type=simple ExecStart=/usr/local/bin/wsdd -d MYDOMAIN [Install] WantedBy=multi-user.target wsdd -d MYDOMAINのところを、環境にあわせて書き換えてください。 次に、systemdに登録・起動テストを行います。 systemctl enable wsdd systemctl start wsdd 起動に成功すると、エクスプローラーのネットワークに表示されます。  なおこのwsddはpython3が必要です。一度試してみてください。SMBv1/CIFSを停止していても、大丈夫です。 cで書かれたほかのwsddの実装もあるようなので、いずれパッケージになるかもしれませ
コメントを投稿
続きを読む

Hyper-V Server2019にワークグループ環境下でWindows10(1809)から接続

Hyper-V server 2019に、ワークグループ環境にてWindows10(1809)から接続してみました。Windows10にHyper-V管理ツールがインストールされていることと、Hyper-V Serverをインストール済であることが前提です。以下、Hyper-V serverは名前がHyperVSV、アドレスは192.168.1.110としています。 まず、Hyper-V server上で、powershellを起動し、以下のコマンドを入力します。 Enable-WSManCredSSP -Role Server -Force 続いて、クライアントのWindows10のpowershell で以下のコマンドを入力します。 winrm quickconfig -Force Enable-WSManCredSSP -Role Client -DelegateComputer * -Force さらに、クライアントマシンで、gpedit(グループポリシーエディタ)を起動し、以下の要領でポリシーを設定します。 a. [コンピューターの構成]->[管理テンプレート]->[システム]->[資格情報の委任]->[NTLMのみのサーバー認証で新しい資格情報の委任を許可する] を有効にし、サーバを一覧に追加[表示...]ボタンをクリックして、「WSMAN/*」を追加 b. [コンピューターの構成]->[管理テンプレート]->[システム]->[資格情報の委任]->[NTLM のみのサーバー認証で保存された資格情報の委任を許可する] を有効にし、サーバを一覧に追加[表示...]ボタンをクリックして、「*」を追加 また、名前解決できるように、（notepadを管理者権限で実行し）C:\Windows\System32\Drivers\etc\hostsにサーバ名とIPアドレスの対を追加。 192.168.1.110 HyperVSV 最後に、Hyper-Vマネージャーを起動し、Windows10からHyper-V サーバに接続します。手順は以下の通りです。 「サーバーに接続」->コンピュータの選択->別のコンピューターに[HyperVSV]と入力し、[別のユーザーとして接続する
コメントを投稿
続きを読む

フレッツ光クロス：MAP-E ROUTER by Debian Box (iptables)

フレッツ光クロスがようやく開通したので、Debianにてrouterを構成し接続してみました。なお、プロバイダーを選ぶにあたっては、IPoE方式がそれぞれ異なるため検討したところ、IPoEでは、MAP-Eでもv6plusとocnバーチャルコネクトがあり、前者がポート数240なのに対し、後者は約４倍のポート数が使えるようなネットの情報をみて、OCNバーチャルコネクトを選択しました。（プロバイダーとしてはぷららです。なおDS-LiteはCE側でのNATではないので今回は見送りました。）そこで、OCN バーチャルコネクトをDebian(iptables)で実現するとどうなるかと思い、ネットの情報を頼りにしつつ、設定した次第です。 実際に試した結果、とりあえず通信できていますが、MAP-Eは本来マッピングルールをマップサーバから取得するはずなので、今回のやり方が正解とはいえませんし、仕様変更されると通信できなくなる可能性があります。あくまでも参考程度ですが、本稿をUPしてみました。 2023/03/16追記： こちら にゲームコンソールNAT越え(Nintendo Switch ナットタイプ A判定)対応版を投稿しました。 2023/03/28追記：※1の記述および3行無効化によりNAT越え(Nintendo Switch ナットタイプ B判定)できるようになりました。 構成は以下の通りです。 ルーターがDebianで回線がOCNバーチャルコネクトであること以外はなにも特別なところはない構成です。 さて、いきなり設定ですが、まず、割り当てられたプレフィックスを確認します。 確認は、 dhclient -6 -d -P enp2s0 とします。出力の中に 前略 RCV: | | X-- IAPREFIX 2400:4050:5c71:af00::/56 後略 このようにプレフィックスが表示されるので、その確認したプレフィックスを書き留めておきます。これを こちらで 入力します。すると、 CE: 2400:4050:5c71:af00:99:f171:c600:2f00 IPv4 アドレス: 153.241.113.198 ポート番号：(1776-1791 2800-2815 3824-3839) 4848-4863 5872-5887 6896-
コメントを投稿
続きを読む