cascade insert into master detail

Oleg Noga <[email protected]> Tue, 4 Dec 2007 19:17:22 +0200

Newsgroups	gmane.comp.gnu.enterprise.general
Message-ID	<[email protected]>

Hello Reinhard,

Is it possible to insert new row to master and some rows to detailt in
same time?

Database is test.sql

spr_user_group.gfd:

When i am only inserting new spr_user row it is ok.
When i am only inserting new spr_usergroup row it is ok.
When i am trying to insert a user with newly entered group list i have
exception:

--------------------------
An unexpected error has occured:
null value in column "usergroup_user_id" violates not-null constraint
.
Please contact your system administrator.

INSERT INTO spr_usergroup (usergroup_group_id) VALUES
(%(new_usergroup_group_id)s)

  new_usergroup_group_id: 1
--------------------------

I have added 'primarykey' attribute to ds_spr_user datasource
  
spr_user.gdf:

When trying to insert new row have error:

INSERT INTO spr_user (user_login, user_id, user_sign, user_name) VALUES
(%(new_user_login)s, %(new_user_id)s, %(new_user_sign)s, %(new_user_name)s)

  new_user_login: u'1'
  new_user_sign: u'1'
  new_user_id: None
  new_user_name: u'1'

What is 'rowid' datasource attribute?
How datasource behaviour depends on primarykey, primarykeyseq and
rowid attributes defined or not?

-- 
Best regards,
 Oleg                          mailto:[email protected]

_______________________________________________
Gnue mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/gnue

test.sql (text/plain, 6 KB)

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: spr_group; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE spr_group (
    group_id integer NOT NULL,
    group_name character varying(255) NOT NULL,
    group_sysname character varying(50) NOT NULL
);


ALTER TABLE public.spr_group OWNER TO postgres;

--
-- Name: spr_group_group_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE spr_group_group_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.spr_group_group_id_seq OWNER TO postgres;

--
-- Name: spr_group_group_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE spr_group_group_id_seq OWNED BY spr_group.group_id;


--
-- Name: spr_group_group_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('spr_group_group_id_seq', 6, true);


--
-- Name: spr_user; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE spr_user (
    user_id integer NOT NULL,
    user_login character varying(25) NOT NULL,
    user_password character varying(250),
    user_name character varying(250) NOT NULL,
    user_sign character varying(250) NOT NULL
);


ALTER TABLE public.spr_user OWNER TO postgres;

--
-- Name: spr_usergroup; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE spr_usergroup (
    usergroup_id integer NOT NULL,
    usergroup_user_id integer NOT NULL,
    usergroup_group_id integer NOT NULL
);


ALTER TABLE public.spr_usergroup OWNER TO postgres;

--
-- Name: spr_user_group_user_group_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE spr_user_group_user_group_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.spr_user_group_user_group_id_seq OWNER TO postgres;

--
-- Name: spr_user_group_user_group_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE spr_user_group_user_group_id_seq OWNED BY spr_usergroup.usergroup_id;


--
-- Name: spr_user_group_user_group_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('spr_user_group_user_group_id_seq', 48, true);


--
-- Name: spr_user_user_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE spr_user_user_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.spr_user_user_id_seq OWNER TO postgres;

--
-- Name: spr_user_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE spr_user_user_id_seq OWNED BY spr_user.user_id;


--
-- Name: spr_user_user_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('spr_user_user_id_seq', 37, true);


--
-- Name: group_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE spr_group ALTER COLUMN group_id SET DEFAULT nextval('spr_group_group_id_seq'::regclass);


--
-- Name: user_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE spr_user ALTER COLUMN user_id SET DEFAULT nextval('spr_user_user_id_seq'::regclass);


--
-- Name: usergroup_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE spr_usergroup ALTER COLUMN usergroup_id SET DEFAULT nextval('spr_user_group_user_group_id_seq'::regclass);


--
-- Data for Name: spr_group; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY spr_group (group_id, group_name, group_sysname) FROM stdin;
1	Administrator	ADMINISTRATOR
2	Manager	MANAGER
3	Storeman	STOREMAN
4	Analyst	ANALYST
5	Guest	GUEST
\.


--
-- Data for Name: spr_user; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY spr_user (user_id, user_login, user_password, user_name, user_sign) FROM stdin;
2	manager	d41d8cd98f00b204e9800998ecf8427e	Manager	-
1	admin	d41d8cd98f00b204e9800998ecf8427e	Admin	-
3	storeman	d41d8cd98f00b204e9800998ecf8427e	Storeman	-
4	analyst	d41d8cd98f00b204e9800998ecf8427e\n	Analyst	-
5	boss	d41d8cd98f00b204e9800998ecf8427e	Boss	-
\.


