Dirt Cheap Oracle step #5(b) of 6: Making Apache and EPG Play Together

[Previous]

I want people to get to both my Apache web server (port 8000) as well as Apex (port 8080) via the default HTTP port (80) through the router, but you can’t map one port to two ports – otherwise, how is the router to know which port to pass requests on to? There’s a good reason why I’ve chosen the web server to get all the traffic, and that’s because I have an idea how to get Apache to pass traffic to APEX, but not how the other way around could be done (EPG pass traffic to Apache).

This was a bit of a conundrum, and I was forced to do some spelunking in the documentation for Apache web server configuration. Reading these docs is highly recommended, it’s a lot of fun although at first it’s hard to know where to start. I’d recommend starting with the sections on Configuration Files and Configuration Sections, with a bit of browsing through the various commands, and then maybe the URL Rewriting Guide.

It wasn’t pretty; in the end it came down to one of two options:
(a) Redirect
(b) Rewrite

I can’t use redirection because whenever someone goes to my web page, redirection means Apache will send back a message saying “wrong address, please try this new address” which would include port 8080, which for at least one friend of mine would be blocked.

So I tried URL rewriting, adding the following lines into my /etc/httpd/conf/httpd.conf:

<Location /apex>
    RewriteEngine On
    RewriteRule (.*) http://%{SERVER_NAME}:8080%{REQUEST_URI} [last]
    Order deny,allow
    Deny from none
    Allow from all
</Location>

(BTW if you’re wondering, the third line starting with “RewriteRule” is one long line, “{REQUEST_URI}…” should appear directly after “…8080%” without any line break)

This means: for any URL requesting /apex or anything under that folder, rewrite the request to go to the same server and address, but on port 8080.

This would have worked, except that according to the documentation, if the rewrite engine changes the host or port of the url, it will actually do a Redirect! So rewriting is no better than redirection for this case.

In the end, I found a third option:
(c) ProxyPass

I added the following lines into httpd.conf:

ProxyRequests Off
ProxyPass /apex http://localhost:8080/apex
ProxyPassReverse / http://localhost:8080
ProxyReceiveBufferSize 49152

(the above assumes that localhost is mapped in my hosts file)

This solved the problem for me. What the lines above mean is, any request for /apex or anything underneath it is internally passed on to http://localhost:8080/apex which will act like a proxy server for the request. This operation is transparent to the client, which doesn’t know that anything’s changed. I believe ProxyPassReverse is also needed so that requests going back from port 8080 is routed in the reverse direction; not entirely sure how that works but at least it works.

