Setting indexes in the sqlmodel tables automatically

[nsheff]

In databio/bbconf#19 I raised a point about how we were selecting based on a non-identifier column, and how this therefore needed an index.

I had to manually add the index in the postgres server, which isn't ideal... it would be better if any necessary indexes could be specified in the json schema.

I'd recommend adding an index: true parameter option, and then when we read the schema, automatically setting the indexes on those columns.

[donaldcampbelljr]

Currently, id is the primary key:

 Field(default=None, primary_key=True),

For indexing other definitions, I believe the syntax is:

 Field(default=None, index=True)

So parsed_schema should be able to add this if it is detected in the output schema. Should happen here:

pipestat/pipestat/backends/db_backend/db_parsed_schema.py

Lines 135 to 164 in 6301b86

	def _make_field_definitions(self, data: Dict[str, Any], require_type: bool):
	# TODO: default to string if no type key?
	# TODO: parse "required" ?
	defs = {}
	for name, subdata in data.items():
	try:
	typename = subdata[SCHEMA_TYPE_KEY]
	except KeyError:
	if require_type:
	_LOGGER.error(f"'{SCHEMA_TYPE_KEY}' is required for each schema element")
	raise
	else:
	data_type = str
	else:
	data_type = self._get_data_type(typename)
	if data_type == CLASSES_BY_TYPE["object"] or data_type == CLASSES_BY_TYPE["array"]:
	defs[name] = (
	data_type,
	Field(sa_column=Column(JSONB), default=null()),
	)
	else:
	defs[name] = (
	# Optional[subdata[SCHEMA_TYPE_KEY]],
	# subdata[SCHEMA_TYPE_KEY],
	# Optional[str],
	# CLASSES_BY_TYPE[subdata[SCHEMA_TYPE_KEY]],
	data_type,
	Field(default=subdata.get("default")),
	)
	return defs

[donaldcampbelljr]

This functionality was added in v0.8.0. Closing this issue.