1つのWebプロジェクトの開発中に生まれたツールを共有したいので、テーブル、ストアドプロシージャ、インデックス、その他のデータベース住民の海で迷子にならないようにしています。



プロジェクト自体は、バックエンド-PostgreSQLとしてDjangoで作成されています。 当初、未加工のSQLとストアドプロシージャを優先してDjango ORMの使用を少なくとも部分的に放棄することが決定されました。 つまり、ほとんどすべてのビジネスロジックがデータベースレベルに移行しました。 ORMの準備方法を知っているとすぐに言わなければなりませんが、この場合、多くのサンプルに関連付けられた多段階の計算を実行する必要がありました。これはデータベースサーバーで実行し、中間データをアプリケーションにドラッグしない方が適切です。



Django Migrationsの喜びなしに、データベース構造を手動で維持する必要に直面して、手動でインクリメンタルSQLパッチを書くことができることがわかりましたが、データベースオブジェクトの依存関係を追跡することは困難です。 たとえば、別の場所で使用される関数に別の引数を追加する場合、単純なCREATE OR REPLACEでは十分ではありません。最初にDROP、次にCREATEが必要です。 この場合、最初にそれに依存する関数を削除してから再作成する必要があります（他の誰かがこれらの関数に依存している場合は、それらも再作成する必要があります）。



カットの下で、チュートリアル形式の機能の簡単な説明。 会いましょう-Sqlibrist。



彼らはすでに私の問題を解決する方法を学んだと言わなければなりません。 たとえば、 Sqitchは比較的長い間存在しています。 SQLで宣言形式でデータベースの構造を記述することができます。 各テーブル、ビュー、または関数は個別のファイルに保存され、単純なDSLが依存関係を記述します。 このユーティリティはPerlで書かれており、Perlの開発とそのパッケージのエコシステムに精通していない私は、このユーティリティをコンパイルするために一生懸命努力しなければなりませんでした。 おそらく、長い開発の歴史のために、Sqitchはそのような単純なプログラムに関して多くの依存関係を持っています。 また、依存関係の混乱した記述や、構造バージョンの操作も好きではありませんでした。 私は、自分にとって不快に思えるツールに適応して対処したくなかったと認めています。



Sqlibristを作成するとき、Sqitch、Django Migrations、およびいくつかのVCSに触発されました。 また、シンプルで直感的に使用できるようにしたかったのです。 データベース構造オブジェクトは個別のファイルに保存されます。 各オブジェクトには、このオブジェクトを作成および（オプションで）削除するSQLステートメントが含まれています。 オブジェクト間の依存関係は、組み込みDSLのディレクティブの形式で明示的に記述されます（ちなみに、キーワードはREQ、UP、DOWNの3つだけです）。 バージョン管理システムと同様に、Sqlibristはデータベース構造のスナップショットと、以前のスナップショットから更新するためのSQLパッチを保存します。



Sqlibristのインテリジェンスは制限されており、SQLを解析せず、ALTER TABLEを生成しません-これがあなたの仕事です。 ファイルの変更のみを追跡し、指示に従ってパッチを作成し、適用された移行を追跡します。

これはなんとなく抽象的に聞こえますが、練習に移りましょう。

設置

私のメインOSはサーバーとデスクトップの両方のLinuxなので、インストール手順は彼女専用です。 誰かがWindowsとMacを手伝ってくれるかもしれません。



まず、ヘッダーファイル：

Ubuntu

$ sudo apt-get install python-pip python-dev libyaml-dev $ sudo apt-get install libmysqlclient-dev # for MySQL $ sudo apt-get install libpq-dev # PostgreSQL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Fedora / CentOS

 $ sudo dnf install python-devel python-pip libyaml-devel $ sudo dnf install postgresql-devel # PostgreSQL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 $ sudo dnf install mariadb-devel # for MariaDB
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

または

 $ sudo dnf install mysql++-devel # for MySQL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SqlibristはPythonで作成されており、PyYAMLとpsycopg2およびmysql-pythonの2つの依存関係があります。



virtualenvまたはシステムライブラリのいずれかでpipを使用してインストールします。

 $ pip install sqlibrist
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

または

 $ sudo pip install sqlibrist
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インストール後、 sqlibristコマンドが使用可能になります。

オンラインストアデータベース

