Feature request: Editable table & combo box #3

Activity

1. leoedin commented on Jun 7, 2023

   

   leoedin

   on Jun 7, 2023

   Author

   More actions

   Can you list the step you took, starting from opening the website, and where the first pain points were ? Did you go sql.ophir.dev -> Get started -> technical, then downloaded the binary, wrote the first select, and hit a wall ? What did you do then ? What is the app you were trying to build ?

   Yeah, exactly that. The first problem was that the "shell" component was missing (but that sounds like a bug). Once I had that, the GUI components I included started appearing - but I had to copy the first few lines of the example on github to make it work.

   I was playing around with making a simple inventory tracking system - it's basically a bunch of relational tables defining products, suppliers, customers, orders, shipments etc - with the idea that the total inventory can be summarised by outbound shipments - inbound shipments. I need interfaces to insert things, and then a bunch of pages for reporting the state of the database - basically rending a few different SQL queries to show total stock, lists of orders etc. It's mostly a toy project to replace a few excel spreadsheets that I've been trying over the past few months in various no-code tools, but always running up against limitations due to their implementation details. Most no-code tools do very well with simple mappings between a database table and what you see on screen, but fall apart with relational data (like having many-to-many relations defined by a separate table). I can handle all of that in SQL, but most no-code tools don't let you edit the SQL.

   The most important thing for me is that the underlying database structure is tool-agnostic - if I start hitting limitations with whatever low-code tool makes the CRUD part (and the database is useful enough to invest time in) I can port it to a "real" framework.
2. lovasoa commented on Jun 7, 2023

   

   lovasoa

   on Jun 7, 2023

   Last edited by lovasoa

   Collaborator

   More actions

   Thank you for opening this, and for all the feedback !

   • About queries not working when not explicitly selecting the shell component: that should be fixed. Can you confirm v0.6.8 works with the first SQL you tried, without a shell ?
   • About examples, we currently have https://github.com/lovasoa/SQLpage/tree/main/examples . I'll add a link to it higher on the front page, and in the technical instructions. Is the inventory tracking system you were working on proprietary ? If not, maybe you could contribute it to the examples folder on this repo ?
   • About examples with hardcoded data, you are talking about the examples in the documentation page on the official site, right ? Currently, they are rendered dynamically, and help ensure they are always up to date and work. But I could add another type of examples, that are not dynamic, but demonstrate dynamic data querying. I think that would be useful in particular for the form component.

   Features

   • searchable table of database items with edition : if the information is tabular, there is no great way to do that today, agreed. What I would use today is a list or card component, with clickable items. To do that with a table, I was thinking about the following two ideas (that may not be incompatible between themselves):
     • add a row-level property named link to the "table" component. The entire row would become clickable. You would to something like select 'table' as component; select *, 'details.sql?id=' || id as link from my_items;.
     • add support for markdown to the table component. When a column name ends in _md, interpret the column contents as markdown. You would use it like so:

     • select 'table' as component;
       select *, '[edit](details.sql?id=' || id || ')' as actions_md from my_items;

       • Problem is, depending on where the value comes from, it may lead to a markdown injection from a malicious user.
       • (both solution have the downside that usage may become unintuitive to an user who wants a column in their table named "link" or "_md".
   • select from a predefined set of values that come from the db : What would you think about something like

     •  SELECT 'form' as component;
        SELECT 'Friend' as name, 'select' as type, group_concat(family_name, '\n') AS options FROM people;

   • checkbox value not sent when not checked : I haven't checked, but I think it's the way the browser works. It doesn't send the value. I'm not a super fan of abstracting that away. But I could definitely add examples to make it more obvious how to work with them.
   • JSON API I've been thinking about it. I'll make a separate issue to track that. It's definitely something I'd love to have, but haven't figured all the technical details about it yet.
3. leoedin commented on Jun 7, 2023

   

   leoedin

   on Jun 7, 2023

   Author

   More actions

   select from a predefined set of values that come from the db : What would you think about something like

   SELECT 'form' as component;
   SELECT 'Friend' as name, 'select' as type, group_concat(family_name, '\n') AS options FROM people;
   

   That could work - although to be useful it'd need to preserve the primary key of each row in people - so you can create a relationship with the subsequent information. You really need 2 lists for the options - the displayed string, and the underlying HTML value element.
4. leoedin commented on Jun 7, 2023

   

   leoedin

   on Jun 7, 2023

   Last edited by leoedin

   Author

   More actions

   (both solution have the downside that usage may become unintuitive to an user who wants a column in their table named "link" or "_md".
   Yeah, that's tricky. _md is probably less likely to clash with someones columns than link. It's also more extensible.

   Could you pass a list of markdown-rendered rows in the first SELECT? Something like

   select 
   	'table' as component,
   	1 as search,
   	'actions' as markdown;
   SELECT 
    *, '[edit](details.sql?id=' || id || ')' as actions from my_items;
   

   Maybe you'd want the _md to be mandatory, I don't know. Still possible to render markdown from the database, but at least it'd have to be explicit.
5. lovasoa commented on Jun 7, 2023

   

   lovasoa

   on Jun 7, 2023

   Collaborator

   More actions

   That could work - although to be useful it'd need to preserve the primary key of each row in people - so you can create a relationship with the subsequent information. You really need 2 lists for the options - the displayed string, and the underlying HTML value element.

   SELECT 'form' as component;
   SELECT 
       'Friend' as name, 
       'select' as type, 
       group_concat(family_name, '\n') AS options
       group_concat(id, '\n') AS option_values
   FROM people;

   ?

   👍React with 👍1Reacted by leoedin
6. lovasoa commented on Jun 7, 2023

   

   lovasoa

   on Jun 7, 2023

   Collaborator

   More actions

     select 
     'table' as component,
     1 as search,
     'actions' as markdown;
   SELECT 
   *, '[edit](details.sql?id=' || id || ')' as actions from my_items;

   Yes, that sounds better ! I'll see how feasible it is

   👍React with 👍1Reacted by leoedin
7. lovasoa commented on Jun 9, 2023

   

   lovasoa

   on Jun 9, 2023

   Last edited by lovasoa

   Collaborator

   More actions

   I rewrote the landing page and the getting started instructions on the official website to try and make them easier to understand. @leoedin , can you tell me if the new version is better ?

   https://sql.ophir.dev/
8. lovasoa commented on Jun 10, 2023

   

   lovasoa

   on Jun 10, 2023

   Collaborator

   More actions

   Now that I've opened separate issues for the feature requests mentioned above, I'll convert this thread to a github discussion.
9. sqlpage

   locked and limited conversation to collaborators on Jun 10, 2023
10. lovasoa

    converted this issue into a discussion #11 on Jun 10, 2023