Sqlite

[iNikitaGricenko]

What changes are being made and why?

Added code that will help sqlite to connect to database in kestra storage

Unfortunately i cannot test part where needs to download database file, and try to connect to it in external storage (Where file not in same directory as plugin). But hope that someone can help me with that

How the changes have been QAed?

id: sqlite-external
namespace: tutorial

tasks:
  - id: get0
    type: io.kestra.plugin.fs.http.Download
    disabled: true
    uri: https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite

  - id: wdir
    type: io.kestra.core.tasks.flows.WorkingDirectory
    tasks:
      - id: get
        type: io.kestra.plugin.scripts.shell.Commands
        outputFiles:
          - "*.sqlite"
        taskRunner:
          type: io.kestra.core.models.tasks.runners.types.ProcessTaskRunner
        commands:
          - curl -L -o Chinook_Sqlite.sqlite https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
      - id: db
        type: io.kestra.core.tasks.storages.LocalFiles
        inputs:
          Chinook_Sqlite.sqlite: "{{ outputs.get.outputFiles['Chinook_Sqlite.sqlite'] }}"

      - id: analyze_media_store
        type: io.kestra.plugin.jdbc.sqlite.Query
        description: Find the total number of tracks purchased in each genre
        url: jdbc:sqlite:Chinook_Sqlite.sqlite
        sqliteFile: "{{ outputs.get.outputFiles['Chinook_Sqlite.sqlite'] }}"
        sql: |
          SELECT Genre.Name, COUNT(InvoiceLine.InvoiceLineId) AS TracksPurchased
          FROM Genre
          JOIN Track ON Genre.GenreId = Track.GenreId
          JOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackId
          GROUP BY Genre.Name
          ORDER BY TracksPurchased DESC;

---