If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Pavi has produced an article for Oracle DBAs interested in finding ways of partitioning the tables as well showing a new form of partitioning using Oracle 9i.Business applications are growing at a faster rate than salaries, and so is the data supporting them, especially e-business applications where the data growth has been around 20-30 percent or more annually. Oracle came up with the idea of Partitioning the Tables. Yes ! The name sounds right “Partitioning”, the literal meaning of Partitioning is “The Act of dividing something into parts” and in our case that ‘something’ is nothing but…? 

You guessed it right! Data.

Partitioning in Oracle

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a “partition”. From an “Application Development” perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

So now you know partitioning in oracle now the only thing that yo u need to know is little bit of syntax and that’s it, and you are a partitioning guru.

Oracle introduced partitioning with 8.0. With this version only, ” Range Partitioning” was supported. I will come to details later about what that means. Then with Oracle 8i ” Hash and Composite Partitioning” was also introduced and with 9i ” List Partitioning”, it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go thru the details now.

Advantages of using Partition’s in Table

1. Smaller and more manageable pieces of data ( Partitions )
2. Reduced recovery time
3. Failure impact is less
4. import / export can be done at the ” Partition Level”.
5. Faster access of data
6. Partitions work independent of the other partitions.
7. Very easy to use

Types of Partitioning Methods

1. RANGE Partitioning

This type of partitioning creates partitions based on the ” Range of Column” values. Each partition is defined by a ” Partition Bound” (non inclusive ) that basically limits the scope of partition. Most commonly used values for ” Range Partition” is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter. So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.

This is example of the syntax needed for creating a RANGE PARTITION.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
;

the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999.

2. HASH Partitioning

Under this type of partitioning the records in a table, are partitions based of a Hash value found in the value of the column, that is used for partitioning. ” Hash Partitioning” does not have any logical meaning to the partitions as do the range partitioning. Lets take one example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01,
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03,
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;

The above example creates four hash partitions based on the zip codes from where the orders were placed.

3. List Partitioning ( Only with 9i)

Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’

Lets take one example. In fact, we will modify the same example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04,
PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.

4. Composite Range-Hash Partitioning

This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

