Add a mode to link images in a different folder

[SilvioGrosso]

Describe the new feature

It would be great to have a new option to add a link (url) pointing to a folder with some images in order to visualize them in Sqlitebrowser one by one.

In essence, each row of the database might contain the path of one image.
For example,
ID | description | Location
1 |rose_flower | C:\Users\silvio\Desktop\flower\rose.jpg
2 |tulip_flower | C:\Users\silvio\Desktop\flower\tulip.jpg

At present, you can import an image into your database as blob. In the long run, however, you quickly increase the database size and make it much slower to work with.
By having the images separated from the database this one would remain small as regards its size.

Additional info

As regards the link, it would be important to get the path of your image saved as relative in order to easily move the database and the folder with your images in different drives (whenever you decide to backup them).
For instance, you create a new folder in the C:\ drive and save in this same folder your database and your images as well.
Later on, whenever you move this folder, with both your Database and your images, for example into the D:\ drive, Db Browser is able to visualize your images even though the path has been changed (from C to D).

Does this feature exist in another product or project?

This options is available with QGIS, an open source GIS software.
Usually the images have their GPS data (latitude and longitude) so that you can visualize them in their exact position in your map [1]

[1] https://gis.stackexchange.com/questions/251023/link-pdf-jpeg-to-locations-in-qgis-all-coming-from-a-standard-folder

[mgrojo]

It would be interesting to support this and maybe it isn't too difficult.

I'm thinking on adding a new button (Link File, probably under Import, like Insert Values... is under New Record) to write the selected file-name to the cell, possibly trimming the path part if it coincides to the DB path. For viewing, we could reuse the Image mode. First, it would try to interpret the content as an image and if it fails, as a file-name containing an image. The automatic switching wouldn't work for these linked images, as reading the file from disk would be too slow for that. But manual selection would do it.

Would that cover your use case, @SilvioGrosso?

[justinclift]

One thing that springs to mind, is whether this could be abused. eg linking to (say) /etc/passwd

While that itself could have useful potential (eg some weird form of import 😉), would we be opening pandora's box and then wanting to try and shut it again.

This might be all ok of course, I'm just putting the idea out there so we can think it through. 😄

[SilvioGrosso]

Hello @mgrojo,

The automatic switching wouldn't work for these linked images, as reading the file from disk would be too slow for that. But manual selection would do it.
Would that cover your use case?

Yep. It would be great.

In short, I need to move around, for backup purposes, to differente Drives (C:\ - D:\ etc), on Windows 10 - 64 bit, a folder containing:

• my database;
• jpeg images.
  As long as Db Browser is able to visualize these jpeg images through some sort of "link" everything is fine :-)

Thanks a lot indeed!

[chrisjlocke]

One thing that springs to mind, is whether this could be abused. eg linking to (say) /etc/passwd

With me being a muppet, can you explain what your concern is behind this? Doesn't db4s have the same permissions as the user running it, so db4s can't import anything the user can't see anyway can it? Or am I (most probably) missing the point?

[mgrojo]

One thing that springs to mind, is whether this could be abused. eg linking to (say) /etc/passwd

While that itself could have useful potential (eg some weird form of import 😉), would we be opening pandora's box and then wanting to try and shut it again.

Ok, that would mean that if the user selects the Image mode and in the cell it's stored /etc/passwd, then we would try to read the file and since it isn't an image, we would close it without using the content. Do you think that could be a problem? I guess it could only mean activating some alert if selinux or similar is activated and that is reported; or read access is denied. In my system the file is readable and no alert is produced (the passwords hashes are stored in /etc/shadow, which is unreadable as a regular user). I don't guess how reading an arbitrary file as an image could be maliciously exploited. But security is arcane knowledge for me.

Would we need a special "Linked image" mode, so it is more obvious for the user that the referred file-name will be read?

[mgrojo]

@SilvioGrosso

In short, I need to move around, for backup purposes, to differente Drives (C:\ - D:\ etc), on Windows 10 - 64 bit, a folder containing:

Yes, it seems that the proposal would fit your need. Thanks for the idea, it's a typical organisation of image data.

I also thought of reading the file contents in XML and JSON modes, but that case would be more cumbersome, since these modes are editors and not plain viewers like the Image mode, so I finally disregarded the idea.

[mgrojo]

@SilvioGrosso There's a way to get something closer to what you want, although a full "linked-image" mode implementation still seems useful to me.

