Database Tables

The Visitors module uses three main database tables to store analytics data. This document provides detailed information about each table's structure and relationships.

Table Overview

Core Tables

visitors_visit - Visitor session data (master table)
visitors_event - Individual page view events (detail table)
visitors_counter - Aggregated statistics (summary table)

Relationships

visitors_visit (1) ←→ (many) visitors_event
visitors_counter ← aggregates from ← visitors_visit/visitors_event

visitors_visit Table

Purpose: Stores visitor session information and metadata

Schema

CREATE TABLE visitors_visit (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  visitor_id varchar(32) NOT NULL,
  uid int(10) unsigned NOT NULL DEFAULT 0,
  localtime int(11) NOT NULL DEFAULT 0,
  returning int(11) NOT NULL DEFAULT 0,
  total_visits int(11) NOT NULL DEFAULT 0,
  total_page_views int(11) NOT NULL DEFAULT 0,
  total_events int(11) NOT NULL DEFAULT 0,
  entry int(11) NOT NULL DEFAULT 0,
  entry_time int(11) NOT NULL DEFAULT 0,
  exit int(11) NOT NULL DEFAULT 0,
  exit_time int(11) NOT NULL DEFAULT 0,
  total_time int(11) NOT NULL DEFAULT 0,
  time_since_first int(11) DEFAULT NULL,
  time_since_last int(11) DEFAULT NULL,
  bot int(11) NOT NULL DEFAULT 0,

  -- Configuration fields
  config_id varchar(32) NOT NULL DEFAULT '',
  config_resolution varchar(9) NOT NULL DEFAULT '',
  config_pdf int(11) NOT NULL DEFAULT 0,
  config_flash int(11) NOT NULL DEFAULT 0,
  config_java int(11) NOT NULL DEFAULT 0,
  config_quicktime int(11) NOT NULL DEFAULT 0,
  config_realplayer int(11) NOT NULL DEFAULT 0,
  config_windowsmedia int(11) NOT NULL DEFAULT 0,
  config_silverlight int(11) NOT NULL DEFAULT 0,
  config_cookie int(11) NOT NULL DEFAULT 0,
  config_browser_engine varchar(255) NOT NULL DEFAULT '',
  config_browser_name varchar(255) NOT NULL DEFAULT '',
  config_browser_version varchar(255) NOT NULL DEFAULT '',
  config_client_type varchar(255) NOT NULL DEFAULT '',
  config_device_brand varchar(255) NOT NULL DEFAULT '',
  config_device_model varchar(255) NOT NULL DEFAULT '',
  config_device_type varchar(255) NOT NULL DEFAULT '',
  config_os varchar(255) NOT NULL DEFAULT '',
  config_os_version varchar(255) NOT NULL DEFAULT '',

  -- Location fields
  location_browser_lang varchar(12) NOT NULL DEFAULT '',
  location_ip varchar(45) NOT NULL DEFAULT '',
  location_continent varchar(2) NOT NULL DEFAULT '',
  location_country varchar(2) NOT NULL DEFAULT '',
  location_region varchar(128) NOT NULL DEFAULT '',
  location_city varchar(128) NOT NULL DEFAULT '',
  location_latitude decimal(10,6) DEFAULT NULL,
  location_longitude decimal(10,6) DEFAULT NULL,

  PRIMARY KEY (id),
  KEY idx_visitor_id (visitor_id),
  KEY idx_uid (uid),
  KEY idx_localtime (localtime),
  KEY idx_location_country (location_country),
  KEY idx_device_type (config_device_type),
  KEY idx_browser_name (config_browser_name)
);

Field Descriptions

Core Session Fields

id: Unique visit identifier (primary key)
visitor_id: Unique visitor identifier (cookie-based)
uid: Drupal user ID (0 for anonymous)
localtime: Visit start timestamp
returning: Whether this is a return visit (0/1)
total_visits: Visit number for this visitor
total_page_views: Number of pages viewed in this visit
total_events: Number of Events in this visit

Session Timing

entry: Entry page ID
entry_time: Time spent on entry page
exit: Exit page ID
exit_time: Time spent on exit page
total_time: Total session duration
time_since_first: Seconds since the visitor's first visit
time_since_last: Seconds since the visitor's last visit

Browser Configuration

config_resolution: Screen resolution (e.g., "1920x1080")
config_pdf: PDF plugin support (0/1)
config_flash: Flash plugin support (0/1)
config_java: Java support (0/1)
config_cookie: Cookie support (0/1)
config_browser_*: Browser information
config_device_*: Device information
config_os: Operating system information

Geographic Data

location_ip: IP address
location_continent: Continent code (2 letters)
location_country: Country code (ISO 2 letters)
location_region: State/province name
location_city: City name
location_latitude/longitude: Geographic coordinates

visitors_event Table

Purpose: Stores individual page view events and interactions

Schema

