#INCLUDE DEFINES.H

PARAMETER cScrptName, cAppName, cDbName, cScrptType, cInstallPath
** cScrptType is 'FULL' OR 'MIGRATE'
PUBLIC oSqlSrvr, oDb
STORE .F. TO llError, lRelease, llSchemaAdded
ON ERROR llError = .T.
lnParams = PARAMETERS()
IF  lnParams < 4  OR NOT (cScrptType == 'FULL' OR cScrptType == 'MIGRATE')
	WAIT WINDOW "Usage: SchemaGen2 <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 + 'Just Tables Error.log') TO eh
STORE FCREATE(cScrptName) TO _TEXT
STORE FCREATE(_LOG_PATH + "\" + UPPER(cAppName) + 'Just 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
/******************** Script Started: <<TTOC(DATETIME())>> ********************/
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 FOR ALLTRIM( UPPER( type )) =  'TABLE'
	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
IF ((jcType = 'TABLE') .AND. (EVALUATE(cAppName + "_Tables.Order") = 0))
TEXT
GO
CREATE SCHEMA AUTHORIZATION sa
ENDTEXT
ENDIF
** For Full Schema generation
IF cScrptType = 'MIGRATE'
TEXT
<<STRTRAN(IIF(jcType = 'TABLE', STRTRAN(oDb.Tables(cTblName).Script(_MIGRATE_SCRIPT), ;
					'(suser_name(null))', 'SYSTEM_USER'), ;
		STRTRAN(oDb.Views(cTblName).Script(_MIGRATE_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

TEXT
/******************** Script Ended: <<TTOC(DATETIME())>> ********************/
PRINT 'Creation of Tables(Only) Completed.'
GO
ENDTEXT
= FCLOSE(ah)
SET TEXTMERGE OFF
SET TEXTMERGE TO
IF FSEEK(eh, 0, 2) > 0
	= FCLOSE(eh)
	cLogFile = _LOG_PATH + "\" + cAppName + 'Just 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