The trick is to load the fileio extension (see #1838) and use a custom display format to read the file contents and return it as a BLOB, i.e. readfile("yourLinkedImageColumn"). Then the cell editor will show you the image. The drawback is that currently the display formats turns the column to read-only mode. The best work around would be saving the custom display format as a view. Then you can use the table to manually modify the linked image file and the SQL-view to display the image.

It would be nice to improve the display formats feature, so the user can still modify the cell data using the original format. That would make this use cases more agile.

On the other hand, if there are other interested people in a full-fledged linked-image mode, please up-vote this issue using 👍 in the issue description, so we can known how useful would be considered such feature.

[SilvioGrosso]

Hello @mgrojo

Sorry for being late with my reply but these days I am on holiday :-)

At present, as a complete workaround to any real sqlite database, I have been using LibreOffice Calc.
It works extremely fine so far because I can link any jpeg image (ctr+k as a shortcut in the cell of LibreOffice Calc to create the link), even when they are located in some subfolder.
Whenever I click on the cell, with a link to an image, this action triggers the opening of this same image which is displayed through a differente application. As a default on my computer (Windows 10) now they are opened with Picasa (which is an image displayer of Google; on Linux it might be Digikam for a change). In short, I suppose the click on the cell containing the image is followed by the running of the .exe of Picasa (on Windows 10 it is my default software application to display pictures).

Here is a screenshot of LibreOffice 6.2.4 Calc, on Window 10 (64 bit), with a small "database" (I have clicked on the firt image "Lizard" ("C2" cell and Picasa has been launched to display my image):

What it is useful of LibreOffice Calc is that this option is "portable" in that I can move around the folder containing the LibreOffice Calc sheet AND the folder (and subfolders) with the images.
For instance, I have moved everything to an USB pen and the links are still working (even though the path to these images has changed!).

Just out of curiosity, how long do you suppose it would take to develop such a feature on Db Browser for Sqlite?

[SilvioGrosso]

BTW, Just tried to load the fileio extentsion "fileio.dll"
Here is my setup (Windows 10 - 64 bit):
Version 3.11.99 (Jul 29 2019)
Built for x86_64-little_endian-llp64, running on x86_64
Qt Version 5.11.3

Unfortunately, it doens't work.
Here is the error message I always get with this extension (Tools > load extension):
Error loading extension: error during initialization

[mgrojo]

Hello @mgrojo

Sorry for being late with my reply but these days I am on holiday :-)

It wasn't late at all, and holidays are for disconnecting 😄

At present, as a complete workaround to any real sqlite database, I have been using LibreOffice Calc.
It works extremely fine so far because I can link any jpeg image (ctr+k as a shortcut in the cell of LibreOffice Calc to create the link), even when they are located in some subfolder.
Whenever I click on the cell, with a link to an image, this action triggers the opening of this same image which is displayed through a differente application. As a default on my computer (Windows 10) now they are opened with Picasa (which is an image displayer of Google; on Linux it might be Digikam for a change). In short, I suppose the click on the cell containing the image is followed by the running of the .exe of Picasa (on Windows 10 it is my default software application to display pictures).

That would be nice to have in DB4S, as already discussed in #1791 (comment)

What it is useful of LibreOffice Calc is that this option is "portable" in that I can move around the folder containing the LibreOffice Calc sheet AND the folder (and subfolders) with the images.
For instance, I have moved everything to an USB pen and the links are still working (even though the path to these images has changed!).

Curious, given that the stored paths are absolute. Some fallback search mechanism, I suppose.

Just out of curiosity, how long do you suppose it would take to develop such a feature on Db Browser for Sqlite?

No idea. It depends on available personal time and motivation.

BTW, Just tried to load the fileio extentsion "fileio.dll"
Here is my setup (Windows 10 - 64 bit):
Version 3.11.99 (Jul 29 2019)
Built for x86_64-little_endian-llp64, running on x86_64
Qt Version 5.11.3

Unfortunately, it doens't work.
Here is the error message I always get with this extension (Tools > load extension):
Error loading extension: error during initialization

Where did you get the extension? Maybe there is some incompatibility. Does the "Error log" in "SQL Log" dock show something else?

[SilvioGrosso]

Thanks for your reply!

Just tried again and the "Error log" is empty.
I suppose I may be missing some .dll libraries on my Wndows 10 (64 bit) setup...

[mgrojo]

Maybe @karim might help with that.

