Site Technology / API

Each query requires a key, which identifies the user/developer in question.

You can acquire a key from Bisqwit if you explain your needs well enough. Temporary (a day or week) keys for testing may be granted by just asking.

   request =
     [ (DESC|DESCRIBE|EXPLAIN) [EXTENDED] ] query.
   
   query =
    (SELECT
       [DISTINCT]
       ( '*'
       | (expression [ [ AS ] name ]) % ','
       )
    [ FROM table_refs
      [ WHERE    expression ]
      [ GROUP BY (expression [ASC | DESC]) % ',' ]
      [ HAVING   expression ] ]
    [ ORDER BY (expression [ASC | DESC]) % ',' ]
    [ LIMIT integer
       ( OFFSET integer
       | ( ',' integer )?
       ) ]
    ) % (UNION [ DISTINCT | ALL ])
     .

   table_refs =
       ( table_name [ [ AS ] table_alias ]
       | '(' table_refs ')'
       | LEFT JOIN table_refs ( ON expression
                              | USING '(' (name % ',') ')'
                              )
       ) % ','.

   name = field_name_or_alias.

   expression =
    ( expr_head*
      ( [table_name_or_alias '.'] name
     | TRUE
     | FALSE
     | numeric_constant
     | string_constant 
     | expression
     | [ EXISTS ] '(' query ')'
     | COUNT '(' [ DISTINCT ] ( '*' | expression ) ')'
     | [ SUM | LENGTH | AVG | FLOOR ] '(' expression ')'
      ) expr_tail*
     ) % expr_sep.

    expr_head = NOT | '!' | '-' | '+'.

    expr_tail =
        IS [ NOT ] NULL
      | [ NOT ] IN '(' ( query
                      | ( expression % ',' )
                        ) ')'
      | BETWEEN expression AND expression
      | LIKE string_constant.
    
    expr_sep =
           '+' | '-' | '/' | '*' | '^'
         | '=' | '<' | '>' | '<=' | '>=' | '!=' | '<>'
         | AND | OR | XOR | MOD.

The syntax is also available as a graphical flowchart at http://bisqwit.iki.fi/kala/snap/tasvqueryflowchart.png. However, it is a large image.

The API can also access portions of the forums by prefixing the table name (phpBB standard table names) with the database name, for example: select count(*) from nesvideos_forum.posts. Do note that not all tables and fields can be accessed.

This example program, written in PHP, queries and gets the list of all currently published Game Boy movie file names:

  <?php
  $key = YOUR_USERKEY_HERE;
  $sql = 'select filename from movie m,movie_file f,filetype t,system s'.
         'where m.id=movieid and t.id=typeid and filetypeclass="M"'.
         'and obsoleted_by=-1 and s.id=systemid and abbr="GB"';
  $soap_options = Array('compression' => SOAP_COMPRESSION_ACCEPT | SOAP_COMPRESSION_GZIP);
  $client = new SoapClient('TasvideosQuery.wsdl', // ← this file must exist
                           $soap_options);
  $response = $client->doDbQuery($key, $sql, true, 5000);
  print_r($response->columns);

This example program, written in Ruby, fetches the source code of the Rules page:

  require 'soap/wsdlDriver.rb'
  key = YOUR_USER_KEY_HERE          ## ↓↓ this file must exist
  client = SOAP::WSDLDriverFactory.new('TasvideosQuery.wsdl').create_rpc_driver
  response = client.doLoadPage(key, 'Rules')
  print response.wtxt

The SQL interface has limitations on how often it can be accessed. It also tries to protects itself against queries that cause tons of database load.

These values are currently held in secret, but should not cause problems if you don't access the site too often. Depending on the query (and the popularity of this API), the minimum allowed time between queries may range from 15 seconds to 2 hours.