# Declaring a schema Instead of writing `CREATE` statements and keeping track of which have run, you can say what the schema should be and let the driver work out the difference. Three methods do it, one for each kind of object: | Method | Declares | With | |---|---|---| | `ensure_labels()` | vertex and edge labels, their parents, and properties with columns of their own | `DesiredLabel`, `PromotedProperty` | | `ensure_indexes()` | property indexes, including vector indexes | `DesiredIndex`, `IndexElement` | | `ensure_constraints()` | uniqueness and check constraints | `Unique`, `Check` | Each reads the catalogs, compares what is there with what you asked for, runs only the statements that close the gap, and returns them. Running the same declaration again returns an empty list. ```pycon >>> from agensgraph import DesiredIndex >>> conn.ensure_indexes([DesiredIndex("movie", ["title"])]) ['create property index on movie (title)'] >>> conn.ensure_indexes([DesiredIndex("movie", ["title"])]) [] ``` All three are on `AsyncConnection` as well, awaited. ## Before you start **Select the graph first.** The statements these methods run name labels without a graph, so they apply to the graph the session has selected. `graph=` changes which graph's catalogs are *read*, not where the statements go; use it with `dry_run=True` to see what another graph would need, and select that graph with `conn.graph()` before applying it. **Nothing is committed for you.** The statements run on the connection like any other, so on a connection that is not in autocommit they belong to the open transaction until you call `commit()`. That lets a schema change and the data that depends on it commit together. **Name labels as the server stores them.** Every name you give is quoted, so it is taken literally. `CREATE VLABEL Movie` stores `movie`, because an unquoted name is folded to lower case, and `DesiredIndex("Movie", ...)` then asks for a label that does not exist. [`conn.labels()`](inspecting.md#labels) shows what each label is really called. ## Seeing the statements first Every method takes `dry_run=True`, which returns the statements without running any: ```pycon >>> from agensgraph import DesiredLabel >>> conn.ensure_labels( ... [DesiredLabel("studio"), DesiredLabel("produced", kind="e")], dry_run=True ... ) ['create vlabel if not exists studio', 'create elabel if not exists produced'] ``` Mistakes in the declaration itself, such as an index with no properties, are reported in a dry run as well, before anything would run. ## Labels ```python from agensgraph import DesiredLabel conn.ensure_labels([ DesiredLabel("studio"), # a vertex label DesiredLabel("produced", kind="e"), # an edge label DesiredLabel("animated", parent="movie"), # a vertex label that inherits from movie ]) ``` `DesiredLabel(name, kind="v", parent=None, properties=())`. List a parent before its children, since the statements run in the order you give them. `ensure_labels()` creates the labels that are missing and **never drops one**, whatever you leave out of the list. A label holds the elements written to it, so removing it is a decision about data rather than schema, and a declaration that could make it would empty a graph when handed a shorter list. It also leaves an existing label's parent alone. A name that exists as the other kind, an edge label asked for as a vertex label, is refused with `ValueError` naming both kinds. After it has created anything it refreshes the connection's label table, so the new labels can be read at once in either rendering. ### Declare the labels you write to A write to a label that does not exist creates it, and that is DDL inside the writer's transaction. Two writers arriving at once then race to create the same label, and one of them fails with a unique violation or `42P07`. With the label declared first, concurrent writers do not race over it at all. Declare every label a service writes to when it starts. ### Properties with a column of their own From AgensGraph 2.18 a label can keep a property in a column of its own, a *promoted* property. Reading that one property then never touches the rest of the property map, and the column's type is enforced when a value is written. Declare one with `PromotedProperty(name, type)`: ```pycon >>> from agensgraph import PromotedProperty >>> conn.ensure_labels([ ... DesiredLabel("review", properties=[PromotedProperty("score", "numeric")]), ... ]) ['create vlabel if not exists review (score numeric generated)'] >>> conn.declared_properties("review") [DeclaredProperty(label='review', name='score', type='numeric', nullable=True, inherited=False)] ``` On a label that already exists, a missing column is added with `ALTER VLABEL ... ADD COLUMN`. A column the label already has, declared on it or inherited from its parent, is left as it is, whatever its type, so asking for an inherited column on a child changes nothing. Adding a column to a label that already holds elements rewrites its table, which reads every element once. On a server that cannot keep a property in a column, a declaration with `properties` raises `agensgraph.errors.CapabilityError`, naming the version that can, even in a dry run. [Bulk loading](../data/bulk-loading.md#give-the-key-a-column-of-its-own) explains why an ingest key should be promoted, and [Embedding vectors](../data/vectors.md) why an embedding should be. ## Indexes ```python from agensgraph import DesiredIndex, IndexElement conn.ensure_indexes([ DesiredIndex("person", ["name"]), DesiredIndex("movie", ["genre", "year"]), DesiredIndex("movie", [IndexElement("year", descending=True)], name="movie_newest"), DesiredIndex("movie", ["year"], name="movie_recent", where="year > 2000"), ]) ``` `DesiredIndex(label, properties, unique=False, name=None, method="btree", where=None)`. `properties` mixes plain names and `IndexElement`s. An `IndexElement` carries what a plain name cannot: `IndexElement(property, operator_class=None, descending=False, nulls_first=None, cast=None)`. An index is created as a *property index*, the form a Cypher query matches. Without `name`, the server names it, `movie_genre_year_idx` for the second one above. ### How an index is matched A plain index is matched on what it does, not on its name: the label, the method, and the properties in order, with their operator classes, sort order and casts. An index you created by hand that does the same job is recognised and left alone, whatever it is called. One that differs only in being unique is dropped and created again. A **partial** index, one with `where`, needs a `name`, because the server stores the predicate in a normalised form that does not look like what you wrote and cannot be compared with it. It is matched by that name, and its predicate is never compared, so changing the predicate under the same name is not noticed. Give the changed index a new name to have it rebuilt. ### Spell it the way the server prints it An index the server reports differently from what you declared looks like a different index. Two habits avoid that: - **Name an operator class only when it is not the default** for that type and method. The server leaves a default out when it prints a definition, so naming `jsonb_ops` for a `gin` index, or `vector_l2_ops` for an `ivfflat` one, can never be matched. - **Write a cast the way the server prints the type**: `integer`, not `int`; `double precision`, not `float8`. The method checks its own work. After running anything it reads the catalogs again, and if what it asked for still does not match, it raises `RuntimeError` listing what is outstanding, rather than doing the same work again on the next run. The statements have run by then, so the index exists; correct the declaration to match it. ### Vector indexes A vector index is declared like any other. For an embedding kept in the property map, the index is over a cast that carries the dimension; for one with a column of its own, the column is indexed directly and a cast is refused, because a search on the column would never use it: ```{code-block} python :class: no-run conn.ensure_indexes([ DesiredIndex( "movie", [IndexElement("embedding", "vector_cosine_ops", cast="vector(4)")], method="hnsw", ), DesiredIndex("doc", [IndexElement("embedding", "vector_cosine_ops")], method="hnsw"), ]) ``` [Embedding vectors](../data/vectors.md#indexing) covers which operator class serves which search. ### Uniqueness on an edge's endpoints An index cannot make an edge unique per pair of vertices. `start` and `end` are columns of the edge's table, but a property index keys on *properties*, so an index naming them would be over two properties no edge has, and would accept every duplicate. `DesiredIndex` refuses `unique=True` over `start` or `end` for that reason. Write that index in SQL, over the columns: ```{code-block} python :class: no-run conn.execute('CREATE UNIQUE INDEX produced_pair ON moviekb.produced (start, "end")') ``` It is not a property index, so neither `indexes()` nor `ensure_indexes()` sees it. ## Constraints ```pycon >>> from agensgraph import Check, Unique >>> conn.ensure_constraints([ ... Unique("movie", "title"), ... Check("movie", "year > 1880", "movie_year_plausible"), ... ]) ['create constraint movie_title_unique on movie assert title is unique', 'create constraint movie_year_plausible on movie assert year > 1880'] ``` `Unique(label, property, name=None)` asserts that no two elements of the label have the same value of one property. Without a name it is named `