Now, that works, but when I access APEX pages via port 8000 (80 from the outside), I get all the text but no images. That’s because APEX was serving images via /i/ on port 8080 (via EPG), but now we have this proxy thing only passing requests for /apex/* to it, and Apache web server can’t find /i/.

At first I tried adding another ProxyPass line for /i to port 8080, and this did work; however, all the pages loaded very slowly, all the images were visibly taking time to load each time a page was refreshed, so no images were being cached. I figured this lack of caching was because Apache can’t tell that an image hasn’t been changed since the last time it was requested.

This performance problem was solved by getting all the Apex images and putting them on another folder on the Linux box (e.g. /usr/lib/oracle/apex/images) and adding the following line to httpd.conf:

Alias /i/ "/usr/lib/oracle/apex/images/"

This means that all the APEX images are now served by Apache web server instead of by Apex. It also means that application images I’ve loaded have to be copied out to the file system, but in my case there weren’t many and it didn’t take long.

It’s all very simple, isn’t it? Not. But at least it works, and reasonably well. My friends and family are suitably impressed, although they have no inkling of the trouble I went to to get it all working. Ah, the thankless life of an Oracle programmer – but it’s all worth it in the end when I can do full-on development remotely, wherever in the world I happen to be (e.g. down the road at a friend’s place).

Next problem: world peace. Or, migrating my existing APEX applications from the XP box to Linux. Depending on how much time I have.

[Next]


Dirt Cheap Oracle step #5(a) of 6: Port Forwarding & DDNS

[Previous]

Disclaimer: don’t blame me if you follow any of the instructions here and get yourself into a right mess – think of me as a helpful but aloof guide who occasionally gets his words mixed up 🙂

I can access Apex from the Linux box, but not from my WinXP machine across the network. This is because the firewall in Linux by default blocks most ports. I’ve got APEX (using EPG) listening on port 8080, and Apache web server (for the static web pages and images) listening on port 8000, so I open up those two ports (System menu -> Administration -> Security Level & Firewall).

The router, by default, assigns IP addresses dynamically via DHCP to each device that connects to it; in order that I don’t have to keep logging into the router to see what today’s IP address is, I need to tell the router to reserve an IP address for the Linux box. To do that I log into the router’s administration page (for mine it’s http://10.1.1.1), and examine the DHCP settings page. There I find that two devices are connected, each with a unique MAC address, and with the IP address currently assigned to them. One is the WinXP machine, the other is the Linux box. I happen to know which one is which, but I suppose if I didn’t know I could have just disconnected one of them and seen which one disappeared from the router. Anyway, I copy the MAC address and tell the router to assign it a static IP address (in my case, 10.1.1.3) (a word of advice: on some routers you have to also change the settings that specify the range of dynamic IP addresses that can be assigned by DHCP, so that they don’t conflict with the static IP addresses; in my case, my router does not work that way).

By the way, if my explanation of this doesn’t help you, just google “static IP address” – you’ll find heaps of guides around – keep reading, and it’ll all make sense…

Now I can access it from WinXP via http://10.1.1.3. For convenience I add a line like the following to C:\windows\system32\drivers\etc\hosts:

10.1.1.3        linuxpc        linuxpc

This means I can access it via http://linuxpc, at least from WinXP. The equivalent file on linux, used in exactly the same way, is /etc/hosts [Wikipedia: Hosts file].

What I want now is for friends and family to get to it from the outside. For security reasons there’s a number of things blocking that access which I need to take care of. Also, I want the URL that I give out to be relatively simple; a longish URL with strange numbers and characters can be intimidating and easy to get wrong. Also, at least one of my friends works at a place that seems to block different ports (e.g. 8000). So I want them to get in on port 80, which is the default for HTTP and doesn’t need to be specified in their browser.

At the moment the router ignores mosts requests from the outside world; it only exists to serve the little network connected locally to it and couldn’t care less about my friends and family. I need to make it just a little more friendly. First thing, I need to get a port mapped. In my router admin I navigate to the Port Forwarding (called “Virtual Server” on my router; on your router it might appear in “Network Address Translation (NAT/NAPT/PAT)” or “DMZ host”) section, and select the LAN IP I wish to map the port to (in my case, 10.1.1.3). This router gives me a whole lot of preset ports for various games and applications, but I want to do something different, so I select “User” and click “Add”. Here is where I can create a Rule for mapping ports. I want the router to accept TCP traffic on port 80 (the default for web stuff) but send it to port 8000 on the Linux box. So I give it a name (e.g. something imaginative like “Port80to8000”), select TCP for the Protocol, put in 80 for both Port Start and Port End, and pop in 8000 for the Port Map. I don’t need a range so Port Map End stays blank, and click Apply. Now, I just Add my new rule (Port80to8000) to the list of rules for 10.1.1.3, click Apply, and reboot the router. Now, traffic coming in from the outside on port 80 should be sent to port 8000 on the Linux box.

We’re not out of the woods yet, though, there’s a few more things blocking external access. Firstly, iiNet by default block any incoming traffic on port 80, as well as a few other ports, which is good from a security point of view, but doesn’t help me much, so I go into my account management page and switch this option off.

(Once that’s all done, I can test that external access to the port is working, by logging into the machine I want to test, and plugging 80 into this online tool: CanYouSeeMe.org – Open Port Check Tool.)

Finally, and this is the biggy, to get to my site from the outside, people need a URL; or at least, an IP address they can use (that won’t change from day to day). Now, I don’t have a domain name, nor do I have a static IP address – all I get is a dynamic (i.e. can change without notice) IP address assigned by iiNet. I could upgrade to a business broadband account with a static IP address along with a nice domain name, but I want to do this on the cheap, so I don’t.

Instead, I use DDNS (Dynamic DNS). I learned about this from an excellent article by Nathan Taylor in PC User magazine (May 2007). There’s a few sites out there that do this, the one I use is www.no-ip.com which provides a basic service for free. I sign up, get a domain name of my own (I won’t tell you what it is but it looks like mycomputer.no-ip.info), and plug in the settings into the DDNS page on my router.

The way this works is: whenever someone types in my domain name (e.g. http://mycomputer.no-ip.info) into their web browser, the request goes to no-ip.com. Their computer looks up their database for the current IP address for “mycomputer.no-ip.info”, and then forwards the request on to it. This process is pretty much seamless and without any overhead that I’ve noticed. Whenever iiNet change my IP address, my router sends a message back to no-ip.com with the new IP address and their database is updated so that future requests are passed on correctly.

If your router doesn’t support DDNS, don’t worry – you can get software that will run on a computer on your network, and it will do the same job – whenever the IP address changes, it will notify no-ip.com of the change.

After all that fun, I tried to test it from my WinXP box, but I just couldn’t get it to work. As it turns out, you just can’t access the local network using the external address from within that network – apart from using www.canyouseeme.org to check the port, you have to test your web site from outside your network. So I used my “phone a friend” lifeline and got him to try it out.

You guessed it, it worked first time. Ha! Well,… if I said that I’d be lying. It didn’t work first time, the actual process of working all of the above out was a little bit more bumpy than I’ve made out; but in the end it was all working, and I learned a great deal in the process.

There’s one more thing (oh no, I hear you moan…). You’ll notice I’ve only mapped port 80 to port 8000, which is the Apache web server, and not port 8080, which is APEX. That’s the subject of tomorrow’s post, so stay tuned!

[Next]


Dirt Cheap Oracle steps #3 & #4 of 6: Install Oracle XE and APEX 3.0

[Previous]

Ok kids, play time is over, it’s time to install Oracle, upgrade Apex and then turn out the lights for a good night’s sleep. After double-checking that they haven’t released 11g XE yet :(, I download Oracle XE 10gR2, as well as Apex 3.0 for good measure (no Apex 4.0 yet either…).

Download Oracle XE 10g Release 2 (10.2.0.1) for Linux x86. I chose the RPM version.

Download Oracle Application Express. I chose 3.0.1.

Did some reading, and learned a lot about configuring Linux, but in the end it was a frightfully simple matter to install the RPM using the RPM [Wikipedia:RPM] command (who would have guessed), it installed without a hitch and told me to run the configure command, which I did, and that went fine as well. So now I’ve got Oracle running.

Apex as installed along with Oracle XE works a bit differently to what I’m used to (I’m running 3.0 on Oracle 10GR2 Enterprise Edition on my WinXP box for experimental purposes), it serves as the administration GUI to the database (no Enterprise Manager, unfortunately) as well as the application development environment. The database (Oracle XE), being free, is limited to 4GB of user data, and will only use 1GB of RAM (which is fine, that’s all I’m giving it anyway). To help ensure I don’t go over 4GB I’ll keep my large media files on the file system instead of in the database, and I’ll design my applications with automated cleanup routines for tables that grow a lot. So far my applications on my experimental 10G database only use 1GB, and that’s including a whole lot of BLOBs, so I don’t think I’ll have a problem with this limit if I’m careful.

It uses the Embedded PL/SQL Gateway (EPG) [docs] for its web server. Nothing to report there, it worked fine right out of the box.

Next step is to upgrade to APEX 3.0 which means I lose the cute APEX database administration interface, but I have to do this because the apps I want to migrate are currently on 3.0. Not to worry, there’s a good guide for administrating Oracle XE and APEX 3.0.1 here. After upgrading I disable the SYSTEM login for APEX, and use the ADMIN account like I’m accustomed to.

After a bit of tyre-kicking I’m off to bed.

[Next]


Dirt Cheap Oracle

Seeing how Oracle XE and Linux is free, I want to see for just a minimum of outlay I could get a working database and web server running. I am also keen to give Linux a try; I’ve been a Windows user ever since my dad upgraded his computer from MS-DOS 3.3 to Windows 3.1, and while I’ve always worked for companies that used Unix I’ve never really had to learn much about it.

My goal is to get Linux up and running on the cheapest hardware I could find (free, if possible), and to run Oracle XE, the free version of the Oracle database, which includes Application Express. On the same machine I want to set up a web server to serve static web pages and streaming media.

My plan is as follows:

  1. Obtain a barebones machine to be the server. Must be free, or dirt cheap.
  2. Install Oracle Enterprise Linux on it.
  3. Install Oracle XE 10g R2.
  4. Upgrade APEX to version 3.0.
  5. Set up the router for external access.
  6. Migrate all Apex applications from the Win XP box.

In the next few posts I’ll describe how I went on all six points.

[Next]


Which APEX am I logging into?

I’ve got two computers at home both running APEX, and when I log into APEX the page looks exactly the same – except for the IP address in the URL, which is just slightly different. If you’re forgetful like me it’s easy to confuse which machine I’m logged in to. Here’s a tip:

Login to apex_admin on each machine in turn and click Manage Service, then Messages. Then select a Custom Message for the Login Message and/or a System Message, and enter the name of the machine, or some other message that will remind you which machine it is. The message will be shown whenever you see the login screen, and when you login to APEX.

If you find the text isn’t visible enough, you can insert HTML into the message, e.g. <B>Development</B>.


APEX 3.0 via Embedded PL/SQL Gateway

I managed to get APEX 3.0 working on Oracle 10.2.0.1 using the embedded PL/SQL gateway (i.e. without installing Apache), contrary to the advice given here. I used apex_epg_config instead of apex_epg_config11. For it to work, however, I had to remove some security on XDB so it’s not a recommended option. This involved modifying my xdbconfig.xml to allow anonymous access to XDB using this:

DECLARE
  configxml SYS.XMLType;
BEGIN
  SELECT INSERTCHILDXML(xdburitype('/xdbconfig.xml').getXML(),
         '/xdbconfig/sysconfig/protocolconfig/httpconfig',
         'allow-repository-anonymous-access',
         XMLType('true'),
         'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
  INTO configxml FROM DUAL;
  DBMS_XDB.cfg_update(configxml);
END;
/

Now for some general tips:

Tip #1. List Your DAD Authorizations

As described so well in the excellent article DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database Release 2, you can authorize and deauthorize a DAD using the DBMS_EPG package. It should be noted that dropping a DAD will not remove the authorizations as well, so you need to deauthorize it as well. To find out what authorizations have been made on your database, you can query the DBA_EPG_DAD_AUTHORIZATION view, e.g.:

SQL> select * from dba_epg_dad_authorization;

DAD_NAME USERNAME
-------- ---------
APEX     ANONYMOUS

1 row selected.

There’s also a USER_EPG_DAD_AUTHORIZATION view which shows the DAD_NAME for authorized DADs for the current user.


Tip #2. List Your DADS

You can find out what DADs have been set up on your instance using the DBMS_EPG package. There is a bug, however, that causes dbms_epg.get_all_dad_attributes to return nothing, so this script gets each attribute separately with dbms_epg.get_dad_attribute. The script lists all the DADS, their mappings and their attributes.

set serveroutput on

PROMPT list all dads
declare
  blank       dbms_epg.varchar2_table;
  dad_names   dbms_epg.varchar2_table;
  paths       dbms_epg.varchar2_table;
  procedure show_dad_attribute(dad in varchar2, attr in varchar2) is
    val varchar2(4000);
  begin
    val := dbms_epg.get_dad_attribute(dad, attr);
    if val is not null then
      dbms_output.put_line('... ' || attr || '=' || val);
    end if;
  end;
begin
  dbms_epg.get_dad_list(dad_names);
  if dad_names.count > 0 then
    for i in dad_names.first..dad_names.last loop
      dbms_output.put_line(dad_names(i));
      paths := blank;
      dbms_epg.get_all_dad_mappings(dad_names(i), paths);
      if paths.count > 0 then
        for j in paths.first..paths.last loop
          dbms_output.put_line('... path=' || paths(j));
        end loop;
      else
        dbms_output.put_line('... No mappings found');
      end if;
      show_dad_attribute(dad_names(i),'after-procedure');
      show_dad_attribute(dad_names(i),'always-describe-procedure');
      show_dad_attribute(dad_names(i),'authentication-mode');
      show_dad_attribute(dad_names(i),'before-procedure');
      show_dad_attribute(dad_names(i),'bind-bucket-lengths');
      show_dad_attribute(dad_names(i),'bind-bucket-widths');
      show_dad_attribute(dad_names(i),'cgi-environment-list');
      show_dad_attribute(dad_names(i),'compatibility-mode');
      show_dad_attribute(dad_names(i),'database-username');
      show_dad_attribute(dad_names(i),'default-page');
      show_dad_attribute(dad_names(i),'document-path');
      show_dad_attribute(dad_names(i),'document-procedure');
      show_dad_attribute(dad_names(i),'document-table-name');
      show_dad_attribute(dad_names(i),'error-style');
      show_dad_attribute(dad_names(i),'exclusion-list');
      show_dad_attribute(dad_names(i),'fetch-buffer-size');
      show_dad_attribute(dad_names(i),'input-filter-enable');
      show_dad_attribute(dad_names(i),'info-logging');
      show_dad_attribute(dad_names(i),'owa-debug-enable');
      show_dad_attribute(dad_names(i),'max-requests-per-session');
      show_dad_attribute(dad_names(i),'nls-language');
      show_dad_attribute(dad_names(i),'path-alias');
      show_dad_attribute(dad_names(i),'path-alias-procedure');
      show_dad_attribute(dad_names(i),'request-validation-function');
      show_dad_attribute(dad_names(i),'session-cookie-name');
      show_dad_attribute(dad_names(i),'session-state-management');
      show_dad_attribute(dad_names(i),'transfer-mode');
      show_dad_attribute(dad_names(i),'upload-as-long-raw');
    end loop;
  else
    dbms_output.put_line('No dads found');
  end if;
end;
/

When the above script is run, you’ll get something like this:

APEX
... path=/apex/*
... authentication-mode=Basic
... database-username=ANONYMOUS
... default-page=apex
... document-path=docs
... document-procedure=wwv_flow_file_mgr.process_download
... document-table-name=wwv_flow_file_objects$
... nls-language=american_america.al32utf8
... request-validation-function=wwv_flow_epg_include_modules.authorize

APEX Tip: Page Auto Refresh

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with APEX version 2.2 but should work on earlier versions of APEX or HTMLDB.

  1. Create an Application Item to store the current refresh interval (e.g. F100_REFRESH_INTERVAL)
  2. Create an Application Computation to initialise it (e.g. F100_REFRESH_INTERVAL_COMP):
    • Computation Item = F100_REFRESH_INTERVAL
    • Computation Point = On New Instance (e.g. On Login)
    • Computation Type = Static Assignment
    • Computation = (default number of seconds, e.g. 60)
  3. Create a static List of Values (e.g. REFRESH_INTERVAL):

Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)

Now, for each page you wish to have auto-refreshed, edit the Page Properties:

  • HTML Header = <meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">

Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):

  • Display As = Select List with Submit
  • Region = (any region, I prefer the Breadcrumb region if one exists)
  • Label = Refresh
  • Source Used = Always, replacing any existing value in session state
  • Source Type = Item (application or page item name)
  • Maintain session state = Per session
  • Source value or expression = F100_REFRESH_INTERVAL
  • List of Values definition = REFRESH_INTERVAL

I also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:

  • Source Used = Always, replacing any existing value in session state
  • Source Type = PL/SQL Expression or Function
  • Maintain session state = Per session
  • Source value or expression = TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')

Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.

You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.

If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.


Learning APEX

I’ve been enjoying learning Oracle APEX the last few weeks, and have started to appreciate the depth of the product – there’s more than meets the eye. At first I was dependent on the wizards (of which there are many) for just about everything except SQL and PL/SQL. When I came across problems I’d just delete and re-create the entire page – but now I’m more often able to find the source of a problem and fix it. I’ve found that having a good knowledge of SQL, PL/SQL, HTML and Cascading Style Sheets is very helpful when working with APEX.


Upgraded to HTMLDB 2.0

Upgraded from 1.6. I’ve already done some playing on 2.0 on htmldb.oracle.com, already liked the little interface and management improvements.

I’m not too interested in all the bells and whistles – I just want to see first if I can produce usable maintenance forms for my preferred flavour of tables. I’ve been using the wizards to create all my pages, but have come across problems whenever the tables use natural PKs (specifically, when I try to make master-detail pages) – so far most problems have been resolved by adding a PK populated by a sequence from a trigger. Not what I really want to do; I’m hoping that I can find a way to get HTMLDB to work with natural-pk tables.

The main disadvantage to using surrogate keys I’ve found is practical – apart from the obvious one that I have to ensure that a unique constraint is on the natural key (just have to be careful to do it) – but then these surrogate keys get propogated throughout my design (especially when I have modelled multiple inheritance with table-per-subclass, which is my personal preference) and writing queries and certain types of constraints can become tricky.

So far my impression of HTMLDB is that it is a nice simple product – but hopefully not so simplistic as that it requires surrogate keys everywhere?

Another problem that I’ll investigate is HTMLDB’s behaviour in regards to checking for record changes prior to updates; I’ve gotten the “can’t update because the hash values differ” error quite often when I’ve based a page on an updateable view. I use updateable views a lot as a way of hiding the database complexity from the application, and I don’t want to have to (a) denormalise my design just to suit HTMLDB, or (b) try to design HTMLDB to work with each entity separately.


Experimenting with Oracle

I’ll post here results from some experiments I’m doing with Oracle. I want to explore the implications and possible benefits of mapping a truly relational logical database model directly to the physical model. In other words:

  1. Every entity mapped to a “base table”
  2. Every base table has one or more Unique keys (one arbitrarily chosen as “Primary”)
  3. No surrogate keys, except where the model doesn’t include any natural key
  4. All columns mandatory
  5. Application interfaces via fully updateable views (materialized or not)

In particular, I would like to determine how (if possible) to implement various types of table and database constraints apart from the standard PK and FK constraints.

Initial experiments indicate most of this is very possible, and with careful coding can be made to work. In fact, I’m sure some advantages over common practice will be demonstrated.

I’m doing all this on Oracle 10g Release 2 which I downloaded recently. I’ve installed it on my P4 with XP Home. For the frontend I’m using HtmlDB 1.6, although I’ll probably upgrade to HtmlDB 2.0 soon.

I should note that I don’t seriously expect this approach to have enough merit to be used in real projects – but this is not because of a lack in the relational model of data but because of the poor support of it by the SQL standard (and therefore, by DBMS’s that implement it). Therefore, I expect that to get all this to work I’ll have to write a lot of code very carefully, but that the resulting database will be very easy to write applications and queries for. For example, implementing all constraints on the database for a particular entity means that I don’t need complicated constraint checking code in every module that works with that entity.

I expect I may have to wrestle with row locking and handling constraint violations correctly in the presence of multiple users/sessions; I don’t want to have to enforce serialized access to get this to work because of the scalability problem.

I’ve found it interesting so far, anyway.