search

cldellow / datasette-ui-extras

Add editing UI and other power-user features to Datasette.
Apache License 2.0
12 stars 1 forks source link

Edit UI (DDL) #54

Open cldellow opened 1 year ago

cldellow commented 1 year ago

48 is about a UI to edit data. That presupposes that you have an existing schema of tables. For a non-technical user, writing SQL to create such tables may be too high of a barrier.

  1. For users with create-table permission, add a Create table button on the Database page. It should make a new, unnamed table and redirect you to it. The table will have a single rowid id column.

  2. For users with create-table permission, the table page should show a link to customize the table.

  3. The customize table page should let you rename a table.

  4. The customize table page should let you drop the table. You should have to type something to confirm.

  5. The customize table page should let you add a simple column:

    • BOOLEAN
    • TEXT
    • DATE
    • DATETIME
    • INTEGER
    • REAL

  6. The customize table page should let you add a column that is a non-compound foreign key to another table.

  7. The customize table page should let you rename a column.

  8. The customize table page should let you drop a column.

  9. The customize table page should let you toggle NULL/NOT NULL for a column

  10. The customize table page should let you add restrict the values that a TEXT column can use. It should add a CHECK (column IN (...)) expression.

    • We should use the form of constraints that give a meaningful name in the error, eg CONSTRAINT tablename_column_valid CHECK (column IN (...))

  11. The customize table page should let you add a UNIQUE constraint on a column.

  12. The customize table page should let you re-order columns.

cldellow commented 1 year ago

As of Datasette 1.0, Datasette depends on sqlite-utils. It has some support for modifying schemas, see the sqlite-utils Transforming tables documentation.

However, after digging in a little bit, it's both somewhat opinionated (eg https://github.com/simonw/sqlite-utils/issues/524) and not as full-featured as what I'd like (eg https://github.com/simonw/sqlite-utils/issues/358)

Table migrations are pretty important, I think I'm not comfortable doing monkeypatching shenanigans here.

In fact, I think I'd like to be even stricter than sqlite-utils: if I can't parse the table definition and fully understand it, I'd probably want to stop processing.

Unfortunately, I think this might mean that I write my own code here. :( It might be a little simpler, in that it can focus on doing 1 step at a time, which might make this a bit more tractable?