1. Adam Dutko
  2. Portfolio

Source

Portfolio / beacon / web / php / utilities / queries.php

<?php

    ############################################################################
    ## NOTE: I'm aware we can bind parameters, I chose not to but we are using
    ##       prepare to check for bad queries.
    ############################################################################

    ######################
    ##  ACCOUNT RELATED ##
    ######################
    // Developers
    // --> login
    // --> ip (can't be localhost!)
    // --> session
    // --> cookie
    // --> password
    // --> id
    // --> updated
    // --> created

    #######
    ## CREATION
    #######
    $defaultCreateUserPartOne = "INSERT INTO developers SET login = '";
    $defaultCreateUserPartTwo = "', password = password('";
    $defaultCreateUserPartThree = "'), firstname = '";
    $defaultCreateUserPartFour = "', lastname = '";
    $defaultCreateUserPartFive = "', created = NOW(), ip = ";

    #######
    ## IDENTIFICATION
    #######
    $defaultCheckUserExistsLogin = "SELECT verified, id as cnt FROM developers WHERE login = '";
    ### PASSWORD PIECE
    $defaultCheckUserExistsPassword = "' and password = password('";
    ### SALT ANOTHER TIME
    $defaultCheckUserExistsSalt = "')";


    #######
    ## GET ACCOUNT INFORMATION
    #######
    $retrieveUserDataBeginOfQuery = "SELECT firstname as fname, lastname as lname, login FROM developers where login = '";
    $retrieveUserDataEndOfQuery = "'";
  
    $getUserIdBegin = "SELECT id from developers where login = '";
    $getUserIdEnd = "'";

    #######
    ## UPDATE ACCOUNT INFORMATION
    #######
    $updateAccountFname = "UPDATE developers set updated = now(), firstname ='";
    $updateAccountLname = "', lastname ='";
    $updateAccountPass = "', password = password('";
    $updateWhereClause = "' where login='";

    ######################
    ##  PROJECT RELATED ##
    ######################

    #######
    ## RETRIEVE PROJECTS
    #######
    $getUsersProjectsBegin = "SELECT id, name, owner, updated, created, type from projects where owner ='";
    $getUsersProjectsEnd = "'";

    #######
    ## CREATE PROJECT
    #######
    $insertProjectBegin = "INSERT into projects (created,owner,name,type) values (now(),'";
    $insertProjectEnd = "')";

    #######
    ## DELETE PROJECT
    #######
    $deleteProjectBegin = "DELETE from projects where owner='";
    $deleteProjectAnd = "' and id='"; 
    $deleteProjectEnd = "'";

    #######
    ## UPLOAD PROJECT FILE
    #######
    $insertFileProjectBegin = "INSERT into files (created,name,projectid,devid,module,path,revision) values (now(),'";
    $insertFileProjectEnd = "')";

    #######
    ## GET FILES FOR A SPECIFIC PROJECT
    #######
    $getFilesForProjectBegin = "SELECT name,revision from files where projectid = '";
    $getFilesForProjectEnd = "'";

    #######
    ## GET A SPECIFIC FILE FROM A PROJECT
    #######
    $getFileForProjectBegin = "SELECT name,module,path from files where projectid = '";
    $getFileForProjectEnd = "'";

    #######
    ## UNLINK FILES FROM A PROJECT
    #######
    $unlinkFilesForProjectBegin = "DELETE from files where projectid = '";
    $unlinkFilesForProjectRevision = "' and revision = '";
    $unlinkFilesForProjectDev = "' and devid = '";
    $unlinkFilesForProjectName = "' and name = '";
    $unlinkFilesForProjectEnd = "'";

    #######
    ## GET FILE COUNT
    #######
    $getFilesForProjectCountBegin = "SELECT count(id) as total from files where projectid ='";
    $getFilesForProjectCountEnd = "'";

    #######
    ## GET HIGHEST FILE REV
    #######
    $getFilesForProjectMaxRevBegin = "SELECT max(revision) as last from files where projectid ='";
    $getFilesForProjectMaxRevMod = "' and module = '";
    $getFilesForProjectMaxRevEnd = "'";

    #######
    ## UPDATE PROJECT TIMESTAMP
    #######
    $updateProjectUpdatedStart = "UPDATE projects set updated = now() where id = '";
    $updateProjectUpdatedEnd = "'";

    ######################
    ##  JOB RELATED ##
    ######################

    #######
    ## CREATE JOB ENTRY
    #######
    $createJobStatusStart = "INSERT into jobs (created,status,projectid,description,file,pid) values (now(),'";
    $createJobStatusProjectId = "','";
    $createJobStatusDesc = "','";
    $createJobStatusFile = "','";
    $createJobStatusPid = "','";
    $createJobStatusEnd = "')";

    #######
    ## UPDATE JOB ENTRY
    #######


    #######
    ## MONITOR JOB ENTRY
    #######
    $getCatalogJobStatusBegin = "SELECT status from jobs where projectid = '";
    $getCatalogJobStatusPID = "' and pid = '";
    $getCatalogJobStatusEnd = "';";


    #######
    ## ALL USER JOBS
    #######
    ## TODO: Need to add filter by user
    $getUserJobsBegin = "SELECT status,updated,description,created,projectid,file,id,pid from jobs";
    $getUserJobsEnd = "';";

?>