Into the Dayta-b'hase

Slides & code at catherinedevlin.blogspot.com

Me

8 yr. Oracle database administrator

(align: LE)

IntelliTech Systems, Dayton, OH

If you're here to discuss the difference in B-tree optimization between Postgres and MySQL's ISAPI engine... get out. Go see Aaron instead; this will be way too basic for you.

psql --echo-all

Why a RDBMS

Relational DataBase Management System

• simultaneous access
• atomicity
• implementation-independent access
• data relationships
• data quality

Flat File

name, moniker, race, player
Gruk, the Unbathed, half-orc, Christie
Lois, of Edina, human, Cordelia
Mickey, the Tricky, human, NPC
Steve, of Cleveland, half-elf, Grant

Losing flatness

name, moniker, race, player, weapon1, damage1, weapon2, damage2
Gruk, the Unbathed, half-orc, Christie, Morningstar, d8, Dagger, d4
Lois, of Edina, human, Cordelia, Staff, d6, ,
Mickey, the Tricky, human, NPC, Dagger, d4, Dagger, d4
Steve, of Cleveland, half-elf, Grant, Longsword, d8, Longbow, d6

Downright bumpy

name, moniker, race, player, class, level
Gruk, the Unbathed, half-orc, barbarian, 3
Lois, of Edina, human, Cordelia, bard, 8
Mickey, the Tricky, human, NPC, sorcerer/wizard, 4/2

Trying to fix

weapon, damage
Dagger, d4
Morningstar, d8
Longbow, d6
Longsword, d8
Staff, d6

You can write increasingly complex, fragile code to get at this data... but gradually, you're writing your own database system. A bad one.

Installing & creating

$ sudo apt-get install postgresql

$ sudo su - postgres

$ createuser <yourname>

$ exit

$ createdb dnd

What have we got?

Yes, it's running

Files + process

Think "pharmacist"

It's like rules without a campaign setting or adventure

Similar to a filesystem, which is running whether you're accessing it or not

How do you get in?

• command-line SQL client
• GUI (pgadmin3)
• spreadsheet, etc. through ODBC
• homemade program
• web app (TurboGears, Django, Ruby on Rails)...

What about MS Access?

pgadmin3

$ psql dnd

dnd=# alter user catherine with password 'friend';

dnd=# \q

$ sudo apt-get install pgadmin3

$ pgadmin3

Huh? Looks empty - use plug to create new connection

Look how structured it is - metadata

SQL

the Common Tongue of RDBMS

$ psql dnd

dnd=#

Campaign theme

Naming issues

Consistency in case

Table names: plural?

join_words_with_underscores?

Backup

$ pg_dump dnd > dnd.sql

dnd=# \i dnd.sql

Remember the pharmacy issue

Reporting

dnd=# \g resultfile.txt

Jasper Reports

See catherinedevlin.blogspot.com

See also

PostgreSQL 8.3 and Beyond

Aaron Thul

Sunday, 11:00 am

Maple B