Jan, 2012
T: text R: required PK,FK: Primary key, Foreign key UFK: used as a foreign key somewhere else A: auto sequence/identity N: number SN: number generated through an explicit sequence I: index D: date/time
user_id T(50),R,PK first_name T(50),R, middle_name T(50), last_name T(50),R password T(50) email T(255) active (Y/N) T(1:Y),R master_page_template_item_id N
folder_id A, folder_name T(50),R,I parent_folder_id N,PK public (Y/N) T(1:N) secure (Y/N) T(1:N) owner_user_id T(50),FK,R folder_short_description T(Long) folder_long_description T(long) folder_child_segment T(long) folder_last_updated_on D,R folder_last_updated_by D,R
report_id SN(report_sequence),PK, report_short_name M report_long_name M report_description M public (yes/no) B(:YES) url M update_url M field_values M where_clause M report_type_id N,FK report_content_id N,FK(sql_statements.statement_id) owner_user_id T(50),FK created_by T(50),FK created_on D last_updated_by T(50),FK last_updated_on D
A content item is stored here. report_id is the id that is used to display an i tem in akc. The body of the item is stored in the sql_statements. The id of that table is kept in report_content_id. The value for report_id is obtained from a sequence stored in aspire_sequences table.The name of this sequence is "report_sequence". An item's body is first stored in sql_statements and then the sql_statements.statement_id is used to insert the row in the reports table.
id A, statement_id SN(sql_report_sequence),UFK, PK statement M database_name T(50)
the statement_id is a manual sequence. name of the sequence is sql_report_sequence and is kept in aspire_sequences table. The "id" field is currently not used, but could have been. However the "id" field is auto geneated.
filed_item_id A,PK folder_id FK item_id FK (reports.report_id) item_type N
item_type is not used right now. Not sure why it is not in the item table. the item_id is most likely the reports.report_id field.
attachment_id A,PK file_id FK(reports.report_id) filename T(255) filesize N owner_user_id FK(userid) last_updated_on D last_updated_by FK(userid)
surl_id A, PK surl_owner_user_id FK(user) surl_map T(127) surl_url T(255) surl_last_updated_by FK(user) surl_last_updated_on D surl_uniquemap T(127)
The surl_uniquemap is created to quickly validate if the combination of userid and a url map is unique. So this column is redundant data but needed for validating uniqueness. This is just a quick work around.
seq_name T(50),PK seq_value N
This table is used to come up with sequence numbers for a given sequence name. The current value of the sequence is in seq_value. This value gets incremented everytime a new sequence number is needed for the given sequence. In retrospect i think auto numbers may work better.
Deprecate this as well. I haven't used it in a while. It shouldn't influence the current functionality.
report_type_id an report_type_name report_type_table_name report_type_column_name
applications application_notes topics notes report_types development_type
folders.folder_id sql_statements.id filed_items.filed_item_id file_attachments.attachment_id shorturls.surl_id