Ingimp DB views

From StatsJam

The following are views available in the ingimp DB.
-- Selects significant users, where a significant user is defined as someone
-- who has saved an image on at least two different occassions, at least 1 day apart
CREATE VIEW significant_users AS
SELECT
  users_who_saved_table.user_id
FROM
 (SELECT
    interaction_log.user_id,
    EXTRACT(EPOCH FROM (MAX(interaction_log.log_date)-MIN(interaction_log.log_date)))/60/60/24 AS num_days_diff
  FROM
    interaction_log,
    (SELECT
      DISTINCT event_record.log_num
    FROM
      event_record
    WHERE
      event_record.event_type_id = 7 -- This is the ID for an image save event
    ) AS logs_with_save_table
  WHERE
    interaction_log.log_num = logs_with_save_table.log_num
    AND user_id NOT IN (SELECT user_id FROM ingimp_developer_ids)
  GROUP BY
    interaction_log.user_id
  ) AS users_who_saved_table
WHERE
  num_days_diff >= 1
;

-- Logs for significant users (defined above)
CREATE VIEW significant_logs AS
SELECT
  log_num
FROM
  interaction_log,
  significant_users
WHERE
  interaction_log.user_id = significant_users.user_id
;

-- Gets all images that are document images (and not opened by GIMP to create thumbnails and such)
CREATE VIEW document_images AS
SELECT
    doc_event.log_num,
    doc_event.image_id
FROM
    window_showhide_event AS doc_event
WHERE
    doc_event.image_id IS NOT NULL AND
    doc_event.image_id >= 0
GROUP BY
    doc_event.log_num,
    doc_event.image_id
;


-- all event_records for images in documents (not thumbnails)
CREATE VIEW document_event AS
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec
FROM
    event_record AS event,
    document_images AS images
WHERE
    event.log_num = images.log_num AND
    (event.image_id = images.image_id OR
     event.image_id = -1 OR
     event.image_id IS NULL)
;


-- Table massaging... Skip down to command_events and use that in place of event_record

-- Domain-specific commands that modify the document (no undo/redos)
CREATE VIEW native_command_events AS
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec
FROM
    event_record as event,
    gimp_undo_event as undo_event
WHERE
    event.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    undo_event.undo_event_type_id = 0; -- push events


-- Events like open, save, but nothing that modifies undo stack
CREATE VIEW non_undo_stack_events AS
SELECT
    *
FROM
    event_record as event
WHERE
    event.event_type_id <> 0;


CREATE VIEW undo_events AS
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec,
    command_names.command_name
FROM
    event_record AS event,
    gimp_undo_event AS undo_event,
    command_names
WHERE
    event.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    command_names.command_id = event.command_id AND
    undo_event.undo_event_type_id = 3 -- undo executed
;


CREATE VIEW redo_events AS
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec,
    command_names.command_name
FROM
    event_record AS event,
    gimp_undo_event AS undo_event,
    command_names
WHERE
    event.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    command_names.command_id = event.command_id AND
    undo_event.undo_event_type_id = 4 -- redo executed
;


-- Undo/redo events recast as "normal" commands that can be looked up in command_names table
CREATE VIEW undo_redo_events AS
    SELECT
        event.log_num,
        event.entry_num,
        event.command_num,
        event.event_type_id,
        1 AS command_id, -- Undo is first entry in command name table
        event.old_image_id,
        event.image_id,
        event.test_image,
        event.event_date,
        event.elapsed_time_usec,
        event.elapsed_recording_time_usec
    FROM
        event_record AS event,
        gimp_undo_event as undo_event
    WHERE
        event.log_num = undo_event.log_num AND
        event.entry_num = undo_event.entry_num AND
        undo_event.undo_event_type_id = 3 -- undo executed
