ORACLE INTERNALS

So, a few people have asked me what NEXTGRES Gateway is.  My short answer, the ultimate database compatibility server.

Sorry if this blog entry sounds very marketing-oriented, but I’ve been working on this personal project non-stop for the last 8 months and am really excited about it.

NEXTGRES Gateway in a nutshell:

• Designed to assist in database/application migration
• Written in C
• Supports TCP and UDP sockets
• Runs in multi-threaded or multi-process mode (depending on configuration)
• Runs on Windows, UNIX, Linux
• Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) server-side wire-level protocols
• Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) client-side wire-level protocols, as well as ODBC
• Supports pass-through SQL or syntax translation between MySQL, PostgreSQL, Oracle, and SQL Server
• Supports native procedural language execution via translation of PL/SQL and T-SQL to C (no C complier is needed on your system though, thanks to TCC)
• Supports data type conversion to/from MySQL, PostgreSQL, Oracle, and SQL Server/Sybase
• Supports local statement/result-set caching

In this entry, I’m going to focus on MySQL server emulation.

MySQL Server Emulation
Now that I’ve fully completed the MySQL server emulation component, something I discussed with the Oracle Data Access guys at OpenWorld, here’s a couple examples for you.

Say you have an application that runs on MySQL and you’d like to migrate it to Postgres, but don’t want to do any code changes.  Well, if you’re using fairly standard ODBC/JDBC, you don’t have much to worry about.  But what if it’s a PHP application using the mysql_* calls, or an application using the MySQL client libraries, or a third-party application you don’t have the code for?  The answer is to use NEXTGRES Gateway.

NEXTGRES Gateway allows you migrate your data to another database transparently to the application.  The general process for using NEXTGRES Gateway is as follows:

• Migrate Data (MySQL to Postgres/Oracle/SQL Server)
• Shutdown MySQL
• Start NEXTGRES Gateway in MySQL Emulation mode and point it to the Postgres/Oracle/SQL Server data
• Test
• You’re done!

Unlike other databases which claim to be compatible, NEXTGRES Gateway allows you to migrate an application to another database server with no application changes.

In the following example, I’m using the native MySQL client to connect to a PostgreSQL 8.3 database.  It’s important to note that no changes have been made to the MySQL client, it’s just connecting to NEXTGRES Gateway which is emulating the MySQL server by performing SQL syntax and protocol translation to Postgres.

jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 pgdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from pg_tables;
+-------+
| count |
+-------+
| 51    |
+-------+
1 row in set (0.01 sec)

mysql> select tablename from pg_tables limit 5;
+-------------------------+
| tablename               |
+-------------------------+
| sql_features            |
| sql_implementation_info |
| pg_statistic            |
| sql_languages           |
| sql_packages            |
+-------------------------+
5 rows in set (0.01 sec)

That’s cool and all, but say we want to move our MySQL application to Oracle:

jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 oradb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from all_tables;
+----------+
| COUNT(*) |
+----------+
| 108      |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from user_tables;
+----------+
| COUNT(*) |
+----------+
| 9        |
+----------+
1 row in set (0.01 sec)

mysql> select user from dual;
+-----------+
| USER      |
+-----------+
| AUTOGRAPH |
+-----------+
1 row in set (0.01 sec)

mysql> select table_name from all_tables limit 5;
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| DUAL                  |
| SYSTEM_PRIVILEGE_MAP  |
| TABLE_PRIVILEGE_MAP   |
| STMT_AUDIT_OPTION_MAP |
| AUDIT_ACTIONS         |
+-----------------------+
5 rows in set (0.02 sec)

For those that didn’t catch it, NEXTGRES Gateway performed a simple SQL translation from MySQL to Oracle syntax on:

SELECT table_name FROM all_tables LIMIT 5;

to

SELECT table_name FROM all_tables WHERE ROWNUM < 6;

If you want to see more, I’ll be happy to demonstrate MySQL, Postgres, and Oracle emulation at the Southeastern Oracle Users Conference, February 24 & 25 in Charlotte, North Carolina.  I’ll also be presenting my session, “Listening In: Passive Capture and Analysis of Oracle Network Traffic”.  This session is designed to help you diagnose issues with and optimize applications for, the Oracle network protocol.

Added on 2010-01-30 per Baron’s question:

