nuclight/description.txt

## description.txt
Создать таблицу со следующими столбцами:
1. id - уникальный идентификатор
2. name - рандомные 12 символов
3. phone - рандомные 11 цифр
4. created - дата создания записи

Заполнить таблицу 3 миллионами записей.
Создать простейший интерфейс на html-странице (не в консоли) для постраничного вывода данных из таблицы и поиском по телефону

## generate.pl
#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;

use constant NUM_ROWS_PER_OP    => 30;
use constant NAME_LEN		=> 12;
use constant PHONE_LEN		=> 11;

my $dbh = DBI->connect('dbi:SQLite:./phones.db',
		undef, undef,
		{RaiseError => 1, AutoCommit => 0}
	) or die $DBI::errstr;

my $insphone_sth = $dbh->prepare(
	"INSERT INTO phones (name, phone) VALUES (?, ?)"
	. ", (?, ?)" x (NUM_ROWS_PER_OP-1)
);

# XXX by default SQLite is VERY slow on write, but we're in a test, yeah?
$dbh->do("PRAGMA synchronous = OFF");
$dbh->do("PRAGMA journal_mode = MEMORY");

sub gen_name {
    my $res = "";
    for (1 .. NAME_LEN) {
        # printable ASCII characters are from 32 to 126
        $res .= chr(32 + int(rand 126-32));
    }
    return $res;
}

sub gen_phone {
    my $res = "";
    for (1 .. PHONE_LEN) {
        $res .= chr(ord("0") + int(rand 10));
    }
    return $res;
}

# main loop
$| = 1;
for (1 .. 100_000) {
    # $dbh->begin_work; # not necessary when AutoCommit=0
    $insphone_sth->execute(map { gen_name, gen_phone } 1 .. NUM_ROWS_PER_OP);
    $dbh->commit, print "." unless $_ % 100;
}

$dbh->disconnect();

print "finish ok.\n";

## index.cgi
use v5.10;
use strict;
use warnings;

use URI;  # CGI.pm is deprecated since 5.19, and the task is simple
use URI::QueryParam;
use DBI;
use Template;

use constant PAGE_LIMIT => 30;	# just a default per page

# begin work
my $dbh = DBI->connect('dbi:SQLite:../phones.db',
		undef, undef,
		{RaiseError => 1, AutoCommit => 0}
	) or die $DBI::errstr;

my $host	= $ENV{'HTTP_HOST'};
my $script_name	= $ENV{'SCRIPT_NAME'};
my $uri = URI->new($host . $script_name . "?" . $ENV{'QUERY_STRING'}, "http");
my $find  = $uri->query_param('find')  // "";
my $skip  = $uri->query_param('skip')  // 0;
my $limit = $uri->query_param('limit') // PAGE_LIMIT;

# clean illegal characters from attacker, if any
$find  =~ s/[^0-9]//g;
$skip  =~ s/[^0-9]//g;
$limit =~ s/[^0-9]//g;

my $sql_tail = "FROM phones";
if ($find) {
    $sql_tail .= " WHERE phone LIKE ? ORDER BY id, phone";
}
else {
    $sql_tail .= " ORDER BY created"
}

my $cnt = $dbh->prepare("SELECT COUNT(*) $sql_tail");

$sql_tail .= " LIMIT $skip, $limit";

my $sth = $dbh->prepare("SELECT * $sql_tail");

my @bind_vars = $find ? "%$find%" : ();
my $total = (@{ $dbh->selectcol_arrayref($cnt, {}, @bind_vars) })[0];
my $resul = $sth->execute( @bind_vars );

# get rows and convert unix time to human time
my @rows;
while (my $row = $sth->fetchrow_hashref()) {
    push @rows, {
        id      => $row->{id},
        name    => $row->{name},
        phone   => $row->{phone},
        created => scalar localtime $row->{created},
    };
}

my $vars = {
   totalcnt	=> $total,
   find         => $find,
   skip         => $skip,
   limit        => $limit,
   hostname     => $host,
   script_name  => $script_name,
   rows         => \@rows,
};

# finally pass data to template rendering
$| = 1;
print "Content-Type: text/html\n\n";

my $template = Template->new({
    RELATIVE => 1,
});

$template->process("../main.tt2", $vars)
    || die "Template process failed: ", $template->error(), "\n";

$dbh->disconnect();

## main.tt2.html
<html>
<head>
  <title>Phones DB</title>
  <meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
</head>
<body bgcolor="#E6E6DA">
  <form>
    Search phone:
    <input type="text" name="find" value="[% find | html %]">
    <input type="submit"><br>
    <input type="text" size=3 maxlength=3 name="limit" value="[% limit %]">
    records per page
  </form>
