# 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
```sql
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,
  visit_count int(11) NOT NULL DEFAULT 0,
  page_count 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,
  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)
- **visit_count**: Visit number for this visitor
- **page_count**: Number of pages viewed 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

#### 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
```sql
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
```sql
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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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:

```sql
-- 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
```bash
# 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](data-migration.md).