Lets modify the above example again:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY HASH(CUST_NAME)
SUBPARTITION TEMPLATE(
(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04,
SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

The above example shows that each range partition has been further sub-partitioned into smaller partitions based on the list value specified. SHIP_TO_ARIZONA is a sub-partition by a List value AZ. This partition will be present in the main partitions by range SO99Q1 etc.

5. Composite Range-List Partitioning ( Only with 9i)

This is also a combination of Range and List Partitions, basically first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using List key values. Each sub partitions individually represents logical subset of the data not like composite Range-Hash Partition.

Index organized tables can be partitioned using Range or Hash Partitions

Lets modify the above partition once more.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY LIST(SHIP_TO_STATE)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 TABLESPACE TS01,
SUBPARTITION SP2 TABLESPACE TS02,
SUBPARTITION SP3 TABLESPACE TS03,
SUBPARTITION SP4 TABLESPACE TS04,
SUBPARTITION SP5 TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

With Oracle 9i, there is also a feature to create indexes on the partitions. The indexes can be:

a. Local indexes

This is created the same manner as the index on existing partitioned table. Each partition of a local index corresponds to one partition only.

b. Global Partitioned Indexes

This can be created on a partitioned or a non-partitioned tables. But for now, they can be partitioned using the ” Range Partitioning” only. For example, in above example, where I divided the table into partitions representing a quarter, a ” Global Index” can be created by using a different ” Partitioning Key” and can have different number of partitions.

c. Global Non- Partitioned Indexes

This is no different than the ordinary index created on a non-partitioned table. The index structure is not partitioned.

Conclusion

Partitioning greatly enhances the performance, manageability and availability of most databases. Partitioning can be applied to any databases and software shops using such a great option, have greatly improved the user satisfaction for there business applications.

I hope this overview of partitioning will provide some answers to What? When? Who? Where ? Why and How? of partitioning in Oracle. 

For detailed syntax and documentation refer to Oracle documentation. This article does not claim to provide a full understanding or the partitioning feature in oracle. Oracle has been modifying this feature with new releases. So lot more to come. Watch out.

—

by Pavi Agrawal 

• 0 Comments

For those who use databases but are not familar to the Oracle 9i database, perhaps you should read about the datatypes used in Oracle 9i and how they can be used to support object orientation.It is assumed that you have read my previous Oracle article based on creating an Oracle database in the latest Oracle database 9i. Before we can create a table, one should sit down and take the time to get to know the datatypes available for Oracle.

Also, upon reading articles relating to the Oracle database, you should have come across the term Abstract datatypes. This article will discuss abstract datatypes in depth so that creating a table, which is designed to include abstract datatypes, will inevitably become more understandable. These abstract datatypes, which I like to call user-defined types, are datatypes that behave like objects.

Oracle Datatypes

These Oracle datatypes are as follows:

Character Strings

• CHAR (size) – A fixed-sized field of characters. The largest this particular datatype can become is 2000 bytes. In other words, it can only hold 2000 characters. If you don’t specify the length of the CHAR datatype, the default size is a single character (i.e. 1 byte).

• NCHAR (size) – A fixed-sized field of characters, where the character set is determined by its definition. So, the maximum size is 2000 bytes per row or 2000 characters. This handles multibyte character sets.

• VARCHAR2 (size) – A variable-sized field of characters. The largest this datatype can become is 4000 characters.

• NVARCHAR2 (size) – A variable-sized field of characters, where the character set is determined by its definition. The maximum size is 4000 bytes per row or 4000 characters. This handles multibyte character sets.

Note: The VARCHAR2 datatype is the successor of VARCHAR. So it is recommended that you use VARCHAR2 as a variable-sized array of characters.

• LONG – A variable-sized field of characters. The maximum size of this field is 2GB.

Number

• NUMBER (precision, scale) – A variable-sized number, where the precision is between 1 and 38 and size is between -84 and 127. A NUMBER datatype with only one parameter is NUMBER (precision), where the parameter specifies the precision of the number. A NUMBER datatype with no parameters is set to its maximum size.

Date and Time

• DATE – A fixed-sized 7 bit field that is used to store dates. One thing to note is that the time is stored as part of the date. The default format DD-MON-YY can be overridden by NLS_DATE_FORMAT.

• TIMESTAMP (precision) – A variable-sized value ranging from 7 to 11 bytes, that is used to represent a date/time value. It includes both date and time. The precision parameter determines how many numbers are in the fractional part of SECOND field. The precision of the SECOND field within the TIMESTAMP value may have a value ranging from 0 to 9 with a default precision of 6.

• TIMESTAMP (precision) WITH TIME ZONE – A fixed-sized value of 13 bytes, which represents a date/time value along with a time zone setting. There are two ways one can set the time zone. The first is by using the UTC offset, say ‘+10:0’, or secondly by the region name, say ‘Australia/Sydney’.

• TIMESTAMP (precision) WITH LOCAL TIME – A variable value ranging from 7 to 11 bytes. This particular datatype is similar to the TIMESTAMP WITH TIME ZONE datatype. The difference is that the data is normalised to the database time zone when stored. The entry is manipulated to concur with the client’s time zone when retrieved.

Intervals

• INTERVAL DAY (day_precision) TO SECOND (second_precision) – A fixed-sized 11 byte value that represents a period of time. It includes days, hours, minutes and seconds.

• INTERVAL YEAR (year_precision) TO MONTH - A fixed-sized 5 byte value that represents a period of time. It includes years and months.

Binaries

• RAW (size) – A variable-sized field of raw binary data. The maximum size for this datatype is 2000 bytes.

• LONG RAW - A variable-sized field of raw binary data. The maximum size for this datatype is 2 GB.

• BLOB – The Binary Large Object is a field that holds unstructured binary data. The maximum size for this datatype is 4 GB.

• CLOB – The Character Large Object is a field that holds single byte character data. The maximum size for this datatype is 4 GB.

• NCLOB – The National Character Large Object is a field that holds either single byte of multibyte character data dependent on the national character set. The maximum size for this datatype is 4 GB.

• BFILE – An external binary file. The maximum size for this file is 4 GB. The size is also limited by the operating system.

Rows

• ROWID – A datatype that contains binary data that is used to identify a row.

Each ROWID is:

• • 6 bytes for normal indexes on non-partitioned tables, local indexes on partitioned tables and row pointers for chained/migrated rows.
  • 10 bytes for global indexes on partitioned tables.

• UROWID – The Universal ROWID is the datatype used to store both logical and physical ROWID values as well as foreign tables accessed through a gateway.

Alternatives for ANSI Standard Datatypes

Instead of using ANSI standard datatypes, you can use Oracle defined datatypes. View the table below to see the Oracle datatype alternative for ANSI standard datatypes.

ANSI Standard	Oracle Datatype
CHARACTER and CHAR	CHAR
CHARACTER VARYING and CHAR VARYING	VARCHAR2
NUMERIC, DECIMAL, DEC, INTEGER, INT and SMALLINT	NUMBER
FLOAT, REAL, DOUBLE PRECISION	FLOAT

Abstract Datatypes

In Oracle, one may create there own datatypes. Abstract datatypes allow Oracle to hold a range of datatypes. So, an abstract datatypes can have many parts to it. To do this one needs to create the datatype as an object. This object is made up of one or more datatypes.

Example of an Abstract Datatype

Let’s say that we want a datatype to split up a person’s address. The abstract datatype may be,

CREATE OR REPLACE TYPE persons_address AS OBJECT
(
v_streetNumber                      NUMBER,
v_streetName                         VARCHAR2(30),
v_citySuburb                          VARCHAR2(30),
v_state                                  VARCHAR2(4),
v_postCode                            NUMBER
);

When we create a table that references this abstract datatype the values must be inserted as

pe

rsons_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345)

You should now know what datatypes exist in Oracle 9i. You should also understand how one could use this datatypes to create abstract datatypes and hence support object orientation.

Now you understand how datatypes work in Oracle, you should be ready to create Oracle tables.

—

by Ben Shepherd 

• 0 Comments

Every wondered how the Oracle database is structured? To learn more about the structure and how one would create an Oracle 9i database using the latest features read this article.When creating a database, the main concept is to know how the database is structured in Oracle 9i. You should be aware that ever since the release of Oracle 8i, the Oracle database support object orientated structures. This means that Oracle supports abstract data types and methods.

If you are new to Oracle, then perhaps you are unclear on how data is stored and what files are included internally and externally. With this article you will be clear about how to create a database using Oracle 9i’s latest features.

How Data is Stored 

Data in Oracle is stored in tables and accessed data via a relational model. This means that one may use the tables of data items inside the database in a manner such that the tables relate to one another with the use of primary and foreign keys.

Tables that contain a foreign key are often translated as being a lookup table. Each table should have a primary key, so that each row can be uniquely identified. The primary key is used to connect with the foreign key in another table to form a relationship.

Oracle also supports object-orientated structures. This allows the database to include abstract data types and methods. Due to this object orientation property, objects may related to other objects and that object may exist inside other objects.

Files are used to store data in Oracle. Oracle 9i removes the risk of having orphan file. That is, like the name suggests the file doesn’t have a parent, which means that the datafile was not removed when it’s tablespace is removed. To prevent this from ever happening to your database again, Oracle has introduced “Oracle Managed Files”.

Oracle Managed files, abbreviated as OMF, uses a brand new system of storing files. OMF uses file system directories as opposed to filenames for files that are associated with the tablespace.

This means that when a tablespace is created, the files are given a unique system-generated name, using the locations that have been assigned to them along with two new initialization parameters. So when the tablespace is removed, the files will be removed with it.

Another new feature in Oracle 9i is the System Managed Undo, abbreviated as SMO. The SMO was created to make the creation of the database simpler for the DBA and to reduce the problem relating to misused rollback segments. The DBA now creates a tablespace as an UNDO type to allow the Oracle database to dynamically and automatically manage the number of undoes, along with the size of the undo segments within the tablespace.

A database is partitioned using tablespaces. The tablespace named SYSTEM is the databases default tablespace. These tablespaces allows for the grouping of elements within the system that transact with the database.

A tablespace contains files. These files are called datafiles, which are used to physically store data for the database. Notice this property suggests that datafiles are internal and external structure types, since they are parented by a tablespace (internal property) and are physical storage files (external). The datafile may be resized after it has been created.

Rules about Tablespaces and Datafiles

• A tablespace is parented by the database. So a tablespace cannot be a part of another database.
• A datafile is parented by the tablespace. So a datafile cannot be part of another database.
• A datafile may not be removed from a tablespace.

Now that we know how data is stored using Oracle, there are other special physical files that are associated with data storage and will assist in data recovery. The files that are created when the database in created and they are as follows,

• Redo log files, which will monitor transactions in a chronological manner. These files are known as online redo log files. The reason why this is an important file to have associated with the database is evident when the database malfunctions.

According to the Oracle 9i DBA Handbook, a database should consist of 3 or more online redo files. To archive old online redo files before they are inevitably over written, you may set the database to run in ARCHIVELOG mode. This allows the DBA to look further back into the history of transactions with the database.

• Control files, which holds the location of all the files the database will require. Multiple copies are made and stored on different disks to back up this operation-critical file. The CONTROL_FILES parameter specifies the names of the database control files. An instance must be shut down prior to an addition of a new control file.
• Temp files, which acts as a temporary storage for a result set. If the result exceeds the memory available in the RAM’s buffer, then the result set will be stored into the temporary data file. Make sure that the size of the file is large enough to hold large sorting operations.

The other files associated with an Oracle 9i database are called parameter files. There are two types of parameter files. These are,

• Static parameter files – This is what the init.ora file is. This are common known as a PFILE and should be based on the init.ora file and renamed as init{SID}.ora, where SID is the system identifier.
• Server parameter files – This is a new feature in Oracle 9i named the SPFILE. These parameter files are written in binary and should not be altered manually. The files should be named as spfile{SID}.ora, where SID is again the system identifier. The Oracle server always maintains these files.

To create the SPFILE we do the following,

CREATE SPFILE = ‘<directory>\spfile{SID}.ora’
FROM PFILE = ‘init{SID}.ora’

There are two types of parameter that are located in these parameter files. These are,

• Implicit parameter, which are parameters with no value. Oracle therefore assumes that the value for the parameter is the Oracle default values.
• Explicit parameter, which are parameter that have a value assigned to it.

Now we know how the parameter files work. Well, to access data inside the database, Oracle uses a set of background processes that are shared between users. The DBA Handbook states that an instance is “ a set of memory structures and background processes that access a set of database files.” Every instance has a large memory structure known as the System Global Area, abbreviated as SGA. The SGA will be explained in another article.

But where do the parameter fit in?

Well the parameters are used to set the size and composition of an instance and are the stored in one of the two specific parameter files as stated previously. The parameter file is read during the startup process. So, to access a database one must start an instance, which may call either the PFILE or the SPFILE. If there exist an SPFILE, then the STARTUP command will read that parameter file. If the SPFILE doesn’t exist, the default SPFILE is read. If that doesn’t exist than the PFILE is read. If you don’t wish for the server to read the PFILE, then simply override the default SPFILE with the PFILE.

STARTUP PFILE = ‘<directory>.init{SID}.ora’

Now you have a good understanding of architecture of an Oracle database, let us create one.

Create a Database 

There are two ways that you can create a database in Oracle 9i. One way is to use the Oracle Database Configuration Assistant. Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 9i’s latest features. Simply read the instruction and follow the prompts. The other way to create a database is to do it manually. This will now be demonstrated.

Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory.

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename. The command to do this action will depend on the platform you have the DBMS installed. If you are using Windows, simply type,

Set ORACLE_SID = {SID}

Alternatively, on the a unix platform, the DBA must type,

Export ORACLE_SID = {SID}

Use SQL*Plus under / as sysdba and set the database to nomount mode.

So, if we wish to override the default spfile to start the new instance in nomount mode then type,

Startup nomount pfile = ‘<directory>\init{SID}.ora’

Now we are about to create the database. When creating a new database, write the code into an sql file so that your have a copy of the database features as well as the ability to fix any mistakes in the create database command. Here is the code,

CREATE DATABASE ShepIT
Maxinstances   1
Maxloghistory  1
Maxlogfiles  10
Maxlogmembers 15
Maxdatafiles  100
Datafile ‘<directory>\datafile_01.dbf’
size 300M reuse autoextend on next 15M maxsize unlimited
character set WE8MSWIN1252
national character set AL16UFT16
logfile
group 1 (‘<directory>\redo_01.log’) size 75M
group 2 (‘<directory>\redo_02.log’) size 75M
group 3 (‘<directory>\redo_03.log’) size 75M
default temporary tablespace TEMP
tempfile ‘<directory>\temp_01.dbf’
extent management local uniform size 1M
undo tablespace UNDO_TS datafile ‘<directory>\datafile_01.dbf’
size 125M reuse autoextend on next 15M maxsize unlimited;

Oracle, should respond by saying that the database is created. Once this is done you are ready to load scripts that are needed to support your Oracle products by typing the following commands

@<directory>\catalog.sql
@<directory>\catproc.sql
@<directory>\catexp.sql

Ok, the database is configured. Change the SYSTEM and SYS passwords, to ensure better security for your database.

—

by Ben Shepherd

• 0 Comments

In this conclusion to a two-part article, you’ll learn how to drag and drop slides in our example slideshow to modify it, and how to filter by category. This article is excerpted from chapter six of the book Ruby on Rails: Up and Running, written by Bruce A. Tate and Curt Hibbs (O’Reilly, 2006; ISBN: 0596101325). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Drag and Drop Everything (Almost Everything)

We have already displayed a list of thumbnails of all photos that are in the slideshow and enabled the user to drag them around to rearrange their order in the slideshow. Now let’s add a second list of thumbnails, showing all photos that are not being used in the slideshow.

We’ll let the user add a photo to the slideshow by dragging it from the list of unused photos and dropping it onto the slideshow thumbnails. Similarly, we can enable the user to remove photos from the slideshow by dragging its thumbnail from the slideshow and dropping on the unused photos list. Finally, we’ll allow the user to filter the unused photos list by category.

As you might expect, we can accomplish all that in a very small amount of code. We will add a mere 58 lines of Ruby code to the models and controllers, 47 lines to the view templates, and 16 lines to our CSS stylesheet! Figure 6-4 gives you a preview of how this is going to look when we’re done.

Figure 6-4. Preview of drag-and-drop slideshow editing

Let’s start by updating the slideshow’s edit template. Edit photos/app/views/slideshows/edit.rhtml to look like this:

<h1>Editing slideshow</h1>

<div id=’slideshow-contents’>
<p style=’text-align: center;’><b>Slideshow Photos</b></p>
<div id=’slideshow-thumbs’>
<%= render :partial => ’show_slides_draggable’ %>
</div>
</div>

<div id=’slideshow-photo-picker’>
<p style=’text-align: center;’><b>Unused Photos</b></p>
<div id=’slideshow-photos’>
<%= render :partial => ‘photo_picker’ %>
</div>
</div>

<div id=’slideshow-attributes’>
<p><%= link_to ‘Play this Slideshow’, :action => ’show’, :id => @slideshow %></p>
<div style=’border: thin solid; padding-left: 1em;’>
<p style=’text-align: center;’><b>Attributes</b></p>
<%= start_form_tag :action => ‘update’, :id => @slideshow %>
<%= render :partial => ‘form’ %>
<%= submit_tag ‘Save Attributes’ %>
<%= end_form_tag %>
</div>
<p>
<b>Hint:</b> Drag and drop photos between the
two lists to add and remove photos from the
slideshow. Drag photos within the slideshow to
rearrange their order.
</p>
</div>

<%= drop_receiving_element(”slideshow-contents”,
:update => “slideshow-thumbs”,
:url => {:action => “add_photo” },
:accept => “photos”,
:droponempty => “true”,
:loading => visual_effect(:fade),
:complete => visual_effect(:highlight, ’sortable_thumbs’)
) %>

This file has been almost entirely rewritten, so there are no marked-as-changed lines. You can see that I have laid out this edit page into three sections:

<div id=’slideshow-contents’> . . . </div>
<div id=’slideshow-photo-picker’> . . . </div>
<div id=’slideshow-attributes’> . . . </div>

Only the slideshow-photo-picker is new. It shows the list of unused photos that can be added to the slideshow. We will set up the CSS stylesheet to display these sections side-by-side as you saw them in Figure 6-4.

slideshow-contents is rendered by the partial template show_slides_draggable,
slideshow-photo-picker is rendered by the partial template photo_picker, and slideshow-attributes is mostly rendered by the form partial template that was generated from the scaffolding. I say “mostly” because I added a few things inline around the rendering of form.

Finally, notice two Ajax related helpers: drop_receiving_element and observe_field. We’ll come back to these in a little bit after we have discussed some prerequisite details.

Now, make these changes to photos/app/controllers/slideshows_controller.rb, replacing the edit method and creating the unused_photos method:

def edit
@slideshow = Slideshow.find(params[:id])
session[:slideshow] = @slideshow
@photos = unused_photos(@slideshow)
end

def unused_photos(slideshow)
all_photos = Photo.find(:all)
candidates = []
for photo in all_photos
in_slideshow = false
for slide in slideshow.slides
if slide.photo.thumbnail === photo.thumbnail
in_slideshow = true
break
end
end
candidates << photo if not in_slideshow
end
return candidates
end

The purpose of this code is to retrieve all the data needed by the edit.rhtml view template:

@slideshow = Slideshow.find(params[:id])

The id of the slideshow that you want to edit is passed in the request parameters from the browser. Here you retrieve that id and read that slideshow from the database, which you store in the instance variable @slideshow to make it available to the view template.

session[:slideshow] = @slideshow

Ajax actions requests will be coming in as the user makes changes, and you need to know what slideshow to change. This line saves a reference to the slideshow in the session hash. I’m using a key value of :slideshow to save and retrieve this from the session, but that value is arbitrary and could have been any unique identifier.

@photos = unused_photos(@slideshow)

This line calls the new method unused_photos to retrieve a list of all photos that are not in the slideshow; it then saves that list in @photos.

def unused_photos(slideshow)

This method returns a list of photos that are not in the slideshow. The logic should be self-explanatory. First, create an empty array (candidates = []), and then iterate through the list of all photos, adding them to the array (candidates << photo) if they are not already in the slideshow. The technique used here is grossly inefficient, but it will suffice for our purposes.

We still need to create the photo_picker template that generates the HTML to display all the photos that can still be added to a slideshow, so go ahead and create the file photos/app/views/slideshows/_photo_picker.rhtml with this in it:

<% for photo in @photos %>
<%= image_tag(”photos/#{photo.thumbnail}”,
:style => “vertical-align: middle”,
:id => “photo_#{photo.id}”,
:class => “photos”) %>
<%= draggable_element “photo_#{photo.id}”, :revert => true %>
<% end %>

This template iterates through the list of photos in @photos. For each photo, it uses the image_tag helper to create an HTML image tag and the draggable_element helper to generate the JavaScript code that makes it draggable. You can see that the first parameter of draggable_element matches the value of the id attribute (:id => “photo_#{photo.id}”) on the image tag. The draggable_element helper expects the id of the HTML element that it should make draggable, followed by zero or more options. The single option used here (:revert => true) says to move the element back to its original position after it is dropped.

But where can these draggable images be dropped? Recall that at the end of the slideshow’s edit.rthtml template we had:

<%= drop_receiving_element(”slideshow-contents”,
:update => “slideshow-thumbs”,
:url => {:action => “add_photo” },
:accept => “photos”,
:droponempty => “true”,
:loading => visual_effect(:fade),
:complete => visual_effect(:highlight, ’sortable_thumbs’)
) %>

Just like the draggable_element helper, the drop_receiving_element helper expects the ID of the HTML element onto which you can drop something that was declared as draggable. The remaining parameters are options that given as name/value pairs (the order is not important). These options are doing a lot, so let’s go through them one at a time:

:update => “slideshow-thumbs”

This gives the ID of the HTML element that should be updated when a photo is dropped on our slideshow-contents div. The :position and :url options say how, and with what, that HTML element should be updated. When the :position option is omitted (as it is here), the HTML returned from the server replaces the target elements HTML. The :position option says that the returned HTML should be inserted into target element, instead of replacing it. The value :position can be specified as :before, :top, :bottom, and :after.

:url => {:action => “add_photo” }

This option constructs the URL that is sent to the server (via a background Ajax request) when a photo is dropped (you’ve seen this before). This executes the add_photo method in the current controller (the SlideshowsController). The add_ photo action adds the dropped photo to the slideshow and returns an HTML fragment that will replace the existing HTML in the target element, which, as you will see, is a rerendering of the slideshow’s contents, which now include the added photo.

:accept => “photos”

Without this option, you could drop any draggable element here. However, this line says that only HTML elements that have the class attribute “photos” can be dropped here. Remember that in our photo picker template we gave each photo class attribute of “photos”.

:droponempty => “true”

This option says that the user can drop photos here even if the target is completely empty.

:loading => visual_effect(:fade)
:complete => visual_effect(:highlight, ’sortable_thumbs’)

:loading and :complete (plus a few more events) specify client-side JavaScript event handlers that are executed at specific points in the progress of the Ajax request. In both cases, we are displaying a visual effect that gives the user positive feedback. The :loading event occurs when the browser begins loading the response, and the :complete event occurs when its all finished. The code specifies that the dropped photo will fade until it becomes invisible. It also highlights the target area on which the photo was dropped.

Adding a Dropped Photo

Now we need to create the add_photo method to actually add a dropped photo to the slideshow. Edit photos/app/controllers/slideshows_controller.rb, and add this:

  def add_photo
slideshow_id = session[:slideshow].id
photo_id = params[:id].split(”_”)[1]
slide = Slide.new()
slide.photo_id = photo_id
slide.slideshow_id = slideshow_id
if !slide.save
flash[:notice] = ‘Error: unable to add photo.’
end
@slideshow = Slideshow.find(slideshow_id)
session[:slideshow] = @slideshow
render_partial ’show_slides_draggable’
  end

Let’s walk through this code:

slideshow_id = session[:slideshow].id

This line retrieves the current slideshow from the session hash and gets the slideshow’s id.

photo_id = params[:id].split(”_”)[1]

The id attribute of the dropped photo get passed as the :id parameter. If you recall from the photo_picker template, we set those ids to values such as “photo_1″ and “photo_19″, so the remainder of this line of code splits the string on the underscore, grabs the second half, and assigns it to photo_id.

The next five lines create a new slide, assign to it the photo id and the slideshow id, and then save it to the database.

Finally, we render and return the show_slides_draggable partial, after setting @slideshow to the current slideshow (which is needed by the partial template).

All that code handles dragging new photos to add to the slideshow. Now we just need to add a little more code to implement dragging a photo from the slideshow to the unused photos list as an intuitive way to remove photos from the slideshow.

The displayed list of photos in the slideshow are already draggable because we made them into a sortable list. The only problem with the current implementation is that the photos can be dragged vertically only. They need to be dragged both vertically for reordering and horizontally to the unused photos column.

We can drag the photos only vertically because the default option for a sortable list is :constraint => ‘vertical’. Fortunately, you can change this by editing the file photos/app/views/slideshows/_show_slides_draggable.rhtml and changing the call to the sortable_element helper to add this :constraint option:

  <%= sortable_element(’sortable_thumbs’,
:url => {:action => ‘update_slide_order’},
:constraint => ”) %>

Now you can drag those photos anywhere. But you still need to make the unused photos list into a drop receiver that uses Ajax to remove the dropped photo from the slideshow.

To do so, edit photos/app/views/slideshows/edit.rhtml, and add this at the end:

  <%= drop_receiving_element(”slideshow-photo-picker”,
:update => “slideshow-photos”,
:url => {:action => “remove_slide” },
:accept => “slides”,
:droponempty => “true”,
:loading => visual_effect(:fade),
:complete => visual_effect(:highlight, ’slideshow-photos’)
) %>

This code is almost identical to the other drop_receiving_element we used. The difference is that the target is the slideshow-photo-picker, and the action taken on a drop is to call the remove_slide method. Also, notice that you can drop only “slides” here (that is, HTML elements with a class attribute of slides). If you go back and take a look at how we defined the partial template photos/app/views/ slideshows/_show_slides_draggable.rhtml, you will see that we did, indeed, make each item in the sortable list a slide.

Add the remove_slide method to photos/app/controllers/slideshows_controller.rb:

  def remove_slide
slideshow_id = session[:slideshow].id
slide_id = params[:id].split(”_”)[1]
Slide.delete(slide_id)
@slideshow = Slideshow.find(slideshow_id)
session[:slideshow] = @slideshow
@photos = unused_photos(@slideshow)
render_partial ‘photo_picker’
end

In this code, you get the id of slide you want to remove, and then delete it from the slide database table. Remember, this action does not delete the photo from the database. The slide data says what photos are in a given slideshow, and deleting an entry from the slide table removes that slide from its slideshow. Finally, you render the HTML for the photo picker, which now includes the removed slide.

I’ll bet you’re anxious to see all this in action. All you need to do is to update the style sheet and then try it out. Edit photos/public/stylesheets/slideshows.css, and add the following:

  #slideshow-photo-picker {
float: left;
width: 10em;
text-align: center;
border-right: thin solid #bbb;
padding: 0.50em;
padding-bottom: 10em;
}

  img.thumbnail {
border: 2px solid black;
margin-bottom: 1em;
}

  img.photos {
border: 2px solid black;
margin-bottom: 1em;
}

Whew! That’s it: try it now!

The first thing you’ll notice is that the Unused Photos section is empty (see Figure 6-5). That’s because all the photos are currently in the slideshow. Just drag a few of the slides out of the slideshow and drop them into the Unused Photos column; then you’ll have something more like Figure 6-6.

Filtering by Category

Displaying all unused photos might seem acceptable right now, but we have only nine photos. If there were 900, it would quickly become unusable. So, our final feature in this chapter will be to display only the unused photos in a particular category.

The first thing to do in our controller is get a list of all categories that can populate the drop-down selection box. Edit photos/app/controllers/slideshows_controller.rb, and add this line to the end of the edit method:

  @all_categories = Category.find(:all, :order=>”name”)

This line retrieves a list of categories that can populate a drop-down selection box that the user will use to display only those unused photos that are in the selected category.

Figure 6-5.  Drag and drop add and remov

Now, edit photos/app/views/slideshows/edit.rhtml, and add this right after the ‘Play this Slideshow’ line:

  <p>
<label for=”category_id”>Filter “Unused Photos” on this Category</label><br/>
<%= collection_select(:category, :id, @all_categories, :id, :long_name) %>
<%= observe_field(:category_id,
:frequency => 2.0,
:update => ’slideshow-photos’,
:url => { :action => ‘change_filter’},
:with => ‘category_id’ ) %>
</p>

The collection_select helper is normally used inside an HTML form, but here we are using it because it conveniently knows how to display a collection in a drop-down box. It will never be submitted as part of a form.

As shown, the observe_field helper checks the category drop-down box for changes every two seconds. When a change is detected, an Ajax request is fired off to the change_filter method, which returns new HTML (that has been appropriately
filtered) to replace the slideshow-photos section.

Figure 6-6.  Some unused photos

The Category model class automatically shows a collection of all photos that are in a particular category. However, we need to get a collection of photos that are in a given category and in all of its child categories.

Edit photos/app/models/category.rb, and add this method:

  def photos_including_child_categories
result = photos.clone
children.each do |c| 
c.photos_including_child_categories.each {|p|
result << p if not result.include? p}
end
result
  end

This method recursively collects a list of all photos in its own category and all of its child categories. You can use this in to get the list of unused photos to display.

In the meantime, edit photos/app/controllers/slideshows_controller.rb to add the change_filter method:

  def change_filter
slideshow_id = session[:slideshow].id
category_id = params[:category_id] || 1
session[:category_id] = category_id
@slideshow = Slideshow.find(slideshow_id)
session[:slideshow] = @slideshow
@photos = unused_photos(@slideshow)
render_partial ‘photo_picker’
end

This method stores the chosen category id in the session hash, retrieves a new list of unused photos, and then renders the photo_picker. Notice the bold code line in the previous code. This line tries to retrieve the category id from the request parameters. If there aren’t any parameters, params[:category_id] returns nil, and the || operator returns the rightmost argument (”1″ in this case).

Also, in this slideshow controller, we need to update the method that retrieves the unused photos to pay attention to the category setting. Do so by editing the unused_ photos method; then replace the line all_photos=Photo.find(:all) with the following:

  category_id = session[:category_id] || 1
session[:category_id] = category_id
category = Category.find(category_id)
all_photos = category.photos_including_child_categories

We’re done; we’ve added category filtering! Fire up your browser, and try it (you may need to assign some categories to some unused photos). Now it looks like Figure 6-7.

Figure 6-7.  Filtering on categories

We’ve come a long way in a very short time. With fewer than 200 lines of code, we’ve added drag-and-drop capability to add and reorder slides. We’ve also added the core capability to actually show a slideshow. Ajax made our application much easier to use and more attractive. Next, we’ll look into testing this application.

—

by O’Reilly Media 

• 0 Comments

In this first article in a two-part series, you will learn how Ruby on Rails implements Ajax. We will use the specific example of a slideshow to demonstrate our points. This article is excerpted from chapter six of the book Ruby on Rails: Up and Running, written by Bruce A. Tate and Curt Hibbs (O’Reilly, 2006; ISBN: 0596101325). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Ajax is one of the most important emerging trends in web applications. Web sites like Google Maps and Gmail dramatically demonstrate that web applications do not have to be slow, clunky, page-at-a-time web forms. Ajax techniques can reclaim some of the fluidity and responsiveness that was lost when we moved from desktop applications to web applications.

Ajax (which stands for the cryptic “Asynchronous JavaScript and XML”) is a technique for building web pages that are more interactive, exciting, and dynamic. Ajax is asynchronous: JavaScript libraries can communicate with the server at any time, and the web page need not be frozen while waiting for a response. Ajax uses JavaScript on the browser, any language on the server, and XML to specify messages.

When you use this emerging technique, a web page can communicate with the server at any time, updating only those portions of the display that need it. Users experience more responsive web pages, with immediate feedback. Even though using Ajax techniques usually requires significantly more sophisticated design and implementation skills, the benefits to the end user are so great that Ajax-enabled web applications will soon become the rule, not the exception. Fortunately, Rails makes Ajax so simple that, for typical cases, using Ajax is almost as easy as not using it.

How Rails Implements Ajax

Rails has a simple, consistent model for how it implements Ajax operations. Once the browser has rendered and displayed the initial web page, different user actions cause it to display a new web page (like any traditional web application) or trigger an Ajax operation:

Some trigger fires

This trigger could be the user clicking on a button or link, the user making changes to the data on a form or in a field, or just a periodic trigger (based on a timer).

The web client calls the server

A JavaScript method, XMLHttpRequest, sends data associated with the trigger to an action handler on the server. The data might be the ID of a checkbox, the text in an entry field, or a whole form.

The server does something

The server-side action handler–a Rails controller action (for our purposes)–does something with the data and returns an HTML fragment to the web client.

The client receives the response

The client-side JavaScript, which Rails creates automatically, receives the HTML fragment and uses it to update a specified part of the current pages HTML, often the content of a <div> tag.

These steps are the simplest way to use Ajax in a Rails application, but with a little extra work, you can have the server return any kind of data in response to an Ajax request, and you can create custom JavaScript in the browser to perform more involved interactions. We’ll stick to HTML fragments in this chapter.

Rails uses the Prototype and script.aculo.us JavaScript libraries to implement browser support for Ajax. You can use these libraries independently of Rails, but with their seamless integration with Rails, you probably wont want to. Throughout this chapter, we’ll exploit the Ajax and special-effects capabilities that come with Rails to implement missing features in our Photo Share application.

Playing a Slideshow

Let’s see what happens when we try to play a slideshow. Browse to http://127.0.0.1: 3000/slideshows/list, and click the Play link for our only slideshow. As you can see in Figure 6-1, this URL invokes the show action on the slideshow controller, but the action is still using the scaffold code.

We need to change this page to actually “play” the slideshow by sequentially displaying the pictures contained in the slideshow. To do this, we will initially display the first picture in the slideshow; then, once every two seconds, we’ll make an Ajax call to get and display the next picture.

The controller sets up all the slides in a slideshow for playback. You need to start @slideshow with the current slideshow set to play. You also need to put the current slide (initially, 0) and the whole slideshow into a holding area called the session, so you won’t have to read from the database each time you play a new slide. Edit the slideshows controller (app/controllers/slideshows_controller.rb), and modify the show method to look like this:

  def show
@slideshow = Slideshow.find(params[:id])
session[:slideshow] = @slideshow
session[:slide_index] = 0

Figure 6-1.  Playing a slideshow that still uses scaffolding

 

    @slide = @slideshow.slides[0]
end

Every two seconds in this code, the browser sends an Ajax request to get the next slide. You can’t use instance variables to keep track of where you are in the slideshow because instance variables exist only until you finish processing the current request. Use the Rails-provided session object instead, which is persistent across requests. Let’s look at this code in a little more detail.

session[:slideshow] = @slideshow stores a reference to the current slideshow in the session hash at the key :slideshow. We do the same thing with the index of the current slide that is being played. We initially set the slide_index to zero to point to the first slide, and our Ajax request increments the index by one as it displays each slide. We can retrieve these values from the session hash during the Ajax requests for the next slide.

Now, edit the view template (app/views/slideshows/show.rhtml) to look like this:

  <p><i><%= @slideshow.name %></i></p>

  <div id=”slides”>
<%= render :partial => “show_slide” %>
</div> 

  <%= periodically_call_remote :update    => ’slides’,
:url       => { :action => :show_slide },
:frequency => 2.0 %>

This RHTML template contains three things: a title line, the div that displays the current slide, and a magic Ajax incantation that we will now pick apart.

The periodically_call_remote Rails helper function creates JavaScript that periodically sends a request to the server and uses the HTML fragment that is returned to replace the content of the update target. In this case, the update target is an HTML element with an ID of ’slides’, which is a <div> tag. The returned HTML fragment replaces the contents of this <div> tag. The URL that makes the request is constructed to ensure that it will be routed to the show_slide method of the current controller (the slideshows controller). Finally, the frequency parameter makes the call once every two seconds. All Ajax help functions take their parameters in key/value pairs, so you can list the parameters in any order.

We need to display each slide as it comes back to the client; Rails uses a partial HTML template to do this work. To create the partial view template, place the following contents in a new file called app/views/slideshows/_show_slide.rhtml:

  <%= image_tag “photos/#{@slide.photo.filename}” %>
<p><%= @slide.photo.filename %></p>

And its controller method in app/controllers/slideshows_controller.rb:

  def show_slide
@slideshow = session[:slideshow]
session[:slide_index] += 1
@slide = @slideshow.slides[session[:slide_index]]
if @slide == nil
session[:slide_index] = 0
@slide = @slideshow.slides[0]
end
render :partial => “show_slide”
end

This method retrieves the slideshow information from the session, moves to the next slide (or back to the beginning if at the end), and then explicitly renders the partial view template show_slide. You need to render a partial view or render with the option :render_layout => false . Otherwise, Rails tries to render a full template, including layout. As our page already has a layout, simply render a partial template, consisting of an image tag for the slide, and its name.

Finally, you need to update your standard layout template to include script tags for the Prototype JavaScript library because the client-side JavaScript code that Rails creates for you uses them, so in app/views/layouts/standard.rhtml, insert this line immediately after the title tags:

  <%= javascript_include_tag ‘prototype’, ‘effects’, ‘dragdrop’ %>

This line includes three JavaScript files that are shipped with Rails: prototype.js and two Script.aculos.us files, effects.js and dragdrop.js. We will use these last two shortly.

Now show a slideshow by loading slideshows/list and clicking Show, and you will see the actual pictures in the slideshow, changing every two seconds.

Using Drag-and-Drop to Reorder Slides

The scaffolding we have for editing a slideshow shows just the slideshow attributes that are stored directly in the slideshows table: the slideshows name and the date on which it was created. The most important part is missing: the photos that are part of the slideshow!

By now, you’ve probably realized that this is because the scaffolding code deals with only one database table: the slideshows table. The relationship data about which photos are assigned to a slideshow and their order in the slideshow are stored in the slides table. Scaffolding does not handle relationships, so you have to write the code to edit this relationship data.

We’re going to display a list of thumbnails of all the photos that are in a slideshow, and then let the user reorder them using drag-and-drop. If you’ve had to struggle through implementing drag-and-drop before, you’re not going to believe how easy this is going to be. Here’s a hint: this will take a total 34 additional lines of Ruby, CSS, and RHTML template!

Let’s start by reviewing the current implementation of the edit action in the slideshow controller:

  def edit
@slideshow = Slideshow.find(params[:id])
end

This action expects to find the ID of the slideshow to edit passed in as the id parameter, which is normally decoded from the URL. You find the slideshow with that ID and assign that slideshow object to the instance variable @slideshow, so that it can be accessed in the view template.

That is really all that’s needed here, so you won’t have to add any code to this method. The changes will start with the edit view template, so edit the template photos/app/views/slideshows/edit.rhtml and make it look like this (the changes are in bold):

  <h1>Editing slideshow</h1>

  <%= link_to ‘Play this Slideshow’,
:action => ’show’, :id => @slideshow %>

  <div id=’slideshow-contents’>
    <%= render :partial => ’show_slides_draggable’ %>
</div>
<div id=’slideshow-attributes’>
    <%= start_form_tag :action => ‘update’, :id => @slideshow %>
<%= render :partial => ‘form’ %>
<%= submit_tag ‘Save Attributes’ %>
<%= end_form_tag %>
</div>

Notice that the existing <%= render :partial => ‘form’ %> is wrapped in a <div> tag with an id attribute of slideshow-attributes. You will use this name in one of your CSS files to control how this section is displayed.

There is also a completely new section that displays thumbnails of the photos in the slideshow:

  <div id=’slideshow-contents’>
<%= render :partial => ’show_slides_draggable’ %>
</div>

This code also uses a <div> tag with an id attribute, for the same reason: to use a CSS file to control its appearance. This div also renders a new partial view template named show_slides_draggable, which we will create next.

Create the file photos/app/views/slideshows/_show_slides_draggable.rhtml with the following contents:

  <ol id=’sortable_thumbs’>
<% for slide in @slideshow.slides %>
<li id=’thumbs_<%= slide.id %>’ class=’slides’>
<%= thumbnail_tag slide %>
</li>
<% end %>
</ol>

  <%= sortable_element(’sortable_thumbs’,
:url => {:action => ‘update_slide_order’}) %>

The first part is pretty standard stuff. We’re creating an HTML ordered list, in which each list item is a thumbnail image of one of the photos in the slideshow (note that the thumbnail_tag helper function that was created earlier). However, it’s the last two lines that do the heavy lifting.

sortable_element is a helper function that generates the JavaScript code that turns our list into a user-sortable, drag-and-drop-capable list. It wraps this list an HTML form, and the :url option specifies the URL to post to the server whenever the user changes the order of the list. In this case, it calls the action method update_slide_order in our slideshow controller. This call works in the background using an Ajax call.

The update_slide_order method is pretty simple as well. Edit photos/app/controllers/ slideshows_controller.rb, and add this method:

  def update_slide_order
params[:sortable_thumbs].each_with_index do |id, position|
Slide.update(id, :position => position)
end
end

This method iterates through each slide in the list, extracting its ID and position in the list, and uses this information to update that slide’s database row with its new position. Let’s walk through this code in a little more detail:

1. params is a hash that holds all the parameters sent to the server in the HTTP request. params[:sortable_thumbs] retrieves the parameter for the sortable_thumbs list, which is an ordered array of the IDs of each thumbnail in the list.
2. each_with_index is a Ruby iterator that, just like the each iterator, walks through the array one item at a time. But on each iteration, each_with_index passes to the code block both the object held in the array (the slide id) and its index in the array (which is assigned to position).
3. Slide.update(id, :position => position) then calls the Slide model class to update the slide identified by id with its new position.

We’re almost ready to give it a try, but first let’s edit photos/public/stylesheets/slideshows.css and add some formatting instructions for the two div IDs we created. Add the following at the end of the file:

  #slideshow-contents {
float: left;
width: 11em;
padding: 0.50em;
text-align: center;
border-right: thin solid #bbb;
padding: 0.50em;
padding-bottom: 10em;
}

  #slideshow-attributes {
