Add export/import .zip endpoints

[karlomikus]

Add support to import recipes exported via bar:export-recipes via api endpoint.

[zhdenny]

Definitely looking forward to having the import command operational

[zhdenny]

Able to make any progress on this import feature yet? bar:export-recipes is currently working but is kind of pointless without a working import.

[karlomikus]

Is bar:import-recipes command not working?

[zhdenny]

I run the export command successfully on latest version of everything:

root@3c336e14704a:/var/www/cocktails# php artisan bar:export-recipes 1
 [INFO] Starting recipe export from bar: 1 - "Curfew"
 [OK] Data exported to file: /var/www/cocktails/storage/bar-assistant/backups/202312110326_recipes.zip

This creates the zip file. I then move that zip file to my test instance of Bar-Assistant (also latest version) and run the import command:

 root@69dabfcd462c:/var/www/cocktails# php artisan bar:import-recipes /var/www/cocktails/storage/bar-assistant/backups/202312110326_recipes.zip
Unable to open zip file: "/var/www/cocktails/storage/bar-assistant/var/www/cocktails/storage/bar-assistant/backups/202312110326_recipes.zip"

I verified permissions are good on the zip file.....I'm not sure what else I can do. It just doesn't want to open the zip file the export command created.

[zhdenny]

Including screenshot of file structure / permissions of my test instance of bar-assistant showing permissions should be good across the board here

[karlomikus]

Ah, path is relative to bar-assistant folder, so the command should be:

php artisan bar:import-recipes backups/202312110326_recipes.zip

[zhdenny]

Ah, path is relative to bar-assistant folder, so the command should be:

php artisan bar:import-recipes backups/202312110326_recipes.zip

This WORKS! I was failing with this for a while. I did not know the path was relative to a specific folder like that. Thanks.

[rflume]

Hi,

I'm currently trying to import from a zipped backup but run into an error.

Bar Assistant is started locally with Docker Compose based on the compose file in the docs.

bar-assistant.image: barassistant/server:v3
salt-rim.image: barassistant/salt-rim:v2
meilisearch.image: getmeili/meilisearch:v1.4

redis.image: redis
webserver.image: nginx:alpine

Displayed versions in Web UI are:

Bar Assistant: [v3.12.1]
Salt Rim: [v2.12.0]
Meilisearch: [1.4.2]

The backup zip was copied into the container withdocker cp ~/20240502_recipes_jWde6oaQ.zip bar-assistant-bar-assistant-1:/var/www/cocktails/storage/bar-assistant/backups/ (only an initial user has been created, no bar yet).

docker exec -it bar-assistant-bar-assistant-1 php artisan bar:import-recipes backups/20240502_recipes_jWde6oaQ.zip                                                   

 Enter the id of the bar you want to import to, or leave empty to create a new one:
 >

 Enter new bar name:
 > Home Bar

 Enter the id of the user you want to assign this bar to:
 > 1

User with id found: 1 - <EMAIL_REDACTED>
Bar created successfully

 Continue with importing the data? (yes/no) [no]:
 > yes

