Cache（十二）父子表的创建Cache（十三）父子表查询、新增与事务

• 父子表介绍及创建

举例：

 

 创建操作步骤（不用填sqlrowid表达式，根据父表自动生成）

 

 

•  父子表的新增、查询、事务

 

 

---

练习创建父子表

1. 创建类选择持久类
2. 编写变量
   

   /// 功能：创建学生选课表
   /// 创建日期：2023年8月15日
   /// 创建人：houyuxuan
   /// 字段：StSc_RowId,DHCScCourseDR,DHCScScore
   Class User.DHCStudentCourseSFCA Extends %Persistent [ SqlTableName = DHC_StudentCourseSFCA ]
   {
   ///课程变量
   Property DHCScCourseDR As DHCCourseSFC [SqlFieldName=DHC_ScCourse_DR];
   /// 成绩
   Property DHCScScore As %Integer [SqlFieldName=DHCScScore];
   
   }

3. 新建storage
   
   
   
4. 父表中定义子表关系
   Relationship ChildCourse As DHCStudentCourseSFCA [ Cardinality = children, Inverse = DHCParRef ];
   
5. 子表中定义父表关系
   
   Relationship DHCParRef As DHCStudentSFCA [ Cardinality = parent, Inverse = ChildCourse ];
   
   
6. 子表中创建childsub，作用是获取父表的ROWID
   首先确定存储的global，父子表一般存在一个global中
   $S取父表ROWID赋值给子表，Course自己定义的节点，"0"相当于childsub第三层节点将$i取出的值赋值给第三层节点
   ///创建childsub
   Property DHCScChildSub As %Numeric(SCALE=0) [ InitialExpression = {$I(^DHCCOURSESFCA(+$S($d(initvalue):initvalue,1:%d(0)),"Course","0"))}, Required, SqlColumnNumber = 2, SqlFieldName = DHC_ScChildSub ];
   
   
   
   
7. 为childsub后每一个变量添加SqlColumnNumber
    SqlColumnNumber=2
8.  给childsub定义索引
   Index IndexName On DHCScChildSub[IdKey,PrimaryKey,Unique];
9. 添加map datamaster
   
   
   
   
10. 添加data存储
    
11. 定义节点，并将两个rowid起始值设为1
    
    
12. 添加RowId
    
13. 定义rowidname属性值
    
14. 查看新建的表结构
    
15. $s：返回第一个表达式的值 cache12  0.50.48
16. 添加索引
    

完整代码

/// 功能：创建学生选课表
/// 创建日期：2023年8月15日
/// 创建人：houyuxuan
/// 字段：StSc_RowId,DHCScCourseDR,DHCScScore
Class User.DHCStudentCourseSFCA Extends %Persistent [ SqlTableName = DHC_StudentCourseSFCA, StorageStrategy = NewStorage1 ]
{

/// 子表中定义父表	
Relationship DHCScParRef As DHCStudentSFCA [ Cardinality = parent, Inverse = ChildCourse, SqlFieldName = DHC_ScParRef ];

/// 创建childsub
Property DHCScChildSub As %Numeric(SCALE = 0) [ InitialExpression = {$INCREMENT(^DHCCOURSESFCA(+$SELECT($DATA(initvalue):initvalue,1:%d(0)),"Course","0"))}, Required, SqlColumnNumber = 2, SqlFieldName = DHC_ScChildSub ];

/// 课程变量
Property DHCScCourseDR As DHCCourseSFC [ SqlColumnNumber = 3, SqlFieldName = DHC_ScCourse_DR ];

/// 成绩
Property DHCScScore As %Integer [ SqlColumnNumber = 4, SqlFieldName = DHC_ScScore ];

/// 给childsub定义索引
Index IndexName On DHCScChildSub [ IdKey, PrimaryKey, Unique ];

Storage NewStorage1
{
<SQLMap name="DataMaster">
<Data name="DHC_ScCourse_DR">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="DHC_ScScore">
<Delimiter>"^"</Delimiter>
<Piece>2</Piece>
</Data>
<Global>^DHCCOURSESFCA</Global>
<RowIdSpec name="1">
<Expression>{L1}</Expression>
<Field>DHC_StudentSFCA.St_RowId</Field>
</RowIdSpec>
<RowIdSpec name="2">
<Expression>{L3}</Expression>
<Field>DHC_ScChildSub</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>{DHC_StudentSFCA.St_RowId}</Expression>
<StartValue>1</StartValue>
</Subscript>
<Subscript name="2">
<Expression>"Course"</Expression>
</Subscript>
<Subscript name="3">
<Expression>{DHC_ScChildSub}</Expression>
<StartValue>1</StartValue>
</Subscript>
<Type>data</Type>
</SQLMap>
<SqlRowIdName>StSc_RowId</SqlRowIdName>
<StreamLocation>^User.DHCStudentCourseSFCAS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

---

练习父子表的查询、新增

1. 终端模拟
   先取出RowId

DHC-APP>s ParRef=0
 
DHC-APP>s ParRef=$O(^DHCSTUDENTSFCA(ParRef))
 
DHC-APP>w
 
ParRef=3

 cache13 0.14.39

DHC-APP>s ChildSub=0

DHC-APP>s ChildSub=$O(^DHCSTUDENTSFCA(ParRef,"Course",ChildSub))
 
DHC-APP>w
 
ChildSub=1
ParRef=3