/**
* @param array $uploadTreeArray
* @throws \Exception
*/
protected function prepareUploadTree($uploadTreeArray = array())
{
$this->dbManager->prepare($stmt = 'insert.uploadtree', "INSERT INTO uploadtree (uploadtree_pk, parent, upload_fk, pfile_fk, ufile_mode, lft, rgt, ufile_name, realparent) VALUES (\$1, \$2, \$3, \$4, \$5, \$6, \$7, \$8, \$9)");
foreach ($uploadTreeArray as $uploadTreeEntry) {
$this->dbManager->freeResult($this->dbManager->execute($stmt, $uploadTreeEntry));
}
}
/**
* @param int $uploadId
* @param Package $package
*/
public function addUploadToPackage($uploadId, Package $package)
{
$statementName = __METHOD__;
$this->dbManager->prepare($statementName, "INSERT INTO upload_packages (package_fk, upload_fk) VALUES(\$1, \$2)");
$res = $this->dbManager->execute($statementName, array($package->getId(), $uploadId));
$this->dbManager->freeResult($res);
}
public function rerunBulkAndDeciderOnUpload($uploadId, $groupId, $userId, $jobId)
{
$bulkIds = $this->getBulkIds($uploadId, $groupId, $userId);
if (count($bulkIds) == 0) {
return 0;
}
/* @var $uploadDao UploadDao */
$uploadDao = $GLOBALS['container']->get('dao.upload');
$topItem = $uploadDao->getUploadParent($uploadId);
/* @var $deciderPlugin DeciderJobAgentPlugin */
$deciderPlugin = plugin_find("agent_deciderjob");
$dependecies = array();
$sql = "INSERT INTO license_ref_bulk (user_fk,group_fk,rf_text,upload_fk,uploadtree_fk) " . "SELECT \$1 AS user_fk, \$2 AS group_fk,rf_text,\$3 AS upload_fk, \$4 as uploadtree_fk\n FROM license_ref_bulk WHERE lrb_pk=\$5 RETURNING lrb_pk, \$5 as lrb_origin";
$sqlLic = "INSERT INTO license_set_bulk (lrb_fk,rf_fk,removing) " . "SELECT \$1 as lrb_fk,rf_fk,removing FROM license_set_bulk WHERE lrb_fk=\$2";
$this->dbManager->prepare($stmt = __METHOD__ . 'cloneBulk', $sql);
$this->dbManager->prepare($stmtLic = __METHOD__ . 'cloneBulkLic', $sqlLic);
foreach ($bulkIds as $bulkId) {
$res = $this->dbManager->execute($stmt, array($userId, $groupId, $uploadId, $topItem, $bulkId));
$row = $this->dbManager->fetchArray($res);
$this->dbManager->freeResult($res);
$resLic = $this->dbManager->execute($stmtLic, array($row['lrb_pk'], $row['lrb_origin']));
$this->dbManager->freeResult($resLic);
$dependecies[] = array('name' => 'agent_monk_bulk', 'args' => $row['lrb_pk'], AgentPlugin::PRE_JOB_QUEUE => array('agent_decider'));
}
$errorMsg = '';
$jqId = $deciderPlugin->AgentAdd($jobId, $uploadId, $errorMsg, $dependecies);
if (!empty($errorMsg)) {
throw new Exception($errorMsg);
}
return $jqId;
}
public function hasActionPermissionsOnJob($jobId, $userId, $groupId)
{
$result = array();
$stmt = __METHOD__;
$this->dbManager->prepare($stmt, "SELECT *\n FROM job\n LEFT JOIN group_user_member gm\n ON gm.user_fk = job_user_fk\n WHERE job_pk = \$1\n AND (job_user_fk = \$2\n OR gm.group_fk = \$3)");
$res = $this->dbManager->execute($stmt, array($jobId, $userId, $groupId));
while ($row = $this->dbManager->fetchArray($res)) {
$result[$row['jq_pk']] = $row['end_bits'];
}
$this->dbManager->freeResult($res);
return $result;
}
public function getAllJobStatus($uploadId, $userId, $groupId)
{
$result = array();
$stmt = __METHOD__;
$this->dbManager->prepare($stmt, "SELECT jobqueue.jq_pk as jq_pk, jobqueue.jq_end_bits as end_bits\n FROM jobqueue INNER JOIN job ON jobqueue.jq_job_fk = job.job_pk\n LEFT JOIN group_user_member gm ON gm.user_fk = job_user_fk\n WHERE job.job_upload_fk = \$1 AND (job_user_fk = \$2 OR gm.group_fk = \$3)");
$res = $this->dbManager->execute($stmt, array($uploadId, $userId, $groupId));
while ($row = $this->dbManager->fetchArray($res)) {
$result[$row['jq_pk']] = $row['end_bits'];
}
$this->dbManager->freeResult($res);
return $result;
}
protected function setUp()
{
$this->testDb = new TestPgDb();
$this->dbManager =& $this->testDb->getDbManager();
$this->testDb->createPlainTables(array('upload', 'uploadtree'));
$this->dbManager->prepare($stmt = 'insert.upload', "INSERT INTO upload (upload_pk, uploadtree_tablename) VALUES (\$1, \$2)");
$uploadArray = array(array(1, 'uploadtree'), array(2, 'uploadtree_a'));
foreach ($uploadArray as $uploadEntry) {
$this->dbManager->freeResult($this->dbManager->execute($stmt, $uploadEntry));
}
$logger = M::mock('Monolog\\Logger');
// new Logger("UploadDaoTest");
$logger->shouldReceive('debug');
$this->uploadPermissionDao = new UploadPermissionDao($this->dbManager, $logger);
$this->assertCountBefore = \Hamcrest\MatcherAssert::getCount();
}
/**
* \brief Display the user record edit form
*
* \param $UserRec - Database users record for the user to be edited.
* \param $SessionIsAdmin - Boolean: This session is by an admin
* \return the text of the display form on success, or error on failure.
*/
private function DisplayForm($UserRec, $SessionIsAdmin)
{
$vars = array('isSessionAdmin' => $SessionIsAdmin, 'userId' => $UserRec['user_pk']);
/* For Admins, get the list of all users
* For non-admins, only show themself
*/
if ($SessionIsAdmin) {
$stmt = __METHOD__ . '.asSessionAdmin';
$sql = "SELECT * FROM users ORDER BY user_name";
$this->dbManager->prepare($stmt, $sql);
$res = $this->dbManager->execute($stmt);
$allUsers = array();
while ($row = $this->dbManager->fetchArray($res)) {
$allUsers[$row['user_pk']] = htmlentities($row['user_name']);
}
$this->dbManager->freeResult($res);
$vars['allUsers'] = $allUsers;
}
$vars['userName'] = $UserRec['user_name'];
$vars['userDescription'] = $UserRec['user_desc'];
$vars['userEMail'] = $UserRec["user_email"];
$vars['eMailNotification'] = $UserRec['email_notify'] == 'y';
if ($SessionIsAdmin) {
$vars['allAccessLevels'] = array(PLUGIN_DB_NONE => _("None (very basic, no database access)"), PLUGIN_DB_READ => _("Read-only (read, but no writes or downloads)"), PLUGIN_DB_WRITE => _("Read-Write (read, download, or edit information)"), PLUGIN_DB_ADMIN => _("Full Administrator (all access including adding and deleting users)"));
$vars['accessLevel'] = $UserRec['user_perm'];
$SelectedFolderPk = $UserRec['root_folder_fk'];
$vars['folderListOption'] = FolderListOption($ParentFolder = -1, $Depth = 0, $IncludeTop = 1, $SelectedFolderPk);
}
$vars['isBlankPassword'] = $UserRec['_blank_pass'] == 'on';
$vars['agentSelector'] = AgentCheckBoxMake(-1, array("agent_unpack", "agent_adj2nest", "wget_agent"), $UserRec['user_name']);
$vars['bucketPool'] = SelectBucketPool($UserRec["default_bucketpool_fk"]);
return $vars;
}
private function updateLicense($row, $sameText)
{
$log = "Text of '{$row['shortname']}' already used for '{$sameText['rf_shortname']}'";
$stmt = __METHOD__;
$sql = 'UPDATE license_ref SET ';
$param = array($sameText['rf_pk']);
if (!empty($row['source']) && empty($sameText['rf_source'])) {
$stmt .= '.updSource';
$sql .= ' rf_source=$2';
$param[] = $row['source'];
$log .= ', updated the source';
}
if (false !== $row['risk'] && $row['risk'] !== $sameText['rf_risk']) {
$stmt .= '.updRisk';
$sql .= count($param) == 2 ? ', rf_risk=$3' : 'rf_risk=$';
$param[] = $row['risk'];
$log .= ', updated the risk level';
}
if (count($param) > 1) {
$this->dbManager->prepare("{$sql} WHERE rf_pk=\$1", $stmt);
$res = $this->dbManager->execute($stmt, $param);
$this->dbManager->freeResult($res);
}
return $log;
}
/**
* @param DbManager $dbManager
* @param int $groupId
* @param int $usageId
*/
public function __construct(DbManager $dbManager, $groupId, $usageId = null, $full = false)
{
$this->usageId = $usageId ?: self::CONCLUSION;
$this->groupId = $groupId;
$this->dbManager = $dbManager;
if ($this->usageId == self::TRIVIAL && !$full) {
return;
}
$licenseView = new LicenseViewProxy($groupId);
if ($full) {
$query = $licenseView->asCTE() . ' SELECT distinct on(rf_pk) rf_pk rf_fk, rf_shortname parent_shortname, rf_parent FROM (
SELECT r1.rf_pk, r2.rf_shortname, usage, rf_parent FROM ' . $licenseView->getDbViewName() . ' r1 inner join license_map on usage=$1 and rf_fk=r1.rf_pk
left join license_ref r2 on rf_parent=r2.rf_pk
UNION
SELECT rf_pk, rf_shortname, -1 usage, rf_pk rf_parent from ' . $licenseView->getDbViewName() . ') full_map ORDER BY rf_pk,usage DESC';
$stmt = __METHOD__ . ".{$this->usageId},{$groupId},full";
} else {
$query = $licenseView->asCTE() . ' SELECT rf_fk, rf_shortname parent_shortname, rf_parent FROM license_map, ' . $licenseView->getDbViewName() . ' WHERE rf_pk=rf_parent AND rf_fk!=rf_parent AND usage=$1';
$stmt = __METHOD__ . ".{$this->usageId},{$groupId}";
}
$dbManager->prepare($stmt, $query);
$res = $dbManager->execute($stmt, array($this->usageId));
while ($row = $dbManager->fetchArray($res)) {
$this->map[$row['rf_fk']] = $row;
}
$dbManager->freeResult($res);
}
/**
* @param int $uploadId
* @param int $groupId
* @return int
*/
public function getReusedUpload($uploadId, $groupId)
{
$statementName = __METHOD__;
$this->dbManager->prepare($statementName, "SELECT reused_upload_fk, reused_group_fk, reuse_mode FROM upload_reuse WHERE upload_fk = \$1 AND group_fk=\$2");
$res = $this->dbManager->execute($statementName, array($uploadId, $groupId));
$reusedPairs = $this->dbManager->fetchAll($res);
$this->dbManager->freeResult($res);
return $reusedPairs;
}
/**
* @param string $scannerName
* @param int $uploadId
* @return array[] with keys agent_id,agent_rev,agent_name
*/
public function getSuccessfulAgentEntries($scannerName, $uploadId)
{
$stmt = __METHOD__ . ".getAgent.{$scannerName}";
$this->dbManager->prepare($stmt, $sql = "SELECT agent_pk AS agent_id,agent_rev,agent_name " . "FROM agent LEFT JOIN {$scannerName}" . self::ARS_TABLE_SUFFIX . " ON agent_fk=agent_pk " . "WHERE agent_name=\$2 AND agent_enabled AND upload_fk=\$1 AND ars_success " . "ORDER BY agent_id DESC");
$res = $this->dbManager->execute($stmt, array($uploadId, $scannerName));
$agents = $this->dbManager->fetchAll($res);
$this->dbManager->freeResult($res);
return $agents;
}
/**
* @brief Return total Job data with time elapsed
* @param $job_pk
* @return $row
*/
public function getDataForASingleJob($job_pk)
{
$statementName = __METHOD__ . "getDataForASingleJob";
$this->dbManager->prepare($statementName, "SELECT *, jq_endtime-jq_starttime as elapsed FROM jobqueue LEFT JOIN job ON job.job_pk = jobqueue.jq_job_fk WHERE jobqueue.jq_pk =\$1");
$result = $this->dbManager->execute($statementName, array($job_pk));
$row = $this->dbManager->fetchArray($result);
$this->dbManager->freeResult($result);
return $row;
}
public function getPermissionGroups($uploadId)
{
$this->dbManager->prepare($stmt = __METHOD__, "SELECT perm_upload_pk, perm, group_pk, group_name\n FROM groups, perm_upload\n WHERE group_fk=group_pk AND upload_fk=\$1\n ORDER BY group_name");
$res = $this->dbManager->execute($stmt, array($uploadId));
$groupMap = array();
while ($row = $this->dbManager->fetchArray($res)) {
$groupMap[$row['group_pk']] = $row;
}
$this->dbManager->freeResult($res);
return $groupMap;
}
/**
* @brief Get job queue data from db.
*
* @param $job_pks Array of $job_pk's to display.
* @param $page Get data for this display page. Starts with zero.
*
* @return array of job data
* \code
* JobData [job_pk] Array of job records (JobRec)
*
* JobRec['jobqueue'][jq_pk] = array of JobQueue records
* JobRec['jobqueue'][jq_pk]['depends'] = array of jq_pk's for dependencies
* JobRec['upload'] = array for upload record
* JobRec['job'] = array for job record
* JobRec['uploadtree'] = array for parent uploadtree record
*
* JobQueue ['jq_pk'] = jq_pk
* JobQueue ['jq_type'] = jq_type
* JobQueue ['jq_itemsprocessed'] = jq_itemsprocessed
* JobQueue ['jq_starttime'] = jq_starttime
* JobQueue ['jq_endtime'] = jq_endtime
* JobQueue ['jq_log'] = jq_log
* JobQueue ['jq_endtext'] = jq_endtext
* JobQueue ['jq_end_bits'] = jq_end_bits
* \endcode
**/
public function getJobInfo($job_pks, $page = 0)
{
/* Output data array */
$jobData = array();
foreach ($job_pks as $job_pk) {
/* Get job table data */
$statementName = __METHOD__ . "JobRec";
$jobRec = $this->dbManager->getSingleRow("SELECT * FROM job WHERE job_pk= \$1", array($job_pk), $statementName);
$jobData[$job_pk]["job"] = $jobRec;
if (!empty($jobRec["job_upload_fk"])) {
$upload_pk = $jobRec["job_upload_fk"];
/* Get Upload record for job */
$statementName = __METHOD__ . "UploadRec";
$uploadRec = $this->dbManager->getSingleRow("SELECT * FROM upload WHERE upload_pk= \$1", array($upload_pk), $statementName);
if (!empty($uploadRec)) {
$jobData[$job_pk]["upload"] = $uploadRec;
/* Get Upload record for uploadtree */
$uploadtree_tablename = $uploadRec["uploadtree_tablename"];
$statementName = __METHOD__ . "uploadtreeRec";
$uploadtreeRec = $this->dbManager->getSingleRow("SELECT * FROM {$uploadtree_tablename} where upload_fk = \$1 and parent is null", array($upload_pk), $statementName);
$jobData[$job_pk]["uploadtree"] = $uploadtreeRec;
} else {
$statementName = __METHOD__ . "uploadRecord";
$uploadRec = $this->dbManager->getSingleRow("SELECT * FROM upload right join job on upload_pk = job_upload_fk where job_upload_fk = \$1", array($upload_pk), $statementName);
/* upload has been deleted so try to get the job name from the original upload job record */
$jobName = $this->getJobName($uploadRec["job_upload_fk"]);
$uploadRec["upload_filename"] = "Deleted Upload: " . $uploadRec["job_upload_fk"] . "(" . $jobName . ")";
$uploadRec["upload_pk"] = $uploadRec["job_upload_fk"];
$jobData[$job_pk]["upload"] = $uploadRec;
}
}
/* Get jobqueue table data */
$statementName = __METHOD__ . "job_pkforjob";
$this->dbManager->prepare($statementName, "SELECT jq.*,jd.jdep_jq_depends_fk FROM jobqueue jq LEFT OUTER JOIN jobdepends jd ON jq.jq_pk=jd.jdep_jq_fk WHERE jq.jq_job_fk=\$1 ORDER BY jq_pk ASC");
$result = $this->dbManager->execute($statementName, array($job_pk));
$rows = $this->dbManager->fetchAll($result);
if (!empty($rows)) {
foreach ($rows as $jobQueueRec) {
$jq_pk = $jobQueueRec["jq_pk"];
if (array_key_exists($job_pk, $jobData) && array_key_exists('jobqueue', $jobData[$job_pk]) && array_key_exists($jq_pk, $jobData[$job_pk]['jobqueue'])) {
$jobData[$job_pk]['jobqueue'][$jq_pk]["depends"][] = $jobQueueRec["jdep_jq_depends_fk"];
} else {
$jobQueueRec["depends"] = array($jobQueueRec["jdep_jq_depends_fk"]);
$jobData[$job_pk]['jobqueue'][$jq_pk] = $jobQueueRec;
}
}
} else {
unset($jobData[$job_pk]);
}
$this->dbManager->freeResult($result);
}
return $jobData;
}
private function getGroupMembers($groupsWhereUserIsAdmin)
{
$this->dbManager->prepare($stmt = __METHOD__, "SELECT user_name,gum.group_fk FROM group_user_member gum, users WHERE user_fk=user_pk");
$res = $this->dbManager->execute($stmt);
$gum = array();
while ($row = $this->dbManager->fetchArray($res)) {
if (array_key_exists($row['group_fk'], $groupsWhereUserIsAdmin)) {
$gum[] = array($row['user_name'], $row['group_fk']);
}
}
$this->dbManager->freeResult($res);
return $gum;
}
public function getFolderChildFolders($folderId)
{
$results = array();
$stmtFolder = __METHOD__;
$sqlFolder = "SELECT foldercontents_pk,foldercontents_mode, folder_name FROM foldercontents,folder " . "WHERE foldercontents.parent_fk=\$1 AND foldercontents.child_id=folder.folder_pk" . " AND foldercontents_mode=" . self::MODE_FOLDER;
$this->dbManager->prepare($stmtFolder, $sqlFolder);
$res = $this->dbManager->execute($stmtFolder, array($folderId));
while ($row = $this->dbManager->fetchArray($res)) {
$results[$row['foldercontents_pk']] = $row;
}
$this->dbManager->freeResult($res);
return $results;
}
public function getMainLicenseIds($uploadId, $groupId)
{
$stmt = __METHOD__;
$sql = "SELECT rf_fk FROM upload_clearing_license WHERE upload_fk=\$1 AND group_fk=\$2";
$this->dbManager->prepare($stmt, $sql);
$res = $this->dbManager->execute($stmt, array($uploadId, $groupId));
$ids = array();
while ($row = $this->dbManager->fetchArray($res)) {
$ids[$row['rf_fk']] = $row['rf_fk'];
}
$this->dbManager->freeResult($res);
return $ids;
}
/**
* @return string[] with keys being shortname
*/
protected function getLicenseTexts()
{
$licenseTexts = array();
$licenseViewProxy = new LicenseViewProxy($this->groupId, array(LicenseViewProxy::OPT_COLUMNS => array('rf_pk', 'rf_shortname', 'rf_text')));
$this->dbManager->prepare($stmt = __METHOD__, $licenseViewProxy->getDbViewQuery());
$res = $this->dbManager->execute($stmt);
while ($row = $this->dbManager->fetchArray($res)) {
if (array_key_exists($row['rf_pk'], $this->includedLicenseIds)) {
$licenseTexts[$row['rf_shortname']] = $row['rf_text'];
}
}
$this->dbManager->freeResult($res);
return $licenseTexts;
}
protected function getCopyrights($upload_pk, $item, $uploadTreeTableName, $agentId, $type, $filter)
{
$offset = GetParm('iDisplayStart', PARM_INTEGER);
$limit = GetParm('iDisplayLength', PARM_INTEGER);
$tableName = $this->getTableName($type);
$orderString = $this->getOrderString();
list($left, $right) = $this->uploadDao->getLeftAndRight($item, $uploadTreeTableName);
if ($filter == "") {
$filter = "none";
}
$sql_upload = "";
if ('uploadtree_a' == $uploadTreeTableName) {
$sql_upload = " AND UT.upload_fk={$upload_pk} ";
}
$join = "";
$filterQuery = "";
if ($type == 'statement' && $filter == "nolic") {
$noLicStr = "No_license_found";
$voidLicStr = "Void";
$join = " INNER JOIN license_file AS LF on cp.pfile_fk=LF.pfile_fk ";
$filterQuery = " AND LF.rf_fk IN (SELECT rf_pk FROM license_ref WHERE rf_shortname IN ('{$noLicStr}','{$voidLicStr}')) ";
} else {
// No filter, nothing to do
}
$params = array($left, $right, $type, $agentId);
$filterParms = $params;
$searchFilter = $this->addSearchFilter($filterParms);
$unorderedQuery = "FROM {$tableName} AS cp " . "INNER JOIN {$uploadTreeTableName} AS UT ON cp.pfile_fk = UT.pfile_fk " . $join . "WHERE cp.content!='' " . "AND ( UT.lft BETWEEN \$1 AND \$2 ) " . "AND cp.type = \$3 " . "AND cp.agent_fk= \$4 " . $sql_upload;
$totalFilter = $filterQuery . " " . $searchFilter;
$grouping = " GROUP BY content ";
$countQuery = "SELECT count(*) FROM (SELECT content, count(*) {$unorderedQuery} {$totalFilter} {$grouping}) as K";
$iTotalDisplayRecordsRow = $this->dbManager->getSingleRow($countQuery, $filterParms, __METHOD__ . $tableName . ".count");
$iTotalDisplayRecords = $iTotalDisplayRecordsRow['count'];
$countAllQuery = "SELECT count(*) FROM (SELECT content, count(*) {$unorderedQuery}{$grouping}) as K";
$iTotalRecordsRow = $this->dbManager->getSingleRow($countAllQuery, $params, __METHOD__, $tableName . "count.all");
$iTotalRecords = $iTotalRecordsRow['count'];
$range = "";
$filterParms[] = $offset;
$range .= ' OFFSET $' . count($filterParms);
$filterParms[] = $limit;
$range .= ' LIMIT $' . count($filterParms);
$sql = "SELECT content, hash, count(*) as copyright_count " . $unorderedQuery . $totalFilter . " GROUP BY content, hash " . $orderString . $range;
$statement = __METHOD__ . $filter . $tableName . $uploadTreeTableName;
$this->dbManager->prepare($statement, $sql);
$result = $this->dbManager->execute($statement, $filterParms);
$rows = $this->dbManager->fetchAll($result);
$this->dbManager->freeResult($result);
return array($rows, $iTotalDisplayRecords, $iTotalRecords);
}
public function testGetEstimatedTimeShouldNotDivideByZero()
{
$this->dbManager->prepare($stmt = 'insert.jobqueue', "INSERT INTO jobqueue (jq_pk, jq_job_fk, jq_type, jq_args, jq_starttime, jq_endtime, jq_endtext, jq_end_bits, jq_schedinfo, jq_itemsprocessed)" . "VALUES (\$1, \$2, \$3, \$4,\$5, \$6,\$7,\$8,\$9,\$10)");
$nowTime = time();
$diffTime = 2345;
$nomosTime = date('Y-m-d H:i:sO', $nowTime - $diffTime);
$uploadArrayQue = array(array(8, $jobId = 1, $jqType = "nomos", 1, $nomosTime, null, "Started", 0, "localhost.5963", $itemNomos = 147), array(1, $jobId, "ununpack", 1, "2015-04-21 18:29:19.23825+05:30", "2015-04-21 18:29:26.396562+05:30", "Completed", 1, null, $itemCount = 646));
foreach ($uploadArrayQue as $uploadEntry) {
$this->dbManager->freeResult($this->dbManager->execute($stmt, $uploadEntry));
}
$showJobsDaoMock = M::mock('Fossology\\Lib\\Dao\\ShowJobsDao[getNumItemsPerSec]', array($this->dbManager, $this->uploadDao));
$showJobsDaoMock->shouldReceive('getNumItemsPerSec')->andReturn(0);
$estimated = $showJobsDaoMock->getEstimatedTime($jobId, $jqType);
assertThat($estimated, equalTo('0:00:00'));
}
/**
* @param ItemTreeBounds $item
* @param string $hash
* @param int $userId
* @param string $cpTable
*/
public function rollbackTable($item, $hash, $userId, $cpTable = 'copyright')
{
$itemTable = $item->getUploadTreeTableName();
$stmt = __METHOD__ . ".{$cpTable}.{$itemTable}";
$params = array($hash, $item->getLeft(), $item->getRight(), $userId);
$sql = "UPDATE {$cpTable} AS cpr SET content = cpa.oldtext, hash = \$1\n FROM " . $cpTable . "_audit as cpa, {$itemTable} AS ut\n WHERE cpr.pfile_fk = ut.pfile_fk\n AND cpr.ct_pk = cpa.ct_fk\n AND md5(cpa.oldtext) = \$1\n AND ( ut.lft BETWEEN \$2 AND \$3 )\n AND cpa.user_fk=\$4";
if ('uploadtree_a' == $item->getUploadTreeTableName()) {
$params[] = $item->getUploadId();
$sql .= " AND ut.upload_fk=\$" . count($params);
$stmt .= '.upload';
}
$this->dbManager->prepare($stmt, "{$sql}");
$resource = $this->dbManager->execute($stmt, $params);
$this->dbManager->freeResult($resource);
}
function addFunctions($schema)
{
// prosrc
// proretset == setof
$sql = "SELECT proname AS name,\n pronargs AS input_num,\n proargnames AS input_names,\n proargtypes AS input_type,\n proargmodes AS input_modes,\n proretset AS setof,\n prorettype AS output_type\n FROM pg_proc AS proc\n INNER JOIN pg_language AS lang ON proc.prolang = lang.oid\n WHERE lang.lanname = 'plpgsql'\n ORDER BY proname;";
$stmt = __METHOD__;
$this->dbman->prepare($stmt, $sql);
$result = $this->dbman->execute($stmt);
while ($row = $this->dbman->fetchArray($result)) {
$sql = "CREATE or REPLACE function " . $row['proname'] . "()";
$sql .= ' RETURNS ' . "TBD" . ' AS $$';
$sql .= " " . $row['prosrc'];
$schema['FUNCTION'][$row['proname']] = $sql;
}
$this->dbman->freeResult($result);
return $schema;
}
/**
* \brief Update the database
*
* \return An update status string
*/
function Updatedb()
{
$rfId = intval($_POST['rf_pk']);
$shortname = trim($_POST['rf_shortname']);
$fullname = trim($_POST['rf_fullname']);
$url = $_POST['rf_url'];
$notes = $_POST['rf_notes'];
$text = trim($_POST['rf_text']);
$parent = $_POST['rf_parent'];
$report = $_POST['rf_report'];
$riskLvl = intval($_POST['risk_level']);
if (empty($shortname)) {
$text = _("ERROR: The license shortname is empty.");
return "<b>{$text}</b><p>";
}
if ($this->isShortnameBlocked($rfId, $shortname, $text)) {
$text = _("ERROR: The shortname or license text already exist in the license list. License not added.");
return "<b>{$text}</b><p>";
}
$md5term = empty($text) || stristr($text, "License by Nomos") ? 'null' : 'md5($10)';
$sql = "UPDATE license_ref SET\n rf_active=\$2, marydone=\$3, rf_shortname=\$4, rf_fullname=\$5,\n rf_url=\$6, rf_notes=\$7, rf_text_updatable=\$8, rf_detector_type=\$9, rf_text=\$10,\n rf_md5={$md5term},\n rf_risk=\$11\n WHERE rf_pk=\$1";
$params = array($rfId, $_POST['rf_active'], $_POST['marydone'], $shortname, $fullname, $url, $notes, $_POST['rf_text_updatable'], $_POST['rf_detector_type'], $text, $riskLvl);
$this->dbManager->prepare($stmt = __METHOD__ . ".{$md5term}", $sql);
$this->dbManager->freeResult($this->dbManager->execute($stmt, $params));
$parentMap = new LicenseMap($this->dbManager, 0, LicenseMap::CONCLUSION);
$parentLicenses = $parentMap->getTopLevelLicenseRefs();
if (array_key_exists($parent, $parentLicenses) && $parent != $parentMap->getProjectedId($rfId)) {
$stmtDel = __METHOD__ . '.deleteFromMap';
$this->dbManager->prepare($stmtDel, 'DELETE FROM license_map WHERE rf_fk=$1 AND usage=$2');
$this->dbManager->execute($stmtDel, array($rfId, LicenseMap::CONCLUSION));
$this->dbManager->insertTableRow('license_map', array('rf_fk' => $rfId, 'rf_parent' => $parent, 'usage' => LicenseMap::CONCLUSION));
}
$reportMap = new LicenseMap($this->dbManager, 0, LicenseMap::REPORT);
$reportLicenses = $parentMap->getTopLevelLicenseRefs();
if (array_key_exists($report, $reportLicenses) && $report != $reportMap->getProjectedId($rfId)) {
$stmtDel = __METHOD__ . '.deleteFromMap';
$this->dbManager->prepare($stmtDel, 'DELETE FROM license_map WHERE rf_fk=$1 AND usage=$2');
$this->dbManager->execute($stmtDel, array($rfId, LicenseMap::REPORT));
$this->dbManager->insertTableRow('license_map', array('rf_fk' => $rfId, 'rf_parent' => $report, 'usage' => LicenseMap::REPORT));
}
$ob = "License {$_POST['rf_shortname']} updated.<p>";
return $ob;
}
/**
*
* @param string $tablename
* @param string $columnname
* @param array $map using keys
* @return int
*/
private function checkDatabaseEnum($tablename, $columnname, $map)
{
$errors = 0;
$stmt = __METHOD__ . ".{$tablename}.{$columnname}";
$sql = "SELECT {$columnname},count(*) FROM {$tablename} GROUP BY {$columnname}";
$this->dbManager->prepare($stmt, $sql);
$res = $this->dbManager->execute($stmt);
while ($row = $this->dbManager->fetchArray($res)) {
if (!array_key_exists($row[$columnname], $map)) {
echo "(-) found invalid {$columnname} '" . $row[$columnname] . "' in table '{$tablename}'\n";
$errors++;
} else {
if ($this->verbose) {
echo "(+) found valid {$columnname} '" . $row[$columnname] . "' in table '{$tablename}'\n";
}
}
}
$this->dbManager->freeResult($res);
return $errors;
}
/**
* @param int $uploadTreeId
* @param int|null $clearingId
* @return Highlight[]
*/
public function getHighlightBulk($uploadTreeId, $clearingId = null)
{
$stmt = __METHOD__;
$sql = "SELECT h.clearing_event_fk, h.start, h.len, ce.rf_fk, rf_text\n FROM clearing_event ce\n INNER JOIN highlight_bulk h ON ce.clearing_event_pk = h.clearing_event_fk\n INNER JOIN license_ref_bulk lrb ON lrb.lrb_pk = h.lrb_fk\n WHERE ce.uploadtree_fk = \$1";
$params = array($uploadTreeId);
if (!empty($clearingId)) {
$stmt .= ".clearingId";
$params[] = $clearingId;
$sql .= " AND h.clearing_event_fk = \$" . count($params);
}
$this->dbManager->prepare($stmt, $sql);
$result = $this->dbManager->execute($stmt, $params);
$highlightEntries = array();
while ($row = $this->dbManager->fetchArray($result)) {
$newHighlight = new Highlight(intval($row['start']), intval($row['start'] + $row['len']), Highlight::BULK, 0, 0);
$newHighlight->setLicenseId($row['rf_fk']);
$highlightEntries[] = $newHighlight;
}
$this->dbManager->freeResult($result);
return $highlightEntries;
}
/**
* \return return rows to process, and $upload_pk
* @param $Uploadtree_pk
* @param $Agent_pk
* @param $upload_pk
* @param $hash
* @param $type
* @param $tableName
* @throws Exception
* @return array
*/
function GetRows($Uploadtree_pk, $Agent_pk, &$upload_pk, $hash, $type, $tableName)
{
global $PG_CONN;
/******* Get license names and counts ******/
/* Find lft and rgt bounds for this $Uploadtree_pk */
$sql = "SELECT lft,rgt,upload_fk FROM uploadtree\n WHERE uploadtree_pk = {$Uploadtree_pk}";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
$row = pg_fetch_assoc($result);
$lft = $row["lft"];
$rgt = $row["rgt"];
$upload_pk = $row["upload_fk"];
pg_free_result($result);
/* get all the copyright records for this uploadtree. */
$sql = "SELECT content, type, uploadtree_pk, ufile_name, PF\n from {$tableName},\n (SELECT uploadtree_pk, pfile_fk as PF, ufile_name from uploadtree \n where upload_fk=\$1\n and uploadtree.lft BETWEEN \$2 and \$3) as SS\n where PF=pfile_fk and agent_fk=\$4 and hash=\$5 and type=\$6 order by uploadtree_pk";
$statement = __METHOD__ . $tableName;
$this->dbManager->prepare($statement, $sql);
$result = $this->dbManager->execute($statement, array($upload_pk, $lft, $rgt, $Agent_pk, $hash, $type));
$rows = $this->dbManager->fetchAll($result);
$this->dbManager->freeResult($result);
return $rows;
}
public function getLicenseShortnamesContained(ItemTreeBounds $itemTreeBounds, $latestSuccessfulAgentIds = null, $filterLicenses = array('VOID'))
{
$uploadTreeTableName = $itemTreeBounds->getUploadTreeTableName();
$noLicenseFoundStmt = empty($filterLicenses) ? "" : " AND rf_shortname NOT IN (" . implode(", ", array_map(function ($name) {
return "'" . $name . "'";
}, $filterLicenses)) . ")";
$statementName = __METHOD__ . '.' . $uploadTreeTableName;
$agentFilter = '';
if (is_array($latestSuccessfulAgentIds)) {
$agentIdSet = "{" . implode(',', $latestSuccessfulAgentIds) . "}";
$statementName .= ".{$agentIdSet}";
$agentFilter = " AND agent_fk=ANY('{$agentIdSet}')";
}
$this->dbManager->prepare($statementName, "SELECT license_ref.rf_shortname\n FROM license_file JOIN license_ref ON license_file.rf_fk = license_ref.rf_pk\n INNER JOIN {$uploadTreeTableName} uploadTree ON uploadTree.pfile_fk=license_file.pfile_fk\n WHERE upload_fk=\$1\n AND lft BETWEEN \$2 AND \$3\n {$noLicenseFoundStmt} {$agentFilter}\n GROUP BY rf_shortname\n ORDER BY rf_shortname ASC");
$result = $this->dbManager->execute($statementName, array($itemTreeBounds->getUploadId(), $itemTreeBounds->getLeft(), $itemTreeBounds->getRight()));
$licenses = array();
while ($row = $this->dbManager->fetchArray($result)) {
$licenses[] = $row['rf_shortname'];
}
$this->dbManager->freeResult($result);
return $licenses;
}
/**
* \brief Display the tag info data associated with the file.
*/
function ShowTagInfo($Upload, $Item)
{
$VT = "";
$text = _("Tag Info");
$VT .= "<H2>{$text}</H2>\n";
$groupId = Auth::getGroupId();
$row = $this->uploadDao->getUploadEntry($Item);
if (empty($row)) {
$text = _("Invalid URL, nonexistant item");
return "<h2>{$text} {$Item}</h2>";
}
$lft = $row["lft"];
$rgt = $row["rgt"];
$upload_pk = $row["upload_fk"];
if (empty($lft)) {
$text = _("Upload data is unavailable. It needs to be unpacked.");
return "<h2>{$text} uploadtree_pk: {$Item}</h2>";
}
$sql = "SELECT * FROM uploadtree INNER JOIN (SELECT * FROM tag_file,tag WHERE tag_pk = tag_fk) T\n ON uploadtree.pfile_fk = T.pfile_fk WHERE uploadtree.upload_fk = \$1\n AND uploadtree.lft >= \$2 AND uploadtree.rgt <= \$3 UNION SELECT * FROM uploadtree INNER JOIN\n (SELECT * FROM tag_uploadtree,tag WHERE tag_pk = tag_fk) T ON uploadtree.uploadtree_pk = T.uploadtree_fk\n WHERE uploadtree.upload_fk = \$1 AND uploadtree.lft >= \$2 AND uploadtree.rgt <= \$3 ORDER BY ufile_name";
$this->dbManager->prepare(__METHOD__, $sql);
$result = $this->dbManager->execute(__METHOD__, array($upload_pk, $lft, $rgt));
if (pg_num_rows($result) > 0) {
$VT .= "<table border=1>\n";
$text = _("FileName");
$text2 = _("Tag");
$VT .= "<tr><th>{$text}</th><th>{$text2}</th><th></th></tr>\n";
while ($row = pg_fetch_assoc($result)) {
$VT .= "<tr><td align='center'>" . $row['ufile_name'] . "</td><td align='center'>" . $row['tag'] . "</td>";
if ($this->uploadDao->isAccessible($upload_pk, $groupId)) {
$VT .= "<td align='center'><a href='" . Traceback_uri() . "?mod=tag&action=edit&upload={$Upload}&item=" . $row['uploadtree_pk'] . "&tag_file_pk=" . $row['tag_file_pk'] . "'>View</a></td></tr>\n";
} else {
$VT .= "<td align='center'></td></tr>\n";
}
}
$VT .= "</table><p>\n";
}
$this->dbManager->freeResult($result);
return $VT;
}
/**
* @param $itemId - uploadtree id
* @param $uploadId - upload id
*/
public function getCopyrightList($itemId, $uploadId)
{
if (empty($itemId)) {
$itemId = $this->uploadDao->getUploadParent($uploadId);
}
if (!$this->selectAgentId($uploadId)) {
echo 'no valid copyright agent found';
return;
}
$uploadtree_tablename = $this->uploadDao->getUploadtreeTableName($uploadId);
$toprow = $this->uploadDao->getItemTreeBounds($itemId, $uploadtree_tablename);
$extraWhere = 'agent_fk=' . $this->agentId . ' AND lft>' . $toprow->getLeft() . ' AND rgt<' . $toprow->getRight();
$allCopyrightEntries = $this->copyrightDao->getAllEntries('copyright', $uploadId, $uploadtree_tablename, empty($this->type) || $this->type == 'all' ? null : $this->type, false, null, $extraWhere);
$modeMask = empty($this->includeContainer) ? 3 << 28 : 1 << 28;
$sql = "SELECT uploadtree_pk, ufile_name, lft, rgt FROM {$uploadtree_tablename} \n WHERE upload_fk=\$1 AND lft>\$2 AND rgt<\$3 AND (ufile_mode & \$4) = 0\n ORDER BY uploadtree_pk";
$this->dbManager->prepare($outerStmt = __METHOD__ . '.loopThroughAllRecordsInTree', $sql);
$outerresult = $this->dbManager->execute($outerStmt, array($toprow->getUploadId(), $toprow->getLeft(), $toprow->getRight(), $modeMask));
while ($row = $this->dbManager->fetchArray($outerresult)) {
$this->printRow($row, $uploadtree_tablename, $allCopyrightEntries);
//$this->uploadDao->getParentItemBounds($uploadId)->getItemId());
}
$this->dbManager->freeResult($outerresult);
}
public function testGetLicenseShortnamesContained()
{
$this->testDb->createPlainTables(array('license_ref', 'license_file', 'uploadtree'));
$this->dbManager->queryOnce("CREATE TABLE \"uploadtree_a\" AS SELECT * FROM uploadtree");
$this->testDb->createViews(array('license_file_ref'));
$this->testDb->insertData(array('license_file', 'uploadtree_a'));
$this->testDb->insertData_license_ref($limit = 3);
$stmt = __METHOD__ . '.select.license_ref';
$this->dbManager->prepare($stmt, "SELECT rf_pk,rf_shortname FROM license_ref");
$licRes = $this->dbManager->execute($stmt);
$licAll = array();
while ($erg = $this->dbManager->fetchArray($licRes)) {
$licAll[$erg['rf_pk']] = $erg['rf_shortname'];
}
$this->dbManager->freeResult($licRes);
$pfileId = 42;
$agentId = 23;
$matchPercent = 50;
$uploadtreeId = 512;
$uploadId = 123;
$left = 2009;
$right = 2014;
$mydate = "'2014-06-04 14:01:30.551093+02'";
foreach ($licAll as $licenseRefNumber => $shortname) {
$this->dbManager->queryOnce("INSERT INTO license_file (rf_fk, agent_fk, rf_match_pct, rf_timestamp, pfile_fk)\n VALUES ({$licenseRefNumber}, {$agentId}, {$matchPercent}, {$mydate}, {$pfileId})");
}
$this->dbManager->queryOnce("INSERT INTO uploadtree (uploadtree_pk, upload_fk, pfile_fk, lft, rgt)\n VALUES ({$uploadtreeId}, {$uploadId}, {$pfileId}, {$left}, {$right})");
$licDao = new LicenseDao($this->dbManager);
$itemTreeBounds = new ItemTreeBounds($uploadtreeId, "uploadtree", $uploadId, $left, $right);
$licenses = $licDao->getLicenseShortnamesContained($itemTreeBounds);
assertThat($licenses, is(arrayContainingInAnyOrder(array_values($licAll))));
$licensesForBadAgent = $licDao->getLicenseShortnamesContained($itemTreeBounds, array(2 * $agentId));
assertThat($licensesForBadAgent, is(emptyArray()));
$licensesForNoAgent = $licDao->getLicenseShortnamesContained($itemTreeBounds, array());
assertThat($licensesForNoAgent, is(emptyArray()));
$this->addToAssertionCount(\Hamcrest\MatcherAssert::getCount() - $this->assertCountBefore);
}