[SilvioGrosso]

Thanks!
The other 2 extensions work fine (formats, maths)
I have downloaed the ZIP version of Db browser (29 July)

[karim]

Had the same problem a while back with fileio extension as mentioned here #1932 (comment). It used to work fine before but not anymore. I will look into it.

[karim]

I found out why I wasn't able to load fileio extension using sqlite command line. It was failing to initialize, on the sqlite3_create_module function. It was unable to register the virtual table fsdir.

It turns out that sqlite CLI (shell.c) already includes fileio.c, so both readfile, writefile, and the virtual table fsdir are available. Loading fileio extension will always fail because these names are already registered.

I have no problem loading and using it with DB4S though...

@SilvioGrosso Do you have access to a Windows 7 PC, or a VM, that you can load the extension on?

We had a problem like this before with another extention (SpatiaLite) that was working fine on Windows 7 but not on Windows 10. It is probably missing DLL like you said. I am not using Windows 10 so I can't test, but I will see what I can do.

@chrisjlocke Can you load fileio extension?

[SilvioGrosso]

Hello @karim

@SilvioGrosso Do you have access to a Windows 7 PC, or a VM, that you can load the extension on?

Nope, I am sorry :-(

BTW, I do own a computer with Windows 8.1 (64 bit) though.
Just tried the latest ZIP 64 bit nighly build (29 July) and this extension works fine!
No problem while loading it!

To recap:

• Windows 10 (64 bit): it doesn' t work;
• Windows 8.1 (64 bit): it works;
• Windows 7: not tested.

Btw, I have tested this SQL code on Window 10 (64 bit) where this extension is not loaded:

create table images
(id integer,
name text,
image text);

insert into images values (
1, 'lizard', 'D:\IMAGES_LINK\pictures\1.jpg');

select * from images("D:\IMAGES_LINK\pictures\1.jpg");
--I get an error and my image is not displayed (Execution finished with errors.
Result: 'images' is not a function)

[karim]

To recap:

• Windows 10 (64 bit): it doesn' t work;

• Windows 8.1 (64 bit): it works;

• Windows 7: not tested.

Thanks for narrowing it down. It works with me on Windows 7, so it is something with Windows 10 missing some files.

select * from images("D:\IMAGES_LINK\pictures\1.jpg");

This query is wrong. select * from fsdir(path-here) worked in my screenshot above because fsdir is a virtual table (loaded from an extension).

Your query should some something like...

1. select * from images where id = 1;
2. select * from images where name = 'lizard';
3. select * from images where image = 'D:\IMAGES_LINK\pictures\1.jpg';

However, this will return a row, but will not display an image. I don't think we have this feature implemented.

[SilvioGrosso]

Hello @karim

This query is wrong. select * from fsdir(path-here) worked in my screenshot above because fsdir is a virtual table (loaded from an extension).

Ok. Now I have got it!

Here is my working result on Windows 10:
In short, from what I have gathered from your previous message, it appears like this extention is not entirely necessary because it works out of the box (without it loaded I mean) on my Windows 10 setup.

[karim]

Nice. 😃

Is this what you were looking for? It is possible but not as simple as double-clicking a column with a file path for an image and it will automatically open an image viewer.

@mgrojo mentioned that this is possible using the same method you just used now.

1. Store the image path into a column.
2. Find a way to read that path and return the image as a BLOB data. fileio has a readfile function that you can use also instead of fsdir.
3. Open the data in the Edit Database Cell dialog and change the Mode to Image.

it appears like this extention is not entirely necessary because it works out of the box (without it loaded I mean) on my Windows 10 setup.

I don't know how this is possible. 😄

fsdir is not a standard SQLite function and it must be loaded somehow. Maybe you have it automatically loaded via Edit > Preferences > Extensions option?

[SilvioGrosso]

Hello @karim

Is this what you were looking for?

Yep, this image, with a Lizard on it, comes up from this discussion:
https://discuss.pixls.us/t/sharpening-techniques-lizard-subject/13427

Maybe you have it automatically loaded via Edit > Preferences > Extensions option?

Yep. I didn't know about that because I never opened this tab (option) before.
In short, I was completely unaware of this setting :-)

[mgrojo]

I'd like to maintain this issue open for the possible enhancement of a linked image mode. It is not currently discarded 😄

[SilvioGrosso]

Hello @mgrojo

Thanks a lot for not closing this ticket :-)

