Database's Data Model?

(傑夫·羅布森) #1

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!

(Gerhard Schlager) #2

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.

(Kane York) #3

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!

(Mittineague) #4

I often prefer the command line.

cd /var/discourse 
sudo ./launcher enter app

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.

(Bas van Leeuwen) #5

I can’t find this file here?
discourse/app/models at master · discourse/discourse · GitHub

(Mittineague) #6

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)
#
(Bas van Leeuwen) #7

Ah got it, sorry, was a bit confused :sleeping:

Hoped there was a table_names overview :slight_smile: