Recherche avancée

Sur d’autres sites (11168)

• Adjusting The Timetable and SQL Shame

  16 août 2012, par Multimedia Mike — General, Python, sql

  My Game Music Appreciation website has a big problem that many visitors quickly notice and comment upon. The problem looks like this :

  
  
  

  The problem is that all of these songs are 2m30s in length. During the initial import process, unless a chiptune file already had curated length metadata attached, my metadata utility emitted a default play length of 150 seconds. This is not good if you want to listen to all the songs in a soundtrack without interacting with the player page, but have various short songs (think “game over” or other quick jingles) that are over in a few seconds. Such songs still pad out 150 seconds of silence.

  So I needed to correct this. Possible solutions :

  1. Manually : At first, I figured I could ask the database which songs needed fixing and listen to them to determine the proper lengths. Then I realized that there were well over 1400 games affected by this problem. This just screams “automated solution”.
  2. Automatically : Ask the database which songs need fixing and then somehow ask the computer to listen to the songs and decide their proper lengths. This sounds like a winner, provided that I can figure out how to programmatically determine if a song has “finished”.

  SQL Shame
  This play adjustment task has been on my plate for a long time. A key factor that has blocked me is that I couldn’t figure out a single SQL query to feed to the SQLite database underlying the site which would give me all the songs I needed. To be clear, it was very simple and obvious to me how to write a program that would query the database in phases to get all the information. However, I felt that it would be impure to proceed with the task unless I could figure out one giant query to get all the information.

  This always seems to come up whenever I start interacting with a database in any serious way. I call it SQL shame. This task got some traction when I got over this nagging doubt and told myself that there’s nothing wrong with the multi-step query program if it solves the problem at hand.

  Suddenly, I had a flash of inspiration about why the so-called NoSQL movement exists. Maybe there are a lot more people who don’t like trying to derive such long queries and are happy to allow other languages to pick up the slack.

  Estimating Lengths
  Anyway, my solution involved writing a Python script to iterate through all the games whose metadata was output by a certain engine (the one that makes the default play length 150 seconds). For each of those games, the script queries the song table and determines if each song is exactly 150 seconds. If it is, then go to work trying to estimate the true length.

  The forgoing paragraph describes what I figured was possible with only a single (possibly large) SQL query.

  For each song represented in the chiptune file, I ran it through a custom length estimator program. My brilliant (err, naïve) solution to the length estimation problem was to synthesize seconds of audio up to a maximum of 120 seconds (tightening up the default length just a bit) and counting how many of those seconds had all 0 samples. If the count reached 5 consecutive seconds of silence, then the estimator rewound the running length by 5 seconds and declared that to be the proper length. Update the database.

  There were about 1430 chiptune files whose songs needed updates. Some files had 1 single song. Some files had over 100. When I let the script run, it took nearly 65 minutes to process all the files. That was a single-threaded solution, of course. Even though I already had the data I needed, I wanted to try to hand at parallelizing the script. So I went to work with Python’s multiprocessing module and quickly refactored it to use all 4 CPU threads on the machine where the files live. Results :

  • Single-threaded solution : 64m42s to process corpus (22 games/minute)
  • Multi-threaded solution : 18m48s with 4 CPU threads (75 games/minute)

  More than a 3x speedup across 4 CPU threads, which is decent for a primarily CPU-bound operation.

  Epilogue
  I suspect that this task will require some refinement or manual intervention. Maybe there are songs which actually have more than 5 legitimate seconds of silence. Also, I entertained the possibility that some songs would generate very low amplitude noise rather than being perfectly silent. In that case, I could refine the script to stipulate that amplitudes below a certain threshold count as 0. Fortunately, I marked which games were modified by this method, so I can run a new script as necessary.

  SQL Schema
  Here is the schema of my SQlite3 database, for those who want to try their hand at a proper query. I am confident that it’s possible ; I just didn’t have the patience to work it out. The task is to retrieve all the rows from the games table where all of the corresponding songs in the songs table is 150000 milliseconds.
  

  sql

  < view plain text >

  1. CREATE TABLE games
  2.   (
  3.    id INTEGER PRIMARY KEY AUTOINCREMENT,
  4.    uncompressed_sha1 TEXT,
  5.    uncompressed_size INTEGER,
  6.    compressed_sha1 TEXT,
  7.    compressed_size INTEGER,
  8.    system TEXT,
  9.    game TEXT,
  10.    gme_system TEXT default NULL,
  11.    canonical_url TEXT default NULL,
  12.    extension TEXT default "gamemusicxz",
  13.    enabled INTEGER default 1,
  14.    redirect_to_id INT DEFAULT -1,
  15.    play_lengths_modified INT DEFAULT NULL) ;
  16. CREATE TABLE songs
  17.   (
  18.    game_id INTEGER,
  19.    song_number INTEGER NOT NULL,
  20.    song TEXT,
  21.    author TEXT,
  22.    copyright TEXT,
  23.    dumper TEXT,
  24.    length INTEGER,
  25.    intro_length INTEGER,
  26.    loop_length INTEGER,
  27.    play_length INTEGER,
  28.    play_order INTEGER default -1) ;
  29. CREATE TABLE tags
  30.   (
  31.    game_id INTEGER,
  32.    tag TEXT NOT NULL,
  33.    tag_type TEXT default "filename") ;
  34. CREATE INDEX gameid_index_songs ON songs(game_id) ;
  35. CREATE INDEX gameid_index_tag ON tags(game_id) ;
  36. CREATE UNIQUE INDEX sha1_index ON games(uncompressed_sha1) ;

