Changeset: 5b3ef702e941 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5b3ef702e941
Modified Files:
        clients/mapiclient/dump.c
        clients/mapiclient/mclient.c
        gdk/gdk_logger.c
        sql/ChangeLog
        sql/backends/monet5/Tests/pyloader01.stable.out
        sql/backends/monet5/UDF/pyapi3/Tests/pyloader3_01.stable.out
        sql/backends/monet5/sql_cat.c
        sql/backends/monet5/sql_upgrades.c
        sql/backends/monet5/sql_user.c
        sql/common/sql_types.c
        sql/include/sql_catalog.h
        sql/scripts/99_system.sql
        sql/server/rel_psm.c
        sql/server/rel_select.c
        sql/server/sql_env.c
        sql/server/sql_mvc.c
        sql/server/sql_mvc.h
        sql/storage/bat/bat_logger.c
        sql/storage/sql_storage.h
        sql/storage/store.c
        
sql/test/BugDay_2005-10-06_2.9.3/Tests/CrashMe_SQL_server_crash-2.SF-921673.stable.out
        sql/test/BugTracker-2009/Tests/join_topn.SF-2654133.stable.out
        
sql/test/BugTracker-2017/Tests/crash_correlated_subqueries_in_select.Bug-6254.stable.out
        
sql/test/BugTracker-2018/Tests/count_from_commented_function_signatures.Bug-6542.stable.out
        sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.err
        sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.out
        sql/test/Dependencies/Tests/Dependencies.stable.out
        sql/test/Dependencies/Tests/Dependencies.stable.out.int128
        sql/test/Dependencies/Tests/dependency_loop.stable.out
        sql/test/Dependencies/Tests/dependency_loop.stable.out.int128
        sql/test/Tests/systemfunctions.stable.out
        sql/test/Tests/systemfunctions.stable.out.int128
        sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.stable.out
        sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
        sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
        sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
        sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
        sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
        sql/test/emptydb-upgrade/Tests/upgrade.stable.out
        sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
        sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
        sql/test/emptydb/Tests/check.SQL.py
        sql/test/emptydb/Tests/check.stable.out
        sql/test/emptydb/Tests/check.stable.out.32bit
        sql/test/emptydb/Tests/check.stable.out.int128
        sql/test/testdb-upgrade-chain-hge/Tests/dump.stable.out.int128
        sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
        sql/test/testdb-upgrade-chain/Tests/dump.stable.out
        sql/test/testdb-upgrade-chain/Tests/upgrade.stable.err
        sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
        sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
        sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
        sql/test/testdb-upgrade-hge/Tests/dump.stable.out.int128
        sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
        sql/test/testdb-upgrade/Tests/dump.stable.out
        sql/test/testdb-upgrade/Tests/upgrade.stable.out
        sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
        sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:

Implemented a "system" column in the sys.functions table to indicate system 
functions.
Also fixed database upgrade:
- The new tables sys.table_partitions, sys.range_partitions, and
sys.value_partitions were not added to the SQL catalog when upgrading
from a database that didn't have those tables.
- When creating a new database, lots of "objects" (tables, columns,
etc.) are created with their own object id.  This id is stored in the
SQL catalog, usually in the "id" column.  When starting a server on a
pre-existing database, these ids were not read from the catalog, but
the system tables and columns were assigned fresh ids which probably
overlapped and possibly conflicted with ids already in the catalog.
The catalog was not updated to reflect the new ids (which would be
hard).  We here fixed that the ids are taken from the catalog instead
of creating new ids.  The code probably causes a crash (intentionally)
when no proper upgrade code is present for any new tables and columns
that are created in the function store_load.


diffs (truncated from 60201 to 300 lines):

diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -204,10 +204,10 @@ has_hugeint(Mapi mid)
 {
        MapiHdl hdl;
        bool ret;
-       static int hashge = -1;
+       static int answer = -1;
 
-       if (hashge >= 0)
-               return (bool) hashge;
+       if (answer >= 0)
+               return (bool) answer;
 
        if ((hdl = mapi_query(mid,
                              "SELECT id "
@@ -223,8 +223,56 @@ has_hugeint(Mapi mid)
        if (mapi_error(mid))
                goto bailout;
        mapi_close_handle(hdl);
-       hashge = (int) ret;
-       return ret;
+       answer = (int) ret;
+       return answer;
+
+  bailout:
+       if (hdl) {
+               if (mapi_result_error(hdl))
+                       mapi_explain_result(hdl, stderr);
+               else
+                       mapi_explain_query(hdl, stderr);
+               mapi_close_handle(hdl);
+       } else
+               mapi_explain(mid, stderr);
+       return 0;
+}
+
+/* return TRUE if the sys.functions table has a column called system */
+static int
+has_funcsys(Mapi mid)
+{
+       MapiHdl hdl;
+       int ret;
+       static int answer = -1;
+
+       if (answer >= 0)
+               return answer;
+
+       if ((hdl = mapi_query(mid,
+                             "SELECT id "
+                             "FROM sys._columns "
+                             "WHERE name = 'system' "
+                             "AND table_id = ("
+                             " SELECT id"
+                             " FROM sys._tables"
+                             " WHERE name = 'functions'"
+                             " AND schema_id = ("
+                             "  SELECT id"
+                             "  FROM sys.schemas"
+                             "  WHERE name = 'sys'))")) == NULL ||
+           mapi_error(mid))
+               goto bailout;
+       ret = mapi_get_row_count(hdl) == 1;
+       while ((mapi_fetch_row(hdl)) != 0) {
+               if (mapi_error(mid))
+                       goto bailout;
+       }
+       if (mapi_error(mid))
+               goto bailout;
+       mapi_close_handle(hdl);
+       answer = ret;
+       return answer;
 
   bailout:
        if (hdl) {
@@ -1797,16 +1845,15 @@ dump_functions(Mapi mid, stream *toConso
                                              query_size - query_len,
                                              "AND s.name = '%s' ", sname);
                if (fname)
-                       query_len += snprintf(query + query_len,
-                                             query_size - query_len,
-                                             "AND f.name = '%s' ", fname);
-               if (!wantSystem)
-                       query_len += snprintf(query + query_len,
-                                             query_size - query_len,
-                                             "AND f.id NOT IN (SELECT 
function_id FROM sys.systemfunctions) ");
+                       query_len += snprintf(query + query_len, query_size - 
query_len, "AND f.name = '%s' ", fname);
+               if (!wantSystem) {
+                       if (has_funcsys(mid))
+                               query_len += snprintf(query + query_len, 
query_size - query_len, "AND NOT f.system ");
+                       else
+                               query_len += snprintf(query + query_len, 
query_size - query_len, "AND f.id NOT IN (SELECT function_id FROM 
sys.systemfunctions) ");
+               }
        }
-       query_len += snprintf(query + query_len, query_size - query_len,
-                             "ORDER BY f.func, f.id");
+       query_len += snprintf(query + query_len, query_size - query_len, "ORDER 
BY f.func, f.id");
        assert(query_len < (int) query_size);
        if (query_len >= (int) query_size)
                goto bailout;
@@ -1919,6 +1966,25 @@ dump_database(Mapi mid, stream *toConsol
                      "p.grantor = g.id "
                "ORDER BY s.name, t.name, c.name, a.name, g.name, p.grantable";
        const char *function_grants =
+               has_funcsys(mid) ?
+               "SELECT s.name, f.name, a.name, "
+                      "CASE p.privileges "
+                           "WHEN 1 THEN 'SELECT' "
+                           "WHEN 2 THEN 'UPDATE' "
+                           "WHEN 4 THEN 'INSERT' "
+                           "WHEN 8 THEN 'DELETE' "
+                           "WHEN 16 THEN 'EXECUTE' "
+                           "WHEN 32 THEN 'GRANT' END, "
+                      "g.name, p.grantable "
+               "FROM sys.schemas s, sys.functions f, "
+                    "sys.auths a, sys.privileges p, sys.auths g "
+               "WHERE s.id = f.schema_id AND "
+                     "f.id = p.obj_id AND "
+                     "p.auth_id = a.id AND "
+                     "p.grantor = g.id "
+                     "AND NOT f.system "
+               "ORDER BY s.name, f.name, a.name, g.name, p.grantable"
+               :
                "SELECT s.name, f.name, a.name, "
                       "CASE p.privileges "
                            "WHEN 1 THEN 'SELECT' "
@@ -1964,6 +2030,59 @@ dump_database(Mapi mid, stream *toConsol
                "ORDER BY s.name, seq.name";
        /* we must dump tables, views, procedures and triggers in order of 
creation since they can refer to each other */
        const char *tables_views_functions_triggers =
+               has_funcsys(mid) ?
+               ", vft (sname, name, id, query, remark, type) AS ("
+                       "SELECT s.name AS sname, " /* tables */
+                              "t.name AS name, "
+                              "t.id AS id, "
+                              "NULL AS query, "
+                              "NULL AS remark, "
+                              "t.type AS type "
+                       "FROM sys.schemas s, "
+                             "sys._tables t "
+                       "WHERE t.type IN (0, 3, 4, 5, 6, 12, 13, 14, 15) AND "
+                             "t.system = FALSE AND "
+                             "s.id = t.schema_id AND "
+                             "s.name <> 'tmp' "
+                       "UNION "
+                       "SELECT s.name AS sname, " /* views */
+                              "t.name AS name, "
+                              "t.id AS id, "
+                              "t.query AS query, "
+                              "rem.remark AS remark, "
+                              "NULL AS type "
+                       "FROM sys.schemas s, "
+                            "sys._tables t LEFT OUTER JOIN comments rem ON 
t.id = rem.id "
+                       "WHERE t.type = 1 AND "
+                             "t.system = FALSE AND "
+                             "s.id = t.schema_id AND "
+                             "s.name <> 'tmp' "
+                       "UNION "
+                       "SELECT s.name AS sname, " /* functions */
+                              "f.name AS name, "
+                              "f.id AS id, "
+                              "NULL AS query, "
+                              "NULL AS remark, " /* emitted separately */
+                              "NULL AS type "
+                       "FROM sys.schemas s, "
+                            "sys.functions f "
+                       "WHERE s.id = f.schema_id "
+                       "AND NOT f.system "
+                       "UNION "
+                       "SELECT s.name AS sname, " /* triggers */
+                              "tr.name AS name, "
+                              "tr.id AS id, "
+                              "tr.\"statement\" AS query, "
+                              "NULL AS remark, " /* not available yet */
+                              "NULL AS type "
+                       "FROM sys.triggers tr, "
+                            "sys.schemas s, "
+                            "sys._tables t "
+                       "WHERE s.id = t.schema_id AND "
+                             "t.id = tr.table_id AND t.system = FALSE"
+               ") "
+               "SELECT id, sname, name, query, remark, type FROM vft ORDER BY 
id"
+               :
                ", vft (sname, name, id, query, remark, type) AS ("
                        "SELECT s.name AS sname, " /* tables */
                               "t.name AS name, "
diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c
--- a/clients/mapiclient/mclient.c
+++ b/clients/mapiclient/mclient.c
@@ -2634,19 +2634,18 @@ doFile(Mapi mid, stream *fp, bool useins
                                                 */
                                                q += snprintf(q, endq - q, 
"%s", comments_clause);
                                                q += snprintf(q, endq - q, 
"%s", with_clause);
-                                               q += snprintf(q, endq - q, 
"SELECT type, fullname, remark FROM describe_all_objects\n");
-                                               q += snprintf(q, endq - q, 
"WHERE (ntype & %u) > 0\n", x);
+                                               q += snprintf(q, endq - q, " 
SELECT type, fullname, remark FROM describe_all_objects");
+                                               q += snprintf(q, endq - q, " 
WHERE (ntype & %u) > 0", x);
                                                if (!wantsSystem) {
-                                                       q += snprintf(q, endq - 
q, "AND NOT system\n");
+                                                       q += snprintf(q, endq - 
q, " AND NOT system");
                                                }
                                                if (!hasSchema) {
-                                                       q += snprintf(q, endq - 
q, "AND (sname IS NULL OR sname = current_schema)\n");
+                                                       q += snprintf(q, endq - 
q, " AND (sname IS NULL OR sname = current_schema)");
                                                }
                                                if (*line) {
-                                                       q += snprintf(q, endq - 
q, "AND (%s LIKE '%s')\n", name_column, line);
+                                                       q += snprintf(q, endq - 
q, " AND (%s LIKE '%s')", name_column, line);
                                                }
-                                               q += snprintf(q, endq - q, 
"ORDER BY fullname, type, remark\n");
-                                               q += snprintf(q, endq - q, 
";\n");
+                                               q += snprintf(q, endq - q, " 
ORDER BY fullname, type, remark");
 
                                                hdl = mapi_query(mid, query);
                                                free(query);
diff --git a/gdk/gdk_logger.c b/gdk/gdk_logger.c
--- a/gdk/gdk_logger.c
+++ b/gdk/gdk_logger.c
@@ -1678,8 +1678,10 @@ logger_load(int debug, const char *fn, c
                        bat bid = *(log_bid *) Tloc(b, p);
                        oid pos = p;
 
-                       if (BUNfnd(lg->dcatalog, &pos) == BUN_NONE)
-                               BBPretain(bid);
+                       if (BUNfnd(lg->dcatalog, &pos) == BUN_NONE &&
+                           BBPretain(bid) == 0 &&
+                           BUNappend(lg->dcatalog, &pos, false) != GDK_SUCCEED)
+                               goto error;
                }
        }
        lg->freed = logbat_new(TYPE_int, 1, TRANSIENT);
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,6 +1,12 @@
 # ChangeLog file for sql
 # This file is updated with Maddlog
 
+* Fri Aug 10 2018 Sjoerd Mullender <sjo...@acm.org>
+- The sys.functions table now has an extra column called "system" of
+  type BOOLEAN whose value is TRUE for system functions (i.e. functions
+  that should not be dumped).  The table sys.systemfunctions has been
+  changed to a view and is now officially deprecated.
+
 * Fri Aug  3 2018 Pedro Ferreira <pedro.ferre...@monetdbsolutions.com>
 - Extended merge tables with partitioning using a predicate:
   > CREATE MERGE TABLE [ IF NOT EXISTS ] table_name (... columns ...)
diff --git a/sql/backends/monet5/Tests/pyloader01.stable.out 
b/sql/backends/monet5/Tests/pyloader01.stable.out
--- a/sql/backends/monet5/Tests/pyloader01.stable.out
+++ b/sql/backends/monet5/Tests/pyloader01.stable.out
@@ -66,10 +66,10 @@ Ready.
 #DROP LOADER myfunc2;
 #DROP LOADER myfunc3;
 #SELECT * FROM functions WHERE name='myfunc';
-% sys.functions,       sys.functions,  sys.functions,  sys.functions,  
sys.functions,  sys.functions,  sys.functions,  sys.functions,  sys.functions,  
sys.functions # table_name
-% id,  name,   func,   mod,    language,       type,   side_effect,    varres, 
vararg, schema_id # name
-% int, varchar,        varchar,        varchar,        int,    int,    
boolean,        boolean,        boolean,        int # type
-% 1,   0,      0,      0,      1,      1,      5,      5,      5,      1 # 
length
+% sys.functions,       sys.functions,  sys.functions,  sys.functions,  
sys.functions,  sys.functions,  sys.functions,  sys.functions,  sys.functions,  
sys.functions,  sys.functions # table_name
+% id,  name,   func,   mod,    language,       type,   side_effect,    varres, 
vararg, schema_id,      system # name
+% int, varchar,        varchar,        varchar,        int,    int,    
boolean,        boolean,        boolean,        int,    boolean # type
+% 1,   0,      0,      0,      1,      1,      5,      5,      5,      1,      
5 # length
 #ROLLBACK;
 
 # 13:26:39 >  
diff --git a/sql/backends/monet5/UDF/pyapi3/Tests/pyloader3_01.stable.out 
b/sql/backends/monet5/UDF/pyapi3/Tests/pyloader3_01.stable.out
--- a/sql/backends/monet5/UDF/pyapi3/Tests/pyloader3_01.stable.out
+++ b/sql/backends/monet5/UDF/pyapi3/Tests/pyloader3_01.stable.out
@@ -66,10 +66,10 @@ Ready.
 #DROP LOADER myfunc2;
 #DROP LOADER myfunc3;
 #SELECT * FROM functions WHERE name='myfunc';
-% sys.functions,       sys.functions,  sys.functions,  sys.functions,  
sys.functions,  sys.functions,  sys.functions,  sys.functions,  sys.functions,  
sys.functions # table_name
-% id,  name,   func,   mod,    language,       type,   side_effect,    varres, 
vararg, schema_id # name
-% int, varchar,        varchar,        varchar,        int,    int,    
boolean,        boolean,        boolean,        int # type
-% 1,   0,      0,      0,      1,      1,      5,      5,      5,      1 # 
length
+% sys.functions,       sys.functions,  sys.functions,  sys.functions,  
sys.functions,  sys.functions,  sys.functions,  sys.functions,  sys.functions,  
sys.functions,  sys.functions # table_name
+% id,  name,   func,   mod,    language,       type,   side_effect,    varres, 
vararg, schema_id,      system # name
+% int, varchar,        varchar,        varchar,        int,    int,    
boolean,        boolean,        boolean,        int,    boolean # type
+% 1,   0,      0,      0,      1,      1,      5,      5,      5,      1,      
5 # length
 #ROLLBACK;
 
 # 13:26:39 >  
diff --git a/sql/backends/monet5/sql_cat.c b/sql/backends/monet5/sql_cat.c
--- a/sql/backends/monet5/sql_cat.c
+++ b/sql/backends/monet5/sql_cat.c
@@ -756,7 +756,7 @@ create_func(mvc *sql, char *sname, char 
                throw(SQL,"sql.create_func", SQLSTATE(3F000) "CREATE %s%s: no 
such schema '%s'", KF, F, sname);
        if (!s)
                s = cur_schema(sql);
-       nf = mvc_create_func(sql, NULL, s, f->base.name, f->ops, f->res, 
f->type, f->lang, f->mod, f->imp, f->query, f->varres, f->vararg);
+       nf = mvc_create_func(sql, NULL, s, f->base.name, f->ops, f->res, 
f->type, f->lang, f->mod, f->imp, f->query, f->varres, f->vararg, f->system);
        if (nf && nf->query && nf->lang <= FUNC_LANG_SQL) {
                char *buf;
                sql_rel *r = NULL;
diff --git a/sql/backends/monet5/sql_upgrades.c 
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -79,14 +79,15 @@ sql_fix_system_tables(Client c, mvc *sql
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list

Reply via email to