UNION
    SELECT
        event.log_num,
        event.entry_num,
        event.command_num,
        event.event_type_id,
        2 AS command_id, -- Redo is second entry in command name table
        event.old_image_id,
        event.image_id,
        event.test_image,
        event.event_date,
        event.elapsed_time_usec,
        event.elapsed_recording_time_usec
    FROM
        event_record AS event,
        gimp_undo_event as undo_event
    WHERE
        event.log_num = undo_event.log_num AND
        event.entry_num = undo_event.entry_num AND
        undo_event.undo_event_type_id = 4; -- redo executed


-- Reconstitutes document events so undo/redo can be mapped into
-- command_names table without having to do a special indirect
-- lookup (normally, the command id for an undo/redo event is the
-- command undone/redone)
CREATE VIEW command_events AS
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    command_names.command_name,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec
FROM
    native_command_events AS event,
    command_names
WHERE
    event.command_id = command_names.command_id
UNION
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    command_names.command_name,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec
FROM
    non_undo_stack_events AS event,
    command_names
WHERE
    event.command_id = command_names.command_id
UNION
SELECT
    event.log_num,
    event.entry_num,
    event.command_num,
    event.event_type_id,
    event.command_id,
    command_names.command_name,
    event.old_image_id,
    event.image_id,
    event.test_image,
    event.event_date,
    event.elapsed_time_usec,
    event.elapsed_recording_time_usec
FROM
    undo_redo_events AS event,
    command_names
WHERE
    event.command_id = command_names.command_id;

    
-- Command counts
CREATE VIEW per_log_command_stats AS
SELECT
    event.log_num,
    event.command_name,
    COUNT(event.command_id) AS frequency
FROM
    command_events AS event
WHERE
    event.event_type_id <> 9 AND -- image dispose events
    event.event_type_id <> 6 -- save requested events
GROUP BY
    event.command_id,
    event.log_num,
    event.command_name
ORDER BY
    frequency DESC
;


CREATE VIEW per_log_undo_stats AS
SELECT
    event.log_num,
    command_name,
    COUNT(event.command_id) AS frequency
FROM
    undo_events AS event
GROUP BY
    event.log_num,
    event.command_id,
    command_name
ORDER BY
    frequency DESC;


CREATE VIEW per_log_redo_stats AS
SELECT
    event.log_num,
    command_name,
    COUNT(event.command_id) AS frequency
FROM
    redo_events AS event
GROUP BY
    event.command_id,
    event.log_num,
    command_name
ORDER BY
    frequency DESC;


CREATE VIEW command_stats AS
SELECT
    command_name,
    SUM(frequency) AS frequency
FROM
    per_log_command_stats_cache
GROUP BY
    command_name
ORDER BY
    frequency DESC;


CREATE VIEW undo_stats AS
SELECT
    command_name,
    SUM(frequency) AS frequency
FROM
    per_log_undo_stats_cache
GROUP BY
    command_name
ORDER BY
    frequency DESC;


CREATE VIEW redo_stats AS
SELECT
    command_name,
    SUM(frequency) AS frequency
FROM
    per_log_redo_stats_cache
GROUP BY
    command_name
ORDER BY
    frequency DESC;


CREATE VIEW commands_per_image AS
SELECT
    event.log_num,
    event.image_id,
    COUNT(event.entry_num) AS command_count
FROM
    document_event AS event,
    gimp_image as image
WHERE
    event.log_num = image.log_num AND
    event.entry_num = image.entry_num AND
    event.image_id = image.image_id
GROUP BY
    event.log_num,
    event.image_id;


CREATE VIEW commands_per_image_stats AS
SELECT
    '' AS stats,
    MIN(command_count) AS min_num_commands,
    MAX(command_count) AS max_num_commands,
    AVG(command_count) AS ave_num_commands,
    STDDEV(command_count) AS std_num_commands
FROM
    commands_per_image
GROUP BY
    stats;


-- Which commands are most frequently undone?
CREATE VIEW undone_commands AS
SELECT
    command_name,
    COUNT(event.command_id) AS frequency
FROM
    event_record AS event,
    command_names,
    gimp_undo_event AS undo_event
