m:n Beziehungen zu Lookup-Tables mit einem SQL-Befehl erstellen

puzzleBei diesen Beitrag habe ich echt überlegt, ihn unter einem anderen Namen zu veröffentlichen. Ist der Inhalt doch eigentlich sowas von trivial und für Menschen mit mehr als zwei Hirnzellen wahrscheinlich sowieso offensichtlich. Aber das Gefühl, das mich durchflutet hat als mich Erkenntnis küsste war so erfüllend und erhebend, dass ich dieses gerne mit Euch teilen würde.

SQL ist eine geniale Erfindung. Zwar wird es im Moment arg von den schmächtigen noSQL bedrängt, aber es wird wie triumphieren *star-wars-musik-on*, denn die Anderen werden untergehen wie die noAngels, es wird gewinnen wie die Maden über die noMaden, häufiger verwendet werden, so wie es das Ah gegenüber noAh wird und edler sein, wie der Orden gegenüber noRden! Ich bin heute noch auf der Suche nach der vernünftigen Frage, die nicht in ein einziges SQL-Statement verpackt werden kann.*star-wars-musik-off*

Beispiel-Schema

Doch zurück zum Problem: Es seien Entitäten gegeben, die m:n Beziehungen zu „lookup-Werten“ haben. Ein bestehender Wert in einem Set also, das sich selten ändert wie beispielsweise Kategorien, Zustände, Farben, Ländercodes, Geschlechter oder so etwas. Ein weiterer Use Case wäre ein Import, bei dem die Datensätze schon erstellt wurden und nun nur noch die Verbindungen fehlen.

Hier ein Beispiel:
Personen beinhalten, ja ratet mal, Personen.Hier vereinfacht nur mit Namen und Vornamen.
Personentypen bezeichnen die Art der Person. Beispielsweise Lernender, Lehrperson, Verwaltungsangestellter oder auch Schulleiter.

Jede Person kann mehreren Personentypen entsprechen. Diagnose: klassisches m:n mit Verbindungstabelle (personen_personentypen).

Weiterlesen

MySQL berücksichtigt keine Gross- und Kleinschreibung bei select?

Manchmal habe ich echt das Gefühl, ein paar Jahre ganz feste kognitiv eingeschränkt gewesen zu sein. Per Zufall stellte ich heute fest, was das „ci“ bei den Datenbank Collations in Mysql heisst.

<musik typ="leicht-nervig" creator="Synthesizer" dauer="10" />
Moderator: Wahaaas bedeutet das Zeh Ih (_ci) bei den Datenbank Collations in MySQL?
Kandidat: Hmmm, uff, kann ich es im Kontext haben?
Moderator: Natürlich! Zeh Ih wie in utf8_general_ci.
Kandidat: Ui, das weiss ich nicht, gibt es Auswahlmöglichkeiten?
Moderator: Ja sicher:

  1. collation identifier
  2. childish internet
  3. clown institut
  4. corporate identity
  5. case insensitive


Und die Lösung: CASE INSENSITIVE

Was bedeutet _ci in der Praxis?

Tabelle:

+----+----------+----------+------------------------------------------+
| id | username | salt     | password                                 |
+----+----------+----------+------------------------------------------+
|  1 | Admin    | 2IT8mKiX | 8fd334609270a4b78c536dbdee4182b5b1d00bb7 |
+----+----------+----------+------------------------------------------+

Tja, folgender Tabelleneintrag wird gefunden mit folgenden Selects:

SELECT * FROM users WHERE username='Admin'; -- Klar, logo
SELECT * FROM users WHERE username='ADMIN'; -- Ui
SELECT * FROM users WHERE username='admiN'; -- Uiui
SELECT * FROM users WHERE username='admin'; -- Uiui, huch

Und das impliziert wiederum, dass ein User, der sich ADMIN nennt, unter Umständen vor dem eigentlichen Admin gefunden wird. Das ist doch eher hässlich. Wieso ist mir das nicht früher aufgefallen..?

Und die Lösung?

Die Collation ändern! Von utf8_general_ci zu utf8_bin und schon verhält sich die Datenbank erwartungskonform.

Was bedeutet dies für Rails?

Zum Einen sollte die Collation in der Datei config/database.yml festgelegt werden:

development:
  adapter: mysql
  encoding: utf8
  collation: utf8_bin
  database: lomas_development
  pool: 5
  username: lomas
  password: sagichnit
  socket: /var/run/mysqld/mysqld.sock

Leider wird die Collation bei Tests in Ruby on Rails nicht berücksichtigt! Das treibt mich zur Verzweiflung, echt!

