#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=users.lite","","",
	{PrintError => 1, AutoCommit => 0}) or die "Can't connect";

$dbh->do('DROP TABLE users');
$dbh->do(qq{ CREATE TABLE users
			(user TEXT,
			 site TEXT,
			 type TEXT)
			});

my $sql_fmt = "INSERT INTO users VALUES(?,?,?)";
while(<DATA>) {
    $dbh->do($sql_fmt, {}, /"([^"]+)"/g);
    $dbh->commit if $. % 1_000_000 == 0; # commit every 1,000,000
}

$dbh->commit;
$dbh->disconnect;

__DATA__
user="john" website="www.yahoo.com" type="Entertainment"
user="john" website="www.yahoo.com" type="Entertainment"
user="john" website="www.yahoo.com" type="Entertainment"
user="david" website="www.facebook.com" type="Social Networking"
user="john" website="www.facebook.com" type="Social Networking"
user="mike" website="www.google.com" type="Search Engines"

##</code><code>##

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=users.lite","","",
	{PrintError => 1, AutoCommit => 0}) or die "Can't connect";

# Prepare and print list of all websites to every user
my $sth = $dbh->prepare(<<SQL);
SELECT *
FROM users
ORDER BY user, site
SQL

$sth->execute;
while(my @row = $sth->fetchrow_array) {
	printf "%-15s%-20s%s\n", @row;
}
print "\n";

# Create list of users from most visits to least for @users array
$sth = $dbh->prepare(<<SQL);
SELECT user, COUNT(user) Count
FROM users
GROUP BY user
ORDER BY Count DESC, user
SQL

$sth->execute;
my @users;
while(my @row = $sth->fetchrow_array) {
	push @users, $row[0];
}

# Counts for each website and counts of categories visited by user
for my $user (@users) {
	$sth = $dbh->prepare(qq{SELECT site, COUNT(site) Count
							FROM users
							WHERE user = '$user'
							GROUP BY site
							ORDER BY Count DESC
					});
	$sth->execute;
	printf "Name: %s\n\t%-20s%s\n", $user, qw/ Website Count /;
	while(my @row = $sth->fetchrow_array) {
		printf "\t%-20s%s\n", @row;
	}
	print "\n";
	
	printf "\t%-20s%s\n", qw/ Category Count /;
	$sth = $dbh->prepare(qq{SELECT type, COUNT(type) Count
							FROM users
							WHERE user = '$user'
							GROUP BY type
							ORDER BY Count DESC
					});
	$sth->execute;
	while(my @row = $sth->fetchrow_array) {
		printf "\t%-20s%s\n", @row;
	}
	print "\n";
}

$dbh->disconnect;