WHERE
    event.command_id = command_names.command_id AND
    event.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    undo_event.undo_event_type_id = 3 -- undo executed
GROUP BY
    event.command_id,
    command_name
ORDER BY
    frequency DESC;


CREATE VIEW redone_commands AS
SELECT
    command_name,
    COUNT(event.command_id) AS frequency
FROM
    event_record AS event,
    command_names,
    gimp_undo_event AS undo_event
WHERE
    event.command_id = command_names.command_id AND
    event.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    undo_event.undo_event_type_id = 4 -- redo executed
GROUP BY
    event.command_id,
    command_name
ORDER BY
    frequency DESC;


-- Command counts, focus counts
CREATE VIEW command_counts AS
SELECT
    event.log_num,
    COUNT(event.entry_num) AS count
FROM
    event_record AS event
GROUP BY
    event.log_num;


CREATE VIEW significant_sessions AS
SELECT
    command_counts.log_num
FROM
    command_counts
WHERE
    command_counts.count > 50
;


CREATE VIEW focus_counts AS
SELECT
    event.log_num,
    COUNT(event.entry_num) AS count
FROM
    window_focus_event AS event
GROUP BY
    event.log_num;


CREATE VIEW focus_stats AS
SELECT
    event.log_num,
    event.count AS num_commands,
    focus.count AS num_focus_events,
    event.count / focus.count AS commands_to_focus_ratio
FROM
    command_counts AS event,
    focus_counts AS focus
WHERE
    event.log_num = focus.log_num;


CREATE VIEW all_undo_timings AS
SELECT
    l.log_num,
    r.command_num,
    ((r.elapsed_time_usec - l.elapsed_time_usec)/1000000) AS undo_time_secs,
    command_name
FROM
    event_record AS l,
    event_record AS r,
    gimp_undo_event AS r_undo,
    command_names
WHERE
    r.command_num = (l.command_num + 1) AND
    l.log_num = r.log_num AND
    r.log_num = r_undo.log_num AND
    r.entry_num = r_undo.entry_num AND
    r_undo.undo_event_type_id = 3 AND -- undo executed
    l.command_id = command_names.command_id;


CREATE VIEW command_undo_timings AS
SELECT
    l.log_num,
    r.command_num,
    ((r.elapsed_time_usec - l.elapsed_time_usec)/1000000) AS undo_time_secs,
    command_name
FROM
    event_record AS l,
    event_record AS r,
    gimp_undo_event AS l_undo,
    gimp_undo_event AS r_undo,
    command_names
WHERE
    r.command_num = (l.command_num + 1) AND
    l.log_num = r.log_num AND
    r.log_num = r_undo.log_num AND
    r.entry_num = r_undo.entry_num AND
    r_undo.undo_event_type_id = 3 AND -- undo executed
    l.log_num = l_undo.log_num AND
    l.entry_num = l_undo.entry_num AND
    l_undo.undo_event_type_id = 0 AND -- undo pushed
    l.command_id = command_names.command_id;


-- Paint-like tools
CREATE VIEW paint_tools AS
SELECT
    log.log_num,
    undo_event.entry_num
FROM
    interaction_log as log,
    event_record as event,
    gimp_undo_event as undo_event
WHERE
    log.log_num = event.log_num AND
    log.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    (
    undo_event.undo_command_type_id = 34 OR -- Paint
    undo_event.undo_command_type_id = 77 -- Paint
    )
;


-- Filter-like tools
CREATE VIEW filter_tools AS
SELECT
    log.log_num,
    undo_event.entry_num
FROM
    interaction_log as log,
    event_record as event,
    gimp_undo_event as undo_event
WHERE
    log.log_num = event.log_num AND
    log.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    (
    undo_event.undo_command_type_id = 38 OR -- Plug-In
    undo_event.undo_command_type_id = 14 OR -- Drawable
    undo_event.undo_command_type_id = 46 -- Drawable
    )
;