margin-left: 23em;
padding-left: 1.5em;
padding-top: 1.5em;
}

This causes the contents of the slideshow (which will be a list of thumbnail images) to be displayed down the left side of the page, and the slideshow’s attributes will be displayed immediately to the right of the thumbnails.

Let’s see how this looks. Browse to http://127.0.0.1:3000/slideshows/list, and click the edit link for our one and only slideshow. It will look like Figure 6-2.

Click on one of the photos, and try dragging it around. When you drop it into a new location, update_slide_order is called to write the new order to the database.

Lets fix one minor thing here before we move on. Wouldn’t it be better to see the number of each photo appear vertically aligned in the middle of the thumbnail instead of at the bottom? Because the HTML for each thumbnail image is created by…

Figure 6-2.  A drag-and-drop list of photos

…

our own helper function, thumbnail_tag, we just need to edit that function and add a vertical-align style attribute.

First, edit photos/app/helpers/slideshows_helper.rb, and add the code shown in bold:

  module SlideshowsHelper
def thumbnail_tag(slide)
image_tag(”photos/#{slide.photo.thumbnail}”,
:style=>”vertical-align:middle”) if slide
end
end

Now, refresh your browser: the list numbers are nicely centered, as you can see in Figure 6-3.

With a very small amount of code, we added a very nice drag-and-drop user interface for reordering the slides in a slideshow. But we’re just getting started with our Ajax-enabled user interface.

