most people can figure out an sql command syntax to do what they need much 
easier than they can write a macro to do the same thing because of the requisite concepts of 'subroutine' and 'dimension' which most people dont know about and the different interface you need to work with to write a macro.

if we provide these types of query functions it would speed adoption of Base with a lot of smaller simpler project users and make others more productive to boot.  

0) we should probably control access rights directly not by limiting functionality of update/insert/drop functions in sql-editor.


----if a non-destructive query is considered a requirement such that update/insert/drop are not acceptable then the macro for these queries should be made simpler to access/setup.




----the problem with adding destructive queries to the sql editor is presumably that it concerns adminsitrators because it makes potential tinkering damage by random users more likely.  




so the real problem to be tackled should be not IF to add the update/add/remove functionality but HOW to add the add/update/remove query functionality to sql editor while limiting any pottential misuse.  



so maybe the most productive place to put that access control would be to pull it from the permission structure of the hsqldb/mysql/postgresql database instead of limiting the functionality of the Base database.



http://hsqldb.org/doc/2.0/guide/accesscontrol-chapt.html



     "Access Rights



     By default, the objects in a schema can only be accessed by the schema owner. 

     But the schema owner can grant privileges (access rights) on the objects 

     to other users or roles.



     Things can get far more complex, because the grant of privileges 

     can be made WITH GRANT OPTION. In this case, the role or user that has been granted 

     the privilege can grant the privilege to other roles and users.



     Privileges can also be revoked from users or roles.



     The statements for granting and revoking privileges normally specify which privileges 

     are granted or revoked. However, there is a shortcut, ALL PRIVILEGES, which means all 

     the privileges that the <grantor> has on the schema object. The <grantor> is normally 

     the CURRENT_USER of the session that issues the statement.



     The user or role that is granted privileges is referred to as <grantee> for 

     the granted privileges."



     ...



     "The INSERT privilege designates the columns into which explicit values can be inserted. 

     To be able to insert a row into the table, the user must therefore have the INSERT privilege

     on the table, or at least all the columns that do not have a default value."




so access rights are part of hsqldb and if we apply them we dont have to limit functionality.





1) further to make it easy to access maybe we should add an "access controls" functionality group to the base gui: 

(tables, queries, forms, reports) ---> (tables, queries, forms, reports, accesscontrols)



or add "access controls" as an item to the secondaryclick menu for tables, queries, forms, reports icons in the Base gui.





example of desired use of update/insert/delete is:

   -- a 'reset field' button which activates an update query changing values in some particular field which have some particular value and changing that value to some other particular value



   -- an 'add record' button that takes inforamtion entered in display fields and adds a record for them in the database



   -- a 'delete record' button that removes a record from the database






2) test of update query:


version:

LibreOffice 3.3.0 

OOO330m9 (Build:1)

libreoffice-build 3.2.99.2


test file done on mswindows-os


make a table: 

Table1.key, integer, autonumber, primary key

Table1.fld-flag, boolean

Table1.fld-desc, string

Table1.fld-int, integer



populate the table:

(auto number didnt seem to work in key when enter in table view so entered number manually)

key,    fld-flag,     fld-desc,     fld-int

1,      No,           one,            1 

2,      YES,          two,            2 

3,      YES,          three,          3

4,      No,           four,           4





when run as query fails: 

     UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True;



(error was: "LibraOffice Base: The data content could not be loaded.  Statement does not generate a result set.")





when run same sql code as tools-sql-execute_command it succeeds ("Command successfully executed."):

     UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True;





which is comfirmed by running query as:

     SELECT "fld-flag", "fld-desc", "fld-int" FROM "Table1" WHERE "fld-flag" = True



which correctly returns empty since none were true:



key,    fld-flag,     fld-desc,     fld-int

1,      No,           one,            1 

2,      NO,           two,            2 

3,      NO,           three,          3

4,      No,           four,           4



if go back in to table view to try to edit boolean flags to true get an error:



'File input/output error: C:\Program Files\Office\New Database1.odb.log in statement 

[UPDATE "Table1" SET "fld-flag" = ? WHERE "ID-key" = ?] '



but if then exit base and then restart base and reopen the file then there is no problem editing the values in the table view. 

this data edit error was not reproduceable.