Edit UI (DDL)

[cldellow]

#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]

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 simonw/sqlite-utils#524) and not as full-featured as what I'd like (eg simonw/sqlite-utils#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?