PostgreSQL Auto Increment

This post will demonstrate how to auto increment on a column in PostgreSQL.

In our example we will create a table, Managers.

Our table will have three columns: mgr_id, mgr_name, and mgr_email.

For each insertion of a new Manager entry, we want to auto increment our primary key, mgr_id, by 1.

testdb=> CREATE SEQUENCE mgr_id_seq;
CREATE SEQUENCE

We now create the Manager table.

For mgr_id, set as PRIMARY KEY and set the DEFAULT to NEXTVAL('mgr_id_seq') as shown below.

This will increment our sequence by 1 each time a new Manager entry is inserted.

testdb=> CREATE TABLE managers(
testdb(> mgr_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('mgr_id_seq'),
testdb(> mgr_name VARCHAR(50),
testdb(> mgr_email VARCHAR(50)
testdb(> );

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "managers_pkey" for table "managers"
CREATE TABLE
testdb=>

testdb=> INSERT INTO managers (mgr_name, mgr_email) VALUES('bob smith', 'bob@smith.com');
INSERT 0 1
testdb=> INSERT INTO managers (mgr_name, mgr_email) VALUES('tom jones', 'tom@jones.com');
INSERT 0 1

testdb=> select * from managers;
 mgr_id | mgr_name  |   mgr_email
--------+-----------+---------------
      1 | bob smith | bob@smith.com
      2 | tom jones | tom@jones.com
(2 rows)

testdb=>

As we an see from above, the increment begins at 1 and increments by 1 by default.







Unlike PhpMyAdmin, PhpPgAdmin does not have a handy drop-down to auto increment a column when you create a table.

You can simply use the PhpPgAdmin SQL Editor to create the sequence and table as shown in steps 1 to 3 above.

Alternatively, you can create your sequence using the Sequence node in PhpPgAdmin as shown below:



With your sequence created, you can then set the DEFAULT value for the mgr_id column to nextval('mgr_id_seq') in the table creation GUI as shown below:




More on Sequences in PostgreSQL:

http://www.postgresql.org/docs/9.0/static/sql-createsequence.html

http://www.postgresql.org/docs/9.1/static/functions-sequence.html

Posted at 06:44AM Feb 12, 2012 by David in PostgreSQL  |  Comments[0] | Tags: | Export to:

Install Oracle 11g Express (XE) on CentOS

This post will cover basic installation and configuration of Oracle 11g Express Edition (XE) on CentOS.

We will also take a quick look at configuring Application Express (APEX) for 11g XE.

Basic installation is straight forward.

If you just want to get up and running, you can just do steps 1 to 4 below (and 10 and 11 for Apex). The remaining steps (5 to 9) cover basic backup, recovery, and performance configuration.

The full system requirements are here


Your CentOS box should have swap equal to 2xRAM.

On every CentOS installation I have done for XE, I just needed to update/install the packages for libaio, bc, and flex.

[root@ms3 ~]#  yum install libaio bc flex

You can download the Oracle XE rpm, oracle-xe-11.2.0-1.0.x86_64.rpm.zip, from the OTN here

Unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip:

[root@ms3 ~]# unzip -q oracle-xe-11.2.0-1.0.x86_64.rpm.zip

This will create the directory Disk1. Change to the Disk1 directory:

[root@ms3 ~]# cd Disk1
[root@ms3 Disk1]# ls
oracle-xe-11.2.0-1.0.x86_64.rpm  response  upgrade

Install the rpm using rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

[root@ms3 Disk1]# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-xe              ########################################### [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

[root@ms3 Disk1]#

When installation completes, run '/etc/init.d/oracle-xe configure' to configure and start the database.

Unless you wish to change the ports, except the defaults and set SYS/SYSTEM password.

[root@ms3 Disk1]# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press  to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

The installation created the directory /u01 under which Oracle XE is installed.





To set the required Oracle environment variables, use the oracle_env.sh the script included under cd /u01/app/oracle/product/11.2.0/xe/bin

[root@ms3 Disk1]# cd /u01/app/oracle/product/11.2.0/xe/bin

To set the environment for your current session run '. ./oracle_env.sh':

[root@ms3 bin]# . ./oracle_env.sh

To set the environment permanently for users, add the following to the .bashrc or .bash_profile of the users you want to access the environment:

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

You should now be able to access SQL*Plus

[root@ms3 bin]# sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 21 08:17:26 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect sys/Password as sysdba
Connected.
SQL>

To allow remote access to Oracle 11g XE GUI (as well as Application Express GUI) issue the following from SQL*Plus

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

PL/SQL procedure successfully completed.

You should now be able to access the Oracle 11g XE Home Page GUI at:

http://localhost:8080/apex/f?p=4950:1

Replace localhost above with your IP or domain as required.

Log in as SYSTEM using the password you selected in Step 2 above.





To protect against disk failure, you should move the Flash Recovery Area to a separate disk.

This is actually now called the Fast Recovery Area, but the existing documentation still refers to it as the Flash Recovery Area

If a separate disk is not in your budget you should, at the very least, move the Flash Recovery Area to a partition other than the Oracle installation directory.

By default, the Fast Recovery Area will be located under /u01/app/oracle/fast_recovery_area

SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
SQL>

So, to move it elsewhere, first create the new directory

[root@ms3 ~]# mkdir /opt/fra

Change the owner to oracle and the group to dba

[root@ms3 ~]# chown oracle:dba /opt/fra

Now, change the DB_RECOVERY_FILE_DEST to the location you selected above.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/fra';

System altered.

SQL>

To move the files use the movelog.sql script:

SQL> @?/sqlplus/admin/movelogs
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> declare
  2     cursor rlc is
  3        select group# grp, thread# thr, bytes/1024 bytes_k
  4          from v$log
  5        order by 1;
  6     stmt     varchar2(2048);
  7     swtstmt  varchar2(1024) := 'alter system switch logfile';
  8     ckpstmt  varchar2(1024) := 'alter system checkpoint global';
  9  begin
 10     for rlcRec in rlc loop
 11    stmt := 'alter database add logfile thread ' ||
 12                 rlcRec.thr || ' size ' ||
 13                 rlcRec.bytes_k || 'K';
 14        execute immediate stmt;
 15        begin
 16           stmt := 'alter database drop logfile group ' || rlcRec.grp;
 17           execute immediate stmt;
 18        exception
 19           when others then
 20              execute immediate swtstmt;
 21              execute immediate ckpstmt;
 22              execute immediate stmt;
 23        end;
 24        execute immediate swtstmt;
 25     end loop;
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL>
SQL>

Now, set an appropriate size for the Fast Recovery Area. Use df -h to insure that there is ample space.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G;

System altered.

Verify the new location and size.

SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/fra
db_recovery_file_dest_size           big integer 20G
SQL>

You should have at least two Redo Log Groups and each group should have at least two members.

Additionally, the members should be spread across disks (or at least directories)

For whatever reason, only one member is created per group on install.

You can view the redo log files using SQL> SELECT * FROM V$LOGFILE;

Since the default location for the two members is the Flash Recovery Area, the two existing members have been moved to our new FRA.

You should now add an additional member for each group under /u01/app/oracle/oradata/XE

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log1b.LOG' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log2b.LOG' TO GROUP 2;

Database altered.

SQL>

The default values for parameters and sessions is quite low on the default installation.

SQL> show parameters sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     172
shared_server_sessions               integer

SQL> show parameters processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4
log_archive_max_processes            integer     4
processes                            integer     100

You can increase these parameters.

After each change, you will need to restart the database.

Increase sessions and then bounce database.

SQL> alter system set sessions=250 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             780143616 bytes
Database Buffers          281018368 bytes
Redo Buffers                5541888 bytes
Database mounted.
Database opened.

Verify change to sessions parameter:

SQL> show parameters sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     252
shared_server_sessions               integer

Increase processes and restart database

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL>


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             763366400 bytes
Database Buffers          297795584 bytes
Redo Buffers                5541888 bytes
Database mounted.
Database opened.

Verify change to processes parameter:

SQL>  show parameters processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4
log_archive_max_processes            integer     4
processes                            integer     200
SQL>

To enable online or "hot" backups, Archivelog Mode must be enabled.

Additionally, if you do not enable Archivelog Mode and take only offline or "cold" backups, should you need to restore the database you will only be able to restore to the last backup

To enable Archivelog Mode, shutdown the database and then startup mount:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             763366400 bytes
Database Buffers          297795584 bytes
Redo Buffers                5541888 bytes
Database mounted.

Enable Archivelog Mode

SQL> alter database archivelog;

Database altered.

Open the database and verify that Archivelog Mode is enabled

SQL> alter database open;

Database altered.

SQL>

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

SQL>

To create automated backups, you can modify the backup.sh included under /u01/app/oracle/product/11.2.0/xe/config/scripts

Create a directory for your backup script

[root@ms3 ~]# mkdir /opt/ora_backup

Change the owner to oracle and the group to dba

[root@ms3 ~]# chown oracle:dba /opt/ora_backup

Copy the backup.sh script from /u01/app/oracle/product/11.2.0/xe/config/scripts to the directory you created above.

[root@ms3 ~]# cp  /u01/app/oracle/product/11.2.0/xe/config/scripts/backup.sh /opt/ora_backup/backup.sh

Open the backup.sh script in a text editor or vi. The last section will look like this:

else
   echo Backup of the database succeeded.
   echo Log file is at $rman_backup_current.
fi

#Wait for user to press any key
echo -n "Press ENTER key to exit"
read userinp 

Change it to:

else
   echo Backup of the database succeeded.
   echo Log file is at $rman_backup_current.
   mail -s 'Oracle Backup Completed' 'david@davidghedini.com' < /u01/app/oracle/oxe_backup_current.log
fi

#Wait for user to press any key
#echo -n "Press ENTER key to exit"
#read userinp 

The line we added above, mail -s 'Oracle Backup Completed' 'david@davidghedini.com' < /u01/app/oracle/oxe_backup_current.log, will send us an email notification that the backup has completed as well as cat the backup log to the body of the email.

Note that we have also commented out the last two lines of the script (the prompt).

Create a cron job to run the script as user oracle.

You should run it at least once a day. With Archivelog Mode enabled, it is important that backups be taken regularly to prevent the Flash Recovery Area from filling.





Oracle 11g Express Edition comes with Application Express 4.0.2 already installed.

If you elect to upgrade to the latest version (4.1 as of this writing), you can do so but will loose access to the XE GUI. Not a huge loss, but something to keep in mind.

Although Apex is already installed, you will need to set the Internal Admin password.

To do so, run the apxchpwd.sql located under /u01/app/oracle/product/11.2.0/xe/apex:

Note: pick something simple like Password123! as you will be prompted to change it on first log in anyways.

SQL> @/u01/app/oracle/product/11.2.0/xe/apex/apxchpwd.sql
Enter a value below for the password for the Application Express ADMIN user.


Enter a password for the ADMIN user              []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.


Commit complete.

SQL>

You can access the Application Express GUI at:

http://localhost:8080/apex/f?p=4550:1

Replace localhost above with your IP or domain as required.

Workspace: Internal
User Name: admin
Password: (whatever you selected above).

Alternatively, you can access via

http://localhost:8080/apex/f?p=4550:10 or http://localhost:8080/apex/apex_admin

Again, replace localhost above with your IP or domain as required.





Unless you have a license for Oracle HTTP Server (OHS), your options are the embedded PLSQL Gateway (EPG) or Apex Listener.

The Application Express that comes installed with Oracle 11g XE is configured using the EPG.

While the EPG is simpler than Apex Listener, it can be painfully slow as of Apex 3.2.

Apex Listener, while quite fast, adds an extra layer of complexity.

You will need to install an application server to run Apex Listener.

I have run Apex Listener on both Tomcat (unsupported) as well as Oracle GlassFish 3.x (supported) and was not impressed with either.

A lot of people who know far more than I do about APEX (read: 99.9999% of the population) like the Apex Listener.

Apex Listener and it's installation guide can be found here.

The Apex Listener installation guide is well done and simple to follow.

If you need to install Oracle GlassFish or GlassFish CE (basic installation is the same), you can use my GlassFish 3.1 instructions here.

If you want to be an outlaw and use Tomcat, you can use my Tomcat 6 installation guide here. or my Tomcat 7 installation guide here..

Posted at 03:54AM Sep 25, 2011 by David in Oracle  |  Comments[1] | Tags: centos oracle | Export to:

Apache Roller: Export Blog Posts to PDF, Word, and PPT with Eclipse and BIRT

This post will cover how to export your Apache Roller blog posts to multiple formats using Eclipse and BIRT.

At the footer of each of my posts I have the option to export the post to PDF, Word, PowerPoint or HTML (print preview) as shown below.

At the onset, I'll say that while this solution works well for me, it has a fair number of issues which I've listed at bottom.

There are, of course, a number of ways you can accomplish exporting using itext and other tools.

Despite the additional memory overhead I chose Eclipse and BIRT because the solution is extremely simple to implement. It also lends itself to serving multiple blogs from a single BIRT instance and even from a single Eclipse report document.

What you will need:

1. Ecplise. I am using Helios (eclipse-reporting-helios-SR2).

2. BIRT Runtime Viewer. I've used both 2.5.2 as well as 3.7




For our data source, rather than my Postgresql database, I'll use my atom feed at http://www.davidghedini.com/pg/feed/entries/atom.

Using my atom feed means less security worries (no authentication required) and having my data source already in XML format.

In Eclipse, in the Data Pane, right click Data Source and select 'New Data Source'. Select 'XML Data Source'.

Give your Data Source a name. I'll name my Data Source 'DsDavesBlog'



For the url of the Data Source, enter your Apache Roller rss feed url



Click Test Connection to test and then click Finish.





In Eclipse, in the Data Pane, right click on Data Sets and select 'New Data Set'.

Under XML Data Source, highlight the Data Source you created in step 1 (we used 'DsDavesBlog').

Give your new Data Set a name as shown below. I'm calling mine 'dSetDavesBlog'. Click Next




In the New XML Data Set screen, select 'Use the XML File Defined in the Data Source' as shown below. Click Next



You should now see the Row Mapping screen as shown below. For XPath Expression, choose 'entry' from the tree menu and then use the add arrow. The resulting value of XPath Expression at right should now be '/feed/entry' as shown below.



You should now see the Column Mapping screen as shown below. Expand the 'entry' tree node as shown below.



Use the center arrows to add 'title' and 'content' as shown below.

'title' is our blog post title and 'content' is the actual blog post content.



Expand the 'link' tree entry beneath 'entry as shown below. Highlight the @href element and now use the center arrow to add the /link/@href element

The /link/@href XPath expression will provide a link to post's permalink page.



Your Column Mapping screen should now look as below.



Click the Show Sample Data button to verify the data set is pulling the correct elements



Close the Show Sample Data screen and click FInish





Now, create your layout in Eclipse.

For a basic report document like mine, Eclipse is simple to use.

You can simply create a grid and then drag your Data Set items into the appropriate locations. I've also added some text elements as well for the report document header, as well as social networking links.

The one Data Set element that requires special attention is the 'content' Data Set item. The Content Type for this element must be HTML as shown below.

To set the Content Type as HTML, drag a Dynamic Text field into the report document from the Palette Window. You can then bind the 'content' data set item to the Dynamic Text field.

Your 'content' report element should now look as below.






As part of the procedure for calling our Eclipse document(s) we will need to create a parameter for our report. In our case, the post title will serve as the parameter.

In the Data pane, right click on Report Parameters and select 'New Parameter'.

I'll call my parameter pTitle.



Now, in the Data pane, double click on the Data Set that we created above (dSetDavesBlog).

The Edit Data Set Window appears.

Select 'Filters' from the left menu, then click the 'New' button.

As show below, in the The New Filter Condition window, select row["title"] from the first drop down, 'Equal To' from the middle drop down, and '' from the right drop down as shown below.

In the Expression Builder window, click on 'Report Parameters' from the Category selection box.

In the Sub Category selection box, click on '--All--'.

In the Double Click to Insert selection box, double click on the parameter we created, pTitle as shown below. Click OK.

The New Filter Condition window will now look like below. Click OK.

The Edit Data Set window will not look like below.

Click OK.

Now, it's time to test our parameter. Click the Preview Tab in the report window.

The Enter Parameters pop up window should appear as below.

Enter the title of one of your posts and then click OK.

The report should return the post you passed as the parameter value as shown below.

We now have a report that we can use to export our posts to PDF, Word, PPT, Excel, or HTML (Print Preview).

Now, it's time to install BIRT.

Step 5: Install the BIRT Runtime Viewer.

Installing the BIRT Runtime Viewer is very simple. We will be installing on our existing Tomcat instance. For JBoss, WebSphere, WebLogic, you can find instructions here: http://www.eclipse.org/birt/phoenix/deploy/viewerSetup.php

1. Download the BIRT Runtime Viewer here: http://download.eclipse.org/birt/downloads/

2. Extract the contents.

3. Copy the WebViewerExample directory to your Tomcat webapps directory.

4. For my site, I renamed the WebViewerExample to 'export'. You can name it whatever you like.

5. Start or Restart your Tomcat instance.

6. You should now be able to reach the runtime viewer at http://YourDomain.com:8080/export

Note: In my set up I am using Jason Weathersby's Filename Generator, which you can download here: http://www.birt-exchange.org/org/devshare/deploying-birt-reports/1322-filename-generator-that-uses-a-url-parameter-to-name-export/

Now that BIRT is up and running, you need to make a few changes.

1. Delete the webcontent directory from BIRT. You do not need it and keeping the webcontent directory will cause issues if a file is not found and when using HTML as the export type.

2. Set PermSize If you do not set PermSize you will encounter memory errors. Simply add the desired parameters to your Catalina.sh as shown below or use whatever method you like for setting your JAVA_OPTS.

JAVA_OPTS="-Xms256m -Xmx512m -XX:PermSize=128M -XX:MaxPermSize=256M" 

Step 6: Publish Report to BIRT.

Publish your report to your BIRT directory under webapps. You need only publish the rptdesign document.

Step 7: Calling the Report from Your Roller Posts.

In order to call the reports from our posts we will to have JQuery available. You can probably use straight javascript, but I'm too lazy for that

If you do not already have JQuery available, add the following line to the header of your blog. You can use whatever version you wish.

To dynamically build the BIRT urls for calling your reports, you can do the following:

1. Get or create icons for PDF, Word, etc... If you like, you can download mine in zip format here:

http://www.davidghedini.com/DocImages.zip

In my script below, I have the image paths as /images/DocImages.

2. Create a JavaScript file to build the urls based on the document type you are exporting. These are the urls that need to be created both to render the links in the footer and to call the reports in various formats from BIRT.

For my blog, I use http://www.davidghedini.com/js/birt-rollers.js as shown below.

/* Get post title using JQuery */

var postTitle = $('.entryTitle').last().text();



/* Start building the url string to call the report from BIRT. */

var urlStart = '<a href="http://www.davidghedini.com/export/frameset?__report='+rollerId+'.rptdesign&pTitle='+postTitle+'&__filename='+postTitle;


/* Using Jason Weathersby's Filename Generator above to set document name to post title name */


/* Finish building the url string for BIRT based on document type  */

var urlStopPdf = '&__format=pdf&__pageoverflow=0&__asattachment=true&__overwrite=false"title="Export Post to PDF"><img src="http://www.davidghedini.com/PostImages/pdf.jpg" id="portImage" border="none"></a> '
var urlStopWord = '&__format=doc&__pageoverflow=0&__asattachment=true&__overwrite=false"title="Export Post to Word"><img src="http://www.davidghedini.com/PostImages/Word2007Logo.gif" id="portImage" border="none"></a> '
var urlStopPpt = '&__format=ppt&__pageoverflow=0&__asattachment=true&__overwrite=false"title="Export Post to PowerPoint"><img src="http://www.davidghedini.com/PostImages/powerpoint_logo.jpg" id="portImage" border="none"></a> '



/* HTML Print Preview */

var urlHtmlStart = '<a href="http://www.davidghedini.com/export/output?__report='+rollerId+'.rptdesign&pTitle=';
var urlHtmlStop = '&__format=html&__pageoverflow=0&__overwrite=false&__title='+rollerId+' '+postTitle+'  "title="Print Preview"  target="_blank"><img src="http://www.davidghedini.com/images/print_icon.gif" id="portImage" border="none"></a> '




/* Print doc links to post footer */
document.write(urlStart+urlStopPdf+urlStart+urlStopWord+urlStart+urlStopPpt+urlHtmlStart+postTitle+urlHtmlStop);

As you can see from above, we first use JQuery bit at the beginning:

var postTitle = $('.entryTitle').last().text();

The above captures the post title on both multiple post pages as well as single (permalink) pages.

The remainder of the js file is simply building the appropriate url for the document type we want to export to.

As noted above, I am using Jason Weathersby's Filename Generator to set the exported document to that of the post title. If you do not have or want to use this, remove the following from the end of var urlStart:

+'&__filename='+postTitle

2. Upload your birt-rollers.js file to your server.

3. Add the javascript below to your _Day template, where varRollerId is the Eclipse document name and the url is the location you uploaded birt-rollers.js.

<script type="text/javascript">
var rollerId = 'DavidGhedini.com';
var rollerUrl = '<sc'+'ript src=http://www.davidghedini.com/js/birt-rollers.js></sc'+'ript>';
document.write(rollerUrl);
</script>

I added the above javascript to my template pages as below:

 

            Posted at $utils.formatDate($entry.pubTime, "hh:mma MMM dd, yyyy")
            by $entry.creator.screenName in $entry.category.name  | 
            #if ($utils.isUserAuthorizedToAuthor($entry.website))
                $text.get("macro.weblog.entrypermalink.edit")  | 
            #end
            #set($commentCount = $entry.commentCount)
            #if($entry.commentsStillAllowed || $commentCount > 0)  
                #set($link = "$url.comments($entry.anchor)" )
                $text.get("macro.weblog.comments")[$commentCount]
            #end

| Tags: #foreach($tag in $entry.tags) $tag.name  #end |  Export to:
<script type="text/javascript">
var rollerId = 'DavidGhedini.com';
var rollerUrl = '<sc'+'ript src=http://www.davidghedini.com/js/birt-rollers.js></sc'+'ript>';
document.write(rollerUrl);
</script>

As noted at the start of this post, there are a number of issues with this solution.

I'm reasonably sure someone with better Java skills (read: anyone) could make a better go of it.

Also, there are a number of free online services that convert to some formats. The problem I had with these online services is that they work using the url of your post. The result is that the document produced is little more than a screen shot as it includes sidebars, menus, and other elements I didn't want in my document.

I wanted the body of my document to just capture my entryBox div

Problems (ones that I know about):

1. Handling of HTML content is spotty. Some tags can be escaped while others do not work well.

2. I cannot get the CSS of Syntax Highlighting to work in the document.

3. I have no idea how to set page breaks.

4. BIRT uses memory resources.

Again, the issues could probably be addressed by those with Java and/or Eclipse skills.


The upside for me is:

1. No-brainer deployment. You can implement the basic solution in less than 30 minutes.

2. A single BIRT instance can serve any number of blogs. Just change the value of var rollerId to the name of the document being used for the particular blog

3. Because it is using the atom feed as the data source and not a JNDI or similar, there should be no significant security concerns with multiple bloggers sharing the same BIRT instance.

4. Export to any supported BIRT format - PDF, HTML, Word, PowerPoint, CSV, and XLS.

5. Eclipse document can easily be reused for any blog. Simply change the atom url for the data source. Taking it a step further, rather than using a static text field for the document title, you could use the XPath to /entry/name (or any other identifier). Finally, as the data source is always the Roller atom feed, if you wanted to serve any number of blogs without having to update even the data source, you could set your Data Source dynamically. You can do this by adding a new parameter (pDataSource below) and changing your data-source xml as below:

<data-sources>
        <oda-data-source extensionID="org.eclipse.datatools.enablement.oda.xml" name="dsDavesBlog" id="7">
            <method name="beforeOpen"><![CDATA[filelist = this.getExtensionProperty("FILELIST");
filelisturl = filelist;
this.setExtensionProperty("FILELIST", "http://" + params["pDataSource"] + filelisturl);]]></method>
            <property name="FILELIST">/feed/entries/atom</property>
        </oda-data-source>
    </data-sources>

You could then set your Data Source in your javascript using something like:

<script type="text/javascript">
var rollerId = 'DavidGhedini.com';
var rollerDataSource = 'http://www.davidghedini.com/pg';
var rollerUrl = '<sc'+'ript src=http://www.davidghedini.com/js/birt-rollers.js></sc'+'ript>';
document.write(rollerUrl);
</script>

Posted at 06:33PM Sep 10, 2011 by David in Roller  |  Comments[0] | Tags: eclipse birt roller | Export to: