MySQL to Perl to JSON

Das nette CPAN stellt für die Umwandlung von Perl-Datenstrukturen in JSON das eigentlich geeignete JSON-Modul zur Verfügung. Will man jedoch Daten, die man gerade aus einer MySQL-Datenbank geholt hat, in mit dem CPAN-JSON-Modul so in JSON konvertieren, dass gängige JavaScript-Frameworks das JSON anstandslos verarbeiten können, gibt es Probleme:

Angenommen, das Holen der Daten in Perl erfolgt so

my $statementHandle = $dbh->prepare($statement);
my $queryResult = $statementHandle->execute();
my $resultHashRef = $statementHandle->fetchall_arrayref({});

und die Konvertierung nach JSON so

my $jsonText = JSON->new->encode($resultHashRef);

so werden numerische Werte aus Spalten, die beispielsweise mit SMALLINT(5) angelegt wurden, mit Hochkommata umschlossen, obwohl dies nicht sein dürfte (JavaScript-Frameworks im Frontend erwarten Typsicherheit).

Die eigentliche Information, von welchem Typ eine Spalte ist, ist am Statement-Handle und am DBH jedoch vorhanden. Mit

my $typesOfFields  = $statementHandle->{TYPE};

lassen sich die Datentypen aller in der letzten Abfrage gestellten Spalten holen.

Ein anschließendes Iterieren über die Typen und Ausgabe von deren type_info offenbart die internen Metainformationen über den Datentyp der jeweiligen Spalte (und weil mir JSON gut gefällt, lasse ich mir das auch gleich als JSON ausgeben):

foreach my $typeElement (@$typesOfFields) {
  my $typeInfo = $dbh->type_info($typeElement);
  my $jsonType = JSON->new->encode($typeInfo);
  print "jsonType = ".$jsonType."\n";
}

Die Ausgabe dieses Codes sieht dann für eine Tabelle mit einer einzigen Spalte, die als SMALLINT(5) angelegt wurde, so aus:

jsonType = {
 "UNSIGNED_ATTRIBUTE" : 0,
 "MAXIMUM_SCALE" : 0,
 "INTERVAL_PRECISION" : 0,
 "CREATE_PARAMS" : null,
 "NUM_PREC_RADIX" : 10,
 "SEARCHABLE" : 3,
 "LOCAL_TYPE_NAME" : "Short integer",
 "LITERAL_PREFIX" : null,
 "COLUMN_SIZE" : 5,
 "MYSQL_NATIVE_TYPE" : 2,
 "MINIMUM_SCALE" : 0,
 "TYPE_NAME" : "smallint",
 "AUTO_UNIQUE_VALUE" : 0,
 "NULLABLE" : 1,
 "SQL_DATATYPE" : 5,
 "DATA_TYPE" : 5,
 "LITERAL_SUFFIX" : null,
 "CASE_SENSITIVE" : 0,
 "FIXED_PREC_SCALE" : 0,
 "MYSQL_IS_NUM" : 1,
 "SQL_DATETIME_SUB" : 0
}

Für eine Tabelle mit einer Spalte, die mit VARCHAR(255) angelegt wurde, sieht sie so aus:

jsonType = {
 "UNSIGNED_ATTRIBUTE" : 0,
 "MAXIMUM_SCALE" : 0,
 "INTERVAL_PRECISION" : 0,
 "CREATE_PARAMS" : "max length",
 "NUM_PREC_RADIX" : null,
 "SEARCHABLE" : 3,
 "LOCAL_TYPE_NAME" : "variable length string",
 "LITERAL_PREFIX" : "'",
 "COLUMN_SIZE" : 255,
 "MYSQL_NATIVE_TYPE" : 253,
 "MINIMUM_SCALE" : 0,
 "TYPE_NAME" : "varchar",
 "AUTO_UNIQUE_VALUE" : 0,
 "NULLABLE" : 1,
 "SQL_DATATYPE" : 12,
 "DATA_TYPE" : 12,
 "LITERAL_SUFFIX" : "'",
 "CASE_SENSITIVE" : 0,
 "FIXED_PREC_SCALE" : 0,
 "MYSQL_IS_NUM" : 0,
 "SQL_DATETIME_SUB" : 0
}

Das Feld, anhand dessen man numerische von nicht-numerischen Spalten unterscheiden kann, ist MYSQL_IS_NUM. Ist sein Wert 0, ist es kein numerisches Feld, bei 1 schon. Jedoch verursacht speziell die Kombination von Anforderungen, MySQL plus Perl plus JSON, noch ein weiteres Problem: Weder MySQL noch Perl kennen atomare Bool-Werte. In MySQL behilft man sich im CREATE TABLE-Statment in den allermeisten Fällen mit enum('true','false'). Jedoch kennt auch Perl keinen eigens dafür gedachten Boolean-Typ, so dass bei der JSON-Konvertierung der Bool-Wert auch hier mit Hochkommata umschlossen wird.

Das JSON-Modul aus dem CPAN sieht für Boolsche Werte hier die eigenen Typen JSON::true oder JSON::false vor. Damit man diese in einer Perl-Datenstruktur jedoch setzen kann, muss man erst herausfinden, ob eine Spalte überhaupt ein enum-Typ war. enum unterscheidet sich vom VARCHAR weder beim MYSQL_IS_NUM noch beim LITERAL_SUFFIX, das aussagt, ob der Value bei der Ausgabe in Quotes gesetzt werden soll oder nicht. Glücklicherweise gibt es hier noch das Feld MYSQL_NATIVE_TYPE, das für den enum den Wert 254, für den VARCHAR jedoch den Wert 253 trägt (für SMALLINT(5) hat es den Wert 2).

Mit diesem Wissen lässt sich nun mit ein wenig Aufwand ein korrekter JSON-String bauen, der von JavaScript-Frameworks wie beispielsweise ExtJS anstandslos geschluckt wird.

Geholfen haben mir:
http://www.mathematik.uni-ulm.de/help/perl5/doc/DBD/mysql.html
http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-CHP-9-SECT-3.html
http://cpansearch.perl.org/src/CAPTTOFU/DBD-mysql-3.0007/dbdimp.c