Ausserdem gibt es noch etwas zu beachten bei validates_uniqueness_of. validates_uniqueness_of ist case-sensitive. Das heisst, auch hier werden admin und ADMIN akzeptiert. Erst ein weiterer Parameter verbietet dies:

  validates_uniqueness_of :username, :case_sensitive => false

Installation von Drupal ohne „CREATE TEMPORARY TABLE“ Rechte unter MySQL

Problem

Drupal[lang_de]Einige Hoster geben – wahrscheinlich wegen des momentanen Vollmondes – das MySQL Recht CREATE TEMPORARY TABLES nicht an ihre Kunden. Drupal möchte das leider und so muss man es umgehen 🙁 …

Bei der Installation beispielsweise wird gemeldet:[/lang_de]
[lang_en]
Some hosting providers do not grant the MySQL-userright CREATE TEMPORARY TABLES to their customers. Maybe because it’s full moon at the moment and everyone seems a bit crazy. But a href=“http://drupal.org/“>Drupal really wants this right and so a workaround is needed 🙁 …

The error during the installation could be something like this:[/lang_en]

user warning: Access denied for user: 'drupal@%' to database 'drupal' query: CREATE
TEMPORARY TABLE missing_nids SELECT n.nid, n.created, n.uid FROM node n LEFT
JOIN node_comment_statistics c ON n.nid = c.nid WHERE c.comment_count IS NULL in
/var/websites/drupal/includes/database.mysql.inc on line 167.

[lang_de]

Eine Art Lösung

Ich habe für Drupal 5.2 einen Patch erstellt der das Problem lösen sollte. CREATE TEMPORARY TABLES ist in Drupal vorbildlich in eine einzige Funktion db_query_temporary in der Datei includes/database.mysql.inc ausgelagert. Grundsätzlich erstellt dieser Patch statt einer temporären eine richtige Tabelle, deren Erstellungsdatum in einer weiteren Tabelle mit Namen temporary_table gespeichert wird. cron.php entfernt diese dann regelmässig. Sollte die Tabelle schon existieren bei der Erstellung, wird sie vorgängig gelöscht.

Das Ganze funktioniert nur für MySQL und MySQLI und bedeutet einen enormen Geschwindigkeitsverlust. Ich habe den Patch nur bei einer low-traffic Site getestet und weiss nicht, ob es vielleicht noch Race-Conditions geben könnte. Benutzung also auf eigene Gefahr.[/lang_de]
[lang_en]

Kind of a Solution

I have written a patch for Drupal 5.2 which should kinda solve the problem. In Drupal, CREATE TEMPORARY TABLES is only used in the db_query_temporary function, found in the file includes/database.mysql.inc, which is a very clean progamming style! Basically, this patch creates a real table instead of a temporary one. The name and the creationdate is stored in an extra table called temporary_table. The cron.php removes these tables on a regular base. If the table already exists, it is dropped.

This patch only works for MySQL and MySQLI and may be a performance loss. I have tested this patch only on a low traffic site and I do not know if there are race conditions. Use it on your own risk…

Addendum

Another solution has been posted at Drupal.org.

[/lang_en]

[lang_en]Here it is: Patch to use Drupal without the CREATE TEMPORARY TABLES.

To apply it, change into the Drupal base directory and issue patch -p0<drupalwotemptables.patch on the commandline.[/lang_en]
[lang_de]Und hier ist er: Patch für Drupal ohne CREATE TEMPORARY TABLES.

Um ihn anzuwenden ins Basisverzeichnis wechseln und patch -p0<drupalwotemptables.patch ausführen.

Nachschlag

Eine weitere Lösung wurde bei Drupal geposted.
[/lang_de]

Unicode-Hölle: PHP, Apache, Mysql und Symfony in UTF-8

Ich will mein altes ASCII wieder!!! Unicode ist die Hölle. Vielleicht helfen folgende Dinge dem, der damit rumkämpfen will/muss:

  • Im ganzen Pfad muss die Codierung stimmen bzw. gezielt umgewandelt werden. In unserem Beispiel also Dateisystem, Datenbank, (PHP), Applikation und Browser.
  • Glaube nicht was Du siehst! Was angezeigt wird ist wieder aus einer Applikation die irgendwie encodiert.
  • Firefox: View -> Character Encoding ist Dein Freund. Damit sieht man, was der Browser denkt er erhalte und das Umschalten auf 8859-1 kann zeigen, dass man es noch nicht geschafft hat und immernoch das alte Encoding verwendet wird.
  • utf8_encode() und utf8_decode() sind böse! Wenn man die braucht stimmt etwas nicht.

