| <!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> |