[% INCLUDE summary_pages IF limit > 30 %]
  <table width="100%" border="1">
  <th>Id</th><th>Name</th><th>Phone</th><th>Date/time created</th>
  </th>
[% FOREACH row IN rows %]
  <tr>
     <td>[% row.id %]</td>
     <td>[% row.name | html %]</td>
     <td>[% row.phone %]</td>
     <td>[% row.created %]</td>
  </tr>
[% END %]
  </table>
[% INCLUDE summary_pages %]
[% BLOCK summary_pages %]
  Total [% totalcnt %] records found.<br>
  Page:
[% INCLUDE "../paginator.tt2" %]
[% END %]
</body>
</html>

## paginator.tt2.html
[% lastpage = (totalcnt + limit - 1) div limit;
   curpage  = skip div limit + 1;
   pagenum = 1;
   WHILE pagenum <= lastpage;
     IF pagenum <= 4
     || (pagenum <= (curpage + 3) && pagenum >= (curpage - 3))
     || pagenum >= (lastpage - 3);
       curskip = (pagenum - 1) * limit;
       INCLUDE onepage skip = curskip;
       pagenum = pagenum + 1;
     ELSE;
%]...[%
       pagenum = pagenum < curpage ? (curpage - 3) : (lastpage - 3);
     END;
   END;
%]
[% BLOCK onepage %]
  [% IF pagenum == curpage %]
    <b>[% pagenum %]</b>
  [% ELSE %]
    <a href="[% script_name %]?find=[% find %]&limit=[% limit %]&skip=[% skip %]">[% pagenum %]</a>
  [% END %]
[% END %]

## phones.sql
CREATE TABLE phones (
  id      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name    CHAR(12) NOT NULL,
  phone   CHAR(11) NOT NULL,
  created INTEGER              -- it's unix time in SQLite
);

CREATE INDEX "phones_idx" ON phones(phone);

CREATE TRIGGER on_new_phone AFTER INSERT ON phones BEGIN
  UPDATE phones
  SET created = strftime('%s','now') - 3000 + NEW.id % 1000
  WHERE phones.id = NEW.id;
