Try WSO2 Cloud for Free
Sign in

All docs This doc
||
Skip to end of metadata
Go to start of metadata

The underlying use case of the Export Github Issues to Google Spreadsheet scenario is that you get details of newly created Github issues from a list of Git repositories in Github and add those details to a Google Spreadsheet. This  section explains how to modify this default scenario as follows:

  • Change  the information about issues that you retrieve from Github.
  • Change the default time duration of the scheduled task that checks whether new Git issues are created.
  • Retrieve the issues that were created after a time stamp that you mention. You can set any time stamp in the past to see information for those old issues.

Let's get started.

  1. Download and install WSO2 EI tooling.

  2. Download the contents in this Github location to a suitable location in your machine.

  3. Open the EI tooling interface. The workspace is any folder in your local file system where your tooling artifacts will be saved. For example,

  4. Once the workspace opens, click the File -> Import menu from the menu bar and then under the General drop-down, click Existing Projects into Workspace.
  5. Select the source folder that you downloaded earlier in step 2 as the root directory, select all the projects inside the directory, and click Finish.
     
    Let's import the required connectors to the project.
  6. Right click  GithubToSpreadsheet and click Add or Remove Connector.
     
  7. Select Add connector and click Next.
  8. Connect to the WSO2 Store using the Connector store location option and select the following connectors from the list once the available connectors load.

    • Github

    • Google Spreadsheet

You have now created a tooling project using the downloaded artifacts. Next, let's modify the artifacts to our requirements.

Modify the project's artifacts

  1. Expand GithubToSpreadsheet in the left navigation and note that the project's artifacts open in the tooling interface as follows:
     
  2. Open the sequence githubToSpreadsheet.xml under src -> man -> synapse-config -> sequences:

    Tip: See Working with sequences for information about sequences.

  3. To get more information about the Github issues, go to the  Source tab of the above sequence and modify the below code as you want. For example, if you want to get the issue No, edit the property mediator as follows:

    <property expression="//number" name="number" scope="default" type="STRING">

  4. To change the time duration of the scheduled task, open getGitIssuesToSpreadsheet.xml that is under src -> man -> synapse-config -> tasks and change the value of the Interval property.

    Tip: If you change the task interval here, be sure to change the task_interval_time property with the same value in the environment variables also.

    Tip: For more information on scheduled tasks, see creating a scheduled task.

  5. To get all the issues that are created after a timestamp that you specify, create a  mysql database to store the timestamp.

    There is a scheduled task that polls the Github repository and collects the issues that are created after a timestamp value that is in the database. After each poll, the sceduled task updates the timestamp value to the current time so that the next time it polls, it will retreive only the newer issues from Github. However, you might sometimes want to see all issues that are created after a specific time in the past. 

     Click here for information on how to create a database to store the timestamp...

    You can create a database and store the timestamp value in it. The timestamp is in the ISO 8601 format: YYYY-MM-DDTHH:MM:SSZ.

    1. Log in to WSO2 Integration Cloud by going to http://wso2.com/cloud/, clicking the Sign In link and then selecting WSO2 Integration Cloud.
    2. The Integration Cloud opens. Click CREATE DATABASE.
    3. Enter the following information in the form that opens, scroll down, and click Create Database.
      • Database Name: githubToSpreadsheetRegistry
      • Default User Name: root
      • Give a suitable password

    4. The database that you just created opens on the console. Copy the database name, URL, and username. 

      Tip

      You see additional characters in the database name and username because the Integration Cloud adds them to uniquely identify the tenant of the database and the user.

    5. Assuming you already have MySQL Server installed in your local environment, go to the command-line or terminal, type mysql and press enter to go to the MySQL prompt, and then execute the following commands to connect to the MySQL database that you just created.
      The following commands are an example. You can change the database name, table name, column name and Timestamp value as you want.

      SQL Commands
      mysql -u  root -h mysql.storage.cloud.wso2.com -p
      use <your_database_name> (e.g., use githubToSpreadsheetRegistry_testcompanybiru)
      create table githubToSpreadsheetRegistry (Timestamp VARCHAR(40));
      DESCRIBE githubToSpreadsheetRegistry;
      INSERT INTO githubToSpreadsheetRegistry (Timestamp) VALUES("2017-01-01T04:02:59.169Z");
  6. Back in the Tooling interface, open the sequence githubToSpreadsheet.xml that is under src -> man -> synapse-config -> sequences and replace its code with this code.

  7. Modify the following code blocks in your code with your mysql credentials, database URL, driver, table name, and column name.

      <dblookup>
            <connection>
                <pool>
                    <password>root</password>
                    <property name="autocommit" value="false"/>
                    <driver>com.mysql.jdbc.Driver</driver>
                    <url>jdbc:mysql://mysql.storage.cloud.wso2.com:3306/githubToSpreadsheetRegistry_testcompanybiru?useSSL=false</url>
                    <user>root_ZvVXAGb1</user>
                </pool>
            </connection>
            <statement>
                <sql><![CDATA[select Timestamp from githubToSpreadsheetRegistry]]></sql>
                <result column="Timestamp" name="githubToSpreadsheetRegistryTimestamp"/>
            </statement>
        </dblookup
    <dbreport>
            <connection>
                <pool>
                    <password>root</password>
                    <property name="autocommit" value="false"/>
                    <driver>com.mysql.jdbc.Driver</driver>
                    <url>jdbc:mysql://mysql.storage.cloud.wso2.com:3306/githubToSpreadsheetRegistry_testcompanybiru?useSSL=false</url>
                    <user>root_ZvVXAGb1</user>
                </pool>
            </connection>
            <statement>
                <sql><![CDATA[UPDATE githubToSpreadsheetRegistry SET Timestamp = ?]]></sql>
                <parameter expression="$ctx:newsystemTime" type="VARCHAR" xmlns:ns="http://org.apache.synapse/xsd"/>
            </statement>
        </dbreport>
  8. Once you are done modifying the default scenario, right click GithubToSpreadsheetCAPP and click Export Composite Application Project. You will then be promped to save the project's artifacts as a CAR file.
     

You have now modified the default scenario.

  • No labels