SQL-Adapter (mssql) triggerbar machen
-
Hallo,
ich bin dabei, einige meiner eigenen Entwicklungen mit ioBroker zu verbinden. Natürlich werde ich die hier dann zur Verfügung stellen. Es geht um diverse hersteller- und technologieunabhängige SQL-Module bis hin zu kognitiven Verfahren, alles SQL-basierend. Der SQL-Adapter stellt hierzu eine hervorragende Input-Schnittstelle dar, inkl. bereits vorhandener Administration der Datenübernahme!
Ich habe mich (schon vor Jahren) für MS-SQL entschieden, weil ich nur hier ein funktional vollumfängliches professionelles System mit all seinen Vorteilen haben kann, dass dennoch sogar auf einem PC installierbar ist, und das als als frei verfügbare "Express"-Version das auch noch für lau. Die einzig vielleicht (hier) relevante Einschränkung dieser SQL-Version "max 10GB Datenbank-Größe" ist nicht wirklich eine Hürde, wenn man diese richtig liest: "je Datenbank"! Man kann nun beliebig viele Datenbanken auf einem DB-Server anlegen und diese auch crossover arbeiten lassen…. IO-Daten von Archiven, Konfigurationen, Kernel usw. trennen...
...und dann gibts auch noch die Instanzen eines DB-Servers als weiteren Multiplikator. Voila!Dabei bin ich nun aber leider auf ein Problem gestossen. Nicht wirklich ein Bug für die bisherigen systeminternen Graphenauswertungen, aber ein (simpel behebbarer) Bug für die sonstige Weiterverarbeitung in SQL:
BLOB-Datentypen, hier: der alte Datentyp "TEXT" sind leider nicht triggerbar, d.h. Spalten dieses Typs dürfen in Folgeprozesse triggernden Tabellen (was Voraussetzung ist für Echtzeitbehandlung) nicht auftreten!
Betroffen ist die "val"-Spalte in der Tabelle "ts_string", dummerweise ausgerechnet die liefernde Werte-Spalte für alle (nichtnumerischen/-boolschen) Inhalte.
Die Behebung ist aber einfach: Einmalig den Datentyp TEXT in NVARCHAR(max) ändern.
Dieser neue Datentyp ist ansonsten funktional abwärtskompatibel zu TEXT.
Das geht auch nachträglich bei bereits gefüllten Tabellen, bei mir selbst bei knapp einem GB Daten, problemlos und schnell.
Ich habe gleich NVARCHAR statt VARCHAR verwendet (also die Unicode-Variante), um auch gleich die Speicherung Unicode-konformer Inhalte in der DB zu ermöglichen.
Bisherige Tests über mehrere Wochen und viele Graphen zeigen mir, dass diese Änderung bzgl. der bisherigen Funktion im ioBroker offenbar problemlos möglich ist, also keine Auswirkungen auf sonstige Funktionen bzw. die Verwendung des Adapters hat.
Natürlich kann das nun abschließend und endgültig nur der eigentliche Entwickler des Adapters einschätzen, worum ich hiermit bitte.
Wenn das ok wäre: Vielleicht könnte man diese Anpassung des Datentyps dann auch gleich bei der Adapter-Installation vornehmen?
Ansonsten habe auch noch einige zusätzliche Anpassungen gemacht, um die Verarbeitung erheblich zu beschleunigen. Stichwort Indizierung und Zuweisung der fehlenden Primäschlüssel bei den Datentabellen. Das wirkt nun auch bei den "klassischen" Anwendungen des Adatpters (FLOT).
Bei den PKs habe ich auf deren wirkliche Vernetzung mit FKs in den referierenden Tabellen verzichtet, um evtl. Daten-Abbauprozess (die diese erst wieder auflösen oder zumindest bzgl. Reihenfolge beachten müßten) nicht zu gefährden. Somit ist die Konsistenz-stabilisierende Wirkung der Schlüssel noch offen (gabs bisher aber auch noch nicht), jedoch wirken bereits die beschleunigenden Effekte.
Angewendet habe ich die SQL-Scripte bei Version MSSQL 2014. Sollten aber auch schon ab 2012 gehen. Entscheidend ist die Verfügbarkeit des Datentyps nvarchar(max). Die Indizes und PK's gibts "schon immer", kenne die schon seit SQL 6.5 (ca. 1998)
Die Scripts sind aufwandsvermeidend NICHT auf WIEDERHOLBARKEIT getrimmt, bringen also Fehlermeldungen bei wiederholtem Ausführen, wenn die Objekte schon (oder nicht mehr) vorhanden sind. In einem solchen Fall einfach die iobroker-Datenbank selbst löschen und durch den Adapter bzw. Recover des vorher hoffentlich gespeichertem (!!) SQL-Dumps neu anlegen lassen. (noch schneller gehts mit Backup per: 1. DB stoppen, 2. DB-Files wegspeichern, 3. DB wieder starten, Recover: das ganze revers)
hier nun the codes:
Datentyp ändern:
!
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_ts_string ( id int NULL, ts bigint NULL, val nvarchar(MAX) NULL, ack bit NULL, _from int NULL, q int NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE dbo.Tmp_ts_string SET (LOCK_ESCALATION = TABLE) GO IF EXISTS(SELECT * FROM dbo.ts_string) EXEC('INSERT INTO dbo.Tmp_ts_string (id, ts, val, ack, _from, q) SELECT id, ts, CONVERT(nvarchar(MAX), val), ack, _from, q FROM dbo.ts_string WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.ts_string GO EXECUTE sp_rename N'dbo.Tmp_ts_string', N'ts_string', 'OBJECT' GO COMMIT !optional und unabhängig davon:
Fehlende Indizes und PK in Datentabellen ergänzen (für beschleunigte SQL-Verarbeitung):
!
--======================================================================== -- Ergänzungen an ioBroker-Tabellen -- - echte PK-Spalte (_id) -- - Indizes an FK-Spalten (id --> dp, _from --> src) -- -- Rel. 1 hb/... 14.03.2017 --======================================================================== ! -- ts_bool ! /* Überprüfen Sie das Skript gründlich, bevor Sie es außerhalb des Datenbank-Designer-Kontexts ausführen, um potenzielle Datenverluste zu vermeiden.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.ts_bool ADD _id uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_ts_bool__id DEFAULT newid() GO DECLARE @v sql_variant SET @v = N'PK' EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'ts_bool', N'COLUMN', N'_id' GO ALTER TABLE dbo.ts_bool ADD CONSTRAINT PK_ts_bool PRIMARY KEY CLUSTERED ( _id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ! GO CREATE NONCLUSTERED INDEX IX_ts_bool_dpID ON dbo.ts_bool ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_ts_bool_srcID ON dbo.ts_bool ( _from ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE dbo.ts_bool SET (LOCK_ESCALATION = TABLE) GO COMMIT GO ! -- ts_numbers ! /* Überprüfen Sie das Skript gründlich, bevor Sie es außerhalb des Datenbank-Designer-Kontexts ausführen, um potenzielle Datenverluste zu vermeiden.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.ts_number ADD _id uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_ts_number__id DEFAULT newid() GO DECLARE @v sql_variant SET @v = N'Primary Key' EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'ts_number', N'COLUMN', N'_id' GO ALTER TABLE dbo.ts_number ADD CONSTRAINT PK_ts_number PRIMARY KEY CLUSTERED ( _id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ! GO CREATE NONCLUSTERED INDEX IX_ts_number_dtID ON dbo.ts_number ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_ts_number_srcID ON dbo.ts_number ( _from ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE dbo.ts_number SET (LOCK_ESCALATION = TABLE) GO COMMIT GO ! -- ts_string ! BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.ts_string ADD _id uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_ts_string__id DEFAULT newid() GO DECLARE @v sql_variant SET @v = N'PK' EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'ts_string', N'COLUMN', N'_id' GO ALTER TABLE dbo.ts_string ADD CONSTRAINT PK_ts_string PRIMARY KEY CLUSTERED ( _id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ! GO CREATE NONCLUSTERED INDEX IX_ts_string_dtID ON dbo.ts_string ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_ts_string_srcID ON dbo.ts_string ( _from ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE dbo.ts_string SET (LOCK_ESCALATION = TABLE) GO COMMIT GO !Falls das hier doch off topic sein sollte: Bitte verschieben!
Viele Grüße bb61
-
Hi,
vielen Dank. Ist zwar kein "Fehler", aber ich denke ok
Ab welchen MSSQL-Versionen ist denn VARCHAR vorhanden? Welche Versions-Abhängigkeit würde man sich mit einer solchen Änderung reinholen? Ist es die 2012er wie oben steht?
Mein Ansatz (ich bin der aktuelle Maintainer des sql-Adapters) wäre im ersten Schritt ein Update des Adapters das er je nach Antwort auf die Frage oben) neue Tabellen auf deinem "new Style" hin anlegt. Da wäre super wenn Du mir sagen könntest wie ich https://github.com/ioBroker/ioBroker.sq … ssql.js#L7 und folgende anpassen muss das es "new style" gleich korrekt anlegt. Am besten inklusive der Indizes und so
Änderungen gern als Code hier im Forum oder als Pull-Request im Github
Für bestehende DBs würe ich deine "Update-Skripte" in die Github-README packen, sodass jeder das selbst machen kann. Das finde ich irgendwie gerade gefährlich weil ich es nicht im Adapter-Kontext sinnvoll testen kann und man da schon wissen sollte was man tut und so.
Was hältst Du davon?
-
Hi apollon77,
das können wir gerne so machen. Muss jetzt aber erst noch ein wenig arbeiten, heute Abend… mehr.
Zum Testen habe ich notfalls ne ganze Firma voller Server (inkl. "Erlaubnis, das zu tun"). ..oder meinen privaten in meinem Heim.
...VARCHAR gibt es schon ewig, kann aber nur max 8000 Zeichen speichern je Record (NVARCHAR max 4000), neu ist aber (N)VARCHAR(max) als "neuer BLOG-Datentyp" ohne Maximalbegrenzung. Ich schau mal heute abend bzgl. konkreter Einführung.
Mit github habe ich absolut noch keine praktischen Erfahrungen. Ausser durch einige Downloads für ioBroker die letzten Wochen. Ich verstehe aber den Ansatz und alles läßt sich lernen. ...unsere Kunden lieben nicht so das Öffentliche, deshalb haben wir intern eigene CVS-Lösungen. ...und sie sind mit Microsoft verheiratet, da nimmt man selbst auch sonst das, was man seit Jahrzehnten kennt / drauf geschult ist / Lösungen rumliegen hat.
Die Scripte oben sind übrigens zumeist sogar zeitoptimierend servergeneriert. Da ist noch viel Redundanz drin, der sich bei initialer Anwendung sicher vermeiden läßt.
Bis dann bb61
-
kurze Mittagpausen-Beschäftigung:
Ab welchen MSSQL-Versionen ist denn VARCHAR vorhanden? Welche Versions-Abhängigkeit würde man sich mit einer solchen Änderung reinholen? Ist es die 2012er wie oben steht? `
lt. MS-Technet: ab MSSQL 2005, siehe das Popup im Bild (etwa Bildmitte "Andere Versionen")
Auf Servern testen kann ich das hier bis zurück zu SQL 2012.
Anwenderseitiges Upgrade darauf sollte aber sicher unspektakulär sein bei der kostenfreien Express-Version und der ansonsten bisherigen ausschliesslichen Verwendung als History-Speicher.
–--
Ich optimiere die Scripts dann mal für direkte initiale Einbindung bzw. auf Wiederholbarkeit bei nachträglicher Anwendung
VG bb61
2273_iobroker-nvarchar-frage.png -
Hey, ich habs mal ins Trello gepackt das das Thema nicht runterfällt.
Meine Idee wäre:
-
Optimierungen für die Neuanlage von DBs in die Create-Skripte für alle Datenbanktypen einbauen, wo relevant
-
Anleitung im Github wie bestehende DBs "aufgerüstet" werden können (ein "Upgrade" im Code ist mir zu gefährlich weil es sehr schwierig testbar ist und bei den Kunden sehr viele Dinge passieren können.
-
-
Hallo bb61,
vielen Dank für die Scripte. Nachdem ich auch schon des Öfteren gegen die Wand der nicht vorhandenen PrimaryKeys gelaufen bin wenn ich schnell mal eine Auswertung über ODBC in diversen Programmen fahren wollte habe ich mir heute mal etwas Zeit genommen und war schon fast dabei selbst sowas zu bauen. Habe aber dann glücklicherweise nochmal die Forensuche angeworfen und bin auf deinen Beitrag gestoßen. Nochmals vielen Dank dafür - hat mir einige Stunden Arbeit erspart. Die Scripte sind problemlos durchgelaufen, bis auf einen Timeout bei der ts_number-Tabelle. Da sind halt doch schon ziemlich viele Datensätze drin. Die Timeout-Grenze kann man ja aber hochsetzen…
Ich wäre auch stark dafür, das mit in den Standard für Neuanlagen einzubauen. Tabellen ohne PK anzulegen ist ja doch schon ein wenig "suboptimales" Datenbankdesign.
Jetzt habe ich aber noch eine Frage. Wie wandelst Du denn die "ts"-Spalte ressourcenschonend in ein "lesbares" Datumsformat um?
Gruß Thilo