Starting recipes import...
[2024-05-02 12:45:46] production.ERROR: Attempt to read property "id" on null {"exception":"[object] (ErrorException(code: 0): Attempt to read property \"id\" on null at /var/www/cocktails/app/External/Import/FromRecipesData.php:139)
[stacktrace]
#0 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Foundation/Bootstrap/HandleExceptions.php(255): Illuminate\\Foundation\\Bootstrap\\HandleExceptions->handleError()
#1 /var/www/cocktails/app/External/Import/FromRecipesData.php(139): Illuminate\\Foundation\\Bootstrap\\HandleExceptions->Illuminate\\Foundation\\Bootstrap\\{closure}()
#2 /var/www/cocktails/app/External/Import/FromRecipesData.php(56): Kami\\Cocktail\\External\\Import\\FromRecipesData->importIngredients()
#3 /var/www/cocktails/app/Console/Commands/BarImportRecipes.php(97): Kami\\Cocktail\\External\\Import\\FromRecipesData->process()
#4 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Kami\\Cocktail\\Console\\Commands\\BarImportRecipes->handle()
#5 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#6 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure()
#7 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#8 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\\Container\\BoundMethod::call()
#9 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(211): Illuminate\\Container\\Container->call()
#10 /var/www/cocktails/vendor/symfony/console/Command/Command.php(326): Illuminate\\Console\\Command->execute()
#11 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(180): Symfony\\Component\\Console\\Command\\Command->run()
#12 /var/www/cocktails/vendor/symfony/console/Application.php(1096): Illuminate\\Console\\Command->run()
#13 /var/www/cocktails/vendor/symfony/console/Application.php(324): Symfony\\Component\\Console\\Application->doRunCommand()
#14 /var/www/cocktails/vendor/symfony/console/Application.php(175): Symfony\\Component\\Console\\Application->doRun()
#15 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\\Component\\Console\\Application->run()
#16 /var/www/cocktails/artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle()
#17 {main}
"}

In FromRecipesData.php line 139:

  Attempt to read property "id" on null

I found that the _meta.json file does not include a _id key, deleted it, re-zipped the directory and then the import succeeds (at least it's displayed in the terminal). The new bar is also created and assigned to me. However, no ingredients or cocktails are actually imported for the bar.

Any help is much appreciated.

And as a side note, the documentation for Import from .zip (Server) is currently not correct. It describes the process of deleting a user.

[zhdenny]

I author this database to users.....so far, no complaints from people importing using the instructions I provide. https://github.com/zhdenny/bar_assistant_database

[rflume]

Hi zhdenny, thanks for the link. I tried the import with the zip file from the Dropbox link and actually get the same error:

docker exec -it bar-assistant-bar-assistant-1 /bin/sh
# ls -lah /var/www/cocktails/storage/bar-assistant
total 237M
drwxr-xr-x 5 www-data www-data 4.0K May  2 22:08 .
drwxr-xr-x 1 www-data www-data 4.0K Apr 21 14:52 ..
-rw-r--r-- 1 www-data www-data 237M May  2 20:59 202405020710_recipes.zip
drwxr-xr-x 2 www-data www-data 4.0K May  2 21:52 backups
-rw-r--r-- 1 www-data www-data 328K May  2 22:08 database.ba3.sqlite
drwx------ 4 www-data www-data 4.0K May  2 22:07 temp
drwxr-xr-x 5 www-data www-data 4.0K May  2 21:52 uploads
# sqlite3 storage/bar-assistant/database.ba3.sqlite 'SELECT * FROM users;'
1|<REDACTED>|<REDACTED>|2024-05-02 22:05:42|<REDACTED>||2024-05-02 22:05:42|2024-05-02 22:05:42
# sqlite3 storage/bar-assistant/database.ba3.sqlite 'SELECT * FROM bars;'
# ls storage/bar-assistant
202405020710_recipes.zip  backups  database.ba3.sqlite  uploads
# php artisan bar:import-recipes 202405020710_recipes.zip

 Enter the id of the bar you want to import to, or leave empty to create a new one:
 >

 Enter new bar name:
 > test

 Enter the id of the user you want to assign this bar to:
 > 1

User with id found: 1 - <REDACTED>
Bar created successfully

 Continue with importing the data? (yes/no) [no]:
 > yes

Starting recipes import...
[2024-05-02 22:08:10] production.ERROR: Attempt to read property "id" on null {"exception":"[object] (ErrorException(code: 0): Attempt to read property \"id\" on null at /var/www/cocktails/app/External/Import/FromRecipesData.php:139)
[stacktrace]
#0 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Foundation/Bootstrap/HandleExceptions.php(255): Illuminate\\Foundation\\Bootstrap\\HandleExceptions->handleError()
#1 /var/www/cocktails/app/External/Import/FromRecipesData.php(139): Illuminate\\Foundation\\Bootstrap\\HandleExceptions->Illuminate\\Foundation\\Bootstrap\\{closure}()
#2 /var/www/cocktails/app/External/Import/FromRecipesData.php(56): Kami\\Cocktail\\External\\Import\\FromRecipesData->importIngredients()
#3 /var/www/cocktails/app/Console/Commands/BarImportRecipes.php(97): Kami\\Cocktail\\External\\Import\\FromRecipesData->process()
#4 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Kami\\Cocktail\\Console\\Commands\\BarImportRecipes->handle()
#5 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#6 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure()
#7 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#8 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\\Container\\BoundMethod::call()
#9 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(211): Illuminate\\Container\\Container->call()
#10 /var/www/cocktails/vendor/symfony/console/Command/Command.php(326): Illuminate\\Console\\Command->execute()
#11 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(180): Symfony\\Component\\Console\\Command\\Command->run()
#12 /var/www/cocktails/vendor/symfony/console/Application.php(1096): Illuminate\\Console\\Command->run()
#13 /var/www/cocktails/vendor/symfony/console/Application.php(324): Symfony\\Component\\Console\\Application->doRunCommand()
#14 /var/www/cocktails/vendor/symfony/console/Application.php(175): Symfony\\Component\\Console\\Application->doRun()
#15 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\\Component\\Console\\Application->run()
#16 /var/www/cocktails/artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle()
#17 {main}
"}

In FromRecipesData.php line 139:

  Attempt to read property "id" on null

#

[zhdenny]

Alright, I can confirm I have the same error. I just tested importing my database into my test instance of Bar Assistant.

@karlomikus karlomikus will need help figure this out for sure

[karlomikus]

Should be fixed now

[zhdenny]

Yes this is fixed on my end. I was able to export my bar from one Bar Assistant instance and import it into my test Bar Assistant instance without errors.

[rflume]

Thanks for looking into this, the import is now also working for me.

However, I discovered another bug which occurs when using , signs in the ingredients' note section. My export contained the following cocktail JSON:

{
    "_id": "caipirinha",
    "name": "Caipirinha",
    ...
    "ingredients": [
        ...
        {
            "_id": "sugar",
            ...
            "note": "If no brown sugar is available, use fine white sugar",
            ...
        }
    ]
}

The import failed with an error as the note field value is not wrapped in quotations during the import into SQLite:

  SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: cocktail_ingredients.
  ingredient_id (Connection: sqlite, SQL: insert into "cocktail_ingredients" ("cocktail_id", "ingredien
  t_id", "amount", "units", "optional", "note", "sort") values (49, ?, 4, barspoon, 1, If no brown suga
  r is at hand, use fine white sugar, 4))

Full error logs:

# php artisan bar:import-recipes 20240503_recipes_h3uVhUua.zip

 Enter the id of the bar you want to import to, or leave empty to create a new one:
 >

 Enter new bar name:
 > imported

 Enter the id of the user you want to assign this bar to:
 > 1

User with id found: 1 - <REDACTED>
Bar created successfully

 Continue with importing the data? (yes/no) [no]:
 > yes

Starting recipes import...
[2024-05-04 00:17:58] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: cocktail_ingredients.ingredient_id (Connection: sqlite, SQL: insert into "cocktail_ingredients" ("cocktail_id", "ingredient_id", "amount", "units", "optional", "note", "sort") values (49, ?, 4, barspoon, 1, If no brown sugar is at hand, use fine white sugar, 4)) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: cocktail_ingredients.ingredient_id (Connection: sqlite, SQL: insert into \"cocktail_ingredients\" (\"cocktail_id\", \"ingredient_id\", \"amount\", \"units\", \"optional\", \"note\", \"sort\") values (49, ?, 4, barspoon, 1, If no brown sugar is at hand, use fine white sugar, 4)) at /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php:829)
[stacktrace]
#0 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(783): Illuminate\\Database\\Connection->runQueryCallback()
#1 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(576): Illuminate\\Database\\Connection->run()
#2 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(540): Illuminate\\Database\\Connection->statement()
#3 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Query/Processors/Processor.php(32): Illuminate\\Database\\Connection->insert()
#4 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3507): Illuminate\\Database\\Query\\Processors\\Processor->processInsertGetId()
#5 /var/www/cocktails/app/External/Import/FromRecipesData.php(274): Illuminate\\Database\\Query\\Builder->insertGetId()
#6 /var/www/cocktails/app/External/Import/FromRecipesData.php(60): Kami\\Cocktail\\External\\Import\\FromRecipesData->importBaseCocktails()
#7 /var/www/cocktails/app/Console/Commands/BarImportRecipes.php(97): Kami\\Cocktail\\External\\Import\\FromRecipesData->process()
#8 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Kami\\Cocktail\\Console\\Commands\\BarImportRecipes->handle()
#9 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#10 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure()
#11 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#12 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\\Container\\BoundMethod::call()
#13 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(211): Illuminate\\Container\\Container->call()
#14 /var/www/cocktails/vendor/symfony/console/Command/Command.php(326): Illuminate\\Console\\Command->execute()
#15 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(180): Symfony\\Component\\Console\\Command\\Command->run()
#16 /var/www/cocktails/vendor/symfony/console/Application.php(1096): Illuminate\\Console\\Command->run()
#17 /var/www/cocktails/vendor/symfony/console/Application.php(324): Symfony\\Component\\Console\\Application->doRunCommand()
#18 /var/www/cocktails/vendor/symfony/console/Application.php(175): Symfony\\Component\\Console\\Application->doRun()
#19 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\\Component\\Console\\Application->run()
#20 /var/www/cocktails/artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle()
#21 {main}

[previous exception] [object] (PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: cocktail_ingredients.ingredient_id at /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php:587)
[stacktrace]
#0 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(587): PDOStatement->execute()
#1 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(816): Illuminate\\Database\\Connection->Illuminate\\Database\\{closure}()
#2 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(783): Illuminate\\Database\\Connection->runQueryCallback()
#3 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(576): Illuminate\\Database\\Connection->run()
#4 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Connection.php(540): Illuminate\\Database\\Connection->statement()
#5 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Query/Processors/Processor.php(32): Illuminate\\Database\\Connection->insert()
#6 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3507): Illuminate\\Database\\Query\\Processors\\Processor->processInsertGetId()
#7 /var/www/cocktails/app/External/Import/FromRecipesData.php(274): Illuminate\\Database\\Query\\Builder->insertGetId()
#8 /var/www/cocktails/app/External/Import/FromRecipesData.php(60): Kami\\Cocktail\\External\\Import\\FromRecipesData->importBaseCocktails()
#9 /var/www/cocktails/app/Console/Commands/BarImportRecipes.php(97): Kami\\Cocktail\\External\\Import\\FromRecipesData->process()
#10 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Kami\\Cocktail\\Console\\Commands\\BarImportRecipes->handle()
#11 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#12 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure()
#13 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#14 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\\Container\\BoundMethod::call()
#15 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(211): Illuminate\\Container\\Container->call()
#16 /var/www/cocktails/vendor/symfony/console/Command/Command.php(326): Illuminate\\Console\\Command->execute()
#17 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Console/Command.php(180): Symfony\\Component\\Console\\Command\\Command->run()
#18 /var/www/cocktails/vendor/symfony/console/Application.php(1096): Illuminate\\Console\\Command->run()
#19 /var/www/cocktails/vendor/symfony/console/Application.php(324): Symfony\\Component\\Console\\Application->doRunCommand()
#20 /var/www/cocktails/vendor/symfony/console/Application.php(175): Symfony\\Component\\Console\\Application->doRun()
#21 /var/www/cocktails/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\\Component\\Console\\Application->run()
#22 /var/www/cocktails/artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle()
#23 {main}
"}

In Connection.php line 829:

  SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: cocktail_ingredients.
  ingredient_id (Connection: sqlite, SQL: insert into "cocktail_ingredients" ("cocktail_id", "ingredien
  t_id", "amount", "units", "optional", "note", "sort") values (49, ?, 4, barspoon, 1, If no brown suga
  r is at hand, use fine white sugar, 4))

In Connection.php line 587:

  SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: cocktail_ingredients.
  ingredient_id

[karlomikus]

That is just the debug information, it's not the real SQL that gets executed.

This seems like some ingredients are missing and they are referenced in the cocktail recipe. It's possible that it's a cache issue, try running docker compose exec bar-assistant php artisan cache:clear. If that doesn't work you could share the file and I can try to replicate it locally.

[rflume]

The error occurred on a fresh Bar Assistant setup with Docker compose, not sure at which step cleaning the cache could help. I'll send you an email with a Dropbox link to download the full export zip fiel and the two issue-causing files from it.

Maybe it could be due to a conflict with ingredient "sugar" after scraping a recipe from a web URL, as the ingredient name is Sugar ) and not Sugar.

[karlomikus]

Yes, you are right, seems like if there are multiple ingredients with the same(ish) name, they get assigned the same ID when exporting. This should be considered a bug. I'll get it fixed in future versions.