<commit_before>
create database if not exists instructables;

CREATE TABLE IF NOT EXISTS contests (
cid int unsigned NOT NULL AUTO_INCREMENT,
contestname VARCHAR(500),
contesturl VARCHAR(500),
contestends date,
contestentries int unsigned default 0,
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (cid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE IF NOT EXISTS posts (
pid int unsigned NOT NULL AUTO_INCREMENT,
posturl varchar(500),
title varchar(500),
author varchar(255), 
postimage varchar(500),
posted date,
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (pid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE IF NOT EXISTS stats (
sid int unsigned NOT NULL AUTO_INCREMENT,
pid int	unsigned NOT NULL,
views int unsigned default 0,
favs  int unsigned default 0,
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (sid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;



CREATE TABLE IF NOT EXISTS entries (
eid int unsigned NOT NULL AUTO_INCREMENT,
contestid int unsigned NOT NULL,
postid int unsigned NOT NULL,
dateadded DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (eid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
<commit_msg>changes to posts table to hold API key id as unique key<commit_after>
create database if not exists instructables;
CREATE USER 'instructables'@'localhost' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON instructables.* TO 'instructables'@'localhost' WITH GRANT OPTION;

CREATE TABLE IF NOT EXISTS contests (
cid int unsigned NOT NULL AUTO_INCREMENT,
contestname VARCHAR(500),
contesturl VARCHAR(500),
contestends date,
contestentries int unsigned default 0,
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (cid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE IF NOT EXISTS posts (
pid int unsigned NOT NULL AUTO_INCREMENT,
piid VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE Key,
posturl varchar(500),
title varchar(500),
author varchar(255),
postimage varchar(500),
posted date,
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (pid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE IF NOT EXISTS stats (
sid int unsigned NOT NULL AUTO_INCREMENT,
pid int unsigned NOT NULL,
views int unsigned default 0,
favs  int unsigned default 0,
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (sid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;



CREATE TABLE IF NOT EXISTS entries (
eid int unsigned NOT NULL AUTO_INCREMENT,
contestid int unsigned NOT NULL,
postid int unsigned NOT NULL,
dateadded DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (eid)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
{'fraction_non_alphanumeric': 0.053728427222403126, 'fraction_numerical': 0.020514490394008468, 'mean_word_length': 6.045871559633028, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2194983', 'n_tokens_mistral': 1111, 'n_tokens_neox': 985, 'n_words': 380}