DBMS

RDBMSはWebフレームワークのデータストアとしての基礎機能にとどまらず、高度な機能が多数実装されています。

DBアクセスの基礎SQL

テーブル作成以降はアプリケーションフレームワークがカバーするケースも多くありますが、ロール作成、DB作成、アクセス許可はSQL操作が必要になります。

Photo by Giammarco Boscaro

Articles

sqldef: Benefis and usage
sqldef is a CLI tool to maintain RDBMS schema along with corresponding configs. It is a ridgepole clone written in Go. Unlike standard migrations in popular web frameworks, their configs define the whole schema instead of the differences between schema versions. With traditional migrations, you cannot confirm the implemented schema in the database until the migration is applied.……
SQL loop by shell
While PL/pgSQL has LOOP flow for recursive operations, SQL or psql doesn’t have one. If you want to loop without PL/pgSQL, utilize bash: for id in $(psql -U<user> <database> -t -c "SELECT id FROM <table>"); do # any recursive query psql -U<user> <database> -t -c "CALL some_procedure($id)" done psql -t in the outer query excludes extra outputs like column header, so variable id gets each id from table.……
INSERT duplicate records into PostgreSQL
DBMS has the unique index feature that protects against INSERTing duplicate records, and at a minimum, the primary key must be unique. In cases like testing, where you might want to enter the same data, including the primary key, you need to be clever about how you use INSERT statement.……
Ridgepole: Benefits and usage
The standard database definition tool for Ruby on Rails is ActiveRecord’s Migration. It allows version control by writing database definition changes in Ruby scripts instead of SQL. However, with migration, as the number of DB definition changes increases, innumerable files will be generated and the outlook will be poor. By using ridgepole, DB definitions can be aggregated into one configuration file using the same notation as migration, and it can be operated with idempotent settings.……