[SQL]
#Core
CheckUser=SELECT userid,nickname,realname,realm,email FROM users WHERE email=? AND password=SHA1(?)
CheckUserOld=SELECT userid,nickname,realname,realm,email FROM users WHERE email=? AND password=OLD_PASSWORD(?)
AllRealms=SELECT * FROM realms
GetRealmByID=SELECT * FROM realms WHERE realmid=?
GetRealmByName=SELECT * FROM realms WHERE realm=?
#
# Session Management
#
CreateSession=INSERT INTO sessions (timeout,name,userid,realm,folderid,langcode,optionid,labyrinth) VALUES (?,?,?,?,1,?,?,?)
UpdateSession=UPDATE sessions SET $field=? WHERE labyrinth=?
UpdateSessionX=UPDATE sessions SET timeout=?,name=?,userid=?,realm=?,langcode=?,optionid=? WHERE labyrinth=?
CheckSession=SELECT userid,name,realm,folderid,langcode,optionid FROM sessions WHERE labyrinth=?
SaveSession=UPDATE sessions SET timeout=?,name=?,userid=?,realm=?,optionid=? WHERE labyrinth=?
TimeStampSession=UPDATE sessions SET timeout=? WHERE labyrinth=?
DeleteSessions=DELETE FROM sessions WHERE timeout < ?
DeleteSession=DELETE FROM sessions WHERE labyrinth=?
CountSessions=SELECT DISTINCT s.userid,s.name as realname FROM sessions AS s \
LEFT JOIN users AS u ON u.userid=s.userid
RetrieveSession=SELECT query FROM sessions WHERE labyrinth=?
StoreSession=UPDATE sessions SET query=? WHERE labyrinth=?
#TODO:
#GetLang
#SetLangUser
#SetLangSession
#
# Folders
#
AllFolders=SELECT f.*,a.accessname,f2.path as parentname,f.path as foldername FROM folders f \
INNER JOIN access a ON a.accessid=f.accessid \
LEFT JOIN folders f2 ON f2.folderid=f.parent \
ORDER BY foldername
GetFolder=SELECT * FROM folders WHERE folderid=?
GetFolderByPath=SELECT * FROM folders WHERE path=?
GetFolderAccess=SELECT folderid FROM acls WHERE groupid IN ($groups) OR userid=$userid) AND accessid >= $access
InsertFolder=INSERT INTO folders SET path=?,accessid=?,parent=?
UpdateFolder=UPDATE folders SET path=?,accessid=?,parent=? WHERE folderid=?
DeleteFolder=DELETE FROM folders WHERE folderid IN ($ids)
#
# Access Permissions
#
AllAccess=SELECT * FROM access WHERE accessid <= ? ORDER BY accessid
GetPermission=SELECT a.folderid,a.groupid,a.userid,a.accessid \
FROM acls AS a \
INNER JOIN folders AS f ON a.folderid=f.folderid \
WHERE a.folderid IN ($folders) AND (a.groupid IN ($groups) \
OR a.userid = $user) \
ORDER BY f.parent
UserACLs=SELECT l.*,f.path,a.accessname,b.accessname accesspath FROM acls AS l \
INNER JOIN access AS a ON a.accessid=l.accessid \
INNER JOIN folders AS f ON f.folderid=l.folderid \
INNER JOIN access AS b ON b.accessid=f.accessid \
WHERE l.userid=? ORDER BY f.path
UserACLCheck=SELECT * FROM acls WHERE userid=? AND folderid=?
UserACLInsert=INSERT INTO acls (accessid,userid,folderid) VALUES (?,?,?)
UserACLUpdate=UPDATE acls SET accessid=? WHERE userid=? AND folderid=?
UserACLDelete=DELETE FROM acls WHERE userid=? AND accessid=? AND folderid=?
GroupACLs=SELECT * FROM acls AS l \
INNER JOIN access AS a ON a.accessid=l.accessid \
INNER JOIN groups AS g ON g.groupid=l.groupid \
WHERE l.groupid=?
GroupACLSave=INSERT INTO acls (groupid,accessid,folderid) VALUES (?,?,?)
GroupACLDelete=DELETE FROM acls WHERE groupid=? AND accessid=? AND folderid=?
#
# Groups
#
GetGroupUserMap=SELECT groupid FROM ixusergroup WHERE type=1 AND linkid=?
GetGroupParents=SELECT groupid FROM ixusergroup WHERE type=2 AND linkid \
IN ($groups)
AllGroups=SELECT * FROM groups $where ORDER BY groupname
GroupCount=SELECT COUNT(linkid) AS count FROM ixusergroup WHERE groupid = ? AND type=1 GROUP BY groupid
GetGroup=SELECT * FROM groups WHERE groupid=?
LinkUsers=SELECT i.*,u.email,u.realname \
FROM ixusergroup AS i \
INNER JOIN users AS u ON i.linkid=u.userid \
WHERE i.type=1 AND i.groupid=?
LinkedUsers=SELECT i.*,g.groupname FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.groupid=g.groupid \
WHERE i.type=1 AND i.linkid=?
LinkGroups=SELECT i.*,g.groupname FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.linkid=g.groupid \
WHERE i.type=2 AND i.groupid=?
AllGroupIndex=SELECT * FROM ixusergroup WHERE type=2
AddGroup=INSERT INTO groups (groupname,master) VALUES (?,0)
AddLinkIndex=INSERT INTO ixusergroup (type,linkid,groupid) VALUES (?,?,?)
SaveGroup=UPDATE groups SET groupname=? WHERE groupid=?
DeleteGroup=DELETE FROM groups WHERE groupid=?
DeleteGroupIndex=DELETE FROM ixusergroup WHERE groupid=?
DeleteLinkIndices=DELETE FROM ixusergroup WHERE linkid=? AND i.type=?
DeleteLinkIndex=DELETE FROM ixusergroup \
WHERE type=? AND linkid=? AND groupid=?
GetGroupID=SELECT groupid FROM groups WHERE groupname=?
UserGroups=SELECT i.*,g.groupname FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.groupid=g.groupid \
WHERE type=1 AND linkid=?
#
# Users
#
CountUsers=SELECT COUNT(*) FROM users WHERE confirmed=1
AllUsers=SELECT * FROM users AS u \
ORDER BY nickname,realname
SearchUserNames=SELECT * FROM users AS u \
WHERE (nickname LIKE ? OR realname LIKE ?) $where \
ORDER BY $order
SearchUsers=SELECT * FROM users AS u \
WHERE (nickname LIKE ? OR realname LIKE ?) $where \
ORDER BY $order
ListAllNames=SELECT * FROM users ORDER BY nickname,realname
FindUser=SELECT userid,nickname,realname FROM users WHERE email = ?
FindUsers=SELECT u.userid,u.realname,u.email FROM users AS u \
WHERE $where
GetEmail=SELECT email FROM users WHERE userid=?
GetUserByID=SELECT * FROM users WHERE userid=?
UserName=SELECT u.userid,u.nickname,u.realname \
FROM users AS u \
WHERE u.userid=?
NewUser=INSERT INTO users (password,nickname,realname,email,actuserid) VALUES (SHA1(?),?,?,?,?)
SaveUser=UPDATE users SET nickname=?,realname=?,email=? WHERE userid=?
AdminSaveUser=UPDATE users SET accessid=?,search=?,realm=?,nickname=?,realname=?,email=?,imageid=? WHERE userid=?
ConfirmUser=UPDATE users SET confirmed=? WHERE userid=?
MailUser=UPDATE users SET mailed=? WHERE userid=?
DeleteUser=DELETE FROM users WHERE userid IN ($del)
ValidUser=SELECT * FROM users WHERE userid=? AND password=SHA1(?)
ChangePassword=UPDATE users SET password=SHA1(?) WHERE userid=?
FindUserByAct=SELECT u.*,x.code FROM users u \
INNER JOIN ixsurvey AS x ON x.userid=u.userid \
WHERE u.actuserid=?
UpdateActUser=UPDATE users SET actuserid=? WHERE userid=?
DisableUser=UPDATE users SET search=0 WHERE actuserid=?
GetUserInfoByID=SELECT * FROM users WHERE userid=?
SaveUserInfo=UPDATE users SET $keys WHERE userid=?
BanUsers=UPDATE users SET password=? WHERE userid in ($ids)
#
# Images
#
AllImageStock=SELECT * FROM imagestock
GetImageByID=SELECT * FROM images WHERE imageid=?
AddImage=INSERT INTO images (tag,link,type,href,dimensions) VALUES (?,?,?,?,?)
SaveImage=UPDATE images SET tag=?,link=?,type=?,href=?,dimensions=? WHERE imageid=?
GetImagesByType=SELECT * FROM images WHERE type=? ORDER BY link,tag
GetDefaultImages=SELECT * FROM images WHERE tag='DEFAULT'
DeleteImage=DELETE FROM images WHERE imageid=?
GetImage=SELECT * FROM images WHERE imageid=?
MetaImages=SELECT i.* FROM images AS i \
INNER JOIN imetadata AS m ON i.imageid=m.imageid \
$where \
ORDER BY i.imageid
InsertMetaData=INSERT INTO imetadata (imageid,tag) VALUES (?,?)
DeleteMetaData=DELETE FROM imetadata WHERE imageid=?
SearchMetaData=SELECT * FROM images AS i \
INNER JOIN imetadata AS m ON i.imageid=m.imageid\
WHERE tag IN (?)
GetMetaData=SELECT * FROM imetadata WHERE imageid=?
#
# Surveys
#
GetUserCode=SELECT * FROM ixsurvey WHERE userid=?
SaveUserCode=INSERT INTO ixsurvey (code,userid) VALUES (?,?);
SurveyLogin=SELECT u.* FROM ixsurvey AS x \
INNER JOIN users AS u ON x.userid=u.userid \
WHERE x.code=?
SurveyCheck=SELECT * FROM ixsurvey WHERE userid=?
CheckCourse=SELECT * FROM ixcourse WHERE courseid=? AND userid=?
SaveSurvey=INSERT INTO survey (name,value,code) VALUES (?,?,?);
SaveSurveyIndex=UPDATE ixsurvey SET completed=? WHERE userid=?
SaveEvaluation=INSERT INTO evaluation (courseid,name,value,code) VALUES (?,?,?,?);
SaveEvaluationIndex=UPDATE ixcourse SET completed=? WHERE courseid=? AND userid=?
SaveTalkIndex=INSERT INTO ixcourse (completed,courseid,userid) VALUES (?,?,?)
CourseCheck=SELECT s.userid,x.completed,c.* FROM ixsurvey AS s \
INNER JOIN ixcourse AS x ON x.userid=s.userid \
INNER JOIN course AS c ON (c.courseid=x.courseid AND c.talk=0) \
WHERE x.courseid=? AND x.userid=?
TalkCheck=SELECT c.*,x.completed FROM course AS c \
LEFT JOIN ixcourse AS x ON (x.courseid=c.courseid AND x.userid=?) \
WHERE talk>0 AND c.courseid=?
ListCourses=SELECT * FROM ixcourse AS x \
INNER JOIN course AS c ON c.courseid=x.courseid AND c.talk=0 \
WHERE x.userid=? ORDER BY datetime
ListTalks=SELECT c.*,x.completed FROM course AS c \
LEFT JOIN ixcourse AS x ON (x.courseid=c.courseid AND x.userid=?) \
WHERE talk>0 ORDER BY talk,course,tutor
# result checks
SurveyResults=select * FROM survey
SurveyResponse=select x.userid,u.realname,x.completed FROM ixsurvey AS x \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE x.completed>0
NoSurvey=select x.userid,u.realname,x.code,u.confirmed,u.email FROM ixsurvey AS x \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE x.completed=0 $where
DoneSurvey=SELECT x.userid,u.realname,x.code,u.confirmed,u.email,x.completed FROM ixsurvey AS x \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE x.completed>0
UpdateSurveyCode=UPDATE survey SET code=? WHERE surveyid=?
ListSurvey=SELECT * FROM survey ORDER BY surveyid
SentSurvey=UPDATE ixsurvey SET sentdate=? WHERE userid=?
SentAnnounce=UPDATE users SET mailed=? WHERE userid=?
NoCourse=SELECT s.userid,u.realname,c.courseid,c.course,u.email,s.code FROM ixsurvey AS s \
INNER JOIN ixcourse AS x ON x.userid=s.userid \
INNER JOIN course AS c ON c.courseid=x.courseid \
INNER JOIN users AS u ON u.userid=s.userid \
WHERE x.completed=0
SurveyQuestionResults=SELECT value,count(value) FROM survey WHERE name=? GROUP BY value
SurveyFeedbackResults=SELECT value,1 FROM survey WHERE name=?
SurveyResponded=SELECT userid FROM ixsurvey WHERE completed>0
SurveyNotResponded=SELECT userid FROM ixsurvey WHERE completed=0
DoneTutorial=SELECT x.userid,u.realname,u.confirmed,u.email,x.completed FROM ixcourse AS x \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE x.completed>0
AllCourses=SELECT * FROM course
AllActiveCourses=SELECT * FROM course WHERE talk > -1
FindCourseByName=SELECT * FROM course WHERE course=?
FindCourse=SELECT * FROM course WHERE course=? AND tutor=?
GetCourse=SELECT * FROM course WHERE courseid=?
AddCourse=INSERT INTO course (course,tutor,room,datetime,talk) VALUES (?,?,?,?,?)
SaveCourse=UPDATE course SET course=?,tutor=?,room=?,datetime=?,talk=? WHERE courseid=?
InsertCourse=INSERT INTO course (course,tutor,room,datetime,talk,userid,actuserid) VALUES (?,?,?,?,?,?,?)
UpdateCourse=UPDATE course SET course=?,tutor=?,room=?,datetime=?,talk=?,userid=?,actuserid=? WHERE courseid=?
DisableTalk=UPDATE course SET talk=-1 WHERE acttalkid=?
FindCourseByAct=SELECT * FROM course WHERE acttalkid=?
UpdateActCourse=UPDATE course SET acttalkid=?,actuserid=? WHERE courseid=?
CourseQuestionResults=SELECT value,count(value) FROM evaluation WHERE courseid=? AND name=? GROUP BY value
CourseQuestionFeedback=SELECT value,code FROM evaluation WHERE courseid=? AND name=?
CourseQuestionTag=SELECT value FROM evaluation WHERE courseid=? AND name=? AND code=?
UpdateSurveyValue=UPDATE survey SET value=? WHERE surveyid=?
EvaluationResults=SELECT * FROM evaluation;
UpdateEvaluationValue=UPDATE evaluation SET value=? WHERE evalid=?
UpdateEvaluationCode=UPDATE evaluation SET code=? WHERE evalid=?
#
# Announcements
#
GetAnnounces=SELECT * FROM announce
GetAnnounceByID=SELECT * FROM announce WHERE announceid=?
AddAnnounce=INSERT INTO announce (hFrom, hSubject, body, publish) VALUES (?,?,?,?)
SaveAnnounce=UPDATE announce SET hFrom=?, hSubject=?, body=?, publish=? WHERE announceid=?
AnnounceSent=SELECT COUNT(DISTINCT userid) FROM ixannounce WHERE announceid=?
AnnounceNotSent=SELECT COUNT(DISTINCT userid) FROM users \
WHERE userid NOT IN (SELECT userid FROM ixannounce WHERE announceid=?) AND confirmed=1 AND search=1
ListAnnounceSent=SELECT DISTINCT u.* FROM users AS u \
LEFT JOIN ixannounce AS x ON x.userid=u.userid \
WHERE x.announceid=? AND u.search=1 $sort
ListAnnounceUnsent=SELECT u.*,x.code FROM users AS u \
INNER JOIN ixsurvey AS x ON x.userid=u.userid \
WHERE (u.userid) NOT IN (SELECT DISTINCT z.userid FROM users AS z \
LEFT JOIN ixannounce AS x ON x.userid=z.userid WHERE x.announceid=?) \
AND u.confirmed=1 AND u.search=1 $sort
InsertAnnounceIndex=INSERT INTO ixannounce (announceid,userid,sentdate) VALUES (?,?,?)
CountConfirmedUsers=SELECT COUNT(*) FROM users WHERE confirmed=1 AND search=1
ListConfirmedUsers=SELECT u.*,x.code FROM users AS u \
INNER JOIN ixsurvey AS x ON x.userid=u.userid \
WHERE u.confirmed=1 AND u.search=1
ListSelectedUsers=SELECT u.*,x.code FROM users AS u \
INNER JOIN ixsurvey AS x ON x.userid=u.userid \
WHERE u.confirmed=1 AND u.search=1 AND u.userid IN ($ids)
AdminSurveyNot=SELECT u.*,x.code FROM users AS u \
INNER JOIN ixsurvey AS x ON x.userid=u.userid \
WHERE u.confirmed=1 AND u.search=1 AND u.userid NOT IN (SELECT userid FROM ixsurvey WHERE completed>0) \
$sort
AdminSurveys=SELECT x.userid,u.realname,u.email,x.completed FROM ixsurvey AS x \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE x.completed>0 \
$sort
AdminCourses=SELECT c.*,x.completed,x.userid,u.realname,u.email FROM course AS c \
LEFT JOIN ixcourse AS x ON (x.courseid=c.courseid) \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE talk=0 AND x.completed > 0 \
ORDER BY u.realname,c.course
AdminTalks=SELECT c.*,x.completed,x.userid,u.realname,u.email FROM course AS c \
LEFT JOIN ixcourse AS x ON (x.courseid=c.courseid) \
INNER JOIN users AS u ON u.userid=x.userid \
WHERE talk>0 AND x.completed > 0 \
ORDER BY u.realname,c.course
AdminTalkList=SELECT * FROM course ORDER BY talk DESC,datetime,course
GetTalkByID=SELECT * FROM course WHERE courseid=?
SaveTalk=UPDATE course SET tutor=?,course=?,datetime=?,talk=? WHERE courseid=?