PostgreSQL: Entity ID GenerationType migrieren

Angenommen, man möchte die ID einer Entity von

auf

migrieren, und dazu explizit eine Sequence in der Datenbank anlegen. Ein Migrationsskript könnte dann so aussehen:

Aaaber: IDs können negativ sein:

org.postgresql.util.PSQLException: ERROR: setval: value -91 is out of bounds for sequence “foo_bar_id_sequence” (1..9223372036854775807)

😄

Naheligende Lösung wäre, das SELECT in

zu ändern. setval geht allerdings per default aber beim nächsten Wert los, im Fall “1” (wenn gar keine oder nur negative IDs vorhanden sind) also bei 2. Das ist nicht direkt schlimm, aber uncool. Eine (umständliche!) Lösung ist eine Fallunterscheidung – und falls man die Migration viele viele male durchführen muss, verpackt man die Fallunterscheidung in einer FUNCTION:

Aufruf dann per:

Deutlich einfacher ist allerdings:

FALSE sorgt dafür, dass nicht der nächste Wert genommen wird, sondern genau dieser – und “+ 1” vermeidet einen Wert von 0, der ebenfalls out of bounds wäre.

Danke an Nils für’s vereinfachen! 🙂 Die umständliche Version bleibt trotzdem online als Template für PG Functions.

Hibernate Envers Timestamp

Ein Envers Timestamp sieht in der Datenbank bsplw so aus: 1441090155727 (und ist ein Bigint). Das wäre der 30.04.47636, 06:02:07, und ich bezweifel, dass der Datenbankeintrag aus der Zukunft kommt. Die Lösung ist so naheliegend wie einfach: Die letzten drei Stellen stehen für die Millisekunden, der “korrekte” Timestamp lautet 1441090155, und das ist der 01.09.2015, 08:49:15.

MS SQL: “DROP COLUMN” mit Defaultwert

MS SQL, once again: Folgendes Skript legt eine Spalte mit Default “1” an:

Aber es legt nicht nur diese Spalte an, sondern auch einen Constraint “DF__lc_storea__isPri__6A85CC04”. Möchte man diese Spalte nun wieder löschen:

dann meldet MS SQL Erfolg (!), tatsächlich aber wurde die Spalte nicht gelöscht m( Besonders schön in Migrationsskripten. Nicht.

Man kann den Constraint nun explizit löschen:

Oder man sucht ihn dynamisch aus den Tiefen des Systems:

HTH

MS SQL: Alle Tabellen leeren

Ich möchte das nicht jedes mal googlen:

In dem Zusammenhang: Flyway initOnMigrate in Play Applikationen:

MS SQL: Datenbanken migrieren

Komplette MS SQL-Datenbanken von einem Server auf den anderen zu migrieren, ist nicht so einfach große Scheiße.

Das geht damit los, dass nicht offensichtlich ist, wie man die Daten aus der Datenbank herausbekommt. Es gibt im “Microsoft SQL Server Management Studio” (alleine der Name!):

  • Rechtsklick -> Tasks -> Daten exportieren (“Export Data”). Hier kann man nur tabellenweise exportieren.
  • Rechtsklick -> Tasks -> Sichern (“Backup”). Hier wird (trotz “Sicherungstyp: Vollständig”) nur das Schema exportiert.
  • Rechtsklick -> Tasks -> Skripts generieren (“Generate Scripts”): Erzeugt eine .sql-Datei, die auch nur dann die Daten enthält (und nicht nur das Schema), wenn unter Erweitert -> Datentypen, für die ein Skript erstellt wird (“Types of data to script”) “Schema und Daten” ausgewählt wird.

Es geht damit weiter, dass .sql-Dateien ab einer gewissen Größe nicht vom Management Studio verarbeitet werden können – sie werden exportiert, aber sie werden nicht wieder importiert. Das geht nur (?) über die Konsole:

Dabei wurde bei mir allerdings die Tabelle dbo.schema_version nicht mit migriert, was Flyway aus dem Konzept bringt. Will man die Tabelle (oder jede andere?) manuell migrieren, muss man darauf achten, welche Sprache eingestellt ist. Kein Witz: Ist der Quell-Server Englisch, der Zielserver aber Deutsch, kann es einen

Meldung 242, Ebene 16, Status 3, Zeile 3
Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs.

(The conversion of a varchar data type to a datetime data type resulted in an out-of-range value) geben. Der Grund: Ein

ist englische Schreibweise (Monat vor Tag), es muss dann

heißen… klar: Die Syntax des Skripts ist natürlich abhängig von der Sprache des Clients, nichts liegt näher m(

UPDATE: Unnötig zu sagen, dass der Import per Konsole unendlich lange dauert.

Spring Data JPA: Subqueries

JPQL-Subqueries können nicht im FROM stehen – aber Native Queries haben diese Einschränkung nicht (zwingend). Klar, denn native Queries sind eben nativ, sprich in der Sprache der jeweiligen Datenbank. Wer also damit leben kann, die Portabilität zu verlieren, der kann wie folgt “beliebige” Queries verwenden:

MySQL vs. UTF-8 (vs. CodeIgniter)

MySQL kann UTF-8… falsch. Besser gesagt: Ungenau. MySQL kann UTF-8, aber das heißt da anders:

Ein Zeichen in UTF-8 ist (bis zu) vier Bytes lang, “UTF-8” in MySQL kann aber nur bis zu drei. Deswegen fällt der Unterschied oft gar nicht auf, oder erst beim ersten Zeichen, das vier Bytes braucht. Will man echtes UTF-8, muss man einiges umkonfigurieren; in Kurzform:

  1. SQL muss in der Version 5.3.3+ vorhanden sein, siehe Link oben
  2. Jede Datenbank, jede Tabelle (ggf. jede Spalte) müssen auf “CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci” umgestellt werden
    • Dabei zu beachten: InnoDB kann wohl nur 767 Bytes pro Index Schlüssel. Wenn man vier (statt drei) Bytes pro Zeichen rechnet, sind das bsplw. nur 191 (statt 255) Zeichen!
    • Das gilt auch für Typen wie TINYTEXT, die wohl nur 255 Bytes = 63 Zeichen bei 4 Byte/Zeichen speichern können. Ggf. ist hier ein anderer Typ zu wählen
  3. ggf. Server, Client, etc. umstellen (dabei hilft “SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';“)
  4. ggf. Datenbank reparieren und optimieren (war bei mir nicht nötig, ich habe alle Tabellen neu angelegt):

bzw.

Insbesondere bei Punkt 3 hängt es bei CodeIgniter, btw. Wenn man in der Datenbank-Config

 stehen hat, dann ist das ebenfalls nicht das echte UTF-8. Stattdessen korrekt ist:

Update

Das Problem betrifft auch mysqldump: Will man die eine utf8mb4-Datenbank dumpen, muss man das explizit sagen, denn mysqldump nutzt per Default nur utf8 (Quelle):

Das ist so tief verankert, dass auch Dumps über Tools wie Sequel Pro nur utf8 nutzen m(

WordPress: Posts sortieren über mehrere Custom Fields

In WordPress kann man eigene Daten zu jedem Post speichern, so genannte Custom Fields. Technisch funktioniert das so, dass pro Post und Field ein Eintrag in der Tabelle wp_postmeta angelegt wird.

Beispiel: Ein Rating-Plugin könnte pro bewertetem Post einen Eintrag <meta_key:rating> mit der Gesamtzahl an “Sternen” (1=schlecht, 5=super), und einen Eintrag <meta_key:raters> mit der Anzahl an abgegebenen Stimmen anlegen. Also zwei Zeilen in wp_postmeta pro Post, für den Stimmen abgegeben wurden. Genau so macht es “Post Rate“. Anmerkung: Man speichert das schon deshalb als zwei diskrete Werte, um bei der nächsten Bewertung wieder den Durchschnitt berechnen zu können – ich musste kurz überlegen, bis mir das klar wurde 🙂

So, nun hat man in dem Beispiel bewertete Posts, und möchte diese Posts vielleicht nach ihrer Bewertung sortiert anzeigen. Kann ja nicht so schwer sein, schließlich gibt es zB $wpdb mit get_results(). Zu einem Problem wird das (für jemanden, dessen SQL-Kenntnisse etwas eingerostet sind mich) dadurch, dass es eben zwei Einträge in wp_postmeta gibt – und beide speichern ihren Wert in wp_postmeta.meta_value! Für eine Sortierung müsste man die Bewertung (stehen unter <meta_key:rating> in meta_value) durch die Anzahl an Stimmen (stehen unter <meta_key:raters> in meta_value) teilen.

Lösung: JOIN. Erst habe ich einen LEFT JOIN versucht geklaut, aber der definiert sich so, dass er alle Posts ausgegeben hätte, selbst wenn für sie gar kein Rating abgegeben wurde. Deshalb also nur JOIN. Basierend auf dem Foreneintrag mit dem LEFT JOIN ist dabei folgendes entstanden:

Etwas wie AS lag mir schon auf der Zunge, allerdings eher als SELECT AS… das in einem (bzw. zwei) JOIN zu verwenden, und zwar sowohl in dem JOIN (“rating.post_id”), wie auch im ORDER BY hätte ich nicht hinbekommen. In diesem Sinne: HTH 🙂

PS: Die formatierte Ausgabe ergibt sich dann aus dem bereits erwähnten Eintrag.

SQL: Custom Order

Normalerweise sortiert man Datensätze alphabetisch oder numerisch oder so. Will man eine bestimmte Gruppe von Datensätzen (z.B. alle mit category 4) ganz oben sehen (statt an vierter Stelle), so nutze man CASE:

Das Ganze macht, dass die Datensätze in zwei Kategorien eingeteilt werden, nämlich “1” und “2”. Nach diesen wird (vor-)sortiert, jede weitere Sortierung erfolgt dann innerhalb dieser Kategorien.

HTH