-- Text tools
CREATE VIEW text_tools AS
SELECT
    log.log_num,
    undo_event.entry_num
FROM
    interaction_log as log,
    event_record as event,
    gimp_undo_event as undo_event
WHERE
    log.log_num = event.log_num AND
    log.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    (
    undo_event.undo_command_type_id = 32 OR -- Text
    undo_event.undo_command_type_id = 59 OR -- Text
    undo_event.undo_command_type_id = 60 -- Text modified
    )
;


CREATE VIEW paint_counts AS
SELECT
    log.log_num,
    COUNT(paint_tools.entry_num) AS paint_count
FROM
    interaction_log AS log
    LEFT JOIN paint_tools_cache AS paint_tools ON (log.log_num = paint_tools.log_num)
GROUP BY
    log.log_num
;


CREATE VIEW text_counts AS
SELECT
    log.log_num,
    COUNT(text_tools.entry_num) AS text_count
FROM
    interaction_log AS log
    LEFT JOIN text_tools_cache AS text_tools ON (log.log_num = text_tools.log_num)
GROUP BY
    log.log_num
;


CREATE VIEW filter_counts AS
SELECT
    log.log_num,
    COUNT(filter_tools.entry_num) AS filter_count
FROM
    interaction_log AS log
    LEFT JOIN filter_tools_cache AS filter_tools ON (log.log_num = filter_tools.log_num)
GROUP BY
    log.log_num
;


CREATE VIEW tool_stats AS
SELECT
    AVG(paint_count) AS ave_paint,
    STDDEV(paint_count) AS std_paint,
    AVG(text_count) AS ave_text,
    STDDEV(text_count) AS std_text,
    AVG(filter_count) AS ave_filter,
    STDDEV(filter_count) AS std_filter
FROM
    interaction_log AS log,
    paint_counts_cache AS paint_counts,
    text_counts_cache AS text_counts,
    filter_counts_cache AS filter_counts
WHERE
    log.log_num = paint_counts.log_num AND
    log.log_num = text_counts.log_num AND
    log.log_num = filter_counts.log_num
;

    
-- Need to verify this is the correct SQL
CREATE VIEW per_user_tool_stats AS
SELECT
    log.user_id,
    SUM(paint_count) AS paint_total,
    SUM(text_count) AS text_total,
    SUM(filter_count) AS filter_total,
    AVG(paint_count) AS ave_paint,
    STDDEV(paint_count) AS std_paint,
    AVG(text_count) AS ave_text,
    STDDEV(text_count) AS std_text,
    AVG(filter_count) AS ave_filter,
    STDDEV(filter_count) AS std_filter
FROM
    interaction_log AS log,
    paint_counts_cache AS paint_counts,
    text_counts_cache AS text_counts,
    filter_counts_cache AS filter_counts
WHERE
    log.log_num = paint_counts.log_num AND
    log.log_num = text_counts.log_num AND
    log.log_num = filter_counts.log_num
GROUP BY
    log.user_id
;

    
CREATE VIEW images_on_close AS
SELECT
    image.log_num,
    image.entry_num,
    image.image_id
FROM
    gimp_image AS image,
    document_event AS event,
    window_showhide_event AS doc_event
WHERE
    event.event_type_id = 9 AND -- dispose event
    image.log_num = event.log_num AND
    image.entry_num = event.entry_num AND
    event.log_num = doc_event.log_num AND
    doc_event.image_id IS NOT NULL AND
    doc_event.image_id = image.image_id -- Make sure we only get doc events
GROUP BY
    image.log_num,
    image.entry_num, -- TODO: Verify correct behavior here
    image.image_id
;


-- Image sizes (on close)
-- Can also do this as a count per total number of commands
CREATE VIEW image_sizes_on_close AS
SELECT
    image.log_num,
    image.image_id,
    image.width,
    image.height
FROM
    gimp_image AS image,
    images_on_close_cache AS images_on_close
