Kanben view in RT 4.2.12

dear rt-developers,

we want to add a kanban view for tickets in RT but in order to do that a
REST interface with websocket support is required.

the current 1.0 interface does not support JSON (will be fixed with the
2.0 REST API AFAIK) which means we have to create java-script objects
from the input in the browser manually (without being able to use
parseJSON(…))

our initial idea is to use mysql with triggers:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

as this seems to be an easy way to monitor the tickets table for changes
and then note these ticket IDs down.

the websocket REST-interface then sees all attached clients and every
time a ticket changes it can push the ticket ID(s) to the client and the
client then can apply the change.

=== the question ===

  • where in the RT code would be the best place to add the WEBSOCKET REST
    extension (into the routing) + it needs an main loop waiting for
    ’ticket’-table changes
  • how to make this a general solution: instead of binding this feature
    to MYSQL we could also extend the ticket API with various callbacks on:
  • add/remove/update ticket functionality
    === /the question ===

we plan to release the kanban as open source once we are done with the
implementation.

if you could help us out with implementation details, that’d be a delight.

thanks in advance,
joachim & paul

dear rt-developers,

Hi Joachim and Paul,

we want to add a kanban view for tickets in RT but in order to do that a
REST interface with websocket support is required.

Sounds great! :slight_smile:

the current 1.0 interface does not support JSON (will be fixed with the
2.0 REST API AFAIK)

That’s correct.

which means we have to create java-script objects
from the input in the browser manually (without being able to use
parseJSON(…))

our initial idea is to use mysql with triggers:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

as this seems to be an easy way to monitor the tickets table for changes
and then note these ticket IDs down.

Please be aware that you’re limiting the potential audience of your extension by tying it to any specific database engine. RT has many users on Postgres and Oracle as well.

My suggestion would be to add the notification system at the ORM layer. Probably in the guts of RT (RT::Record, RT::SearchBuilder) or possibly in the generic ORM, DBIx::SearchBuilder. Changes to records in the database pass through a very small list of Perl methods (in particular, RT::Record::_Set), so by instrumenting the right place you should be able to see all changes happening.

the websocket REST-interface then sees all attached clients and every
time a ticket changes it can push the ticket ID(s) to the client and the
client then can apply the change.

=== the question ===

  • where in the RT code would be the best place to add the WEBSOCKET REST
    extension (into the routing) + it needs an main loop waiting for
    ’ticket’-table changes

You’ll probably want to use Plack::App::WebSocket with an event-based PSGI server (probably Twiggy). RT is typically deployed with FastCGI or similar, so you have your work cut out for you there. :slight_smile: You’ll either want to have good documentation around switching an RT deployment from FastCGI to Twiggy, or have your users deploy a standalone Twiggy server alongside their RT http server.

  • how to make this a general solution: instead of binding this feature
    to MYSQL we could also extend the ticket API with various callbacks on:
  • add/remove/update ticket functionality

Instrumenting RT::Record::Create and RT::Record::_Set generally, or RT::ticket::Create and RT::ticket::_Set specifically, should get you most of the way there.

=== /the question ===

we plan to release the kanban as open source once we are done with the
implementation.

if you could help us out with implementation details, that’d be a delight.

Absolutely! This is the best forum for such conversations. :slight_smile:

thanks in advance,
joachim & paul

Thanks!
Shawn

dear rt-developers,

Hi Joachim and Paul,

we want to add a kanban view for tickets in RT but in order to do that a
REST interface with websocket support is required.

Sounds great! :slight_smile:

this is our current state:

teaser:
https://lastlog.de/misc/rt4.2-kanban.jpg

  • uses REST 1.0 to get the data
  • jQuery UI for the GUI
  • moving tickets effects its state
  • tickets can be assigned to different users (dropdown)
  • ticket priority affects the vertical position
  • completely embedded in RT 4.2.12 (mason template)

the current 1.0 interface does not support JSON (will be fixed with the
2.0 REST API AFAIK)

That’s correct.

which means we have to create java-script objects
from the input in the browser manually (without being able to use
parseJSON(…))

