ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/bioseg/trunk/sql/bioseg.sql
Revision: 3
Committed: Tue Aug 14 23:05:57 2007 UTC (12 years, 2 months ago) by kmr
File size: 4081 byte(s)
Log Message:
Moved all files to trunk directory.

Line User Rev File contents
1 kmr 1 --
2     -- Test bioseg datatype
3     --
4    
5     --
6     -- first, define the datatype. Turn off echoing so that expected file
7     -- does not depend on contents of bioseg.sql.
8     --
9     SET client_min_messages = warning;
10     \set ECHO none
11     \i bioseg.sql
12     \set ECHO all
13     RESET client_min_messages;
14    
15     --
16     -- testing the input and output functions
17     --
18    
19     -- Any number
20     SELECT '1'::bioseg AS bioseg;
21     SELECT '99999999'::bioseg AS bioseg;
22    
23    
24     -- Finite intervals
25     SELECT '1..2'::bioseg AS bioseg;
26     SELECT '10000000..20000000'::bioseg AS bioseg;
27    
28     -- invalid input
29     SELECT ''::bioseg AS bioseg;
30     SELECT '..10'::bioseg AS bioseg;
31     SELECT '0..10'::bioseg AS bioseg;
32     SELECT '0'::bioseg AS bioseg;
33     SELECT '-10'::bioseg AS bioseg;
34     SELECT '-10..-1'::bioseg AS bioseg;
35     SELECT '-10..0'::bioseg AS bioseg;
36     SELECT '-10..1'::bioseg AS bioseg;
37     SELECT 'ABC'::bioseg AS bioseg;
38     SELECT '1ABC'::bioseg AS bioseg;
39     SELECT '1.'::bioseg AS bioseg;
40     SELECT '1.....'::bioseg AS bioseg;
41     SELECT '.1'::bioseg AS bioseg;
42     SELECT '1..2.'::bioseg AS bioseg;
43     SELECT '1 e7'::bioseg AS bioseg;
44     SELECT '1e700'::bioseg AS bioseg;
45    
46     --
47     -- testing the operators
48     --
49    
50     -- equality/inequality:
51     --
52     SELECT '24..33'::bioseg = '24..33'::bioseg AS bool;
53     SELECT '24..50'::bioseg != '24..33'::bioseg AS bool;
54    
55     -- overlap
56     --
57     SELECT '2'::bioseg && '2'::bioseg AS bool;
58     SELECT '2'::bioseg && '2'::bioseg AS bool;
59     SELECT '2..2'::bioseg && '2..2'::bioseg AS bool;
60     SELECT '2..2'::bioseg && '2'::bioseg AS bool;
61     SELECT '2..2'::bioseg && '2'::bioseg AS bool;
62     SELECT '2..2'::bioseg && '2'::bioseg AS bool;
63     SELECT '2'::bioseg && '2..2'::bioseg AS bool;
64     SELECT '2'::bioseg && '2..2'::bioseg AS bool;
65     SELECT '2'::bioseg && '2..3'::bioseg AS bool;
66    
67     -- overlap on the left
68     --
69     SELECT '2'::bioseg &< '2'::bioseg AS bool;
70     SELECT '2'::bioseg &< '3'::bioseg AS bool;
71     SELECT '1..2'::bioseg &< '2'::bioseg AS bool;
72     SELECT '1..2'::bioseg &< '3'::bioseg AS bool;
73     SELECT '1..2'::bioseg &< '1..2'::bioseg AS bool;
74     SELECT '1..2'::bioseg &< '1..3'::bioseg AS bool;
75     SELECT '1..2'::bioseg &< '2..3'::bioseg AS bool;
76     SELECT '1..2'::bioseg &< '3..4'::bioseg AS bool;
77    
78     -- overlap on the right
79     --
80     SELECT '2'::bioseg &> '2'::bioseg AS bool;
81     SELECT '3'::bioseg &> '2'::bioseg AS bool;
82     SELECT '2'::bioseg &> '1..2'::bioseg AS bool;
83     SELECT '3'::bioseg &> '1..2'::bioseg AS bool;
84     SELECT '1..2'::bioseg &> '1..2'::bioseg AS bool;
85     SELECT '1..3'::bioseg &> '1..3'::bioseg AS bool;
86     SELECT '2..3'::bioseg &> '1..2'::bioseg AS bool;
87     SELECT '3..4'::bioseg &> '1..2'::bioseg AS bool;
88    
89     -- left
90     --
91     SELECT '2'::bioseg << '2'::bioseg AS bool;
92     SELECT '2'::bioseg << '3'::bioseg AS bool;
93     SELECT '2..2'::bioseg << '2'::bioseg AS bool;
94     SELECT '2..3'::bioseg << '3'::bioseg AS bool;
95     SELECT '2..3'::bioseg << '3'::bioseg AS bool;
96     SELECT '2..3'::bioseg << '2..3'::bioseg AS bool;
97     SELECT '2..3'::bioseg << '2..4'::bioseg AS bool;
98     SELECT '2..3'::bioseg << '3..4'::bioseg AS bool;
99     SELECT '2..3'::bioseg << '3..5'::bioseg AS bool;
100    
101     -- right
102     --
103     SELECT '2'::bioseg >> '2'::bioseg AS bool;
104     SELECT '3'::bioseg >> '2'::bioseg AS bool;
105     SELECT '2'::bioseg >> '2..2'::bioseg AS bool;
106     SELECT '3'::bioseg >> '2..3'::bioseg AS bool;
107     SELECT '2..3'::bioseg >> '2..3'::bioseg AS bool;
108     SELECT '3..4'::bioseg >> '2..3'::bioseg AS bool;
109    
110    
111     -- "contained in" (the left value belongs within the interval specified in the right value):
112     --
113     SELECT '2'::bioseg <@ '2'::bioseg AS bool;
114     SELECT '2'::bioseg <@ '2..3'::bioseg AS bool;
115     SELECT '3'::bioseg <@ '2..3'::bioseg AS bool;
116     SELECT '3..4'::bioseg <@ '2..5'::bioseg AS bool;
117     SELECT '2..5'::bioseg <@ '2..5'::bioseg AS bool;
118    
119     -- "contains" (the left value contains the interval specified in the right value):
120     --
121     SELECT '2'::bioseg @> '2'::bioseg AS bool;
122     SELECT '2..4'::bioseg <@ '3'::bioseg AS bool;
123     SELECT '3'::bioseg <@ '2..4'::bioseg AS bool;
124    
125     -- Load some example data and build the index
126     --
127     CREATE TABLE test_bioseg (s bioseg);
128    
129     \copy test_bioseg from 'data/test_bioseg.data'
130    
131     CREATE INDEX test_bioseg_ix ON test_bioseg USING gist (s);
132     SELECT count(*) FROM test_bioseg WHERE s @> '11..11';
133    
134     -- Test sorting
135     SELECT * FROM test_bioseg WHERE s @> '11..20' GROUP BY s;
136    
137     DROP TABLE test_bioseg;