SQL修改记录 /*2019-09-05 开始这个文件*/ /*2019-07-24 修改字段为bigint*/ Alter table Achivement alter column achivementid set data type bigint ; Alter table BadgeLog alter column badgelogid set data type bigint ; Alter table ConnectLog alter column connectlogid set data type bigint ; Alter table DeviceUseLog alter column deviceuselogid set data type bigint ; Alter table EmoneyLog alter column emoneylogid set data type bigint ; Alter table ExamAnswer alter column examanswerid set data type bigint ; Alter table HearAnswer alter column examanswerid set data type bigint ; Alter table PointLog alter column pointlogid set data type bigint ; Alter table RaceScore alter column racescoreid set data type bigint ; Alter table WordAchive alter column wordachiveid set data type bigint ; /*修改字段为bigint结束*/ ALTER SEQUENCE seq_achivementid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_badgelogid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_connectlogid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_deviceuselogid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_emoneylogid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_examanswerid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_hearanswerid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_pointlogid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_racescoreid MAXVALUE 9223372036854775807 ; ALTER SEQUENCE seq_wordachiveid MAXVALUE 9223372036854775807 ; alter table UnivAssignComm alter column voice set data type varchar(100) , alter column description set data type varchar(1000) , alter column memo set data type varchar(1000) ; alter table UnivAssignComm alter column voice set default '' , alter column description set default '' , alter column memo set default '' ; /*修改高教成绩为带小数点*/ Alter table UnivAchivement alter column score set data type decimal(5,2) , alter column accuracy set data type decimal(5,2) , alter column fluent set data type decimal(5,2) , alter column integrity set data type decimal(5,2) ; Alter table UnivHearAnswer alter column score set data type decimal(5,2) , alter column origiscore set data type decimal(5,2) , add column trueScore decimal(5,2) default 0 ; Alter table UnivExamAnswer alter column origiscore set data type decimal(5,2) , alter column score set data type decimal(5,2) , alter column truescore set data type decimal(5,2) , alter column machinescore set data type decimal(5,2) , alter column humanscore set data type decimal(5,2) ; Alter table UnivRaceScore alter column score set data type decimal(5,2) , alter column accuracy set data type decimal(5,2) , alter column fluent set data type decimal(5,2) , alter column integrity set data type decimal(5,2) ; Alter table UnivRaceMaxScore alter column maxScore set data type decimal(5,2) ; Alter table UnivWordAchive alter column score set data type decimal(5,2) , alter column accuracy set data type decimal(5,2) , alter column fluent set data type decimal(5,2) , alter column integrity set data type decimal(5,2) ; Alter table UnivFinishLessonStat alter column maxscore set data type decimal(5,2) , alter column avgscore set data type decimal(5,2) ; /*UnivUserHonor.totalScore 不改*/ Alter table UnivUserHonor alter column totalScore set data type decimal(25,2) ; Alter table UnivPronoStat alter column totalscore set data type numeric(30,2) default 0.0 ; /*高教同基教的保持一致*/ Alter table UnivExam add column isoral integer default 1 , add column teacherId bigint default 0 ; comment on column UnivExam.isoral is '考试类型，0听说;1口语;2听力;3同步训练' ; comment on column UnivExam.teacherid is '教师id' ; Alter table UnivClassOwnexam add column objectType smallint default 1 ; comment on column UnivClassOwnexam.classesId is '班级ID，或学校ID，或地区ID' ; comment on column UnivClassOwnexam.objectType is '1班级小组，2学校，3区县，4市，5省' ; alter table univassignment add assigntype character varying(50) DEFAULT 'L'::character varying ; COMMENT ON COLUMN univassignment.assigntype IS '作业类型，L课程默认，W单词' ; alter table univclassownhear add objecttype smallint DEFAULT 1 ; COMMENT ON COLUMN classownhear.objecttype IS '1班级小组，2学校， 3区县，4市，5省' ; alter table univstudent add signature character varying(300) DEFAULT ''::character varying; COMMENT ON COLUMN univstudent.signature IS '个性签名'; alter table univteacher add signature character varying(300) DEFAULT ''::character varying; COMMENT ON COLUMN univteacher.signature IS '个性签名'; /*高教所有听力考试，包括老的和最新听力考试，isoral -1 老的； 2019-06-12*/ Create or Replace view V_UnivHearExam as select examid , examtype , isrecommend , title , description , 1 as exampapercount , concat('[{"paperId":' , hearpaperid ,'}]') as exampapers , created , starttime , endtime , isnetexam , teacherid , -1 as isoral from UnivHearexam UNION select a.examid , a.examtype , a.isrecommend , a.title , a.description , a.exampapercount , a.exampapers , a.created , a.starttime , a.endtime , a.isnetexam , a.teacherid , a.isoral from UnivExam a where a.isoral = 2 ; /*视图 ，教师拥有及被授权的听力试卷*/ create or replace view V_UnivhearPaper as SELECT m.hearpaperid, n.teacherId, m.examlevel, m.title, '' as subtitle,'' as memo, '' as content, m.status, m.areaid , -1 as isOral FROM HearPaper m INNER JOIN ( SELECT b.hearpaperid, b.objectId as teacherId FROM UnivHearPaperAuth b where b.objectType =1 UNION SELECT b.hearpaperid, c.teacherId FROM UnivTeacher c inner join UnivHearPaperAuth b on c.schoolId = b.objectId and b.objectType =2 UNION SELECT b.hearpaperid, c.teacherId FROM UnivTeacher c inner join UnivHearPaperAuth b on c.areaId = b.objectId and b.objectType =3 UNION SELECT b.hearpaperid, c.teacherId FROM UnivTeacher c inner join UnivHearPaperAuth b on floor( c.areaId /100 ) * 100 = b.objectId and b.objectType =4 UNION SELECT b.hearpaperid, c.teacherId FROM UnivTeacher c inner join UnivHearPaperAuth b on floor( c.areaId /10000 ) * 10000 = b.objectId and b.objectType =5 ) as n ON m.hearpaperid = n.hearpaperid UNION SELECT m.examPaperId AS hearpaperid, n.teacherId , m.examLevel , m.title ,'' as subtitle,'' as memo, '' as content , m.status , m.areaid , m.isOral FROM ExamPaper M INNER JOIN ( SELECT b.examPaperId , b.objectId as teacherId FROM UnivExamPaperAuth b where b.objectType =1 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on c.schoolId = b.objectId and b.objectType =2 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on c.areaId = b.objectId and b.objectType =3 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on floor( c.areaId /100 ) * 100 = b.objectId and b.objectType =4 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on floor( c.areaId /10000 ) * 10000 = b.objectId and b.objectType =5 ) as n ON m.isOral = 2 and m.examPaperId = n.examPaperId ; /*视图 ，外语学院教师拥有及被授权的口语试卷*/ create or replace view V_UnivexamPaper as SELECT m.examPaperId , m.authorType , n.teacherId , m.examLevel , m.title , m.isRecommend , '' as part1Text , m.part1Voice , m.part1OrigiScore , '' as part2Text , m.part2Voice , '' as part2Question , m.part2EachOrigiScore , m.totalScore , m.status , m.areaid , m.content , m.machineScore , m.humanScore , m.usedArea , m.paperFile , m.md5Code , m.isOral FROM ExamPaper M INNER JOIN ( SELECT b.examPaperId , b.objectId as teacherId FROM UnivExamPaperAuth b where b.objectType =1 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on c.schoolId = b.objectId and b.objectType =2 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on c.areaId = b.objectId and b.objectType =3 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on floor( c.areaId /100 ) * 100 = b.objectId and b.objectType =4 UNION SELECT b.examPaperId , c.teacherId FROM UnivTeacher c inner join UnivExamPaperAuth b on floor( c.areaId /10000 ) * 10000 = b.objectId and b.objectType =5 ) as n ON m.examPaperId = n.examPaperId ; /*关于高教的修改结束*/