This project is read-only.
T-SFQL
T-SFQL (Transact-Structured Full-text Query Language) is a T-SQL standards-based language for structure of full-text query. Hubble.net proposed it in 2009.
SQL for Database
Create database

EXEC SP_ADDDATABASE

The first parameter is the database name that refers to the Hubble.net.

The second parameter is the default full-text index path of the database.

The third parameter is the default database adapter name. If adapter is not specified when table is created, it will use the default adapter in this table's database. Hubble.net includes a database adapter for sql server 2005.

The fourth parameter is the default connection string, This string is used to connect relational database via Hubble.net.
If connection string is not specified when creating a table, it will use the default connection string of the table's database.

Creating database will only create connectivity information between hubble.net and relational database. It won't create a real database in relational database. This connectivity information is specified in 3rd and 4th parameter.

Example:
exec sp_adddatabase 'News', 'd: \ test \ news \', 'SQLSERVER2005', 'Data Source = (local); Initial Catalog = News; Integrated Security = True';
Delete database
EXEC SP_DROPDATABASE
The first parameter is the database name used in Hubble.net.
This operation only deletes the relation from hubble.net to relational database. It does not delete the database in relational database.

Example:
EXEC SP_DROPDATABASE 'News'
SQL for table
Create table
{

Syntax
[Directory (directory)]-Optional
[IndexOnly]-Optional
[ForceCollectCount (count)]-Optional
[DBTableName (table name)]-Optional
[DBAdapter (adapter name)]-Optional
[DBConnect (connection string)]-Optional
CREATE TABLE
table_name
(
(<Column_definition>
| Column_name AS computed_column_expression
| <Table_constraint>) [, ... n]
)

Property

[Directory (directory)]-Optional
This attribute specifies the full-text index in the local address. This attribute is optional. When it is not specified, Hubble.net will use the default directory for that database which the table belongs to.
Full-text index will be built under this address.

[IndexOnly]-Optional
This attribute specifies text data tables for just indexing. This attribute is optional. If it is set to index only, the implementation of the insert, modify, and delete statements with only modifying the index, but not the data itself.
This attribute is useful in Rebuild or a database-driven index.

[ForceCollectCount (count)]-Optional
the number of data records Mandatory recycling of optional attributes.
When the bulk insert, Hubble.net not immediately update the index, but to insert more than ForceCollectionCount update the index when doing so in order to maximize the efficiency of the index. ForecCollectionCount The default value for 5000. You can modify this value to any positive integer.
[DBTableName (table name)]-Optional

The corresponding table in the database table name, optional attributes.
As Hubble.net full-text index and the database is associated with, Hubble.net responsible for indexing, the database for storage. If you want to Hubble.net the table name and database table name not the same, you can set this property. By default, the database table name and table name the same as Hubble.net.

[DBAdapter (adapter name)]-Optional
The database adapter name, optional attributes. If not specified, the table where the database will use the default database adapter.
Here to specify which database adapter. Hubble.net currently offers MS SQLSERVER 2005/2008 database adapter. Users can implement IDBAdapter interface, and through his SP_AddExternalReference system stored procedure to prepare the database adapter is installed in the Hubble.net.

[DBConnect (connection string)]-Optional

Connection string, optional attributes. If not specified, the table where the database will use the default database connection strings.
This attribute specifies the database adapter to connect the corresponding database connection string.

Data Type
The data types are currently supported are:

Int, TinyInt, SmallInt, BigInt, Float, DateTime, SmallDateTime, Date, NVarchar, Varchar, NChar, Char

in which

The range of datetime is same as the range of .net datetime data type. Precision is milliseconds

The range for smalldatetime is January 1, 1980 0:0:0 to December 31, 2047 23:59:59, Precision is to seconds

The range of Date is from 1st of January 0001 to 1st of January in 5883516, Measure unit is day

nvarchar, varchar, nchar, char the four string types can support a specified length and unlimited length.

When leng is limited, limit can be specified as nvarchar (max)

If the string type is a non-text mode index (Untokenized index) , the length of the maximum is 32.

column_definition
Column Name: Same as T-SQL, if it is keyword, use [] specify the column names.

Data Type
See previous section

Index type – optional

Tokenized
Tokenized index type means that the field is full-text index field. If you specify the type, you must specify the Analyzer, the name of the parser

Untokenized
Untokenized index type means that the field is a non-full-text index field. It is usually for some price, size, classification metadata fields. if it is required to filter metadata in the search text, it must set the appropriate metadata field as Untokenized type index field.

None
The default index type, only storage.

Analyzer
If the index type is Tokenized, it must use Analyzer keyword to set analyzer name.

If Analyzer is 'SimpleAnalyzer', users can implement IAnalyzer interface, and install it via SP_AddExternalReference system stored procedure.

NULL, NOT NULL
Tokenized, Untokenized type index fields must be NOT NULL, None type index fields can be NULL.

Default-Optional
The default value of the specified field
up to here ---

PRIMARY KEY
The definition in the field to specify the field in a primary key field. Note that by Hubble.net set the primary key field is not a clustered index, while the non-unique clustered index. Hubble.net table clustered index field DocId.
FASTEST
May declare that a full-text index field is the quickest way to use indexing, the quickest way to build an index, the inverted index component words are not stored location information, reducing the size of the index.
To note that the FASTEST way if you choose to use indexing, you can not come to the full text of Like a query can only use the Match.

DocId Field
DocId field is Hubble.net established for each field in the table by default, the user in the preparation of construction of the table statement is not required to specify this field. This field is self-growth field and is the only index of aggregation. This field is used to locate specific records. User inserted data does not need to specify this field.

Other constraints
Hubble.net order to simplify the construction of the table statement, under construction in the table statement does not provide definitions of other constraints, if only the other constraints, such as the scope of the field, you can SP_ExcuteSql system stored procedure to set up.
Note: The construction of the table statement will automatically be in the corresponding relational database to create the corresponding data sheet.
Example

1. A simple press table (specify the local address and the database adapter properties)


Create table News

(

Title nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Content nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Time SmallDateTime Untokenized NOT NULL Default '1990-01-01 ',

Url nvarchar (max)

);
exec SP_ExcuteSql 'Create index I_Time on news (time)';



CREATE TABLE statement for more than the establishment of a News full-text index tables, full-text index built on the News table where the database specified in the default directory, the database adapter and the connection string also use their respective default values specified in the database.
This table has four fields, including Title, Content for the full-text indexing field, Time for non-full-text indexing field, Url to only store the field.
Table building is completed, we adopted SP_ExcuteSql direct manipulation SQLSERVER2005 database Test database to create the database for the Time field, the establishment of a non-clustered index,
This index in just the scope of inquiry on the Time to be useful.


2. A simple press table (specify the local address and the database adapter properties)

[Directory ( 'd: \ test \ news \')]

[DBAdapter ( 'SQLSERVER2005')]

[DBConnect ( 'Data Source = (local); Initial Catalog = Test; Integrated Security = True')]

Create table News

(

Title nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Content nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Time SmallDateTime Untokenized NOT NULL Default '1990-01-01 ',

Url nvarchar (max)

);

exec SP_ExcuteSql 'Create index I_Time on news (time)';

CREATE TABLE statement for more than the establishment of a News full-text index tables, full-text index based on d: \ test \ news directory, this table and local SQLSERVER2005 or 2008 database, the default instance of the Test Library Association News table.
This table has four fields, including Title, Content for the full-text indexing field, Time for non-full-text indexing field, Url to only store the field.
Table building is completed, we adopted SP_ExcuteSql direct manipulation SQLSERVER2005 database Test database to create the database for the Time field, the establishment of a non-clustered index,
This index in just the scope of inquiry on the Time to be useful.

3. Specify the primary key
Single-field primary key

[Directory ( 'd: \ test \ news \')]

[DBAdapter ( 'SQLSERVER2005')]

[DBConnect ( 'Data Source = (local); Initial Catalog = Test; Integrated Security = True')]

Create table News

(

NewsId int NOT NULL PRIMARY KEY,

Title nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Content nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Time SmallDateTime Untokenized NOT NULL Default '1990-01-01 ',

Url nvarchar (max)

);

This table, we specify the primary key field NewsId field
Multi-field primary key

[Directory ( 'd: \ test \ news \')]

[DBAdapter ( 'SQLSERVER2005')]

[DBConnect ( 'Data Source = (local); Initial Catalog = Test; Integrated Security = True')]

Create table News

(

NewsId int NOT NULL PRIMARY KEY,

NewsType nvarchar (16) UnTokenized NOT NULL PRIMARY KEY,

Title nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Content nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Time SmallDateTime Untokenized NOT NULL Default '1990-01-01 ',

Url nvarchar (max)

);

This table, we specify NewsId and NewsType field primary key field

}
Delete table
EXEC SP_DROPTABLE
The stored procedure has a unique parameter is the table name. If you want to delete the table does not exist, equally successful return.

Note: execute this stored procedure will also delete the corresponding relational database tables, they must be careful.

Example
Exec sp_droptable 'News'
The statement, delete the current database in the News table.
Insert
INSERT INTO
{
table_name)
}

{ [(column_list)]
{ VALUES ( { DEFAULT
| NULL
| expression
},...n
)
}
}
DEFAULT VALUES
Arguments
INTO
Is an optional keyword that can be used between INSERT and the target table.
table_name
Is the name of a table that is to receive the data.
(column_list)
Is a list of one or more columns in which to insert data. column_list must be enclosed in parentheses and delimited by commas.
If a column is not in column_list, Microsoft® SQL Server™ must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. SQL Server automatically provides a value for the column if the column:
• Has an IDENTITY property. The next incremental identity value is used.
• Has a default. The default value for the column is used.
• Has a timestamp data type. The current timestamp value is used.
• Is nullable. A null value is used.
columnlist and VALUES list must be used when inserting explicit values into an identity column, and the SET IDENTITYINSERT option must be ON for the table.
VALUES
Introduces the list of data values to be inserted. There must be one data value for each column in column_list (if specified) or in the table. The values list must be enclosed in parentheses.
If the values in the VALUES list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value.
DEFAULT
Forces SQL Server to load the default value defined for a column. If a default does not exist for the column and the column allows NULLs, NULL is inserted. For a column defined with the timestamp data type, the next timestamp value is inserted. DEFAULT is not valid for an identity column.
expression
Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.

DEFAULT VALUES
Forces the new row to contain the default values defined for each column.

Example:
INSERT T1 VALUES (1, 'Row #1') ;
INSERT INTO T1 (column1,column2) VALUES (-99,'Explicit identity value') ;
Batch insert
Insert large amounts of data, if every single record inserts, insert the pace will be very slow, in which case we need to bulk insert. Bulk insert, only time will need multiple insert statements can be sent to the hubble.net, each insert statement is a semicolon ";" at the end.
When you want to bulk insert a lot of data, we propose to 1000-5000 records as a group, perform bulk insert operations in batches, so that the speed of insertion will be greatly enhanced.
Example

INSERT T1 VALUES (1, 'Row # 1');
INSERT T1 VALUES (2, 'Row # 2');
INSERT T1 VALUES (3, 'Row # 3');
INSERT T1 VALUES (4, 'Row # 4');
INSERT T1 VALUES (5, 'Row # 5');

SQL statement that once the above bulk insert 5 records.
Delete
Delete statement syntax is same as T-SQL. Where sub statement is same as where in select.
Syntax

DELETE
FROM
(
tablename WITH (<tablehint_limited> ... n)
View_name
Rowsetfunctionlimited
)

FROM (<table_source>) ,... n n = 1
WHERE
(<search_condition>
)
]

Example
Delete T where c1> 100 and c1 <200 and c2 match 'China ^ 256.3 BeiJing ^ 100 ShangHai'
Lookup table T in the C1 is greater than 100 and less than 200 and the C2 match China, BeiJing, and ShangHai three words record. These records will be deleted.
Update
Update statement syntax is same as T-SQL. Where sub statement is same as where in select.
Syntax

UPDATE
(
tablename WITH (<tablehint_limited> ... n)
View_name
Rowsetfunctionlimited
)
SET
(column_name = (expression | DEFAULT | NULL)
@ Variable = expression
@ Variable = column = expression) ,... n

((FROM (<table_source>) ,... n n = 1

WHERE
<search_condition>)
)

Example
Update T Set c1 = 12 where c1> 100 and c1 <200 and c2 match 'China ^ 256.3 BeiJing ^ 100 ShangHai'

Update the records in table T that C1 is greater than 100 and less than 200 and the C2 match China, BeiJing, and ShangHai three words record. And these records c1 Change the value of field 12.
Select
Syntax
SELECT ALL
<select_list>
FROM (<table_source>) ,... n n = 1
WHERE <search_condition>
Cannot resolve the wiki link macro, invalid number of inputs.)
,... n] n = 1
Arguments
Top n

Lists the records before the n

Between n1 To n2

List from n1 to n2 between the records (including n2)


ALL
Listed in the table being queried all of the columns, usually * means ALL

select_list
Listed in the specified column

FROM
The source of the specified query.
table_source
Table name, version 0.1 is only available for a single-table query, there can only be to write a table name.

* specify the field rank

T-SFQL statement that only allows for full-text index field to specify the rank of other field specifies the right value is invalid.
Syntax:
FieldName ^ Boost
^ As a delimiter, indicating the weight began to set
Boost for the rank, unsigned integer, range 0-65535

Example:
Select * from T where c1 ^ 5 match 'BeiJing'

* specify the rank of records.

Hubble.net allow you specify the rank of records. We should build the table and create an Int type of a field called Rank. You can specify the rank of records by field.
Example:
Create table News

(

NewsId int NOT NULL PRIMARY KEY,

Title nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Content nvarchar (max) Tokenized Analyzer 'SimpleAnalyzer' NOT NULL Default'',

Time SmallDateTime Untokenized NOT NULL Default '1990-01-01 ',

Url nvarchar (max),
Rank Int NOT NULL,

);

* Position-independent fuzzy match

Syntax:
FieldName Match 'Query words ^ Boost'

Query words: To check the word for multiple words, separated by spaces between words.

^: Separator, said the rank started to set.
Boost: rank, unsigned integer, range is 0-65535. Option, you do not specify the rank, the default rank is 1.

Example:
Select * from T where C2 Match 'China ^ 256 BeiJing ^ 100 ShangHai'

* Location Dependent Fuzzy Matching

Syntax:
FieldName Like 'Query words ^ Boost ^ Position'

Query words: To check the word for multiple words, separated by spaces between words.

^: Separator, said that setting the weight or the correct location.
Boost: rank, unsigned integer, range is 0-65535. Option, you do not specify the rank, the default rank is 1.
Position: starting position of each words, unsigned integer, optional, must since the rank. If it is not specified, the default is the word appears in the query string in the position.
Example:
Select * from T where C2 Like 'China ^ 256 ^ 0 BeiJing ^ 100 ^ 6 ShangHai ^ 1 ^ 13'

Select * from T where C2 Like 'China, the Chinese people ^ 256 ^ 0 ^ 100 ^ 0'

Select * from T where C2 Like 'BeiJing China'

* Note:

1. If the meta-data field is the logical query needs and the full-text search at the same time the the meta-data field must be set to Untokenized.
2. If "and" "or" apeared in Where statement at same time, it must use bracket to split them.
For example:
Hubble.net can't allow following sql:
Where c1> 100 and c1 <200 or c2 match 'China ^ 256 BeiJing ^ 100 ShangHai'
We should write the sql as:
Where (c1> 100 and c1 <200) or c2 match 'China ^ 256 BeiJing ^ 100 ShangHai'

Example
Select * from T where c1> 100 and c1 <200 and c2 match 'China ^ 256 BeiJing ^ 100 ShangHai'

Last edited Jan 30, 2011 at 10:06 PM by eaglet2006, version 1

Comments

No comments yet.