See the methods listed below..
> i get that for normal human beings that don't have 24 or 36" rolls printing a
> structure was not the greatest thing ever, but for us…
May I point out that for those of us who don’t have plotters in our office,
Staples (and I expect most other printing service bureaus, Kinkos etc) have
blueprint printing capability that is very reasonably priced. You can print a
3ftx4ft chart for under $10. Just upload the PDF and pick it up at your local
store.
Here’s the SQP Script Generator code. There are three methods:
System_Export_SQL_Script, System_SQL_NameOut and System_SQL_FldScrp. You may
have to tweak the destination data types in System_SQL_FldScrp. It was built
for Transact-SQL, but there is an Oracle case which I haven’t used/tested. The
result SQL Script includes constraints, which you may have to remove depending
on your tool since the dependent tables are not always created prior to the
parent table, causing errors.
I wish I could remember where I got the original code. It was many years ago.
Hope it helps someone.
Tom Benedict
// Method: System_Export_SQLScript
// ----------------------------------------------------
// User name (OS): Tom Benedict
// Date and time: 7/19/2019, 04:13:52
// ----------------------------------------------------
// Description
// Generates a SQL Script which describes the 4D Structure. Can be used to
generate a SQL Schema used by any SQL tool
// bld277002
//
C_LONGINT($i;$j)
C_TEXT(PrimKey;FirstInd;CRChar)
CRChar:=Char(Carriage return)
ARRAY INTEGER(PKOneFile;0)
ARRAY POINTER(PKOneField;0)
ARRAY INTEGER(PKManyFile;0)
ARRAY POINTER(PKManyField;0)
ARRAY TEXT(MCLNames;0)
ARRAY INTEGER(MCLSize;0)
ARRAY INTEGER(SQL_FNos;Get last table number)
ARRAY TEXT(SQL_FNames;Get last table number)
For ($i;1;Get last table number)
If (Is table number valid($i)) // 10/6/10 TGB
SQL_FNos{$i}:=$i
SQL_FNames{$i}:=Table name($i)
End if
End for
SORT ARRAY(SQL_FNames;SQL_FNos;>) // CS Sort so it easier to find tables in
DDL output file
// **************************
// if you want to select the files to send implement this dialog
// Open window(20;50;420;400;2)
// DIALOG(•[CONFIGURE]•;"SQL_Files") `****
// CLOSE WINDOW
// ***************************
// If (OK=1)
// find foreign keys
// assumes many table's foreign key is one table's primary key
$Size:=Size of array(SQL_FNos)
For ($i;1;$Size)
If (SQL_FNos{$i}>0)
For ($j;1;Get last field number(SQL_FNos{$i}))
GET RELATION
PROPERTIES(SQL_FNos{$i};$j;RelFile;RelField)
If (RelFile#0)
INSERT IN ARRAY(PKOneFile;1)
INSERT IN ARRAY(PKOneField;1)
INSERT IN ARRAY(PKManyFile;1)
INSERT IN ARRAY(PKManyField;1)
PKManyFile{1}:=SQL_FNos{$i}
PKManyField{1}:=Field(SQL_FNos{$i};$j)
PKOneFile{1}:=RelFile
PKOneField{1}:=Field(RelFile;RelField)
End if
End for
End if
End for
DocRef:=Create document("";"TEXT")
If (Ok=1)
If (Size of array(MCLNames)#0)
For ($i;1;Size of array(MCLNames))
ScratchStr:=MCLNames{$i}
If (Position(" ";ScratchStr)=1)
ScratchStr:=Replace string(ScratchStr;" ";"";1)
// just replace number 1
End if
ScratchStr:="MCL_"+ScratchStr
ScratchStr:=System_SQL_NameOut (ScratchStr)
$TableDef:="CREATE TABLE "+System_SQL_NameOut
(ScratchStr)+CRChar+"("+CRChar
$TableDef:=$TableDef+"VALUE
CHAR("+String(MCLSize{$i})+") PRIMARY KEY"
$TableDef:=$TableDef+CRChar+");"+CRChar
SEND PACKET(DocRef;$TableDef)
End for
End if
For ($i;1;Size of array(SQL_FNos))
$MyFile:=SQL_FNos{$i}
If ($MyFile>0)
ARRAY TEXT(FornKeys;0) // this table's field that refs
ARRAY TEXT(FornKeyFile;0) // the other table's pk
PrimKey:=""
FirstInd:=""
$TableDef:="CREATE TABLE "+System_SQL_NameOut (Table
name($MyFile))+CRChar+"("+CRChar
If (Get last field number($MyFile)>0)
$FldPtr:=Field($MyFile;1)
$FldScrpt:=System_SQL_FldScrp ($FldPtr)
$FieldName:=Field name($FldPtr)
$TableDef:=$TableDef+System_SQL_NameOut
($FieldName)+$FldScrpt
For ($j;2;Get last field number($MyFile))
$FldPtr:=Field($MyFile;$j)
$FldScrpt:=System_SQL_FldScrp ($FldPtr)
If ($FldScrpt#"") // don't
send"zzFieldName"
$FieldName:=Field name($FldPtr)
$TableDef:=$TableDef+","+CRChar+System_SQL_NameOut ($FieldName)+$FldScrpt
End if
End for
End if
//Table Constraints?00:00:00?
If (PrimKey#"")
$TableDef:=$TableDef+","+CRChar+"CONSTRAINT
PK_"+PrimKey+" PRIMARY KEY "+"("+System_SQL_NameOut (PrimKey)+")"
Else
If (FirstInd#"")
// Names any assumed primary keys as
PK_FI_KeyName(for First Indexed field found)
$TableDef:=$TableDef+","+CRChar+"CONSTRAINT PK_"+FirstInd+" PRIMARY KEY
"+"("+System_SQL_NameOut (FirstInd)+")"
End if
End if
If (Size of array(FornKeys)#0)
For ($j;1;Size of array(FornKeys))
$TableDef:=$TableDef+","+CRChar+"CONSTRAINT FK_"+System_SQL_NameOut
(FornKeys{$j})+" FOREIGN KEY "+"("+System_SQL_NameOut (FornKeys{$j})+")"+"
REFERENCES "+System_SQL_NameOut (FornKeyFile{$j})+"("+FornKeys{$j}+")"
End for
End if
$TableDef:=$TableDef+CRChar+");"+CRChar
SEND PACKET(DocRef;$TableDef)
End if
End for
CLOSE DOCUMENT(docRef)
ALERT("Done with SQL DDL Creation.")
End if
// Method: System_SQL_NameOut
// ----------------------------------------------------
// User name (OS): Tom Benedict
// Date and time: 7/19/2019, 04:15:07
// ----------------------------------------------------
// Description
// currently just looks for a space and wraps quotes around the name if found.
// this is a legal oracle name
// if spaces are illegal then use $0:=Replace string($0;" ";"")
// bld277002
//
// ----------------------------------------------------
// Declarations
// - Input
C_TEXT($1)
// - Output
C_TEXT($0)
// - Variables
// ----------------------------------------------------
$0:=Replace string($1;Char(Double quote);"") //clear any quote chars we
already have
If (Position(" ";$0)=1)
$0:=Replace string($0;" ";"";1) //clear a leading space
End if
If ((Position(" ";$0)#0) | (Position(".";$0)#0))
$0:=Char(Double quote)+$0+Char(Double quote) //if any internal spaces
then quote the string
End if
// Method: System_SQL_FldScrp
// ----------------------------------------------------
// User name (OS): tgbpbp
// Date and time: 7/19/2019, 04:16:25
// ----------------------------------------------------
// Description
// bld277002
//
// ----------------------------------------------------
// Declarations
// - Input
C_POINTER($1) //Field Pointer
// - Output
C_TEXT($0)
// - Variables
C_LONGINT($Len)
C_BOOLEAN($Index)
// ----------------------------------------------------
$0:=""
GET FIELD PROPERTIES($1;$Type;$Len;$Index)
//Oracle specific data types
Case of
: (Position("zz";Field name($1))=1)
$0:=""
: ($Type=7) //SubFile
$0:=""
Else
If (False) //true for SQL anywhere; False for ORACLE
Case of
: ($Type=0) //Alpha
$0:="CHAR("+String($Len)+")"
: ($Type=1) //Real
$0:="DOUBLE"
: ($Type=2) //Text
$0:="CHAR(32767)"
: ($Type=3) //Picture
$0:="LONG BINARY"
: ($Type=4) //DATE; Dates for last modified or
created are better as TIMESTAMPs
$0:="TIMESTAMP"
: ($Type=11) //Time
$0:="TIME"
: ($Type=6) //Boolean
$0:="SMALLINT"
: ($Type=7) //SubFile
$0:="error"
: ($Type=8) //Integer
$0:="SMALLINT"
: ($Type=9) //Longint
$0:="INTEGER"
: ($Type=30) //Blob
$0:="CHAR(32767)"
: ($Type=38) //Object
$0:="CHAR(32767)"
End case
Else //False for Oracle
Case of
: ($Type=0) //Alpha
$0:="VARCHAR("+String($Len)+")"
: ($Type=1) //Real
$0:="REAL"
: ($Type=2) //Text
$0:="VARCHAR(8000)"
: (($Type=3) | ($Type=30)) //Picture or BLOB
$0:="IMAGE"
: (($Type=4) | ($Type=11)) //Date or Time
$0:="SMALLDATETIME"
: ($Type=6) //Boolean
$0:="BIT NOT NULL DEFAULT 0"
: ($Type=8) //Integer
$0:="SMALLINT"
: ($Type=9) //Longint
$0:="INT" //"BIGINT"
: ($Type=30) //Blob
$0:="CHAR(32767)"
: ($Type=38) //Object
$0:="CHAR(32767)"
End case
End if
$0:=" "+$0
If ($Index)
$Posn:=Find in array(PKOneField;$1)
If ($Posn#-1) // Is This Field Used as a Primary Key
PrimKey:=Field name($1)
$0:=$0+" NOT NULL" //PK_ThisFile
Else
If (FirstInd="")
FirstInd:=Field name($1)
End if
End if
End if
$Posn:=Find in array(PKManyField;$1)
If ($Posn#-1) // Is This Field Used as a Foreign Key
If (Find in array(SQL_FNos;PkOneFile{$posn})#-1)
$0:=$0+" NOT NULL"
INSERT IN ARRAY(FornKeys;1)
INSERT IN ARRAY(FornKeyFile;1)
FornKeys{1}:=Field name($1)
FornKeyFile{1}:=Table name(PkOneFile{$posn})
Else
$0:=$0+" NULL"
End if
Else // CS Added to always default NULL
If (Position("NULL";$0)=0) //01/03/12 13:04 Lee, Terry
Remove Unicode Correction
$0:=$0+" NULL"
End if
End if
End case
> On Oct 4, 2019, at 06:01, Mike Kerner via 4D_Tech <[email protected]>
> wrote:
>
> thanks miyako.
> tom, i think we'd like to look at your script, too.
> bringing these systems from v.6 and 2004 to v.17 - I think we're going to
> miss just dumping the diagram to one of our plotters. i get that for
> normal human beings that don't have 24 or 36" rolls printing a structure
> was not the greatest thing ever, but for us...
>
> On Fri, Oct 4, 2019 at 1:15 AM Patrick Emanuel via 4D_Tech <
> [email protected]> wrote:
>
>> Hi Tom,
>>
>> should be good if you're able to share it here ;-)
>>
>> Cheers
>>
>> Patrick
>>
>>
>>
>> -----
>> Patrick EMANUEL
>> ------------------------------------------------------------
>> Administrator
>> www.association-qualisoft.eu
>> (Soft1002, Simply Asso & QS_Toolbox)
>> --
>> Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html
>> **********************************************************************
>> 4D Internet Users Group (4D iNUG)
>> Archive: http://lists.4d.com/archives.html
>> Options: https://lists.4d.com/mailman/options/4d_tech
>> Unsub: mailto:[email protected]
>> **********************************************************************
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
> and did a little diving.
> And God said, "This is good."
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:[email protected]
> **********************************************************************
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:[email protected]
**********************************************************************
