Roku Developer Program

[jbrave]

Hello,

I want to create an automatic facility to report urls containing corrupted m4a's in my app, for now, storing them to my webserver. I just am not sure what the best method of doing this might be. Its a Yahoo webserver so minimal features functions, although supposedly it supports php5.

Any suggestions on approaches? If I could just use a webpage and send a url like http://myserver.com&url="http://blabla.com/1122.m4a" but how to parse and store that info. Or If I create a web form, could I somehow post that form and store the result on my server?

- Joel

[kbenson]

Does it support MySQL, CGI and Perl? If so, I'll throw something your way, including a MySQL schema. If not, maybe I'll code something in PHP to match the schema. It's dead simple.

If you don't have access to MySQL, then your options pretty much suck all around anyway (I guess you could use the built in SQLite functionality in PHP5).

[jbrave]

Hi, I'll find out!

- Joel

[jbrave]

Ok, here we go:

# PHP 5
# Perl 5.8.7
# MySQL 4.1

[kbenson]

The perl code required an external (but very small) perl Module, so I figured it's probably less trouble to just rewrite it in PHP quickly. It's not like it's complex or anything.
Here's the script:

<?php

// Define settings
$dbserver = "localhost"; // Change as required
$dbname   = "myRokuDb";  // Whatever DB you have access to
$dbuser   = "someuser";  // User to connect to DB as
$dbpass   = "dbpassword";// Password for DB user

// Connect to DB
$dbconn = mysql_connect($dbserver, $dbuser, $dbpass);
if (!$dbconn) die("Error connecting to database!");
if (!mysql_select_db($dbname)) die("Error selecting database $dbname");

// Get args
$channel = !empty($_REQUEST['channel']) ? mysql_real_escape_string($_REQUEST['channel']) : "";
$uuid    = !empty($_REQUEST['uuid'])    ? mysql_real_escape_string($_REQUEST['uuid'])    : "";
$key     = !empty($_REQUEST['key'])     ? mysql_real_escape_string($_REQUEST['key'])     : "";
$value   = !empty($_REQUEST['value'])   ? mysql_real_escape_string($_REQUEST['value'])   : "";
$ip      = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);

// Verify args
if (empty($channel)) die("Missing or invalid paramater 1");
if (empty($uuid) or !preg_match('/^[0-9a-f]{32}$/i',$uuid)) die("Missing or invalid paramater 2");
if (empty($key)) die("Missing or invalid paramater 3");
if (empty($value)) die("Missing or invalid paramater 4");

// Store data
$insert_sql = "INSERT INTO storedata (`time`, `channel`, `ip`, `uuid`, `key`, `value`) VALUES (NOW(),'$channel','$ip','$uuid','$key','$value')";
$result = mysql_query($insert_sql); // Stupid default PHP mysql functions without binding...
if (!$result) die("Error saving data!");

print "Data saved";
?>

Drop that into a .php file on your webhost, and configure your database


Here's the table schema:

CREATE TABLE `storedata` (
  `id` bigint(20) NOT NULL auto_increment,
  `channel` varchar(20) NOT NULL default 'unspecified',
  `ip` varchar(15) NOT NULL default '0.0.0.0',
  `uuid` char(32) NOT NULL default '',
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `key` varchar(255) NOT NULL default '',
  `value` blob NOT NULL,
  PRIMARY KEY  (`id`)
);

You'll need to generate a random uuid string in the channel to use this, which this can do:

uuid = ""
for i=1 to 32
        o = RND(16)
        if o <= 10
                o = o + 47
        else
                o = o + 96 - 10
        end if
        uuid = uuid + CHR(o)
end for

