ISQL wishlist [CORE3376]

[firebird-automations]

Submitted by: @pavel-zotov

Votes: 2

1) provide the ability to run ISQL with default isolation level that is different than snapshot (e.g., ISQL -isol read_committed) 2) add option to avoid garbage collect when run every DML during session: ISQL -G ... my_db.fdb ISQL should NOT change isolation level to it's "default" one after each statement that has just finished (today ISQL changes every time isolation level to snapshot even if it was set to another value before running some statement); 3) often it is necessary to have a loop that surrounds execute block (for example, if after EB there is COMMIT and after commit I wish to start this EB again; maybe simple GOTO <label> can be implemented ?); provide ability to get OS-environment variables; 4) autocomplete key words and system tables with long names (like 'rdb$mon$attachments') when I typed only part of long name and pressing 'Tab' key; 5) the default typing mode is "overwrite characters" and the cursor form not alert about it; IMHO the default typing mode with normal cursor must be INSERT; 6) run the script named 'autorun.sql' if it is present in current directory (I mean that this script must be filled with commands that must be entered every time after start session); 7) can ISQL remember commands that I typed in it ? (even if I restart computer?) 8) why ISQL runs TWO transactions by default ? can it run in ONE transaction ? if no, can ISQL alert me that it runs TWO transactions (like when I type: ISQL -Z and it shows versions) ?

[firebird-automations]

Commented by: @hvlad

Most of the points due to not reading docs and not asking at support lists. Bad way to create tracker tickets.

> 1) provide the ability to run ISQL with default isolation level that is different than snapshot (e.g., ISQL -isol read_committed)

Default isolation level is read-committed. Anyway i see no sence to set transaction mode via command line.

2) add option to avoid garbage collect when run every DML during session: ISQL -G ... my_db.fdb

This is valid request. Also is will be good to have support for every isc_dpb_XXX tags at CONNECT statement.

> ISQL should NOT change isolation level to it's "default" one after each statement that has just finished (today ISQL changes every time > isolation level to snapshot even if it was set to another value before running some statement);

I.e. make SET TRANSACTION permanent. I also support this request but can't said if it will break existing scripts.

> 3) often it is necessary to have a loop that surrounds execute block (for example, if after EB there is COMMIT and after commit I wish to > start this EB again; maybe simple GOTO <label> can be implemented ?);

Lets don't make ibescript from isql

> provide ability to get OS-environment variables;

Examples of use, please ?

> 4) autocomplete key words and system tables with long names (like 'rdb$mon$attachments') when I typed only part of long name > and pressing 'Tab' key;

Nice to have but see above about ibe. If someone willing to implement it i will not object :)

> 5) the default typing mode is "overwrite characters" and the cursor form not alert about it; IMHO the default typing mode with normal > cursor must be INSERT;

Really ? Are you sure ? I never saw that default typing mode is "overwrite". At least on windows.

> 6) run the script named 'autorun.sql' if it is present in current directory (I mean that this script must be filled with commands that must > be entered every time after start session);

No, no and no. Lets not create dangerous holes in security.

> 7) can ISQL remember commands that I typed in it ? (even if I restart computer?)

I don't think it is good idea...

> 8) why ISQL runs TWO transactions by default ?

Because it is good way to work with mix of DDL and DML statements. More detailed answer is not for bug tracker, ask at support list.

> can it run in ONE transaction ?

SET AUTODDL OFF

> if no, can ISQL alert me that it runs TWO transactions (like when I type: ISQL -Z and it shows versions) ?

just query MON$TRANSACTIONS and be happy ;)

[firebird-automations]

Commented by: @pavel-zotov

>> 1) provide the ability to run ISQL with default isolation level that is different than snapshot (e.g., ISQL -isol read_committed)

>Default isolation level is read-committed. Anyway i see no sence to set transaction mode via command line.

Read-committed is in use only when I run ISQL without `-n` switch. And I really don't know which of these two transactions is used for DDL and which for DML. Example:

ISQL -n -user sysdba -pas masterkey test.fdb

Database: TEST.FDB SQL> set list on; SQL> select CON> a.mon$remote_process, CON> t.mon$transaction_id tid, CON> decode(t.mon$state,0,'idle',1,'active','unknown') state, CON> t.mon$timestamp started_at, CON> decode(t.mon$isolation_mode,0,'consistency',1,'concurrency',2,'rc recver',3,'rc no_rec') level_isol CON> from mon$transactions t join mon$attachments a CON> on t.mon$attachment_id=a.mon$attachment_id; commit;

MON$REMOTE_PROCESS C:\1INSTALL\FIREBIRD\FB_2_5\bin\isql.exe TID 71 STATE active STARTED_AT 8-MAR-2011 LEVEL_ISOL concurrency

Note that isolation level in this case is CONCURRENCY. The above query results in TWO transactions only if we launch ISQL without `-n` switch:

MON$REMOTE_PROCESS C:\1INSTALL\FIREBIRD\FB_2_5\bin\isql.exe TID 72 STATE active STARTED_AT 8-MAR-2011 LEVEL_ISOL concurrency