• How to expose new API methods in the HTTP Reporting API – Introducing the Piwik Platform

  26 février 2015, par Thomas Steur — Development

  This is the next post of our blog series where we introduce the capabilities of the Piwik platform (our previous post was How to write UI tests for your plugin). This time you’ll learn how to extend our Reporting API. For this tutorial you will need to have basic knowledge of PHP.

  What is Piwik’s Reporting API ?

  It allows third party applications to access analytics data and manipulate miscellaneous data (such as users or websites) through HTTP requests.

  What is it good for ?

  The Reporting API is used by the Piwik UI to render reports, to manage users, and more. If you want to add a feature to the Piwik UI, you might have to expose a method in the API to access this data. As the API is called via HTTP it allows you to fetch or manipulate any Piwik related data from anywhere. In these exposed API methods you can do pretty much anything you want, for example :

  • Enhance existing reports with additional data
  • Filter existing reports based on custom rules
  • Access the database and generate custom reports
  • Persist and read any data
  • Request server information

  Getting started

  In this series of posts, we assume that you have already set up your development environment. If not, visit the Piwik Developer Zone where you’ll find the tutorial Setting up Piwik.

  To summarize the things you have to do to get setup :

  • Install Piwik (for instance via git).
  • Activate the developer mode : ./console development:enable.
  • Generate a plugin : ./console generate:plugin --name="MyApiPlugin". There should now be a folder plugins/MyApiPlugin.
  • And activate the created plugin : ./console plugin:activate "MyApiPlugin"

  Let’s start creating an API

  We start by using the Piwik Console to create a new API :

  ./console generate:api

  The command will ask you to enter the name of the plugin the created API should belong to. I will simply use the above chosen plugin name “MyApiPlugin”. There should now be a file plugins/MyApiPlugin/API.php which contains already an example to get you started easily :

  class API extends \Piwik\Plugin\API
  {
      public function getAnswerToLife($truth = true)
      {
          if ($truth) {
              return 42;
          }
   
          return 24;
      }
   
      public function getExampleReport($idSite, $period, $date, $wonderful = false)
      {
          $table = DataTable::makeFromSimpleArray(array(
              array('label' => 'My Label 1', 'nb_visits' => '1'),
              array('label' => 'My Label 2', 'nb_visits' => '5'),
          ));
   
          return $table;
      }
  }
  Télécharger

  Any public method in that file will be available via the Reporting API. For example the method getAnswerToLife can be called via this URL : index.php?module=API&method=MyApiPlugin.getAnswerToLife. The URL parameter method is a combination of your plugin name and the method name within this class.

  Passing parameters to your method

  Both example methods define some parameters. To pass any value to a parameter of your method simply specify them by name in the URL. For example ...&method=MyApiPlugin.getExampleReport&idSite=1&period=week&date=today&wonderful=1 to pass values to the parameters of the method getExampleReport.

  Returning a value

  In an API method you can return any boolean, number, string or array value. A resource or an object cannot be returned unless it implements the DataTableInterface such as DataTable (the primary data structure used to store analytics data in Piwik), DataTable\Map (stores a set of DataTables) and DataTable\Simple (a DataTable where every row has two columns : label and value).

  Did you know ? You can choose the response format of your API request by appending a parameter &format=JSON|XML|CSV|... to the URL. Check out the Reporting API Reference for more information.

  Best practices

  Check user permissions

  Do not forget to check whether a user actually has permissions to access data or to perform an action. If you’re not familiar with Piwik’s permissions and how to check them read our User Permission guide.

  Keep API methods small

  At Piwik we aim to write clean code. Therefore, we recommend to keep API methods small (separation of concerns). An API pretty much acts like a Controller :

  public function createLdapUser($idSite, $login, $password)
  {
      Piwik::checkUserHasAdminAccess($idSite);
      $this->checkLogin($login);
      $this->checkPassword($password);
      
      $myModel = new LdapModel();
      $success = $myModel->createUser($idSite, $login, $password);
      
      return $success;
  }
  Télécharger

  This is not only easy to read, it will also allow you to create simple tests for LdapModel (without having to bootstrap the whole Piwik layer) and you will be able to reuse it in other places if needed.

  Calling APIs of other plugins

  For example if you want to fetch an existing report from another plugin, say a list of all Page URLs, do not request this report by calling that method directly :

  \Piwik\Plugins\Actions\API::getInstance()->getPageUrls($idSite, $period, $date);

  . Instead, issue a new API request :

  $report = \Piwik\API\Request::processRequest('Actions.getPageUrls', array(
  
      'idSite' => $idSite,
  
      'period' => $period,
  
      'date'   => $date,
  
  ));

  This has several advantages :

  • It avoids a fatal error if the requested plugin is not available on a Piwik installation
  • Other plugins can extend the called API method via events (adding additional report data to a report, doing additional permission checks) but those events will be only triggered when requesting the report as suggested
  • If the method parameters change, your request will most likely still work

  Publishing your Plugin on the Marketplace

  In case you want to share your API with other Piwik users you can do this by pushing your plugin to a public GitHub repository and creating a tag. Easy as that. Read more about how to distribute a plugin and best practices when publishing a plugin.

  Isn’t it easy to create a API ? We never even created a file ! If you have any feedback regarding our APIs or our guides in the Developer Zone feel free to send it to us.

