-- generator on mysql SET SESSION group_concat_max_len = 16500; select -- c.TABLE_NAME, concat( 'create table [', c.TABLE_NAME, '] (', group_concat( concat( '[', c.COLUMN_NAME, '] ', case c.DATA_TYPE when 'char' then concat('char(', c.CHARACTER_MAXIMUM_LENGTH, ')') when 'varchar' then concat('varchar(', c.CHARACTER_MAXIMUM_LENGTH, ')') when 'text' then 'varchar(max)' when 'mediumtext' then 'varchar(max)' when 'mediumblob' then 'varbinary(max)' when 'enum' then concat('varchar(100)') else c.DATA_TYPE end, ' ', if(c.IS_NULLABLE = 'NO', 'not null', 'null'), if( c.EXTRA != 'auto_increment' and c.COLUMN_DEFAULT is not null, concat( ' default ', if( c.DATA_TYPE in ('int', 'float'), c.COLUMN_DEFAULT, concat('\'', c.COLUMN_DEFAULT, '\'') ), ' ' ), '' ), if(c.DATA_TYPE = 'int' and c.EXTRA = 'auto_increment', ' identity(1,1) ', ' '), if(c.DATA_TYPE = 'enum', concat(' check([', c.COLUMN_NAME, '] in ', substring(c.COLUMN_TYPE, 5), ' or [', c.COLUMN_NAME, '] = \'\') '), '') ) order by c.ORDINAL_POSITION separator ', ' ), if( pr.primaries is not null, concat( ', primary key(', pr.primaries, ')' ), '' ), ');' ) from INFORMATION_SCHEMA.COLUMNS c left join ( select pri.TABLE_SCHEMA, pri.TABLE_NAME, group_concat(concat('[', pri.COLUMN_NAME, ']') order by pri.ORDINAL_POSITION separator ', ') primaries from INFORMATION_SCHEMA.COLUMNS pri where pri.TABLE_SCHEMA = 'prj_carleyfoundry_import' and pri.COLUMN_KEY = 'PRI' group by pri.TABLE_SCHEMA, pri.TABLE_NAME ) pr on pr.TABLE_SCHEMA = c.TABLE_SCHEMA and pr.TABLE_NAME = c.TABLE_NAME where c.TABLE_SCHEMA = 'prj_carleyfoundry_import' group by c.TABLE_SCHEMA, c.TABLE_NAME -- generated on mssql /* create table [cf_AclResource] ([id] int not null identity(1,1) , [title] varchar(255) not null default '' , [query] varchar(255) not null default '' , [message] varchar(max) not null , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_AclResource2Role] ([idAclResource] int not null default 0 , [idAclRole] varchar(100) not null default 'admin' check([idAclRole] in ('admin','user','guest') or [idAclRole] = '') , primary key([idAclResource], [idAclRole])); create table [cf_AclResourcePrivilege] ([idAclResource] int not null default 0 , [name] varchar(255) not null default '' , [title] varchar(255) not null default '' , [sorter] int not null default 0 , primary key([idAclResource], [name])); create table [cf_AclRole] ([id] varchar(100) not null default 'admin' check([id] in ('admin','user','guest') or [id] = '') , [title] varchar(255) not null default '' , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_AclRule] ([id] int not null identity(1,1) , [idAclRole] varchar(100) not null default 'admin' check([idAclRole] in ('admin','user','guest') or [idAclRole] = '') , [idRole] int not null default 0 , [idAclResource] int not null default 0 , [privilege] varchar(255) null , [type] varchar(100) not null default 'allow' check([type] in ('allow','deny') or [type] = '') , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Client] ([id] int not null identity(1,1) , [number] varchar(255) not null default '' , [name] varchar(255) not null default '' , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Client2User] ([id] int not null identity(1,1) , [clientId] int not null default 0 , [userId] int not null default 0 , primary key([id])); create table [cf_Department] ([id] int not null identity(1,1) , [parentId] int not null default 0 , [name] varchar(255) not null default '' , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_DepartmentAndProcessSorter] ([id] int not null identity(1,1) , [departmentId] int null , [processId] int null , [sorter] int not null default 0 , primary key([id])); create table [cf_DPermission] ([id] int not null default 0 , [title] varchar(255) not null default '' , [resourceId] int not null default 0 , [privilegeId] int not null default 0 , primary key([id])); create table [cf_DPrivilege] ([id] int not null default 0 , [name] varchar(255) not null default '' , primary key([id])); create table [cf_DResource] ([id] int not null default 0 , [name] varchar(255) not null default '' , primary key([id])); create table [cf_DState] ([id] varchar(2) not null default '' , [title] varchar(255) not null default '' , primary key([id])); create table [cf_DStatus] ([id] varchar(15) not null default '' , [title] varchar(55) not null default '' , primary key([id])); create table [cf_Form] ([id] int not null identity(1,1) , [blockedBy] int not null default 0 , [blockedAt] datetime null , [height] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_FormElement] ([id] int not null identity(1,1) , [formId] int not null default 0 , [formType] varchar(100) not null default 'process' check([formType] in ('process','part') or [formType] = '') , [typeId] int not null default 0 , [left] int not null default 0 , [top] int not null default 0 , [zIndex] int not null default 0 , [draftFor] int null , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_FormElementProperty] ([id] int not null identity(1,1) , [elementTypeId] int not null default 0 , [label] varchar(255) not null default '' , [alias] varchar(255) not null default '' , [type] varchar(100) not null default 'text' check([type] in ('text','textarea','select','checkbox') or [type] = '') , [options] varchar(max) not null , [sorter] int not null default 0 , primary key([id])); create table [cf_FormElementPropertyValue] ([elementId] int not null default 0 , [propertyId] int not null default 0 , [value] varchar(max) not null , primary key([elementId], [propertyId])); create table [cf_FormSubmission] ([id] int not null identity(1,1) , [formId] int not null default 0 , [formType] varchar(100) not null default 'process' check([formType] in ('process','part') or [formType] = '') , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_FormSubmissionValue] ([id] int not null identity(1,1) , [submissionId] int not null default 0 , [elementId] int not null default 0 , [value] varchar(max) null , primary key([id])); create table [cf_Media] ([id] int not null identity(1,1) , [processId] int not null default 0 , [partId] int not null default 0 , [revisionStart] int null , [revisionEnd] int null , [title] varchar(255) not null default '' , [description] varchar(max) not null , [type] varchar(100) not null default 'image' check([type] in ('image','video') or [type] = '') , [image] varchar(255) null , [video] varchar(255) null , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Part] ([id] int not null identity(1,1) , [blockedVersion] int null default 0 , [blockedBy] int not null default 0 , [blockedAt] datetime null , [height] int not null default 300 , [defaultProcessId] int not null default 0 , [currentVersion] int not null default 0 , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Part2PartData] ([id] int not null identity(1,1) , [partId] int not null default 0 , [versionId] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Part2Process] ([partId] int not null default 0 , [processId] int not null default 0 ); create table [cf_Part2ProcessData] ([id] int not null identity(1,1) , [processId] int not null default 0 , [versionId] int not null default 0 , [setId] int null , [revisionEnd] int null , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Part2User] ([id] int not null identity(1,1) , [partId] int not null default 0 , [userId] int not null default 0 , primary key([id])); create table [cf_PartVersion] ([id] int not null identity(1,1) , [partId] int not null default 0 , [preProduction] varchar(100) not null default 'y' check([preProduction] in ('y','n') or [preProduction] = '') , [version] int not null default 0 , [number] varchar(255) not null default '' , [name] varchar(255) not null default '' , [clientId] int not null default 0 , [weight] float not null default 0 , [alloyTemper] varchar(255) not null default '' , [status] varchar(255) not null default '' , [localDirectory] varchar(255) not null default '' , [image] varchar(255) null , [comments] varchar(max) not null , [userId] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Process] ([id] int not null identity(1,1) , [departmentId] int not null default 0 , [name] varchar(255) not null default '' , [isMedia] varchar(100) not null default 'n' check([isMedia] in ('n','y') or [isMedia] = '') , [isMultiply] varchar(100) not null default 'n' check([isMultiply] in ('n','y') or [isMultiply] = '') , [sorter] int not null default 0 , [blockedBy] int not null default 0 , [blockedAt] datetime null , [height] int not null default 0 , [sidebar] varchar(255) not null default '' , [new] varchar(100) not null default 'y' check([new] in ('y','n') or [new] = '') , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_Process2User] ([id] int not null identity(1,1) , [processId] int not null default 0 , [userId] int not null default 0 , primary key([id])); create table [cf_User] ([id] int not null identity(1,1) , [firstName] varchar(255) not null default '' , [lastName] varchar(255) not null default '' , [login] varchar(255) null , [email] varchar(128) not null default '' , [password] varchar(255) not null default '' , [salt] varchar(8) not null default '' , [autologinCode] varchar(32) null , [newPassword] varchar(32) null , [newSalt] varchar(8) null , [recoverCode] varchar(32) null , [recoverCodeExpirationDate] datetime null , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_User2Department] ([userId] int not null default 0 , [departmentId] int not null default 0 ); create table [cf_User2Permission] ([userId] int not null default 0 , [permissionId] int not null default 0 ); create table [cf_zz_Admin] ([id] int not null identity(1,1) , [firstName] varchar(255) not null default '' , [middleInitial] char(1) not null default '' , [lastName] varchar(255) not null default '' , [login] varchar(50) not null default '' , [password] varchar(32) not null default '' , [salt] varchar(8) not null default '' , [autologinCode] varchar(32) null , [remoteAuthEnabled] varchar(100) not null default 'n' check([remoteAuthEnabled] in ('n','y') or [remoteAuthEnabled] = '') , [remotePasswordExpirationDate] datetime null , [email] varchar(255) null , [stsLoginCode] varchar(32) null , [stsLoginEnabled] varchar(100) not null default 'n' check([stsLoginEnabled] in ('n','y') or [stsLoginEnabled] = '') , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_DFileType] ([extension] varchar(8) not null default '' , [type] varchar(255) not null default '' , primary key([extension])); create table [cf_zz_DLanguage] ([name] varchar(3) not null default '' , [title] varchar(255) not null default '' , [shortTitle] varchar(255) not null default '' , [icon] varchar(64) not null default '' , [locale] varchar(16) not null default '' , [def] varchar(100) not null default 'n' check([def] in ('y','n') or [def] = '') , [enabled] varchar(100) not null default 'y' check([enabled] in ('y','n') or [enabled] = '') , [sorter] int not null default 0 , primary key([name])); create table [cf_zz_DNotFoundMode] ([id] int not null default 0 , [title] varchar(255) not null default '' , [sorter] int not null default 0 , primary key([id])); create table [cf_zz_DPageHandler] ([name] varchar(64) not null default '' , [title] varchar(max) not null , [sorter] int not null default 0 , primary key([name])); create table [cf_zz_DPageItemGroup] ([id] int not null identity(1,1) , [name] varchar(255) not null default '' , [title] varchar(255) not null default '' , [sorter] int not null default 0 , primary key([id])); create table [cf_zz_DPageItemType] ([id] int not null identity(1,1) , [type] varchar(64) not null default '' , [title] varchar(255) not null default '' , [sorter] int not null default 0 , primary key([id])); create table [cf_zz_DPageOption] ([name] varchar(32) not null default '' , [title] varchar(255) not null default '' , [show] varchar(100) not null default 'n' check([show] in ('n','y') or [show] = '') , [value] varchar(100) not null default 'n' check([value] in ('n','y') or [value] = '') , [sorter] int not null default 0 , primary key([name])); create table [cf_zz_DraftHtmlBlock] ([id] int not null identity(1,1) , [added] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_DraftHtmlBlockContent] ([idBlock] int not null default 0 , [language] varchar(3) not null default '' , [content] varchar(max) not null , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([idBlock], [language])); create table [cf_zz_DraftPage] ([id] int not null default 0 , [idParent] int not null default 0 , [alias] varchar(255) null , [handler] varchar(64) not null default 'site' , [bodyTemplate] varchar(255) not null default 'default.tpl' , [showInSiteMap] varchar(100) not null default 'y' check([showInSiteMap] in ('y','n') or [showInSiteMap] = '') , [showInMenu] varchar(100) not null default 'y' check([showInMenu] in ('y','n') or [showInMenu] = '') , [allowDelete] varchar(100) not null default 'y' check([allowDelete] in ('y','n') or [allowDelete] = '') , [isSecure] varchar(100) not null default 'n' check([isSecure] in ('n','y') or [isSecure] = '') , [isIndexing] varchar(100) not null default 'y' check([isIndexing] in ('y','n') or [isIndexing] = '') , [isRoot] varchar(100) not null default 'n' check([isRoot] in ('n','y') or [isRoot] = '') , [hasBlocks] varchar(100) not null default 'n' check([hasBlocks] in ('n','y') or [hasBlocks] = '') , [final] varchar(100) not null default 'n' check([final] in ('n','y') or [final] = '') , [system] varchar(100) not null default 'n' check([system] in ('n','y') or [system] = '') , [redirect] varchar(100) not null default 'n' check([redirect] in ('n','y') or [redirect] = '') , [redirectType] varchar(100) null check([redirectType] in ('page','url') or [redirectType] = '') , [redirectPageId] int not null default 0 , [redirectStatus] int not null default 302 , [redirectAlias] varchar(255) not null default '' , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_DraftPageContent] ([idPage] int not null default 0 , [language] varchar(3) not null default '' , [title] varchar(255) not null default '' , [menuTitle] varchar(255) not null default '' , [header] varchar(max) not null , [keywords] varchar(max) not null , [description] varchar(max) not null , primary key([idPage], [language])); create table [cf_zz_DraftPageItem] ([id] int not null identity(1,1) , [idPage] int not null default 0 , [idGroup] int not null default 1 , [type] varchar(64) not null default '' , [number] int null , [name] varchar(255) null , [title] varchar(255) not null default '' , [allowDelete] varchar(100) not null default 'y' check([allowDelete] in ('y','n') or [allowDelete] = '') , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_DraftPageItemConfig] ([id] int not null identity(1,1) , [idItem] int not null default 0 , [name] varchar(255) not null default '' , [value] varchar(max) null , primary key([id])); create table [cf_zz_DraftPageOption] ([id] int not null identity(1,1) , [idPage] int not null default 0 , [name] varchar(50) not null default '' , [show] varchar(100) not null default 'n' check([show] in ('n','y') or [show] = '') , [value] varchar(100) not null default 'n' check([value] in ('n','y') or [value] = '') , primary key([id])); create table [cf_zz_File] ([id] int not null identity(1,1) , [name] varchar(255) not null default '' , [nameFs] varchar(255) not null default '' , [type] varchar(255) not null default '' , [size] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_HtmlBlock] ([id] int not null identity(1,1) , [added] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_HtmlBlockContent] ([idBlock] int not null default 0 , [language] varchar(3) not null default '' , [content] varchar(max) not null , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([idBlock], [language])); create table [cf_zz_Log] ([id] int not null identity(1,1) , [priority] varchar(100) not null default 'EMERG' check([priority] in ('EMERG','ALERT','CRIT','ERR','WARN','NOTICE','INFO','DEBUG') or [priority] = '') , [ip] varchar(15) not null default '' , [idSession] varchar(32) null , [serverName] varchar(255) not null default '' , [url] varchar(255) not null default '' , [message] varchar(max) not null , [backtrace] varbinary(max) null , [added] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_NotFound] ([id] int not null identity(1,1) , [modeId] int null , [isAjax] varchar(100) not null default 'n' check([isAjax] in ('y','n') or [isAjax] = '') , [request] varchar(255) not null default '' , [referer] varchar(255) not null default '' , [userAgent] varchar(255) not null default '' , [ipAddress] varchar(25) not null default '' , [added] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_Page] ([id] int not null identity(1,1) , [idParent] int not null default 0 , [alias] varchar(255) null , [handler] varchar(64) not null default 'site' , [bodyTemplate] varchar(255) not null default 'default.tpl' , [showInSiteMap] varchar(100) not null default 'y' check([showInSiteMap] in ('y','n') or [showInSiteMap] = '') , [showInMenu] varchar(100) not null default 'y' check([showInMenu] in ('y','n') or [showInMenu] = '') , [allowDelete] varchar(100) not null default 'y' check([allowDelete] in ('y','n') or [allowDelete] = '') , [isSecure] varchar(100) not null default 'n' check([isSecure] in ('n','y') or [isSecure] = '') , [isIndexing] varchar(100) not null default 'y' check([isIndexing] in ('y','n') or [isIndexing] = '') , [isRoot] varchar(100) not null default 'n' check([isRoot] in ('n','y') or [isRoot] = '') , [hasBlocks] varchar(100) not null default 'n' check([hasBlocks] in ('n','y') or [hasBlocks] = '') , [final] varchar(100) not null default 'n' check([final] in ('n','y') or [final] = '') , [system] varchar(100) not null default 'n' check([system] in ('n','y') or [system] = '') , [redirect] varchar(100) not null default 'n' check([redirect] in ('n','y') or [redirect] = '') , [redirectType] varchar(100) null check([redirectType] in ('page','url') or [redirectType] = '') , [redirectPageId] int not null default 0 , [redirectStatus] int not null default 302 , [redirectAlias] varchar(255) not null default '' , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_PageContent] ([idPage] int not null default 0 , [language] varchar(3) not null default '' , [title] varchar(255) not null default '' , [menuTitle] varchar(255) not null default '' , [header] varchar(max) not null , [keywords] varchar(max) not null , [description] varchar(max) not null , primary key([idPage], [language])); create table [cf_zz_PageIndex] ([id] int not null identity(1,1) , [idPage] int not null default 0 , [language] varchar(3) not null default '' , [url] varchar(255) not null default '' , [title] varchar(255) not null default '' , [content] varchar(max) not null , [scriptDuration] float not null default 0.000000000000 , [reindex] varchar(100) not null default 'n' check([reindex] in ('y','n') or [reindex] = '') , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_PageItem] ([id] int not null identity(1,1) , [idPage] int not null default 0 , [idGroup] int not null default 1 , [type] varchar(64) not null default '' , [number] int null , [name] varchar(255) null , [title] varchar(255) not null default '' , [allowDelete] varchar(100) not null default 'y' check([allowDelete] in ('y','n') or [allowDelete] = '') , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_PageItemConfig] ([id] int not null identity(1,1) , [idItem] int not null default 0 , [name] varchar(255) not null default '' , [value] varchar(max) null , primary key([id])); create table [cf_zz_PageOption] ([id] int not null identity(1,1) , [idPage] int not null default 0 , [name] varchar(50) not null default '' , [show] varchar(100) not null default 'n' check([show] in ('n','y') or [show] = '') , [value] varchar(100) not null default 'n' check([value] in ('n','y') or [value] = '') , primary key([id])); create table [cf_zz_Redirection] ([id] int not null identity(1,1) , [source] varchar(max) not null , [dest] varchar(max) not null , [status] int not null default 301 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([id])); create table [cf_zz_Settings] ([name] varchar(255) not null default '' , [idCategory] int not null default 0 , [fieldType] varchar(255) not null default '' , [label] varchar(255) not null default '' , [value] varchar(max) not null , [options] varchar(max) not null , [description] varchar(max) not null , [required] varchar(100) not null default 'y' check([required] in ('y','n') or [required] = '') , [system] varchar(100) not null default 'n' check([system] in ('y','n') or [system] = '') , [sorter] int not null default 0 , [added] datetime not null default '0000-00-00 00:00:00' , [changed] datetime not null default '0000-00-00 00:00:00' , primary key([name])); create table [cf_zz_SettingsCategory] ([id] int not null identity(1,1) , [name] varchar(255) not null default '' , [system] varchar(100) not null default 'n' check([system] in ('y','n') or [system] = '') , [sorter] int not null default 0 , primary key([id])); create table [cf_zz_SettingsOptions] ([id] int not null identity(1,1) , [name] varchar(255) not null default '' , [variable] varchar(255) not null default '' , [value] varchar(255) not null default '' , [type] varchar(100) not null default 'string' check([type] in ('string','bool','int','float','double') or [type] = '') , primary key([id])); */