(No, it's not a correct UUID)
I generally generate one per run of a channel, so subsequent requests to the data storage script from the same channel run can be identified. You can omit this and just make uuid = STRING(32,"0") for a 32 character string of zeroes.

The actual submission looks something like this on mine:

http = CreateObject("roUrlTransfer")
http.setUrl(YOUR_SCRIPT_URL_HERE)
http.InitClientCertificates() ' if https without a real cert
http.EnablePeerVerification(false) ' if https without a real cert 
http.EnableHostVerification(false) ' if https without a real cert
sPostData = "channel=fontsizer&uuid="+http.urlEncode(uuid)+"&key="+http.urlEncode("f:"+sFont+sDefinition)+"&value="+http.urlEncode(sFS)
http.postFromString(sPostData)

Of course, change the key and value portion of the post data before posting.

Whew.

[jbrave]

Wow, thanks for all that! What is the UUID for, is it just to prevent simultanious access to the same record?

- Joel

[kbenson]

"jbrave" wrote:
Wow, thanks for all that! What is the UUID for, is it just to prevent simultanious access to the same record?

Simply a unique identifier per channel run. I generate it on channel start, and use the same one until the channel stops. You can then link together records submitted by the same run of the channel (same user is implied by that). I used a similar method when storing duration counters for KidPaint (which this was hastily adapted from originally). The time counter kept advancing, but as long as the UUID was the same, I could tell it was the same run of the channel. I could also do a search for unique UUIDs to determine how many different runs of the channel there were. Just using the IP address isn't sufficient, as many people have multiple Rokus on the same public IP, and many ISPs provide proxies which may lump many requests to the same IP.

[jbrave]

I've been working on this for quite a few hours now - now I no longer get database connect errors, but I'm getting an "error saving data" output from the script. At the moment, i"m just using curl -d to post from the command line. Nothing is being inserted into the database, I've got the dbname and the table name correct.

The post:

curl -d "channel=mychannelname&uuid=13594446900000&key=195158&value=http%3A%2F%2Fasdf.s3.amazonaws.com%2Ftf%2F001%2F396%2F124%2F30204.64k.m4a" http://www.myserver.com/badm4a.php

Error saving data!

<?php

// Define settings
$dbserver = "mysql"; // Change as required
$dbname   = "mydbname";  // Whatever DB you have access to
$dbuser   = "myusername";  // User to connect to DB as
$dbpass   = "mypassword";// Password for DB user

// Connect to DB
$dbconn = mysql_connect("mysql","myusername","mypassword");
if (!$dbconn) die("Error connecting to database!");
if (!mysql_select_db($dbname)) die("Error selecting database $dbname");

// Get args
$channel = !empty($_REQUEST['channel']) ? mysql_real_escape_string($_REQUEST['channel']) : "";
$uuid    = !empty($_REQUEST['uuid'])    ? mysql_real_escape_string($_REQUEST['uuid'])    : "";
$key     = !empty($_REQUEST['key'])     ? mysql_real_escape_string($_REQUEST['key'])     : "";
$value   = !empty($_REQUEST['value'])   ? mysql_real_escape_string($_REQUEST['value'])   : "";
$ip      = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);

// Verify args
if (empty($channel)) die("Missing or invalid paramater 1");
if (empty($uuid) or !preg_match('/^[0-9a-f]{32}$/i',$uuid)) die("Missing or invalid paramater 2");
if (empty($key)) die("Missing or invalid paramater 3");
if (empty($value)) die("Missing or invalid paramater 4");

// Store data
$insert_sql = "INSERT INTO storedata (`time`, `channel`, `ip`, `uuid`, `key`, `value`) VALUES (NOW(),'$channel','$ip','$uuid','$key','$value')";
$result = mysql_query($insert_sql); // Stupid default PHP mysql functions without binding...
if (!$result) die("Error saving data!");

print "Data saved";
?>

[kbenson]

Hmm, did you create the database table with the create statement I posted?

I purposefully left the error messages vague, but you can try replacing

if (!$result) die("Error saving data!");

with

if (!$result) die(mysql_errno($link) . ": " . mysql_error($link));

That will display the actual error mysql is returning, and may be helpful.

[jbrave]

I only have phpmyadmin on this system so I created the table manually based on the statement syntax you posted. I'll try swapping in that error code you posted.