User:MPopov (WMF)/Notes/Quarry

Tables

SHOW tables;

Tables_in_bnwiki_p
abuse_filter
abuse_filter_action
abuse_filter_history
abuse_filter_log
actor
actor_archive
actor_filearchive
actor_image
actor_ipblocks
actor_logging
actor_oldimage
actor_recentchanges
actor_revision
actor_user
archive
archive_compat
archive_userindex
babel
category
categorylinks
change_tag
change_tag_def
comment
comment_filearchive
comment_image
comment_ipblocks
comment_logging
comment_oldimage
comment_protected_titles
comment_recentchanges
comment_revision
content
content_models
externallinks
filearchive
filearchive_compat
filearchive_userindex
flaggedimages
flaggedpage_config
flaggedpage_pending
flaggedpages
flaggedrevs
flaggedrevs_promote
flaggedrevs_statistics
flaggedrevs_stats
flaggedrevs_stats2
flaggedrevs_tracking
flaggedtemplates
geo_tags
global_block_whitelist
image
image_compat
imagelinks
interwiki
ip_changes
ipblocks
ipblocks_compat
ipblocks_ipindex
ipblocks_restrictions
iwlinks
l10n_cache
langlinks
linter
logging
logging_compat
logging_logindex
logging_userindex
module_deps
oldimage
oldimage_compat
oldimage_userindex
page
page_compat
page_props
page_restrictions
pagelinks
pif_edits
protected_titles
protected_titles_compat
recentchanges
recentchanges_compat
recentchanges_userindex
redirect
revision
revision_actor_temp
revision_compat
revision_userindex
site_identifiers
site_stats
sites
slot_roles
slots
templatelinks
transcode
updatelog
user
user_former_groups
user_groups
user_properties
wbc_entity_usage
wikilove_log

Edits

DESCRIBE revision;

Field	Type	Null	Key	Default	Extra
rev_id	int(8) unsigned	NO		0
rev_page	int(8) unsigned	NO		0
rev_comment_id	decimal(20,0)	YES		None
rev_actor	decimal(20,0)	YES		None
rev_timestamp	varbinary(14)	NO
rev_minor_edit	tinyint(1) unsigned	NO		0
rev_deleted	tinyint(1) unsigned	NO		0
rev_len	int(8) unsigned	YES		None
rev_parent_id	int(8) unsigned	YES		None
rev_sha1	varbinary(32)	YES		None

Edit Summary

DESCRIBE comment; and DESCRIBE comment_revision;

Field	Type	Null	Key	Default	Extra
comment_id	bigint(20) unsigned	NO		0
comment_hash	int(11)	NO		None
comment_text	blob	NO		None
comment_data	blob	YES		None

Edit Tags

DESCRIBE change_tag;

Field	Type	Null	Key	Default	Extra
ct_id	int(10) unsigned	NO		0
ct_rc_id	int(10) unsigned	YES		None
ct_log_id	int(10) unsigned	YES		None
ct_rev_id	int(10) unsigned	YES		None
ct_params	blob	YES		None
ct_tag_id	int(10) unsigned	NO		None

DESCRIBE change_tag_def;

Field	Type	Null	Key	Default	Extra
ctd_id	int(10) unsigned	NO		0
ctd_name	varbinary(255)	NO		None
ctd_user_defined	tinyint(1)	NO		None
ctd_count	bigint(20) unsigned	NO		0

Examples

Content Translation edit summaries

SELECT rev_id AS revision_id, rev_page AS page_id, comment_text AS revision_summary
FROM change_tag_def ctd
JOIN change_tag ct ON ctd.ctd_id = ct.ct_tag_id
JOIN revision rev ON ct.ct_rev_id = rev.rev_id
-- JOIN revision_comment_temp rct ON rev.rev_id = rct.revcomment_rev -- internal only
-- JOIN `comment` c ON rct.revcomment_comment_id = c.comment_id -- internal only
JOIN `comment` c ON rev.rev_comment_id = c.comment_id -- public only
WHERE ctd_name = 'contenttranslation'