package de.tud.kom.socom.database;
import de.tud.kom.socom.GlobalConfig;
class QueryStrings implements GlobalConfig {
/**
* STATIC (ENUMERATION-LIKE) TABLES
*/
protected static final String CREATE_USER_STATES_TABLE = "CREATE TABLE userstates "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), UNIQUE(name));";
protected static final String CREATE_LOG_TYPES_TABLE = "CREATE TABLE userlogtypes "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), UNIQUE(name));";
protected static final String CREATE_SOCIAL_NETWORKS_TABLE = "CREATE TABLE socialnetworks "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), urllogin VARCHAR(255), " +
"urlprofile VARCHAR(255));";
protected static final String CREATE_INFLUENCE_TYPES_TABLE = "CREATE TABLE influencetypes "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), UNIQUE(name));";
protected static final String CREATE_GAME_GENRES_TABLE = "CREATE TABLE gamegenres "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), UNIQUE(name));";
protected static final String CREATE_CONTENT_TYPES_TABLE = "CREATE TABLE contenttypes "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), UNIQUE(name));";
protected static final String CREATE_DELETED_FLAGS_TABLE = "CREATE TABLE deletedflags "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, reason VARCHAR(255), UNIQUE(reason));";
protected static final String CREATE_REPORT_TYPE_TABLE = "CREATE TABLE reporttypes " +
"(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, type VARCHAR(255), UNIQUE(type));";
/**
* USER SPECIFIC TABLES
*/
protected static final String CREATE_USERS_TABLE = "CREATE TABLE users"
+ "(uid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), password VARCHAR(255),"
+ "currentstate BIGINT, currentgameinst BIGINT, visibility INTEGER, isadmin BOOLEAN, deleted INTEGER DEFAULT 0, "
+ "FOREIGN KEY (currentstate) REFERENCES userstates(id), "
+ "FOREIGN KEY (currentgameinst) REFERENCES gameinstances(id),"
+ "FOREIGN KEY (deleted) REFERENCES deletedflags(id));";
protected static final String CREATE_USER_NETWORK_ACCOUNTS = "CREATE TABLE usersnaccounts "
+ "(uid BIGINT, gameid BIGINT, snid BIGINT, username VARCHAR(255), token VARCHAR(255), "
+ "PRIMARY KEY(uid, snid, gameid), "
+ "FOREIGN KEY (uid) REFERENCES users(uid), "
+ "FOREIGN KEY (gameid) REFERENCES games(gameid), "
+ "FOREIGN KEY (snid) REFERENCES socialnetworks(id));";
protected static final String CREATE_USER_NETWORK_FRIENDS = "CREATE TABLE usersnfriends "
+ "(uid BIGINT, friendid BIGINT, "
+ "PRIMARY KEY(uid, friendid), "
+ "FOREIGN KEY (uid) REFERENCES users(uid), "
+ "FOREIGN KEY (friendid) REFERENCES users(uid));";
protected static final String CREATE_USER_PROGRESS_TABLE = "CREATE TABLE userprogress "
+ "(uid BIGINT, scnid BIGINT, playtime INTEGER, time TIMESTAMP, "
+ "PRIMARY KEY(uid, scnid, time), "
+ "FOREIGN KEY (uid) REFERENCES users(uid), "
+ "FOREIGN KEY (scnid) REFERENCES gamecontexts(id));";
protected static final String CREATE_USER_LOGS_TABLE = "CREATE TABLE userlogs "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, uid BIGINT, gameinstid BIGINT, typeid BIGINT, "
+ "content LONGVARCHAR, time TIMESTAMP, visibility INTEGER DEFAULT 0, deleted INTEGER DEFAULT 0, "
+ "FOREIGN KEY (uid) REFERENCES users(uid), "
+ "FOREIGN KEY (typeid) REFERENCES userlogtypes(id));";
protected static final String CREATE_USER_METADATA_TABLE = "CREATE TABLE userdata " +
"(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, uid BIGINT, " +
"updated TIMESTAMP, key VARCHAR(255), value VARCHAR(255), visibility INTEGER DEFAULT 0, deleted INTEGER DEFAULT 0, " +
"FOREIGN KEY (uid) REFERENCES users(uid)," +
"FOREIGN KEY (deleted) REFERENCES deletedflags(id));";
protected static final String CREATE_USER_GAMES_TABLE = "CREATE TABLE usergames "
+ "(uid BIGINT, gameinstanceid BIGINT, timestamp TIMESTAMP, " +
"FOREIGN KEY (uid) REFERENCES users(uid)," +
"FOREIGN KEY (gameinstanceid) REFERENCES gameinstances(id));";
/**
* GAME SPECIFIC TABLES
*/
protected static final String CREATE_GAMES_TABLE = "CREATE TABLE games "
+ "(gameid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), "
+ "genre BIGINT, password VARCHAR(255), "
+ "FOREIGN KEY (genre) REFERENCES gamegenres(id), "
+ "UNIQUE(name));";
protected static final String CREATE_GAMEINSTANCES_TABLE = "CREATE TABLE gameinstances "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, gameid bigint, "
+ "version VARCHAR(255), description LONGVARCHAR, image VARCHAR(255), hits INT, "
+ "FOREIGN KEY (gameid) REFERENCES games(gameid), "
+ "UNIQUE(gameid, version));";
protected static final String CREATE_GAME_CONTEXT_TABLE = "CREATE TABLE gamecontexts "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, externalid VARCHAR(255), gameinstid BIGINT, "
+ "name VARCHAR(255), description LONGVARCHAR, image VARCHAR(255), autogenerated BOOLEAN DEFAULT false, "
+ "FOREIGN KEY (gameinstid) REFERENCES gameinstances(id), "
+ "UNIQUE(externalid, gameinstid));";
protected static final String CREATE_GAME_CONTEXT_RELATIONS_TABLE = "CREATE TABLE gamecontextrelations "
+ "(parentid BIGINT, childid BIGINT, autogenerated BOOLEAN DEFAULT false, timesused BIGINT DEFAULT 0, "
+ "PRIMARY KEY(parentid, childid), "
+ "FOREIGN KEY (parentid) REFERENCES gamecontexts(id), "
+ "FOREIGN KEY (childid) REFERENCES gamecontexts(id));";
/**
* INFLUENCE SPECIFIC TABLES
*/
protected static final String CREATE_INFLUENCE_TABLE = "CREATE TABLE influence "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, externalid VARCHAR(255), gameinstid BIGINT, contextid BIGINT, ownerid BIGINT, "
+ "question VARCHAR(1024), timeout TIMESTAMP DEFAULT NULL, type BIGINT, allowfreeanswers BOOLEAN, minchoices INTEGER, "
+ "maxchoices INTEGER, maxdigits INTEGER, maxlines INTEGER, visibility INTEGER, freevotable BOOLEAN, maxbytes BIGINT DEFAULT -1, "
+ "attendees VARCHAR(255) DEFAULT '', template BOOLEAN DEFAULT false, " //attendees are stored with uid seperated by semicolons
+ "FOREIGN KEY (gameinstid) REFERENCES gameinstances(id), "
+ "FOREIGN KEY (contextid) REFERENCES gamecontexts(id), "
+ "FOREIGN KEY (ownerid) REFERENCES users(uid), "
+ "FOREIGN KEY (type) REFERENCES influencetypes(id))";
protected static final String CREATE_INFLUENCE_FREE_ANSWERS_TABLE = "CREATE TABLE influencefreeanswers"
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, influenceid BIGINT, "
+ "text VARCHAR(1024), ownerid BIGINT, answercount INTEGER, createdtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
+ "visibility INTEGER DEFAULT 2, deleted INTEGER DEFAULT 0, "
+ "FOREIGN KEY (influenceid) REFERENCES influence(id), "
+ "FOREIGN KEY (ownerid) REFERENCES users(uid), "
+ "FOREIGN KEY (deleted) REFERENCES deletedflags(id));";
protected static final String CREATE_INFLUENCE_PREDEFINED_ANSWERS_TABLE = "CREATE TABLE influencepredefinedanswers"
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, influenceid BIGINT, "
+ "text VARCHAR(1024), answercount INTEGER, deleted INTEGER DEFAULT 0, "
+ "FOREIGN KEY (influenceid) REFERENCES influence(id));";
/**
* CONTENT SPECIFIC TABLES
*/
protected static final String CREATE_GAME_CONTENT_TABLE = "CREATE TABLE gamecontent "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, owner BIGINT, contextid BIGINT, "
+ "title VARCHAR(255), description LONGVARCHAR, category INTEGER, metadata OTHER, content BLOB, type BIGINT, "
+ "secretident VARCHAR(255), visibility INTEGER, time TIMESTAMP, hits INTEGER DEFAULT 0, deleted INTEGER DEFAULT 0, "
+ "FOREIGN KEY (owner) REFERENCES users(uid), "
+ "FOREIGN KEY (contextid) REFERENCES gamecontexts(id), "
+ "FOREIGN KEY (type) REFERENCES contenttypes(id), "
+ "FOREIGN KEY (deleted) REFERENCES deletedflags(id));";
protected static final String CREATE_CONTENT_COMMENTS_TABLE = "CREATE TABLE contentcomments "
+ "(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, uid BIGINT, contentid BIGINT, text LONGVARCHAR, "
+ "time TIMESTAMP, deleted INTEGER DEFAULT 0, "
+ "FOREIGN KEY (uid) REFERENCES users(uid), "
+ "FOREIGN KEY (contentid) REFERENCES gamecontent(id), "
+ "FOREIGN KEY (deleted) REFERENCES deletedflags(id));";
protected static final String CREATE_CONTENT_RATINGS_TABLE = "CREATE TABLE contentratings "
+ "(uid BIGINT, contentid BIGINT, value DOUBLE, time TIMESTAMP, "
+ "PRIMARY KEY(uid, contentid), "
+ "FOREIGN KEY (uid) REFERENCES users(uid), "
+ "FOREIGN KEY (contentid) REFERENCES gamecontent(id));";
/**
* ACHIEVEMENT SPECIFIC TABLES
*/
protected static final String CREATE_ACHIEVEMENT_TABLE = "CREATE TABLE achievement "
+ "(achievementid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, gameid BIGINT, "
+ "categoryid BIGINT, name VARCHAR(255), image VARCHAR(255), description LONGVARCHAR, "
+ "FOREIGN KEY (categoryid) REFERENCES achievementcategory(categoryid), "
+ "FOREIGN KEY (gameid) REFERENCES games(gameid));";
protected static final String CREATE_ACHIEVEMENT_CATEGORY_TABLE = "CREATE TABLE achievementcategory "
+ "(categoryid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
+ "gameid BIGINT, name VARCHAR(255), "
+ "FOREIGN KEY (gameid) REFERENCES games(gameid));";
protected static final String CREATE_ACHIEVEMENTLEVEL_TABLE = "CREATE TABLE achievementlevel "
+ "(levelid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, achievementid BIGINT, "
+ "level INTEGER, countermax BIGINT, rewardpoints INTEGER, "
+ "FOREIGN KEY (achievementid) REFERENCES achievement(achievementid));";
protected static final String CREATE_ACHIEVEMENT_REWARD_TABLE = "CREATE TABLE achievementreward "
+ "(rewardid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), "
+ "description LONGVARCHAR, value BIGINT);";
protected static final String CREATE_ACHIEVEMENT_REWARD_RELATIONS_TABLE = "CREATE TABLE achievementrewardrelations "
+ "(levelid BIGINT, rewardid BIGINT, "
+ "PRIMARY KEY(levelid, rewardid), "
+ "FOREIGN KEY (levelid) REFERENCES achievementlevel(levelid), "
+ "FOREIGN KEY (rewardid) REFERENCES achievementreward(rewardid));";
protected static final String CREATE_ACHIEVEMENT_PROGRESS_TABLE = "CREATE TABLE achievementprogress "
+ "(userid BIGINT, achievementid BIGINT, timecompleted TIMESTAMP, counter BIGINT, isCompleted BOOLEAN, "
+ "PRIMARY KEY(userid, achievementid), "
+ "FOREIGN KEY (userid) REFERENCES users(uid), "
+ "FOREIGN KEY (achievementid) REFERENCES achievement(achievementid));";
/**
* SOCIAL NETWORK TABLES
*/
protected static final String CREATE_SN_APPS_TABLE = "CREATE TABLE socialnetworkapps " +
"(gameid BIGINT, sn BIGINT, appid BIGINT, appsecret VARCHAR(255), token_redirect_url VARCHAR(255), " +
"general_redirect_url VARCHAR(255)," +
"FOREIGN KEY (gameid) REFERENCES games(gameid), " +
"FOREIGN KEY (sn) REFERENCES socialnetworks(id));";
protected static final String CREATE_SOCIAL_NETWORK_PAGES_TABLE = "CREATE TABLE socialnetworkpages " +
"(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, snid BIGINT, uid BIGINT, " +
"gameinstanceid BIGINT, pageidentifier VARCHAR(255), token VARCHAR(255), " +
"FOREIGN KEY (snid) REFERENCES socialnetworks(id), " +
"FOREIGN KEY (gameinstanceid) REFERENCES gameinstances(id), FOREIGN KEY (uid) REFERENCES users(uid))";
/**
* REPORT TABLES
*/
protected static final String CREATE_REPORT_TABLE = "CREATE TABLE reports " +
"(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, type BIGINT, " +
"reference BIGINT, reference2 VARCHAR(255) DEFAULT NULL, report LONGVARCHAR, " +
"informant BIGINT, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
"reviewed BOOLEAN DEFAULT false, reviewedby BIGINT DEFAULT NULL, " +
"review LONGVARCHAR DEFAULT NULL, reviewedon TIMESTAMP DEFAULT NULL, " +
"FOREIGN KEY (type) REFERENCES reporttypes(id), " +
"FOREIGN KEY (informant) REFERENCES users(uid), " +
"FOREIGN KEY (reviewedby) REFERENCES users(uid));";
/**
* ALL CREATE QUERYS
*/
protected static final String[] allCreateQuerys = new String[] {
CREATE_USER_STATES_TABLE, CREATE_LOG_TYPES_TABLE,
CREATE_SOCIAL_NETWORKS_TABLE, CREATE_INFLUENCE_TYPES_TABLE,
CREATE_GAME_GENRES_TABLE, CREATE_CONTENT_TYPES_TABLE,
CREATE_DELETED_FLAGS_TABLE, CREATE_REPORT_TYPE_TABLE,
CREATE_GAMES_TABLE, CREATE_GAMEINSTANCES_TABLE, CREATE_GAME_CONTEXT_TABLE,
CREATE_GAME_CONTEXT_RELATIONS_TABLE, CREATE_USERS_TABLE,
CREATE_USER_LOGS_TABLE, CREATE_USER_METADATA_TABLE,
CREATE_USER_PROGRESS_TABLE,
CREATE_USER_NETWORK_ACCOUNTS, CREATE_USER_NETWORK_FRIENDS,
CREATE_USER_GAMES_TABLE,
CREATE_GAME_CONTENT_TABLE, CREATE_CONTENT_COMMENTS_TABLE,
CREATE_CONTENT_RATINGS_TABLE, CREATE_INFLUENCE_TABLE,
CREATE_INFLUENCE_FREE_ANSWERS_TABLE, CREATE_INFLUENCE_PREDEFINED_ANSWERS_TABLE,
CREATE_ACHIEVEMENT_CATEGORY_TABLE, CREATE_ACHIEVEMENT_TABLE,
CREATE_ACHIEVEMENTLEVEL_TABLE, CREATE_ACHIEVEMENT_REWARD_TABLE,
CREATE_ACHIEVEMENT_REWARD_RELATIONS_TABLE, CREATE_ACHIEVEMENT_PROGRESS_TABLE,
CREATE_SN_APPS_TABLE, CREATE_SOCIAL_NETWORK_PAGES_TABLE, CREATE_REPORT_TABLE
};
/**
* Insert predefined values
*/
protected static final String INSERT_USER_STATES_TABLE_OFFLINE = "INSERT INTO userstates (id, name) OVERRIDING SYSTEM VALUE VALUES (" + USERSTATE_OFFLINE + ", 'Offline')";
protected static final String INSERT_USER_STATES_TABLE_ONLINE = "INSERT INTO userstates (id, name) OVERRIDING SYSTEM VALUE VALUES (" + USERSTATE_ONLINE + ", 'Online')";
protected static final String INSERT_USER_STATES_TABLE_PLAYING = "INSERT INTO userstates (id, name) OVERRIDING SYSTEM VALUE VALUES (" + USERSTATE_PLAYING + ", 'Spielt')";
protected static final String INSERT_GAME_GENRES_TABLE_ACTION = "INSERT INTO gamegenres (name) VALUES ('Action')";
protected static final String INSERT_GAME_GENRES_TABLE_ACTION_ADVENTURE = "INSERT INTO gamegenres (name) VALUES ('Action-Adventure')";
protected static final String INSERT_GAME_GENRES_TABLE_ADVENTURE = "INSERT INTO gamegenres (name) VALUES ('Adventure')";
protected static final String INSERT_GAME_GENRES_TABLE_ROLE_PLAYING = "INSERT INTO gamegenres (name) VALUES ('Role-Playing')";
protected static final String INSERT_GAME_GENRES_TABLE_SIMULATION = "INSERT INTO gamegenres (name) VALUES ('Simulation')";
protected static final String INSERT_GAME_GENRES_TABLE_SPORTS = "INSERT INTO gamegenres (name) VALUES ('Sports')";
protected static final String INSERT_GAME_GENRES_TABLE_STRATEGY = "INSERT INTO gamegenres (name) VALUES ('Strategy')";
protected static final String INSERT_LOG_TYPES_TABLE_INFO = "INSERT INTO userlogtypes (name) VALUES ('INFO')";
protected static final String INSERT_LOG_TYPES_TABLE_ERROR = "INSERT INTO userlogtypes (name) VALUES ('ERROR')";
protected static final String INSERT_LOG_TYPES_TABLE_FINISHLEVEL = "INSERT INTO userlogtypes (name) VALUES ('FINISH_LEVEL')";
protected static final String INSERT_LOG_TYPES_TABLE_HELPREQUESTED = "INSERT INTO userlogtypes (name) VALUES ('HELP_REQUEST')";
protected static final String INSERT_LOG_TYPES_TABLE_OTHER = "INSERT INTO userlogtypes (name) VALUES ('OTHER')";
protected static final String INSERT_SOCIAL_NETWORKS_FACEBOOK = "INSERT INTO socialnetworks (name, urllogin, urlprofile) VALUES ('" + SOCIALNETWORK_FACEBOOK + "', '???', '???')"; // TODO: right urls
protected static final String INSERT_SOCIAL_NETWORKS_GOOGLEPLUS = "INSERT INTO socialnetworks (name, urllogin, urlprofile) VALUES ('" + SOCIALNETWORK_GOOGLEPLUS + "', '???', '???')"; // TODO: right urls
protected static final String INSERT_INFLUENCE_TYPES_TABLE_TEXT = "INSERT INTO influencetypes (name) VALUES ('text')";
protected static final String INSERT_INFLUENCE_TYPES_TABLE_IMAGE = "INSERT INTO influencetypes (name) VALUES ('image')";
protected static final String INSERT_INFLUENCE_TYPES_TABLE_AUDIO = "INSERT INTO influencetypes (name) VALUES ('audio')";
protected static final String INSERT_DELETED_FLAGS_VISIBLE = "INSERT INTO deletedflags (reason) VALUES ('visible')";
protected static final String INSERT_DELETED_FLAGS_HIDE = "INSERT INTO deletedflags (reason) VALUES ('hide_only')";
protected static final String INSERT_DELETED_FLAGS_ABUSE = "INSERT INTO deletedflags (reason) VALUES ('abuse')";
protected static final String INSERT_DELETED_FLAGS_OFFENSE = "INSERT INTO deletedflags (reason) VALUES ('offense')";
protected static final String INSERT_REPORT_TYPE_USER = "INSERT INTO reporttypes (type) VALUES ('user')";
protected static final String INSERT_REPORT_TYPE_CONTENT = "INSERT INTO reporttypes (type) VALUES ('content')";
protected static final String INSERT_REPORT_TYPE_CONTENT_COMMENT = "INSERT INTO reporttypes (type) VALUES ('contentcomment')";
protected static final String INSERT_REPORT_TYPE_INFLUENCE_ANSWER = "INSERT INTO reporttypes (type) VALUES ('influenceanswer')";
protected static final String[] allInsertQuerys = new String[] {
INSERT_USER_STATES_TABLE_OFFLINE,
INSERT_USER_STATES_TABLE_ONLINE,
INSERT_USER_STATES_TABLE_PLAYING,
INSERT_GAME_GENRES_TABLE_ACTION,
INSERT_GAME_GENRES_TABLE_ACTION_ADVENTURE,
INSERT_GAME_GENRES_TABLE_ADVENTURE,
INSERT_GAME_GENRES_TABLE_ROLE_PLAYING,
INSERT_GAME_GENRES_TABLE_SIMULATION,
INSERT_GAME_GENRES_TABLE_SPORTS,
INSERT_GAME_GENRES_TABLE_STRATEGY,
INSERT_LOG_TYPES_TABLE_INFO,
INSERT_LOG_TYPES_TABLE_ERROR,
INSERT_LOG_TYPES_TABLE_FINISHLEVEL,
INSERT_LOG_TYPES_TABLE_HELPREQUESTED,
INSERT_LOG_TYPES_TABLE_OTHER,
INSERT_SOCIAL_NETWORKS_FACEBOOK,
INSERT_SOCIAL_NETWORKS_GOOGLEPLUS,
INSERT_INFLUENCE_TYPES_TABLE_TEXT,
INSERT_INFLUENCE_TYPES_TABLE_IMAGE,
INSERT_INFLUENCE_TYPES_TABLE_AUDIO,
INSERT_DELETED_FLAGS_VISIBLE,
INSERT_DELETED_FLAGS_HIDE,
INSERT_DELETED_FLAGS_ABUSE,
INSERT_DELETED_FLAGS_OFFENSE,
INSERT_REPORT_TYPE_USER,
INSERT_REPORT_TYPE_CONTENT,
INSERT_REPORT_TYPE_CONTENT_COMMENT,
INSERT_REPORT_TYPE_INFLUENCE_ANSWER,
};
}