TRS.WCM.Developer/TRS.WCM.Developer/sp/p_data_migration_xwcmviewfieldinfo.sql

DROP PROCEDURE IF EXISTS p_data_migration_xwcmviewfieldinfo;
DELIMITER $$
CREATE PROCEDURE p_data_migration_xwcmviewfieldinfo(IN citySuffix VARCHAR(20),
                                              IN prevViewFieldId BIGINT(20),
                                              IN viewFieldIdOffset BIGINT(20),
                                              IN tableInfoIdOffset BIGINT(20),
                                              IN classIdOffset BIGINT(20),
                                              IN dbFieldIdOffset BIGINT(20),
                                              IN fieldGroupIdOffset BIGINT(20),
                                              IN relationViewIdOffset BIGINT(20),
                                              IN viewIdOffset BIGINT(20))
	BEGIN
	
	  DECLARE over INT DEFAULT 0;

    DECLARE  f_VIEWFIELDINFOID int(11) DEFAULT NULL;
    DECLARE  f_TABLENAME varchar(50) DEFAULT NULL;
    DECLARE  f_TABLEID int(11) DEFAULT '0';
    DECLARE  f_FIELDNAME varchar(50) DEFAULT NULL;
    DECLARE  f_DBFIELDNAME varchar(50) DEFAULT NULL;
    DECLARE  f_DBTYPE int(11) DEFAULT '0';
    DECLARE  f_DBLENGTH int(11) DEFAULT '0';
    DECLARE  f_ANOTHERNAME varchar(200) DEFAULT NULL;
    DECLARE  f_FIELDTYPE int(11) DEFAULT '0';
    DECLARE  f_DEFAULTVALUE varchar(200) DEFAULT NULL;
    DECLARE  f_ENMVALUE varchar(1000) DEFAULT NULL;
    DECLARE  f_NOTNULL smallint(6) DEFAULT NULL;
    DECLARE  f_CLASSID int(11) DEFAULT '0';
    DECLARE  f_VIEWID int(11) DEFAULT '0';
    DECLARE  f_FIELDORDER int(11) DEFAULT '0';
    DECLARE  f_INOUTLINE smallint(6) DEFAULT NULL;
    DECLARE  f_INDETAIL smallint(6) DEFAULT '1';
    DECLARE  f_SEARCHFIELD smallint(6) DEFAULT NULL;
    DECLARE  f_TITLEFIELD smallint(6) DEFAULT NULL;
    DECLARE  f_DBFIELD int(11) DEFAULT '0';
    DECLARE  f_VALIDATOR varchar(200) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_FROMMAINTABLE int(11) DEFAULT '1';
    DECLARE  f_LOCATECHANNEL varchar(100) DEFAULT NULL;
    DECLARE  f_RADORCHK smallint(6) DEFAULT NULL;
    DECLARE  f_NOTEDIT smallint(6) DEFAULT '0';
    DECLARE  f_HIDDENFIELD smallint(6) DEFAULT '0';
    DECLARE  f_INMULTITABLE smallint(6) DEFAULT '1';
    DECLARE  f_DBSCALE int(11) DEFAULT '3';
    DECLARE  f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE  f_IDENTITYFIELD smallint(6) DEFAULT NULL;
    DECLARE  f_LIBFILEDID int(11) DEFAULT NULL;
    DECLARE  f_FIELDGROUPID int(11) DEFAULT NULL;
    DECLARE  f_RELATIONVIEWID int(11) DEFAULT '0';
    DECLARE  f_ISURLFIELD smallint(6) DEFAULT '0';
    DECLARE  f_EDITSTYLE varchar(10) DEFAULT NULL;
    DECLARE  f_APPENDIXSELECTLIB varchar(10) DEFAULT NULL;
    DECLARE  f_APPENDIXNOTUPLOAD varchar(10) DEFAULT NULL;
    DECLARE  f_FieldWidth varchar(50) DEFAULT NULL;
    DECLARE  f_FieldClass varchar(300) DEFAULT NULL;
    DECLARE  f_ISONLYSITE varchar(10) DEFAULT NULL;


  DECLARE cur CURSOR FOR
		SELECT
      VIEWFIELDINFOID,
      TABLENAME,
      TABLEID,
      FIELDNAME,
      DBFIELDNAME,
      DBTYPE,
      DBLENGTH,
      ANOTHERNAME,
      FIELDTYPE,
      DEFAULTVALUE,
      ENMVALUE,
      NOTNULL,
      CLASSID,
      VIEWID,
      FIELDORDER,
      INOUTLINE,
      INDETAIL,
      SEARCHFIELD,
      TITLEFIELD,
      DBFIELD,
      VALIDATOR,
      CRUSER,
      CRTIME,
      FROMMAINTABLE,
      LOCATECHANNEL,
      RADORCHK,
      NOTEDIT,
      HIDDENFIELD,
      INMULTITABLE,
      DBSCALE,
      ATTRIBUTE,
      IDENTITYFIELD,
      LIBFILEDID,
      FIELDGROUPID,
      RELATIONVIEWID,
      ISURLFIELD,
      EDITSTYLE,
      APPENDIXSELECTLIB,
      APPENDIXNOTUPLOAD,
      FieldWidth,
      FieldClass,
      ISONLYSITE
    FROM v_wcm.xwcmviewfieldinfo
		ORDER BY VIEWFIELDINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET over = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_VIEWFIELDINFOID,
    f_TABLENAME,
    f_TABLEID,
    f_FIELDNAME,
    f_DBFIELDNAME,
    f_DBTYPE,
    f_DBLENGTH,
    f_ANOTHERNAME,
    f_FIELDTYPE,
    f_DEFAULTVALUE,
    f_ENMVALUE,
    f_NOTNULL,
    f_CLASSID,
    f_VIEWID,
    f_FIELDORDER,
    f_INOUTLINE,
    f_INDETAIL,
    f_SEARCHFIELD,
    f_TITLEFIELD,
    f_DBFIELD,
    f_VALIDATOR,
    f_CRUSER,
    f_CRTIME,
    f_FROMMAINTABLE,
    f_LOCATECHANNEL,
    f_RADORCHK,
    f_NOTEDIT,
    f_HIDDENFIELD,
    f_INMULTITABLE,
    f_DBSCALE,
    f_ATTRIBUTE,
    f_IDENTITYFIELD,
    f_LIBFILEDID,
    f_FIELDGROUPID,
    f_RELATIONVIEWID,
    f_ISURLFIELD,
    f_EDITSTYLE,
    f_APPENDIXSELECTLIB,
    f_APPENDIXNOTUPLOAD,
    f_FieldWidth,
    f_FieldClass,
    f_ISONLYSITE
    ;



    WHILE over= 0 DO

      # 断点续传
      IF (f_VIEWFIELDINFOID > prevViewFieldId)

        THEN

            SET @VIEWINFOID = f_VIEWINFOID;
            SET f_VIEWFIELDINFOID = f_VIEWFIELDINFOID + viewFieldIdOffset;
            SET f_TABLEID = f_TABLEID + tableInfoIdOffset;
            SET f_CLASSID = f_CLASSID + classIdOffset;
            SET f_VIEWID = f_VIEWID + viewIdOffset;
            SET f_DBFIELD = f_DBFIELD + dbFieldIdOffset;
            -- SET f_LIBFILEDID = f_LIBFILEDID + ;
            SET f_FIELDGROUPID = f_FIELDGROUPID + fieldGroupIdOffset;
            SET f_RELATIONVIEWID = f_RELATIONVIEWID + relationViewIdOffset;

            insert into trs_hycloud_iip.xwcmviewfieldinfo
              (
                VIEWFIELDINFOID,
                TABLENAME,
                TABLEID,
                FIELDNAME,
                DBFIELDNAME,
                DBTYPE,
                DBLENGTH,
                ANOTHERNAME,
                FIELDTYPE,
                DEFAULTVALUE,
                ENMVALUE,
                NOTNULL,
                CLASSID,
                VIEWID,
                FIELDORDER,
                INOUTLINE,
                INDETAIL,
                SEARCHFIELD,
                TITLEFIELD,
                DBFIELD,
                VALIDATOR,
                CRUSER,
                CRTIME,
                FROMMAINTABLE,
                LOCATECHANNEL,
                RADORCHK,
                NOTEDIT,
                HIDDENFIELD,
                INMULTITABLE,
                DBSCALE,
                ATTRIBUTE,
                IDENTITYFIELD,
                LIBFILEDID,
                FIELDGROUPID,
                RELATIONVIEWID,
                ISURLFIELD,
                EDITSTYLE,
                APPENDIXSELECTLIB,
                APPENDIXNOTUPLOAD,
                FieldWidth,
                FieldClass,
                ISONLYSITE
              )
              VALUES
                (
                  f_VIEWFIELDINFOID,
                  concat(f_TABLENAME, citySuffix),
                  f_TABLEID,
                  f_FIELDNAME,
                  f_DBFIELDNAME,
                  f_DBTYPE,
                  f_DBLENGTH,
                  f_ANOTHERNAME,
                  f_FIELDTYPE,
                  f_DEFAULTVALUE,
                  f_ENMVALUE,
                  f_NOTNULL,
                  f_CLASSID,
                  f_VIEWID,
                  f_FIELDORDER,
                  f_INOUTLINE,
                  f_INDETAIL,
                  f_SEARCHFIELD,
                  f_TITLEFIELD,
                  f_DBFIELD,
                  f_VALIDATOR,
                  f_CRUSER,
                  f_CRTIME,
                  f_FROMMAINTABLE,
                  f_LOCATECHANNEL,
                  f_RADORCHK,
                  f_NOTEDIT,
                  f_HIDDENFIELD,
                  f_INMULTITABLE,
                  f_DBSCALE,
                  f_ATTRIBUTE,
                  f_IDENTITYFIELD,
                  f_LIBFILEDID,
                  f_FIELDGROUPID,
                  f_RELATIONVIEWID,
                  f_ISURLFIELD,
                  f_EDITSTYLE,
                  f_APPENDIXSELECTLIB,
                  f_APPENDIXNOTUPLOAD,
                  f_FieldWidth,
                  f_FieldClass,
                  f_ISONLYSITE
                );

            SELECT CONCAT('成功迁移 xwcmviewfieldinfo[VIEWFIELDINFOID=', @VIEWFIELDINFOID, ']') ;
    END IF ;

		FETCH cur INTO
      f_VIEWFIELDINFOID,
      f_TABLENAME,
      f_TABLEID,
      f_FIELDNAME,
      f_DBFIELDNAME,
      f_DBTYPE,
      f_DBLENGTH,
      f_ANOTHERNAME,
      f_FIELDTYPE,
      f_DEFAULTVALUE,
      f_ENMVALUE,
      f_NOTNULL,
      f_CLASSID,
      f_VIEWID,
      f_FIELDORDER,
      f_INOUTLINE,
      f_INDETAIL,
      f_SEARCHFIELD,
      f_TITLEFIELD,
      f_DBFIELD,
      f_VALIDATOR,
      f_CRUSER,
      f_CRTIME,
      f_FROMMAINTABLE,
      f_LOCATECHANNEL,
      f_RADORCHK,
      f_NOTEDIT,
      f_HIDDENFIELD,
      f_INMULTITABLE,
      f_DBSCALE,
      f_ATTRIBUTE,
      f_IDENTITYFIELD,
      f_LIBFILEDID,
      f_FIELDGROUPID,
      f_RELATIONVIEWID,
      f_ISURLFIELD,
      f_EDITSTYLE,
      f_APPENDIXSELECTLIB,
      f_APPENDIXNOTUPLOAD,
      f_FieldWidth,
      f_FieldClass,
      f_ISONLYSITE
      ;
		
	END WHILE;
	
	CLOSE cur;

  SELECT '迁移 xwcmviewfieldinfo 完毕';
		
	END$$
DELIMITER ;