Create Index in the output database

[gidden]

copying @katyhuff's text from cyclus/cyclus#603:

Suggestion from Yarden ( @yarden-livnat ) :

One of the last steps of database creation should be indexing (CREATE_INDEX ? )
Since it takes some time (how much?), it's likely that we will want a flag that turns off this feature for parametric simulation sets (because creating the index at the end of each simulation is fine, but is probably better to do just once at the end of the parametric series).

We should do this probably by adding a line to the create-table function for the sqlite backend that adds the name of that table to the list of tables to index.

Then, at the beginning of the close-table function in that sqlite backend, create an index based on that list.

See cyclus/cyclus#603 for further discussion

[opotowsky]

@rwcarlsen I'm having difficulty understanding this and I'm wondering (since you've implemented this in cyan) if you could explain it a little more. I don't even understand the purpose of it, so maybe we can start there :-)

[rwcarlsen]

cyan creates the following indexes:

Index("Resources", "SimId", "ResourceId", "QualId")
Index("Compositions", "SimId", "QualId", "NucId")
Index("Transactions", "SimId", "ResourceId")
Index("Transactions", "TransactionId")
Index("ResCreators", "SimId", "ResourceId")
Index("Agents", "SimId", "Prototype")
Index("Agents", "SimId", "AgentId", "Prototype")
Index("Inventories", "SimId", "AgentId", "StartTime", "EndTime", "Quantity")
Index("Inventories", "SimId", "ResourceId", "StartTime")
Index("Inventories", "SimId", "StartTime", "EndTime", "ResourceId",
"Quantity")

Where "Index([table-name], [field1], field2], ...)" is my shorthand for
running this sql command:

CREATE INDEX IF NOT EXISTS [table-name]_[field1]_[field2]_... ON

[table-name]([field1], [field2], ...);

Then you will want to run this sql command after everything else is done:

ANALYZE;

Note that you won't have the inventories table - this is still a major
issue that needs to be addressed - how will cymmetric support querying
nuclide-specific inventories of facilities over time. And I'm not sure if
you create the same agents table (from joining agententry and agentexit).

On Wed, May 6, 2015 at 4:22 PM, opotowsky [email protected] wrote:

@rwcarlsen https://github.com/rwcarlsen I'm having difficulty
understanding this and I'm wondering (since you've implemented this in
cyan) if you could explain it a little more. I don't even understand the
purpose of it, so maybe we can start there :-)

—
Reply to this email directly or view it on GitHub
#54 (comment).

[opotowsky]

@rwcarlsen I'm not sure how to handle this wrt to hdf5 vs sqlite and the inventories vs the compact inventories we now have access to via cyclus.

[rwcarlsen]

pytables can also create indices somewhat analogous to sql indices. But we should probably move this to 1.5 - a feature for another day.