Hi!
i am building a testsuite for my user administration application
for debian-edu. the database i implement stores the information
that is first read in from some example ldap dumps (LDIFs) and
then provides this (and inforamtion which is added later on) to
the test scripts.
this is my database structure:
CREATE TABLE e_user ( u_id INTEGER NOT NULL PRIMARY KEY,
u_name VARCHAR2(128),
u_login VARCHAR2(128) UNIQUE
);
CREATE TABLE e_group ( g_id INTEGER NOT NULL PRIMARY KEY,
g_name VARCHAR2(128) UNIQUE,
g_type INTEGER NOT NULL
);
CREATE TABLE r_is_group_member ( m_uid INTEGER NOT NULL,
m_gid INTEGER NOT NULL
);
CREATE TABLE e_group_type ( t_id INTEGER NOT NULL PRIMARY KEY,
t_name VARCHAR2(128) UNIQUE
);
CREATE TABLE e_password (p_uid INTEGER NOT NULL UNIQUE,
p_pwd VARCHAR2(128)
);
This join shows how the ids are connected:
my $sql_join= "FROM e_user AS u
LEFT OUTER JOIN r_is_group_member AS m ON u.u_id = m.m_uid
LEFT OUTER JOIN e_group AS g ON g.g_id = m.m_gid
LEFT OUTER JOIN e_group_type AS t ON t.t_id = g.g_type
LEFT OUTER JOIN e_password AS p ON u.u_id = p.p_uid ";
because i need to generate sql queries automatically to
accomodate very differnt test scenarios i would like to do
all my queries on a join of those tables. and because it is well
possible that there are users without membership in any groups
and groups without any members (and even users without (known,
non-encrypted) passwords), every one of those tabels could be
empty for a given joining id.
of cause for that reason i would really need an FULL OUTER JOIN
instead of LEFT OUTER JOIN.
now i am a sql newby and dont know how i could work around
sqlite`s limitation of not haveing a FULL OUTER JOIN with clever
combinations of LEFT OUTER JOINs (is that possible?). Because i
the above example e_user *allways* must be present, resticting
search results on groups to those witch have at least on group
member. so, do i need to construct different sql boilerplates for
group queries or is there a way to make even e_user optional?
