ΒΆ 7

Listing tables

Manticore Search has a single level of hierarchy for tables.

Unlike other DBMS, there is no concept of grouping tables into databases in Manticore. However, for interoperability with SQL dialects, Manticore accepts SHOW DATABASES statements for interoperability with SQL dialect, statements, but the statement does not return any results.

SHOW TABLES

General syntax:

SHOW TABLES [ LIKE pattern ]

The SHOW TABLESstatement lists all currently active tables along with their types. The existing table types are local, distributed, rt, percolate and template.

SQL 
SHOW TABLES;

+----------+-------------+
| Index    | Type        |
+----------+-------------+
| dist     | distributed |
| plain    | local       |
| pq       | percolate   |
| rt       | rt          |
| template | template    |
+----------+-------------+
5 rows in set (0.00 sec)
PHP 
$client->nodes()->table();

Array
(
    [dist1] => distributed
    [rt] => rt
    [products] => rt
)
Python 
utilsApi.sql('SHOW TABLES')

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
           {u'Index': u'rt', u'Type': u'rt'},
           {u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}
javascript 
res = await utilsApi.sql('SHOW TABLES');

{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}
Java 
utilsApi.sql("SHOW TABLES")

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}
C# 
utilsApi.Sql("SHOW TABLES")

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}

Optional LIKE clause is supported for filtering tables by name.

SQL 
SHOW TABLES LIKE 'pro%';

+----------+-------------+
| Index    | Type        |
+----------+-------------+
| products | distributed |
+----------+-------------+
1 row in set (0.00 sec)
PHP 
$client->nodes()->table(['body'=>['pattern'=>'pro%']]);

Array
(
    [products] => distributed
)
Python 
res = await utilsApi.sql('SHOW TABLES LIKE \'pro%\'');

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}
javascript 
utilsApi.sql('SHOW TABLES LIKE \'pro%\'')

{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}
Java 
utilsApi.sql("SHOW TABLES LIKE 'pro%'")

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}
C# 
utilsApi.Sql("SHOW TABLES LIKE 'pro%'")

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}

DESCRIBE

{DESC | DESCRIBE} table [ LIKE pattern ]

The DESCRIBE statement lists the table columns and their associated types. The columns are document ID, full-text fields, and attributes. The order matches the order in which fields and attributes are expected by INSERT and REPLACE statements. Column types include field, integer, timestamp, ordinal, bool, float, bigint, string, and mva. ID column will be typed as bigint. Example:

mysql> DESC rt;
+---------+---------+
| Field   | Type    |
+---------+---------+
| id      | bigint  |
| title   | field   |
| content | field   |
| gid     | integer |
+---------+---------+
4 rows in set (0.00 sec)

An optional LIKE clause is supported. Refer to
SHOW META for its syntax details.

SELECT FROM name.@table

You can also view the table schema by executing the query select * from <table_name>.@table. The benefit of this method is that you can use the WHERE clause for filtering:

SQL 
select * from tbl.@table where type='text';

+------+-------+------+----------------+
| id   | field | type | properties     |
+------+-------+------+----------------+
|    2 | title | text | indexed stored |
+------+-------+------+----------------+
1 row in set (0.00 sec)

You can also perform many other actions on <your_table_name>.@table considering it as a regular Manticore table with columns consisting of integer and string attributes.

SQL 
select field from tbl.@table;
select field, properties from tbl.@table where type in ('text', 'uint');
select * from tbl.@table where properties any ('stored');

SHOW CREATE TABLE

SHOW CREATE TABLE name

Prints the CREATE TABLE statement used to create the specified table.

SQL 
SHOW CREATE TABLE tbl\G

       Table: tbl
Create Table: CREATE TABLE tbl (
f text indexed stored
) charset_table='non_cjk,cjk' morphology='icu_chinese'
1 row in set (0.00 sec)

Percolate table schemas

If you use the DESC statement on a percolate table, it will display the outer table schema, which is the schema of stored queries. This schema is static and the same for all local percolate tables:

mysql> DESC pq;
+---------+--------+
| Field   | Type   |
+---------+--------+
| id      | bigint |
| query   | string |
| tags    | string |
| filters | string |
+---------+--------+
4 rows in set (0.00 sec)

If you want to view the expected document schema, use the following command:
DESC <pq table name> table:

mysql> DESC pq TABLE;
+-------+--------+
| Field | Type   |
+-------+--------+
| id    | bigint |
| title | text   |
| gid   | uint   |
+-------+--------+
3 rows in set (0.00 sec)

Also desc pq table like ... is supported and works as follows:

mysql> desc pq table like '%title%';
+-------+------+----------------+
| Field | Type | Properties     |
+-------+------+----------------+
| title | text | indexed stored |
+-------+------+----------------+
1 row in set (0.00 sec)