Schemafull CRUD

Now that we've learned how to define both tables and fields, It's time for some schemafull CRUD.

In this lesson, we'll only cover what is different from schemaless CRUD, namely how to update the schema and data for tables and fields using:

• •

  The FIELD clause on the REMOVE statement

• •

  The OVERWRITE clause on the DEFINE statement

• •

  The ALTER statement

Adding and removing fields

When updating our schemaless tables, we could just SET or UNSET fields and they would appear. Not so for a SCHEMAFULL table, however.

UPDATE product:01FSXKCPVR8G1TVYFT4JFJS5WB
SET new_field = "why not?";

UPDATE product UNSET category;

Run Query

In both of these cases we get a schema error. In the first case it's because the new_field field hasn't been defined yet, and in the second because category is a field that can't hold the value NONE, which is what UNSET is trying to do.

For our second UPDATE query which tries to UNSET the category field on our product table, we also get a schema error.

REMOVE FIELD category ON TABLE product;

SELECT category FROM product limit 1;

UPDATE product;

SELECT * FROM product limit 1;

Run Query

In order to remove a field from our schemafull table, we use the REMOVE FIELD statement.

REMOVE FIELD on a schemafull table removes both the schema definition and the field's data. That is why the SELECT straight after it shows NONE for category, and why the UPDATE after that runs without an error.

Sometimes a schemafull table holds fields that it has no definition for at all, such as fields left over from a time when the table was schemaless. To remove all of these at once, use ALTER TABLE with REMOVE UNDECLARED FIELDS. The drop_old_fields part below is a name for the migration, which makes sure that it only runs once.

ALTER TABLE product MIGRATE EXISTING ROWS AS drop_old_fields REMOVE UNDECLARED FIELDS;

Updating fields and tables

For updating existing schema definitions there are two options:

• •

  The OVERWRITE clause on the DEFINE statement

• •

  The ALTER statement.

For example, we can change the datatype of a field or change a table from schemafull to schemaless.

-- Change from a generic number type to float
DEFINE FIELD OVERWRITE price ON TABLE product TYPE float
    MIGRATE EXISTING ROWS AS price_to_float USING <float> $value FALLBACK 0.0;

-- Change from schemafull to schemaless
DEFINE TABLE OVERWRITE product TYPE NORMAL SCHEMALESS;

-- Change again from schemaless to schemafull
ALTER TABLE product SCHEMAFULL;

Run Query

The difference between these two options is that

• •

  DEFINE ... OVERWRITE can be used to both create new definitions and update existing ones, but needs to include the entire definition.

• •

  The ALTER statement only updates existing definitions and only needs to include the items to be updated, not the entire definition.

The price field has a MIGRATE EXISTING ROWS clause. A schemafull table that already holds records will refuse a change to a field if some of those records might not fit the new definition. Here the price field used to hold any number, including integers like 25, so the clause says what to do with them: cast each one to a float with <float> $value, and use 0.0 (the FALLBACK) for any value that fails to cast.

Summary

Let's summarise what we've learned.

When adding and removing fields:

• •

  You need to use DEFINE FIELD to add the field to the schema before using UPDATE to add the data.

• •

  REMOVE FIELD removes the field from the schema, and its data as well.

• •

  ALTER TABLE with REMOVE UNDECLARED FIELDS removes every field that the schema no longer defines.

When changing a field on a schemafull table that already holds records:

• •

  Add MIGRATE EXISTING ROWS to say what the existing values should become.

When updating fields and tables:

• •

  DEFINE ... OVERWRITE can be used to both create new definitions and update existing ones, but needs to include the entire definition.

• •

  The ALTER statement only updates existing definitions and only needs to include the items to be updated, not the entire definition.

That's it for this part on making our data schemafull, in the next part, we'll explore how to make our data secure.