10. Jan. 2013

14. Januar 2013

The SystemTables-component as of today supports external data-sources. Screenshot, Download

SQL datatypes: numbers into names

DE_flag GB_flag

for english readers

Ich habe es mir schon ewig gewünscht, nicht alle Daten in einem Datenfile (*.4DD) haben zu müssen. Das wäre sehr praktisch um Einstellungsdaten, Lookup-Tabellen, Archive und Dokumente/Bilder auszulagern. Das soll jetzt mit V12 und Zwischendurch-Zugriff auf andere 4D Datenbanken (*.4DB) möglich sein. Einschränkung: nur via SQL.

Bisher habe ich benötigte Daten einer Komponente in Blobs gepackt und in den Resources-Ordner ablegt. Ich möchte umstellen auf externe Daten. Dann ist alles schön verpackt und es sind weder Ordner-Struktur noch Benamung zu erfindenden.

Die Datenstruktur ändert sich während der Entwicklung vom theoretischen ins praktische Modell. Also pflege ich die Datenstruktur in 4D. Nach einer Änderung an der Struktur nutze ich System Tables, um die Struktur in der externen Datenbank mit SQL-Befehlen nachzubauen. Naheliegend, oder?

Den Weg beschreibt Charlie Vass in TN 10-26_External_DB. Meine Lösung will ich wie dort vorgeschlagen organisieren: die externe Datenbank ist eine Kopie der internen. Schon beim Studieren der TN stolperte ich über viele Fehler, über Ungenauigkeiten und ein Durcheinander der Begriffe. Auch die hat niemand Korrektur gelesen.

Die in der TechNote vorgestellte Theorie in ein funktionierendes Programm umzusetzen ist stolperig, hat scheint's niemand vor der Veröffentlichung getestet. System Tables liefert in _USER_COLUMS sowohl den Feldnamen und den Feldtyp als Zahlenwert. Es gibt sowohl die bekannte 4D-Nummer (nicht dokumentiertes Array: OLD_DATA_TYPE*) wie die gültige des SQL-Datentyps. Die Nummern sind nicht identisch! Das machte sicherlich Sinn, würden sie nicht überlappen. Doch der SQL-Befehl CREATE TABLE erwartet einen benannten, nicht einen genummerten Datentyp. Wo findet sich die Lookup-Tabelle zum SQL-Datentyp: Nummer entspricht Name? doc.4D.com, kb.4D.com und auch Google finden nichts, kein Kollege schreit hier.

Ergo: selber rausfinden

Nicht daß mich reengineering ausbremste … doch sobald 4D ausbaut, fange ich von vorne an. Muß nicht sein!

Praktisch, meine Komponente System_Tables hilft mir. Spalte 3 DATA_TYPES liefert mir den SQL-Datentyp und die Spalte 8 OLD_DATA_TYPE den 4D-Datentyp.

Systemtables_1

Nummer = Name ?

textHelp_SQLDatatypes

Hier das vorläufige Ergebnis: nicht auf Vollständigkeit, nur auf Praktikabilität bedacht.

Im Bild rechts meine textHelp-Funktion, die mir den SQL-Datentyp liefert.
Weil ich einen zum Datentyp passenden Leerwert brauche, wenn ich Datensätze anlegen will, liefert die Funktion diesen bei Bedarf ebenso.

Über X'' als Leerwert für einen Blob bin ich in der 4D-Dokumentation noch nicht gestolpert. Das habe ich über den SQL-Dump via SQL EXPORT DATABASE** aus 4D rausgefunden.

SQL-datatype 4D-datatype Value
number name constant empty
1 BOOLEAN Is BOOLEAN 0
4 INT32 Is Longint, Is Integer 0
6 REAL Is Real 0
10 VARCHAR Is Text ''
14 VARCHAR Is Text ''
10 VARCHAR Is Alpha Field ''
8 TIMESTAMP Is Date '0000/00/00 00:00:00:00'
9 DURATION Is Time '00:00:00:00'
12 PICTURE Is PICTURE †
13 UUID Is Alpha Field, Länge 0 ''
18 BLOB Is BLOB X''

Die '' sind zwei '. Alle Integer ändere ich in Longinteger.
† Ein leeres Bildfeld liefert diesen 4D SQL-Dump: X'54435034080000000000000000 000000000000004800000000000000 474142560100000001040000001a00000 0056672436f6c0666724c696e650766 7253706c69740476657273000c0000000 4000100000004000100000004000000 0000040001000000'.
Sieht für mich aus wie die Verwaltungsdaten eines Blobs. Brauche ich die Kopfdaten oder reicht X''? Probieren geht über studieren! Probiert: X'' reicht hin.

