Database


1   /**
2    * com.mckoi.database.Database  02 Mar 1998
3    *
4    * Mckoi SQL Database ( http://www.mckoi.com/database )
5    * Copyright (C) 2000, 2001, 2002  Diehl and Associates, Inc.
6    *
7    * This program is free software; you can redistribute it and/or
8    * modify it under the terms of the GNU General Public License
9    * Version 2 as published by the Free Software Foundation.
10   *
11   * This program is distributed in the hope that it will be useful,
12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14   * GNU General Public License Version 2 for more details.
15   *
16   * You should have received a copy of the GNU General Public License
17   * Version 2 along with this program; if not, write to the Free Software
18   * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
19   *
20   * Change Log:
21   * 
22   * 
23   */
24  
25  package com.mckoi.database;
26  
27  import java.sql.*;
28  import java.io.File  ;
29  import java.io.PrintStream  ;
30  import java.io.IOException  ;
31  import java.util.ArrayList  ;
32  import java.util.Map  ;
33  import com.mckoi.debug.*;
34  import com.mckoi.util.Log;
35  import com.mckoi.util.Stats;
36  import com.mckoi.util.Cache;
37  import com.mckoi.database.global.*;
38  import com.mckoi.database.control.DefaultDBConfig;
39  import com.mckoi.database.jdbc.MSQLException;
40  import com.mckoi.store.Store;
41  import com.mckoi.store.MutableArea;
42  
43  /**
44   * The representation of a single database in the system.  A database
45   * is a set of schema, a set of tables and table definitions of tables in
46   * the schema, and a description of the schema.
47   * <p>
48   * This class encapsulates the top level behaviour of a database.  That is
49   * of creating itself, initializing itself, shutting itself down, deleting
50   * itself, creating/dropping a table, updating a table.  It is not the
51   * responsibility of this class to handle table behaviour above this.  Top
52   * level table behaviour is handled by DataTable through the DatabaseConnection
53   * interface.
54   * <p>
55   * The Database object is also responsible for various database management
56   * functions such a creating, editing and removing users, triggers, functions
57   * and services.
58   *
59   * @author Tobias Downer
60   */
61  
62  public final class Database implements DatabaseConstants {
63  
64    // ---------- Statics ----------
65  
66    /**
67     * The name of the system schema that contains tables refering to system
68     * information.
69     */
70    public static final String   SYSTEM_SCHEMA =
71                                          TableDataConglomerate.SYSTEM_SCHEMA;
72  
73    /**
74     * The name of the default schema.
75     */
76    public static final String   DEFAULT_SCHEMA = "APP";
77  
78    /**
79     * The name of the schema that contains JDBC helper tables.
80     */
81    public static final String   JDBC_SCHEMA = "SYS_JDBC";
82    
83    /**
84     * The password privs and grants table.
85     */
86    public static final TableName SYS_PASSWORD =
87                                 new TableName(SYSTEM_SCHEMA, "sUSRPassword");
88  
89    public static final TableName SYS_USERCONNECT =
90                          new TableName(SYSTEM_SCHEMA, "sUSRUserConnectPriv");
91  
92    public static final TableName SYS_USERPRIV =
93                                 new TableName(SYSTEM_SCHEMA, "sUSRUserPriv");
94  
95    public static final TableName SYS_GRANTS =
96                                    new TableName(SYSTEM_SCHEMA, "sUSRGrant");
97  
98    /**
99     * The services table.
100    */
101   public static final TableName SYS_SERVICE =
102                           new TableName(SYSTEM_SCHEMA, "sUSRService");
103 
104   /**
105    * The function factory table.
106    */
107   public static final TableName SYS_FUNCTIONFACTORY =
108                           new TableName(SYSTEM_SCHEMA, "sUSRFunctionFactory");
109 
110   /**
111    * The function table.
112    */
113   public static final TableName SYS_FUNCTION =
114                           new TableName(SYSTEM_SCHEMA, "sUSRFunction");
115 
116   /**
117    * The view table.
118    */
119   public static final TableName SYS_VIEW =
120                           new TableName(SYSTEM_SCHEMA, "sUSRView");
121   
122   /**
123    * The label table.
124    */
125   public static final TableName SYS_LABEL =
126                           new TableName(SYSTEM_SCHEMA, "sUSRLabel");
127 
128   /**
129    * The system internally generated 'sUSRTableColumns' table.
130    */
131   public static final TableName SYS_TABLE_COLUMNS =
132                            new TableName(SYSTEM_SCHEMA, "sUSRTableColumns");
133 
134   /**
135    * The system internally generated 'sUSRTableInfo' table.
136    */
137   public static final TableName SYS_TABLE_INFO =
138                               new TableName(SYSTEM_SCHEMA, "sUSRTableInfo");
139 
140   /**
141    * The system internally generated 'sUSRDataTrigger' table.
142    */
143   public static final TableName SYS_DATA_TRIGGER =
144                             new TableName(SYSTEM_SCHEMA, "sUSRDataTrigger");
145 
146   /**
147    * The system internally generated 'sUSRDatabaseStatistics' table.
148    */
149   public static final TableName SYS_DB_STATISTICS =
150                      new TableName(SYSTEM_SCHEMA, "sUSRDatabaseStatistics");
151 
152   /**
153    * The OLD table used inside a triggered procedure to represent a triggered
154    * row before the operation occurs.
155    */
156   public static final TableName OLD_TRIGGER_TABLE =
157                                         new TableName(SYSTEM_SCHEMA, "OLD");
158   
159   /**
160    * The NEW table used inside a triggered procedure to represent a triggered
161    * row after the operation occurs.
162    */
163   public static final TableName NEW_TRIGGER_TABLE =
164                                         new TableName(SYSTEM_SCHEMA, "NEW");
165   
166 
167   /**
168    * The name of the lock group.  If a user belongs to this group the user
169    * account is locked and they are not allowed to log into the database.
170    */
171   public static final String   LOCK_GROUP = "#locked";
172 
173   /**
174    * THe name of the secure access group.  If a user belongs to this group they
175    * are permitted to perform a number of priviledged operations such as
176    * shutting down the database, and adding and removing users.
177    */
178   public static final String   SECURE_GROUP = "secure access";
179   
180   /**
181    * The name of the user manager group.  Users that belong in this group can
182    * create, alter and drop users from the system.
183    */
184   public static final String   USER_MANAGER_GROUP = "user manager";
185   
186   /**
187    * The name of the schema manager group.  Users that belong in this group can
188    * create and drop schema from the system.
189    */
190   public static final String   SCHEMA_MANAGER_GROUP = "schema manager";
191 
192 
193   /**
194    * The username of the internal secure user.  The internal secure user is only
195    * used for internal highly privileged operations.  This user is given full
196    * privs to everything and is used to manage the system tables, for
197    * authentication, etc.
198    */
199   public static final String   INTERNAL_SECURE_USERNAME = "@SYSTEM";
200 
201 
202   // ---------- Members ----------
203 
204   /**
205    * The DatabaseSystem that this database is part of.
206    */
207   private DatabaseSystem system;
208 
209   /**
210    * The name of this database.
211    */
212   private String   name;
213 
214   /**
215    * The TableDataConglomerate that contains the conglomerate of tables for
216    * this database.
217    */
218   private TableDataConglomerate conglomerate;
219 
220   /**
221    * A flag which, when set to true, will cause the engine to delete the
222    * database from the file system when it is shut down.
223    */
224   private boolean delete_on_shutdown;
225 
226   /**
227    * An internal secure User that is given full grant access to the entire
228    * database.  This user is used to execute system level queries such as
229    * creating and updating system tables.
230    */
231   private User internal_system_user;
232 
233   /**
234    * The database wide TriggerManager object that dispatches trigger events
235    * to the DatabaseConnection objects that are listening for the events.
236    */
237   private TriggerManager trigger_manager;
238   
239 
240   
241   /**
242    * The various log files.
243    */
244   /**
245    * This log file records the DQL commands executed on the server.
246    */
247   private Log commands_log;
248 
249   /**
250    * This is set to true when the 'init()' method is first called.
251    */
252   private boolean initialised = false;
253 
254   /**
255    * A table that has a single row but no columns.
256    */
257   private final Table SINGLE_ROW_TABLE;
258   
259   /**
260    * The Constructor.  This takes a directory path in which the database is
261    * stored.
262    */
263   public Database(DatabaseSystem system, String   name) {
264     this.system = system;
265     this.delete_on_shutdown = false;
266     this.name = name;
267     conglomerate = new TableDataConglomerate(system, system.storeSystem());
268     internal_system_user =
269       new User(INTERNAL_SECURE_USERNAME, this, "", System.currentTimeMillis());
270 
271     // Create the single row table
272     TemporaryTable t;
273     t = new TemporaryTable(this,
274                            "SINGLE_ROW_TABLE", new DataTableColumnDef[0]);
275     t.newRow();
276     SINGLE_ROW_TABLE = t;
277 
278     trigger_manager = new TriggerManager(system);
279 
280   }
281 
282   /**
283    * Returns the name of this database.
284    */
285   public String   getName() {
286     return name;
287   }
288 
289   /**
290    * Returns true if this database is in read only mode.
291    */
292   public boolean isReadOnly() {
293     return getSystem().readOnlyAccess();
294   }
295 
296   /**
297    * Returns the internal system user for this database.
298    */
299   private User internalSystemUser() {
300     return internal_system_user;
301   }
302   
303   // ---------- Log accesses ----------
304 
305   /**
306    * Returns the log file where commands are recorded.
307    */
308   public Log getCommandsLog() {
309     return commands_log;
310   }
311 
312   /**
313    * Returns the conglomerate for this database.
314    */
315   TableDataConglomerate getConglomerate() {
316     return conglomerate;
317   }
318 
319   /**
320    * Returns a new DatabaseConnection instance that is used against this
321    * database.
322    * <p>
323    * When a new connection is made on this database, this method is called
324    * to create a new DatabaseConnection instance for the connection.  This
325    * connection handles all transactional queries and modifications to the
326    * database.
327    */
328   public DatabaseConnection createNewConnection(
329                             User user, DatabaseConnection.CallBack call_back) {
330     if (user == null) {
331       user = internalSystemUser();
332     }
333     
334     DatabaseConnection connection =
335                                 new DatabaseConnection(this, user, call_back);
336     // Initialize the connection
337     connection.init();
338 
339     return connection;
340   }
341 
342   // ---------- Database user management functions ----------
343 
344   /**
345    * Tries to authenticate a username/password against this database.  If we
346    * fail to authenticate then a 'null' object is returned, otherwise a valid
347    * User object is returned.  If a valid object is returned, the user
348    * will be logged into the engine via the UserManager object (in
349    * DatabaseSystem).  The developer must ensure that 'close' is called before
350    * the object is disposed (logs out of the system).
351    * <p>
352    * This method also returns null if a user exists but was denied access from
353    * the given host string.  The given 'host_name' object is formatted in the
354    * database host connection encoding.  This method checks all the values
355    * from the sUSRUserConnectPriv table for this user for the given protocol.
356    * It first checks if the user is specifically DENIED access from the given
357    * host.  It then checks if the user is ALLOWED access from the given host.
358    * If a host is neither allowed or denied then it is denied.
359    */
360   public User authenticateUser(String   username, String   password,
361                                String   connection_string) {
362 
363     // Create a temporary connection for authentication only...
364     DatabaseConnection connection = createNewConnection(null, null);
365     DatabaseQueryContext context = new DatabaseQueryContext(connection);
366     connection.setCurrentSchema(SYSTEM_SCHEMA);
367     LockingMechanism locker = connection.getLockingMechanism();
368     locker.setMode(LockingMechanism.EXCLUSIVE_MODE);
369     try {
370 
371       try {
372         Connection jdbc = connection.getJDBCConnection();
373 
374         // Is the username/password in the database?
375         PreparedStatement stmt = jdbc.prepareStatement(
376             " SELECT \"UserName\" FROM \"sUSRPassword\" " +
377             "  WHERE \"sUSRPassword.UserName\" = ? " +
378             "    AND \"sUSRPassword.Password\" = ? ");
379         stmt.setString(1, username);
380         stmt.setString(2, password);
381         ResultSet rs = stmt.executeQuery();
382         if (!rs.next()) {
383           return null;
384         }
385         rs.close();
386         stmt.close();
387 
388         // Now check if this user is permitted to connect from the given
389         // host.
390         if (userAllowedAccessFromHost(context,
391                                       username, connection_string)) {
392           // Successfully authenticated...
393           User user = new User(username, this,
394                              connection_string, System.currentTimeMillis());
395           // Log the authenticated user in to the engine.
396           system.getUserManager().userLoggedIn(user);
397           return user;
398         }
399 
400         return null;
401 
402       }
403       catch (SQLException e) {
404         if (e instanceof MSQLException) {
405           MSQLException msqle = (MSQLException) e;
406           Debug().write(Lvl.ERROR, this,
407                         msqle.getServerErrorStackTrace());
408         }
409         Debug().writeException(Lvl.ERROR, e);
410         throw new RuntimeException  ("SQL Error: " + e.getMessage());
411       }
412 
413     }
414     finally {
415       try {
416         // Make sure we commit the connection.
417         connection.commit();
418       }
419       catch (TransactionException e) {
420         // Just issue a warning...
421         Debug().writeException(Lvl.WARNING, e);
422       }
423       finally {
424         // Guarentee that we unluck from EXCLUSIVE
425         locker.finishMode(LockingMechanism.EXCLUSIVE_MODE);
426       }
427       // And make sure we close (dispose) of the temporary connection.
428       connection.close();
429     }
430 
431   }
432 
433   /**
434    * Performs check to determine if user is allowed access from the given
435    * host.  See the comments of 'authenticateUser' for a description of
436    * how this is determined.
437    */
438   private boolean userAllowedAccessFromHost(DatabaseQueryContext context,
439                                   String   username, String   connection_string) {
440 
441     // The system user is not allowed to login
442     if (username.equals(INTERNAL_SECURE_USERNAME)) {
443       return false;
444     }
445                                     
446     // We always allow access from 'Internal/*' (connections from the
447     // 'getConnection' method of a com.mckoi.database.control.DBSystem object)
448     // ISSUE: Should we add this as a rule?
449     if (connection_string.startsWith("Internal/")) {
450       return true;
451     }
452 
453     // What's the protocol?
454     int protocol_host_deliminator = connection_string.indexOf("/");
455     String   protocol =
456                     connection_string.substring(0, protocol_host_deliminator);
457     String   host = connection_string.substring(protocol_host_deliminator + 1);
458 
459     if (Debug().isInterestedIn(Lvl.INFORMATION)) {
460       Debug().write(Lvl.INFORMATION, this,
461                   "Checking host: protocol = " + protocol +
462                   ", host = " + host);
463     }
464 
465     // The table to check
466     DataTable connect_priv = context.getTable(SYS_USERCONNECT);
467     Variable un_col = connect_priv.getResolvedVariable(0);
468     Variable proto_col = connect_priv.getResolvedVariable(1);
469     Variable host_col = connect_priv.getResolvedVariable(2);
470     Variable access_col = connect_priv.getResolvedVariable(3);
471     // Query: where UserName = %username%
472     Table t = connect_priv.simpleSelect(context, un_col, Operator.get("="),
473                                   new Expression(TObject.stringVal(username)));
474     // Query: where %protocol% like Protocol
475     Expression exp = Expression.simple(TObject.stringVal(protocol),
476                                        Operator.get("like"), proto_col);
477     t = t.exhaustiveSelect(context, exp);
478     // Query: where %host% like Host
479     exp = Expression.simple(TObject.stringVal(host),
480                             Operator.get("like"), host_col);
481     t = t.exhaustiveSelect(context, exp);
482 
483     // Those that are DENY
484     Table t2 = t.simpleSelect(context, access_col, Operator.get("="),
485                               new Expression(TObject.stringVal("DENY")));
486     if (t2.getRowCount() > 0) {
487       return false;
488     }
489     // Those that are ALLOW
490     Table t3 = t.simpleSelect(context, access_col, Operator.get("="),
491                               new Expression(TObject.stringVal("ALLOW")));
492     if (t3.getRowCount() > 0) {
493       return true;
494     }
495     // No DENY or ALLOW entries for this host so deny access.
496     return false;
497 
498   }
499 
500   /**
501    * Returns true if a user exists in this database, otherwise returns
502    * false.
503    * <p>
504    * NOTE: Assumes exclusive lock on DatabaseConnection.
505    */
506   public boolean userExists(DatabaseQueryContext context, String   username)
507                                                     throws DatabaseException {
508     DataTable table = context.getTable(SYS_PASSWORD);
509     Variable c1 = table.getResolvedVariable(0);
510     // All sUSRPassword where UserName = %username%
511     Table t = table.simpleSelect(context, c1, Operator.get("="),
512                                  new Expression(TObject.stringVal(username)));
513     return t.getRowCount() > 0;
514   }
515 
516   /**
517    * Creates and adds a new user to this database.  The User object for
518    * the user is returned.
519    * <p>
520    * If the user is already defined by the database then an error is generated.
521    * <p>
522    * NOTE: Assumes exclusive lock on DatabaseConnection.
523    */
524   public void createUser(DatabaseQueryContext context,
525                          String   username, String   password)
526                                                     throws DatabaseException {
527 
528     if (username == null || password == null) {
529       throw new DatabaseException("Username or password can not be NULL.");
530     }
531 
532     // The username must be more than 1 character
533     if (username.length() <= 1) {
534       throw new DatabaseException("Username must be at least 2 characters.");
535     }
536     
537     // The password must be more than 1 character
538     if (password.length() <= 1) {
539       throw new DatabaseException("Password must be at least 2 characters.");
540     }
541 
542     // Check the user doesn't already exist
543     if (userExists(context, username)) {
544       throw new DatabaseException("User '" + username + "' already exists.");
545     }
546 
547     // Some usernames are reserved words
548     if (username.equalsIgnoreCase("public")) {
549       throw new DatabaseException("User '" + username +
550                                   "' not allowed - reserved.");
551     }
552 
553     // Usernames starting with @, &, # and $ are reserved for system
554     // identifiers
555     char c = username.charAt(0);
556     if (c == '@' || c == '&' || c == '#' || c == '$') {
557       throw new DatabaseException("User name can not start with '" + c +
558                                   "' character.");
559     }
560 
561     // Add this user to the password table.
562     DataTable table = context.getTable(SYS_PASSWORD);
563     RowData rdat = new RowData(table);
564     rdat.setColumnDataFromObject(0, username);
565     rdat.setColumnDataFromObject(1, password);
566     table.add(rdat);
567 
568   }
569 
570   /**
571    * Deletes all the groups the user belongs to.  This is intended for a user
572    * alter command for setting the groups a user belongs to.
573    * <p>
574    * NOTE: Assumes exclusive lock on DatabaseConnection.
575    */
576   public void deleteAllUserGroups(DatabaseQueryContext context, String   username)
577                                                      throws DatabaseException {
578     Operator EQUALS_OP = Operator.get("=");
579     Expression USER_EXPR = new Expression(TObject.stringVal(username));
580 
581     DataTable table = context.getTable(SYS_USERPRIV);
582     Variable c1 = table.getResolvedVariable(0);
583     // All sUSRUserPriv where UserName = %username%
584     Table t = table.simpleSelect(context, c1, EQUALS_OP, USER_EXPR);
585     // Delete all the groups
586     table.delete(t);
587 
588   }
589   
590   /**
591    * Deletes the user from the system.  This also deletes all information
592    * associated with a user such as the groups they belong to.  It does not
593    * delete the privs a user has set up.
594    * <p>
595    * NOTE: Assumes exclusive lock on DatabaseConnection.
596    */
597   public void deleteUser(DatabaseQueryContext context, String   username)
598                                                      throws DatabaseException {
599     // PENDING: This should check if there are any tables the user has setup
600     //  and not allow the delete if there are.
601 
602     Operator EQUALS_OP = Operator.get("=");
603     Expression USER_EXPR = new Expression(TObject.stringVal(username));
604     
605     // First delete all the groups from the user priv table
606     deleteAllUserGroups(context, username);
607     
608     // Now delete the username from the sUSRUserConnectPriv table
609     DataTable table = context.getTable(SYS_USERCONNECT);
610     Variable c1 = table.getResolvedVariable(0);
611     Table t = table.simpleSelect(context, c1, EQUALS_OP, USER_EXPR);
612     table.delete(t);
613     
614     // Finally delete the username from the sUSRPassword table
615     table = context.getTable(SYS_PASSWORD);
616     c1 = table.getResolvedVariable(0);
617     t = table.simpleSelect(context, c1, EQUALS_OP, USER_EXPR);
618     table.delete(t);
619     
620   }
621 
622   /**
623    * Alters the password of the user but otherwise does not change any
624    * information about the user.
625    * <p>
626    * NOTE: Assumes exclusive lock on DatabaseConnection.
627    */
628   public void alterUserPassword(DatabaseQueryContext context,
629                    String   username, String   password) throws DatabaseException {
630 
631     Operator EQUALS_OP = Operator.get("=");
632     Expression USER_EXPR = new Expression(TObject.stringVal(username));
633 
634     // Delete the current username from the sUSRPassword table
635     DataTable table = context.getTable(SYS_PASSWORD);
636     Variable c1 = table.getResolvedVariable(0);
637     Table t = table.simpleSelect(context, c1, EQUALS_OP, USER_EXPR);
638     if (t.getRowCount() == 1) {
639       table.delete(t);
640     
641       // Add the new username
642       table = context.getTable(SYS_PASSWORD);
643       RowData rdat = new RowData(table);
644       rdat.setColumnDataFromObject(0, username);
645       rdat.setColumnDataFromObject(1, password);
646       table.add(rdat);
647 
648     }
649     else {
650       throw new DatabaseException("Username '" + username + "' was not found.");
651     }
652 
653   }
654 
655   /**
656    * Returns the list of all user groups the user belongs to.
657    */
658   public String  [] groupsUserBelongsTo(DatabaseQueryContext context,
659                                    String   username) throws DatabaseException {
660 
661     DataTable table = context.getTable(SYS_USERPRIV);
662     Variable c1 = table.getResolvedVariable(0);
663     // All sUSRUserPriv where UserName = %username%
664     Table t = table.simpleSelect(context, c1, Operator.get("="),
665                                  new Expression(TObject.stringVal(username)));
666     int sz = t.getRowCount();
667     String  [] groups = new String  [sz];
668     RowEnumeration row_enum = t.rowEnumeration();
669     int i = 0;
670     while (row_enum.hasMoreRows()) {
671       groups[i] = t.getCellContents(1,
672                               row_enum.nextRowIndex()).getObject().toString();
673       ++i;
674     }
675 
676     return groups;
677   }
678 
679   /**
680    * Returns true if the given user belongs to the given group otherwise
681    * returns false.
682    * <p>
683    * NOTE: Assumes exclusive lock on DatabaseConnection.
684    */
685   public boolean userBelongsToGroup(DatabaseQueryContext context,
686                                     String   username, String   group)
687                                                     throws DatabaseException {
688 
689     DataTable table = context.getTable(SYS_USERPRIV);
690     Variable c1 = table.getResolvedVariable(0);
691     Variable c2 = table.getResolvedVariable(1);
692     // All sUSRUserPriv where UserName = %username%
693     Table t = table.simpleSelect(context, c1, Operator.get("="),
694                                  new Expression(TObject.stringVal(username)));
695     // All from this set where PrivGroupName = %group%
696     t = t.simpleSelect(context, c2, Operator.get("="),
697                        new Expression(TObject.stringVal(group)));
698     return t.getRowCount() > 0;
699   }
700 
701   /**
702    * Adds the user to the given group.  This makes an entry in the sUSRUserPriv
703    * for this user and the given group.  If the user already belongs to the
704    * group then no changes are made.
705    * <p>
706    * It is important that any security checks for ensuring the grantee is
707    * allowed to give the user these privs are preformed before this method is
708    * called.
709    * <p>
710    * NOTE: Assumes exclusive lock on DatabaseConnection.
711    */
712   public void addUserToGroup(DatabaseQueryContext context,
713                              String   username, String   group)
714                                                     throws DatabaseException {
715     if (group == null) {
716       throw new DatabaseException("Can add NULL group.");
717     }
718 
719     // Groups starting with @, &, # and $ are reserved for system
720     // identifiers
721     char c = group.charAt(0);
722     if (c == '@' || c == '&' || c == '#' || c == '$') {
723       throw new DatabaseException("The group name can not start with '" + c +
724                                   "' character.");
725     }
726     
727     // Check the user doesn't belong to the group
728     if (!userBelongsToGroup(context, username, group)) {
729       // The user priv table
730       DataTable table = context.getTable(SYS_USERPRIV);
731       // Add this user to the group.
732       RowData rdat = new RowData(table);
733       rdat.setColumnDataFromObject(0, username);
734       rdat.setColumnDataFromObject(1, group);
735       table.add(rdat);
736     }
737     // NOTE: we silently ignore the case when a user already belongs to the
738     //   group.
739   }
740 
741   /**
742    * Sets the lock status for the given user.  If a user account if locked, it
743    * is rejected from logging in to the database.
744    * <p>
745    * It is important that any security checks to determine if the process
746    * setting the user lock is allowed to do it is done before this method is
747    * called.
748    * <p>
749    * NOTE: Assumes exclusive lock on DatabaseConnection.
750    */
751   public void setUserLock(DatabaseQueryContext context, User user,
752                           boolean lock_status) throws DatabaseException {
753 
754     String   username = user.getUserName();
755                             
756     // Internally we implement this by adding the user to the #locked group.
757     DataTable table = context.getTable(SYS_USERPRIV);
758     Variable c1 = table.getResolvedVariable(0);
759     Variable c2 = table.getResolvedVariable(1);
760     // All sUSRUserPriv where UserName = %username%
761     Table t = table.simpleSelect(context, c1, Operator.get("="),
762                                  new Expression(TObject.stringVal(username)));
763     // All from this set where PrivGroupName = %group%
764     t = t.simpleSelect(context, c2, Operator.get("="),
765                        new Expression(TObject.stringVal(LOCK_GROUP)));
766     
767     boolean user_belongs_to_lock_group = t.getRowCount() > 0;
768     if (lock_status && !user_belongs_to_lock_group) {
769       // Lock the user by adding the user to the lock group
770       // Add this user to the locked group.
771       RowData rdat = new RowData(table);
772       rdat.setColumnDataFromObject(0, username);
773       rdat.setColumnDataFromObject(1, LOCK_GROUP);
774       table.add(rdat);
775     }
776     else if (!lock_status && user_belongs_to_lock_group) {
777       // Unlock the user by removing the user from the lock group
778       // Remove this user from the locked group.
779       table.delete(t);
780     }
781     
782   }
783   
784   /**
785    * Grants the given user access to connect to the database from the
786    * given host address.  The 'protocol' string is the connecting protocol
787    * which can be either 'TCP' or 'Local'.  The 'host' string is the actual
788    * host that is connecting.  For example, if the protocol was TCP then
789    * the client host may be '127.0.0.1' for localhost.
790    */
791   public void grantHostAccessToUser(DatabaseQueryContext context,
792                                     String   user, String   protocol, String   host)
793                                                     throws DatabaseException {
794 
795     // The user connect priv table
796     DataTable table = context.getTable(SYS_USERCONNECT);
797     // Add the protocol and host to the table
798     RowData rdat = new RowData(table);
799     rdat.setColumnDataFromObject(0, user);
800     rdat.setColumnDataFromObject(1, protocol);
801     rdat.setColumnDataFromObject(2, host);
802     rdat.setColumnDataFromObject(3, "ALLOW");
803     table.add(rdat);
804 
805   }
806 
807   /**
808    * Returns true if the user belongs to the secure access priv group.
809    */
810   private boolean userHasSecureAccess(DatabaseQueryContext context, User user)
811                                                     throws DatabaseException {
812     // The internal secure user has full privs on everything
813     if (user.getUserName().equals(INTERNAL_SECURE_USERNAME)) {
814       return true;
815     }
816     return userBelongsToGroup(context, user.getUserName(), SECURE_GROUP);
817   }
818 
819   /**
820    * Returns true if the grant manager permits a schema operation (eg, 
821    * CREATE, ALTER and DROP table operations) for the given user.
822    */
823   private boolean userHasSchemaGrant(DatabaseQueryContext context,
824          User user, String   schema, int grant) throws DatabaseException {
825     // The internal secure user has full privs on everything
826     if (user.getUserName().equals(INTERNAL_SECURE_USERNAME)) {
827       return true;
828     }
829            
830     // No users have schema access to the system schema.
831     if (schema.equals(SYSTEM_SCHEMA)) {
832       return false;
833     }
834            
835     // Ask the grant manager if there are any privs setup for this user on the
836     // given schema.
837     GrantManager manager = context.getGrantManager();
838     Privileges privs = manager.userGrants(
839                              GrantManager.SCHEMA, schema, user.getUserName());
840 
841     return privs.permits(grant);
842   }
843 
844   /**
845    * Returns true if the grant manager permits a table object operation (eg, 
846    * SELECT, INSERT, UPDATE, DELETE and COMPACT table operations) for the given
847    * user.
848    */
849   private boolean userHasTableObjectGrant(DatabaseQueryContext context,
850          User user, TableName table_name, Variable[] columns,
851          int grant) throws DatabaseException {
852 
853     // The internal secure user has full privs on everything
854     if (user.getUserName().equals(INTERNAL_SECURE_USERNAME)) {
855       return true;
856     }
857 
858     // PENDING: Support column level privileges.
859 
860     // Ask the grant manager if there are any privs setup for this user on the
861     // given schema.
862     GrantManager manager = context.getGrantManager();
863     Privileges privs = manager.userGrants(
864                GrantManager.TABLE, table_name.toString(), user.getUserName());
865 
866     return privs.permits(grant);
867   }
868 
869   /**
870    * Returns true if the user is permitted to create, alter and drop user
871    * information from the database, otherwise returns false.  Only members of
872    * the 'secure access' group, or the 'user manager' group can do this.
873    */
874   public boolean canUserCreateAndDropUsers(
875            DatabaseQueryContext context, User user) throws DatabaseException {
876     return (userHasSecureAccess(context, user) ||
877             userBelongsToGroup(context, user.getUserName(),
878                                USER_MANAGER_GROUP));
879   }
880 
881   /**
882    * Returns true if the user is permitted to create and drop schema's in the
883    * database, otherwise returns false.  Only members of the 'secure access'
884    * group, or the 'schema manager' group can do this.
885    */
886   public boolean canUserCreateAndDropSchema(
887            DatabaseQueryContext context, User user, String   schema)
888                                                     throws DatabaseException {
889 
890     // The internal secure user has full privs on everything
891     if (user.getUserName().equals(INTERNAL_SECURE_USERNAME)) {
892       return true;
893     }
894                                                       
895     // No user can create or drop the system schema.
896     if (schema.equals(SYSTEM_SCHEMA)) {
897       return false;
898     }
899     else {
900       return (userHasSecureAccess(context, user) ||
901               userBelongsToGroup(context, user.getUserName(),
902                                  SCHEMA_MANAGER_GROUP));
903     }
904   }
905   
906   /**
907    * Returns true if the user can shut down the database server.  A user can
908    * shut down the database if they are a member of the 'secure acces' group.
909    */
910   public boolean canUserShutDown(DatabaseQueryContext context, User user)
911                                                     throws DatabaseException {
912     return userHasSecureAccess(context, user);
913   }
914   
915   /**
916    * Returns true if the user is allowed to execute the given stored procedure.
917    */
918   public boolean canUserExecuteStoredProcedure(DatabaseQueryContext context,
919                   User user, String   procedure_name) throws DatabaseException {
920     // Currently you can only execute a procedure if you are a member of the
921     // secure access priv group.
922     return userHasSecureAccess(context, user);
923   }
924   
925   // ---- General schema level privs ----
926 
927   /**
928    * Returns true if the user can create a table or view with the given name,
929    * otherwise returns false.
930    */
931   public boolean canUserCreateTableObject(
932            DatabaseQueryContext context, User user, TableName table)
933                                                     throws DatabaseException {
934     if (userHasSchemaGrant(context, user,
935                            table.getSchema(), Privileges.CREATE)) {
936       return true;
937     }
938 
939     // If the user belongs to the secure access priv group, return true
940     return userHasSecureAccess(context, user);
941   }
942 
943   /**
944    * Returns true if the user can alter a table or view with the given name,
945    * otherwise returns false.
946    */
947   public boolean canUserAlterTableObject(
948            DatabaseQueryContext context, User user, TableName table)
949                                                     throws DatabaseException {
950     if (userHasSchemaGrant(context, user,
951                            table.getSchema(), Privileges.ALTER)) {
952       return true;
953     }
954 
955     // If the user belongs to the secure access priv group, return true
956     return userHasSecureAccess(context, user);
957   }
958   
959   /**
960    * Returns true if the user can drop a table or view with the given name,
961    * otherwise returns false.
962    */
963   public boolean canUserDropTableObject(
964            DatabaseQueryContext context, User user, TableName table)
965                                                     throws DatabaseException {
966     if (userHasSchemaGrant(context, user,
967                            table.getSchema(), Privileges.DROP)) {
968       return true;
969     }
970 
971     // If the user belongs to the secure access priv group, return true
972     return userHasSecureAccess(context, user);
973   }
974 
975   // ---- Check table object privs ----
976   
977   /**
978    * Returns true if the user can select from a table or view with the given
979    * name and given columns, otherwise returns false.
980    */
981   public boolean canUserSelectFromTableObject(
982            DatabaseQueryContext context, User user, TableName table,
983            Variable[] columns) throws DatabaseException {
984     if (userHasTableObjectGrant(context, user, table, columns,
985                                 Privileges.SELECT)) {
986       return true;
987     }
988     
989     // If the user belongs to the secure access priv group, return true
990     return userHasSecureAccess(context, user);
991   }
992 
993   /**
994    * Returns true if the user can insert into a table or view with the given
995    * name and given columns, otherwise returns false.
996    */
997   public boolean canUserInsertIntoTableObject(
998            DatabaseQueryContext context, User user, TableName table,
999            Variable[] columns) throws DatabaseException {
1000    if (userHasTableObjectGrant(context, user, table, columns,
1001                                Privileges.INSERT)) {
1002      return true;
1003    }
1004    
1005    // If the user belongs to the secure access priv group, return true
1006    return userHasSecureAccess(context, user);
1007  }
1008             
1009  /**
1010   * Returns true if the user can update a table or view with the given
1011   * name and given columns, otherwise returns false.
1012   */
1013  public boolean canUserUpdateTableObject(
1014           DatabaseQueryContext context, User user, TableName table,
1015           Variable[] columns) throws DatabaseException {
1016    if (userHasTableObjectGrant(context, user, table, columns,
1017                                Privileges.UPDATE)) {
1018      return true;
1019    }
1020    
1021    // If the user belongs to the secure access priv group, return true
1022    return userHasSecureAccess(context, user);
1023  }
1024
1025  /**
1026   * Returns true if the user can delete from a table or view with the given
1027   * name and given columns, otherwise returns false.
1028   */
1029  public boolean canUserDeleteFromTableObject(
1030           DatabaseQueryContext context, User user, TableName table)
1031                                                    throws DatabaseException {
1032    if (userHasTableObjectGrant(context, user, table, null,
1033                                Privileges.DELETE)) {
1034      return true;
1035    }
1036    
1037    // If the user belongs to the secure access priv group, return true
1038    return userHasSecureAccess(context, user);
1039  }
1040
1041  /**
1042   * Returns true if the user can compact a table with the given name,
1043   * otherwise returns false.
1044   */
1045  public boolean canUserCompactTableObject(
1046           DatabaseQueryContext context, User user, TableName table)
1047                                                    throws DatabaseException {
1048    if (userHasTableObjectGrant(context, user, table, null,
1049                                Privileges.COMPACT)) {
1050      return true;
1051    }
1052    
1053    // If the user belongs to the secure access priv group, return true
1054    return userHasSecureAccess(context, user);
1055  }
1056
1057  /**
1058   * Returns true if the user can create a procedure with the given name,
1059   * otherwise returns false.
1060   */
1061  public boolean canUserCreateProcedureObject(
1062           DatabaseQueryContext context, User user, TableName table)
1063                                                    throws DatabaseException {
1064    if (userHasSchemaGrant(context, user,
1065                           table.getSchema(), Privileges.CREATE)) {
1066      return true;
1067    }
1068
1069    // If the user belongs to the secure access priv group, return true
1070    return userHasSecureAccess(context, user);
1071  }
1072
1073  /**
1074   * Returns true if the user can drop a procedure with the given name,
1075   * otherwise returns false.
1076   */
1077  public boolean canUserDropProcedureObject(
1078           DatabaseQueryContext context, User user, TableName table)
1079                                                    throws DatabaseException {
1080    if (userHasSchemaGrant(context, user,
1081                           table.getSchema(), Privileges.DROP)) {
1082      return true;
1083    }
1084
1085    // If the user belongs to the secure access priv group, return true
1086    return userHasSecureAccess(context, user);
1087  }
1088
1089  /**
1090   * Returns true if the user can create a sequence with the given name,
1091   * otherwise returns false.
1092   */
1093  public boolean canUserCreateSequenceObject(
1094           DatabaseQueryContext context, User user, TableName table)
1095                                                    throws DatabaseException {
1096    if (userHasSchemaGrant(context, user,
1097                           table.getSchema(), Privileges.CREATE)) {
1098      return true;
1099    }
1100
1101    // If the user belongs to the secure access priv group, return true
1102    return userHasSecureAccess(context, user);
1103  }
1104
1105  /**
1106   * Returns true if the user can drop a sequence with the given name,
1107   * otherwise returns false.
1108   */
1109  public boolean canUserDropSequenceObject(
1110           DatabaseQueryContext context, User user, TableName table)
1111                                                    throws DatabaseException {
1112    if (userHasSchemaGrant(context, user,
1113                           table.getSchema(), Privileges.DROP)) {
1114      return true;
1115    }
1116
1117    // If the user belongs to the secure access priv group, return true
1118    return userHasSecureAccess(context, user);
1119  }
1120
1121
1122
1123
1124
1125  // ---------- Schema management ----------
1126
1127  /**
1128   * Creates the schema information tables introducted in version 0.90.  The
1129   * schema information tables are;
1130   */
1131  void createSchemaInfoTables(DatabaseConnection connection)
1132                                                  throws DatabaseException {
1133
1134    connection.createSchema(DEFAULT_SCHEMA, "DEFAULT");
1135    connection.createSchema(JDBC_SCHEMA, "SYSTEM");
1136
1137  }
1138
1139  /**
1140   * Creates all the system views.
1141   */
1142  private void createSystemViews(DatabaseConnection connection)
1143                                                   throws DatabaseException {
1144    // Obtain the JDBC interface.
1145    try {
1146      Connection jdbc = connection.getJDBCConnection();
1147
1148      // Is the username/password in the database?
1149      Statement stmt = jdbc.createStatement();
1150      
1151      // This view shows the grants that the user has (no join, only priv_bit).
1152      stmt.executeUpdate(
1153        "CREATE VIEW SYS_JDBC.ThisUserSimpleGrant AS " +
1154        "  SELECT \"priv_bit\", \"object\", \"param\", \"grantee\", " +
1155        "         \"grant_option\", \"granter\" " +
1156        "    FROM SYS_INFO.sUSRGrant " +
1157        "   WHERE ( grantee = user() OR grantee = '@PUBLIC' )");
1158      // This view shows the grants that the user is allowed to see
1159      stmt.executeUpdate(
1160        "CREATE VIEW SYS_JDBC.ThisUserGrant AS " +
1161        "  SELECT \"description\", \"object\", \"param\", \"grantee\", " +
1162        "         \"grant_option\", \"granter\" " +
1163        "    FROM SYS_INFO.sUSRGrant, SYS_INFO.sUSRPrivMap " +
1164        "   WHERE ( grantee = user() OR grantee = '@PUBLIC' )" +
1165        "     AND sUSRGrant.priv_bit = sUSRPrivMap.priv_bit");
1166      // A view that represents the list of schema this user is allowed to view
1167      // the contents of.
1168      stmt.executeUpdate(
1169        "CREATE VIEW SYS_JDBC.ThisUserSchemaInfo AS " +
1170        "  SELECT * FROM SYS_INFO.sUSRSchemaInfo " +
1171        "   WHERE \"name\" IN ( " +
1172        "     SELECT \"param\" " +
1173        "       FROM SYS_JDBC.ThisUserGrant " +
1174        "      WHERE \"object\" = 65 " +
1175        "        AND \"description\" = 'LIST' )");
1176      // A view that exposes the sUSRTableColumn table but only for the tables
1177      // this user has read access to.
1178      stmt.executeUpdate(
1179        "CREATE VIEW SYS_JDBC.ThisUserTableColumns AS " +
1180        "  SELECT * FROM SYS_INFO.sUSRTableColumns " +
1181        "   WHERE \"schema\" IN ( " +
1182        "     SELECT \"name\" FROM SYS_JDBC.ThisUserSchemaInfo )");
1183      // A view that exposes the sUSRTableInfo table but only for the tables
1184      // this user has read access to.
1185      stmt.executeUpdate(
1186        "CREATE VIEW SYS_JDBC.ThisUserTableInfo AS " +
1187        "  SELECT * FROM SYS_INFO.sUSRTableInfo " +
1188        "   WHERE \"schema\" IN ( " +
1189        "     SELECT \"name\" FROM SYS_JDBC.ThisUserSchemaInfo )");
1190
1191      // A JDBC helper view for the 'getTables' meta-data method
1192      stmt.executeUpdate(
1193"  CREATE VIEW SYS_JDBC.Tables AS " +
1194"  SELECT NULL AS \"TABLE_CAT\", \n" +
1195"         \"schema\" AS \"TABLE_SCHEM\", \n" +
1196"         \"name\" AS \"TABLE_NAME\", \n" +
1197"         \"type\" AS \"TABLE_TYPE\", \n" +
1198"         \"other\" AS \"REMARKS\", \n" +
1199"         NULL AS \"TYPE_CAT\", \n" +
1200"         NULL AS \"TYPE_SCHEM\", \n" +
1201"         NULL AS \"TYPE_NAME\", \n" +
1202"         NULL AS \"SELF_REFERENCING_COL_NAME\", \n" +
1203"         NULL AS \"REF_GENERATION\" \n" +
1204"    FROM SYS_JDBC.ThisUserTableInfo \n");
1205      // A JDBC helper view for the 'getSchemas' meta-data method
1206      stmt.executeUpdate(
1207"  CREATE VIEW SYS_JDBC.Schemas AS " +
1208"  SELECT \"name\" AS \"TABLE_SCHEM\", \n" +
1209"         NULL AS \"TABLE_CATALOG\" \n" +
1210"    FROM SYS_JDBC.ThisUserSchemaInfo\n");
1211      // A JDBC helper view for the 'getCatalogs' meta-data method
1212      stmt.executeUpdate(
1213"  CREATE VIEW SYS_JDBC.Catalogs AS " +
1214"  SELECT NULL AS \"TABLE_CAT\" \n" +
1215"    FROM SYS_INFO.sUSRSchemaInfo\n" + // Hacky, this will generate a 0 row
1216"   WHERE FALSE\n");                   // table.
1217      // A JDBC helper view for the 'getColumns' meta-data method
1218      stmt.executeUpdate(
1219"  CREATE VIEW SYS_JDBC.Columns AS " +
1220"  SELECT NULL AS \"TABLE_CAT\",\n" +
1221"         \"schema\" AS \"TABLE_SCHEM\",\n" +
1222"         \"table\" AS \"TABLE_NAME\",\n" +
1223"         \"column\" AS \"COLUMN_NAME\",\n" +
1224"         \"sql_type\" AS \"DATA_TYPE\",\n" +
1225"         \"type_desc\" AS \"TYPE_NAME\",\n" +
1226"         IF(\"size\" = -1, 1024, \"size\") AS \"COLUMN_SIZE\",\n" +
1227"         NULL AS \"BUFFER_LENGTH\",\n" +
1228"         \"scale\" AS \"DECIMAL_DIGITS\",\n" +
1229"         IF(\"sql_type\" = -7, 2, 10) AS \"NUM_PREC_RADIX\",\n" +
1230"         IF(\"not_null\", 0, 1) AS \"NULLABLE\",\n" +
1231"         '' AS \"REMARKS\",\n" +
1232"         \"default\" AS \"COLUMN_DEF\",\n" +
1233"         NULL AS \"SQL_DATA_TYPE\",\n" +
1234"         NULL AS \"SQL_DATETIME_SUB\",\n" +
1235"         IF(\"size\" = -1, 1024, \"size\") AS \"CHAR_OCTET_LENGTH\",\n" +
1236"         \"seq_no\" + 1 AS \"ORDINAL_POSITION\",\n" +
1237"         IF(\"not_null\", 'NO', 'YES') AS \"IS_NULLABLE\"\n" +
1238"    FROM SYS_JDBC.ThisUserTableColumns\n");
1239      // A JDBC helper view for the 'getColumnPrivileges' meta-data method
1240      stmt.executeUpdate(
1241"  CREATE VIEW SYS_JDBC.ColumnPrivileges AS " +
1242"  SELECT \"TABLE_CAT\",\n" +
1243"         \"TABLE_SCHEM\",\n" +
1244"         \"TABLE_NAME\",\n" +
1245"         \"COLUMN_NAME\",\n" +
1246"         IF(\"ThisUserGrant.granter\" = '@SYSTEM', \n" +
1247"                        NULL, \"ThisUserGrant.granter\") AS \"GRANTOR\",\n" +
1248"         IF(\"ThisUserGrant.grantee\" = '@PUBLIC', \n" +
1249"                    'public', \"ThisUserGrant.grantee\") AS \"GRANTEE\",\n" +
1250"         \"ThisUserGrant.description\" AS \"PRIVILEGE\",\n" +
1251"         IF(\"grant_option\" = 'true', 'YES', 'NO') AS \"IS_GRANTABLE\" \n" +
1252"    FROM SYS_JDBC.Columns, SYS_JDBC.ThisUserGrant \n" +
1253"   WHERE CONCAT(Columns.TABLE_SCHEM, '.', Columns.TABLE_NAME) = \n" +
1254"         ThisUserGrant.param \n" +
1255"     AND SYS_JDBC.ThisUserGrant.object = 1 \n" +
1256"     AND SYS_JDBC.ThisUserGrant.description IS NOT NULL \n");
1257      // A JDBC helper view for the 'getTablePrivileges' meta-data method
1258      stmt.executeUpdate(
1259"  CREATE VIEW SYS_JDBC.TablePrivileges AS " +
1260"  SELECT \"TABLE_CAT\",\n" +
1261"         \"TABLE_SCHEM\",\n" +
1262"         \"TABLE_NAME\",\n" +
1263"         IF(\"ThisUserGrant.granter\" = '@SYSTEM', \n" +
1264"                        NULL, \"ThisUserGrant.granter\") AS \"GRANTOR\",\n" +
1265"         IF(\"ThisUserGrant.grantee\" = '@PUBLIC', \n" +
1266"                    'public', \"ThisUserGrant.grantee\") AS \"GRANTEE\",\n" +
1267"         \"ThisUserGrant.description\" AS \"PRIVILEGE\",\n" +
1268"         IF(\"grant_option\" = 'true', 'YES', 'NO') AS \"IS_GRANTABLE\" \n" +
1269"    FROM SYS_JDBC.Tables, SYS_JDBC.ThisUserGrant \n" +
1270"   WHERE CONCAT(Tables.TABLE_SCHEM, '.', Tables.TABLE_NAME) = \n" +
1271"         ThisUserGrant.param \n" +
1272"     AND SYS_JDBC.ThisUserGrant.object = 1 \n" +
1273"     AND SYS_JDBC.ThisUserGrant.description IS NOT NULL \n");
1274      // A JDBC helper view for the 'getPrimaryKeys' meta-data method
1275      stmt.executeUpdate(
1276"  CREATE VIEW SYS_JDBC.PrimaryKeys AS " +
1277"  SELECT NULL \"TABLE_CAT\",\n" +
1278"         \"schema\" \"TABLE_SCHEM\",\n" +
1279"         \"table\" \"TABLE_NAME\",\n" +
1280"         \"column\" \"COLUMN_NAME\",\n" +
1281"         \"SYS_INFO.sUSRPrimaryColumns.seq_no\" \"KEY_SEQ\",\n" +
1282"         \"name\" \"PK_NAME\"\n" +
1283"    FROM SYS_INFO.sUSRPKeyInfo, SYS_INFO.sUSRPrimaryColumns\n" +
1284"   WHERE sUSRPKeyInfo.id = sUSRPrimaryColumns.pk_id\n" +
1285"     AND \"schema\" IN\n" +
1286"            ( SELECT \"name\" FROM SYS_JDBC.ThisUserSchemaInfo )\n");
1287      // A JDBC helper view for the 'getImportedKeys' meta-data method
1288      stmt.executeUpdate(
1289"  CREATE VIEW SYS_JDBC.ImportedKeys AS " +
1290"  SELECT NULL \"PKTABLE_CAT\",\n" +
1291"         \"sUSRFKeyInfo.ref_schema\" \"PKTABLE_SCHEM\",\n" +
1292"         \"sUSRFKeyInfo.ref_table\" \"PKTABLE_NAME\",\n" +
1293"         \"sUSRForeignColumns.pcolumn\" \"PKCOLUMN_NAME\",\n" +
1294"         NULL \"FKTABLE_CAT\",\n" +
1295"         \"sUSRFKeyInfo.schema\" \"FKTABLE_SCHEM\",\n" +
1296"         \"sUSRFKeyInfo.table\" \"FKTABLE_NAME\",\n" +
1297"         \"sUSRForeignColumns.fcolumn\" \"FKCOLUMN_NAME\",\n" +
1298"         \"sUSRForeignColumns.seq_no\" \"KEY_SEQ\",\n" +
1299"         I_FRULE_CONVERT(\"sUSRFKeyInfo.update_rule\") \"UPDATE_RULE\",\n" +
1300"         I_FRULE_CONVERT(\"sUSRFKeyInfo.delete_rule\") \"DELETE_RULE\",\n" +
1301"         \"sUSRFKeyInfo.name\" \"FK_NAME\",\n" +
1302"         NULL \"PK_NAME\",\n" +
1303"         \"sUSRFKeyInfo.deferred\" \"DEFERRABILITY\"\n" +
1304"    FROM SYS_INFO.sUSRFKeyInfo, SYS_INFO.sUSRForeignColumns\n" +
1305"   WHERE sUSRFKeyInfo.id = sUSRForeignColumns.fk_id\n" +
1306"     AND \"sUSRFKeyInfo.schema\" IN\n" +
1307"              ( SELECT \"name\" FROM SYS_JDBC.ThisUserSchemaInfo )\n");
1308      // A JDBC helper view for the 'getExportedKeys' meta-data method
1309      stmt.executeUpdate(
1310"  CREATE VIEW SYS_JDBC.ExportedKeys AS " +
1311"  SELECT NULL \"PKTABLE_CAT\",\n" +
1312"         \"sUSRFKeyInfo.ref_schema\" \"PKTABLE_SCHEM\",\n" +
1313"         \"sUSRFKeyInfo.ref_table\" \"PKTABLE_NAME\",\n" +
1314"         \"sUSRForeignColumns.pcolumn\" \"PKCOLUMN_NAME\",\n" +
1315"         NULL \"FKTABLE_CAT\",\n" +
1316"         \"sUSRFKeyInfo.schema\" \"FKTABLE_SCHEM\",\n" +
1317"         \"sUSRFKeyInfo.table\" \"FKTABLE_NAME\",\n" +
1318"         \"sUSRForeignColumns.fcolumn\" \"FKCOLUMN_NAME\",\n" +
1319"         \"sUSRForeignColumns.seq_no\" \"KEY_SEQ\",\n" +
1320"         I_FRULE_CONVERT(\"sUSRFKeyInfo.update_rule\") \"UPDATE_RULE\",\n" +
1321"         I_FRULE_CONVERT(\"sUSRFKeyInfo.delete_rule\") \"DELETE_RULE\",\n" +
1322"         \"sUSRFKeyInfo.name\" \"FK_NAME\",\n" +
1323"         NULL \"PK_NAME\",\n" +
1324"         \"sUSRFKeyInfo.deferred\" \"DEFERRABILITY\"\n" +
1325"    FROM SYS_INFO.sUSRFKeyInfo, SYS_INFO.sUSRForeignColumns\n" +
1326"   WHERE sUSRFKeyInfo.id = sUSRForeignColumns.fk_id\n" +
1327"     AND \"sUSRFKeyInfo.schema\" IN\n" +
1328"              ( SELECT \"name\" FROM SYS_JDBC.ThisUserSchemaInfo )\n");
1329      // A JDBC helper view for the 'getCrossReference' meta-data method
1330      stmt.executeUpdate(
1331"  CREATE VIEW SYS_JDBC.CrossReference AS " +
1332"  SELECT NULL \"PKTABLE_CAT\",\n" +
1333"         \"sUSRFKeyInfo.ref_schema\" \"PKTABLE_SCHEM\",\n" +
1334"         \"sUSRFKeyInfo.ref_table\" \"PKTABLE_NAME\",\n" +
1335"         \"sUSRForeignColumns.pcolumn\" \"PKCOLUMN_NAME\",\n" +
1336"         NULL \"FKTABLE_CAT\",\n" +
1337"         \"sUSRFKeyInfo.schema\" \"FKTABLE_SCHEM\",\n" +
1338"         \"sUSRFKeyInfo.table\" \"FKTABLE_NAME\",\n" +
1339"         \"sUSRForeignColumns.fcolumn\" \"FKCOLUMN_NAME\",\n" +
1340"         \"sUSRForeignColumns.seq_no\" \"KEY_SEQ\",\n" +
1341"         I_FRULE_CONVERT(\"sUSRFKeyInfo.update_rule\") \"UPDATE_RULE\",\n" +
1342"         I_FRULE_CONVERT(\"sUSRFKeyInfo.delete_rule\") \"DELETE_RULE\",\n" +
1343"         \"sUSRFKeyInfo.name\" \"FK_NAME\",\n" +
1344"         NULL \"PK_NAME\",\n" +
1345"         \"sUSRFKeyInfo.deferred\" \"DEFERRABILITY\"\n" +
1346"    FROM SYS_INFO.sUSRFKeyInfo, SYS_INFO.sUSRForeignColumns\n" +
1347"   WHERE sUSRFKeyInfo.id = sUSRForeignColumns.fk_id\n" +
1348"     AND \"sUSRFKeyInfo.schema\" IN\n" +
1349"              ( SELECT \"name\" FROM SYS_JDBC.ThisUserSchemaInfo )\n");
1350
1351    }
1352    catch (SQLException e) {
1353      if (e instanceof MSQLException) {
1354        MSQLException msqle = (MSQLException) e;
1355        Debug().write(Lvl.ERROR, this,
1356                      msqle.getServerErrorStackTrace());
1357      }
1358      Debug().writeException(Lvl.ERROR, e);
1359      throw new RuntimeException  ("SQL Error: " + e.getMessage());
1360    }
1361
1362  }
1363  
1364  /**
1365   * Creates all the priv/password system tables.
1366   */
1367  private void createSystemTables(DatabaseConnection connection)
1368                                                   throws DatabaseException {
1369
1370    // --- The user management tables ---
1371    DataTableDef sUSRPassword = new DataTableDef();
1372    sUSRPassword.setTableName(SYS_PASSWORD);
1373    sUSRPassword.addColumn(DataTableColumnDef.createStringColumn("UserName"));
1374    sUSRPassword.addColumn(DataTableColumnDef.createStringColumn("Password"));
1375
1376    DataTableDef sUSRUserPriv = new DataTableDef();
1377    sUSRUserPriv.setTableName(SYS_USERPRIV);
1378    sUSRUserPriv.addColumn(DataTableColumnDef.createStringColumn("UserName"));
1379    sUSRUserPriv.addColumn(
1380                      DataTableColumnDef.createStringColumn("PrivGroupName"));
1381
1382    DataTableDef sUSRUserConnectPriv = new DataTableDef();
1383    sUSRUserConnectPriv.setTableName(SYS_USERCONNECT);
1384    sUSRUserConnectPriv.addColumn(
1385                           DataTableColumnDef.createStringColumn("UserName"));
1386    sUSRUserConnectPriv.addColumn(
1387                           DataTableColumnDef.createStringColumn("Protocol"));
1388    sUSRUserConnectPriv.addColumn(
1389                               DataTableColumnDef.createStringColumn("Host"));
1390    sUSRUserConnectPriv.addColumn(
1391                             DataTableColumnDef.createStringColumn("Access"));
1392
1393    DataTableDef sUSRGrant = new DataTableDef();
1394    sUSRGrant.setTableName(SYS_GRANTS);
1395    sUSRGrant.addColumn(DataTableColumnDef.createNumericColumn("priv_bit"));
1396    sUSRGrant.addColumn(DataTableColumnDef.createNumericColumn("object"));
1397    sUSRGrant.addColumn(DataTableColumnDef.createStringColumn("param"));
1398    sUSRGrant.addColumn(DataTableColumnDef.createStringColumn("grantee"));
1399    sUSRGrant.addColumn(DataTableColumnDef.createStringColumn("grant_option"));
1400    sUSRGrant.addColumn(DataTableColumnDef.createStringColumn("granter"));
1401
1402    DataTableDef sUSRService = new DataTableDef();
1403    sUSRService.setTableName(SYS_SERVICE);
1404    sUSRService.addColumn(DataTableColumnDef.createStringColumn("name"));
1405    sUSRService.addColumn(DataTableColumnDef.createStringColumn("class"));
1406    sUSRService.addColumn(DataTableColumnDef.createStringColumn("type"));
1407
1408    DataTableDef sUSRFunctionFactory = new DataTableDef();
1409    sUSRFunctionFactory.setTableName(SYS_FUNCTIONFACTORY);
1410    sUSRFunctionFactory.addColumn(
1411                              DataTableColumnDef.createStringColumn("name"));
1412    sUSRFunctionFactory.addColumn(
1413                             DataTableColumnDef.createStringColumn("class"));
1414    sUSRFunctionFactory.addColumn(
1415                              DataTableColumnDef.createStringColumn("type"));
1416
1417    DataTableDef sUSRFunction = new DataTableDef();
1418    sUSRFunction.setTableName(SYS_FUNCTION);
1419    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("schema"));
1420    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("name"));
1421    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("type"));
1422    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("location"));
1423    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("return_type"));
1424    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("args_type"));
1425    sUSRFunction.addColumn(DataTableColumnDef.createStringColumn("username"));
1426
1427    DataTableDef sUSRView = new DataTableDef();
1428    sUSRView.setTableName(SYS_VIEW);
1429    sUSRView.addColumn(DataTableColumnDef.createStringColumn("schema"));
1430    sUSRView.addColumn(DataTableColumnDef.createStringColumn("name"));
1431    sUSRView.addColumn(DataTableColumnDef.createBinaryColumn("query"));
1432    sUSRView.addColumn(DataTableColumnDef.createBinaryColumn("data"));
1433    sUSRView.addColumn(DataTableColumnDef.createStringColumn("username"));
1434
1435    DataTableDef sUSRLabel = new DataTableDef();
1436    sUSRLabel.setTableName(SYS_LABEL);
1437    sUSRLabel.addColumn(DataTableColumnDef.createNumericColumn("object_type"));
1438    sUSRLabel.addColumn(DataTableColumnDef.createStringColumn("object_name"));
1439    sUSRLabel.addColumn(DataTableColumnDef.createStringColumn("label"));
1440
1441    DataTableDef sUSRDataTrigger = new DataTableDef();
1442    sUSRDataTrigger.setTableName(SYS_DATA_TRIGGER);
1443    sUSRDataTrigger.addColumn(DataTableColumnDef.createStringColumn("schema"));
1444    sUSRDataTrigger.addColumn(DataTableColumnDef.createStringColumn("name"));
1445    sUSRDataTrigger.addColumn(DataTableColumnDef.createNumericColumn("type"));
1446    sUSRDataTrigger.addColumn(DataTableColumnDef.createStringColumn("on_object"));
1447    sUSRDataTrigger.addColumn(DataTableColumnDef.createStringColumn("action"));
1448    sUSRDataTrigger.addColumn(DataTableColumnDef.createBinaryColumn("misc"));
1449    sUSRDataTrigger.addColumn(DataTableColumnDef.createStringColumn("username"));
1450    
1451    // Create the tables
1452    connection.alterCreateTable(sUSRPassword, 91, 128);
1453    connection.alterCreateTable(sUSRUserPriv, 91, 128);
1454    connection.alterCreateTable(sUSRUserConnectPriv, 91, 128);
1455    connection.alterCreateTable(sUSRGrant, 195, 128);
1456    connection.alterCreateTable(sUSRService, 91, 128);
1457    connection.alterCreateTable(sUSRFunctionFactory, 91, 128);
1458    connection.alterCreateTable(sUSRFunction, 91, 128);
1459    connection.alterCreateTable(sUSRView, 91, 128);
1460    connection.alterCreateTable(sUSRLabel, 91, 128);
1461    connection.alterCreateTable(sUSRDataTrigger, 91, 128);
1462
1463  }
1464
1465  /**
1466   * Sets all the standard functions and procedures available to engine.
1467   * This creates an entry in the SYS_FUNCTION table for all the dynamic
1468   * functions and procedures.  This may not include the functions exposed
1469   * though the FunctionFactory interface.
1470   */
1471  public void setupSystemFunctions(DatabaseConnection connection,
1472                                   String   admin_user) throws DatabaseException {
1473
1474    final String   GRANTER = INTERNAL_SECURE_USERNAME;
1475
1476    // The manager handling the functions.
1477    ProcedureManager manager = connection.getProcedureManager();
1478
1479    // Define the SYSTEM_MAKE_BACKUP procedure
1480    Class   c = com.mckoi.database.procedure.SystemBackup.class;
1481    manager.defineJavaProcedure(
1482          new ProcedureName(SYSTEM_SCHEMA, "SYSTEM_MAKE_BACKUP"),
1483          "com.mckoi.database.procedure.SystemBackup.invoke(ProcedureConnection, String)",
1484          TType.STRING_TYPE, new TType[] { TType.STRING_TYPE },
1485          admin_user);
1486
1487    // -----
1488
1489    // Set the grants for the procedures.
1490    GrantManager grants = connection.getGrantManager();
1491
1492    // Revoke all existing grants on the internal stored procedures.
1493    grants.revokeAllGrantsOnObject(GrantManager.TABLE,
1494                                   "SYS_INFO.SYSTEM_MAKE_BACKUP");
1495    
1496    // Grant execute priv with grant option to administrator
1497    grants.addGrant(Privileges.PROCEDURE_EXECUTE_PRIVS,
1498                    GrantManager.TABLE,
1499                    "SYS_INFO.SYSTEM_MAKE_BACKUP",
1500                    admin_user, true, GRANTER);
1501
1502  }
1503
1504  /**
1505   * Clears all the grant information in the sUSRGrant table.  This should only
1506   * be used if we need to refresh the grant information for whatever reason
1507   * (such as when converting between different versions).
1508   */
1509  private void clearAllGrants(DatabaseConnection connection)
1510                                                    throws DatabaseException {
1511    DataTable grant_table = connection.getTable(SYS_GRANTS);
1512    grant_table.delete(grant_table);
1513  }
1514
1515  /**
1516   * Set up the system table grants.
1517   * <p>
1518   * This gives the grantee user full access to sUSRPassword,
1519   * sUSRUserPriv, sUSRUserConnectPriv, sUSRService, sUSRFunctionFactory,
1520   * and sUSRFunction.  All other sUSR tables are granted SELECT only.
1521   * If 'grant_option' is true then the user is given the option to give the
1522   * grants to other users.
1523   */
1524  private void setSystemGrants(DatabaseConnection connection,
1525                               String   grantee) throws DatabaseException {
1526
1527    final String   GRANTER = INTERNAL_SECURE_USERNAME;
1528    
1529    // Add all priv grants to those that the system user is allowed to change
1530    GrantManager manager = connection.getGrantManager();
1531
1532    // Add schema grant for APP
1533    manager.addGrant(Privileges.SCHEMA_ALL_PRIVS, GrantManager.SCHEMA,
1534                     "APP",
1535                     grantee, true, GRANTER);
1536    // Add public grant for SYS_INFO
1537    manager.addGrant(Privileges.SCHEMA_READ_PRIVS, GrantManager.SCHEMA,
1538                     "SYS_INFO",
1539                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1540    // Add public grant for SYS_JDBC
1541    manager.addGrant(Privileges.SCHEMA_READ_PRIVS, GrantManager.SCHEMA,
1542                     "SYS_JDBC",
1543                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1544
1545    // For all tables in the SYS_INFO schema, grant all privileges to the
1546    // system user.
1547    manager.addGrantToAllTablesInSchema("SYS_INFO",
1548             Privileges.TABLE_ALL_PRIVS, grantee, false, GRANTER);
1549    
1550    // Set the public grants for the system tables,
1551    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1552                     "SYS_INFO.sUSRConnectionInfo",
1553                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1554    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1555                     "SYS_INFO.sUSRCurrentConnections",
1556                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1557    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1558                     "SYS_INFO.sUSRDatabaseStatistics",
1559                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1560    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1561                     "SYS_INFO.sUSRDatabaseVars",
1562                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1563    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1564                     "SYS_INFO.sUSRProductInfo",
1565                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1566    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1567                     "SYS_INFO.sUSRSQLTypeInfo",
1568                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1569
1570    // Set public grants for the system views.
1571    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1572                     "SYS_JDBC.ThisUserGrant",
1573                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1574    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1575                     "SYS_JDBC.ThisUserSimpleGrant",
1576                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1577    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1578                     "SYS_JDBC.ThisUserSchemaInfo",
1579                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1580    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1581                     "SYS_JDBC.ThisUserTableColumns",
1582                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1583    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1584                     "SYS_JDBC.ThisUserTableInfo",
1585                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1586
1587    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1588                     "SYS_JDBC.Tables",
1589                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1590    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1591                     "SYS_JDBC.Schemas",
1592                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1593    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1594                     "SYS_JDBC.Catalogs",
1595                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1596    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1597                     "SYS_JDBC.Columns",
1598                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1599    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1600                     "SYS_JDBC.ColumnPrivileges",
1601                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1602    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1603                     "SYS_JDBC.TablePrivileges",
1604                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1605    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1606                     "SYS_JDBC.PrimaryKeys",
1607                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1608    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1609                     "SYS_JDBC.ImportedKeys",
1610                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1611    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1612                     "SYS_JDBC.ExportedKeys",
1613                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1614    manager.addGrant(Privileges.TABLE_READ_PRIVS, GrantManager.TABLE,
1615                     "SYS_JDBC.CrossReference",
1616                     GrantManager.PUBLIC_USERNAME_STR, false, GRANTER);
1617
1618  }
1619
1620  /**
1621   * Sets the system table listeners on the SYS_INFO.sUSRView table.  These
1622   * listeners are used to cache information
1623   * that is stored and retrieved from those tables.
1624   */
1625  private void setSystemTableListeners() {
1626//    getSystem().addMasterTableListener(SYS_VIEW, new ViewTableListener());
1627  }
1628  
1629  /**
1630   * Goes through all tables in the database not in the SYS_INFO schema and
1631   * adds an entry in the grant table for it.
1632   * <p>
1633   * This is for converting from a pre-grant database.
1634   *
1635   * @param connection the database transaction
1636   * @param grantee the grantee to apply the table privs to
1637   */
1638  private void convertPreGrant(DatabaseConnection connection,
1639                               String   grantee) throws DatabaseException {
1640
1641    String   GRANTER = INTERNAL_SECURE_USERNAME;
1642    GrantManager manager = connection.getGrantManager();
1643
1644    // Setup grants for any user schema that have been created.
1645    SchemaDef[] all_schema = connection.getSchemaList();
1646    for (int i = 0; i < all_schema.length; ++i) {
1647      SchemaDef schema = all_schema[i];
1648      // The admin user is given full privs to all tables in USER or DEFAULT
1649      // schema.
1650      if (schema.getType().equals("USER") ||
1651          schema.getType().equals("DEFAULT")) {
1652        // Don't set grants for default schema
1653        if (!schema.getType().equals("DEFAULT")) {
1654          manager.addGrant(Privileges.TABLE_ALL_PRIVS, GrantManager.SCHEMA,
1655                           schema.getName(), grantee, true, GRANTER);
1656        }
1657        manager.addGrantToAllTablesInSchema(schema.getName(),
1658                 Privileges.TABLE_ALL_PRIVS, grantee, true, GRANTER);
1659      }
1660    }
1661
1662  }
1663
1664  /**
1665   * Converts tables from a database that are pre database schema.
1666   */
1667  private void convertPreSchema(DatabaseConnection connection)
1668                                                   throws DatabaseException {
1669    throw new DatabaseException(
1670                 "Converting from pre-schema no longer supported.");
1671  }
1672
1673  /**
1674   * Creates and sets up a new database to an initial empty state.  The
1675   * creation process involves creating all the system tables and views, adding
1676   * an administrator user account, creating schema, and setting up the initial
1677   * grant information for the administrator user.
1678   * <p>
1679   * The 'username' and 'password' parameter given are set for the administrator
1680   * account.
1681   */
1682  public void create(String   username, String   password) {
1683
1684    if (isReadOnly()) {
1685      throw new RuntimeException  ("Can not create database in read only mode.");
1686    }
1687
1688    if (username == null || username.length() == 0 ||
1689        password == null || password.length() == 0) {
1690      throw new RuntimeException  (
1691                               "Must have valid username and password String");
1692    }
1693
1694    try {
1695      // Create the conglomerate
1696      conglomerate.create(getName());
1697
1698      DatabaseConnection connection = createNewConnection(null, null);
1699      DatabaseQueryContext context = new DatabaseQueryContext(connection);
1700      connection.getLockingMechanism().setMode(
1701                                            LockingMechanism.EXCLUSIVE_MODE);
1702      connection.setCurrentSchema(SYSTEM_SCHEMA);
1703
1704      // Create the schema information tables introduced in version 0.90
1705      // and 0.94
1706      createSchemaInfoTables(connection);
1707
1708      // The system tables that are present in every conglomerate.
1709      createSystemTables(connection);
1710      // Create the system views
1711      createSystemViews(connection);
1712
1713      // Creates the administrator user.
1714      createUser(context, username, password);
1715      // This is the admin user so add to the 'secure access' table.
1716      addUserToGroup(context, username, SECURE_GROUP);
1717      // Allow all localhost TCP connections.
1718      // NOTE: Permissive initial security!
1719      grantHostAccessToUser(context, username, "TCP", "%");
1720      // Allow all Local connections (from within JVM).
1721      grantHostAccessToUser(context, username, "Local", "%");
1722
1723      // Sets the system grants for the administrator
1724      setSystemGrants(connection, username);
1725
1726      // Set all default system procedures.
1727      setupSystemFunctions(connection, username);
1728
1729      try {
1730        // Close and commit this transaction.
1731        connection.commit();
1732      }
1733      catch (TransactionException e) {
1734        Debug().writeException(e);
1735        throw new Error  ("Transaction Error: " + e.getMessage());
1736      }
1737
1738      connection.getLockingMechanism().finishMode(
1739                                            LockingMechanism.EXCLUSIVE_MODE);
1740      connection.close();
1741
1742      // Close the conglomerate.
1743      conglomerate.close();
1744
1745    }
1746    catch (DatabaseException e) {
1747      Debug().writeException(e);
1748      throw new Error  ("Database Exception: " + e.getMessage());
1749    }
1750    catch (IOException   e) {
1751      Debug().writeException(e);
1752      throw new Error  ("IO Error: " + e.getMessage());
1753    }
1754
1755  }
1756
1757  /**
1758   * Initializes the database.  This opens all the files that are required for
1759   * the operation of the database.  If it finds that the version of the
1760   * data files are not a compatible version, this method throws an exception.
1761   * <p>
1762   * NOTE: Perhaps a better name for this method is 'open'.
1763   */
1764  public void init() throws DatabaseException {
1765
1766    if (initialised) {
1767      throw new RuntimeException  ("Init() method can only be called once.");
1768    }
1769
1770    // Reset all session statistics.
1771    stats().resetSession();
1772
1773    try {
1774      File   log_path = system.getLogDirectory();
1775      if (log_path != null && system.logQueries()) {
1776        commands_log = new Log(new File  (log_path.getPath(), "commands.log"),
1777                               256 * 1024, 5);
1778      }
1779      else {
1780        commands_log = Log.nullLog();
1781      }
1782
1783      // Check if the state file exists.  If it doesn't, we need to report
1784      // incorrect version.
1785      if (!storeSystem().storeExists(getName() + "_sf")) {
1786        // If state store doesn't exist but the legacy style '.sf' state file
1787        // exists,
1788        if (system.getDatabasePath() != null &&
1789            new File  (system.getDatabasePath(), getName() + ".sf").exists()) {
1790          throw new DatabaseException(
1791             "The state store for this database doesn't exist.  This means " +
1792             "the database version is pre version 1.0.  Please see the " +
1793             "README for the details for converting this database.");
1794        }
1795        else {
1796          // If neither store or state file exist, assume database doesn't
1797          // exist.
1798          throw new DatabaseException("The database does not exist.");
1799        }
1800      }
1801
1802      // Open the conglomerate
1803      conglomerate.open(getName());
1804
1805      // Check the state of the conglomerate,
1806      DatabaseConnection connection = createNewConnection(null, null);
1807      DatabaseQueryContext context = new DatabaseQueryContext(connection);
1808      connection.getLockingMechanism().setMode(
1809                                     LockingMechanism.EXCLUSIVE_MODE);
1810      if (!connection.tableExists(TableDataConglomerate.PERSISTENT_VAR_TABLE)) {
1811        throw new DatabaseException(
1812           "The sUSRDatabaseVars table doesn't exist.  This means the " +
1813           "database is pre-schema version 1 or the table has been deleted." +
1814           "If you are converting an old version of the database, please " +
1815           "convert the database using an older release.");
1816      }
1817
1818      // What version is the data?
1819      DataTable database_vars =
1820               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
1821      Map   vars = database_vars.toMap();
1822      String   db_version = vars.get("database.version").toString();
1823      // If the version doesn't equal the current version, throw an error.
1824      if (!db_version.equals("1.4")) {
1825        throw new DatabaseException(
1826           "Incorrect data file version '" + db_version + "'.  Please see " +
1827           "the README on how to convert the data files to the current " +
1828           "version.");
1829      }
1830      
1831      // Commit and close the connection.
1832      connection.commit();
1833      connection.getLockingMechanism().finishMode(
1834                                     LockingMechanism.EXCLUSIVE_MODE);
1835      connection.close();
1836
1837    }
1838    catch (TransactionException e) {
1839      // This would be very strange error to receive for in initializing
1840      // database...
1841      throw new Error  ("Transaction Error: " + e.getMessage());
1842    }
1843    catch (IOException   e) {
1844      e.printStackTrace(System.err);
1845      throw new Error  ("IO Error: " + e.getMessage());
1846    }
1847
1848    // Sets up the system table listeners
1849    setSystemTableListeners();
1850    
1851    initialised = true;
1852
1853  }
1854
1855  /**
1856   * Cleanly shuts down the database.  It is important that this method is
1857   * called just before the system closes down.
1858   * <p>
1859   * The main purpose of this method is to ensure any tables that are backed
1860   * by files and in a 'safe' state and cleanly flushed to the file system.
1861   * <p>
1862   * If 'delete_on_shutdown' is true, the database will delete itself from the
1863   * file system when it shuts down.
1864   */
1865  public void shutdown() throws DatabaseException {
1866
1867    if (initialised == false) {
1868      throw new Error  ("The database is not initialized.");
1869    }
1870
1871    try {
1872      if (delete_on_shutdown == true) {
1873        // Delete the conglomerate if the database is set to delete on
1874        // shutdown.
1875        conglomerate.delete();
1876      }
1877      else {
1878        // Otherwise close the conglomerate.
1879        conglomerate.close();
1880      }
1881    }
1882    catch (IOException   e) {
1883      Debug().writeException(e);
1884      throw new Error  ("IO Error: " + e.getMessage());
1885    }
1886
1887    // Shut down the logs...
1888    if (commands_log != null) {
1889      commands_log.close();
1890    }
1891
1892    initialised = false;
1893
1894  }
1895
1896  /**
1897   * Returns true if the database exists.  This must be called before 'init'
1898   * and 'create'.  It checks that the database files exist and we can boot
1899   * into the database.
1900   */
1901  public boolean exists() {
1902    if (initialised == true) {
1903      throw new RuntimeException  (
1904          "The database is initialised, so no point testing it's existance.");
1905    }
1906
1907    try {
1908      // HACK: If the legacy style '.sf' state file exists then we must return
1909      //   true here because technically the database exists but is not in the
1910      //   correct version.
1911      if (conglomerate.exists(getName())) {
1912        return true;
1913      }
1914      else {
1915        boolean is_file_s_system =
1916                          (system.storeSystem() instanceof V1FileStoreSystem);
1917        if (is_file_s_system &&
1918            new File  (system.getDatabasePath(), getName() + ".sf").exists()) {
1919          return true;
1920        }
1921      }
1922      return false;
1923    }
1924    catch (IOException   e) {
1925      Debug().writeException(e);
1926      throw new RuntimeException  ("IO Error: " + e.getMessage());
1927    }
1928
1929  }
1930
1931  /**
1932   * If the 'deleteOnShutdown' flag is set, the database will delete the
1933   * database from the file system when it is shutdown.
1934   * <p>
1935   * NOTE: Use with care - if this is set to true and the database is shutdown
1936   *   it will result in total loss of data.
1937   */
1938  public final void setDeleteOnShutdown(boolean status) {
1939    delete_on_shutdown = status;
1940  }
1941
1942
1943
1944  /**
1945   * Returns true if the database is initialised.
1946   */
1947  public boolean isInitialized() {
1948    return initialised;
1949  }
1950
1951  /**
1952   * Copies all the persistent data in this database (the conglomerate) to the
1953   * given destination path.  This can copy information while the database
1954   * is 'live'.
1955   */
1956  public void liveCopyTo(File   path) throws IOException   {
1957    if (initialised == false) {
1958      throw new Error  ("The database is not initialized.");
1959    }
1960
1961    // Set up the destination conglomerate to copy all the data to,
1962    // Note that this sets up a typical destination conglomerate and changes
1963    // the cache size and disables the debug log.
1964    TransactionSystem copy_system = new TransactionSystem();
1965    DefaultDBConfig config = new DefaultDBConfig();
1966    config.setDatabasePath(path.getAbsolutePath());
1967    config.setLogPath("");
1968    config.setMinimumDebugLevel(50000);
1969    // Set data cache to 1MB
1970    config.setValue("data_cache_size", "1048576");
1971    // Set io_safety_level to 1 for destination database
1972    // ISSUE: Is this a good assumption to make - 
1973    //     we don't care if changes are lost by a power failure when we are
1974    //     backing up the database.  Even if journalling is enabled, a power
1975    //     failure will lose changes in the backup copy anyway.
1976    config.setValue("io_safety_level", "1");
1977    java.io.StringWriter   debug_output = new java.io.StringWriter  ();
1978    copy_system.setDebugOutput(debug_output);
1979    copy_system.init(config);
1980    final TableDataConglomerate dest_conglomerate =
1981            new TableDataConglomerate(copy_system, copy_system.storeSystem());
1982
1983    // Open the congloemrate
1984    dest_conglomerate.minimalCreate("DefaultDatabase");
1985
1986    try {
1987      // Make a copy of this conglomerate into the destination conglomerate,
1988      conglomerate.liveCopyTo(dest_conglomerate);
1989    }
1990    finally {
1991      // Close the congloemrate when finished.
1992      dest_conglomerate.close();
1993      // Dispose the TransactionSystem
1994      copy_system.dispose();
1995    }
1996    
1997  }
1998
1999  // ---------- Database convertion ----------
2000
2001  /**
2002   * Processes each table in user space and converts the format to the newest
2003   * version of the data file format.  This is simply achieved by running the
2004   * 'compactTable' command on the transaction for each table.
2005   */
2006  private void convertAllUserTables(DatabaseConnection connection,
2007                                 PrintStream   out) throws TransactionException {
2008    out.println("Converting user table format to latest version.");
2009    // Convert all user tables in the database
2010    TableName[] all_tables = connection.getTableList();
2011    for (int i = 0; i < all_tables.length; ++i) {
2012      TableName table_name = all_tables[i];
2013      String   schema_name = table_name.getSchema();
2014      if (!schema_name.equals("SYS_INFO") &&
2015          connection.getTableType(table_name).equals("TABLE")) {
2016        out.println("Converting: " + table_name);
2017        connection.compactTable(table_name);
2018        connection.commit();
2019      }
2020    }
2021  }
2022
2023  /** 
2024   * Returns true if the given sql type is possibly a large object.
2025   */
2026  private static boolean largeObjectTest(int sql_type) {
2027    return (sql_type == SQLTypes.CHAR ||
2028            sql_type == SQLTypes.VARCHAR ||
2029            sql_type == SQLTypes.LONGVARCHAR ||
2030            sql_type == SQLTypes.BINARY ||
2031            sql_type == SQLTypes.VARBINARY ||
2032            sql_type == SQLTypes.LONGVARBINARY ||
2033            sql_type == SQLTypes.BLOB ||
2034            sql_type == SQLTypes.CLOB);
2035  }
2036  
2037  /**
2038   * Scans all the user tables for large objects and if a large object is
2039   * found, it is moved into the BlobStore.  A large object is an object that
2040   * uses more than 16 kbytes of storage space.
2041   */
2042  private void moveLargeObjectsToBlobStore(DatabaseConnection connection,
2043                                           PrintStream   out)
2044                throws TransactionException, IOException  , DatabaseException {
2045    out.println("Scanning user tables for large objects.");
2046
2047    DatabaseQueryContext context = new DatabaseQueryContext(connection);
2048    BlobStore blob_store = conglomerate.getBlobStore();
2049
2050    // Scan all user tables in the database
2051    TableName[] all_tables = connection.getTableList();
2052    for (int i = 0; i < all_tables.length; ++i) {
2053      TableName table_name = all_tables[i];
2054      String   schema_name = table_name.getSchema();
2055      boolean table_changed = false;
2056
2057      if (!schema_name.equals("SYS_INFO") &&
2058          connection.getTableType(table_name).equals("TABLE")) {
2059
2060        out.println("Processing: " + table_name);
2061        DataTable table = connection.getTable(table_name);
2062        DataTableDef table_def = table.getDataTableDef();
2063
2064        boolean possibly_has_large_objects = false;
2065        int column_count = table_def.columnCount();
2066        for (int n = 0; n < column_count; ++n) {
2067          int sql_type = table_def.columnAt(n).getSQLType();
2068          if (largeObjectTest(sql_type)) {
2069            possibly_has_large_objects = true;
2070          }
2071        }
2072
2073        if (possibly_has_large_objects) {
2074
2075          RowEnumeration e = table.rowEnumeration();
2076          while (e.hasMoreRows()) {
2077
2078            int row_index = e.nextRowIndex();
2079            ArrayList   changes = new ArrayList  (4);
2080
2081            for (int p = 0; p < column_count; ++p) {
2082              DataTableColumnDef col_def = table_def.columnAt(p);
2083              int sql_type = col_def.getSQLType();
2084
2085              if (largeObjectTest(sql_type)) {
2086                TObject tob = table.getCellContents(p, row_index);
2087                Object   ob = tob.getObject();
2088                if (ob != null) {
2089                  // String type
2090                  if (ob instanceof StringObject) {
2091                    StringObject s_object = (StringObject) ob;
2092                    if (s_object.length() > 4 * 1024) {
2093                      ClobRef ref =
2094                          blob_store.putStringInBlobStore(s_object.toString());
2095                      changes.add(new Assignment(
2096                                   new Variable(table_name, col_def.getName()),
2097                                   new Expression(
2098                                     new TObject(tob.getTType(), ref))));
2099                    }
2100                  }
2101                  // Binary type
2102                  if (ob instanceof ByteLongObject) {
2103                    ByteLongObject b_object = (ByteLongObject) ob;
2104                    if (b_object.length() > 8 * 1024) {
2105                      BlobRef ref =
2106                             blob_store.putByteLongObjectInBlobStore(b_object);
2107                      changes.add(new Assignment(
2108                                   new Variable(table_name, col_def.getName()),
2109                                   new Expression(
2110                                     new TObject(tob.getTType(), ref))));
2111                    }
2112                  }
2113                }
2114              }
2115            }
2116
2117            // If there was a change
2118            if (changes.size() > 0) {
2119              // Update the row
2120              Assignment[] assignments = (Assignment[]) changes.toArray(
2121                                               new Assignment[changes.size()]);
2122              Table st = table.singleRowSelect(row_index);
2123              table.update(context, st, assignments, -1);
2124              table_changed = true;
2125            }
2126
2127          }  // For each row
2128
2129          if (table_changed) {
2130            // Commit the connection.
2131            connection.commit();
2132            // Compact this table (will remove space from large objects).
2133            connection.compactTable(table_name);
2134          }
2135
2136          // Commit the connection.
2137          connection.commit();
2138
2139        }
2140      }
2141    }
2142  }
2143
2144  /**
2145   * Functionality for converting and old database format to the existing
2146   * format.  This would typically be called from a convert tool program.
2147   * <p>
2148   * Returns true if the convert was successful or false if it wasn't (error
2149   * message is output to the PrintWriter).
2150   */
2151  public boolean convertToCurrent(PrintStream   out, String   admin_username)
2152                                                          throws IOException   {
2153
2154    // Reset all session statistics.
2155    stats().resetSession();
2156
2157    try {
2158      // Don't log commands (there shouldn't be any anyway).
2159      commands_log = Log.nullLog();
2160
2161      // Convert the state file if it is necessary.
2162      File   legacy_state_file =
2163                         new File  (system.getDatabasePath(), getName() + ".sf");
2164      if (legacy_state_file.exists()) {
2165        String   state_store_fn = getName() + "_sf";
2166        // If the state store file already exists
2167        if (storeSystem().storeExists(state_store_fn)) {
2168          throw new IOException  (
2169            "Both legacy and version 1 state file exist.  Please remove one.");
2170        }
2171        out.println("Converting state file to current version.");
2172        // Create the new store,
2173        Store new_ss = storeSystem().createStore(state_store_fn);
2174        StateStore ss = new StateStore(new_ss);
2175        // Convert the existing store
2176        long new_p = ss.convert(legacy_state_file, Debug());
2177        // Set the fixed area in the store to point to this new structure
2178        MutableArea fixed_area = new_ss.getMutableArea(-1);
2179        fixed_area.putLong(new_p);
2180        fixed_area.checkOut();
2181        // Flush the changes to the new store and close
2182        storeSystem().closeStore(new_ss);
2183        // Delete the old state file.
2184        legacy_state_file.delete();
2185        out.println("State store written.");
2186      }
2187
2188      out.println("Opening conglomerate.");
2189      
2190      // Open the conglomerate
2191      conglomerate.open(getName());
2192
2193      // Check the state of the conglomerate,
2194      DatabaseConnection connection = createNewConnection(null, null);
2195      DatabaseQueryContext context = new DatabaseQueryContext(connection);
2196      connection.getLockingMechanism().setMode(LockingMechanism.EXCLUSIVE_MODE);
2197      if (!connection.tableExists(TableDataConglomerate.PERSISTENT_VAR_TABLE)) {
2198        out.println(
2199           "The sUSRDatabaseVars table doesn't exist.  This means the " +
2200           "database is pre-schema version 1 or the table has been deleted." +
2201           "If you are converting an old version of the database, please " +
2202           "convert the database using an older release.");
2203        return false;
2204      }
2205
2206      // Check the user given exists
2207      if (!userExists(context, admin_username)) {
2208        out.println(
2209           "The admin username given (" + admin_username +
2210           ") does not exist in this database so I am unable to convert the " +
2211           "database.");
2212        return false;
2213      }
2214      
2215      // What version is the data?
2216      DataTable database_vars =
2217               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
2218      Map   vars = database_vars.toMap();
2219      String   db_version = vars.get("database.version").toString();
2220      if (db_version.equals("1.0")) {
2221        // Convert from 1.0 to 1.4
2222        out.println("Version 1.0 found.");
2223        out.println("Converting database to version 1.4 schema...");
2224
2225        try {
2226          // Drop the tables that were deprecated
2227          connection.dropTable(new TableName(SYSTEM_SCHEMA, "sUSRPrivAdd"));
2228          connection.dropTable(new TableName(SYSTEM_SCHEMA, "sUSRPrivAlter"));
2229          connection.dropTable(new TableName(SYSTEM_SCHEMA, "sUSRPrivRead"));
2230        }
2231        catch (Error   e) { /* ignore */ }
2232
2233        // Reset the sequence id for the tables.
2234        conglomerate.resetAllSystemTableID();
2235        
2236        // Create/Update the conglomerate level tables.
2237        conglomerate.updateSystemTableSchema();
2238        
2239        // Commit the changes so far.
2240        connection.commit();
2241
2242        // Create/Update the system tables that are present in every
2243        // conglomerate.
2244        createSystemTables(connection);
2245
2246        // Commit the changes so far.
2247        connection.commit();
2248
2249        // Creating the system JDBC system schema
2250        connection.createSchema(JDBC_SCHEMA, "SYSTEM");
2251        // Create the system views
2252        createSystemViews(connection);
2253        
2254        // Sets the system grants for the administrator
2255        setSystemGrants(connection, admin_username);
2256        // Sets the table grants for the administrator
2257        convertPreGrant(connection, admin_username);
2258
2259        // Allow all localhost TCP connections.
2260        // NOTE: Permissive initial security!
2261        grantHostAccessToUser(context, admin_username, "TCP", "%");
2262        // Allow all Local connections (from within JVM).
2263        grantHostAccessToUser(context, admin_username, "Local", "%");
2264
2265        // Convert all tables in the database to the current table format.
2266        convertAllUserTables(connection, out);
2267
2268        // Move any large binary or string objects into the blob store.
2269        moveLargeObjectsToBlobStore(connection, out);
2270
2271        // Set all default system procedures.
2272        setupSystemFunctions(connection, admin_username);
2273        
2274        // Commit the changes so far.
2275        connection.commit();
2276
2277        // Update to version 1.4
2278        database_vars =
2279               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
2280        updateDatabaseVars(context, database_vars, "database.version", "1.4");
2281        db_version = "1.4";
2282
2283      }
2284      
2285      else if (db_version.equals("1.1")) {
2286        // Convert from 1.1 to 1.4
2287        out.println("Version 1.1 found.");
2288        out.println("Converting database to version 1.4 schema...");
2289
2290        // Reset the sequence id for the tables.
2291        conglomerate.resetAllSystemTableID();
2292        
2293        // Create/Update the conglomerate level tables.
2294        conglomerate.updateSystemTableSchema();
2295
2296        // Commit the changes so far.
2297        connection.commit();
2298        
2299        // Create/Update the system tables that are present in every
2300        // conglomerate.
2301        createSystemTables(connection);
2302
2303        // Commit the changes so far.
2304        connection.commit();
2305        // Update the 'database_vars' table.
2306        database_vars =
2307               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
2308
2309        // Creating the system JDBC system schema
2310        connection.createSchema(JDBC_SCHEMA, "SYSTEM");
2311        // Create the system views
2312        createSystemViews(connection);
2313        
2314        // Clear all grants.
2315        clearAllGrants(connection);
2316
2317        // Sets the system grants for the administrator
2318        setSystemGrants(connection, admin_username);
2319        // Sets the table grants for the administrator
2320        convertPreGrant(connection, admin_username);
2321
2322        // Convert all tables in the database to the current table format.
2323        convertAllUserTables(connection, out);
2324        
2325        // Move any large binary or string objects into the blob store.
2326        moveLargeObjectsToBlobStore(connection, out);
2327
2328        // Set all default system procedures.
2329        setupSystemFunctions(connection, admin_username);
2330
2331        // Commit the changes so far.
2332        connection.commit();
2333
2334        // Update to version 1.4
2335        database_vars =
2336               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
2337        updateDatabaseVars(context, database_vars, "database.version", "1.4");
2338        db_version = "1.4";
2339
2340      }
2341
2342      else if (db_version.equals("1.2")) {
2343        // Convert from 1.2 to 1.4
2344        out.println("Version 1.2 found.");
2345        out.println("Converting database to version 1.4 schema...");
2346
2347        // Create/Update the conglomerate level tables.
2348        conglomerate.updateSystemTableSchema();
2349
2350        // Commit the changes so far.
2351        connection.commit();
2352
2353        // Create/Update the system tables that are present in every
2354        // conglomerate.
2355        createSystemTables(connection);
2356
2357        // Commit the changes so far.
2358        connection.commit();
2359
2360        // Convert all tables in the database to the current table format.
2361        convertAllUserTables(connection, out);
2362
2363        // Move any large binary or string objects into the blob store.
2364        moveLargeObjectsToBlobStore(connection, out);
2365
2366        // Commit the changes so far.
2367        connection.commit();
2368
2369        // Set all default system procedures.
2370        setupSystemFunctions(connection, admin_username);
2371
2372        // Commit the changes so far.
2373        connection.commit();
2374
2375        // Update to version 1.4
2376        database_vars =
2377               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
2378        updateDatabaseVars(context, database_vars, "database.version", "1.4");
2379        db_version = "1.4";
2380
2381      }
2382      
2383      else if (db_version.equals("1.3")) {
2384        out.println("Version 1.3 found.");
2385        out.println("Converting database to version 1.4 schema...");
2386
2387        // Create/Update the conglomerate level tables.
2388        conglomerate.updateSystemTableSchema();
2389
2390        // Commit the changes so far.
2391        connection.commit();
2392
2393        // Create/Update the system tables that are present in every
2394        // conglomerate.
2395        createSystemTables(connection);
2396
2397        // Commit the changes so far.
2398        connection.commit();
2399        
2400        // Drop the 'sUSRSystemTrigger' table that was erroniously added in 1.3
2401        try {
2402          connection.dropTable(new TableName(SYSTEM_SCHEMA, "sUSRSystemTrigger"));
2403        }
2404        catch (Error   e) { /* ignore */ }
2405
2406        // Set all default system procedures.
2407        setupSystemFunctions(connection, admin_username);
2408        
2409        // Commit the changes so far.
2410        connection.commit();
2411
2412        // Update to version 1.4
2413        database_vars =
2414               connection.getTable(TableDataConglomerate.PERSISTENT_VAR_TABLE);
2415        updateDatabaseVars(context, database_vars, "database.version", "1.4");
2416        db_version = "1.4";
2417
2418      }
2419      
2420      else if (db_version.equals("1.4")) {
2421        out.println("Version 1.4 found.");
2422        out.println("Version of data files is current.");
2423      }
2424      
2425      else if (!db_version.equals("1.4")) {
2426        // This means older versions of the database will not support the data
2427        // format of newer versions.
2428        out.println("Version " + db_version + " found.");
2429        out.println("This is not a recognized version number and can not be " +
2430                    "converted.  Perhaps this is a future version?  I can " +
2431                    "not convert backwards from a future version.");
2432        return false;
2433      }
2434
2435      // Commit and close the connection.
2436      connection.commit();
2437      connection.getLockingMechanism().finishMode(
2438                                     LockingMechanism.EXCLUSIVE_MODE);
2439      connection.close();
2440      return true;
2441
2442    }
2443    catch (TransactionException e) {
2444      // This would be very strange error to receive for in initializing
2445      // database...
2446      out.println("Transaction Error: " + e.getMessage());
2447      e.printStackTrace(out);
2448      return false;
2449    }
2450    catch (DatabaseException e) {
2451      out.println("Database Error: " + e.getMessage());
2452      e.printStackTrace(out);
2453      return false;
2454    }
2455    
2456    finally {
2457      try {
2458        conglomerate.close();
2459      }
2460      catch (Throwable   e) {
2461        // ignore
2462      }
2463    }
2464    
2465  }
2466  
2467  // ---------- Server side procedures ----------
2468
2469  /**
2470   * Resolves a procedure name into a DBProcedure object.  This is used for
2471   * finding a server side script.  It throws a DatabaseException if the
2472   * procedure could not be resolved or there was an error retrieving it.
2473   * <p>
2474   * ISSUE: Move this to DatabaseSystem?
2475   */
2476  public DatabaseProcedure getDBProcedure(
2477               String   procedure_name, DatabaseConnection connection)
2478                                                     throws DatabaseException {
2479
2480    // The procedure we are getting.
2481    DatabaseProcedure procedure_instance;
2482
2483    // See if we can find the procedure as a .js (JavaScript) file in the
2484    // procedure resources.
2485    String   p = "/" + procedure_name.replace('.', '/');
2486    // If procedure doesn't starts with '/com/mckoi/procedure/' then add it
2487    // on here.
2488    if (!p.startsWith("/com/mckoi/procedure/")) {
2489      p = "/com/mckoi/procedure/" + p;
2490    }
2491    p = p + ".js";
2492
2493    // Is there a resource available?
2494    java.net.URL   url = getClass().getResource(p);
2495
2496    if (url != null) {
2497      // Create a server side procedure for the .js file
2498      //   ( This code is not included in the GPL release )
2499      procedure_instance = null;
2500
2501    }
2502    else {
2503      try {
2504        // Couldn't find the javascript script, so try and resolve as an
2505        // actual Java class file.
2506        // Find the procedure
2507        Class   proc = Class.forName("com.mckoi.procedure." + procedure_name);
2508        // Instantiate a new instance of the procedure
2509        procedure_instance = (DatabaseProcedure) proc.newInstance();
2510
2511        Debug().write(Lvl.INFORMATION, this,
2512                        "Getting raw Java class file: " + procedure_name);
2513      }
2514      catch (IllegalAccessException   e) {
2515        Debug().writeException(e);
2516        throw new DatabaseException("Illegal Access: " + e.getMessage());
2517      }
2518      catch (InstantiationException   e) {
2519        Debug().writeException(e);
2520        throw new DatabaseException("Instantiation Error: " + e.getMessage());
2521      }
2522      catch (ClassNotFoundException   e) {
2523        Debug().writeException(e);
2524        throw new DatabaseException("Class Not Found: " + e.getMessage());
2525      }
2526    }
2527
2528    // Return the procedure.
2529    return procedure_instance;
2530
2531  }
2532
2533  // ---------- System access ----------
2534
2535  /**
2536   * Returns the DatabaseSystem that this Database is from.
2537   */
2538  public final DatabaseSystem getSystem() {
2539    return system;
2540  }
2541
2542  /**
2543   * Returns the StoreSystem for this Database.
2544   */
2545  public final StoreSystem storeSystem() {
2546    return system.storeSystem();
2547  }
2548
2549  /**
2550   * Convenience static for accessing the global Stats object.  Perhaps this
2551   * should be deprecated?
2552   */
2553  public final Stats stats() {
2554    return getSystem().stats();
2555  }
2556
2557  /**
2558   * Returns the DebugLogger implementation from the DatabaseSystem.
2559   */
2560  public final DebugLogger Debug() {
2561    return getSystem().Debug();
2562  }
2563
2564  /**
2565   * Returns the system trigger manager.
2566   */
2567  public final TriggerManager getTriggerManager() {
2568    return trigger_manager;
2569  }
2570
2571  /**
2572   * Returns the system user manager.
2573   */
2574  public final UserManager getUserManager() {
2575    return getSystem().getUserManager();
2576  }
2577
2578  /**
2579   * Creates an event for the database dispatcher.
2580   */
2581  public final Object   createEvent(Runnable   runner) {
2582    return getSystem().createEvent(runner);
2583  }
2584
2585  /**
2586   * Posts an event on the database dispatcher.
2587   */
2588  public final void postEvent(int time, Object   event) {
2589    getSystem().postEvent(time, event);
2590  }
2591
2592  /**
2593   * Returns the system DataCellCache.
2594   */
2595  public final DataCellCache getDataCellCache() {
2596    return getSystem().getDataCellCache();
2597  }
2598
2599  /**
2600   * Returns true if the database has shut down.
2601   */
2602  public final boolean hasShutDown() {
2603    return getSystem().hasShutDown();
2604  }
2605
2606  /**
2607   * Starts the shutdown thread which should contain delegates that shut the
2608   * database and all its resources down.  This method returns immediately.
2609   */
2610  public final void startShutDownThread() {
2611    getSystem().startShutDownThread();
2612  }
2613
2614  /**
2615   * Blocks until the database has shut down.
2616   */
2617  public final void waitUntilShutdown() {
2618    getSystem().waitUntilShutdown();
2619  }
2620
2621  /**
2622   * Executes database functions from the 'run' method of the given runnable
2623   * instance on the first available worker thread.  All database functions
2624   * must go through a worker thread.  If we ensure this, we can easily stop
2625   * all database functions from executing if need be.  Also, we only need to
2626   * have a certain number of threads active at any one time rather than a
2627   * unique thread for each connection.
2628   */
2629  public final void execute(User user, DatabaseConnection database,
2630                            Runnable   runner) {
2631    getSystem().execute(user, database, runner);
2632  }
2633
2634  /**
2635   * Registers the delegate that is executed when the shutdown thread is
2636   * activated.
2637   */
2638  public final void registerShutDownDelegate(Runnable   delegate) {
2639    getSystem().registerShutDownDelegate(delegate);
2640  }
2641
2642  /**
2643   * Controls whether the database is allowed to execute commands or not.  If
2644   * this is set to true, then calls to 'execute' will be executed
2645   * as soon as there is a free worker thread available.  Otherwise no
2646   * commands are executed until this is enabled.
2647   */
2648  public final void setIsExecutingCommands(boolean status) {
2649    getSystem().setIsExecutingCommands(status);
2650  }
2651
2652  /**
2653   * Returns a static table that has a single row but no columns.  This table
2654   * is useful for certain database operations.
2655   */
2656  public final Table getSingleRowTable() {
2657    return SINGLE_ROW_TABLE;
2658  }
2659
2660
2661  // ---------- Static methods ----------
2662
2663  /**
2664   * Given the sUSRDatabaseVars table, this will update the given key with
2665   * the given value in the table in the current transaction.
2666   */
2667  private static void updateDatabaseVars(QueryContext context,
2668                   DataTable database_vars, String   key, String   value)
2669                                                   throws DatabaseException {
2670    // The references to the first and second column (key/value)
2671    Variable c1 = database_vars.getResolvedVariable(0); // First column
2672    Variable c2 = database_vars.getResolvedVariable(1); // Second column
2673
2674    // Assignment: second column = value
2675    Assignment assignment = new Assignment(c2,
2676                                    new Expression(TObject.stringVal(value)));
2677    // All rows from database_vars where first column = the key
2678    Table t1 = database_vars.simpleSelect(context, c1, Operator.get("="),
2679                                    new Expression(TObject.stringVal(key)));
2680
2681    // Update the variable
2682    database_vars.update(context, t1, new Assignment[] { assignment }, -1);
2683
2684  }
2685
2686
2687  public void finalize() throws Throwable   {
2688    super.finalize();
2689    if (isInitialized()) {
2690      System.err.println("Database object was finalized and is initialized!");
2691    }
2692  }
2693
2694}
2695
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags