jberger/websocket_dbi.pl

## websocket_dbi.pl
#!/usr/bin/env perl

use Mojolicious::Lite;
use Mojo::JSON 'j';

# connect to database, store connector in attribute
use DBIx::Connector;
app->attr( connector => sub {
  DBIx::Connector->new("dbi:SQLite:database.db","","", {sqlite_unicode => 1});
});

# add helper methods for interacting with database
helper db => sub { shift->app->connector->dbh };

helper create_table => sub {
  my $self = shift;
  warn "Creating table 'people'\n";
  $self->db->do('CREATE TABLE people (name varchar(255), age int);');
};

helper select => sub {
  my $self = shift;
  my $sth = eval { $self->db->prepare('SELECT * FROM people') } || return undef;
  $sth->execute;
  return $sth->fetchall_arrayref;
};

helper insert => sub {
  my $self = shift;
  my ($name, $age) = @_;
  my $sth = $self->db->prepare('INSERT INTO people VALUES (?,?)');
  return defined $sth->execute($name, $age)
    ? { success => 1, message => 'Insert successful' }
    : { success => 0, message => $sth->errstr };
};

# if statement didn't prepare, assume its because the table doesn't exist
app->select || app->create_table;

# setup base route
any '/' => sub {
  my $self = shift;
  my $rows = $self->select;
  $self->render('index', rows => $rows );
};

# setup websocket message handler
websocket '/insert' => sub {
  my $self = shift;
  $self->on( text => sub {
    my ($ws, $text) = @_;
    my $row = j($text);

    my $db_res = $self->insert(@$row);

    if ( $db_res->{success} ) {
      $db_res->{html} = $ws->render( 'table', rows => [$row], partial => 1 );
    }

    $ws->send({ text => j($db_res) });

  });
};

app->start;

__DATA__

@@ index.html.ep

<!DOCTYPE html>
<html>
<head>
  <title>People</title>
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
</head>
<body>
  <div>
    Name: <input type="text" id="name">
    Age: <input type="text" id="age" >
    <input type="submit" value="Add" onclick="insert()">
  </div>
  <br>
  Data: <br>
  <table border="1">
    <thead>
      <tr><th>Name</th><th>Age</th></tr>
    </thead>
    <tbody id="table">
      %= include 'table'
    </tbody>
  </table>
  %= javascript begin
    function insert () {
      if (!("WebSocket" in window)) {
        alert('Your browser does not support WebSockets!');
        return;
      }
      var ws = new WebSocket("<%= url_for('insert')->to_abs %>");
      ws.onopen = function () {
        var name = $('#name');
        var age = $('#age');
        ws.send(JSON.stringify([name.val(),age.val()]));
        name.val('');
        age.val('');
      };
      ws.onmessage = function (evt) {
        var data = JSON.parse(evt.data);
        if ( data.success ) {
          $('#table').append(data.html);
        }
        console.log( data.message );
      };
    }
  %= end
</body>
</html>

@@ table.html.ep

% foreach my $row (@$rows) {
  <tr>
    % foreach my $text (@$row) {
      <td><%= $text %></td>
    % }
  </tr>
% }
	#!/usr/bin/env perl

	use Mojolicious::Lite;
	use Mojo::JSON 'j';

	# connect to database, store connector in attribute
	use DBIx::Connector;
	app->attr( connector => sub {
	DBIx::Connector->new("dbi:SQLite:database.db","","", {sqlite_unicode => 1});
	});

	# add helper methods for interacting with database
	helper db => sub { shift->app->connector->dbh };

	helper create_table => sub {
	my $self = shift;
	warn "Creating table 'people'\n";
	$self->db->do('CREATE TABLE people (name varchar(255), age int);');
	};

	helper select => sub {
	my $self = shift;
	my $sth = eval { $self->db->prepare('SELECT * FROM people') } \|\| return undef;
	$sth->execute;
	return $sth->fetchall_arrayref;
	};

	helper insert => sub {
	my $self = shift;
	my ($name, $age) = @_;
	my $sth = $self->db->prepare('INSERT INTO people VALUES (?,?)');
	return defined $sth->execute($name, $age)
	? { success => 1, message => 'Insert successful' }
	: { success => 0, message => $sth->errstr };
	};

	# if statement didn't prepare, assume its because the table doesn't exist
	app->select \|\| app->create_table;

	# setup base route
	any '/' => sub {
	my $self = shift;
	my $rows = $self->select;
	$self->render('index', rows => $rows );
	};

	# setup websocket message handler
	websocket '/insert' => sub {
	my $self = shift;
	$self->on( text => sub {
	my ($ws, $text) = @_;
	my $row = j($text);

	my $db_res = $self->insert(@$row);

	if ( $db_res->{success} ) {
	$db_res->{html} = $ws->render( 'table', rows => [$row], partial => 1 );
	}

	$ws->send({ text => j($db_res) });

	});
	};

	app->start;

	__DATA__

	@@ index.html.ep

	<!DOCTYPE html>
	<html>
	<head>
	<title>People</title>
	<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
	</head>
	<body>
	<div>
	Name: <input type="text" id="name">
	Age: <input type="text" id="age" >
	<input type="submit" value="Add" onclick="insert()">
	</div>
	<br>
	Data: <br>
	<table border="1">
	<thead>
	<tr><th>Name</th><th>Age</th></tr>
	</thead>
	<tbody id="table">
	%= include 'table'
	</tbody>
	</table>
	%= javascript begin
	function insert () {
	if (!("WebSocket" in window)) {
	alert('Your browser does not support WebSockets!');
	return;
	}
	var ws = new WebSocket("<%= url_for('insert')->to_abs %>");
	ws.onopen = function () {
	var name = $('#name');
	var age = $('#age');
	ws.send(JSON.stringify([name.val(),age.val()]));
	name.val('');
	age.val('');
	};
	ws.onmessage = function (evt) {
	var data = JSON.parse(evt.data);
	if ( data.success ) {
	$('#table').append(data.html);
	}
	console.log( data.message );
	};
	}
	%= end
	</body>
	</html>

	@@ table.html.ep

	% foreach my $row (@$rows) {
	<tr>
	% foreach my $text (@$row) {
	<td><%= $text %></td>
	% }
	</tr>
	% }