Postgres direkt aus Gradle

import groovy.sql.Sql

import java.sql.Driver
import java.sql.DriverManager

configurations {
  driver
}

buildscript {
  ext.postgresVersion = '42.1.1'

  dependencies {
    classpath("org.postgresql:postgresql:${postgresVersion}")
  }
}

dependencies {
  driver "org.postgresql:postgresql:${postgresVersion}"
}

// register jdbc drivers:
URLClassLoader loader = GroovyObject.class.classLoader
configurations.driver.each { File file ->
  loader.addURL(file.toURI().toURL())
}
Class driverClass = loader.loadClass('org.postgresql.Driver')
Driver driverInstance = driverClass.newInstance()
DriverManager.registerDriver(driverInstance)

task fooBar() {
  doLast {
    def sql = Sql.newInstance('jdbc:postgresql://localhost:5432/database', 'user', 'password')
    sql.execute(/* ... */)
    sql.close()
  }
}

import groovy.sql.Sql

import java.sql.Driver

import java.sql.DriverManager

configurations {

driver

}

buildscript {

ext.postgresVersion = '42.1.1'

dependencies {

classpath("org.postgresql:postgresql:${postgresVersion}")

}

dependencies {

driver "org.postgresql:postgresql:${postgresVersion}"

}

// register jdbc drivers:

URLClassLoader loader = GroovyObject.class.classLoader

configurations.driver.each { File file ->

loader.addURL(file.toURI().toURL())

}

Class driverClass = loader.loadClass('org.postgresql.Driver')

Driver driverInstance = driverClass.newInstance()

DriverManager.registerDriver(driverInstance)

task fooBar() {

doLast {

def sql = Sql.newInstance('jdbc:postgresql://localhost:5432/database', 'user', 'password')

sql.execute(/* ... */)

sql.close()

}

im Wesentlichen von hier.

PostgreSQL: Entity ID GenerationType migrieren

Angenommen, man möchte die ID einer Entity von

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "foo_bar_sequence")

1 2	@Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "foo_bar_sequence")

auf

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)

1 2	@Id @GeneratedValue(strategy = GenerationType.IDENTITY)

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

CREATE SEQUENCE foo_bar_id_sequence OWNED BY foo_bar.id;
ALTER TABLE foo_bar ALTER COLUMN id SET DEFAULT nextval('foo_bar_id_sequence');
SELECT setval('foo_bar_id_sequence', (SELECT max(id) FROM foo_bar));

CREATE SEQUENCE foo_bar_id_sequence OWNED BY foo_bar.id;

ALTER TABLE foo_bar ALTER COLUMN id SET DEFAULT nextval('foo_bar_id_sequence');

SELECT setval('foo_bar_id_sequence', (SELECT max(id) FROM foo_bar));

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

SELECT setval('foo_bar_id_sequence', (SELECT greatest(max(id), 1) FROM foo_bar));

1	SELECT setval('foo_bar_id_sequence', (SELECT greatest(max(id), 1) FROM foo_bar));

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:

CREATE OR REPLACE FUNCTION safeSetval(_tbl REGCLASS) RETURNS VOID AS
$func$
  DECLARE maxId INT;
BEGIN
  EXECUTE FORMAT('SELECT max(id) FROM %s;', _tbl) INTO maxId;
  CASE
    WHEN maxId ISNULL OR maxId < 0
    THEN PERFORM setval(FORMAT('%s_id_sequence', _tbl), 1, FALSE);
  ELSE
    PERFORM setval(FORMAT('%s_id_sequence', _tbl), maxId);
  END CASE;
END $func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safeSetval(_tbl REGCLASS) RETURNS VOID AS

$func$

DECLARE maxId INT;

BEGIN

EXECUTE FORMAT('SELECT max(id) FROM %s;', _tbl) INTO maxId;

CASE

WHEN maxId ISNULL OR maxId < 0

THEN PERFORM setval(FORMAT('%s_id_sequence', _tbl), 1, FALSE);

ELSE

PERFORM setval(FORMAT('%s_id_sequence', _tbl), maxId);

END CASE;

END $func$ LANGUAGE plpgsql;

Aufruf dann per:

SELECT safeSetval('foo_bar');

1	SELECT safeSetval('foo_bar');

Deutlich einfacher ist allerdings:

SELECT setval('foo_bar_id_sequence',
  (SELECT greatest(max(id) + 1, 1) FROM foo_bar), FALSE);

1 2	SELECT setval('foo_bar_id_sequence', (SELECT greatest(max(id) + 1, 1) FROM foo_bar), FALSE);

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.

PostgreSQL: INSERT SELECT mit Counter

Flyway-Migrationsskript: Ich möchte Spalte bar aus mehreren Tabellen in eine neue Tabelle foo einfügen – plus einer fortlaufenden Zeilennummer:

insert into foo (name, sort_index)
  (select bar as name, row_number() over() as sort_index from
    (select table_a.bar from table_a union select table_b.bar from table_b) tmp);