原始的なオンラインストアの例として、Sqlibristを試してみましょう。

 $ mkdir shop_schema $ cd shop_schema $ sqlibrist init Creating directories... Done.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

initチームは、プロジェクトのディレクトリ構造を作成しました。

 shop_schema sqlibrist.yaml migrations schema constraints functions indexes tables triggers types views
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

sqlibrist.yamlでは、データベースに接続するためのプロジェクト構成は次のとおりです。

 --- default: engine: pg user: <username> name: <database_name> password: <password> # host: 127.0.0.1 # port: 5432
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

設定が正しいことを確認するには：

 $ sqlibrist test_connection Connection OK
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、Sqlibristが適用された移行に関する情報を保存するテーブルを初期化します。 この部分はDjango Migrations / Southと同じです。

 $ sqlibrist initdb Creating db... Creating schema and migrations log table... Done.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ところで、Sqlibristの用語では、移行とは、この移行を適用したり、以前の移行にロールバックしたりするための基本構造とパッチのスナップショットです。



次に、ファイルshop_schema / schema / tables / user.sqlを作成します。

 --UP CREATE TABLE "user" ( id SERIAL PRIMARY KEY, name TEXT, password TEXT);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初の行--UPは、次のSQLステートメントがデータベースオブジェクトを作成することを意味します。 これでテーブルを作成できます。



同様に、さらに2つのファイルを作成します。



shop_schema /スキーマ/テーブル/ p​​roduct.sql ：

 --UP CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT, price MONEY);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

shop_schema /スキーマ/テーブル/ order.sql ：

 --REQ tables/user --UP CREATE TABLE "order" ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES "user"(id), date DATE);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

--REQ tables / userという行に注意してください。 これは、現在のオブジェクトがテーブル/ user.sqlファイル内のオブジェクトに依存していることを意味します （拡張子はREQに記述されていません）。 これにより、パッチが生成されるときに、 オーダーテーブルの前にユーザーテーブルが作成されることが保証されます。 すべての--REQは、ファイルの先頭に移動する必要があります。



別のファイル：



shop_schema / schema / tables / order_product.sql ：

 --REQ tables/order --UP CREATE TABLE order_product ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES "order"(id), product_id INTEGER REFERENCES product(id), quantity INTEGER);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初の移行を作成します。

 $ sqlibrist makemigration -n 'initial' Creating: tables/user tables/product tables/order tables/order_product Creating new migration 0001-initial
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

移行ファイルはshop_schema / migrations / 0001-initialに作成されます：

 up.sql down.sql schema.json
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Up.sqlには、移行を適用するためのパッチが含まれています。この場合、 down.sqlは空で、 schema.jsonには現在のデータベース構造のスナップショットが含まれています。



パッチを適用する前に、パッチのテキストをよく理解して（これが望ましい）、必要なことを確実に行うことができます。 適切でない場合は、 0001-initialディレクトリ全体を削除して、移行を再作成します。 何をしているのかわかっていればup.sqlとdown.sqlを編集できますが、 schema.jsonには触れないでください 。



最初の移行を適用します。

 $ sqlibrist migrate Applying migration 0001-initial... done
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3つのテーブルが作成されます。 次に、注文金額とともにユーザー注文を表示するビューが必要です。



shop_schema / schema / views / user_orders.sql ：

 --REQ tables/user --REQ tables/order --REQ tables/product --REQ tables/order_product --UP CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; --DOWN DROP VIEW user_orders;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

--DOWNディレクティブに続いて、再作成時にuser_ordersを削除するための指示があります。



一般的なルール：テーブルなどのデータを含むオブジェクトを手動で更新するため、説明に--DOWNが含まれず 、関数、型、インデックスを安全に削除および作成できるため、自動化で信頼できます。



また、指定されたユーザーのuser_ordersを返す関数が必要です。

 --REQ views/user_orders --UP CREATE FUNCTION get_user_orders(_user_id INTEGER) RETURNS SETOF user_orders LANGUAGE SQL AS $$ SELECT * FROM user_orders WHERE user_id=_user_id; $$; --DOWN DROP FUNCTION get_user_orders(INTEGER);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次の移行を作成して適用します。

 $ sqlibrist makemigration -n 'user_orders view and function' Creating: views/user_orders functions/get_user_orders Creating new migration 0002-user_orders view and function $ sqlibrist migrate Applying migration 0002-user_orders view and function... done
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

