blob: 0d83c236a742dafe7c78d11d0fdc955b6e30cfd4 [file] [log] [blame]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<html>
<head>
<!--
*************************************************************************
* Copyright (c) 2004, 2006 Actuate Corporation.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
* Actuate Corporation - initial API and implementation
*
*************************************************************************
-->
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="white">
[<b>Non-API</b>] Flat file data source provider - an ODA run-time extension.
<p>
Note: <i>The implementation classes are <b>not</b> public APIs.
<br>Backward compatibility support in future releases is not guaranteed.</i>
<h2>Package Specification</h2>
The ODA flat file driver serves as an exemplary implementation
of the ODA run-time interfaces.
It performs basic data source provider functionalities including:
<p>
<ul>
<li>Executes a query on a specific flat file(CSV, SSV, TSV, PSV) using SQL-like query syntax</li>
<li>Provides the query's result set metadata</li>
<li>Retrieves the query's result set data from the CSV data file</li>
</ul>
<p>
<h2>Consuming the ODA flat file driver</h2>
<p>
<ul>
<li>Start by creating an IConnection instance by the FlatFileDriver.getConnection method.</li>
<li>Open the connection using the IConnection.open( Properties prop ) method.
The driver-specific connection property names are:
<ul>
<li>"HOME" : The directory of the flat file (required property)</li>
<li>"CHARSET" : The character set for decoding the data file; default value= "UTF-8"</li>
<li>"INCLCOLUMNNAME": Indicates whether the flat file contains column name meta-data; valid values= "YES" (default), "NO"</li>
<li>"INCLTYPELINE" : Indicates whether the flat file contains data type meta-data; valid values= "YES" (default), "NO"</li>
<li>"DELIMTYPE": Indicates the delimiter type the flat file uses; valid values="COMMA"(default), "SEMICOLON", "TAB", "PIPE"</li>
</ul>
<li>Create an IQuery instance by the IConnection.newQuery( String dataSetType ) method.</li>
<li>Execute the query by the IQuery.executeQuery method, which returns an IResultSet for data retrieval.</li>
</ul>
</p>
<h2>Data store format</h2>
<p>
The flat file ODA driver expects that both meta-data (including column names
and data types) and data are kept in a single flat file.
The first line of a flat file specifies data column names.
The second line may optionally specify the column data types.
The remaining portion of the file contains data.
</p>
<p>
<h3>Redundant Spaces</h3>
Redundant spaces are allowed in a flat file, but will be trimmed once processed by the flat file driver.
</p>
<h3>Double Quotes</h3>
Double Quotes can be used in a flat file for the purpose of clarity.
The quotes, however, will be trimmed once processed by the driver.
That is, a line in flat file like
<ul>100,"I'm a string","1"</ul>
is processed to be the same as the following line:
<ul>100,I'm a string,1</ul>
A comma within a pair of double quotes would not be treated as separator.
For example,
<ul>"I'm, however, a really normal String"</ul>
contains a single column value.
It is not considered the same as the following line:
<ul>I'm, however, a really normal String</ul>
because the second case is processed to contain three columnn values,
i.e. "I'm", "however", and "a really normal String".
<p>
<h3>Null Values</h3>
Null values are allowed. They are presented as blanks, and are comma-separated from other data.
A flat file that contains only one column, and all the data is of null value will be treated as an empty table.
</p>
<h2>Data Types</h2>
Flat file driver currently supports the following data types:
<br>INT, DOUBLE, STRING, DATE, TIME, TIMESTAMP and BIGDECIMAL.
<br>Support for BLOB and CLOB data types will be added in future.
The driver's data type codes are defined as follows:
<p>
<ul> INT = java.sql.Types.INTEGER;</ul>
<ul> DOUBLE = java.sql.Types.DOUBLE;</ul>
<ul> STRING = java.sql.Types.VARCHAR;</ul>
<ul> DATE = java.sql.Types.DATE;</ul>
<ul> TIME = java.sql.Types.TIME;</ul>
<ul> TIMESTAMP = java.sql.Types.TIMESTAMP;</ul>
<ul> BIGDECIMAL = java.sql.Types.NUMERIC;</ul>
<ul> BLOB = java.sql.Types.BLOB;</ul>
<ul> CLOB = java.sql.Types.CLOB;</ul>
</p>
<h2>SQL-like Query Syntax</h2>
The flat file driver supports limited SQL-like query syntax.
The supported syntax is:
<br>(those in square brackets are optional):
<ul>
<li><b>SELECT</b> column1 [<b>AS</b> alias1] [,column2 [<b>AS</b> alias2]]... [,columnN [<b>AS</b> aliasN]] <b>FROM</b> tableName</li>
</ul>
<ul>
<li><b>SELECT * FROM</b> tableName</li>
</ul>
The query text is case in-sensitive and allows redundant spaces.
The flat file driver does not support multiple tables in the FROM clause.
For example, a table is named "employee.csv" with columns "Id", "Name" and "HireDate",
the following queries are valid:
<ul>SELECT Id FROM employee.csv</ul>
<ul>SELECT Id,Name FROM employee.csv</ul>
<ul>SELECT Id,Name,HireDate FROM employee.csv</ul>
<ul>SELECT * FROM EMPLOYEE</ul>
<ul>select Id AS PersonnelId, Name AS EmployeeName FROM employee.csv</ul>
<ul>SELECT Name AS EmployeeName , Id , HireDate FROM employee.csv</ul>
<ul>SELECT name, name from employee.csv
<br>// This command is valid, but would simply return a result set of two columns with same data values.</ul>
However, the following queries are invalid:
<ul>Id FROM EMPLOYEE.csv <br>// missing keyword "SELECT"</ul>
<ul>Select id <br>// missing keyword "FROM"</ul>
<ul>SELECT I FROM employee.csv <br>// invalid column name</ul>
<br><br>
<b>Advanced SOL-like query-text is also defined, and it can be used in the dataset design,
for detailed information please refer to "<i>org.eclipse.datatolls.connectivity.oda.flatfile.util.querytextutil.package.html</i>"</b>
<br><br>
<!-- Put @see and @since tags down here. -->
@since 3.0
</body>
</html>