Assume, That a Dictionary of Keywords for the Articles Consists of 1000 Words in Random

Task 1

Assume, that a dictionary of keywords for the articles consists of 1000 words in random order. This list changes only once a day at 03:00, while everybody is sleeping. For the rest of the day the main request to this array is finding several closest words to a given one by prefix. (for example: “finish” and “finance” are equally close the word “Finland”. Because only 3 first letters coincide in both cases).

So your task is to write 2 functions: function prepare(&$keywords){…} and function getClosestList(&$keywords, $key, $numWords=5){…}. Function “prepare” will be called once a day to modify $keywords array structure, if needed. But you can also leave empty. getClosestList will return $numWords (from 1 to 10) closest to the $keywords.

Unfortunately you are not allowed to use PHP sort functions, and if you need one, you have to write it by yourself.

How would you change the algorithm if there were 1000000 keywords in array? Is there a way to search a bulk of $keys faster, than calling getClosestList function in the loop? Would you change an algorithm, if getClosestList function needed to return a list of closest words only in case of exact match with some of them (if yes, what would be the changes) and FALSE otherwise?

Task 2

A friend of yours, who is only starting to learn PHP has written a script and asked you to review the code. What advices would you give to your friend, and what vulnerabilities have you found in the code? Correct them please. Btw, how do you check uploaded from the user files to be safe?

config.php

<?php

functionisadmin() {

//correct implementation of a function here

}

mysql_connect('localhost', 'root', 'somepass');

mysql_select_db('first');

?>

index.php

<?php

require_once("config.php");

switch($_GET['action']){

case 'view' :

include("view_comments.php");

break;

case 'add' :

include("add_comments.php");

break;

case 'delete' :

include("delete_comments.php");

break;

}

?>

view_comments.php

<?php

$res = mysql_query("SELECT * FROM `comments` WHERE `pid` = {$_GET['pid']}");

while ($row = mysql_fetch_assoc($res)) {

print $row['comment'];

}

?>

add_comments.php

<?php

$res = mysql_query("INSERT INTO `comments`(`pid`, `comment`) VALUES({$_POST['pid']}, {$_POST['comment']})");

while ($row = mysql_fetch_assoc($res)) {

print $row['comment'];

}

?>

delete_comments.php

<?php

if (!isadmin()) {

header('location:

}

mysql_query("DELETE * FROM `comments` WHERE `pid` = {$_GET['pid']}");

?>

Task 3

Imagine that you’re making a design of a new system. And customer insisted to staying DB-independent, so you can easily switch from MySQL to PgSQL, Oracle/MS SQL Server and vice versa. The basic functionality you need to make – is establishing connection to DB, send the query and retrieve the result. You also know, that you’ll go to vacation in a week and the other guy will have to implement some DB drivers (by a DB driver I mean a class, that uses mysql/pg_<functions>).

So please write the code that would encourage the guy to write his own DB drivers just as you have designed.

What could be the use-case to use a private constructor in such a situation? What design-pattern is usually implemented in such a way? What other design patterns do you know?

Task 4

Let’s say that we’ve got a site, that stores its visit stats in MySQL DB. The data is stored in the following way:

MinStats

Page_id | visit_time

Please write an SQL query that will show `Page_id` of 15 most visited pages and the last time it was visited by someone.

And now assume, that MinStats table should be merged with the main Stat (`date`, `hour`, `num_visited`) table with the same structure, that keeps archive of all the statistic. How would you move the data from MinStatstoStat?

//This merge is made every 15 minutes, so records with current (`date`, `hour`) values can already exist in DB.

Task 5

We’ve got a site, that really appreciates user activity. And it is ready to give different medals for users for posting comments, articles, visits, smart questions etc. The site is rather big already and so it will run a script via cron, to give medals to the users. But while quering the DB we don’t want to select users, that already have current medal.

Users

Id | name | email | pass

Profiles

Id | user_id | extra | num_comments_posted

Medals

Profile_id | medal_type (int)

Our programmer Vasya has written the most obvious query to get all the user_ids that should be given a medal with ID=5 and don’t have it yet.

SELECT DISTINCTuser_idFROM profiles WHERE num_comments_posted>100 AND id NOT IN (SELECT profile_id FROM medals WHERE medal_type=5)

But you surely know, that NOT IN statement runs too slowly in MySQL in most cases. So please rewrite the query without the statement we are trying to avoid.

Task 6

CreateaDrupalmodulethatdependsoncoreContactmodule and has admin-settings page. Thispageshouldcontain 2 settings to handle: default text for the from new input, and the type of this field (input type=text ortextarea). Alsothispageshouldcontaina “save settings” button. Thismoduleshouldalsoadd 1 fieldtoacontactform (ofachosentype) to which user can enter their telephone numbers separated by comma or space. When submitting the form this module should save telephone numbers, IP-address and his user-agent to DB. This data should be saved to a table, that was created during module installation. Module should drop the table during uninstallation process.

//You don’t have to implement CRUD operations interface for this log.

Thank you for your time spent on reading this non-poetic text. Feel free to contact us about any questions connected with a task.

Sincerely Yours,

ArcherSoft team.