• How to expose new API methods in the HTTP Reporting API – Introducing the Piwik Platform

  26 février 2015, par Thomas Steur — Development

  This is the next post of our blog series where we introduce the capabilities of the Piwik platform (our previous post was How to write UI tests for your plugin). This time you’ll learn how to extend our Reporting API. For this tutorial you will need to have basic knowledge of PHP.

  What is Piwik’s Reporting API ?

  It allows third party applications to access analytics data and manipulate miscellaneous data (such as users or websites) through HTTP requests.

  What is it good for ?

  The Reporting API is used by the Piwik UI to render reports, to manage users, and more. If you want to add a feature to the Piwik UI, you might have to expose a method in the API to access this data. As the API is called via HTTP it allows you to fetch or manipulate any Piwik related data from anywhere. In these exposed API methods you can do pretty much anything you want, for example :

  • Enhance existing reports with additional data
  • Filter existing reports based on custom rules
  • Access the database and generate custom reports
  • Persist and read any data
  • Request server information

  Getting started

  In this series of posts, we assume that you have already set up your development environment. If not, visit the Piwik Developer Zone where you’ll find the tutorial Setting up Piwik.

  To summarize the things you have to do to get setup :

  • Install Piwik (for instance via git).
  • Activate the developer mode : ./console development:enable.
  • Generate a plugin : ./console generate:plugin --name="MyApiPlugin". There should now be a folder plugins/MyApiPlugin.
  • And activate the created plugin : ./console plugin:activate "MyApiPlugin"

  Let’s start creating an API

  We start by using the Piwik Console to create a new API :

  ./console generate:api

  The command will ask you to enter the name of the plugin the created API should belong to. I will simply use the above chosen plugin name “MyApiPlugin”. There should now be a file plugins/MyApiPlugin/API.php which contains already an example to get you started easily :

  class API extends \Piwik\Plugin\API
  {
      public function getAnswerToLife($truth = true)
      {
          if ($truth) {
              return 42;
          }
   
          return 24;
      }
   
      public function getExampleReport($idSite, $period, $date, $wonderful = false)
      {
          $table = DataTable::makeFromSimpleArray(array(
              array('label' => 'My Label 1', 'nb_visits' => '1'),
              array('label' => 'My Label 2', 'nb_visits' => '5'),
          ));
   
          return $table;
      }
  }
  Télécharger

  Any public method in that file will be available via the Reporting API. For example the method getAnswerToLife can be called via this URL : index.php?module=API&method=MyApiPlugin.getAnswerToLife. The URL parameter method is a combination of your plugin name and the method name within this class.

  Passing parameters to your method

  Both example methods define some parameters. To pass any value to a parameter of your method simply specify them by name in the URL. For example ...&method=MyApiPlugin.getExampleReport&idSite=1&period=week&date=today&wonderful=1 to pass values to the parameters of the method getExampleReport.

  Returning a value

  In an API method you can return any boolean, number, string or array value. A resource or an object cannot be returned unless it implements the DataTableInterface such as DataTable (the primary data structure used to store analytics data in Piwik), DataTable\Map (stores a set of DataTables) and DataTable\Simple (a DataTable where every row has two columns : label and value).

  Did you know ? You can choose the response format of your API request by appending a parameter &format=JSON|XML|CSV|... to the URL. Check out the Reporting API Reference for more information.

  Best practices

  Check user permissions

  Do not forget to check whether a user actually has permissions to access data or to perform an action. If you’re not familiar with Piwik’s permissions and how to check them read our User Permission guide.

  Keep API methods small

  At Piwik we aim to write clean code. Therefore, we recommend to keep API methods small (separation of concerns). An API pretty much acts like a Controller :

  public function createLdapUser($idSite, $login, $password)
  {
      Piwik::checkUserHasAdminAccess($idSite);
      $this->checkLogin($login);
      $this->checkPassword($password);
      
      $myModel = new LdapModel();
      $success = $myModel->createUser($idSite, $login, $password);
      
      return $success;
  }
  Télécharger

  This is not only easy to read, it will also allow you to create simple tests for LdapModel (without having to bootstrap the whole Piwik layer) and you will be able to reuse it in other places if needed.

  Calling APIs of other plugins

  For example if you want to fetch an existing report from another plugin, say a list of all Page URLs, do not request this report by calling that method directly :

  \Piwik\Plugins\Actions\API::getInstance()->getPageUrls($idSite, $period, $date);

  . Instead, issue a new API request :

  $report = \Piwik\API\Request::processRequest('Actions.getPageUrls', array(
  
      'idSite' => $idSite,
  
      'period' => $period,
  
      'date'   => $date,
  
  ));

  This has several advantages :

  • It avoids a fatal error if the requested plugin is not available on a Piwik installation
  • Other plugins can extend the called API method via events (adding additional report data to a report, doing additional permission checks) but those events will be only triggered when requesting the report as suggested
  • If the method parameters change, your request will most likely still work

  Publishing your Plugin on the Marketplace

  In case you want to share your API with other Piwik users you can do this by pushing your plugin to a public GitHub repository and creating a tag. Easy as that. Read more about how to distribute a plugin and best practices when publishing a plugin.

  Isn’t it easy to create a API ? We never even created a file ! If you have any feedback regarding our APIs or our guides in the Developer Zone feel free to send it to us.