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 **********************************************************************