Just checked again LibreOffice Calc 6.2.4 on Windows 10 and I have realized how it works as regards the abolute - relative link to an image.
When you create a link (ctlr+k, as a shortcut in the cell where you insert the link) a path is created:
file:///D:/IMAGES_LINK/pictures/MAGNOLIA_GRANDIFLORA_STAMENS.jpg

When you cut the whole folder containing the images and the LibreOffice Calc sheet a new path is automagically created.
For instance, I have cut the previous folder (IMAGES_LINK) and moved it from D: to C: and here is one new link automatically created for the first image on Calc:
file:///C:/IMAGES_LINK/pictures/MAGNOLIA_GRANDIFLORA_STAMENS.jpg

On Windows 10 it is pretty straightforward to get the link for a bunch of images.

• You simply select all your images;
• while pressing the shift key, with the right button of your mouse on your images selected, you choose the option "Copy as a path";
  Here is the result for 4 images:
  "C:\IMAGES_LINK\pictures\LIZARD.jpg"
  "C:\IMAGES_LINK\pictures\MAGNOLIA_GRANDIFLORA_STAMENS.jpg"
  "C:\IMAGES_LINK\pictures\MONOCHAMUS_insects_world-collection.jpg"
  "C:\IMAGES_LINK\pictures\XANTHOMONAS_VESICATORIA_pepper_leaves_gyca-sp.jpg"
• Now you only need to paste this multiple path on LibreOffice Calc or directly on Db Browser. If you choose the latter in the Browse editor you add as many rows (e.g 4) as the paths to insert and you paste the first path in the first row (the other rows are filled later on) in the corresponding cell (text type). Later on, you fill in the other information of your table and you save your table with the new rows.
• In conclusion, I visualize these images, one by one, with the SQL code:
  select * from fsdir("D:\IMAGES_LINK\pictures\LIZARD.jpg");

At present, this is the best I can achieve with Db Browser as regards my images :-)
The drawback of Db Browser, compared to LibreOffice Calc, is that the path are not updated automatically when you move the folder on your drives.
In addition, it is not possible to launch a different software (.exe) to visualize the images by simply clicking on the cell with the link (I have got Google Picasa as a default to display them).

At present, I am quite happy with Calc as a "database" for my pictures since on Windows 10 this workflow works like a charm.

Thanks a lot for you work on Db Browser!

[mgrojo]

You keep closing this ticket whenever you answer 😄 If this is not intended then we have some poltergeist 👻

[SilvioGrosso]

Sorry about that (closing issue) it was not intended ! :-)

[mgrojo]

@SilvioGrosso I think this enhancement is mostly implemented. I needed support for opening URLs from database cells. Adding support for opening local files from cells and for inserting references to files, instead of file contents, was then straightforward. Relative filenames are interpreted from the current working directory. That could not be very useful except when launched from the command line, so we could improve it to use the DB file location as base. We should think about that.

Instructions:

• Use long click in "Import file" from "Cell Edit" dock to select the new "Save file reference..." option. The selected file will be inserted as a reference using full pathname. Alternatively you can paste files or URLs in cells.
• Use new button in "Cell Edit" dock to open the URL or file reference in the browser or default application for the file.
• Alternatively, Ctrl+Shift+Click over the cell, will also open the URL or file reference.

Could you give it a try with tomorrow's nightly build and report any rough edge or suggestion?

[mgrojo]

I've just commited an improvement that uses DB file location for relative paths. This will be definitively better, since current working directory is too unpredictable (I guess in Windows, when launched from the application menu, will be the directory where the program is located, and that isn't very useful.

There is also a message in the status bar when files or URLs are opened.

[SilvioGrosso]

Hi @mgrojo !

Just downloaded the last nightly (for Windows 10 - 64 bit, as usual):
DB.Browser.for.SQLite-win64.ZIP
Version 3.11.99 (Dec 23 2019)
Built for x86_64-little_endian-llp64, running on x86_64
Qt Version 5.12.6
SQLite Version 3.30.1

In all truth, I didn't find the button "Cell Edit" in the "Edit Database Cell (CTR+E)"

At present, to work with images (that is, mostly jpeg since they are compressed) with Db Browser I follow 3 different options:
Drag images + Import images + Copy-paste their link

For instance,
CREATE TABLE IMAGE
(ID INTEGER NOT NULL PRIMARY KEY,
NAME TEXT,
IMAGE BLOB);
-- create a table IMAGE to store my images

INSERT INTO IMAGE VALUES
(1, 'DRAG', 'BLOB'),
(2, 'IMPORT', 'BLOB'),
(3, 'COPY', 'BLOB')
--insert 3 rows with the 3 options to adopt later on (drag + import + copy link)

BTW, I have noticed that importing a file (jpeg) works fine the first time it is imported ("Import from file" icon). But later on, when I set again its mode as 'image' the jpeg is not visualized anymore (the box to visualize it is grayed out). To make it work (show) I need to import it once again...

Here is a video with all 3 methods with yesterday's build (DropBox link):
https://www.dropbox.com/s/xstd72z2rx4ql32/IMPORT_IMAGES_3-OPTIONS.mp4?dl=0

You can notice the lack of the button "Cell Edit" in the GUIs and the problem with visualizing image later on when they are imported (row number 2 of the table IMAGE)

A very minor glitch is that the Sql query ("Execute SQL" panel):
select * from fsdir ('D:\IMAGE_TESTNG/3_DITYLENCHUS_DIPSACI.jpg')
works fine but the box to visualize the result is not adapated to its image and you need to manually enlarge it.
This is NOT a bug of course, just a minor annoyance :-)

[mgrojo]

Hi @SilvioGrosso

In all truth, I didn't find the button "Cell Edit" in the "Edit Database Cell (CTR+E)"

Sorry, I meant that there is a new button in the "Edit Database Cell" dock (I always try to abbreviate that name). The button is this:

The button to import the filename is: . It is accessible after a long click over: . Although your current method of copying the file from the Windows Explorer works well also.

BTW, I have noticed that importing a file (jpeg) works fine the first time it is imported ("Import from file" icon). But later on, when I set again its mode as 'image' the jpeg is not visualized anymore (the box to visualize it is grayed out). To make it work (show) I need to import it once again...

Here is a video with all 3 methods with yesterday's build (DropBox link):
https://www.dropbox.com/s/xstd72z2rx4ql32/IMPORT_IMAGES_3-OPTIONS.mp4?dl=0

After importing the file, the data is loaded in the "Edit Database Cell" editor, but not into the DB. You need to click "Apply" there. I knew that this second action for importing could be easily overlooked. The same happens with the new "Save file reference..." button.

A very minor glitch is that the Sql query ("Execute SQL" panel):
select * from fsdir ('D:\IMAGE_TESTNG/3_DITYLENCHUS_DIPSACI.jpg')
works fine but the box to visualize the result is not adapated to its image and you need to manually enlarge it.
This is NOT a bug of course, just a minor annoyance :-)

I'll take a look; it could be improved.

By the way, I've committed a new enhancement for loading embedded data with the default application for that file type. There is a new button in "Edit Database Cell" dock , which saves the cell data to a temporary file with extension according to detected data and opens default external application for the file type. It then asks user to reload the data in a dialog when they have finished editing the data. It can be also used for viewing if the user cancels the reload. This enhancement will be in the next nightly build.

[SilvioGrosso]

Hi @mgrojo

After importing the file, the data is loaded in the "Edit Database Cell" editor, but not into the DB. You need to click "Apply"

Ok. Now I have got it :-)

By the way, I've committed a new enhancement for loading embedded data with the default application for that file type.

Thanks!
Tomorrow I am going to test this new option for sure!

[mgrojo]

A very minor glitch is that the Sql query ("Execute SQL" panel):
select * from fsdir ('D:\IMAGE_TESTNG/3_DITYLENCHUS_DIPSACI.jpg')
works fine but the box to visualize the result is not adapated to its image and you need to manually enlarge it.
This is NOT a bug of course, just a minor annoyance :-)

The next nightly will allow you to configure the "Adjust rows to content" (see issue #2006), but you would have to save the query as a view first (there's a button to make that easily), because in the query results for "Execute SQL" tab, you won't be able to enable it. This is probably work in progress.

[mgrojo]

@SilvioGrosso The release 3.12.0 has been published and it includes this enhancement, so I'll close this issue now. If there is something that could still be improved, please open a new issue. Regarding row height, we already have:

• Row height option (Row height option #419)
• Auto-width of all selected columns and auto-height of all selected rows (Auto-width of all selected columns and auto-height of all selected rows #2005)
• Is there a global setting for row height in the data browser? (Is there a global setting for row height in the data browser? #2081)