- 投稿日:2020-02-25T23:30:47+09:00
tblsでデータベースのスキーマ情報をマークダウンで出力する
概要
DBのスキーマ情報をドキュメント化したいと思ったので使用したツールをメモします。
使用方法
tblsのgithubを参考。
dockerイメージの入手
$ docker pull k1low/tbls:latest
1コマンドでデータベースをドキュメント化できる手軽さ。
$ tbls doc postgres://dbuser:dbpass@hostname:5432/dbname
dockerでやる場合は以下です。
$ docker run --rm -v $PWD:/work k1low/tbls doc postgres://dbuser:dbpass@hostname:5432/dbname筆者の場合は、mysqlなので、以下です。
$ docker run --rm -v $PWD:/work k1low/tbls doc mysql://dbuser:dbpass@hostname:3306/dbname
- 投稿日:2020-02-25T18:47:15+09:00
データベースにおける『ロック』ってなんだ?
データベースにおける
ロック
ってなんだ?READ LOCK と WRITE LOCK という2つのロックがある。
ロック種別 ロックを取ったスレッド その他のスレッド READ READのみ可、WRITEするとエラー READのみ可能。WRITEするとブロック WRITE READ/WRITE可能 READ/WRITE不可。ブロックされる。 READ LOCKを試した(MySQL)
- スレッドAでREAD LOCK
LOCK TABLES user READ;
- スレッドBでREAD/WRITEしてみる
mysql> select * from user; +------+----------+-------+----------+ | id | username | email | password | +------+----------+-------+----------+ | 1 | hoge | hoge | hoge |mysql> INSERT INTO user VALUES ('1','hoge','hoge','hoge'); # 待機
- スレッドAでREAD LOCKを解除
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
- スレッドBのWRITEが実行される
mysql> INSERT INTO user VALUES ('1','hoge','hoge','hoge'); Query OK, 1 row affected (39.20 sec)WRITE LOCKを試した(MySQL)
- スレッドAでWRITE LOCK
mysql> LOCK TABLES user WRITE; Query OK, 0 rows affected (0.00 sec)
- スレッドBでREAD
mysql> select * from user; # 待機
- スレッドAでWRITE LOCKを解除
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
- スレッドBのREADが実行される
mysql> select * from user; ^[[A+------+----------+-------+----------+ | id | username | email | password | +------+----------+-------+----------+ | 1 | hoge | hoge | hoge | ・・・ 5 rows in set (48.68 sec)スレッド
show processlist;
でスレッドの情報を確認すると、当該スレッドがロックされていることが確認できる。
- ロックされているスレッドが多いということは、テーブルロックを長時間に渡って持ったままになる処理がある可能性がある。
mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+--------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+--------------------+ | 2 | root | localhost | foo | Sleep | 22 | | NULL | | 3 | root | localhost | foo | Query | 20 | Waiting for table metadata lock | select * from user | | 6 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+---------------------------------+--------------------+ 3 rows in set (0.00 sec)「グローバルな読み取りロック」とは?
FLUSH TABLES WITH READ LOCK
により データベース全体 のREAD LOCKを取得する。
- 投稿日:2020-02-25T16:17:13+09:00
Django:MigrationでモデルがDBに反映されない
反映されない
表題の通り、マイグレーションしてもDBに反映されず苦しんだので記録しておきます。
環境
・Windows10 Enterprise
・Docker Desktop for Windows:2.1.0.1
・MySQL:5.7.28(Docker上)
・Django:3.0(Docker上)経緯
models.pyに新モデル(モデルAとします)を追加してマイグレーションを行った所、DBに反映されず。
他モデルの変更は反映されるので、モデルAのみ問題と推定。色々やってみた
下記記事などを参考に色々やってみました。
・DjangoでmigrateしてるのにDBに反映されない時の対処法【Djangoアウトプット】
・DjangoでMigrationsのリセット方法(既存のデータベースを残したまま)
・djangoでmigrateができない
・Djangoのmodels.pyの反映(python manage.py makemigrations/migrate)
・modelにカラムを追加したのにmigrationファイルに追加されない現象
・python manage.py migrationが効かない時の対処法 [Django]大体「python manage.py migrate --fake hoge zero」でマイグレーション履歴削除して、フォルダのファイルも削除後、makemigrationしてmigrateすれば反映されるでしょ、という事でしたが全滅。
おまえだったのか
で、一旦DBドロップして再度マイグレーションしてみると…モデルA以外にもうひとつモデル(モデルBとします)が反映されていない。
「?」と思いよくよく「models.py」を読んでいるとこれら2つのモデルのみ「class Meta:」の中に「managed = False」の文字が。models.pyclass Meta: managed = False db_table = 'temp'試しにこれを削除して再度マイグレーションしたら、出来ました。あっさりと。
モデルBはどこかからかコピーしてきた書式をペーストしていて、これをコピペしてモデルAを作成したので今回のトラップにかかった様です。
「managed = False」と書く事でマイグレーションの管理対象外とするそうな。そりゃ反映されない筈だ。
(「経緯」で他モデルの変更は反映されたと書いていますが、モデルBでは試していませんでした)という訳で
コピー元がなぜこれを含んでいたかは謎ですが、一先ず動いたので…
灯台下暗し。もう少し手前から調べてみようと思いました。また時間できたら纏めたいと思います。
時間できたら…(やるのか…?)
- 投稿日:2020-02-25T12:03:26+09:00
rails db:migrateをやり直す
はじめに
テーブル設計ミスでcreate_table :hoge2_tablesにキーを書き忘れ、idというカラムができてしまいました
これでは困ってしまうのでテーブルを作り直そうと思います現在のテーブルはこんな
+-----------+--------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | <-これを消したい | user_id | int | YES | | NULL | | <-ここにキーを移したい | user_name | text | YES | | NULL | | +-----------+--------+------+-----+---------+----------------+migrationファイルを書き換えてrails db:migrate:resetを行う
>rails db:migrate:reset Mysql2::Error: Access to system schema 'mysql' is rejected.: DROP DATABASE IF EXISTS `mysql` Couldn't drop database 'mysql' rails aborted! ActiveRecord::StatementInvalid: Mysql2::Error: Access to system schema 'mysql' is rejected.: DROP DATABASE IF EXISTS `mysql` bin/rails:4:in `require' bin/rails:4:in `<main>' Caused by: Mysql2::Error: Access to system schema 'mysql' is rejected. bin/rails:4:in `require' bin/rails:4:in `<main>' Tasks: TOP => db:drop:_unsafe (See full trace by running task with --trace)リジェクトされてテーブルの更新ができねえ...
いろいろ調べてこちらの記事にたどり着いたバージョンを指定してやり直す
まずはmigrateのステータスを確認する
rails db:migrate:status database: mysql Status Migration ID Migration Name -------------------------------------------------- up 20200218022902 Create huga tables up 20200218074718 Create huga2 tables up 20200219072619 ********** NO FILE ********** up 20200220020515 Create hoge tables up 20200220095931 ********** NO FILE ********** up 20200225014707 Create hoge2 tables一覧からやり直したいテーブルのmigration IDを控えて次のコマンドを実行
rails db:migrate:redo VERSION=20200225014707 == 20200225014707 CreateHoge2Tables: reverting ============================== -- drop_table(:hoge2_tables, {:primary_key=>:user_id}) -> 0.0397s == 20200225014707 CreateHoge2Tables: reverted (0.0514s) ===================== == 20200225014707 CreateHoge2Tables: migrating ============================== -- create_table(:hoge2_tables, {:primary_key=>:user_id}) -> 0.0275s == 20200225014707 CreateHoge2Tables: migrated (0.0281s) =====================いけたっぽいのでテーブルを確認する
mysql> desc hoge2_tables; +-----------+--------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------+------+-----+---------+----------------+ | user_id | bigint | NO | PRI | NULL | auto_increment | | user_name | text | YES | | NULL | | +-----------+--------+------+-----+---------+----------------+idカラムが消えてuser_idが主キーになってますね、めでたしめでたし
- 投稿日:2020-02-25T11:12:48+09:00
Rails5でINSERT IGNOREする
色々あるみたいです。
インサートしたidは不要!パターン
インサート後、id(オートインクリメントされるプライマリーキー)は不要の場合。
1. create! rescue
対象が数件のときには簡単便利。
param = { id: 6, # プライマリーキー name: 'ポール・ポグバ', position: 'MF' } Pogba.create!(param) rescue ActiveRecord::RecordNotUniqueidやユニークインデックスをparamに指定しておけば、重複時に発生する例外をrescueですっ飛ばせる。
参考:https://stackoverflow.com/questions/31396310/insert-ignore-into-table-statement-in-rails-active-records2.gem
activerecord-import
を利用対象が大量にある場合に便利。
ksk.push(Honda.new(id: 10, name: '本田圭佑', position: 'いい意味でプレッシャー')) ksk.push(Honda.new(id: 33, name: '本田圭佑', position: 'ケイスケ・ホンダ')) ... Honda.import ksk on_duplicate_key_ignore: trueちなみにPostgreSQLだけにはなるが、インサートしたあとにidが取得できるらしい。
参考:https://github.com/zdennis/activerecord-import#duplicate-key-ignoreインサートしたidが欲しい!パターン
1. find_or_initialize_by
param = {id: 45, name: 'マリオ・バロテッリ', position: 'FW'} milano = Intel.find_or_initialize_by(param) milano.update(param) if milano.new_record?
update(param)
で「INSERT INTO〜」が実行されるのはキモチワルイ。
参考:https://qiita.com/taimuzu/items/0a21738d018f475d63aefind_or_create_byもあるけどブロック内でわざわざINSERTする値を作り直さなきゃいけないので面倒。(なんか他にいい方法あるのかな)
参考:https://qiita.com/yusabana/items/1b566f61ca556a482f52
- 投稿日:2020-02-25T00:33:56+09:00
C#でMySQLからSELECTした結果を取り出したい
C#は素人なのと、あとコードは再現なので不適切なコードになっている可能性が高いです。
おそらくもっといい解法があるはずですが、調べた限りではよくわかりませんでした。課題
テーブルAからSELECTする、テーブルBからSELECTする、その後ふたつのデータを色々やって最後にテーブルCにインサートする、みたいなことがやりたかったわけですよ。
コード側で色々と処理を行う必要があるため、一度コード側にデータを引き取るのが前提です。問題
ベストプラクティスがわからない。
適切なサンプルコードが見付からない。信頼できるドキュメントはMicrosoft公式くらいしかないわけですが、そこに載ってるサンプルコードはどうにも役に立ちません。
https://docs.microsoft.com/ja-jp/azure/mysql/connect-csharp
https://docs.microsoft.com/ja-jp/dotnet/api/overview/azure/mysql?view=azure-dotnet公式サンプルの例using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); // SQL発行 MySqlCommand selectCommand = new MySqlCommand("SELECT * FROM MyTable", conn); MySqlDataReader results = selectCommand.ExecuteReader(); // 行ごとにループ while(results.Read()) { Console.WriteLine("Column 0: {0} Column 1: {1}", results[0], results[1]); } }ループ中でログ出力する例しか載ってない。
そんな役に立たないコードじゃなくてもっとこう、取得結果をreturn
して他所で使い回す方法はないんですかね。しかも
results[0]
とか、今どき列番号で取ってくるなんて有り得ないじゃろ。
列名で取ってきてくれよ。やってみた
// メイン public static void Main{ MySqlConnection mySqlConnection = new MySqlConnection("ConnectionString"); mySqlConnection.Open(); var tableA = getTable("tableA"); var tableB = getTable("tableB"); } // 取得 public async Task<DataReader> getTable(string tableName){ using (var command = mySqlConnection.CreateCommand()) { command.CommandText = $"SELECT * FROM {tableName}"; using (var reader = command.ExecuteReaderAsync()) { return reader; } } }できた。
はい、これ
There is already an open DataReader associated with this Connection which must be closed first
とか言われて死にます。
どうもDataReaderは同時にひとつしか開けないらしい。
二つ目のSQLを投げる前に、一つ目のSQLはCloseしなくてはなりません。
Closeすると当然データが取れなくなるので、Closeする前に値を取り出しておかねばならないということです。少し変更した
DataReaderから値を取得して、Dictionaryに突っ込んで返すようにしました。
static MySqlConnection mySqlConnection; public static void Main{ mySqlConnection = new MySqlConnection("ConnectionString"); mySqlConnection.Open(); var tableA = getTable("tableA"); var tableB = getTable("tableB"); } // 取得 public Dictionary<string, Dictionary<string, string>> getTable(string tableName){ using (var command = mySqlConnection.CreateCommand()) { command.CommandText = $"SELECT * FROM {tableName}"; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { JObject line = new JObject(); for (int i = 0; i < reader.FieldCount; i++) { line.Add(reader.GetName(i), reader.GetString(i)); } ret.Add(reader.GetString("id"), line); } } } } return ret; }これでできた!
と思いきや、何故かこれでもalready an open DataReader
が発生することがあります。
なんで?returnする前にusingの外に出てるやろ?
原因がさっぱりわからなかったので諦めました。しかも、どうやらその行の列を全て取得するみたいなメソッドがどうも存在しないっぽい。
いちいちFieldCount
で列数を調べて全列をループで回して取り出しています。
SQL発行する度に毎回行×列のループを回さねばならないとか、本当かこれ???そもそも最初からfetchAllがあれば解決なのですよ。
どうしてこの程度のメソッドが用意されてないのですかね?とりあえず完成
mySqlConnectionを毎回開けばええんや。
public static void Main{ var tableA = getTable("tableA"); var tableB = getTable("tableB"); } // 取得 public Dictionary<string, Dictionary<string, string>> getTable(string tableName){ using (MySqlConnection mySqlConnection = new MySqlConnection("ConnectionString")) { mySqlConnection.Open(); using (var command = mySqlConnection.CreateCommand()) { command.CommandText = $"SELECT * FROM {tableName}"; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var line = new Dictionary<string, string>(); for (int i = 0; i < reader.FieldCount; i++) { line.Add(reader.GetName(i), reader.GetString(i)); } ret.Add(reader.GetString("id"), line); } } } } } return ret; }いや……一応これで動いたんだけどさあ、どう考えてもおかしいだろ、これ。
SQLを発行するたび毎回MySqlConnection
をnewしてるってことは、PHPで言うと毎回new PDOってしてるってことですよね。
そんな書き方ありえなーい。
そもそも高々SQL発行するだけなのに何重に括らせる気なんだよ。なんかもっとこう、ちゃんとした正しい書き方ってのがあるよね?
あると思うのですよ。
間違いなく存在するでしょう。実はありまぁす
DataTableってのがあった。
public static void Main{ var tableA = getTable("tableA"); var tableB = getTable("tableB"); } public DataTable getTable(string tableName) { DataTable tbl = new DataTable(); using(MySqlConnection mySqlConnection = new MySqlConnection(builder.ConnectionString)) { mySqlConnection.Open(); using(var command = mySqlConnection.CreateCommand()) { command.CommandText = $"SELECT * FROM {tableName}"; using(var reader = command.ExecuteReader()) { tbl.Load(reader); } } } return tbl; }これでDataTableに全データが入ってくるようで、MySqlConnectionを閉じた後でも問題なく参照することができました。
どう考えても真っ先にこれを紹介すべきなのに、普通に調べたら全然出てこないとかいう。
ここまで調べるのにめっちゃ時間かかった。なおMySqlConnectionを閉じないとエラーになるのは相変わらずです。
どうやったら使い回せるんですかね?
それ以前に、そもそも本当にコレが最終解なんですかね?その他
C#
では特に目立つ気がするのですが、Stackoverflowを機械訳したようなゴミURLばっかり引っかかってくるのはどうにかならないんですかね?