WHERE
    image.image_id = images_on_close.image_id AND
    image.log_num = images_on_close.log_num AND
    image.entry_num = images_on_close.entry_num
ORDER BY
    image.width,
    image.height
;


CREATE VIEW image_size_stats AS
SELECT
    AVG(width) AS ave_image_width,
    AVG(height) AS ave_image_height,
    STDDEV(width) AS std_image_width,
    STDDEV(height) AS std_image_height
FROM
    image_sizes_on_close_cache
;


CREATE VIEW per_user_image_size_stats AS
SELECT
    log.user_id,
    AVG(width) AS ave_image_width,
    AVG(height) AS ave_image_height,
    STDDEV(width) AS std_image_width,
    STDDEV(height) AS std_image_height
FROM
    image_sizes_on_close_cache AS images,
    interaction_log AS log
WHERE
    log.log_num = images.log_num
GROUP BY
    log.user_id
;


-- Layer counts
CREATE VIEW image_layer_counts AS
SELECT
    images_on_close.log_num,
    images_on_close.entry_num,
    images_on_close.image_id,
    COUNT(layer.layer_id) AS num_layers
FROM
    images_on_close_cache AS images_on_close,
    gimp_image AS image,
    gimp_layer AS layer
WHERE
    images_on_close.log_num = image.log_num AND
    images_on_close.entry_num = image.entry_num AND
    images_on_close.log_num = layer.log_num AND
    images_on_close.entry_num = layer.entry_num AND
    image.image_id = layer.image_id
GROUP BY
    images_on_close.log_num,
    images_on_close.entry_num,
    images_on_close.image_id
;


CREATE VIEW user_image_layer_counts AS
SELECT
    log.user_id,
    MIN(layer_counts.num_layers) AS min_num_layers,
    MAX(layer_counts.num_layers) AS max_num_layers,
    AVG(layer_counts.num_layers) AS ave_num_layers,
    STDDEV(layer_counts.num_layers) AS std_num_layers
FROM
    image_layer_counts AS layer_counts,
    interaction_log AS log
WHERE
    layer_counts.log_num = log.log_num
GROUP BY
    log.user_id
;


CREATE VIEW all_image_layer_counts AS
SELECT
    MIN(layer_counts.num_layers) AS min_num_layers,
    MAX(layer_counts.num_layers) AS max_num_layers,
    AVG(layer_counts.num_layers) AS ave_num_layers,
    STDDEV(layer_counts.num_layers) AS std_num_layers
FROM
    image_layer_counts AS layer_counts
;


-- Image lifetimes
CREATE VIEW image_lifetime AS
SELECT
    image.log_num,
    image.image_id,
    (((MAX(event.elapsed_time_usec) - MIN(event.elapsed_time_usec))/1000000) * interval '1 second') AS image_lifetime
FROM
    gimp_image as image,
    document_event as event
WHERE
    image.log_num = event.log_num AND
    image.entry_num = event.entry_num
GROUP BY
    image.log_num,
    image.image_id;



-- Session length
CREATE VIEW session_length AS
SELECT
    event.log_num,
    (MAX(event.elapsed_time_usec)/1000000) AS session_length,
    COUNT(event.entry_num) AS num_doc_events
FROM
    event_record AS event
GROUP BY
    event.log_num;


CREATE VIEW user_stats AS
SELECT
    log.user_id,
    COUNT(log.log_num) AS num_sessions,
    MIN(session_length.session_length) AS min_session_length,
    MAX(session_length.session_length) AS max_session_length,
    AVG(session_length.session_length) AS ave_session_length,
    STDDEV(session_length.session_length) AS std_session_length,
    SUM(session_length.num_doc_events) AS total_num_commands,
    MIN(session_length.num_doc_events) AS min_num_commands,
    MAX(session_length.num_doc_events) AS max_num_commands,
    AVG(session_length.num_doc_events) AS ave_num_commands,
    STDDEV(session_length.num_doc_events) AS std_num_commands,
    MIN(log_date) AS first_seen,
    MAX(log_date) AS last_seen