Hier ein paar Hilfen und Tools.

Dateisystem:

locale -a zeigt die Codierungen. Auf Debian kann man mittels dpkg-reconfigure locales einiges umstellen. Hier mal Unicode zu haben schadet nicht.

Konvertierung mit recode:

Folgendes Kommando wandelt eine Datei in ISO88591 in Unicode um:

recode ISO-8859-1..UTF-8 DATEI

Mysql:
Um Daten in UTF-8 abzulegen, muss:

  • das charset stimmen
  • die Verbindung vom Client zum Server stimmen
  • die Datenbank eine Collation „utf8_unicode_ci“ besitzen
  • jede Tabelle eine Collation „utf8_unicode_ci“ besitzen
  • die Daten in UTF-8 abgefüllt sein

Für das Abfüllen eignet sich mysql-query-browser, da dieser schon mit utf-8 arbeiten kann, was bei der Konsole nicht immer ganz sicher ist.

  • Tools: SHOW VARIABLES LIKE ‚character%‘,damit sieht man, wie man unterwegs ist
  • set names utf8 zwingt die Verbindung zu utf8
  • select _utf8’Trällöällä‘ wandelt den String inUTF-8

Apache:

Irgendwo im Conf sollte AddDefaultCharset UTF-8 stehen. Das vereint das Charset über HTTP.

PHP/Browser:

Wenn der Browser kein UTF-8 anzeigen will, kann man das im php.ini umstellen: default_charset = „UTF-8“.

Links

http://www.phpwact.org/php/i18n/charsets ist eine wirklich gute, praktische Einführung in das Problem im Zusammenhang mit PHP.

Symfony/Creole:

Um da mit UTF-8 zu arbeiten muss databases.yml gefüllt werden. KEIN dsn benutzen, denn dann funzts nicht:

all:
  modul:
    class:      sfPropelDatabase
    param:
      phptype:  mysql
      username: dbuser
      password: dbuserpass
      database: modul
      host:     localhost
      encoding: utf8

Ein ähnliches, hässliches Problem bei der Datenbankmigration wird bei Orthogonal Thought beschrieben.

MySQL Views verwenden mit Propel und Symfony

Symfony, das PHP-Framework ist genial. Will man allerdings die Datenbank-Views verwenden, geht das nicht so direkt.

Damit die Propel orm-Klassen generiert werden, muss im config/schema.yml die Struktur so definiert werden, dass sie nicht direkt in die Datenbank abgespitzt wird:

m_my_view:
  _attributes: { phpName: ModulCompleteView, skipSql: true}
  post_id:        {type: integer}
  post_title:     {type: varchar, size: 255}
  post_content:   {type: longvarchar}
  comment_id:     {type: integer}
  comment_content:{type: longvarchar}

Damit die View bei symfony propel-insert-sql erstellt wird, sollte eine Datei im gleichen Verzeichnis erstellt werden in dem auch lib.model.schema.swl ist (normalerweise /data/sql). Wir nennen sie my-view.sql:

create or replace view m_my_view as
select
  post.id as post_id,
  post.title as post_title,
  post.content as post_content,
  comment.id as comment_id,
  comment.content as comment_content
from
  post, comment
where
  comment.post_id=post.id

Und nun nur noch die Datei sqldb.map ergänzen:

my-view.sql=DB-VERBINDUNG

und nun sollts funzen.

Geburtstagsliste mit MySQL

DAS zentrale Feature unseres Informationssystem ist eine Geburtstagsliste *gähn*. Ich habe eine Tabelle und möchte nun Geburtstage ab heute mit sql herausselecten. Diese birthday list hat folgende Probleme:

  • Ich kann sie nicht nach Geburtstagen ordnen, weil das Jahr keine Rolle spielt.
  • Ich kann auch nicht DAYOFYEAR verwenden, weil es ein Gnusch mit den Schalttagen gibt.
  • Es muss zyklisch sein, so dass Ende Jahr die Geburtstage vom Januar angezeigt werden.

Nun, die rettende Idee war, das Datum umzufomatieren. Also der 26.10.2006 zum 1026 zu machen und danach zu ordnen. Der Rest ist gna:

SELECT id, name, vorname, geburtsdatum
FROM personen_complete
WHERE geburtsdatum IS NOT NULL
ORDER BY
  IF( DATE_FORMAT(geburtsdatum,'%m%d') >= DATE_FORMAT(CURDATE(),'%m%d'),0,1),
  DATE_FORMAT(geburtsdatum,'%m%d') ASC,
  name, vorname