SQLite Forum

Feature request for command shell: External editing of schema
Login

Feature request for command shell: External editing of schema

(1) By anonymous on 2020-05-13 23:55:18 [source]

I have a feature request for the SQLite command shell, which is to add a command to edit the definition of a view and its triggers (whether or not it already exists) using an external editor (such as Vim). The same can be used for editing the triggers of tables, and for the table definition itself if the table is empty. Maybe it works for indexes also.

(2) By Richard Hipp (drh) on 2020-05-14 00:40:20 in reply to 1 [link] [source]

Possible work-around:

  • .shell $EDITOR tempfile.txt
  • .read tempfile.txt

(3) By anonymous on 2020-05-14 09:20:51 in reply to 2 [link] [source]

I would like to second the request.

The suggested workaround is quite a lot of extra work and not very smooth for repeating lots of edit/test cycles. It also puts tempfile naming and eventual cleanup on the user. PostgreSQL's psql command has a great \e feature for combining the above. Perhaps it could be a source of inspiration...

.edit [FILENAME]

With no FILENAME the .edit command could put the last SQL command in a temp file to edit and execute. This would also have the advantage of putting actual SQL in shell history instead of uninformative .shell and .read commands.

Such a syntax would be particularly useful given the SQLite shell's command history being per line instead of per command like most other shells. Re-running a previously multi-line command in SQLite is quite a counting exercise (5 times up & return, 5 times up & return, 5 times up & edit & return, 5 times up,...) Perhaps that deserves a whole feature thread on its own.

(4.1) By Tony Papadimitriou (tonyp) on 2020-05-15 07:17:51 edited from 4.0 in reply to 3 [link] [source]

Philosophical question: Can an anonymous user second a request from either an anonymous or eponymous user considering the latter could be the same person? :)

(Also, can an eponymous user second an anonymous request?)

(5) By Ryan Smith (cuz) on 2020-05-15 10:59:27 in reply to 4.1 [link] [source]

Yes but NEVER do it.

The Matrix will crack and we will all implode...

(6) By anonymous on 2020-05-15 17:39:27 in reply to 3 [link] [source]

The command you may want to edit might not be in the history; it might be in the sqlite_master table. For example, you might have a view and trigger defined as:

CREATE VIEW "abc"(x,y,z) AS SELECT x,y,z FROM "cards" WHERE x < 0;

CREATE TRIGGER "abc_1" INSTEAD OF INSERT ON "abc" BEGIN
  INSERT INTO "abc"(x,y,z) VALUES(new.x,new.y,new.z);
END;

Then you will want to edit the definition of that view, to create a temporary file with:

BEGIN;

DROP VIEW "abc";

CREATE VIEW "abc"(x,y,z) AS SELECT x,y,z FROM "cards" WHERE x < 0;

CREATE TRIGGER "abc_1" INSTEAD OF INSERT ON "abc" BEGIN
  INSERT INTO "abc"(x,y,z) VALUES(new.x,new.y,new.z);
END;

COMMIT;

And then once you edit that file and save it and then it is executed, it will recreate the view with the new definition (or the same definition, if you have not changed it).

(7) By Mark Lawrence (mark) on 2020-05-16 13:50:05 in reply to 6 [link] [source]

( By the way, I am anonymous above - philosophize on that as you will :-)

You are right - my proposed .edit command does not answer your feature request exactly. So I would ammend my syntax proposal to the following:

.edit [--table|-t NAME] [FILENAME]

Where --table NAME dumps the matching begin/table-or-view+triggers/commit definitions (into FILENAME if given or a tmpfile otherwise) to be externally $EDITOR'd and .read. Now that I have described it I can say I would have used this about 50 times today already if it existed.

p.s. There is also a similar \ef in psql for editing function definitions