END;
	Создать таблицу со следующими столбцами:
	1. id - уникальный идентификатор
	2. name - рандомные 12 символов
	3. phone - рандомные 11 цифр
	4. created - дата создания записи

	Заполнить таблицу 3 миллионами записей.
	Создать простейший интерфейс на html-странице (не в консоли) для постраничного вывода данных из таблицы и поиском по телефону
	#!/usr/bin/perl -w

	use strict;
	use warnings;
	use DBI;

	use constant NUM_ROWS_PER_OP => 30;
	use constant NAME_LEN => 12;
	use constant PHONE_LEN => 11;

	my $dbh = DBI->connect('dbi:SQLite:./phones.db',
	undef, undef,
	{RaiseError => 1, AutoCommit => 0}
	) or die $DBI::errstr;

	my $insphone_sth = $dbh->prepare(
	"INSERT INTO phones (name, phone) VALUES (?, ?)"
	. ", (?, ?)" x (NUM_ROWS_PER_OP-1)
	);

	# XXX by default SQLite is VERY slow on write, but we're in a test, yeah?
	$dbh->do("PRAGMA synchronous = OFF");
	$dbh->do("PRAGMA journal_mode = MEMORY");

	sub gen_name {
	my $res = "";
	for (1 .. NAME_LEN) {
	# printable ASCII characters are from 32 to 126
	$res .= chr(32 + int(rand 126-32));
	}
	return $res;
	}

	sub gen_phone {
	my $res = "";
	for (1 .. PHONE_LEN) {
	$res .= chr(ord("0") + int(rand 10));
	}
	return $res;
	}

	# main loop
	$\| = 1;
	for (1 .. 100_000) {
	# $dbh->begin_work; # not necessary when AutoCommit=0
	$insphone_sth->execute(map { gen_name, gen_phone } 1 .. NUM_ROWS_PER_OP);
	$dbh->commit, print "." unless $_ % 100;
	}

	$dbh->disconnect();

	print "finish ok.\n";
	use v5.10;
	use strict;
	use warnings;

	use URI; # CGI.pm is deprecated since 5.19, and the task is simple
	use URI::QueryParam;
	use DBI;
	use Template;

	use constant PAGE_LIMIT => 30; # just a default per page

	# begin work
	my $dbh = DBI->connect('dbi:SQLite:../phones.db',
	undef, undef,
	{RaiseError => 1, AutoCommit => 0}
	) or die $DBI::errstr;

	my $host = $ENV{'HTTP_HOST'};
	my $script_name = $ENV{'SCRIPT_NAME'};
	my $uri = URI->new($host . $script_name . "?" . $ENV{'QUERY_STRING'}, "http");
	my $find = $uri->query_param('find') // "";
	my $skip = $uri->query_param('skip') // 0;
	my $limit = $uri->query_param('limit') // PAGE_LIMIT;

	# clean illegal characters from attacker, if any
	$find =~ s/[^0-9]//g;
	$skip =~ s/[^0-9]//g;
	$limit =~ s/[^0-9]//g;

	my $sql_tail = "FROM phones";
	if ($find) {
	$sql_tail .= " WHERE phone LIKE ? ORDER BY id, phone";
	}
	else {
	$sql_tail .= " ORDER BY created"
	}

	my $cnt = $dbh->prepare("SELECT COUNT(*) $sql_tail");

	$sql_tail .= " LIMIT $skip, $limit";

	my $sth = $dbh->prepare("SELECT * $sql_tail");

	my @bind_vars = $find ? "%$find%" : ();
	my $total = (@{ $dbh->selectcol_arrayref($cnt, {}, @bind_vars) })[0];
	my $resul = $sth->execute( @bind_vars );

	# get rows and convert unix time to human time
	my @rows;
	while (my $row = $sth->fetchrow_hashref()) {
	push @rows, {
	id => $row->{id},
	name => $row->{name},
	phone => $row->{phone},
	created => scalar localtime $row->{created},
	};
	}

	my $vars = {
	totalcnt => $total,
	find => $find,
	skip => $skip,
	limit => $limit,
	hostname => $host,
	script_name => $script_name,
	rows => \@rows,
	};

	# finally pass data to template rendering
	$\| = 1;
	print "Content-Type: text/html\n\n";

	my $template = Template->new({
	RELATIVE => 1,
	});

	$template->process("../main.tt2", $vars)
	\|\| die "Template process failed: ", $template->error(), "\n";

	$dbh->disconnect();
	<html>
	<head>
	<title>Phones DB</title>
	<meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
	</head>
	<body bgcolor="#E6E6DA">
	<form>
	Search phone:
	<input type="text" name="find" value="[% find \| html %]">
	<input type="submit"><br>
	<input type="text" size=3 maxlength=3 name="limit" value="[% limit %]">
	records per page
	</form>
	[% INCLUDE summary_pages IF limit > 30 %]
	<table width="100%" border="1">
	<th>Id</th><th>Name</th><th>Phone</th><th>Date/time created</th>
	</th>
	[% FOREACH row IN rows %]
	<tr>
	<td>[% row.id %]</td>
	<td>[% row.name \| html %]</td>
	<td>[% row.phone %]</td>
	<td>[% row.created %]</td>
	</tr>
	[% END %]
	</table>
	[% INCLUDE summary_pages %]
	[% BLOCK summary_pages %]
	Total [% totalcnt %] records found.<br>
	Page:
	[% INCLUDE "../paginator.tt2" %]
	[% END %]
	</body>
	</html>
	[% lastpage = (totalcnt + limit - 1) div limit;
	curpage = skip div limit + 1;
	pagenum = 1;
	WHILE pagenum <= lastpage;
	IF pagenum <= 4
	\|\| (pagenum <= (curpage + 3) && pagenum >= (curpage - 3))
	\|\| pagenum >= (lastpage - 3);
	curskip = (pagenum - 1) * limit;
	INCLUDE onepage skip = curskip;
	pagenum = pagenum + 1;
	ELSE;
	%]...[%
	pagenum = pagenum < curpage ? (curpage - 3) : (lastpage - 3);
	END;
	END;
	%]
	[% BLOCK onepage %]
	[% IF pagenum == curpage %]
	<b>[% pagenum %]</b>
	[% ELSE %]
	<a href="[% script_name %]?find=[% find %]&limit=[% limit %]&skip=[% skip %]">[% pagenum %]</a>
	[% END %]
	[% END %]
	CREATE TABLE phones (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	name CHAR(12) NOT NULL,
	phone CHAR(11) NOT NULL,
	created INTEGER -- it's unix time in SQLite
	);

	CREATE INDEX "phones_idx" ON phones(phone);

	CREATE TRIGGER on_new_phone AFTER INSERT ON phones BEGIN
	UPDATE phones
	SET created = strftime('%s','now') - 3000 + NEW.id % 1000
	WHERE phones.id = NEW.id;
	END;