Create route for autocomplete

[jczaplew]

Categorized

[jczaplew]

Should include:

columns col_groups strat_names

???: intervals liths

[cambro]

Autocomplete is most helpful for all of the definitions. Columns and column_group names are rarely useful.

On Fri, Jul 10, 2015 at 11:42 AM, John J Czaplewski < notifications@github.com> wrote:

Should include:

columns col_groups strat_names

???: intervals liths

— Reply to this email directly or view it on GitHub https://github.com/UW-Macrostrat/macrostrat-api/issues/85#issuecomment-120439491 .

[jczaplew]

DROP TABLE IF EXISTS autocomplete;

CREATE TABLE autocomplete AS
  SELECT * FROM (
    select id, econ as name, 'econs' as type from econs
    union
    select id, environ as name, 'environments' as type from environs
    union
    select id, concat(lith_att, ' (', att_type, ')') as name, 'lithology_attributes' as type from lith_atts
    union
    select id, project as name, 'projects' as type from projects
    union
    select concept_id AS id, name, 'strat_name_concepts' AS type FROM strat_names_meta
    union
    (select id, CONCAT(strat_name, ' ', rank) AS name, 'strat_name_orphans' as type from strat_names WHERE concept_id = 0)
    union
    select id, col_name as name, 'columns' as type from cols
    union
    select id, col_group as name, 'groups' as type from col_groups
    union
    select id, lith as name, 'lithologies' as type from liths
    union
    select id, interval_name as name, 'intervals' as type from intervals
  ) i;

ALTER TABLE autocomplete ADD INDEX auto_id (id);
ALTER TABLE autocomplete ADD INDEX auto_name (name);
ALTER TABLE autocomplete ADD INDEX auto_type (type);