If we want to export the database and run certain queries against it, say in Tableau, would it just be guess work or can someone point to a community resource on the database’s data model?
I’ve found an older post with a diagram/schema of the database’s tables, but does anyone know if there’s a data dictionary that explains each of the individual items and what they do?
I’m not an expert on this sort of stuff, so apologies in advance if I’m using the wrong language. Hopefully my question makes sense!
I don’t think something like that exists as such diagrams tend to get outdated quite fast.
But the table and column names are quite self explanatory. I recommend you take a look at the database with the Data Explorer Plugin or your favorite database tool. You can always come back with specific questions if you don’t find the data you are looking for.
And if you have any questions that the column name / Data Explorer enum explanation doesn’t resolve, check out app/model/table_name.rb in the Discourse source code!
enter password, then sudo -u postgres psql discourse
Then, being very careful I run queries like
SELECT table_name, table_type
FROM INFORMATION_SCHEMA.TABLES
WHERE table_catalog = 'discourse'
AND table_schema = 'public';
* currently 138 rows
SELECT table_name, column_name, data_type, column_default, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog = 'discourse'
AND table_schema = 'public'
ORDER BY table_name;
* currently 1258 rows, a good idea to have a WHERE table_name = '{{some table name here}}' or a LIMIT in that query.
The “table_name” is a place holder for an actual file name. The file names are the singular of the table names. eg. user.rb - the users table, group.rb - the groups table.
at the bottom of each is similar to
# == Schema Information
#
# Table name: drafts
#
# id :integer not null, primary key
# user_id :integer not null
# draft_key :string not null
# data :text not null
# created_at :datetime not null
# updated_at :datetime not null
# sequence :integer default(0), not null
# revisions :integer default(1), not null
#
# Indexes
#
# index_drafts_on_user_id_and_draft_key (user_id,draft_key)
#
