SQLite doesn't work with an existing persistent database file (it works only using an in-memory DB)

[anna-geller]

Expected Behavior

The following flow:

id: business_automations
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
        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;

you'll see an error that the DB or DB table is not found

I tried the same with the database file passed as input:

id: business_automations_v2
namespace: tutorial
inputs:
  - id: mydb
    type: FILE

tasks:
  - id: wdir
    type: io.kestra.core.tasks.flows.WorkingDirectory
    tasks:
      - id: db
        type: io.kestra.core.tasks.storages.LocalFiles
        inputs:
          Chinook_Sqlite.sqlite: "{{ inputs.mydb }}"

      - 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
        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;

To see that the actual DB is working, the same works perfectly fine in a Python script:

id: business_automations_python
namespace: tutorial

tasks:
  - id: py
    type: io.kestra.plugin.scripts.python.Script
    taskRunner:
      type: io.kestra.core.models.tasks.runners.types.ProcessTaskRunner
    beforeCommands:
      - pip install requests
    script: |
      import requests
      import sqlite3

      def download_file(url, filename):
          response = requests.get(url)
          response.raise_for_status()  
          
          with open(filename, 'wb') as f:
              f.write(response.content)

      url = "https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"
      filename = "Chinook_Sqlite.sqlite"

      download_file(url, filename)
      print("Download completed successfully!")

      connection = sqlite3.connect('Chinook_Sqlite.sqlite')
      cursor = connection.cursor()
      cursor.execute("SELECT * FROM Artist")
      artists = cursor.fetchall()
      for artist in artists:
          print(artist)

      connection.commit()
      connection.close()

Actual Behaviour

No response

Steps To Reproduce

No response

Environment Information

• Kestra Version: 0.17

Example flow

No response

[loicmathieu]

Fixed by #292