This new website is a one-stop shop for board specs: hackerboards.com.
Tag Archives: database
The Database Relational Fuckup
In his book The Database Relational Model on page 25 Date says:
Here I would just comment–here comes the hindsight once again–that if as suggested earlier the database is to be regarded as a (correct!) logical system, it must never be allowed to contain any inconsistencies, at least from the user’s point of view. In other words, “remedying inconsistencies” needs to be done on an individual statement-by-statement basis (not even on a transaction-by-transaction basis). See reference  for further elaboration on this point.
Reference  is then:
C. J. Date and Hugh Darwen: Foundation for Object/Relational Databases: The Third Manifesto. Reading, Mass.: Addison-Wesley (1998). A second edition of this book, under the revised title Foundation for Future Database Systems: The Third Manifesto, is due to appear concurrently with the present book.
I think Date did a lot of harm with this comment. The classic example of a transaction is taking $100 from my account and depositing it in your account. So there’s one statement to take the money out, and another statement to put the money in, and after the first statement but before the second statement the database will always go through an invalid state. If RDBMS integrity facilities were on a transaction-by-transaction basis we could actually use them, as they’re not we suffer. Either we need to do more work than necessary or we have to under specify our constraints, or some awkward combination of both.
Formally Modeling Database Migrations
This is great: Formally Modeling Database Migrations. I need more formal methods in my life.
A great MySQL session
I found this great MySQL session over here. It has a clever approach for creating a big table, and shows how to invoke shell commands from the `mysql` client.
USE test; SET GLOBAL innodb_file_per_table=1; SET GLOBAL autocommit=0; -- Create an uncompressed table with a million or two rows. CREATE TABLE big_table AS SELECT * FROM information_schema.columns; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; COMMIT; ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment; SHOW CREATE TABLE big_table\G select count(id) from big_table; -- Check how much space is needed for the uncompressed table. \! ls -l data/test/big_table.ibd CREATE TABLE key_block_size_4 LIKE big_table; ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed; INSERT INTO key_block_size_4 SELECT * FROM big_table; commit; -- Check how much space is needed for a compressed table -- with particular compression settings. \! ls -l data/test/key_block_size_4.ibd
A light intro to Database Normalization. It referred to a table of normal forms over on Wikipedia.
How to Efficiently Choose the Right Database for Your Applications
Some interesting thoughts on How to Efficiently Choose the Right Database for Your Applications.
Codd’s 12 rules
Today in my travels I came across Codd’s 12 rules. They are rules for what qualifies as a relational database. Apparently these rules are discussed in RELATIONAL MODEL DATABASE MANAGEMENT 2 but as that is $486 I will not have a copy any time soon…
What is a database backup (back to the basics)
Today I saw What is a database backup (back to the basics), which I probably should have read more closely than I did.
I was reading about Abstraction inversion on Wikipedia which mentioned the anti-pattern of “using spreadsheet lookup functions to replicate the functionality of a database”.
So I found this article which said:
$ sudo -u postgres psql
postgres=> alter user postgres password 'apassword'; postgres=> create user your-user createdb createuser password 'passwd'; postgres=> create database your-db-name owner your-user; postgres=> \q
Note: to enable password logins for the ‘postgres’ admin account, edit: /etc/postgresql/9.4/main/pg_hba.conf and after this line:
local all postgres peer
Add this line:
local all postgres md5