[odb-users] Help, how to generate the hxx files automately by odb

[Hugo.Mildenberger at web.de]

> > > I have a database based on mysql, how can i generate all hxx files
> > > by odb not by myself manually?
> > 
> > No, this is not yet supported. We do have plans for an SQL-to-C++
> > compiler, thought.
> 
> I'd appreciate it very much if this functionality became integrated with ODB (and PostgreSQL). Such a program will foster a server-centric development style which in turn may result in a lean and consistent client - server interface, in enhanced maintainability, but also in a reduced network load if data processing is mainly done inside of stored procedures. Even updatable views spanning several tables are possible with a server-centric approach. But there would be also a great reduction of the effort required for the evaluation of a database design alternative (just run make once more to regenerate the client plugin), and unit-testing covering the whole client-server communication could be easily automated. In short, the integration of such a tool would mean a big relief to all those who have ever been forced to develop or maintain a poorly designed graphical user interface.
> 
> 
> Best 

Below is a proof of concept of this idea. odb generated the database access code for a C++ class named "telecom", which tries to accesses a table of the same name. Now the very same code runs db->persist(telecom), db->update(telecom) and db->erase(telecom) without a problem also on a view having a trigger procedure attached. I'm interested to know what you think about this approach ...

Hugo 

---

begin;

drop table if exists telecom_table cascade;
drop table if exists telecom_type cascade;
drop view  if exists telecom cascade;

create table telecom_type (
  id serial not null primary key,
  name text not null unique
);

create table telecom_table (
  type int references telecom_type(id) not null,
  priority text not null,
  uri text not null,
  purpose text not null,
  id serial not null primary key);

create view telecom 
    as select b.name as type, a.priority, a.uri, a.purpose, a.id 
	   from telecom_table as a, telecom_type as b where b.id = a.type;

create or replace function telecom_modify() returns trigger 
language plpgsql as $function$
declare
type_id integer;
begin
	case tg_op
		when 'INSERT' then
			select id  from telecom_type where name=new.type into type_id;
			if  not found  then 
				with a as (
					insert into telecom_type(name) values(new.type) returning id
				) select id from a into type_id;
			end if;
			with a as (
				 insert into telecom_table(type, priority, uri, purpose) 
				 values(type_id, new.priority, new.uri, new.purpose) returning id
			) select id from a into new.id;
			return new;
		when 'UPDATE' then
			select id  from telecom_type where name=new.type into type_id;
			if  not found  then 
				with a as (
					insert into telecom_type(name) values(new.type) returning id
				) select id from a into type_id;
			end if;
			with a as (
				 update telecom_table 
					set type = type_id, priority = new.priority, 
						 uri = new.uri, purpose  = new.purpose 
					where id = old.id returning id
			) select id from a into new.id;
			return new;
		when 'DELETE' then
			delete from telecom_table where id = old.id;
			if not found then return null; end if;
			return old;
		else
			raise EXCEPTION 'tg_op % is unknown',quote_literal(tg_op);
		end case;
	return new;
end; $function$;

create trigger telecom_trigger
       instead of insert or update or delete
               on telecom 
                  for each row 
                      execute procedure telecom_modify();

end;
-- vim:ts=4:sw=4








  



-- 
Hugo Mildenberger <hm at zotac.lan>