insert into foo (name, sort_index)

(select bar as name, row_number() over() as sort_index from

(select table_a.bar from table_a union select table_b.bar from table_b) tmp);

utf8mb4 auf uberspace

Es war vor gut zwei Jahren, dass mir das Thema utf8mb4 auffiel. Ein Thema, das auch meinen Hoster uberspace betrifft, denn dort kommt – CentOS 6 sei “Dank” – MySQL 5.1.73 zum Einsatz, und utf8mb4 gibt es erst ab 5.5.3. Was ich leidvoll feststellen musste, als WordPress einen Post nur bis zu einem eingefügten Emoji gespeichert hat, alles darüber hinaus war (stillschweigend!) flöten. Damals wurde ich auf CentOS 7 vertröstet, das das dann können sollte.

Am 16.2. dieses Jahres kam dann der erlösende Blogpost: uberspace unterstützt MariaDB, und zwar in der “zu MySQL 5.5 kompatiblen” Version 10.0 – trotz CentOS 6:

eine Übergangslösung, die nur für Leute gedacht ist, die zwingend hier und jetzt unbedingt MySQL 5.5 bzw. etwas dazu Kompatibles brauchen.

“Hier und jetzt”, eh, ja. In dem Zusammenhang:

Sobald unsere CentOS-7-Hosts am Start sind […]

Dazu gibt es immer noch keinen Zeitplan, Stand 8. April 2016. Aber zurück zum Thema: Das Wiki sagt

Mit dem Befehl uberspace-setup-mariadb kannst du dir eine Datenbank auf dem Host anlegen. Die Zugangsdaten werden in ~/.my.mariadb.cnf abgelegt.

, was bei mir zuerst nicht geklappt hat:

[crusy@circinus ~]$ uberspace-setup-mariadb
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

1 2	[crusy@circinus ~]$ uberspace-setup-mariadb ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

Nachdem der Support tätig wurde, war der Fehler zwar weg, aber erwähnte .my.mariadb.cnf leer. Inzwischen läuft das aber rund (Danke an den Support an dieser Stelle für die schnelle Reaktion), deshalb hier die vollständige Migration:

// 1. Datenbank aus MySQL exportieren:
[crusy@circinus ~]$ mysqldump -u crusy -pMeinMysqlPasswort crusy_blog > blog.sql

// 2. Datenbank in MariaDB anlegen:
[crusy@circinus ~]$ uberspace-setup-mariadb 
[crusy@circinus ~]$ mysql --defaults-file=~/.my.mariadb.cnf 
mysql> CREATE DATABASE crusy_blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> exit

// 3. MySQL-Dump in MariaDB importieren:
[crusy@circinus ~]$ mysql --defaults-file=~/.my.mariadb.cnf crusy_blog < blog.sql

// 4a. Zugangsdaten für MariaDB ansehen:
[crusy@circinus ~]$ vi .my.mariadb.cnf

// 4b. WordPress auf MariaDB umschalten:  
[crusy@circinus ~]$ vi virtual/blog.crusy.net/wp-config.php
// zu ändern ist konkret:
// define('DB_HOST', '127.0.0.1:3307'); // NICHT localhost:3307
// define('DB_PASSWORD', 'Passwort aus .my.mariadb.cnf');
// define('DB_CHARSET', 'utf8mb4');
// define('DB_COLLATE', 'utf8mb4_unicode_ci'); // '' scheint auch zu funktionieren

// 5. dump löschen:
[crusy@circinus ~]$ rm blog.sql

// 1. Datenbank aus MySQL exportieren:

[crusy@circinus ~]$ mysqldump -u crusy -pMeinMysqlPasswort crusy_blog > blog.sql

// 2. Datenbank in MariaDB anlegen:

[crusy@circinus ~]$ uberspace-setup-mariadb

[crusy@circinus ~]$ mysql --defaults-file=~/.my.mariadb.cnf

mysql> CREATE DATABASE crusy_blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysql> exit

// 3. MySQL-Dump in MariaDB importieren:

[crusy@circinus ~]$ mysql --defaults-file=~/.my.mariadb.cnf crusy_blog < blog.sql

// 4a. Zugangsdaten für MariaDB ansehen:

[crusy@circinus ~]$ vi .my.mariadb.cnf

// 4b. WordPress auf MariaDB umschalten:

[crusy@circinus ~]$ vi virtual/blog.crusy.net/wp-config.php

// zu ändern ist konkret:

// define('DB_HOST', '127.0.0.1:3307'); // NICHT localhost:3307

// define('DB_PASSWORD', 'Passwort aus .my.mariadb.cnf');

// define('DB_CHARSET', 'utf8mb4');

// define('DB_COLLATE', 'utf8mb4_unicode_ci'); // '' scheint auch zu funktionieren

// 5. dump löschen:

[crusy@circinus ~]$ rm blog.sql

