FastJacks Paralleluniversum

Die Welt, in der ich lebe

SQL ohne Schleifen

Stichwörter: activerecord, extract, postgresql, rails, rubyonrails, schleifenlos, sql

Manche Aufgaben schreien geradezu danach, mit Schleifen gelöst zu werden. In SQL ist dies selten nötig. In meinem Beispiel wollten wir für jeden Monat des letzten Jahres die Häufigkeit eines bestimmten Ereignisses errechnen um es graphisch darzustellen. Mein Kollege, der an dieser Stelle besser anonym bleiben möchte, schlug schon vor, die Abfrage 12× mit unterschiedlichen Bedingungen abzuschießen.

Mir fiel ein, daß PostgreSQL über Funktionen verfügt, mit denen man aus einem Datum Jahr/Monat/Tag (und einiges Mehr) extrahieren kann. Die Funktion heißt sinnvollerweise extract. Damit kann man das Problem mit einer einzigen Abfrage erschlagen. Dies sieht dann so aus:

SELECT EXTRACT(month FROM created_on) AS month, COUNT(*) AS occurences
FROM my_event_log
WHERE EXTRACT(year FROM created_on)=2006
GROUP BY EXTRACT(month FROM created_on)
ORDER BY EXTRACT(month FROM created_on)

Dies läßt sich auch prima in Rails verwenden.

events = MyEventLog.count(:conditions => [ "EXTRACT(year FROM created_on)=?", 2006],
                          :group => "EXTRACT(month FROM created_on)",
                          :order => "EXTRACT(month FROM created_on)")

Es sollte an dieser Stelle jedoch nicht unerwähnt bleiben, daß durch die Verwendung von Funktionen wie extract in Verbindung mit ActiveRecord, der datenbankagnostische Aspekt von ActiveRecord verloren geht, da nicht alle Datenbanken die gleichen Funktionen unterstützen. Aber vermutlich ist datenbankagnostische Programmierung eh eine utopische Vision.

Eingetragen von FastJack am 23. April 2007 um 15:54

MS Access, pgsqlODBC und Booleans

Stichwörter: boolean, msaccess, odbc, pgsqlodbc, postgresql, sql, windows

pgsqlODBC macht in den Standardeinstellungen einige sehr sonderbare Sachen. Eine Spalte mit Booleans erscheint in MS Access als Textfeld. Das führt dann natürlich zu einigen Problemen mit Checkboxen. Access meint dann, das Feld wäre zu klein, um den Wert zu speichern. Dabei beherrscht MS Access Booleans. Man muß nur den pgsqlODBC-Treiber überzeugen, das ganze für MS Access verständlich zu präsentieren. Hierzu müssen nur 2 Optionen in der ODBC-Verbindung geändert werden. Die Optionen finden sich unter Datasource

  1. Bools as Char muß ausgeschaltet werden
  2. True is -1muß eingeschaltet werden

Verknüpft man die Tabellen nun (erneut), werden sie in MS Access auch korrekt als Ja/Nein angezeigt und können auch von Checkboxen verwendet werden.

Eingetragen von FastJack am 12. Juni 2006 um 18:03

Spielereien mit Rules und Check Constraints in PostgreSQL

Stichwörter: constraint, postgresql, rule, sql

Ich hatte heute das Problem, daß ich in der Datenbank Zeiträume (Start- und Enddatum) speichern mußte, in denen bestimmte Resourcen in Benutzung sind/sein werden. Eine Resource darf dabei zu einem bestimmten Zeitpunkt nicht mehrfach belegt werden. Ich mußte also sicherstellen, daß sich die Zeitbereiche einer Resource nicht überlappen.

Check Constrainst von PostgreSQL alleine hilft da leider nicht. Der kann nur auf Konsistenz innerhalb eines Datensatzen prüfen, z.B. ob das Startdatum vor dem Enddatum liegt. Soll geprüft werden, ob die neu einzufügenden Daten in irgendeiner Beziehung zu bereits vorhanden Daten stehen, so müssen Regeln (RULE) definiert werden. Dies sieht in meinem Beispiel so aus.

CREATE TABLE sometable (
	id				serial,
	start_date			date		NOT NULL,
	end_date			date		NOT NULL,
	resource			integer	NOT NULL,
	CHECK (start_date<=end_date)
);
CREATE RULE no_overlap_insert AS ON INSERT TO sometable
WHERE EXISTS (
	SELECT * FROM sometable
	WHERE ((( new.start_date>=start_date AND new.start_date<=end_date )
		OR ( new.end_date>=start_date AND new.end_date<=end_date ))
	  AND new.resource=resource)
)
DO INSTEAD NOTHING;

Hier habe ich also ein Check Constrainst, das prüft, ob das Startdatum auch wirklich vor dem Enddatum liegt sowie eine Regel, die verhindert, daß ich eine Resource zu einer Zeit mehrfach belege.

Unschön an dieser Lösung ist jedoch, daß der Check Constrainst einen Fehler hervorruft, wenn die Bedingung nicht erfüllt wird, während die Regel einfach still die Daten verwirft. Man kann also nur anhand der Anzahl der geänderten Datensätze, die nach jedem INSERT/UPDATE zurückgegeben wird, feststellen, ob die Daten erfolgreich gespeichert wurden. Hier wäre es natürlich angenehmer, wenn auch ein Fehler erzeugt würde.