MON$REMOTE_PROCESS C:\1INSTALL\FIREBIRD\FB_2_5\bin\isql.exe TID 73 STATE active STARTED_AT 8-MAR-2011 LEVEL_ISOL rc no_rec

>> 3) often it is necessary to have a loop that surrounds execute block (for example, if after EB there is >COMMIT and after commit I wish to >> start this EB again; maybe simple GOTO <label> can be implemented ?);

>Lets don't make ibescript from isql

What about `purity` of experiments that can be achieved only via ISQL rather that any 3rd party tools ?

>> provide ability to get OS-environment variables;

>Examples of use, please ?

If we want some .sql run many times we should organize the batch with `LOOP` imitation:

SET ITER 0 :beg_label SET /A ITER = ITER + 1 ISQL -i our_script.sql -o our_result.txt test.fdb Goto :beg_label

Suppose we want to put into file `our_result.txt` the number of iteration, i.e. ITER variable. To do this, we must add ECHO-command in batch:

SET ITER 0 :beg_label SET /A ITER = ITER + 1 ECHO 'Iter #⁠' %ITER% > our_result.txt ISQL -i our_script.sql -o our_result.txt test.fdb Goto :beg_label

I think it would be more simple to invoke the content of OS environment just in ISQL when it running, e.g.: EXECUTE BLOCK RETURNS(MSG VARCHAR(255)) AS BEGIN /* ... SOME CODE HERE ... */
MSG=(SELECT 'ITER #⁠'||GET_ENV('ITER') FROM RDB$DATABASE); SUSPEND; END

>> 5) the default typing mode is "overwrite characters" and the cursor form not alert about it; IMHO the default typing mode with normal >> cursor must be INSERT;

>Really ? Are you sure ? I never saw that default typing mode is "overwrite". At least on windows.

Yes, I'm absolutely sure. You can check this 'strange' feature of ISQL on your own PC. Just type any command and than invoke it from history (via 'arrow up'). Than try to insert some character in the middle of it.

>> 6) run the script named 'autorun.sql' if it is present in current directory (I mean that this script must be filled with commands that must >> be entered every time after start session);

>No, no and no. Lets not create dangerous holes in security.

May be you right. But anyway it will be useful that only SET-command will be taken in account from this file. For example:

SET LIST ON; SET AUTODDL OFF; SET WIDTH X 100

>> 7) can ISQL remember commands that I typed in it ? (even if I restart computer?)

>I don't think it is good idea...

Why ? Most people works on the same machines from day to day.

>> if no, can ISQL alert me that it runs TWO transactions (like when I type: ISQL -Z and it shows versions) ?

>just query MON$TRANSACTIONS and be happy ;)

SELECT COUNT(*) FROM MON$TRANSACTIONS T WHERE T.MON$ATTACHMENT_ID = CURRENT_CONNECTION; COMMIT;

So, you advice to type 98 chars only for knowing that my ISQL-session runs two transaction rather than single ? It's cool :-)

[firebird-automations]

Commented by: @hvlad

>>> 1) provide the ability to run ISQL with default isolation level that is different than snapshot (e.g., ISQL -isol read_committed) > >>Default isolation level is read-committed. Anyway i see no sence to set transaction mode via command line. > >Read-committed is in use only when I run ISQL without `-n` switch. And I really don't know which of these two transactions is used for DDL and >which for DML.

a) it is more than easy to learn if you aware of monitoring tables b) you can ask about it at support list and don't spam tracker

>>> 3) often it is necessary to have a loop that surrounds execute block (for example, if after EB there is >COMMIT and after commit I wish to >>> start this EB again; maybe simple GOTO <label> can be implemented ?); > >>Lets don't make ibescript from isql > >What about `purity` of experiments that can be achieved only via ISQL rather that any 3rd party tools ?

Bad luck. And what ? We have a lot of cases which is not reproducible using isql only.

>>> provide ability to get OS-environment variables; > >>Examples of use, please ? ... > I think it would be more simple to invoke the content of OS environment just in ISQL when it running, e.g.: > EXECUTE BLOCK RETURNS(MSG VARCHAR(255)) AS > BEGIN > /* ... SOME CODE HERE ... */ > MSG=(SELECT 'ITER #⁠'||GET_ENV('ITER') FROM RDB$DATABASE); > SUSPEND; > END

your GET_ENV looks like UDF or built-in function. So, it is run at *server* process. But you want to use environment variables defined at *client* process...

>>> 5) the default typing mode is "overwrite characters" and the cursor form not alert about it; IMHO the default typing mode with normal >>> cursor must be INSERT; > >>Really ? Are you sure ? I never saw that default typing mode is "overwrite". At least on windows. > > Yes, I'm absolutely sure. You can check this 'strange' feature of ISQL on your own PC. > Just type any command and than invoke it from history (via 'arrow up'). Than try to insert some character in the middle of it.

Tried. Mode was not changed. It is "insert", as expected.

