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, }; }