#INCLUDE DEFINES.H

PARAMETER cScrptName, cAppName, cDbName, cScrptType, cInstallPath
** cScrptType is 'FULL' or 'AUDIT'
PUBLIC oSqlSrvr, oDb
STORE .F. TO llError, lRelease, llSchemaAdded
ON ERROR llError = .T.
lnParams = PARAMETERS()
IF  lnParams < 4  OR NOT (cScrptType == 'FULL' OR cScrptType == 'AUDIT')
	WAIT WINDOW "Usage: SchemaGen <Script Name>, <App Name>, <Database Name>, <Script Type>" + ;
			CHR(13) + "Script Type is 'FULL' or 'AUDIT'"
	ON ERROR
	RETURN
ENDIF
IF TYPE('osqlsrvr') <> 'O'
	WAIT WINDOW NOWAIT "Connecting to SQL Server..."
	osqlsrvr = createobject('sqlole.sqlserver')
	IF TYPE('oSQLSrvr') <> 'O'
		WAIT WINDOW "Unable to create SQL Server OLE object." TIME 2.0
		RELEASE osqlSrvr
		ON ERROR
		RETURN
	ENDIF
	ret =osqlsrvr.connect(_SQL_SERVER, _SQL_LOGIN, _SQL_PASSWORD)
	lRelease = .T.
ENDIF
oDb = osqlsrvr.databases(cDbName)
IF TYPE('oDb') <> 'O'
	WAIT WINDOW "Unable to create SQL Server Database object." TIME 2.0
	RELEASE osqlSrvr, oDb
	ON ERROR
	RETURN
ENDIF
WAIT WINDOW NOWAIT "Obtaining Tables List..."
STORE FCREATE(_LOG_PATH + "\" + cAppName + ' Tables Error.log') TO eh
STORE FCREATE(cScrptName) TO _TEXT
STORE FCREATE(_LOG_PATH + "\" + UPPER(cAppName) + ' Tables Audit.log') TO ah
IF _TEXT = -1
	WAIT WINDOW "Unable to create output file." TIMEOUT 2.0
	RELEASE ALL
	ON ERROR
	RETURN
ENDIF
SET TEXTMERGE ON NOSHOW
TEXT
PRINT 'Creating Tables...'
GO
CREATE SCHEMA AUTHORIZATION sa
ENDTEXT
USE (IIF(TYPE('cInstallPath') = 'C', cInstallPath, _INSTALL_PATH) + "\" + cAppName + "_Tables")
lcCount = ALLT(STR(RECCOUNT()))
lnRecCount = 0
SELECT COUNT(*) FROM (cAppName + "_Tables") WHERE UPPER(ALLT(Type)) = 'VIEW' INTO ARRAY alnViewCnt
IF _TALLY = 0
	DIMENSION alnViewCnt[1]
	alnViewCnt[1]	= 0
ENDIF
SCAN
	lnRecCount = lnRecCount + 1
	jcType = UPPER(ALLTRIM(EVALUATE(cAppName + "_Tables.Type")))
	cTblName = "dbo." + ALLT(EVALUATE(cAppName + "_Tables.Name"))
	WAIT WINDOW NOWAIT "Generating for " + cTblName + "..." +
				ALLT(STR(lnRecCount)) + "/" + lcCount
** For 'CREATE SCHEMA' stmt addition
IF ((jcType = 'VIEW') .AND. (NOT llSchemaAdded) .AND. (cScrptType = 'FULL') .AND. (alnViewCnt[1] > 0))
llSchemaAdded	= NOT llSchemaAdded
TEXT
GO
PRINT 'Creating Views...'
GO
CREATE SCHEMA AUTHORIZATION sa
ENDTEXT
ENDIF

IF ((jcType = 'TABLE') .AND. (EVALUATE(cAppName + "_Tables.Order") = 0))
TEXT
GO
CREATE SCHEMA AUTHORIZATION sa
ENDTEXT
ENDIF
** For Full Schema generation
IF cScrptType = 'FULL'
TEXT
<<STRTRAN(IIF(jcType = 'TABLE', STRTRAN(oDb.Tables(cTblName).Script(_SCHEMA_SCRIPT), ;
					'(suser_name(null))', 'SYSTEM_USER'), ;
		STRTRAN(oDb.Views(cTblName).Script(_SCHEMA_SCRIPT), ;
					'(suser_name(null))', 'SYSTEM_USER')), 'GO')>>
GRANT SELECT ON <<cTblName>> TO public
ENDTEXT
ENDIF

** For Full Audit Database Schema generation
IF cScrptType = 'AUDIT'
TEXT
<<STRTRAN(STRTRAN(oDb.Tables(cTblName).Script(_SCHEMA_AUDIT), 'IDENTITY (1, 1) '), 'GO')>>
GRANT INSERT ON <<cTblName>> TO public
ENDTEXT
ENDIF
	IF llError
		llError = .F.
		= FPUTS(eH, cTblName + ":" + MESS())
		= FFLUSH(eH)
	ELSE
		= FPUTS(ah, cTblName)
		= FFLUSH(ah)
	ENDIF
	= FFLUSH(_TEXT)
ENDSCAN

** Create schema objects with the GO.
TEXT
GO
ENDTEXT

** Only for full scripting with DRI
IF cScrptType = 'FULL'
TEXT
PRINT 'Creating Indexes for Tables...'
GO
ENDTEXT
** Generate indexes for the tables now.
lcCount = ALLT(STR(RECCOUNT()))
lnRecCount = 0
SCAN FOR Order > 0
	lnRecCount = lnRecCount + 1
	jcType = UPPER(EVALUATE(cAppName + "_Tables.Type"))
	cTblName = "dbo." + ALLT(EVALUATE(cAppName + "_Tables.Name"))
	WAIT WINDOW NOWAIT "Generating indexes for " + cTblName + "..." + ;
				ALLT(STR(lnRecCount)) + "/" + lcCount
IF jcType = 'TABLE'
TEXT
<<oDb.Tables(cTblName).Script(_INDEX_SCRIPT)>>
ENDTEXT
ENDIF
	IF llError
		llError = .F.
		= FPUTS(eH, cTblName + ":" + MESS())
		= FFLUSH(eH)
	ELSE
		= FPUTS(ah, cTblName)
		= FFLUSH(ah)
	ENDIF
	= FFLUSH(_TEXT)
ENDSCAN
ENDIF
** Only for full scripting with DRI
USE
TEXT
/******************** Script Ended: <<TTOC(DATETIME())>> ********************/
PRINT 'Creation of Tables and Views Completed.'
GO
ENDTEXT
= FCLOSE(ah)
SET TEXTMERGE OFF
SET TEXTMERGE TO
IF FSEEK(eh, 0, 2) > 0
	= FCLOSE(eh)
	cLogFile = _LOG_PATH + "\" + cAppName + ' Tables Error.log'
	MODI FILE &cLogFile NOWAIT
ELSE
	= FCLOSE(eh)
ENDIF
IF lRelease
	WAIT WINDOW NOWAIT "Releasing Resources..."
	osqlsrvr.Close
	RELEASE ALL
	CLEAR ALL
	WAIT CLEAR
	ON ERROR
	RETURN
ENDIF