|| ||      

---

Table VxSegmentStringFeature_tab

CREATE TABLE VxSegmentStringFeature_tab (
    SegmentId         NUMBER         NOT NULL,
    FieldName         VARCHAR2(20)   NOT NULL,
    Value             VARCHAR2(512),
  CONSTRAINT VxSegmentStringFeat_pk PRIMARY KEY(SegmentId, FieldName),
  CONSTRAINT VxSegmentStringFeat_fk FOREIGN KEY(SegmentId)
      REFERENCES VxSegment_tab ON DELETE CASCADE
);

The features of each segment are stored in Table VxSegmentStringFeature_tab, and Tables VxSegmentDoubleFeature_tab and VxSegmentIntFeature_tab depending on their type. We will introduce first the table to store string features: VxSegmentStringFeature_tab. This table contains the Id of the segment, the name of the feature and its value.

Query examples:

List all string features from a segmentation:

SQL> SELECT DISTINCT f.FieldName
FROM VxSegmentation_tab seg, VxSegment_tab s, VxSegmentStringFeature_tab f
WHERE (seg.Id = s.segmentationId) AND (seg.VideoName = 'friends418')
 AND (seg.Name = 'blocks') AND (f.SegmentId = s.Id);

List blocks of 'friends418' with type 'effect':

SQL> SELECT s.SegIndex, s.StartPos, s.EndPos
FROM VxSegmentation_tab seg, VxSegment_tab s, VxSegmentStringFeature_tab f
WHERE (seg.Id = s.segmentationId) AND (seg.VideoName = 'friends418')
  AND (seg.Name = 'blocks') AND (f.SegmentId = s.Id) AND (f.FieldName = 'type')
  AND (f.Value = 'effect');

List blocks of 'friends418' with all their string features:

SQL> SELECT s.segIndex, s.startPos, s.endPos, f0.value description, 
  f1.value effect, f2.value type 
FROM VxSegmentation_tab seg, VxSegment_tab s, 
  VxSegmentStringFeature_tab f0, VxSegmentStringFeature_tab f1,
  VxSegmentStringFeature_tab f2
WHERE (seg.videoName = 'friends418') AND (seg.Id = s.segmentationId)
  AND (seg.name = 'blocks') 
  AND (f0.segmentId (+)= s.Id) AND (f0.fieldName (+)= 'description')
  AND (f1.segmentId (+)= s.Id) AND (f1.fieldName (+)= 'effect')
  AND (f2.segmentId (+)= s.Id) AND (f2.fieldName (+)= 'type');

Use the following result formatting to get a better display of results:

COLUMN segIndex HEADING '#'
COLUMN segIndex FORMAT 999 
COLUMN startPos FORMAT 999999
COLUMN endPos FORMAT 999999
COLUMN description FORMAT a30
COLUMN effect FORMAT a10
COLUMN type FORMAT a10

Segmentation views:

The previous query can be introduced as a view, and then be able to perform queries using a simpler statement (but maybe slower):

SQL> CREATE VIEW VxBlocksSegment_tab AS
SELECT seg.Id, s.segmentationId, s.segIndex, s.startPos, s.endPos, 
  f0.value description, f1.value effect, f2.value type 
FROM VxSegmentation_tab seg, VxSegment_tab s, 
  VxSegmentStringFeature_tab f0, VxSegmentStringFeature_tab f1,
  VxSegmentStringFeature_tab f2
WHERE (seg.Id = s.segmentationId) AND (seg.name = 'blocks') 
  AND (f0.segmentId (+)= s.Id) AND (f0.fieldName (+)= 'description')
  AND (f1.segmentId (+)= s.Id) AND (f1.fieldName (+)= 'effect')
  AND (f2.segmentId (+)= s.Id) AND (f2.fieldName (+)= 'type');

Example of querying VxBlocksSegment_tab view:

SQL> SELECT b.segIndex, b.startPos, b.endPos, b.effect
FROM VxSegmentation_tab seg, VxBlocksSegment_tab b
WHERE (b.segmentationId = seg.Id) AND (seg.VideoName = 'friends418') 
  AND (b.type = 'effect');

And here is the same query, without using the view:

SQL> SELECT s.segIndex, s.startPos, s.endPos, f1.value effect
FROM VxSegmentation_tab seg, VxSegment_tab s, 
  VxSegmentStringFeature_tab f1, VxSegmentStringFeature_tab f2
WHERE (s.segmentationId = seg.Id) AND (seg.VideoName = 'friends418') 
  AND (seg.Name = 'blocks') AND (f1.SegmentId = s.Id) 
  AND (f2.SegmentId = s.Id) AND (f2.FieldName = 'type') 
  AND (f2.Value = 'effect') AND (f1.FieldName = 'effect');

---

Go to the next section or return to the index.

---