Figure 6-3.  Nicely centered list numbers

Please check back tomorrow for the conclusion to this article.

—

by O’Reilly Media

• 0 Comments

Java and Flash offer real alternatives for those trying to build web applications that function more like desktop applications. However, they have their shortcomings. AJAX overcomes these shortcomings — especially if you use Ruby on Rails.

Interactivity and responsiveness has been considered the USP of desktop applications and not of web applications. Even though there exist mechanisms such as Java Applets and Flash files that provide interactivity and responsiveness up to a point, they are intrusive. That is, the user needs to install either a Java or a Flash plug-in.

However, with AJAX coming into the foreground, a non-intrusive way of providing interactive and responsive web applications has evolved. Even with AJAX, the problem is not fully solved for developers. The reason for this is the peripheral integration of AJAX in almost all the existing frameworks. Still, there are exceptions in the form of frameworks that provide AJAX-based functionalities as their core services. Ruby-on-Rails (RoR) is one of them.

In this discussion, I will be focusing on using AJAX with RoR. The first section will be a brief overview of AJAX. In the second section, I will detail the steps involved in AJAXifying the RoR application. In the last section, I will be developing a real world application using AJAX and RoR. That’s the agenda for this discussion.

AJAX: an Overview

By definition AJAX is “a technique that extends the traditional web application model to allow for in-page server requests.” In other words, AJAX provides the functionality to make on the fly requests to the server and present the response data to the user by modifying a part of the page without refreshing the whole page. AJAX is not a single technology; rather, it’s a combination of technologies. These technologies form the basic components of AJAX, which are:

