Introduction

The auto shema mechanism automatically creates tables when a specified table in the INSERT statement does not yet exist. The mechanism is implemented in the manticore-extra package and is enabled by default.

To disable it, add auto_schema = 0 to the Searchd section of your Manticore configuration file. To re-enable it, set auto_schema = 1 or simply remove the auto_schema from the configuration.

You can execute INSERT statements to a missing table in the usual way using SQL or HTTP protocol.

Examples of usage

Let's try an SQL insert first:

mysql -P9306 -h0

Run the INSERT query to a missing table:

drop table if exists t; insert into t(i,f,t,s,j,b,m,mb) values(123,1.2,'text here','test@mail.com','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777));

As we see, the query has been executed without errors. Let's make sure that the table has been created and populated with data correctly:

desc t; select * from t;

Now we'll do the same via an HTTP request:

exit;

curl -sX POST http://localhost:9308/insert  -d ' 
{
 "index":"t",
 "id": 2,
 "doc":
 {
   "i" : 123,
   "f" : 1.23,
   "t": "text here",
   "s": "test@mail.com",
   "j": {"a": 123},
   "b": 1099511627776,
   "m": [1,2],
   "mb": [1099511627776,1099511627777]
 }
}'

Check the result again:

curl -sX POST http://localhost:9308/cli -d 'desc t;'

curl -sX POST http://localhost:9308/cli -d 'select * from t;'

Again, the table has been successfully created.

About data type detection

The auto schema mechanism automatically detects the types of insert data values to define the corresponding fields for the table it creates.

Note that, by default, all text values in the VALUES clause are considered to be of the text type:

mysql -P9306 -h0

drop table if exists t; insert into t(f) values('test');desc t;

The exception is values that represent valid email addresses, which are treated as the string type:

drop table if exists t; insert into t(f) values('test@mail.com');desc t;

However, if you do a batch insert with multiple documents you should make sure that each data field contains values of the same type(or at least of compatible types) across all the documents. Otherwise, Manticore will not be able to create a table and will return an error message, as in the example below:

drop table if exists t; insert into t(f) values(123),('test');

Here the values 123 and test from the field f have incompatible data types int and text, respectively.

Examples of auto data conversion

If value types for a field are different but compatible, the resulting field type will be the one that accommodates all the values. Automatic data type transformations applied is such cases include the following conversions:

• mva -> mva64
• uint -> bigint -> float
• string -> text

Let's look at some examples of automatic data conversion:

drop table if exists t; insert into t(f,b) values(123,1099511627776),(1.2,10);desc t;

As you see, the b column has been defined as bigint, and the f column as float.

drop table if exists t; insert into t(mb) values((1,2)),((1099511627776,1099511627777));desc t;

Here we have the data for column mb converted to mva64.

drop table if exists t; insert into t(t) values('test@mail.com'),('test'); desc t;

And here the data for the column t has been converted to text.