Views

[opsecx]

Hi, in reference to discussion in discord with Lola the other day: It would be great for those of us using the db directly for our applications, if views could be provided which corresponds to the old deleted data-tables, so as to preserve compatibility. Leaving a note here on this as requested.

[opsecx]

(as I've said previously, I'm not a fan of the new data model at all, but if views were provided, it would mitigate some of the trouble for us with changing everything in our applications)

[rllola]

I am done with the PR. https://github.com/Zondax/namadexer/pull/167

@opsecx I invite you to test it before merging (no need for resync it will just create the views)

[opsecx]

I am done with the PR. https://github.com/Zondax/namadexer/pull/167

@opsecx I invite you to test it before merging (no need for resync it will just create the views)

awesome, how do I pull this? (not super familiar with git yet but learning :))

[opsecx]

ok I see it's merged now, so pulling main should do the trick?

[rllola]

ok I see it's merged now, so pulling main should do the trick?

Yes it should. There is no resync to do.

You can then explore the views. An example :

blockchain=# SELECT * FROM shielded_expedition.tx_transfert LIMIT 10;
                    source                     |                    target                     |                     token                     |  amount   
-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------
 tnam1qzqwn5py3tlg9kzulljeu4f048pv7a8qqg0suzzq | tnam1qzqwn5py3tlg9kzulljeu4f048pv7a8qqg0suzzq | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 1.000000
 tnam1qr6uru52j0q9tchduasr692f0es8rusa2gvq5v3t | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 1.000000
 tnam1qq45sy8kmzrv823rmgqt6ghwuj6jc70rr59055mw | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000
 tnam1qqpqshqak0cfry9glanntrvv6wwp9lzgnvknen9p | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000
 tnam1q850pdcfxm7fga0fw9y6avr7jyjye4qxayt6ftk8 | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 20.000000
 tnam1qrtvksav4jgag6eejmavav7d7pxgsgr47uzynu77 | tnam1qrtvksav4jgag6eejmavav7d7pxgsgr47uzynu77 | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 1.000000
 tnam1qqad3tglrvppcx5wyfdjnl488s3d898wvsv5mn6f | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000
 tnam1qrjdug0hfyuxhkrncx8ezk8dzhfxsrwjdgze0mru | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000
 tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qruqt7q7nh9c9ser09yjrhwnuqt5fz2rkc068k35 | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 4.000000
 tnam1qq9je57dm384utn6q44ykrrmhdlhgypxqcc324t4 | tnam1qz78mgzlzlpwlrru7a0fnjdyylaxvsecrqmwj257 | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 20.000000
(10 rows)

[opsecx]

ok I see it's merged now, so pulling main should do the trick?

Yes it should. There is no resync to do.

You can then explore the views. An example :

blockchain=# SELECT * FROM shielded_expedition.tx_transfert LIMIT 10;

                    source                     |                    target                     |                     token                     |  amount   

-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------

 tnam1qzqwn5py3tlg9kzulljeu4f048pv7a8qqg0suzzq | tnam1qzqwn5py3tlg9kzulljeu4f048pv7a8qqg0suzzq | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 1.000000

 tnam1qr6uru52j0q9tchduasr692f0es8rusa2gvq5v3t | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 1.000000

 tnam1qq45sy8kmzrv823rmgqt6ghwuj6jc70rr59055mw | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000

 tnam1qqpqshqak0cfry9glanntrvv6wwp9lzgnvknen9p | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000

 tnam1q850pdcfxm7fga0fw9y6avr7jyjye4qxayt6ftk8 | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 20.000000

 tnam1qrtvksav4jgag6eejmavav7d7pxgsgr47uzynu77 | tnam1qrtvksav4jgag6eejmavav7d7pxgsgr47uzynu77 | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 1.000000

 tnam1qqad3tglrvppcx5wyfdjnl488s3d898wvsv5mn6f | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000

 tnam1qrjdug0hfyuxhkrncx8ezk8dzhfxsrwjdgze0mru | tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 10.000000

 tnam1pcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqzmefah | tnam1qruqt7q7nh9c9ser09yjrhwnuqt5fz2rkc068k35 | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 4.000000

 tnam1qq9je57dm384utn6q44ykrrmhdlhgypxqcc324t4 | tnam1qz78mgzlzlpwlrru7a0fnjdyylaxvsecrqmwj257 | tnam1qxvg64psvhwumv3mwrrjfcz0h3t3274hwggyzcee | 20.000000

(10 rows)

This is pretty neat, just installed it and seems to be there. Is there any reason why the one view is named as transfert and not transfer like the old tables? almost came here wondering if it was a typo

[opsecx]

@rllola had a more on-hands look now. the principle is great, the data availability is different from the old tables however. in particular I need a tx_id to join on the other tables, the bare info in tx_transfert is not of use without that. can that be amended as to better match the structure of the old tables? Similarly with new view tx_vote_proposal (which previously was named more aptly just vote_proposal), where there is also no tx_id for me to join on the tables with info on the transactions themselves. if I can get data availability more like the older tables, I'm pretty sure this would fix all my gripes if (and I guess that remains to be clarified) performance is not significantly worsened on queries. thanks for all your work on this!

[opsecx]

I'm guessing this ties into a bigger discussion if there should be more consistency in db structure or if that's mainly considered "internal" and up for change at any point.

[opsecx]

was looking at amending through pr, but using the current sql code for changing existing views isn't taken so well, so got stuck there for now.