db.sql

DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS userCookies;
DROP TABLE IF EXISTS users;

CREATE TABLE sessions (
  sessionID INT,
  userHTTPCookie INT,
  datetime TIMESTAMP,
  IPAddress TEXT,
  userAgent TEXT,
  Referrer TEXT,
  isReturnUser BOOLEAN,
  landingPage TEXT,
  isMobile BOOLEAN,
  device TEXT,
  OS TEXT,
  OSVersion TEXT,
  browser TEXT,
  browserVersion TEXT,
  state TEXT,
  channel TEXT
);

CREATE TABLE events (
  eventID INT,
  userHTTPCookie INT,
  sessionID INT,
  datetime TIMESTAMP,
  pageURL TEXT,
  eventType TEXT,
  contentBlock TEXT,
  actionType TEXT,
  pageControl TEXT,
  userInputValue TEXT
);

CREATE TABLE userCookies (
  userHTTPCookie INT,
  userID INT
);

CREATE TABLE users (
  userID INT,
  name TEXT
);

INSERT INTO sessions (
    sessionID, userHTTPCookie, datetime, userAgent, IPAddress,
    Referrer, isReturnUser, landingPage, isMobile, device, OS,
    OSVersion, browser, browserVersion, state, channel)
  VALUES (
    -- Simple session 1.1001
    1, 1001, '2016-01-08', 'Mozilla/4.0 (MSIE 6.0; Windows NT 5.1)', '1.1.1.1',
    'https://www.google.com/', FALSE, '/', FALSE, 'Desktop', 'Windows',
    '5.1', 'Internet Explorer', '6.0', 'CA', 'searchOrganic'
  ), (
    -- Simple session 2.1002
    2, 1002, '2016-01-08', 'Mozilla/4.0 (MSIE 6.0; Windows NT 5.1)', '1.1.1.1',
    'https://www.google.com/', FALSE, '/', FALSE, 'Desktop', 'Windows',
    '5.1', 'Internet Explorer', '6.0', 'CA', 'searchOrganic'
  ), (
    -- Return session 3.1003
    3, 1003, '2016-01-09', 'Mozilla/4.0 (MSIE 6.0; Windows NT 5.1)', '1.1.1.1',
    'https://www.google.com/', FALSE, '/', FALSE, 'Desktop', 'Windows',
    '5.1', 'Internet Explorer', '6.0', 'CA', 'searchOrganic'
  ), (
    -- Return session 4.1003
    4, 1003, '2016-01-10', 'Mozilla/4.0 (MSIE 6.0; Windows NT 5.1)', '1.1.1.1',
    'https://www.google.com/', FALSE, '/', FALSE, 'Desktop', 'Windows',
    '5.1', 'Internet Explorer', '6.0', 'CA', 'searchOrganic'
  ), (
    -- Multi-device session 5.1004
    5, 1004, '2016-01-10', 'Mozilla/4.0 (MSIE 6.0; Windows NT 5.1)', '1.1.1.1',
    'https://www.google.com/', FALSE, '/', FALSE, 'Desktop', 'Windows',
    '5.1', 'Internet Explorer', '6.0', 'CA', 'searchOrganic'
  ), (
    -- Multi-device session 5.1005
    6, 1005, '2016-01-10', 'Mozilla/4.0 (MSIE 6.0; Windows NT 5.1)', '1.1.1.1',
    'https://www.google.com/', FALSE, '/', FALSE, 'Desktop', 'Windows',
    '5.1', 'Internet Explorer', '6.0', 'CA', 'searchOrganic'
  );