Implementing Views

[missyschoenbaum]

Nature of the problem - the results tables are somewhat complex for users to understand. We have given example queries, but there is still a learning curve especially if you want to join in a production type name or something that has to cross back over into the scenariocreator tables. It has just occurred to me that at the same time we build all the database structures, we could build views. Views function as saved queries and can serve as a stepping stone. They just repackage the results in a way that the dev staff doesn't have to do all the work and I can write shorter documentation to get people started. It is hard for me to predict what SQL Editor people will end up with, this gives us a little more flexibility.

I can write the scripts or just the queries. Does anyone see a problem with this?

[BryanHurst]

Research implementing a SQL viewer.

[BryanHurst]

https://github.com/groveco/django-sql-explorer

[missyschoenbaum]

Wow, many things I like. Some hard to check unless I put my hands on it (like code complete?) Brilliant that it can block destructive words and limit the schema to show. Seems like query history should be local, as compared to shared with me. I think we would be running the most streamlined version. Didn't understand the multiple connection, which is what we would need. Nice if it could share queries, but you clearly understand which database you are in. The concept of views would still add value beyond this, as ad-hoc queries are too hard to manage, even for me.

[missyschoenbaum]

This is brilliant. I am going to add notes in here so I don't forget them. Manage password Make views vs saving more queries, same amount of work for me, what make more sense. what are implementation problems of this, as view is building a database object. How do we add value to user of not having to learn how to dig through this. Make list of what to include/exclude in schema names. Having saved queries/views already using scenario tables helps us hide those, but gives examples if someone wants to do the same to call a user-friendly name.

[missyschoenbaum]

I added two dependencies on this. We need to fix before we implement actual queries in final version, but shouldn't stop other work.

[missyschoenbaum]

@BryanHurst any idea how many characters I have in the description space? I am working up basic queries with descriptions.

[BryanHurst]

I would have to look in the source code. You may potentially have essentially unlimited characters if it is stored as a text block. But if you wanted to be safe, I would go with 256 chars until I can look deeper.

On Mar 6, 2018 9:03 AM, "Missy Schoenbaum" notifications@github.com wrote:

@BryanHurst https://github.com/bryanhurst any idea how many characters I have in the description space? I am working up basic queries with descriptions.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/NAVADMC/ADSM/issues/828#issuecomment-370831676, or mute the thread https://github.com/notifications/unsubscribe-auth/ABILBbmWiZfZVCZFGw5omlskcAWnjxggks5tbrNUgaJpZM4QzAgI .

[missyschoenbaum]

@BryanHurst if there isn't going to be time to work on this, I can do some documentation around how it currently is set up. Even if we popped up a message that said "Use the following password - ADSM" that would get them into it.

[BryanHurst]

@missyschoenbaum while I'm waiting on #879, I've pushed a bit of work on this issue (You can update and see the changes)

I updated the plugin to the latest version which fixed some bugs I had seen previously.
I removed the need to login.
Made links back to ADSM.
Made a slightly better button to get to it.
Tweaked expression permissions so you can modify rows, but not the schema.

What other types of changes would you like so I can start a priority list for this issue?

[BryanHurst]

It is 12:36 and I just release a small fix to this. It will show as integrity error in the update panel.

[missyschoenbaum]

Just an interesting note, the permissions have changed somehow so I don't have to make it run the update more than once to get it to go. It used to tell me I didn't have permission on the first attempt, and sometimes on the second attempt, but would eventually update if I kept trying.

[missyschoenbaum]

This looks great. I was able to get to it with no password, write a simple query, export results file with column headers, see the schema, and go back to ADSM.

@BryanHurst do we think there are other things we want to do on this ticket? If so, we can move to backlog and revisit in future.

[BryanHurst]

I think for now we can move it to the backlog. But a list of feature requests for it would be nice.

[missyschoenbaum]

Feature List: No update to database - I didn't test so may not happen anyway

[missyschoenbaum]

Documentation is up on this

[BryanHurst]

@missyschoenbaum I just realized that we can pre-load queries into this so that users already have examples ready to go.

[missyschoenbaum]

OK, I have been loading them as individual text files into the VX rings branch, Sample Scenario folder. Just added the Cost by production type this morning.

[missyschoenbaum]

This is now being covered by SQL explorer and query library