our initial idea is to use mysql with triggers:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

as this seems to be an easy way to monitor the tickets table for changes
and then note these ticket IDs down.

Please be aware that you’re limiting the potential audience of your extension by tying it to any specific database engine. RT has many users on Postgres and Oracle as well.

My suggestion would be to add the notification system at the ORM layer. Probably in the guts of RT (RT::Record, RT::SearchBuilder) or possibly in the generic ORM, DBIx::SearchBuilder. Changes to records in the database pass through a very small list of Perl methods (in particular, RT::Record::_Set), so by instrumenting the right place you should be able to see all changes happening.

yes, this is sadly true. but for the moment we will be going with MySQL
and TRIGGER. but once we got the websocket-backend integrated properly
we might look into this again.

the websocket REST-interface then sees all attached clients and every
time a ticket changes it can push the ticket ID(s) to the client and the
client then can apply the change.

=== the question ===

  • where in the RT code would be the best place to add the WEBSOCKET REST
    extension (into the routing) + it needs an main loop waiting for
    ‘ticket’-table changes

You’ll probably want to use Plack::App::WebSocket with an event-based PSGI server (probably Twiggy). RT is typically deployed with FastCGI or similar, so you have your work cut out for you there. :slight_smile: You’ll either want to have good documentation around switching an RT deployment from FastCGI to Twiggy, or have your users deploy a standalone Twiggy server alongside their RT http server.

hm. this leaves me with three more questions:

  • could you please point out what i need to do in order to use twiggy
    instead of ‘the usual’ deployment method?

regarding WS:
i’ve been playing with Plack::App::WebSocket a lot. mainly with this
example:

and i’m currently having trouble with two things:

  • how to extend the example to do a mysql query every second and then
    send data to all clients (i extended the example so every incoming
    string is sent to all clients already)?

    something like this:
    my $w = AnyEvent->timer (after => 3, cb => sub { foo });

    but without this:
    AnyEvent::loop::run;
    the timer will never be executed. and if i start the loop with the
    above code the webserver won’t be spinning.

  • looking into the rt-extension-rest2 i wonder how to put the above
    example code into
    https://github.com/bestpractical/rt-extension-rest2/blob/943d8f69ef8e1a0e6d6615fc4f92df0d3fde3cf2/lib/RTx/REST.pm

  • how to make this a general solution: instead of binding this feature
    to MYSQL we could also extend the ticket API with various callbacks on:
  • add/remove/update ticket functionality

Instrumenting RT::Record::Create and RT::Record::_Set generally, or RT::ticket::Create and RT::ticket::_Set specifically, should get you most of the way there.

that is very good information! but i would have to ‘modify’ the RT-Core
as this can’t be done from an extension, right?

=== /the question ===

we plan to release the kanban as open source once we are done with the
implementation.

if you could help us out with implementation details, that’d be a delight.

Absolutely! This is the best forum for such conversations. :slight_smile:

very good!

thanks in advance,
joachim & paul

Thanks!
Shawn

thanks very much for your help! it really is appreciated.

  • where in the RT code would be the best place to add the WEBSOCKET REST
    extension (into the routing) + it needs an main loop waiting for
    ’ticket’-table changes

You’ll probably want to use Plack::App::WebSocket with an event-based PSGI server (probably Twiggy). RT is typically deployed with FastCGI or similar, so you have your work cut out for you there. :slight_smile: You’ll either want to have good documentation around switching an RT deployment from FastCGI to Twiggy, or have your users deploy a standalone Twiggy server alongside their RT http server.

hm. this leaves me with three more questions:

  • could you please point out what i need to do in order to use twiggy
    instead of ‘the usual’ deployment method?

right now i call the server like this:

plackup /tmp/rt4/opt/rt4/sbin/standalone_httpd --port 8080
Twiggy: Accepting connections at http://0.0.0.0:8080/

so this is probably answered now. can i run the server like this in
production?

i had a lengthily discussion about this on irc#perl:

(23:56) < mst> well, you could run the two side by side
(23:56) < mst> you should be running two daemons anyway
(23:56) < mst> you don’t want your websocket handler in the same
process as the main RT code