Et voilà: Von 💩 zu 👍 in fünf einfachen Schritten! 😊 ~~Dasselbe jetzt noch für meinen Feedreader! 💪~~

UPDATE

Meh: Fever “kann” das nicht. Erstens ist utf8 dort hart im Code verdrahtet, zweitens ist bsplw. der Titel eines Eintrags ein varchar(255) – und ein Key:

255 mal 4 Byte (wie in utf8mb4) sind aber mehr als 1000 Byte, und das ist das Limit für einen Schlüssel m( Change Request bei Fever läuft.

UPDATE

Ich empfehle Super Emoji Plus+!

UPDATE

uberspace zum Thema “~~CentOS~~ uberspace 7″ und Zeitplänen – für solche Ansagen mag ich die Jungs 😉

UPDATE

Beim Update auf eine neuere MariaDB-Version hat sich die Interpretation von “utf8” seitens MariaDB “geändert”, jetzt wird daraus utf8_mb3 (nicht 4!). Dadurch gehen (wieder) alle Emojis kaputt. Man muss man jede Spalte (🤯) explizit von “utf8mb3_general_ci” auf “utf8mb4_general_ci” ändern – ich empfehle so.

Postgres: DB leeren

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

DROP SCHEMA public CASCADE;

CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO public;

COMMENT ON SCHEMA public IS 'standard public schema';

Quelle

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: Aktuellen Autoincrement für bestimmte Tabelle

Man liest oft

SELECT SCOPE_IDENTITY();

1	SELECT SCOPE_IDENTITY();

Das funcktioniert aber nur bsplw. nach einem SELECT. Genereller funktioniert dagegen

/* current: */
SELECT IDENT_CURRENT('tablename');

/* next: */
SELECT IDENT_CURRENT('tablename') + IDENT_INCR('tablename');

/* current: */

SELECT IDENT_CURRENT('tablename');

/* next: */

SELECT IDENT_CURRENT('tablename') + IDENT_INCR('tablename');

von

MS SQL: “DROP COLUMN” mit Defaultwert

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

ALTER TABLE lc_storeasset ADD isPrimary bit NOT NULL DEFAULT 1;

1	ALTER TABLE lc_storeasset ADD isPrimary bit NOT NULL DEFAULT 1;

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:

ALTER TABLE lc_storeasset DROP COLUMN isprimary;

1	ALTER TABLE lc_storeasset DROP COLUMN isprimary;

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:

ALTER TABLE lc_storeasset DROP CONSTRAINT DF__lc_storea__isPri__6A85CC04;

1	ALTER TABLE lc_storeasset DROP CONSTRAINT DF__lc_storea__isPri__6A85CC04;

Oder man sucht ihn dynamisch aus den Tiefen des Systems:

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[dbo].[lc_storeasset]') AND [name] = 'isprimary';
IF @ObjectName != '' BEGIN
  EXEC('ALTER TABLE [dbo].[lc_storeasset] DROP CONSTRAINT ' + @ObjectName)
END

DECLARE @ObjectName NVARCHAR(100)

SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS

WHERE [object_id] = OBJECT_ID('[dbo].[lc_storeasset]') AND [name] = 'isprimary';

IF @ObjectName != '' BEGIN

EXEC('ALTER TABLE [dbo].[lc_storeasset] DROP CONSTRAINT ' + @ObjectName)

END

HTH

MS SQL: Alle Tabellen leeren

Ich möchte das nicht jedes mal googlen:

-- disable referential integrity check:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

-- re-enable referential integrity check:
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO

-- disable referential integrity check:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

-- re-enable referential integrity check:

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

In dem Zusammenhang: Flyway initOnMigrate in Play Applikationen:

flyway.initOnMigrate=true

1	flyway.initOnMigrate=true

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:

// Achtung: Ohne "-o" am Ende:
sqlcmd -S <server> -i C:\<your file here>.sql

1 2	// Achtung: Ohne "-o" am Ende: sqlcmd -S <server> -i C:\<your file here>.sql

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

CAST(N'2015-02-17 12:55:56.697' AS DateTime)

1	CAST(N'2015-02-17 12:55:56.697' AS DateTime)

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

CAST(N'2015-17-02 12:55:56.697' AS DateTime)

1	CAST(N'2015-17-02 12:55:56.697' AS DateTime)

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.

crusy.net

♪ Commit ins Abenteuerland ♫

Category Archives for SQL

Postgres direkt aus Gradle

PostgreSQL: Entity ID GenerationType migrieren

PostgreSQL: INSERT SELECT mit Counter

utf8mb4 auf uberspace

UPDATE

UPDATE

UPDATE

UPDATE

Postgres: DB leeren

Hibernate Envers Timestamp

MS SQL: Aktuellen Autoincrement für bestimmte Tabelle

MS SQL: “DROP COLUMN” mit Defaultwert

MS SQL: Alle Tabellen leeren

MS SQL: Datenbanken migrieren