FROM
    interaction_log AS log,
    session_length
WHERE
    log.log_num = session_length.log_num
GROUP BY
    log.user_id
ORDER BY
    last_seen DESC,
    num_sessions DESC
; 


CREATE VIEW per_user_paint_tools AS
SELECT
    log.user_id,
    COUNT(entry_num) AS num_uses
FROM
    interaction_log AS log
    LEFT JOIN paint_tools_cache AS paint_tools ON (log.log_num = paint_tools.log_num)
GROUP BY
    log.user_id
;

    
CREATE VIEW per_user_text_tools AS
SELECT
    log.user_id,
    COUNT(entry_num) AS num_uses
FROM
    interaction_log AS log
    LEFT JOIN text_tools_cache AS text_tools ON (log.log_num = text_tools.log_num)
GROUP BY
    log.user_id
;

    
CREATE VIEW per_user_filter_tools AS
SELECT
    log.user_id,
    COUNT(entry_num) AS num_uses
FROM
    interaction_log AS log
    LEFT JOIN filter_tools_cache AS filter_tools ON (log.log_num = filter_tools.log_num)
GROUP BY
    log.user_id
;

    
CREATE VIEW monitor_sizes AS
SELECT
    log.user_id,
    monitor_width,
    monitor_height
FROM
    interaction_log AS log,
    monitor
WHERE
    log.log_num = monitor.log_num
GROUP BY
    log.user_id,
    monitor_width,
    monitor_height
;


CREATE VIEW monitor_stats AS
SELECT
    AVG(monitor_width) AS ave_monitor_width,
    AVG(monitor_height) AS ave_monitor_height,
    STDDEV(monitor_width) AS std_monitor_width,
    STDDEV(monitor_height) AS std_monitor_height
FROM
    monitor_sizes
;


CREATE VIEW user_list AS
SELECT
    DISTINCT user_id
FROM
    interaction_log
;


CREATE VIEW per_user_commands AS
SELECT
    command_name,
    user_id
FROM
    per_log_command_stats_cache AS commands,
    interaction_log AS log
WHERE
    commands.log_num = log.log_num
GROUP BY
    user_id, command_name
;


CREATE VIEW window_titles AS
SELECT
    COUNT(window_name) AS freq,
    window_name
FROM
    (SELECT
        user_id,
        window_name
     FROM
        interaction_log AS log,
        window_state_event AS window
     WHERE
        log.log_num = window.log_num
     GROUP BY
        log.user_id,
        window_name)
    AS t
WHERE
    window_name IS NOT NULL
GROUP BY
    window_name
ORDER BY
    freq DESC
;


-- Avatars --
CREATE VIEW avatar_entry_counts AS
SELECT
    undo_event.log_num,
    COUNT(undo_event.entry_num) AS num_events
FROM
    gimp_undo_event AS undo_event,
    document_event AS event
WHERE
    event.log_num = undo_event.log_num AND
    event.entry_num = undo_event.entry_num AND
    event.image_id IS NOT NULL AND
    event.image_id >= 0
GROUP BY
    undo_event.log_num
;


CREATE VIEW avatar_undo_command_type_id_counts AS
SELECT
    log.log_num,
    log.user_id,
    undo_event.undo_command_type_id,
    COUNT(undo_event.undo_command_type_id) / num_events AS percent_use,
    SQRT(COUNT(undo_event.undo_command_type_id)) AS sqrt_use
FROM
    interaction_log AS log,
    document_event AS event,
    gimp_undo_event AS undo_event,
    avatar_entry_counts
WHERE
    log.log_num = event.log_num AND
    log.log_num = undo_event.log_num AND
    log.log_num = avatar_entry_counts.log_num AND
    event.entry_num = undo_event.entry_num AND
    event.image_id IS NOT NULL AND
    event.image_id >= 0
GROUP BY
    log.log_num,
    log.user_id,
    undo_event.undo_command_type_id,
    num_events -- TODO: Verify works
