Thanks, Tom.
On Fri, Oct 4, 2019 at 9:54 AM Tom Benedict via 4D_Tech <
4d_tech@lists.4d.com> wrote:
> 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 <4d_tech@lists.4d.com>
> 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 <
> > 4d_tech@lists.4d.com> 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:4d_tech-unsubscr...@lists.4d.com
> >> **********************************************************************
> >
> >
> >
> > --
> > 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:4d_tech-unsubscr...@lists.4d.com
> > **********************************************************************
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **********************************************************************
--
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:4d_tech-unsubscr...@lists.4d.com
**********************************************************************
