This is a framework that has been developed during the last 2 years. What was needed was a way to write pl/sql webapps using some level of abstraction for the front-end: pl/sql gateway, oracle portal portlets or other portal portlets (eg. WSRP).
So this framework enables pl/sql web developers to write an application once and then to plug it in any environment. This is accomplished leveraging the MVC pattern, like Struts or Stripes do for Java developers.
An application is composed by 3 packages:
The business logic package (Model): here the developer will write all the interaction with the data model: DML and possibly queries.
create or replace package srv_test_m
as
procedure on_modify;
procedure on_save;
procedure on_delete;
end srv_test_m;
create or replace package body srv_test_m
as
procedure on_modify
as
begin
for c_cur in (select * from srv_test_t where id= to_number(plsqlmvc.get_value('id'))) loop
plsqlmvc.set_value('name',c_cur.name);
plsqlmvc.set_value('email',c_cur.email);
end loop;
exception
when others then
htp.p(sqlerrm(sqlcode));
end on_modify;
procedure on_save
as
v_id number:= plsqlmvc.get_value('id');
begin
if v_id is null then
insert into srv_test_t values (srv_test_id_seq.nextval,plsqlmvc.get_value('name'),plsqlmvc.get_value('email'));
else
update srv_test_t
set name= plsqlmvc.get_value('name'),
email= plsqlmvc.get_value('email')
where id= v_id;
end if;
end on_save;
procedure on_delete
as
v_id number:= plsqlmvc.get_value('id');
begin
delete srv_test_t
where id= v_id;
end on_delete;
end srv_test_m;
The user intervace package (View): here will use the htp.p to print out the html interface.
create or replace package srv_test_v
as
procedure report;
procedure form;
end srv_test_v;
create or replace package body srv_test_v
as
procedure report
as
begin
plsqlmvc.show_errors;
htp.p('<table>
<tr>
<th>Select</th>
<th>Name</th>
<th>E-Mail</th>
</tr>
');
for c_cur in (select * from srv_test_t) loop
htp.p('<tr>
<td><input type="radio" value="'||c_cur.id||'" name="id"/></td>
<td>'||c_cur.name||'</td>
<td>'||c_cur.email||'</td>
</tr>');
end loop;
htp.p('</table>');
htp.p('<input type="submit" value="Add" name="event_add"/>');
htp.p('<input type="submit" value="Modify" name="event_modify"/>');
htp.p('<input type="submit" value="Delete" name="event_delete"/>');
end report;
procedure form
as
begin
plsqlmvc.show_errors;
if plsqlmvc.is_event('add') then
htp.p('<b>New</b><br/>');
else
htp.p('<b>Modify</b><br/>');
htp.p('<input type="hidden" value="'||plsqlmvc.get_value('id')||'" name="id"/>');
end if;
htp.p('Name: <input type="text" value="'||plsqlmvc.get_value('name')||'" name="name"/><br/>');
htp.p('E-Mail: <input type="text" value="'||plsqlmvc.get_value('email')||'" name="email"/><br/>');
htp.p('<input type="submit" value="Back" name="event_back"/>');
htp.p('<input type="submit" value="Save" name="event_save"/>');
end form;
end srv_test_v;
The controller package (Control): here will be the navigation and check configuration
create or replace package srv_test_c
as
procedure do(name_array plsqlmvc.vc_arr,
value_array plsqlmvc.vc_arr);
end srv_test_c;
create or replace package body srv_test_c
as
c_event_config constant plsqlmvc.vc_list_list:= plsqlmvc.vc_list_list
(
/* report */
plsqlmvc.vc_list('report','modify','form'),--after modify event on report page go to page form
plsqlmvc.vc_list('report','add','form'),--after add event on report page go to page form
/* form */
plsqlmvc.vc_list('form','save','report')--after save event on form page go to page report
);
c_check_config constant plsqlmvc.vc_list_list:= plsqlmvc.vc_list_list
(
/* report */
plsqlmvc.vc_list('report','modify','id','Select',plsqlmvc.check_exists,'You have to select a row.'), -- after modify event on report page check the the http parameter id exists else return an error message : 'You have to select a row.'
plsqlmvc.vc_list('report','delete','id','Select',plsqlmvc.check_exists,'You have to select a row.'),-- after delete event on report page check the the http parameter id exists else return an error message : 'You have to select a row.'
/* form */
plsqlmvc.vc_list('form','save','name','Name',plsqlmvc.check_notnull), -- another example of builtin checks
plsqlmvc.vc_list('form','save','email','E-Mail',plsqlmvc.check_email) – email built-in check
);
procedure do(name_array plsqlmvc.vc_arr,
value_array plsqlmvc.vc_arr)
as
begin
plsqlmvc_gw_session.init_browser_session;
owa_util.http_header_close;
mytemplate.header('Test service');
plsqlmvc_gw_request.load_request(name_array,value_array);
plsqlmvc.show('srv_test_v',
'srv_test_m',
'report',
c_event_config,
c_check_config);
mytemplate.footer;
end do;
end srv_test_c;
Plug your application behind any front end (gateway or portlets).
Event navigation: navigate the application using the configuration and user generated events
Validators: use built in validators to check data integrity or implement your custom checks (custom pl/sql function check)
WSRP 2.0 support (experimental)
Upload and download files via mod_plsql gateway document table (http or wsrp).
Scaffolding (documentation needed)
Transport (service export / import) (documentation needed)
UI Components via xml templates in XDB (documentation needed)
User feedback will drive development, submit your requests:
http://sourceforge.net/tracker/?group_id=234975
Source (last release):
http://sourceforge.net/projects/plsqlmvc/files/plsqlmvc/initial/plsqlmvc.zip/download
Create a database user to contain the sample application
Download sql files: http://sourceforge.net/projects/plsqlmvc/files/plsqlmvc/initial/plsqlmvc.zip/download
sqlplus myuser/myuser @install /public/plsqlmvc/myuser/ myuser/myuser (read the INSTALL.txt)
Test it: http://servername:port/dad/!newuser.srv_test_c.do
To test it via WSRP
use a consumer of your choice or my test wsrp consumer module for Drupal http://sourceforge.net/projects/drupalwsrp
sqlplus myuser/myuser @plsqlmvc_wsrp_impl
sqlplus myuser/myuser @enable_wsrp_impl
enjoy
If you get any other problem please submit a support request:
http://sourceforge.net/tracker/?group_id=234975
plsqlmvc is developed by Andrea A.A. Gariboldi