Nicht im Beispiel enthalten ist eine zweite Regel, die verhindert, daß man bereits vorhandene Datensätze so ändert, daß es doch wieder zu Überlappungen kommt. Hierzu muß einfach eine zweite Regel (mit anderem Namen) erstellt werden, die statt ON INSERT ein ON UPDATE enthält.

Eingetragen von FastJack am 7. Juni 2006 um 18:42

PostgreSQL auf Mac OS X selbstgebaut

Stichwörter: anleitung, apple, installation, kompilieren, launchd, mac, macosx, postgresql, sql

Dies ist eine Anleitung, um PostgreSQL auf Mac OS X Tiger zu installieren. Diese Anleitung ist größtenteils an diese Anleitung angelehnt, doch an einigen Stellen weiche ich davon ab, wo es mir sinnvoll erschien. Ich habe sie für mich und meinen Arbeitskollegen geschrieben, doch ich hoffe, daß sie sich auch für andere als hilfreich erweist.

Es gibt viele Anleitungen, die beschreiben, wie man PostgreSQL auf einem Unix-System installiert. Mac OS X ist im Grunde ein solches. Doch es bringt ein zusätzliches Verzeichnis-Layout mit sich, was sich hierfür eher anbietet. Nach der Installation befinden sich alle Dateien von PostgreSQL in /Library/PostgreSQL. Zusätzlich werden die Binaries sowie die Man-Pages nach /usr/local symlinked, damit man diese einfach von der Kommandozeile benutzen kann. (/usr/local/bin werden die meisten im $PATH haben). Zum Schluß landen die Datenbanken selbst in /Users/Shared/PostgreSQL. Alle Schritte wurden auf einem Powerbook mit G4-Prozessor durchgeführt, sollten jedoch auf den neuen Intel-Macs genauso funktionieren.

Die folgenden Schritte setzen voraus, daß Xcode auf dem System installiert ist. Ansonsten wird es schwierig, PostgreSQL zu kompilieren.

mehr…

Eingetragen von FastJack am 3. Mai 2006 um 10:27

Mit Array in PostgreSQL herumhantieren

Stichwörter: array, postgresql, sql

Ich brauchte bei einer Abfrage einfach nur die beteiligten Mitarbeiter. Eine einfache Auflistung der Namen reichte mir da. Mit PostgreSQL kann man sowas recht einfach erledigen.

SELECT array_to_string(ARRAY['foo', 'bar', 'baz', 'qux'], ', ');
  array_to_string   
--------------------
 foo, bar, baz, qux
(1 row)

Das Array wird als String mit jeweils dem zweiten Parameter aus array_to_string() verkettet. Ist viel einfacher als in der Skriptsprache (PHP, Ruby, Perl) die Rows miteinander zu verketten.

Nachtrag: Und bevor jetzt einer fragt, wie das nun mit Rows aus einer Abfrage geht, hier ein Beispiel. Ich habe diese Tabelle

test=# SELECT * from test_table;
 id | some_string 
----+-------------
  1 | foo
  2 | bar
  3 | baz
  4 | qux
(4 rows)

Und nun zu einer Row verkettet:

test=# SELECT array_to_string(ARRAY(SELECT some_string FROM test_table WHERE id<4), ', ');
 array_to_string 
-----------------
 foo, bar, baz
(1 row)

Eingetragen von FastJack am 10. April 2006 um 14:22

Notiz an mich selbst: PostgreSQL ACLs

Stichwörter: acl, grant, postgresql, sequence, sequenz, sql

Damit ein Benutzer auf eine Tabelle zugreifen darf, muß ihm dieses privileg erst eingeräumt werden. Soweit nix neues. Allerdings sollte man bei PostgreSQL nicht vergessen dem User auch Rechte auf etwaige Sequenzen zu geben, sonst schlägt ein nextval(footable_id_seq) fehl und damit der ganze Insert. Also immer schön ein GRANT SELECT, UPDATE ON footable_id_seq TO foouser; nachschieben.

(Das UPDATE ist nötig, damit beim nextval die Sequenz auch hochgezählt wird.)

Eingetragen von FastJack am 9. März 2006 um 11:52

Rechnen mit Datumswerten in PostgreSQL

Stichwörter: datum, postgresql, rechnen, sql

Damit ich es nicht vergesse, und damit auch die Nachwelt was davon hat, hier ein Beispiel, wie einfach das Rechnen mit Datumswerten in PostgreSQL gehen kann.

SELECT CAST('2006-01-01 00:00:00' AS TIMESTAMP) + 5.75 * INTERVAL '1 hour'

Nachdem ich mich zuvor mit DATE_ADD und DATE_SUB bei MySQL herumgeschlagen habe, war das eine angenehme Abwechslung.

Eingetragen von FastJack am 8. Februar 2006 um 17:29

PostgreSQL auf OpenBSD

Stichwörter: bsd, datenbank, howto, installation, openbsd, postgresql, sql

Für die Installation von Movable Type auf OpenBSD brauchte ich eine Datenbank. Die Wahl fiel auf PostgreSQL. Hier ist was ich tat, um PostgreSQL nach meinen Vorstellungen zu konfigurieren.

mehr…

Eingetragen von FastJack am 23. April 2004 um 20:22