1. JavaScript
2. XMLHttpRequest
3. XML

The second component is an object of JavaScript itself. However the first ‘A’ of AJAX comes from it. The reason for this is detailed below.

JavaScript is the “J” in AJAX. It is the JavaScript that parses the response from the server and makes appropriate changes to the current page. Using JavaScript, one can change a paragraph, replace a product image or change the complete look and feel of the page without the intervention of the server. This reduces the bandwidth and makes the web site look more responsive to the user.

XMLHttpRequest is an object of JavaScript that not only allows the user to create or construct HTTP calls from within the script but also process the response sent by the server. It is available as an ActiveX control in Internet Explorer and as a JavaScript object in other browsers. Though it is known as XMLHttpRequest, one can wrap any kind of HTTP communication using it.

The first ‘A’ in AJAX, which stands for “Asynchronous,” comes at the “behest” of XMLHttpRequest. One of the abilities of XMLHttpRequest is to make a call to the web server in the background while the user continues to do his/her work on the UI. Whenever the response is received, the XMLHttpRequest accepts it and passes it over to the function that is registered to handle the response. Thus the request-response cycle is relieved off the paradigm of synchronous communication.

If one looks up the definition of XML on the net, the most common definition found is this: “XML (Extensible Markup Language) is a W3C initiative that allows information and services to be encoded with meaningful structure and semantics that computers and humans can understand and is great for information exchange, and can easily be extended to include user-specified and industry-specified tags.” The extensibility and the ability to provide data in a structured format is the main reason for XML to be the preferred response data format sent by the server. This doesn’t mean that the server can’t send data in any other format. It can send data in any format and XMLHttpRequest can accept and process it. However, since XML is a standard that is both extensible and structured, it is preferred over every other.

