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.

BBEdit displays the MySQL-dumpfile like this:
-- MySQL dump 9.08
--
-- Host: localhost Database: auskunftdemo
---------------------------------------------------------
-- Server version 4.0.14-nt
  
--
-- Table structure for table '_person'
--
  
CREATE TABLE _person (
MATCHCODE char(15) default NULL,
BEZEICHN char(50) default NULL,
KUERZEL char(10) default NULL,
ZIMMER char(10) default NULL,
TELEFON char(15) default NULL,
KEY i_person1 (MATCHCODE)
) TYPE=MyISAM;
--
-- Dumping data for table '_person'
--
  
INSERT INTO _person VALUES ('BALKEN','Herr Balken','bal','','');
INSERT INTO _person VALUES ('RƒTES','Herr R‰tes','r‰t','','');

there are a further 1.170.482 lines

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 …
to no avail. This is, what I finally came up with:

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.

sqlmngr

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
is part of a single method.

* 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