Fazit: SQL ist unkomfortabel: das gilt grundsätzlich und im 4D Methoden-Editor ebenso. Ja, ich weiß, manche SQL-Ausdrücke sind sehr leistungsfähig. Das ist Regex auch und ebenfalls ein Alptraum.

* das wird niemandem auffallen, der die Arrays direkt anspricht, statt über eine Listbox. Ja, ja, Korrekturlesen ist aufwendig ↵

** hier muß ich die Doku loben: ↵

Der neue Befehl SQL EXPORT DATABASE exportiert alle Datensätze von allen Tabellen der Datenbank in SQL Format. In SQL lautet diese globale Exportoperation "Dump".

DE_flag GB_flag

lieber auf deutsch

SQL datatypes: numbers into names

For long time I've been whishing not to keep all data in one datafile (*.4DD). This would be nice to keep preferences, lookup-tables, archives and documents/pictures at a separate place. Now starting with V12 an ad-hoc access to other 4D databases (*.4DB) is possible. Constrain: only via SQL.

Up to now I save component-data as blobs into the Resources-folder. I'd like to change that and store the data into an external datafile. That would package nicely and I save myself developing a folder-structure and naming-system.

The data-modell usually changes from theory to practical while developing. So I'll update the database-structure inside 4D. Any change will trigger via the System Tables-commandset. That way, I could keep the external database in sync utilizing 4Ds SQL. Obvious, isn't it?

Charlie Vass describes how in TN 10-26_External_DB. My solution will follow his example: the external database will be a copy the internal. While studying the TN I stumbled across errors, impreciseness and a mess of naming. Nobody proofread that TN?

Transfering the theory presented in the TN into a working system is stumble-some, too. Nobody tested before publishing! System tables delivers as part of _USER_COLUMS fieldname and the numeric value of datatype. It even delivers both, the well known 4D-datatype number (undocumented array: OLD_DATA_TYPE*) and the SQL-datatype number. The numbers are different, which would make sense if they wouldn't overlap. The SQL-command CREATE TABLE expects a named datatype, not the numbered one. Where is that lookup-table for SQL-datatypes: number equals name? Neither doc.4D.com, kb.4D.com or even Google finds anything, nor does a colleague answer the question.

Ergo: help yourself

Reengineering doesn't keep me off … but as soon as 4D updates, I need to check. I don't need that, do I?

My component System_Tables comes to the rescue. Column 3 DATA_TYPES contains the SQL-datatype and column 8 OLD_DATA_TYPE the 4D-datatype.

Systemtables_1

Number = Name ?

textHelp_SQLDatatypes

Preliminary results, not aiming at completeness but at practicability.

The picture on the right shows my textHelp-function. TextHelp delivers the SQL-datatype name depending on the given datatype-number.
To keep things in one place, the function also delivers the empty-value of a datatype if asked.

Couldn't find about X'' as empty value for a blob anywhere in the documentation. Found out using SQL-Dump via SQL EXPORT DATABASE**.

Here it is nice tabulated:

SQL-datatype 4D-datatype Value
number name constant empty
1 BOOLEAN Is BOOLEAN 0
4 INT32 Is Longint, Is Integer 0
6 REAL Is Real 0
10 VARCHAR Is Text ''
14 VARCHAR Is Text ''
10 VARCHAR Is Alpha Field ''
8 TIMESTAMP Is Date '0000/00/00 00:00:00:00'
9 DURATION Is Time '00:00:00:00'
12 PICTURE Is PICTURE †
13 UUID Is Alpha Field, Länge 0 ''
18 BLOB Is BLOB X''

That '' are two '. All Integer are converted into Longinteger, my decision.
† en empty picture manifests itself as 4D SQL-Dump: X'54435034080000000000000000 000000000000004800000000000000 474142560100000001040000001a00000 0056672436f6c0666724c696e650766 7253706c69740476657273000c0000000 4000100000004000100000004000000 0000040001000000'.
Looks to me like the headerinfo of a Blob. Do I need the headerdata or is X'' sufficient? Better test than wait! Tested: X'' is sufficient.

Resumee: SQL is uncomfortable: in principal as well as as part of 4D method-editor. Yes I know, some SQL-expressions are very powerful. Same is true for Regex. Both are a nightmare!

* nobody accessing the arrays by code will find out. Better use a listbox and see. Yes, proofreading is costly ↵

** my respect for the docu in this case: ↵

The SQL EXPORT DATABASE command exports in SQL format all the records of all the tables in the database. In SQL, this global export operation is called "Dump".