;


CREATE VIEW avatar_command_name_counts AS
SELECT
    log.log_num,
    log.user_id,
    command_name,
    COUNT(command_name) / num_events AS percent_use,
    SQRT(COUNT(command_name)) AS sqrt_use
FROM
    interaction_log AS log,
    document_event AS event,
    command_names,
    avatar_entry_counts
WHERE
    log.log_num = event.log_num AND
    log.log_num = avatar_entry_counts.log_num AND
    event.command_id = command_names.command_id AND
    event.image_id IS NOT NULL AND
    event.image_id >= 0
GROUP BY
    log.log_num,
    log.user_id,
    command_name,
    num_events -- TODO: Verify works
;


CREATE VIEW avatar_undo_command_type_id_averages AS
SELECT
    log.user_id,
    undo_command_type_id,
    SUM(percent_use) / num_sessions AS avg_percent,
    SUM(sqrt_use) / num_sessions AS sqrt_use
FROM
    interaction_log AS log,
    avatar_undo_command_type_id_counts,
    user_stats
WHERE
    log.user_id = avatar_undo_command_type_id_counts.user_id AND
    log.user_id = user_stats.user_id
GROUP BY
    log.user_id,
    undo_command_type_id,
    num_sessions
;


CREATE VIEW avatar_command_name_averages AS
SELECT
    log.user_id,
    command_name,
    SUM(percent_use) / num_sessions AS avg_percent,
    SUM(sqrt_use) / num_sessions AS sqrt_use
FROM
    interaction_log AS log,
    avatar_command_name_counts,
    user_stats
WHERE
    log.user_id = avatar_command_name_counts.user_id AND
    log.user_id = user_stats.user_id
GROUP BY
    log.user_id,
    command_name,
    num_sessions
;


CREATE VIEW avatar_undo_command_type_id_percent_seen AS
SELECT
    log.user_id,
    undo_command_type_id,
    COUNT(undo_command_type_id) / num_sessions AS percent_seen
FROM
    interaction_log AS log,
    avatar_undo_command_type_id_counts,
    user_stats
WHERE
    log.log_num = avatar_undo_command_type_id_counts.log_num AND
    log.user_id = user_stats.user_id
GROUP BY
    log.user_id,
    undo_command_type_id,
    num_sessions
;


CREATE VIEW avatar_undo_command_name_percent_seen AS
SELECT
    log.user_id,
    command_name,
    COUNT(command_name) / num_sessions AS percent_seen
FROM
    interaction_log AS log,
    avatar_command_name_counts,
    user_stats
WHERE
    log.log_num = avatar_command_name_counts.log_num AND
    log.user_id = user_stats.user_id
GROUP BY
    log.user_id,
    command_name,
    num_sessions
;


CREATE VIEW avatar_command_id_summary AS
SELECT
    id_avgs.user_id,
    id_avgs.undo_command_type_id,
    avg_percent AS avg_command_use,
    sqrt_use AS sqrt_command_use,
    percent_seen AS percent_command_seen,
    SQRT(num_sessions) AS sqrt_num_user_sessions
FROM
    avatar_undo_command_type_id_averages AS id_avgs,
    avatar_undo_command_type_id_percent_seen AS id_seen,
    user_stats
WHERE
    id_avgs.user_id = id_seen.user_id AND
    id_avgs.user_id = user_stats.user_id AND
    id_avgs.undo_command_type_id = id_seen.undo_command_type_id
;


CREATE VIEW avatar_command_name_summary AS
SELECT
    name_avgs.user_id,
    name_avgs.command_name,
    avg_percent AS avg_command_use,
    sqrt_use AS sqrt_command_use,
    percent_seen AS percent_command_seen
FROM
    avatar_command_name_averages AS name_avgs,
    avatar_undo_command_name_percent_seen AS name_seen
WHERE
    name_avgs.user_id = name_seen.user_id AND
    name_avgs.command_name = name_seen.command_name
;