as well as:

(23:58) < hobbs> yeah. Even if you switch the server, RT will do all
kinds of things that will jam up the loop and make your websockets useless
(23:58) < hobbs> or at least perform very badly

any comment on using ‘plack’ instead of ‘psgi’/‘fcgi’ with websockets
implemented like shown in Plack::App::WebSocket?

regarding WS:
i’ve been playing with Plack::App::WebSocket a lot. mainly with this
example:
https://github.com/motemen/Plack-Middleware-WebSocket/blob/master/eg/echo/app.psgi

and i’m currently having trouble with two things:

  • how to extend the example to do a mysql query every second and then
    send data to all clients (i extended the example so every incoming
    string is sent to all clients already)?

    something like this:
    my $w = AnyEvent->timer (after => 3, cb => sub { foo });

    but without this:
    AnyEvent::loop::run;
    the timer will never be executed. and if i start the loop with the
    above code the webserver won’t be spinning.

  • looking into the rt-extension-rest2 i wonder how to put the above
    example code into
    https://github.com/bestpractical/rt-extension-rest2/blob/943d8f69ef8e1a0e6d6615fc4f92df0d3fde3cf2/lib/RTx/REST.pm

got that working, too. was actually pretty simple:
i just had to add a mount “/websocket” like this:

Called by RT::Interface::Web::Handler->PSGIApp

sub PSGIWrap {
print STDERR “REST2.pm: qknight was here: sub PSGIWrap”, “\n\n”;
my ($class, $app) = @;
return builder {
mount $REST_PATH => $class->to_app;
mount ‘/’ => $app;
mount “/websocket” => Plack::App::WebSocket->new(
on_error => sub {
my $env = shift;
print STDERR “plack_app_websocket.psgi: qknight was here:
/websocket on_error”, “\n\n”;
return [500,
[“Content-Type” => “text/plain”],
["Error: " .
$env->{“plack.app.websocket.error”}]];
},
on_establish => sub {
print STDERR “plack_app_websocket.psgi: qknight was here:
/on_established”, “\n\n”;
my $conn = shift; ##
Plack::App::WebSocket::Connection object
my $env = shift; ## PSGI env
push(@WSConnections, $conn);
$conn->on(
message => sub {
my ($conn, $msg) = @
;
print STDERR “plack_app_websocket.psgi: qknight was here:
message: $msg”, “\n\n”;
foreach (@WSConnections) {
$->send($msg);
}
},
finish => sub {
# most epic remove function ever OMFG
(qknight)
my @l;
foreach(@WSConnections) {
if ($
!= $conn) {
push(@l, $_)
}
}
@WSConnections = @l;
undef $conn;
warn “Bye!!\n”;
},
);
}
)->to_app;
};
}

since my @WSConnections; is a global object i can now send messages to
all attached clients yay!

with using a singleton, like shown here:
http://search.cpan.org/~abw/Class-Singleton-1.03/Singleton.pm#DERIVING_SINGLETON_CLASSES

i should be able to create a perl based WSClass which can be used from
all the RT-codebase as:

  • Instrumenting RT::Record::Create and
  • RT::Record::_Set generally, or
  • RT::ticket::Create and
  • RT::ticket::_Set specifically

as you pointed out in order to circumvent the MySQL-only solution.

  • where in the RT code would be the best place to add the WEBSOCKET REST
    extension (into the routing) + it needs an main loop waiting for
    ’ticket’-table changes
    You’ll probably want to use Plack::App::WebSocket with an event-based PSGI server (probably Twiggy). RT is typically deployed with FastCGI or similar, so you have your work cut out for you there. :slight_smile: You’ll either want to have good documentation around switching an RT deployment from FastCGI to Twiggy, or have your users deploy a standalone Twiggy server alongside their RT http server.
    hm. this leaves me with three more questions:
  • could you please point out what i need to do in order to use twiggy
    instead of ‘the usual’ deployment method?
    right now i call the server like this:

plackup /tmp/rt4/opt/rt4/sbin/standalone_httpd --port 8080
Twiggy: Accepting connections at http://0.0.0.0:8080/

so this is probably answered now. can i run the server like this in
production?
The standalone server only runs one process so you’ll likely have
performance issues running that way in production, depending on the size
of production and number of users.
i had a lengthily discussion about this on irc#perl:

(23:56) < mst> well, you could run the two side by side
(23:56) < mst> you should be running two daemons anyway
(23:56) < mst> you don’t want your websocket handler in the same
process as the main RT code

as well as:

(23:58) < hobbs> yeah. Even if you switch the server, RT will do all
kinds of things that will jam up the loop and make your websockets useless
(23:58) < hobbs> or at least perform very badly

any comment on using ‘plack’ instead of ‘psgi’/‘fcgi’ with websockets
implemented like shown in Plack::App::WebSocket?

regarding WS:
i’ve been playing with Plack::App::WebSocket a lot. mainly with this
example:
https://github.com/motemen/Plack-Middleware-WebSocket/blob/master/eg/echo/app.psgi

and i’m currently having trouble with two things:

  • how to extend the example to do a mysql query every second and then
    send data to all clients (i extended the example so every incoming
    string is sent to all clients already)?

    something like this:
    my $w = AnyEvent->timer (after => 3, cb => sub { foo });

    but without this:
    AnyEvent::loop::run;
    the timer will never be executed. and if i start the loop with the
    above code the webserver won’t be spinning.

  • looking into the rt-extension-rest2 i wonder how to put the above
    example code into
    https://github.com/bestpractical/rt-extension-rest2/blob/943d8f69ef8e1a0e6d6615fc4f92df0d3fde3cf2/lib/RTx/REST.pm
    got that working, too. was actually pretty simple:
    i just had to add a mount “/websocket” like this:

Called by RT::Interface::Web::Handler->PSGIApp

sub PSGIWrap {
print STDERR “REST2.pm: qknight was here: sub PSGIWrap”, “\n\n”;
my ($class, $app) = @;
return builder {
mount $REST_PATH => $class->to_app;
mount ‘/’ => $app;
mount “/websocket” => Plack::App::WebSocket->new(
on_error => sub {
my $env = shift;
print STDERR “plack_app_websocket.psgi: qknight was here:
/websocket on_error”, “\n\n”;
return [500,
[“Content-Type” => “text/plain”],
["Error: " .
$env->{“plack.app.websocket.error”}]];
},
on_establish => sub {
print STDERR “plack_app_websocket.psgi: qknight was here:
/on_established”, “\n\n”;
my $conn = shift; ##
Plack::App::WebSocket::Connection object
my $env = shift; ## PSGI env
push(@WSConnections, $conn);
$conn->on(
message => sub {
my ($conn, $msg) = @
;
print STDERR “plack_app_websocket.psgi: qknight was here:
message: $msg”, “\n\n”;
foreach (@WSConnections) {
$->send($msg);
}
},
finish => sub {
# most epic remove function ever OMFG
(qknight)
my @l;
foreach(@WSConnections) {
if ($
!= $conn) {
push(@l, $_)
}
}
@WSConnections = @l;
undef $conn;
warn “Bye!!\n”;
},
);
}
)->to_app;
};
}

since my @WSConnections; is a global object i can now send messages to
all attached clients yay!

with using a singleton, like shown here:
http://search.cpan.org/~abw/Class-Singleton-1.03/Singleton.pm#DERIVING_SINGLETON_CLASSES

i should be able to create a perl based WSClass which can be used from
all the RT-codebase as:

  • Instrumenting RT::Record::Create and
  • RT::Record::_Set generally, or
  • RT::ticket::Create and
  • RT::ticket::_Set specifically

as you pointed out in order to circumvent the MySQL-only solution.
Another thing to think about is that RT has a concept of transactions
itself outside of the DB. All ticket updates are made in the context of
a transaction and for RT to function properly changes need to run
through that. So for updates from the client -> server, you need to make
sure transactions run so things like scrips work.