>>> 6) run the script named 'autorun.sql' if it is present in current directory (I mean that this script must be filled with commands that must >>> be entered every time after start session); > >>No, no and no. Lets not create dangerous holes in security. > > May be you right. But anyway it will be useful that only SET-command will be taken in account from this file.

This is ugly, sorry.

>>> 7) can ISQL remember commands that I typed in it ? (even if I restart computer?) > >>I don't think it is good idea... > > Why ? Most people works on the same machines from day to day.

because it will make a lot of related issues : where to save command's history ? what if few isql instances runs simultaneously ? what about few different isql locations - should they all share same history or not ? etc, etc, etc...

>> if no, can ISQL alert me that it runs TWO transactions (like when I type: ISQL -Z and it shows versions) ? > >>just query MON$TRANSACTIONS and be happy ;) > > SELECT COUNT(*) FROM MON$TRANSACTIONS T WHERE T.MON$ATTACHMENT_ID = CURRENT_CONNECTION; COMMIT; > > So, you advice to type 98 chars only for knowing that my ISQL-session runs two transaction rather than single ? It's cool :-)

Life is hard, isn't is ? :)

I don't object in general to make isql more advanced tool. But new features should be useful for many people, not just for you ;)

[firebird-automations]

Commented by: @pavel-zotov

>>Read-committed is in use only when I run ISQL without `-n` switch. And I really don't know which of these two transactions is used >>for DDL and which for DML.

>a) it is more than easy to learn if you aware of monitoring tables >b) you can ask about it at support list and don't spam tracker

Ok, I`ll try to ask about it at http://sql.ru. I think that this issue can be closed here.

[firebird-automations]

Commented by: @pavel-zotov

in addition to foregoing wishlist:

9) make ISQL start transaction only when I actually type DML / DDL statement and pressed Enter rather than just after I launch ISQL. For example, if I only whant to know version of server I run such command: ISQL -Z my_database.fdb ISQL starts at this moment two transactions and wait in it's command prompt for my typing. If this is a production database, ( OAT - OIT ) can grow to very big values until I exit ISQL.

[firebird-automations]

Commented by: Mykhaylo Masyk (miwa)

>>>> 7) can ISQL remember commands that I typed in it ? (even if I restart computer?) >>>I don't think it is good idea... >> Why ? Most people works on the same machines from day to day. >because it will make a lot of related issues : where to save command's history ? what if few isql instances runs simultaneously ? what about few different isql locations - should they all share same history or not ? etc, etc, etc...

Its really good idea, as for me. Even if it is not so easy to release it. History can be saved in \~/.isql_history like \~/.bash_history in unix. If few instances runs simultaneously, these files can be named like \~/.isql_history_0, \~/.isql_history_1 etc or something like that. In a case of different isql locations the same approach can be used. In a windows systems the first dot in filename may be omitted.

[firebird-automations]

Commented by: @pavel-zotov

in addition:

10) make ISQL display only statistics WITHOUT result, something like: SET STAT ONLY; This feature will be useful when only statistics is interesting but not result of some query that is often huge.

[firebird-automations]

Commented by: @pavel-zotov

11) is it possible to abbreviate SET TRANSACTION statement to SET TRAN or SET TRANS ? Also there is no mention about "set transaction" in ISQL-help:

SQL> help set; Set commands: <... skipped... > SET STATs -- toggle display of performance statistics SET TIME -- toggle display of timestamp with DATE values SET TERM <string> -- change statement terminator string SET WIDTH <col> [<n>] -- set/unset print width to <n> for column <col>

All commands may be abbreviated to letters in CAPitals

[firebird-automations]

Commented by: @asfernandes

SET TRANSACTION is not an ISQL command. It's completely treated in the engine.

[firebird-automations]

Commented by: @pavel-zotov

12) add comand like 'whoami' in linux or add ability to see this in PROMPT, like: [sysdba role sysdba]SQL> [accounter role manager]SQL> etc. Currently there is no way to know this in the middle of the job. Such info is displayed only at the start time of session.

[firebird-automations]

Commented by: @pavel-zotov

13) add ability to TRIM trailing spaces when output varchar columns. For example, if I write the query that parses RDB$PROCEDURE_SOURCE (blob) and separate it on lines then I have to assume some maximal length of each line (e.g. 8192, 16384 or even 32760 characters - who knows which style of coding was applied by developers of these stored procs). isql will output all of lines with this maximal length - even if some line contains of a few words.

Result text file will be huge (tens of MB) and it almost impossible to be edited by common text processors So I have to trim this output via OS utilities (sed in linux, for example). This is inconveniently.

Some variant of SET command (like SET TRAILING TRIM ON) and command switch ('-tt' or '-ts') will be very useful.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Creating a single case for all your issues is not the correct usage for this tracker. Each request/feature should be a separate case, so that they can be reviewed, discussed and implemented (or rejected) as appropriate. A single case makes it impossible to track the individual items.

[firebird-automations]

Commented by: @pavel-zotov

PS. Of course in case of output several columns we can trim trailing spaces only from LAST field because of formatting problems. So, I'm asking about exactly this case: ability to trim trailing spaces from last varchar column.