したがって、4つのテーブル、1つのビュー、1つの関数があります。



user_ordersビューに別のフィールドを追加する必要があるとします。 発生する可能性のある問題は次のとおりです。

• 新しいuser_ordersビューを削除して再作成できますが、 get_user_orders関数はこのビューに依存するため、データベースではこれが許可されません。
• チートしてCREATE OR REPLACE VIEW user_orders ...を取得できますが、ビューフィールドのタイプと関数の結果のタイプは異なります。 そしてこの場合、データベースは関数を再作成せずにこれを行うことはできません。

Sqlibristは、このような問題を解決するように設計されています。 フィールドSUM（op.quantity）をorder_totalとして user_ordersビューに追加します。

 --REQ tables/user --REQ tables/order --REQ tables/product --REQ tables/order_product --UP CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total, SUM(op.quantity) as order_total FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; --DOWN DROP VIEW user_orders;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

変更点を確認できます。

 $ sqlibrist -V diff Changed items: views/user_orders --- +++ @@ -2,7 +2,8 @@ u.id as user_id, o.id as order_id, o.date, - SUM(p.price*op.quantity) AS total + SUM(p.price*op.quantity) AS total, + SUM(op.quantity) as total_quantity FROM "user" u INNER JOIN "order" o ON u.id=o.user_id
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

移行を作成します。

 $ sqlibrist makemigration Updating: dropping: functions/get_user_orders views/user_orders creating: views/user_orders functions/get_user_orders Creating new migration 0003-auto
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初に依存オブジェクト、 つまりget_user_orders関数が削除され、次にビュー自体が削除されていることがわかります。 次に、関数が復元された後、新しい構造でビューが作成されます。 このようなスキームは、任意の深さの依存関係で機能します（ただし、循環依存関係ではありません-Sqlibristが修正を求めます）。



この移行を適用します。

 $ sqlibrist migrate Applying migration 0003-auto... done
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最後に、テーブルに変更を加えましょう。 テーブル定義を持つファイルには--DROPが含まれていないため、 手作業で作業します。

1. CREATE TABLEステートメントを変更します。
2. 同じmakemigrationコマンドを使用して新しい移行を生成します。
3. 必要なALTER TABLEをup.sqlに追加します。

製品テーブルに新しい「タイプ」テキストフィールドを追加します。



shop_schema /スキーマ/テーブル/ p​​roduct.sql ：

 --UP CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT, "type" TEXT, price MONEY);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは項目1です。次に、移行を作成します。

 $ sqlibrist makemigration -n 'new product field' Updating: dropping: functions/get_user_orders views/user_orders creating: views/user_orders functions/get_user_orders Creating new migration 0004-new product field
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

製品テーブルの定義を変更しましたが、 テーブル/製品は移行ログに存在しませんが、それに依存するすべてのオブジェクトが再作成されることに注意してください。 これがポイント2です。



次に、ポイント3： shop_schema / migrations / 0004-new product field / up.sqlエディターで開き 、12行目のテキスト-====をここに追加します==== 。 これは、移行の論理的な中間です。 この時点で、すべての依存オブジェクトが削除され、ALTER TABLEを挿入できます。



以下を貼り付けます。

 ALTER TABLE product ADD COLUMN "type" TEXT;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

up.sqlは次のようになります。

 -- begin -- DROP FUNCTION get_user_orders(INTEGER); -- end -- -- begin -- DROP VIEW user_orders; -- end -- -- begin -- -- ==== Add your instruction here ==== ALTER TABLE product ADD COLUMN "type" TEXT; -- end -- -- begin -- CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total, SUM(op.quantity) as total_quantity FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; -- end -- -- begin -- CREATE FUNCTION get_user_orders(_user_id INTEGER) RETURNS SETOF user_orders LANGUAGE SQL AS $$ SELECT * FROM user_orders WHERE user_id=_user_id; $$; -- end --
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このパッチを適用できます：

 $ sqlibrist migrate Applying migration 0004-new product field... done
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この時点で、オンラインストアはそのままにしておきます。



SqlibristはDjangoプロジェクトに統合することもできます。このコンテキストで使用します。



プロジェクトサイトはこちら 、バグレポートは大歓迎です。