The next obvious question is how these three components fit in the overall picture. Here is an example of AJAX at work. It checks the validity of the username.

var xmlHttp;
function createXmlHttpRequest()
{
//check the type of browser and initialize XMLHttpRequest object
if(window.ActiveXObject)
{
xmlHttp=new ActiveXObject(”Microsoft.XMLHTTP”);
}
else if(window.XMLHttpRequest)
{
xmlHttp=new XMLHttpRequest();
//alert(”inside elseif…”+xmlHttp);
}
}

//This method creates an instance of XMLHttpRequest and calls the server
//Then delegates the data processing to another function on receipt of data
function startRequest()
{
createXmlHttpRequest();

var u1=document.f1.user.value;
xmlHttp.open(”GET”,”validate.php?user=”+u1,true)
xmlHttp.onreadystatechange=handleStateChange;
xmlHttp.send(null);
}

//Does the handling of data and presentation of data
function handleStateChange()
{
if(xmlHttp.readyState==4)
{
if(xmlHttp.status==200)
{
document.getElementById(”results”).innerHTML= xmlHttp.responseText;
}
else
{
alert(”Error loading pagen”+ xmlHttp.status +”:”+ xmlHttp.statusText);
}
}
}

function resetDisplay()
{
document.getElementById(”results”).innerHTML=” ”

}