Transactions might also help for the server -> client updates. You could
tap into the transaction process (maybe even with a scrip?) to send out
all transactions on the websocket. The client code can then be smart
enough to inspect all incoming transactions and update the internal
view/model if the change applies to a ticket that view is managing. (If
you have different views based on queue or kanban board, some
transactions may not be applicable.)

Not sure if this helps or not, just something to consider.

  • how to make this a general solution: instead of binding this feature
    to MYSQL we could also extend the ticket API with various callbacks on:
  • add/remove/update ticket functionality
    Instrumenting RT::Record::Create and RT::Record::_Set generally, or RT::ticket::Create and RT::ticket::_Set specifically, should get you most of the way there.
    that is very good information! but i would have to ‘modify’ the RT-Core
    as this can’t be done from an extension, right?
    Yes, extensions can “modify” RT code by overlaying it. If an extension
    provides it’s own version of an RT file at the same path inside
    local/pluging/RT-Extension… then RT will use the extension’s version
    of the file. You can use this to change just individual subroutines in
    perl code or overlay the whole file.
  • where in the RT code would be the best place to add the WEBSOCKET
    REST
    extension (into the routing) + it needs an main loop waiting for
    ’ticket’-table changes
    You’ll probably want to use Plack::App::WebSocket with an
    event-based PSGI server (probably Twiggy). RT is typically deployed
    with FastCGI or similar, so you have your work cut out for you
    there. :slight_smile: You’ll either want to have good documentation around
    switching an RT deployment from FastCGI to Twiggy, or have your
    users deploy a standalone Twiggy server alongside their RT http server.
    hm. this leaves me with three more questions:
  • could you please point out what i need to do in order to use twiggy
    instead of ‘the usual’ deployment method?
    right now i call the server like this:

plackup /tmp/rt4/opt/rt4/sbin/standalone_httpd --port 8080
Twiggy: Accepting connections at http://0.0.0.0:8080/

so this is probably answered now. can i run the server like this in
production?
The standalone server only runs one process so you’ll likely have
performance issues running that way in production, depending on the size
of production and number of users.
i had a lengthily discussion about this on irc#perl:

(23:56) < mst> well, you could run the two side by side
(23:56) < mst> you should be running two daemons anyway
(23:56) < mst> you don’t want your websocket handler in the same
process as the main RT code

as well as:

(23:58) < hobbs> yeah. Even if you switch the server, RT will do all
kinds of things that will jam up the loop and make your websockets
useless
(23:58) < hobbs> or at least perform very badly

any comment on using ‘plack’ instead of ‘psgi’/‘fcgi’ with websockets
implemented like shown in Plack::App::WebSocket?

regarding WS:
i’ve been playing with Plack::App::WebSocket a lot. mainly with this
example:
https://github.com/motemen/Plack-Middleware-WebSocket/blob/master/eg/echo/app.psgi

and i’m currently having trouble with two things:

  • how to extend the example to do a mysql query every second and then
    send data to all clients (i extended the example so every incoming
    string is sent to all clients already)?

    something like this:
    my $w = AnyEvent->timer (after => 3, cb => sub { foo });

    but without this:
    AnyEvent::loop::run;
    the timer will never be executed. and if i start the loop with the
    above code the webserver won’t be spinning.

  • looking into the rt-extension-rest2 i wonder how to put the above
    example code into
    https://github.com/bestpractical/rt-extension-rest2/blob/943d8f69ef8e1a0e6d6615fc4f92df0d3fde3cf2/lib/RTx/REST.pm

got that working, too. was actually pretty simple:
i just had to add a mount “/websocket” like this:

Called by RT::Interface::Web::Handler->PSGIApp