jharris@jharris-desktop$ mysql -A -u testuser1 -h 127.0.0.1 postgres
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+------------------------------------------------------+
| Tables_in_postgres                                   |
+------------------------------------------------------+
| active_locks                                         |
| active_queries                                       |
| information_schema.administrable_role_authorizations |
| information_schema.applicable_roles                  |
| information_schema.attributes                        |
| information_schema.check_constraint_routine_usage    |
| information_schema.check_constraints                 |
| information_schema.column_domain_usage               |
| information_schema.column_privileges                 |
| information_schema.column_udt_usage                  |
| information_schema.columns                           |
| information_schema.constraint_column_usage           |
| information_schema.constraint_table_usage            |
| information_schema.data_type_privileges              |
| information_schema.domain_constraints                |
| information_schema.domain_udt_usage                  |
| information_schema.domains                           |
| information_schema.element_types                     |
| information_schema.enabled_roles                     |
| information_schema.information_schema_catalog_name   |
| information_schema.key_column_usage                  |
| information_schema.parameters                        |
| information_schema.referential_constraints           |
| information_schema.role_column_grants                |
| information_schema.role_routine_grants               |
| information_schema.role_table_grants                 |
| information_schema.role_usage_grants                 |
| information_schema.routine_privileges                |
| information_schema.routines                          |
| information_schema.schemata                          |
| information_schema.sequences                         |
| information_schema.sql_features                      |
| information_schema.sql_implementation_info           |
| information_schema.sql_languages                     |
| information_schema.sql_packages                      |
| information_schema.sql_parts                         |
| information_schema.sql_sizing                        |
| information_schema.sql_sizing_profiles               |
| information_schema.table_constraints                 |
| information_schema.table_privileges                  |
| information_schema.tables                            |
| information_schema.triggered_update_columns          |
| information_schema.triggers                          |
| information_schema.usage_privileges                  |
| information_schema.view_column_usage                 |
| information_schema.view_routine_usage                |
| information_schema.view_table_usage                  |
| information_schema.views                             |
| jhhdemotbl                                           |
| jhhtest                                              |
| pg_aggregate                                         |
| pg_am                                                |
| pg_amop                                              |
| pg_amproc                                            |
| pg_attrdef                                           |
| pg_attribute                                         |
| pg_auth_members                                      |
| pg_authid                                            |
| pg_autovacuum                                        |
| pg_cast                                              |
| pg_class                                             |
| pg_constraint                                        |
| pg_conversion                                        |
| pg_cursors                                           |
| pg_database                                          |
| pg_depend                                            |
| pg_description                                       |
| pg_enum                                              |
| pg_freespacemap_pages                                |
| pg_freespacemap_relations                            |
| pg_group                                             |
| pg_index                                             |
| pg_indexes                                           |
| pg_inherits                                          |
| pg_language                                          |
| pg_largeobject                                       |
| pg_listener                                          |
| pg_locks                                             |
| pg_namespace                                         |
| pg_opclass                                           |
| pg_operator                                          |
| pg_opfamily                                          |
| pg_pltemplate                                        |
| pg_prepared_statements                               |
| pg_prepared_xacts                                    |
| pg_proc                                              |
| pg_rewrite                                           |
| pg_roles                                             |
| pg_rules                                             |
| pg_settings                                          |
| pg_shadow                                            |
| pg_shdepend                                          |
| pg_shdescription                                     |
| pg_stat_activity                                     |
| pg_stat_all_indexes                                  |
| pg_stat_all_tables                                   |
| pg_stat_bgwriter                                     |
| pg_stat_database                                     |
| pg_stat_sys_indexes                                  |
| pg_stat_sys_tables                                   |
| pg_stat_user_indexes                                 |
| pg_stat_user_tables                                  |
| pg_statio_all_indexes                                |
| pg_statio_all_sequences                              |
| pg_statio_all_tables                                 |
| pg_statio_sys_indexes                                |
| pg_statio_sys_sequences                              |
| pg_statio_sys_tables                                 |
| pg_statio_user_indexes                               |
| pg_statio_user_sequences                             |
| pg_statio_user_tables                                |
| pg_statistic                                         |
| pg_stats                                             |
| pg_tables                                            |
| pg_tablespace                                        |
| pg_timezone_abbrevs                                  |
| pg_timezone_names                                    |
| pg_trigger                                           |
| pg_ts_config                                         |
| pg_ts_config_map                                     |
| pg_ts_dict                                           |
| pg_ts_parser                                         |
| pg_ts_template                                       |
| pg_type                                              |
| pg_user                                              |
| pg_views                                             |
| plproxy.cluster_partitions                           |
| plproxy.clusters                                     |
| testtbl                                              |
| utility.index_byte_sizes                             |
| utility.table_byte_sizes                             |
| utility.user_table_sizes                             |
+------------------------------------------------------+
132 rows in set (0.03 sec)

mysql> show databases;
+-----------+
| Database  |
+-----------+
| template1 |
| template0 |
| postgres  |
+-----------+
3 rows in set (0.01 sec)

All,

It’s been quite awhile since I last wrote a blog entry. For that, I apologize. I’ve been extremely busy at work and haven’t been able to spend much time on Oracle stuff lately. Regardless, this is what I’ve been up to and what you can expect to see soon.

SibylNet & Unofficial Specification of the Oracle Network Protocol

Oracle has given me permission to proceed with releasing my open source client software for Oracle, SibylNet. As such, I’ve been combining all of my past research into a single protocol specification and client library which I hope to release in Q1 2009.

Oracle vs. Postgres Benchmark

To end a long-running dispute I’ve had with the Postgres community regarding over-reliance on the operating system to achieve good database performance, I’ve performed a comparison benchmark between Oracle8i Standard Edition (circa 1999) and Postgres 8.3/8.4-dev (the latest version) on the exact same hardware. The results may or may not surprise you.

A Fast Password Cracker for Oracle

Years ago I had written a password cracker for Oracle8i/9i, but had given up on it. Though, after playing with Laszlo Toth’s woraauthbf awhile back, I decided to update mine to take full advantage of newer multi-core systems. After a bit of redesign, it now makes use of multi-threaded parallel processing, lock-free/nearly-wait-free cache-optimized hash tables, and atomic locking. I now believe I have the world’s fastest password cracker for Oracle… but I’ll leave that to others to test. I’m currently getting this re-ported to Windows and after letting a few select people test it, will release it as open source.

A three-part series of articles pertaining to database frontends for custom storage engines

In today’s business environment, most companies prefer to buy software rather than build it. However, over the past year and a half, I’ve been approached by three different companies looking for a good database frontend with which they could integrate their own internally-developed storage engines. As each of these companies ran into several of the same issues, I thought that this topic would make for a good series of articles not only for those generally interested in databases, but specifically those who may find themselves in the same situation. The three articles (and products mentioned) are as follows:

• Just What the Doctor Ordered (Dr. DeeBee Driver Kit)
• Swimming with the Dolphin (MySQL)
• An Elephant in the Room (Postgres)