1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
--
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
----------------------------------------------------------------------------------------
--
-- This script demonstrates how to declare and use several sample
-- table functions.
--
----------------------------------------------------------------------------------------
connect 'jdbc:derby:memory:vtitest;create=true';
----------------------------------------------------------------------------------------
--
-- Declare the table functions.
--
----------------------------------------------------------------------------------------
--
-- Declare a table function which reads an Apache server log.
--
create function apacheNaturalLogFile( fileURL varchar( 32672 ) ) returns table
(
IP varchar(100),
accessDate timestamp,
request clob,
statusCode int,
fileSize int,
referrer varchar(200),
userAgent clob
)
language java parameter style derby_jdbc_result_set no sql
external name 'org.apache.derbyDemo.vtis.example.ApacheServerLogVTI.apacheNaturalLogFile';
--
-- Declare a table function which reads a Derby JIRA report
--
create function apacheNaturalJiraReport( fileURL varchar( 32672 ) ) returns table
(
keyCol int,
type varchar(20),
priority varchar(10),
status varchar(20),
component varchar(50),
customfieldvalue varchar(200),
title varchar(500)
)
language java parameter style derby_jdbc_result_set no sql
external name 'org.apache.derbyDemo.vtis.example.DerbyJiraReportVTI.apacheNaturalJiraReport';
--
-- Declare a table function which reads the output of an 'svn log' command
--
create function svnLogReader( logFileName varchar( 32672 ) )
returns TABLE
(
XID varchar( 15 ),
committer varchar( 20 ),
commit_time timestamp,
line_count varchar( 10 ),
description varchar( 32672 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
no sql
external name 'org.apache.derbyDemo.vtis.example.SubversionLogVTI.subversionLogVTI'
;
--
-- Register a table function to read a Derby message file
--
create function propertyFileVTI( fileName varchar( 32672 ) )
returns TABLE
(
messageID varchar( 20 ),
messageText varchar( 32672 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
no sql
external name 'org.apache.derbyDemo.vtis.example.PropertyFileVTI.propertyFileVTI'
;
----------------------------------------------------------------------------------------
--
-- Read a log file dumped as a flat file
--
----------------------------------------------------------------------------------------
-- how active were the committers in 2006?
select committer, count(*) as commits
from table( svnLogReader( 'svn_log.txt' ) ) s
where commit_time between timestamp( '2006-01-01 00:00:00' ) and timestamp( '2007-01-01 00:00:00' )
group by committer
;
----------------------------------------------------------------------------------------
--
-- Read a property file of Derby messages
--
----------------------------------------------------------------------------------------
-- find the messages which have not been translated into french
select messageID, substr( m_english.messageText, 1, 100 )
from table( propertyFileVTI( 'messages_en.properties' ) ) m_english
where m_english.messageID not in
(
select m_french.messageID
from table( propertyFileVTI( 'messages_fr.properties' ) ) m_french
);
----------------------------------------------------------------------------------------
--
-- XML VTIs
--
----------------------------------------------------------------------------------------
--
-- Read from the XML log file produced by an Apache web server
--
-- this vti treats accessDate as a timestamp and fileSize as an int
select s.*
from table( apacheNaturalLogFile( 'ApacheServerLog.xml' ) ) s
;
-- look for relevant status codes
select s.*
from table( apacheNaturalLogFile( 'ApacheServerLog.xml' ) ) s
where s.statusCode = 206
;
-- look for relevant IP addresses
select s.*
from table( apacheNaturalLogFile( 'ApacheServerLog.xml' ) ) s
where IP like '208%'
;
-- look for log records in a time range
select s.*
from table( apacheNaturalLogFile( 'ApacheServerLog.xml' ) ) s
where accessDate between timestamp( '2002-07-01 08:40:56.0' ) and timestamp( '2002-07-01 08:42:56.0' )
;
--
-- Read from the XML log file produced by a JIRA report
--
-- treat keys as ints and sort Derby JIRAs by key
select s.*
from table( apacheNaturalJiraReport( 'DerbyJiraReport.xml' ) ) s
where s.keyCol between 2800 and 2950
order by keyCol
;
-- eliminate uninteresting Derby JIRAs
select s.*
from table( apacheNaturalJiraReport( 'DerbyJiraReport.xml' ) ) s
where type != 'Sub-task'
order by keyCol
;
|
