Ingimp DB schema
From StatsJam
This document lays out the DB schema for ingimp. See ingimp DB views for related views.
The database is partitioned into a number of tables corresponding to the various events that are recorded by the system. Below is a walkthrough of the tables.
[edit] Table interaction_log
Every log has an entry in the table interaction_log, described as:
CREATE TABLE interaction_log (
log_num SERIAL PRIMARY KEY,
version VARCHAR(256) NOT NULL,
wrapper_version VARCHAR(20) NOT NULL,
user_id VARCHAR(256),
log_date TIMESTAMP NOT NULL,
timezone INTEGER NOT NULL,
locale VARCHAR(256), -- is NULL if can't determine or is English
log_file_name VARCHAR(256) NOT NULL,
gimp_version VARCHAR(256) NOT NULL,
platform VARCHAR(256) NOT NULL,
platform_system VARCHAR(256) NOT NULL,
platform_release VARCHAR(256) NOT NULL,
platform_version VARCHAR(256) NOT NULL,
platform_machine VARCHAR(256) NOT NULL,
platform_processor VARCHAR(256) NOT NULL,
num_disabled_runs SMALLINT NOT NULL,
log_header VARCHAR(20000),
session_tags VARCHAR(1024),
log_url VARCHAR(256)
);
Each log session has a unique log_num. Most fields in this table should be self-explanatory; they primarily refer to attributes of the user's system and setup. log_header is an optional free-form header that is currently not used, log_url is also not currently used, while platform_* refer to various attributes of the user's computing platform.
[edit] Table event_record and Related Tables
Each event that modifies the document in some way is contained within the table event_record:
CREATE TABLE event_record (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
command_num INTEGER NOT NULL,
event_type_id SMALLINT REFERENCES event_type_names(event_type_id),
command_id INTEGER REFERENCES command_names(command_id),
old_image_id INTEGER,
image_id INTEGER,
test_image VARCHAR(256),
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
elapsed_recording_time_usec DOUBLE PRECISION NOT NULL
);
log_num and entry_num form a unique key pair for most records. command_id references the table command_names, which gives you the name of each command executed (listed below). elapsed_time_usec refers to the elapsed time from the start of the session, in microseconds, for this particular event. elapsed_recording_time_usec is the amount of time to actually perform the logging (which will only be significant for events in which an image histogram is recorded).
event_type_id refers to the type of event. The various event types are found in the table event_type_names, described below.
CREATE TABLE command_names (
command_id SERIAL PRIMARY KEY,
command_name VARCHAR(256) NOT NULL
);
All command names are added to the table as they are encountered in the log files. Undo/redo is an exception. An undo/redo event's command name is the name of the command undone, not "Undo" or "Redo." Accordingly, we add "Undo" and "Redo" to our list of command names, below, and provide a view that replaces undo/redo event command names with "Undo"/"Redo" (see below).
COPY command_names(command_name) FROM STDIN; Undo Redo \.
CREATE TABLE event_type_names (
event_type_id SMALLINT PRIMARY KEY, -- GimpTemplateCreateImage, GimpCreateImage, etc.
event_name VARCHAR(256) NOT NULL
);
COPY event_type_names(event_type_id, event_name) FROM STDIN DELIMITER ',';
0,GimpUndoEvent
1,GimpTemplateCreateImage
2,GimpCreateImage
3,GimpImageRevert
4,GimpImageOpen
5,GimpImageLastOpened
6,GimpImageSaveRequested
7,GimpImageSave
8,GimpImageDuplicate
9,GimpImageDispose
10,GimpPlugInInvoked
11,GimpPlugInCompleted
\.
Each of the above events represents a typical, high-level event. For example, a GimpUndoEvent is any event that modifies the state of the undo stack: Applying a new command, undoing a command, or redoing a command (not just undo'ing or redo'ing a command). Thus, all event_records with an event_type_id of '0' refer to operations that modify the undo stack. Given the event_type_id, we can retrieve more specific information about an event, as described next.
[edit] Table gimp_undo_event and Associated Tables
If the event_type_id of an event_record is '0', we know it is an undo event. More specific information can be found in the gimp_undo_event table:
CREATE TABLE gimp_undo_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
undo_event_type_id SMALLINT REFERENCES undo_event_type_names(undo_event_type_id),
undo_time BIGINT,
undo_command_type_id SMALLINT REFERENCES undo_command_type_names(undo_command_type_id),
undo_size DOUBLE PRECISION,
dirty_mask INTEGER,
action_cancelled BOOLEAN NOT NULL,
composition_time_secs INTEGER NOT NULL -- The time it took for the user to compose the command, if known. -1 if not known
);
This table can be joined with the event_record table via the log_num and entry_num fields. The primary field of interest here is undo_event_type_id, which indicates whether a new command was applied ("pushed" on the stack), or whether an action was undone or redone. The following tables show the value for each type of undo event:
CREATE TABLE undo_event_type_names (
undo_event_type_id SMALLINT PRIMARY KEY,
undo_event_type_name VARCHAR(256) NOT NULL
);
COPY undo_event_type_names(undo_event_type_id, undo_event_type_name) FROM STDIN DELIMITER ',';
0,Undo Pushed
1,Undo Expired
2,Redo Expired
3,Undo Executed
4,Redo Executed
5,Undo/Redo Info Cleared
6,Undo Freeze
7,Undo Thaw
\.
Events with undo_event_type_id's 3 and 4 refer to undo/redo events, respectively.
The undo_command_type_id maps onto the following values:
CREATE TABLE undo_command_type_names (
undo_command_type_id SMALLINT PRIMARY KEY,
undo_command_type_name VARCHAR(256) NOT NULL
);
COPY undo_command_type_names(undo_command_type_id, undo_command_type_name) FROM STDIN DELIMITER ',';
-1,Invalid
0,Invalid
1,Scale image
2,Resize image
3,Flip image
4,Rotate image
5,Crop image
6,Convert image
7,Remove item
8,Merge layers
9,Merge vectors
10,Quick Mask
11,Grid
12,Guide
13,Sample Point
14,Drawable
15,Drawable mod
16,Selection mask
17,Item visibility
18,Linked item
19,Item properties
20,Move item
21,Scale item
22,Resize item
23,Add layer
24,Add layer mask
25,Apply layer mask
26,Floating selection to layer
27,Float selection
28,Anchor floating selection
29,Remove floating selection
30,Paste
31,Cut
32,Text
33,Transform
34,Paint
35,Attach parasite
36,Remove parasite
37,Import paths
38,Plug-In
39,Image type
40,Image size
41,Resolution change
42,Grid
43,Change indexed palette
44,Guide
45,Sample Point
46,Drawable
47,Drawable mod
48,Selection mask
49,Rename item
50,Move item
51,Item visibility
52,Set item linked
53,New layer
54,Delete layer
55,Reposition layer
56,Set layer mode
57,Set layer opacity
58,Lock/Unlock alpha channel
59,Text layer
60,Text layer modification
61,Add layer mask
62,Delete layer mask
63,Apply layer mask
64,Show layer mask
65,New channel
66,Delete channel
67,Reposition channel
68,Channel color
69,New vectors
70,Delete vectors
71,Vectors mod
72,Reposition vectors
73,FS to layer
74,FS rigor
75,FS relax
76,Transform
77,Paint
78,Ink
79,Select foreground
80,Attach parasite
81,Remove parasite
82,Non-undoable action
83,Set preserve transparency
\.
These describe, at a high level, the type of command applied.
Other tables describing the specifics of an event_record are as follows:
CREATE TABLE gimp_create_image (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
comment_size INTEGER
);
CREATE TABLE gimp_image_open (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
uri_length SMALLINT NOT NULL,
uri_alpha_count SMALLINT NOT NULL,
uri_digit_count SMALLINT NOT NULL,
uri_punct_count SMALLINT NOT NULL,
uri_space_count SMALLINT NOT NULL,
uri_forward_slash_count SMALLINT NOT NULL,
uri_backward_slash_count SMALLINT NOT NULL,
uri_hash BIGINT NOT NULL,
filename_length SMALLINT NOT NULL,
filename_alpha_count SMALLINT NOT NULL,
filename_digit_count SMALLINT NOT NULL,
filename_punct_count SMALLINT NOT NULL,
filename_space_count SMALLINT NOT NULL,
filename_forward_slash_count SMALLINT NOT NULL,
filename_backward_slash_count SMALLINT NOT NULL,
filename_hash BIGINT NOT NULL
);
CREATE TABLE gimp_image_last_opened (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
image_index SMALLINT NOT NULL,
filename_length SMALLINT NOT NULL,
filename_alpha_count SMALLINT NOT NULL,
filename_digit_count SMALLINT NOT NULL,
filename_punct_count SMALLINT NOT NULL,
filename_space_count SMALLINT NOT NULL,
filename_forward_slash_count SMALLINT NOT NULL,
filename_backward_slash_count SMALLINT NOT NULL,
filename_hash BIGINT NOT NULL
);
CREATE TABLE gimp_image_save_requested (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
save_as SMALLINT NOT NULL
);
CREATE TABLE gimp_image_save (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
save_as SMALLINT NOT NULL,
save_a_copy SMALLINT NOT NULL,
status INTEGER NOT NULL,
uri_length SMALLINT NOT NULL,
uri_alpha_count SMALLINT NOT NULL,
uri_digit_count SMALLINT NOT NULL,
uri_punct_count SMALLINT NOT NULL,
uri_space_count SMALLINT NOT NULL,
uri_forward_slash_count SMALLINT NOT NULL,
uri_backward_slash_count SMALLINT NOT NULL,
uri_hash BIGINT NOT NULL,
filename_length SMALLINT NOT NULL,
filename_alpha_count SMALLINT NOT NULL,
filename_digit_count SMALLINT NOT NULL,
filename_punct_count SMALLINT NOT NULL,
filename_space_count SMALLINT NOT NULL,
filename_forward_slash_count SMALLINT NOT NULL,
filename_backward_slash_count SMALLINT NOT NULL,
filename_hash BIGINT NOT NULL
);
[edit] Tables gimp_image, gimp_layer, gimp_layer_instance, gimp_histogram_data, and gimp_histogram_data_instance
If you would like to retrieve more information about an image associated with an event, you need to look it up in the gimp_image table:
CREATE TABLE gimp_image (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
image_id INTEGER,
width INTEGER,
height INTEGER,
xresolution DOUBLE PRECISION,
yresolution DOUBLE PRECISION,
resolution_unit INTEGER,
base_type INTEGER,
num_cols INTEGER,
dirty SMALLINT,
dirty_time BIGINT,
instance_count INTEGER,
disp_count INTEGER,
tattoo_state INTEGER,
qmask_state INTEGER,
qmask_inverted INTEGER,
group_count INTEGER,
pushing_undo_group INTEGER,
comp_preview_valid INTEGER
);
Joining the log_num, entry_num, and image_id from the event_record table and gimp_image table gives you the image data for that particular event. Joining these three values with the gimp_layer table gives you all the layers associated with the image for that event:
CREATE TABLE gimp_layer (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
image_id INTEGER,
layer_id INTEGER,
layer_instance_id INTEGER REFERENCES gimp_layer_instance(layer_instance_id)
);
Since layer information changes infrequently for multi-layer images, the gimp_layer table references a layer instance that contains the additional information for a layer:
CREATE TABLE gimp_layer_instance (
layer_instance_id SERIAL PRIMARY KEY,
layer_num SMALLINT,
width INTEGER,
height INTEGER,
visible SMALLINT,
linked SMALLINT,
floating SMALLINT,
removed SMALLINT,
name_length SMALLINT,
name_alpha_count SMALLINT,
name_digit_count SMALLINT,
name_punct_count SMALLINT,
name_space_count SMALLINT,
name_forward_slash_count SMALLINT,
name_backward_slash_count SMALLINT,
name_hash BIGINT,
bytes INTEGER,
drawable_type INTEGER,
has_alpha SMALLINT,
preview_valid SMALLINT,
opacity DOUBLE PRECISION,
layer_mode INTEGER,
preserve_trans SMALLINT
);
As an example, here is the SQL to retrieve some layer information for an image in the first log in the database:
SELECT
gimp_image.width AS "Image Width",
gimp_image.height AS "Image Height",
gimp_layer_instance.width AS "Layer 1 Width",
gimp_layer_instance.height AS "Layer 1 Height",
gimp_layer_instance.visible AS "Layer 1 Visible"
FROM
gimp_image,
gimp_layer,
gimp_layer_instance
WHERE
gimp_image.log_num = 1 AND -- Just take image from first log
gimp_image.log_num = gimp_layer.log_num AND
gimp_image.entry_num = gimp_layer.entry_num AND
gimp_image.image_id = gimp_layer.image_id AND
gimp_layer_instance.layer_num = 0 AND
gimp_layer.layer_instance_id = gimp_layer_instance.layer_instance_id
LIMIT 1 -- Just return 1 result
Here is the query actually rendered in Stats Jam:
| Image Width | Image Height | Layer 1 Width | Layer 1 Height | Layer 1 Visible |
|---|---|---|---|---|
| 1007 | 639 | 1007 | 639 | 1 |
Histogram data for layers and images is similarly stored in gimp_histogram_data and gimp_histogram_data_instance. The histogram data is stored as comma-separated values in the data field of gimp_histogram_data_instance.
CREATE TABLE gimp_histogram_data_instance (
histo_data_instance_id SERIAL PRIMARY KEY,
count DOUBLE PRECISION NOT NULL,
max DOUBLE PRECISION NOT NULL,
mean DOUBLE PRECISION NOT NULL,
median DOUBLE PRECISION NOT NULL,
std_dev DOUBLE PRECISION NOT NULL,
empty_bin_count INTEGER NOT NULL,
data TEXT
);
CREATE TABLE gimp_histogram_data (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
image_id INTEGER NOT NULL,
layer_id INTEGER, -- if it is a composite histogram, this value is NULL
channel INTEGER NOT NULL,
histo_data_instance_id INTEGER REFERENCES gimp_histogram_data_instance(histo_data_instance_id)
);
[edit] Interaction Events
Interaction events (mouse, keyboard, window events) are stored in their own separate tables. Like the event_record table, log_num and entry_num are the keys that uniquely identify an event for each session. For example, the recording of focus events is in window_focus_event:
CREATE TABLE window_focus_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
image_id INTEGER,
window_name VARCHAR(256),
window_role VARCHAR(256),
event_type SMALLINT NOT NULL,
focus_in SMALLINT NOT NULL,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
screen_num SMALLINT NOT NULL
);
The entry_num field is unique amongst all such event tables. Thus, if the user applied a command then lost the window focus, we would see, for example, entry_num '1' in the event_record table then entry_num '2' in the window_focus_event table.
Incidentally, window focus events record the x/y coordinate of the window because moving a window causes a focus out/in event. I don't quite know why.
Window events can be either document window events or tool palette events. Document window events always have an image_id, but no window_name nor role (these values are NULL). Tool palette events have a NULL image_id but a non-NULL window_name and role.
Other window events:
CREATE TABLE window_button_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
image_id INTEGER,
window_name VARCHAR(256),
window_role VARCHAR(256),
event_type SMALLINT NOT NULL,
button SMALLINT NOT NULL,
button_state INTEGER NOT NULL
);
CREATE TABLE window_key_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
image_id INTEGER,
window_name VARCHAR(256),
window_role VARCHAR(256),
event_type SMALLINT NOT NULL,
modifier_state INTEGER NOT NULL
);
CREATE TABLE window_state_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
image_id INTEGER,
window_name VARCHAR(256),
window_role VARCHAR(256),
changed_mask INTEGER NOT NULL,
new_window_state INTEGER NOT NULL
);
CREATE TABLE window_size_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
image_id INTEGER,
window_name VARCHAR(256),
window_role VARCHAR(256),
width INTEGER NOT NULL,
height INTEGER NOT NULL,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
screen_num INTEGER NOT NULL
);
CREATE TABLE window_showhide_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
image_id INTEGER,
window_name VARCHAR(256),
window_role VARCHAR(256),
showwin INTEGER NOT NULL
);
CREATE TABLE window_destroy_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
window_name VARCHAR(256),
window_role VARCHAR(256)
);
[edit] Application Context Events
Events that affect the overall state of the application are stored in the table app_context_event:
CREATE TABLE app_context_event_names (
event_type_id INTEGER PRIMARY KEY,
event_name VARCHAR(256) NOT NULL
);
CREATE TABLE tool_type_names (
tool_type_name_id SERIAL PRIMARY KEY,
tool_type_name VARCHAR(256) NOT NULL
);
CREATE TABLE app_context_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
event_type_id INTEGER REFERENCES app_context_event_names(event_type_id),
tool_type INTEGER,
tool_type_name_id INTEGER REFERENCES tool_type_names(tool_type_name_id),
tool_options_type INTEGER,
visible SMALLINT,
image_id INTEGER
);
COPY app_context_event_names(event_type_id, event_name) FROM STDIN DELIMITER ',';
0,AppContextToolChanged
1,AppContextImageChanged
2,AppContextForegroundChanged
3,AppContextBackgroundChanged
4,AppContextBrushOpacityChanged
5,AppContextPaintModeChanged
6,AppContextBrushChanged
7,AppContextPatternChanged
8,AppContextGradientChanged
9,AppContextPaletteChanged
10,AppContextFontChanged
\.
[edit] Actions
A number of "actions" are recorded. Actions include when menu items are moused over or selected, changes in zoom, etc.
CREATE TABLE action_event_names (
action_id SERIAL PRIMARY KEY,
action_name VARCHAR(256) NOT NULL
);
CREATE TABLE action_event (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
event_date TIMESTAMP NOT NULL,
elapsed_time_usec DOUBLE PRECISION NOT NULL,
action_id INTEGER REFERENCES action_event_names(action_id)
);
[edit] Screen/Monitor Info
We record information regarding each screen/monitor. Screens should refer to the physical device, while monitor should refer to the actual display space.
CREATE TABLE screen (
log_num INTEGER REFERENCES interaction_log(log_num),
screen_num SMALLINT NOT NULL,
screen_width INTEGER NOT NULL,
screen_height INTEGER NOT NULL,
screen_width_mm INTEGER NOT NULL,
screen_height_mm INTEGER NOT NULL
);
CREATE TABLE monitor (
log_num INTEGER REFERENCES interaction_log(log_num),
monitor_num SMALLINT NOT NULL,
monitor_x INTEGER NOT NULL,
monitor_y INTEGER NOT NULL,
monitor_width INTEGER NOT NULL,
monitor_height INTEGER NOT NULL
);
[edit] Consent Timing Data
If users grant consent for collecting these data, we record how long they spend viewing the consent agreement and how much they actually scroll through the consent agreement. Right now, these values are not in a form amenable to data analysis (we need to change that).
CREATE TABLE consent_data (
log_num INTEGER REFERENCES interaction_log(log_num),
consent_view_order SMALLINT NOT NULL,
consent_dwell_times VARCHAR(1024) NOT NULL,
consent_max_scroll_values VARCHAR(1024) NOT NULL,
user_view_order VARCHAR(1024) NOT NULL
);
[edit] Plug-Ins
Plug-ins can call other commands, all of which are recorded in the log file. Accordingly, we mark the start and stop of a plug-in so we can filter out commands not invoked by the user. The following tables track these data.
-- There is some overlap here with action event names; in fact, they
-- may be one in the same. But for now, we keep them separate
CREATE TABLE gimp_plug_in_names (
plug_in_id SERIAL PRIMARY KEY,
plug_in_name VARCHAR(256) NOT NULL
);
CREATE TABLE gimp_plug_in_invoked (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
plug_in_id INTEGER REFERENCES gimp_plug_in_names(plug_in_id),
plug_in_type SMALLINT NOT NULL,
reshow_last INTEGER NOT NULL
);
CREATE TABLE gimp_plug_in_completed (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
plug_in_id INTEGER REFERENCES gimp_plug_in_names(plug_in_id),
plug_in_type SMALLINT NOT NULL
);
[edit] Locale
A user's locale is determined a priori by looking at the language used for commands and doing a reverse-lookup in the i18n tools (this is most definitely a hack -- we should have recorded the unlocalized command names, but didn't). The following tables describe the locales, used by interaction_log.
CREATE TABLE locales (
code VARCHAR(12) NOT NULL,
locale VARCHAR(256) NOT NULL
);
COPY locales(code, locale) FROM STDIN DELIMITER ',';
aa,Afar
ab,Abkhazian
af,Afrikaans
ak,Akan
sq,Albanian
am,Amharic
ar,Arabic
an,Aragonese
hy,Armenian
as,Assamese
av,Avaric
ae,Avestan
ay,Aymara
az,Azerbaijani
ba,Bashkir
bm,Bambara
eu,Basque
be,Belarusian
bn,Bengali
bh,Bihari
bi,Bislama
bs,Bosnian
br,Breton
bg,Bulgarian
my,Burmese
ca,Catalan
ch,Chamorro
ce,Chechen
zh,Chinese
cu,Church
cv,Chuvash
kw,Cornish
co,Corsican
cr,Cree
cs,Czech
da,Danish
dv,Divehi
nl,Dutch
dz,Dzongkha
en,English
eo,Esperanto
et,Estonian
ee,Ewe
fo,Faroese
fj,Fijian
fi,Finnish
fr,French
fy,Western
ff,Fulah
ka,Georgian
de,German
gd,Gaelic
ga,Irish
gl,Galician
gv,Manx
el,Greek
gn,Guarani
gu,Gujarati
ht,Haitian
ha,Hausa
he,Hebrew
hz,Herero
hi,Hindi
ho,Hiri
hu,Hungarian
ig,Igbo
is,Icelandic
io,Ido
ii,Sichuan
iu,Inuktitut
ie,Interlingue
ia,Interlingua
id,Indonesian
ik,Inupiaq
it,Italian
jv,Javanese
ja,Japanese
kl,Kalaallisut
kn,Kannada
ks,Kashmiri
kr,Kanuri
kk,Kazakh
km,Central
ki,Kikuyu
rw,Kinyarwanda
ky,Kirghiz
kv,Komi
kg,Kongo
ko,Korean
kj,Kuanyama
ku,Kurdish
lo,Lao
la,Latin
lv,Latvian
li,Limburgan
ln,Lingala
lt,Lithuanian
lb,Luxembourgish
lu,Luba
lg,Ganda
mk,Macedonian
mh,Marshallese
ml,Malayalam
mi,Maori
mr,Marathi
ms,Malay
mg,Malagasy
mt,Maltese
mo,Moldavian
mn,Mongolian
na,Nauru
nv,Navajo
nr,Ndebele
nd,Ndebele
ng,Ndonga
ne,Nepali
nn,Norwegian
nb,Bokm
no,Norwegian
ny,Chichewa
oc,Occitan
oj,Ojibwa
or,Oriya
om,Oromo
os,Ossetian
pa,Panjabi
fa,Persian
pi,Pali
pl,Polish
pt,Portuguese
ps,Pushto
qu,Quechua
rm,Romansh
ro,Romanian
rn,Rundi
ru,Russian
sg,Sango
sa,Sanskrit
sr,Serbian
hr,Croatian
si,Sinhala
sk,Slovak
sl,Slovenian
se,Northern
sm,Samoan
sn,Shona
sd,Sindhi
so,Somali
st,Sotho
es,Spanish
sc,Sardinian
ss,Swati
su,Sundanese
sw,Swahili
sv,Swedish
ty,Tahitian
ta,Tamil
tt,Tatar
te,Telugu
tg,Tajik
tl,Tagalog
th,Thai
bo,Tibetan
ti,Tigrinya
to,Tonga
tn,Tswana
ts,Tsonga
tk,Turkmen
tr,Turkish
tw,Twi
ug,Uighur
uk,Ukrainian
ur,Urdu
uz,Uzbek
ve,Venda
vi,Vietnamese
vo,Volap
cy,Welsh
wa,Walloon
wo,Wolof
xh,Xhosa
yi,Yiddish
yo,Yoruba
za,Zhuang
zu,Zulu
\.
[edit] Cached Tables
Some queries take significant time to complete. We regularly cache some queries in the tables below.
CREATE TABLE images_on_close_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
image_id INTEGER NOT NULL
);
CREATE TABLE image_sizes_on_close_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
image_id INTEGER NOT NULL,
width INTEGER NOT NULL,
height INTEGER NOT NULL
);
CREATE TABLE paint_tools_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL
);
CREATE TABLE text_tools_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL
);
CREATE TABLE filter_tools_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL
);
CREATE TABLE paint_counts_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
paint_count INTEGER NOT NULL
);
CREATE TABLE text_counts_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
text_count INTEGER NOT NULL
);
CREATE TABLE filter_counts_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
filter_count INTEGER NOT NULL
);
CREATE TABLE tool_stats_cache (
ave_paint DOUBLE PRECISION NOT NULL,
std_paint DOUBLE PRECISION NOT NULL,
ave_text DOUBLE PRECISION NOT NULL,
std_text DOUBLE PRECISION NOT NULL,
ave_filter DOUBLE PRECISION NOT NULL,
std_filter DOUBLE PRECISION NOT NULL
);
CREATE TABLE per_log_command_stats_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
command_name VARCHAR(256) NOT NULL,
frequency INTEGER NOT NULL
);
CREATE TABLE per_log_undo_stats_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
command_name VARCHAR(256) NOT NULL,
frequency INTEGER NOT NULL
);
CREATE TABLE per_log_redo_stats_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
command_name VARCHAR(256) NOT NULL,
frequency INTEGER NOT NULL
);
CREATE TABLE command_stats_cache (
command_name VARCHAR(256) NOT NULL,
frequency INTEGER NOT NULL
);
CREATE TABLE undo_stats_cache (
command_name VARCHAR(256) NOT NULL,
frequency INTEGER NOT NULL
);
CREATE TABLE redo_stats_cache (
command_name VARCHAR(256) NOT NULL,
frequency INTEGER NOT NULL
);
CREATE TABLE image_layer_counts_cache (
log_num INTEGER REFERENCES interaction_log(log_num),
entry_num INTEGER NOT NULL,
image_id INTEGER NOT NULL,
num_layers INTEGER NOT NULL
);
[edit] Persona Tables
The following tables are used when dynamically generating a user's online persona (the stick-figure person). We originally called these "avatars," hence the table names.
CREATE TABLE avatar_command_id_summary_cache (
user_id VARCHAR(256),
undo_command_type_id INTEGER REFERENCES undo_command_type_names(undo_command_type_id),
avg_command_use DOUBLE PRECISION,
sqrt_command_use DOUBLE PRECISION,
percent_command_seen DOUBLE PRECISION,
sqrt_num_user_sessions DOUBLE PRECISION
);
CREATE TABLE avatar_command_name_summary_cache (
user_id VARCHAR(256),
command_name VARCHAR(256) NOT NULL,
avg_command_use DOUBLE PRECISION,
sqrt_command_use DOUBLE PRECISION,
percent_command_seen DOUBLE PRECISION
);
CREATE TABLE user_avatar_types (
user_id VARCHAR(256) PRIMARY KEY,
task_type INTEGER NOT NULL
);
Remember there are also the ingimp DB views.
