根据有序列表中的更改修改数据库表
此问题一直困扰着我一个多星期,现在我迫切需要一个解决方案。根据有序列表中的更改修改数据库表
我一直在为我们基于ColdFusion的CMS系统建立一个模块,它允许用户建立一个具有一系列类的课程。序列顺序表示的先决条件,所以例如:
1类 2类 第3类 第4类
1级是第一类中的序列中,并且需要以前进到2级等...
我已经成功地允许用户创建所述课程,但是编辑它们是个诀窍。在我的编辑界面中,序列用CFSELECT列表框表示。用户可以在列表上执行4个操作:向上移动,向下移动,删除和添加。这使用户能够完全操纵列表。
问题:
我们的数据库是真的老了,其实我们整个的CMS系统,建于99年,从而用一个相当处理过时,有点设计不当的数据集。序列存储如下:
每门课程存储在一个名为moodle_programs的表中。其唯一标识符是自动增量的列mpID。
序列中的每个类都存储在一个名为moodle_course的表中,其唯一标识符是mcID,它是一个自动增量。
我们保存它具有以下结构,称为moodle_relationships在查找表的关系:
- MRID - 自动递增
- MPID - 进程ID
- MCID - 类ID
- 活跃 - 是/否
- 前身 - 序列中的第一行设置为0,其余为前一行的mrID
moodle_relationships中的一行表示序列中的一个类。
这意味着,我的算法基本上需要考虑以下几点:
如果当前类列表=该课程 当前的行数,然后用正确的价值观 否则覆盖MCID如果当前类列表<达到该课程 然后覆盖MCID直到序列的结束的当前的行数,并设置所有剩余行到活性=“N”
这是通过此算法来完成:
//comma deliminated list of mcIds before edit
<CFSET preclasses = listToArray(#PREsectionOrder#, ",")>
// number of classes
<CFSET numpreClasses = ArrayLen(#preclasses#)>
//comma deliminated list of mcIds after edit
<CFSET classes = listToArray(#sectionOrder#, ",")>
//number of classes
<CFSET numClasses = ArrayLen(#classes#)>
<!--- Modify moodle_relationships --->
<CFQUERY NAME="Course_seq" DATASOURCE="#CMS_SD#">
SELECT *
FROM moodle_relationships
WHERE mpID = #URL.id#
</CFQUERY>
<CFSET row = 0>
<CFLOOP QUERY="Course_seq">
<CFSET row = #Course_seq.CurrentRow#>
<CFQUERY NAME="relation" DATASOURCE="#CMS_SD#">
SELECT *
FROM moodle_relationships
WHERE mrID=#Course_seq.mrID#
</CFQUERY>
<CFIF #row# LTE #numClasses#>
<!--Change Classes-->
<CFQUERY NAME="change" DATASOURCE="#CMS_SD#">
UPDATE moodle_relationships
SET mcID='#classes[row]#', active='Y'
WHERE mrID=#relation.mrID#
</CFQUERY>
<CFELSEIF (#row# GT #numClasses#) AND (#row# LTE #numpreClasses#)>
<!--Set to N-->
<CFQUERY NAME="set" DATASOURCE="#CMS_SD#">
UPDATE moodle_relationships
SET active='N'
WHERE mrID=#relation.mrID#
</CFQUERY>
</CFIF>
</CFLOOP>
现在我遇到的问题是当前类的序列超过了上一个序列中的行数。我需要注意什么时候发生这种情况,并为其余行执行INSERT语句。
例如:
原始序列 - 1级,2级,3级 新的序列 - 1级,2级,3级,4级
由于我的循环只会重复三次,它从不考虑第四个索引。
我该如何解决这个问题?
编辑
我设法通过创建CFSET计数= 0来解决这个问题我自己,然后计算+ = 1,当一个项目在数据库中被修改。之后,我创建了这个代码片段。
<CFIF #count# LT #numClasses#>
<CFLOOP FROM="#count#" TO="#numClasses#" INDEX="i">
<CFQUERY NAME="mrID" DATASOURCE="#CMS_SD#">
SELECT MAX(mrID) AS MAXmrID
FROM moodle_relationships
WHERE mpID = #URL.id#
</CFQUERY>
<CFSET ID = #mrID.MAXmrID# - 1>
<CFQUERY NAME="new" DATASOURCE="#CMS_SD#">
INSERT INTO moodle_relationships (mpID, mcID, active, predecessor)
VALUES ('#URL.id#', '#classes[i]#', 'Y', '#ID#')
</CFQUERY>
</CFLOOP>
它只是一个无赖的错误我所看到的作品,其中更新表和回到编辑课程后,有时我注意到,一类已被更改为不同的类,更具体一个已经存在于序列中的类创建一个副本。
我想它与主循环中的比较运算符有关,虽然我无法看到它在哪里,但对于一行的考虑重叠。
任何想法?
有很多方法可以做到这一点。一种方法是删除所有现有记录并输入新的记录。
<cfscript>
/* Count the number of classes before the edit */
preclasses = listToArray(PREsectionOrder, ",");
numpreClasses = ArrayLen(preclasses);
/* Count the number of classes after edit */
classes = listToArray(sectionOrder, ",");
numClasses = ArrayLen(classes);
</cfscript>
<!--- Making sure we have a 'numeric' URL id --->
<CFPARAM name="URL.id" default="0" type="numeric" />
<CFSET courseId = URL.id />
<!--- Only continue if the courseId is not the default 0--->
<CFIF courseId neq 0>
<!--- Delete the existing course and its combination classes --->
<!--- Query checks for a specific course and class id --->
<CFQUERY NAME="Course_seq" DATASOURCE="#CMS_SD#">
DELETE
FROM moodle_relationships
WHERE mpID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#courseId#" />
AND mcID IN (<cfqueryparam cfsqltype="cf_sql_numeric" value="#PREsectionOrder#" list="true" separator=","/>)
</CFQUERY>
<!--- Create and Set the predecessor value to default 0--->
<cfset predecessorValue = 0 />
<!--- Loop over the array of new classId and Insert them--->
<CFLOOP array="#classes#" index="newClass">
<CFQUERY NAME="insertingNewCourse" DATASOURCE="#CMS_SD#">
INSERT INTO moodle_relationships
(mpID, mcID, active, predecessor)
VALUES (
<cfqueryparam cfsqltype="cf_sql_numeric" value="#courseId#" />,
<cfqueryparam cfsqltype="cf_sql_numeric" value="#newClass#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="Y" />,
<cfqueryparam cfsqltype="cf_sql_numeric" value="#predecessorValue#" />
)
</CFQUERY>
<!--- The first one goes as zero. the next one shall be the just inserted classId --->
<cfset predecessorValue = newClass />
</CFLOOP>
</CFIF>
注意:因为,有删除参与,确保在运行测试数据库上运行此代码。
嗯我不知道为什么我没有想到先删除行。我试过这个代码,它比我的体积小很多。然而,我忘记提及当一段关系不再存在时你删除一个类,关系行的活动值应该设置为'N'。 – 2013-03-05 07:58:44
很高兴你通过测试。要将标志设置为'N',您可以将删除代码更改为更新。此外,投票的答案是否有帮助:) – Sanjeev 2013-03-06 02:19:44
完美!我修改了标记为'N'的查询,它的功能就像一个魅力。再次感谢,很高兴能够结束这个项目,这是最后一个细节。 – 2013-03-06 19:41:14