sub PSGIWrap {
print STDERR “REST2.pm: qknight was here: sub PSGIWrap”, “\n\n”;
my ($class, $app) = @;
return builder {
mount $REST_PATH => $class->to_app;
mount ‘/’ => $app;
mount “/websocket” => Plack::App::WebSocket->new(
on_error => sub {
my $env = shift;
print STDERR “plack_app_websocket.psgi: qknight was here:
/websocket on_error”, “\n\n”;
return [500,
[“Content-Type” => “text/plain”],
["Error: " .
$env->{“plack.app.websocket.error”}]];
},
on_establish => sub {
print STDERR “plack_app_websocket.psgi: qknight was here:
/on_established”, “\n\n”;
my $conn = shift; ##
Plack::App::WebSocket::Connection object
my $env = shift; ## PSGI env
push(@WSConnections, $conn);
$conn->on(
message => sub {
my ($conn, $msg) = @
;
print STDERR “plack_app_websocket.psgi: qknight was here:
message: $msg”, “\n\n”;
foreach (@WSConnections) {
$->send($msg);
}
},
finish => sub {
# most epic remove function ever OMFG
(qknight)
my @l;
foreach(@WSConnections) {
if ($
!= $conn) {
push(@l, $_)
}
}
@WSConnections = @l;
undef $conn;
warn “Bye!!\n”;
},
);
}
)->to_app;
};
}

since my @WSConnections; is a global object i can now send messages to
all attached clients yay!

with using a singleton, like shown here:

http://search.cpan.org/~abw/Class-Singleton-1.03/Singleton.pm#DERIVING_SINGLETON_CLASSES

i should be able to create a perl based WSClass which can be used from
all the RT-codebase as:

  • Instrumenting RT::Record::Create and
  • RT::Record::_Set generally, or
  • RT::ticket::Create and
  • RT::ticket::_Set specifically

as you pointed out in order to circumvent the MySQL-only solution.
Another thing to think about is that RT has a concept of transactions
itself outside of the DB. All ticket updates are made in the context of
a transaction and for RT to function properly changes need to run
through that. So for updates from the client -> server, you need to make
sure transactions run so things like scrips work.

Transactions might also help for the server -> client updates. You could
tap into the transaction process (maybe even with a scrip?) to send out
all transactions on the websocket. The client code can then be smart
enough to inspect all incoming transactions and update the internal
view/model if the change applies to a ticket that view is managing. (If
you have different views based on queue or kanban board, some
transactions may not be applicable.)

Not sure if this helps or not, just something to consider.

  • how to make this a general solution: instead of binding this feature
    to MYSQL we could also extend the ticket API with various callbacks
    on:
  • add/remove/update ticket functionality
    Instrumenting RT::Record::Create and RT::Record::_Set generally, or
    RT::ticket::Create and RT::ticket::_Set specifically, should get you
    most of the way there.
    that is very good information! but i would have to ‘modify’ the RT-Core
    as this can’t be done from an extension, right?
    Yes, extensions can “modify” RT code by overlaying it. If an extension
    provides it’s own version of an RT file at the same path inside
    local/pluging/RT-Extension… then RT will use the extension’s version
    of the file. You can use this to change just individual subroutines in
    perl code or overlay the whole file.

i’ve got some updates:

lib/RT/Ticket.pm was extended with:
system(“redis-cli”, “PUBLISH”, “rt-ticket-activity”, $self->Id);

in both:

  • _Set()
  • Create()

as you said. since there can be 4 set calls within 200ms we developed a
normalizer making it just one event for the UI! first we were using perl
abstractions over redis but they caused problems when redis was
restarted, thus requiring RT to be restarted which would be stupid.

oh, talking of ‘redis’: we adapted ‘redis’ for webserver IPC and we
created a second webserver implemented in mojolicious, which is a very
very nice application framework!

auth is now implemented like this:

  1. a client visits RT and issues a login
  2. once a Kanban view is started, a WS connection is made to the newly
    created second webserver written in mojolicious
  3. seen from the client both webservers are appearing as one, so we get
    the same cookies on both ends. now all we have to do is to check if any
    of the supplied cookies are valid. this is done by making a REST call to
    the RT-webserver. if that reports no error, then the /webserver context
    is established.

better ideas are welcome.

we thought about giving the second webserver permission to the database
but the REST query solution could be better in security regards.

with some luck we will release the kanban-plugin + webserver extension +
documentation in about 7 days from now.

thanks for all your support, which helped us very much!

regards,
joachim & paul