26. Mai 2009 

für deutsche Leser

MySQL dump

Those friendly guys over there send me a MySQL-dumpfile. That is a 107 MB text-document.

All table-definitions and records are contained in this single document. Everything is SQL-Syntax. Devided by commentaries first the table-definition then the records. This repeats for 15 tables. For a total of 1.170.508 lines. The first 20-something lines are shown as BBEdit displays them.

How to get those 107 MB of data into 4D and generate the PDFs, which is why I got the data in first place? I used this opportunity, to test the real SQL-power of V11.

First I attempted, in case it were that simple:

• drop the Dump on 4D V11
• import via the File-menu …

Receive packet line for line and react depending on content:

• -- means commentary, no processing
• CREATE TABLE means read till ";", then process
• INSERT INTO immediate processing

Table-definitions

From my earlier attempts I knew that 4D V11 is not very elastic concerning key-words. It doesn't accept char(15) but exactly varchar(15) *, text is varchar without length. Obviously MySQL and 4D V11 don't use an identical SQL-syntax (spring_SQL92 vs. autumn_SQL92): default NULL is nothing 4D V11 will handle, neither KEY nor TYPE=MyISAM.

Therefor the table-definitions had to be cleaned first, changed into something more 4D V11-compatible. Doing that I exchanged also CREATE TABLE with CREATE TABLE IF NOT EXISTS, so I could import into the same 4D structure a couple of times. I omitted KEY completely. Extra "," and ")" and double spaces "  " generate 4D V11 SQL-errors, so I cleaned them out, too.

: ($what="Bereinigen")  ` cleaning because 4D doens't like SQL of MySQL - sort of
  $statementTxt:=$P_Self->
  $statementTxt:=Replace string($statementTxt;" CREATE TABLE";" CREATE TABLE IF NOT EXISTS")
  $statementTxt:=Replace string($statementTxt;" char";" varchar")
  $statementTxt:=Replace string($statementTxt;" text";" varchar")
  $statementTxt:=Replace string($statementTxt;" default NULL";"")
  $statementTxt:=Replace string($statementTxt;" TYPE=MyISAM";"")
  
  $pos_L:=Position("KEY";$statementTxt)
  While ($pos_L>0)
  End while
  
  $statementTxt:=Replace string($statementTxt;Char(Line feed );"")
  $statementTxt:=Replace string($statementTxt;Char(Carriage return );"")
  $statementTxt:=Replace string($statementTxt;" ";" ")
  $statementTxt:=Replace string($statementTxt;",)";")")
  $statementTxt:=Replace string($statementTxt;", )";")")
  $P_Self->:=$statementTxt

Then it's easy. EXECUTE IMMEDIATE executes the SQL-statement immediately. The 4D-method looks like this:
: ($what="Execute_Immediate")
  $statementTxt:=$P_Self->
  Begin SQL
    EXECUTE IMMEDIATE :$statementTxt;
  End SQL

The SQL-definition for every table is called like this:
SQL_Mngr ("Bereinigen";->$receivedSQLStatement)
ON ERR CALL("NoError")  `for unknown Dumps put a red dot in NoError
SQL_Mngr ("Execute_Immediate";->$receivedSQLStatement)
ON ERR CALL("")

Dataimport

The data is a single one-line INSERT INTO-statement. One example here:
INSERT INTO _person VALUES ('RƒTES','Herr R‰tes','r‰t','','');
Receive a line RECEIVE PACKET($docRef;$receivedText;$stopChar)
process with SQL_Mngr ("Execute_Immediate";->$receivedText) immediately.

As so often done, I first attempted to convert the characters above Ascii 127 myself. But that's much easier this way

USE CHARACTER SET

USE ASCII MAP renames to USE CHARACTER SET("iso-8859-1";$importFilter_L) in V11. Available is a subselection of IANA Names. In this case import used "iso-8859-1" and export USE CHARACTER SET("MacRoman";$exportFilter_L). Simple - effektiv - good.

Performance

Acceptable! The big tables had 700.000, 350.000 and 100.000 records respectively. The whole import took about 20 minutes. BBEdit needs already a couple of minutes to open the original-107 MB-textfile.

Everything in one method

I love that way of writing code. Everything which belongs to a modul

• menucall
• process-start
• formmethod
• objectmethods
• intern methodcalls
• service-lines of code
• management of hierarchical lists

* I prefer pragmatism. My code would test char or varchar and react properly, same is true for int and int32. Probably to simple, to much 4D thinking. ↵

You want to have the source-code? No problem! Send a descend amount of $ or € to my PayPal-account: info@mettre.de. Finding out how much?

4D Themen: Berichte in Arbeit