--
-- Data for Name: spr_usergroup; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY spr_usergroup (usergroup_id, usergroup_user_id, usergroup_group_id) FROM stdin;
14	1	1
18	3	3
20	4	4
22	5	2
24	5	3
28	5	4
\.


--
-- Name: spr_group_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY spr_group
    ADD CONSTRAINT spr_group_pkey PRIMARY KEY (group_id);


--
-- Name: spr_user_group_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY spr_usergroup
    ADD CONSTRAINT spr_user_group_pkey PRIMARY KEY (usergroup_id);


--
-- Name: spr_user_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY spr_user
    ADD CONSTRAINT spr_user_pkey PRIMARY KEY (user_id);


--
-- Name: idx_group_id; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idx_group_id ON spr_usergroup USING btree (usergroup_group_id);


--
-- Name: idx_user_id; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idx_user_id ON spr_usergroup USING btree (usergroup_user_id);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

spr_user_group.gfd (text/xml, 1.8 KB)

<?xml version="1.0"?>

<!--  GFD generator  -->

<form name='spr_user' title="Users and groups">

	<datasource name='ds_spr_user' connection='sales' prequery='Y'
		table = 'spr_user'
	/>

	<datasource name='ds_spr_usergroup' connection='sales' prequery='Y'
		table = 'spr_usergroup'

		master="ds_spr_user"
		masterlink="user_id"
		detaillink="usergroup_user_id"

	/>

	<datasource name='ds_spr_group' connection='sales' prequery='Y'
		table = 'spr_group'
	/>

<logic>

	<block name='b_spr_user' datasource='ds_spr_user' rows = '5'>
		<field name = 'id'        field = 'user_id'           />
		<field name = 'login'     field = 'user_login'        />
		<field name = 'password'  field = 'user_password'     />
		<field name = 'name'      field = 'user_name'         />
		<field name = 'sign'      field = 'user_sign'         />
	</block>

	<block name='b_spr_usergroup' datasource='ds_spr_usergroup' rows = '5'>
		<field name = 'id'     	  field = 'usergroup_id'     />
		<field name = 'user_id'   field = 'usergroup_user_id'           />
		<field name = 'group_id'  field = 'usergroup_group_id'                fk_source='ds_spr_group'   fk_description="group_name" fk_key="group_id" />
	</block>

</logic>

<layout>
<page name="page">
	<vbox>
		<hbox block='b_spr_user' label='User'> 
			<vbox><label text='Name'/> <entry field = 'name' /></vbox>
			<vbox><label text='Login'/><entry field = 'login'/></vbox>
			<vbox><label text='Sign'/> <entry field = 'sign' /></vbox>
			<!--entry field = 'id'            label = 'id'         style='label' /-->
		</hbox>
		<vbox block='b_spr_usergroup' label='Groups'>
			<label text='Group list'/><entry field = 'group_id'/>
			<!--entry field = 'user_id'    label = 'user_id'/-->
		</vbox>
	</vbox>
</page>   
</layout>

</form>

spr_user.gfd (text/xml, 1022 B)

<?xml version="1.0"?>

<!--  GFD generator  -->

<form name='spr_user' title="Users only">

	<datasource name='ds_spr_user' connection='sales' prequery='Y'
		table = 'spr_user'
		primarykey = 'user_id'
	/>

<logic>

	<block name='b_spr_user' datasource='ds_spr_user' rows = '5'>
		<field name = 'id'        field = 'user_id'           />
		<field name = 'login'     field = 'user_login'        />
		<field name = 'password'  field = 'user_password'     />
		<field name = 'name'      field = 'user_name'         />
		<field name = 'sign'      field = 'user_sign'         />
	</block>

</logic>

<layout>
<page name="page">
	<vbox>
		<hbox block='b_spr_user' label='User'> 
			<vbox><label text='Name'/> <entry field = 'name' /></vbox>
			<vbox><label text='Login'/><entry field = 'login'/></vbox>
			<vbox><label text='Sign'/> <entry field = 'sign' /></vbox>
			<!--entry field = 'id'            label = 'id'         style='label' /-->
		</hbox>
	</vbox>
</page>   
</layout>

</form>