CREATE VIEW simple_avatar AS
SELECT
    log.user_id,
    AVG(image_sizes.width) AS ave_image_width,
    AVG(image_sizes.height) AS ave_image_height,
    (AVG(image_sizes.width) - ave_image_width) / std_image_width AS norm_image_width,
    (AVG(image_sizes.height) - ave_image_height) / std_image_height AS norm_image_height,
    ((paint_tools.num_uses / user_stats.num_sessions) - ave_paint) / std_paint AS norm_paint,
    ((text_tools.num_uses / user_stats.num_sessions) - ave_text) / std_text AS norm_text,
    ((filter_tools.num_uses / user_stats.num_sessions) - ave_filter) / std_filter AS norm_filter
FROM
    user_stats,
    image_sizes_on_close_cache AS image_sizes,
    image_size_stats,
    tool_stats_cache AS tool_stats,
    interaction_log AS log LEFT JOIN per_user_paint_tools AS paint_tools ON (log.user_id = paint_tools.user_id)
    LEFT JOIN per_user_filter_tools AS filter_tools ON (log.user_id = filter_tools.user_id)
    LEFT JOIN per_user_text_tools AS text_tools ON (log.user_id = text_tools.user_id)
WHERE
    log.user_id = user_stats.user_id AND
    log.log_num = image_sizes.log_num
GROUP BY
    log.user_id,
    image_sizes.width,
    image_sizes.height,
    image_size_stats.ave_image_width,
    image_size_stats.std_image_width,
    image_size_stats.ave_image_height,
    image_size_stats.std_image_height,
    paint_tools.num_uses,
    tool_stats.ave_paint,
    tool_stats.std_paint,
    text_tools.num_uses,
    tool_stats.ave_text,
    tool_stats.std_text,
    filter_tools.num_uses,
    tool_stats.ave_filter,
    tool_stats.std_filter,
    user_stats.num_sessions
;


CREATE VIEW heat_maps_data AS
SELECT
    log.log_num,
    user_id,
    EXTRACT(EPOCH FROM MIN(event_date)) AS start_time_epoch,
    EXTRACT(EPOCH FROM MAX(event_date)) AS end_time_epoch,
    MIN(event_date) AS start_date,
    MAX(event_date) AS end_date,
    timezone
FROM
    interaction_log AS log,
    event_record AS event
WHERE
    log.log_num = event.log_num
GROUP BY
    log.log_num,
    user_id,
    timezone
ORDER BY
    start_date,
    end_date,
    user_id
;


CREATE VIEW log_times AS
SELECT
    log_num,
    version,
    wrapper_version,
    user_id,
    log_date,
    timezone,
    locale,
    log_file_name,
    gimp_version,
    platform,
    platform_system,
    platform_release,
    platform_version,
    platform_machine,
    platform_processor,
    num_disabled_runs,
    log_header,
    session_tags,
    log_url,
    EXTRACT(HOUR FROM log_date) AS log_hour,
    EXTRACT(DOY FROM log_date) AS log_doy,
    EXTRACT(DOW FROM log_date) AS log_dow,
    EXTRACT(WEEK FROM log_date) AS log_week,
    EXTRACT(MONTH FROM log_date) AS log_month,
    EXTRACT(YEAR FROM log_date) AS log_year
FROM
    interaction_log
;


CREATE VIEW logs_per_day AS
SELECT
    AVG(logs_per_day) AS logs_per_day_mean,
    STDDEV(logs_per_day) AS logs_per_day_std
FROM
    (
        SELECT
            COUNT(log_num) AS logs_per_day
        FROM
            log_times,
            regular_users
        WHERE
            log_times.user_id = regular_users.user_id
        GROUP BY
            log_doy
    ) AS logs_per_day
WHERE
    logs_per_day > 0
;
Retrieved from "http://ingimp.org/statsjam/index.php/Ingimp_DB_views"
Ingimp DB views

From StatsJam

Views

Personal tools

Navigation

Search

Toolbox