CREATE TABLE visitors_event (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  visit_id int(10) unsigned NOT NULL,
  page_view varchar(255) NOT NULL DEFAULT '',
  plugin varchar(64) NOT NULL DEFAULT '',
  event varchar(64) NOT NULL DEFAULT '',
  plugin_int_1 int(11) NOT NULL DEFAULT 0,
  plugin_int_2 int(11) NOT NULL DEFAULT 0,
  plugin_var_1 varchar(255) NOT NULL DEFAULT '',
  plugin_var_2 varchar(255) NOT NULL DEFAULT '',
  plugin_var_3 varchar(255) NOT NULL DEFAULT '',
  plugin_var_4 varchar(255) NOT NULL DEFAULT '',
  title text NOT NULL,
  uid int(10) unsigned NOT NULL DEFAULT 0,
  url_prefix int(11) NOT NULL DEFAULT 0,
  url text NOT NULL,
  path varchar(255) NOT NULL DEFAULT '',
  route varchar(255) NOT NULL DEFAULT '',
  referrer_url text NOT NULL,
  server varchar(255) NOT NULL DEFAULT '',

  -- Performance timing fields
  pf_network int(11) NOT NULL DEFAULT 0,
  pf_server int(11) NOT NULL DEFAULT 0,
  pf_transfer int(11) NOT NULL DEFAULT 0,
  pf_dom_processing int(11) NOT NULL DEFAULT 0,
  pf_dom_complete int(11) NOT NULL DEFAULT 0,
  pf_on_load int(11) NOT NULL DEFAULT 0,
  pf_total int(11) NOT NULL DEFAULT 0,

  created int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (id),
  KEY idx_visit_id (visit_id),
  KEY idx_created (created),
  KEY idx_url (url(255)),
  KEY idx_path (path),
  KEY idx_route (route),
  KEY idx_uid (uid),
  FOREIGN KEY (visit_id) REFERENCES visitors_visit(id) ON DELETE CASCADE
);

Field Descriptions

Core Event Fields

id: Unique event identifier
visit_id: Foreign key to visitors_visit table
page_view: Page view identifier
plugin: Event plugin name
event: Event type
created: Event timestamp

Page Information

title: Page title
url: Full page URL
path: Drupal path
route: Drupal route name
referrer_url: Referring page URL
server: Server name

Plugin Data

plugin_int_1/2: Integer plugin data
plugin_var_1/2/3/4: String plugin data

Performance Timing

pf_network: Network connection time (ms)
pf_server: Server response time (ms)
pf_transfer: Data transfer time (ms)
pf_dom_processing: DOM processing time (ms)
pf_dom_complete: DOM complete time (ms)
pf_on_load: Page load event time (ms)
pf_total: Total page load time (ms)

visitors_counter Table

Purpose: Stores aggregated visit counters for entities

Schema

CREATE TABLE visitors_counter (
  entity_id int(10) unsigned NOT NULL,
  entity_type varchar(32) NOT NULL,
  total int(10) unsigned NOT NULL DEFAULT 0,
  today int(10) unsigned NOT NULL DEFAULT 0,
  timestamp int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (entity_id, entity_type),
  KEY idx_total (total),
  KEY idx_today (today),
  KEY idx_timestamp (timestamp),
  KEY idx_entity_type (entity_type)
);

Field Descriptions

entity_id: Drupal entity ID
entity_type: Drupal entity type (node, user, etc.)
total: Total visit count for this entity
today: Today's visit count
timestamp: Last update timestamp

Data Relationships

Visit → Event Relationship

-- Get all events for a visit
SELECT e.* FROM visitors_event e
JOIN visitors_visit v ON e.visit_id = v.id
WHERE v.visitor_id = 'abc123';

-- Get visit summary with event count
SELECT v.*, COUNT(e.id) as event_count
FROM visitors_visit v
LEFT JOIN visitors_event e ON v.id = e.visit_id
GROUP BY v.id;

Counter Aggregation

-- Update counter from events
UPDATE visitors_counter SET
  total = (
    SELECT COUNT(*) FROM visitors_event
    WHERE url LIKE CONCAT('%node/', entity_id, '%')
  )
WHERE entity_type = 'node';

Indexing Strategy

Performance Indexes

-- Essential indexes for common queries
CREATE INDEX idx_visit_localtime_country ON visitors_visit (localtime, location_country);
CREATE INDEX idx_event_created_url ON visitors_event (created, url(255));
CREATE INDEX idx_event_visit_created ON visitors_event (visit_id, created);
CREATE INDEX idx_counter_type_total ON visitors_counter (entity_type, total DESC);

Composite Indexes

-- For geographic reports
CREATE INDEX idx_visit_location ON visitors_visit (location_continent, location_country, location_region);

-- For device analysis
CREATE INDEX idx_visit_device ON visitors_visit (config_device_type, config_browser_name);

-- For performance analysis
CREATE INDEX idx_event_performance ON visitors_event (created, pf_total, url(100));

Data Maintenance

Cleanup Queries

-- Remove old visit data (older than 1 year)
DELETE FROM visitors_visit WHERE localtime < (UNIX_TIMESTAMP() - 31536000);

-- Remove orphaned events
DELETE e FROM visitors_event e
LEFT JOIN visitors_visit v ON e.visit_id = v.id
WHERE v.id IS NULL;

-- Reset daily counters
UPDATE visitors_counter SET today = 0, timestamp = UNIX_TIMESTAMP();

Optimization Queries

-- Analyze table usage
ANALYZE TABLE visitors_visit, visitors_event, visitors_counter;

-- Optimize tables
OPTIMIZE TABLE visitors_visit, visitors_event, visitors_counter;

-- Check table sizes
SELECT
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
  table_rows
FROM information_schema.tables
WHERE table_name LIKE 'visitors_%';

Storage Considerations

Data Growth Estimates

Low traffic (1,000 visits/day): ~10MB/month
Medium traffic (10,000 visits/day): ~100MB/month
High traffic (100,000 visits/day): ~1GB/month

Partitioning Strategy

For high-traffic sites, consider partitioning by date:

-- Partition visitors_event by month
ALTER TABLE visitors_event
PARTITION BY RANGE (created) (
  PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
  -- Add monthly partitions
);

Security Considerations

Data Protection

Personal data should be minimal
Regular cleanup of old data
Access control on database level

Backup Strategy

# Backup visitors tables
mysqldump -u user -p database_name \
  visitors_visit visitors_event visitors_counter \
  > visitors_backup.sql

# Restore from backup
mysql -u user -p database_name < visitors_backup.sql

For migration information, see Data Migration.