Hi all, I've been thinking about a feature request that might be of use for us lowly non-Firebird developers, hopefully in the 3.0 timeframe ;)
======================================================================== What: automatically keep track of databases once accessed on a server and present that in a system view/table. ======================================================================== Why: developers/end users can more easily choose and connect to a database on a Firebird server, even point and click. Hopefully better SQL standard conformance. See pros below. ======================================================================== Pros: - Ease of use for end users: -- Their applications can be modified to provide point and click GUI connection options: avoids typos, extra documentation, looking up aliases, and bothering IT staff. - Ease of use for developers: -- In house apps that keep track of databases can be converted -- Firebird.Net driver can add schema support, e.g. for support of the SharpDevelop database plugin and better support for Visual Studio. -- As mentioned above, developers can use adapted drivers in GUI/RAD environments to connect to a database by visually selecting it. Avoids typos, extra documentation, and looking up aliases. - Easily usable from applications even without driver support (just query RDB$DATABASES if you already have a valid connection, e.g. to employee.fdb) - Hopefully better conformance with SQL standard; if nothing else, good from a PR/marketing perspective Cons: - Increased code complexity/maintenance - Security issue: leakage of information on databases present on system. - Security issue: denial of service attacks with valid credentials by bruteforcing database aliases has increased impact (due to more code executing). Remediation: turn of DatabaseAutoRegistration in fb.conf; needs to be documented in manual/release notes ======================================================================== Assumptions/requirements: - Firebird process has write access to firebird.conf. Should be documented in manual/release notes. See below for troubleshooting. Phase 1: must have for this feature to be usable: 1.1 Parameter in firebird.conf called something like AutoRegistration, default false, to control this feature. 1.2 If Firebird process has no write access to firebird.conf, log error in regular error log. 1.3 System view/table like RDB$DATABASES that returns the list of alias entries (not the actual file path). This allows devs/end users to query this to get an overview of databases on the server. 1.4 If a DROP DATABASE command is committed, delete the entry in aliases.conf and RDB$DATABASES. For performance reasons, maybe don't delete the aliases.conf entry and rely on auto clean up (see below) 1.5 If a CREATE DATABASE command is committed, add the entry to aliases.conf and RDB$DATABASES. Phase 2: should have for this feature to be easily usable: 2.1 Auto clean up of databases that have been deleted: also covers OS level deletion of files if server is not running: 2.1.1 When a connection attempt to a database failed, set a boolean field RDB$DATABASES.ACCESSFAILED, and the date it first as RDB$DATABASES.ACCESSFAILEDDATE 2.1.2 On succesful connection, clear those fields 2.1.3 Have firebird.conf parameter AutoRegistrationAutoDeleteDelay or similar; values in days, hours or minutes. If set to 0, don't use this feature. Suggest default 7 days to allow dba's time to fix access problems. 2.1.4 Whenever connection attempt is made again, if RDB$DATABASES.ACCESSFAILED is true, check if time interval exceeds AutoRegistrationAutoDeleteDelay, and delete row from RDB$DATABASES. Phase 3: nice to have: 3.1 Not only record database name from aliases, but also things the engine reads on connection (e.g. page size, charset,...), possibly file location (possibly only accessible to server SYSDBA for security) 3.2 Only return those databases that current (end) user that connects to the db has open (or connect or whatever it's called) privileges for. ======================================================================== Links to possibly relevant issues in tracker: - CORE-737 SQL INFORMATION_SCHEMA support; only references SQL standard. - CORE-738 SQL Schemas support. Not very much info. - CORE-1422 aliases.conf registration feature request (subset of this one) See also: description of way to get database info, reference to SQL2003 INFORMATION_SCHEMA: http://www.alberton.info/firebird_sql_meta_info.html ======================================================================== Suggestions for things to do/check: - Verify SQL standard regarding schemata for the items to return in RDB$DATABASES. I don't have the standard, so I can only infer what's in it from websites - Verify with driver developers (.Net, JDBC, ODBC, Python, ...) what they'd like to see supported. I suppose this thread covers that... - Look at competition/open source colleagues on what is in their solution (Interbase, PostgreSQL, Oracle, MS SQL Server) and align with them as much as possible - Contact Embarcadero to notify of change, ask they do the same: win win situation as tools that support both Firebird and Interbase will likely more quickly implement this if both servers support (at least a subset of) the same functionality. ======================================================================== I'd like your feedback on this idea before creating a tracker item. thanks, -- Regards, jb ------------------------------------------------------------------------------ Forrester Wave Report - Recovery time is now measured in hours and minutes not days. Key insights are discussed in the 2010 Forrester Wave Report as part of an in-depth evaluation of disaster recovery service providers. Forrester found the best-in-class provider in terms of services and vision. Read this report now! http://p.sf.net/sfu/ibm-webcastpromo Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
