feature: export dataview query result to table

[RiCkYB-667]

Hello there!

Do you think it would be possible for you to create such a method, that could populate an SQL table using the results of a dataview query, rather than the frontmatter?

Table name would be the note's name, and there would be a single dataview query on the note, and your code would upload the result as it is rendered by the dataview (with column names and values)
I am desperate to find some sort of solution so i can easily do an SQL query on the data. The current method of a json string being uploaded into a single record is not suitable for me unfortunately...

Thank you for your kind answer!

[clouedoc]

Hi, sure, can you send me an example data view query + Markdown files? Also, I would be interested in the SQL query that you are planning to use. - Camille 18 nov. 2022, 23:27 de ***@***.***:

…

Hello there! Do you think it would be possible for you to create such a method, that could populate an SQL table using the results of a dataview query, rather than the frontmatter? Table name would be the note's name, and there would be a single dataview query on the note, and your code would upload the result as it is rendered by the dataview (with column names and values) I am desperate to find some sort of solution so i can easily do an SQL query on the data. The current method of a json string being uploaded into a single record is not suitable for me unfortunately... Thank you for your kind answer! — Reply to this email directly, > view it on GitHub <#5>> , or > unsubscribe <https://github.com/notifications/unsubscribe-auth/ADKG2SWGTKOCM6H33CKVYNLWI77ELANCNFSM6AAAAAASE557XE>> . You are receiving this because you are subscribed to this thread.> Message ID: > <clouedoc/postgresql-obsidian/issues/5> @> github> .> com>

[RiCkYB-667]

Please find a very basic set of markdown files on the link : https://drive.google.com/file/d/19xncVZ782mDXk1ZbksOiAnQAlOZV45GF/view?usp=sharing

My use case would be that i build all the RolePlaying elements in Obsidian (characters, items, etc) and upload them using their metadata to SQL, so my gameserver can query it and use all the provided data for the online game.
Dataview would be needed in obsidian to "precompose" the datasets.

Should a reverse query would be possible, please let me know, because that would be non plus ultra!
I mean making a query from the SQL database and with those values updating the corresponding files' metadata.
In case anything is changed in the SQL by a different software, so i could have a two way sync between Obsidian and the SQL.

Oh and i would love to buy you a coffe, or somehow show my gratitude! Please give me a paypal or anything where i can do that! ;)

[clouedoc]

That's a cool use case 😮
I will try to throw a few hours at it, but can't promise anything.

In case anything is changed in the SQL by a different software, so i could have a two way sync between Obsidian and the SQL.

Yeah that would be nice.

Oh and i would love to buy you a coffe, or somehow show my gratitude! Please give me a paypal or anything where i can do that! ;)

I sure would love a coffee, but wait until you get your feature unless you're already making use of this plugin.. Otherwise I will get affected by reactance 🙂

I will setup a GitHub sponsors in the afternoon

Implementation details

User experience

Here's what I'm thinking about, based on your suggestions:

1. The user creates a note that will be dedicated to uploading the data
2. The note has frontmatter with postgresql-table: true
3. There is a single dataview query inside the note
4. The results of the dataview query automatically gets uploaded on trigger.

Triggers:

• the user updates the database note
• the user updates the frontmatter of a note referenced in the results of the dataview query
• the user asks the plugin to "upload all the vault to SQL"
• the user executes a "upload dataview to database" command

Database stuff

Creating the table

1. Create the table if it doesn't exists
2. Create all the rows (infer data types from all Dataview rows)

Uploading each row

1. Upload the row
2. The filename serves as a primary key for pushing and pulling data

Technical challenge

The problems here are:

• inferring the right data types for the SQL columns
• fetching and applying updates from the database
• not destroying/re-creating rows (could cause problems with Foreign Keys on the user's side)

[RiCkYB-667]

As for the technical challenges:
I think we can agree that this method can not be code-controlled enough to eliminate user error. Whoever would use this has to have certain knowledge and discipline.

inferring the right data types for the SQL columns

I am not sure of the available methods, but i would approach by doing simple math with all columns' values and if it fails then stick to varchar().

not destroying/re-creating rows (could cause problems with Foreign Keys on the user's side)

I would be perfectly fine with destroying all tables and re-creating them when uploading from Obsidian to SQL, to eliminate any overwrite or duplication errors.

fetching and applying updates from the database

This i think can actually be skipped. Let's take the Obsidian as the ultimate vault of data and information. The backway synchronization from SQL could cause severe data loss if the database would become corrupt...