We have installed ODK Aggregate on one of our servers with an MySQL
database. We intend to pull the data directly out of the form tables
and use it for our own purposes.
What I am currently attempting to do is get a list of available Forms
with their associated database tables. The only place I am finding the
Form Table names is at `_form_data_model.PERSIST_AS_TABLE_NAME' -
but this has an entry for each field. I could do "SELECT DISTINCT
PERSIST_AS_TABLE_NAME FROM _form_data_model", but that doesn't really
help me get what I want, and it seems a bit clunky.
I also do not see how to connect _form_data_model back to _form_info_fileset.FORM_NAME
Ideally I would like to run a query that gives me two columns: Form
Name & Form Table Name. Each Form Name would appear once for each
associated Form Table Name.
Any tips or information would be greatly appreciated.
Keep in mind that multiple-choice select values and media attachments are
stored in separate tables. Also, if your form has many questions
(somewhere over 250 questions for MySQL), it will be split across multiple
tables.
The form id is in _form_info.
The form name is in _form_info_fileset. From _form_info_fileset, the
_top_level_auri is the PK of the corresponding _form_info table.
Here is an example starting from _form_info.form_id :
select distinct persist_as_table_name, persist_as_schema_name
from _form_info fi, _form_data_model fdm, _form_info_submission_association
sdm
where fi.form_id = 'geo_tagger_v2'
and sdm.uri_md5_form_id = fi._uri
and fdm.uri_submission_data_model = sdm.uri_submission_data_model
The top-level table is ..._CORE and the other tables hang off beneath it.
The _top_level_auri of those holds the PK of the top-level table. If the
table needed to be split, you'll see _CORE1, ... _COREn.
Mitch
···
On Fri, Feb 17, 2012 at 10:07 AM, Feltpad Incorporated <feltpadinc@gmail.com wrote:
We have installed ODK Aggregate on one of our servers with an MySQL
database. We intend to pull the data directly out of the form tables
and use it for our own purposes.
What I am currently attempting to do is get a list of available Forms
with their associated database tables. The only place I am finding the
Form Table names is at `_form_data_model.PERSIST_AS_TABLE_NAME' -
but this has an entry for each field. I could do "SELECT DISTINCT
PERSIST_AS_TABLE_NAME FROM _form_data_model", but that doesn't really
help me get what I want, and it seems a bit clunky.
I also do not see how to connect _form_data_model back to _form_info_fileset.FORM_NAME
Ideally I would like to run a query that gives me two columns: Form
Name & Form Table Name. Each Form Name would appear once for each
associated Form Table Name.
Any tips or information would be greatly appreciated.
Please use the opendatakit-developers@ group for technical questions such
as this.
Aggregate reconstructs the structure of the form from the contents of the
_form_data_model table.
The top-level group of a form is identified by parent_uri_form_data_model =
uri_submission_data_model.
From there, elements within a group have parent_uri_form_data_model = _uri
of the containing group and the ordinal_number (1..n) of the element
indicates the sequence of that element within the group.
The FormDefinition class handles the reconstruction of the form structure
(i.e., FormDefinition.getFormDefinition(...)). These are cached for
performance.
Mitch
···
On Sat, Feb 18, 2012 at 8:18 AM, Feltpad Incorporated wrote:
Thank you for the response.
How does Aggregate know which table is the ... _CORE table? Does it do
a " ... WHERE persist_as_table_name LIKE '%_CORE' " ?