The logic here is very simple, yet the amount of boilerplate code required is pretty high and the complexity of JavaScript increases with the increase in complexity of the logic of data processing and presentation. And I have not shown the logic at server-side. This is the story with almost all of the technology except Ruby-on-Rails. How RoR is different from the others is the topic of the next section.

Implementing AJAX

In the last section, the example demonstrated the complexity involved in AJAXifying an application having even the simplest logic to be implemented. So how can RoR make it simple? RoR does it by providing AJAX as one of its core functionalities. RoR has the prototype, effects, dragdrop and controls JavaScript libraries as built-in libraries. Secondly, a helper called JavaScriptHelper provides wrapping around JavaScript code so that switching between Ruby and JavaScript won’t be necessary. So RoR makes things simpler by wrapping JavaScript libraries and providing them as built-in RoR libraries.

Now let’s look at ways to use AJAX within RoR. To implement AJAX, RoR provides two basic ways which are:

1. Using link_to_remote()
2. Calling form_remote_tag()

The former is used with non-form HTML whereas the later is used with form and form based HTML elements.

The link_to_remote() is one of the simplest yet versatile and flexible helpers in RoR. It takes following parameters:

1. the text for the link
2. the id of the link
3. the URL of the action to be called when the link is clicked
4. the position which tells Rails to insert the response instead of replacing the existing content. This is an optional parameter used with the advanced form of link_to_remote().

So what are the steps required to use link_to_remote()? The steps are as follows:

1. Create the page to be shown to the user
2. Implement the controller to be called by link_to_remote()
3. Develop the page that will provide the response

The steps are common to that of any typical RoR. That is the beauty of RoR. Of these steps, the third step is optional if rendering is taken care of by the action itself.

1. Create the page to be shown to the user:This step involves creation of an RHTML template with the addition of link_to_remote(). However, to call it, another helper will have to be included - javascript_include_tag. Using this one can include any of the four JavaScript libraries. For calling link_to_remote() we need the prototype library. So the statement would look like this:

   <%= javascript_include_tag “prototype” %>
   
   Here is an example of a page having link_to_remote():

   <html>
   <head>
   <title>Ajax Demo</title>
   <%= javascript_include_tag “prototype” %>
   </head>
   <body>
   <h1>What time is it?</h1>
   <div id=”time_div”>
   I don’t have the time, but
   <%= link_to_remote( “click here”,
   :update => “time_div”,
   :url =>{ :action => :say_when }) %>
   and I will look it up.
   </div>
   </body>
   </html>
   
   The link_to_remote() is passed three arguments: the text to be shown i.e. “click here”, the id of the link