Listing tables

Manticore Search has a single level of hierarchy of tables.

There is no concept of grouping tables in databases like in other DBMS. Still, Manticore accepts SHOW DATABASES statements for interoperability with SQL dialect, but the statement doesn’t return anything.

SHOW TABLES

General syntax:

  1. SHOW TABLES [ LIKE pattern ]

SHOW TABLES statement enumerates all currently active tables along with their types. Existing table types are local, distributed, rt, percolate and template.

  • SQL
  • PHP
  • Python
  • javascript
  • Java

SQL PHP Python javascript Java

  1. SHOW TABLES;
  1. $client->nodes()->table();
  1. utilsApi.sql('SHOW TABLES')
  1. res = await utilsApi.sql('SHOW TABLES');
  1. utilsApi.sql("SHOW TABLES")

Response

  1. +----------+-------------+
  2. | Index    | Type        |
  3. +----------+-------------+
  4. | dist     | distributed |
  5. | plain    | local       |
  6. | pq       | percolate   |
  7. | rt       | rt          |
  8. | template | template    |
  9. +----------+-------------+
  10. 5 rows in set (0.00 sec)
  1. Array
  2. (
  3.     [dist1] => distributed
  4.     [rt] => rt
  5.     [products] => rt
  6. )
  1. {u'columns': [{u'Index': {u'type': u'string'}},
  2.               {u'Type': {u'type': u'string'}}],
  3.  u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
  4.            {u'Index': u'rt', u'Type': u'rt'},
  5.            {u'Index': u'products', u'Type': u'rt'}],
  6.  u'error': u'',
  7.  u'total': 0,
  8.  u'warning': u''}
  1. {"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}
  1. {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
  • PHP
  • Python
  • javascript
  • Java

SQL PHP Python javascript Java

  1. SHOW TABLES LIKE 'pro%';
  1. $client->nodes()->table(['body'=>['pattern'=>'pro%']]);
  1. res = await utilsApi.sql('SHOW TABLES LIKE \'pro%\'');
  1. utilsApi.sql('SHOW TABLES LIKE \'pro%\'')
  1. utilsApi.sql("SHOW TABLES LIKE 'pro%'")

Response

  1. +----------+-------------+
  2. | Index    | Type        |
  3. +----------+-------------+
  4. | products | distributed |
  5. +----------+-------------+
  6. 1 row in set (0.00 sec)
  1. Array
  2. (
  3.     [products] => distributed
  4. )
  1. {u'columns': [{u'Index': {u'type': u'string'}},
  2.               {u'Type': {u'type': u'string'}}],
  3.  u'data': [{u'Index': u'products', u'Type': u'rt'}],
  4.  u'error': u'',
  5.  u'total': 0,
  6.  u'warning': u''}
  1. {"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}
  1. {columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}

DESCRIBE

  1. {DESC | DESCRIBE} table [ LIKE pattern ]

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

  1. mysql> DESC rt;
  2. +---------+---------+
  3. | Field   | Type    |
  4. +---------+---------+
  5. | id      | bigint  |
  6. | title   | field   |
  7. | content | field   |
  8. | gid     | integer |
  9. +---------+---------+
  10. 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 see table schema by executing the query select * from <table_name>.table. The benefit of this method is that you can use WHERE for filtering:

  • SQL

SQL

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

Response

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

You can also do many other things, consider <your_table_name>.table just a regular Manticore table where the columns are integer and string attributes.

  • SQL

SQL

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

SHOW CREATE TABLE

  1. SHOW CREATE TABLE name

Prints the CREATE TABLE statement that creates the named table.

  • SQL

SQL

  1. SHOW CREATE TABLE tbl\G

Response

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

Percolate table schemas

If you apply DESC statement to a percolate table it will show the outer table schema, i.e. the schema of stored queries. It’s static and the same for all local percolate tables:

  1. mysql> DESC pq;
  2. +---------+--------+
  3. | Field   | Type   |
  4. +---------+--------+
  5. | id      | bigint |
  6. | query   | string |
  7. | tags    | string |
  8. | filters | string |
  9. +---------+--------+
  10. 4 rows in set (0.00 sec)

If you’re looking for an expected document schema use DESC <pq table name> table:

  1. mysql> DESC pq TABLE;
  2. +-------+--------+
  3. | Field | Type   |
  4. +-------+--------+
  5. | id    | bigint |
  6. | title | text   |
  7. | gid   | uint   |
  8. +-------+--------+
  9. 3 rows in set (0.00 sec)

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

  1. mysql> desc pq table like '%title%';
  2. +-------+------+----------------+
  3. | Field | Type | Properties     |
  4. +-------+------+----------------+
  5. | title | text | indexed stored |
  6. +-------+------+----------------+
  7. 1 row in set (0.00 sec)

Deleting a table

Deleting a table is performed in 2 steps internally:

  1. Table is cleared (similar to TRUNCATE)
  2. All table files are removed from the table folder. All the external table files that were used by the table (such as wordforms, extensions or stopwords) are also deleted. Note that these external files are copied to the table folder when CREATE TABLE is used, so the original files specified in CREATE TABLE will not be deleted.

Deleting a table is possible only when the server is running in the RT mode. It is possible to delete RT tables, PQ tables and distributed tables.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. DROP TABLE products;
  1. POST /cli -d "DROP TABLE products"
  1. $params = [ 'index' => 'products' ];
  3. $response = $client->indices()->drop($params);
  1. utilsApi.sql('DROP TABLE products')
  1. res = await utilsApi.sql('DROP TABLE products');
  1. sqlresult = utilsApi.sql("DROP TABLE products");

Response

  1. Query OK, 0 rows affected (0.02 sec)
  1. {
  2. "total":0,
  3. "error":"",
  4. "warning":""
  5. }
  1. Array
  2. (
  3.     [total] => 0
  4.     [error] =>
  5.     [warning] =>
  6. )
  1. {u'error': u'', u'total': 0, u'warning': u''}
  1. {"total":0,"error":"","warning":""}
  1. {total=0, error=, warning=}

Here is the syntax of the DROP TABLE statement in SQL:

  1. DROP TABLE [IF EXISTS] index_name

When deleting a table via SQL, adding IF EXISTS can be used to delete the table only if it exists. If you try to delete a non-existing table with the IF EXISTS option, nothing happens.

When deleting a table via PHP, you can add an optional silent parameter which works the same as IF EXISTS.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. DROP TABLE IF EXISTS products;
  1. POST /cli -d "DROP TABLE IF EXISTS products"
  1. $params =
  2. [
  3.   'index' => 'products',
  4.   'body' => ['silent' => true]
  5. ];
  7. $client->indices()->drop($params);
  1. utilsApi.sql('DROP TABLE IF EXISTS products')
  1. res = await utilsApi.sql('DROP TABLE IF EXISTS products');
  1. sqlresult = utilsApi.sql("DROP TABLE IF EXISTS products");

Response

  1. {u'error': u'', u'total': 0, u'warning': u''}
  1. {"total":0,"error":"","warning":""}
  1. {total=0, error=, warning=}

Emptying a table

The table can be emptied with a TRUNCATE TABLE SQL statement or with a truncate() PHP client function.

Here is the syntax for the SQL statement:

  1. TRUNCATE TABLE index_name [WITH RECONFIGURE]

When this statement is executed, it clears the RT table completely. It disposes the in-memory data, unlinks all the table data files, and releases the associated binary logs.

A table can also be emptied with DELETE FROM index WHERE id>0, but it’s not recommended as it’s much slower than TRUNCATE.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. TRUNCATE TABLE products;
  1. POST /cli -d "TRUNCATE TABLE products"
  1. $params = [ 'index' => 'products' ];
  2. $response = $client->indices()->truncate($params);
  1. utilsApi.sql('TRUNCATE TABLE products')
  1. res = await utilsApi.sql('TRUNCATE TABLE products');
  1. utilsApi.sql("TRUNCATE TABLE products");

Response

  1. Query OK, 0 rows affected (0.02 sec)
  1. {
  2. "total":0,
  3. "error":"",
  4. "warning":""
  5. }
  1. Array(
  2.     [total] => 0
  3.     [error] => 
  4.     [warning] => 
  5. )
  1. {u'error': u'', u'total': 0, u'warning': u''}
  1. {"total":0,"error":"","warning":""}
  1. {total=0, error=, warning=}

One of the possible uses of this command is before attaching a table.

When RECONFIGURE option is used new tokenization, morphology, and other text processing settings specified in the config take effect after the table gets cleared. In case the schema declaration in config is different from the table schema the new schema from config got applied after table get cleared.

With this option clearing and reconfiguring a table becomes one atomic operation.

  • SQL
  • HTTP
  • PHP
  • Python
  • javascript
  • Java

SQL HTTP PHP Python javascript Java

  1. TRUNCATE TABLE products with reconfigure;
  1. POST /cli -d "TRUNCATE TABLE products with reconfigure"
  1. $params = [ 'index' => 'products', 'with' => 'reconfigure' ];
  2. $response = $client->indices()->truncate($params);
  1. utilsApi.sql('TRUNCATE TABLE products WITH RECONFIGURE')
  1. res = await utilsApi.sql('TRUNCATE TABLE products WITH RECONFIGURE');
  1. utilsApi.sql("TRUNCATE TABLE products WITH RECONFIGURE");

Response

  1. Query OK, 0 rows affected (0.02 sec)
  1. {
  2. "total":0,
  3. "error":"",
  4. "warning":""
  5. }
  1. Array(
  2.     [total] => 0
  3.     [error] => 
  4.     [warning] => 
  5. )
  1. {u'error': u'', u'total': 0, u'warning': u''}
  1. {"total":0,"error":"","warning":""}
  1. {total=0, error=, warning=}