Hi folks!
I'm building a tool (with PyQt) which automatically generates menues and
forms for maintaining data on a database. Instead of hardcoding the names
of tables and fields into the code, i'd like to store this info into the
db itself. So my tool shall gather the info by the following statement
SELECT tablename,fields FROM dbapp;
with the result
users|username,prename,surname
bureaus|nr,username
based on which my tool will generate two forms for maintaing users and
bureaus and their attributes.
I therefore wrote that SQL script:
---
create table dbapp_tablenames (
tablenameID integer primary key,
tablename text not null unique
);
create table dbapp_tablefields (
tablenameID integer not null,
tablefield text not null
);
insert into dbapp_tablenames (tablename) values ('users');
insert into dbapp_tablenames (tablename) values ('bureaus');
insert into dbapp_tablefields (tablenameID,tablefield) values (1,'username');
insert into dbapp_tablefields (tablenameID,tablefield) values (1,'prename');
insert into dbapp_tablefields (tablenameID,tablefield) values (1,'surname');
insert into dbapp_tablefields (tablenameID,tablefield) values (2,'nr');
insert into dbapp_tablefields (tablenameID,tablefield) values (2,'username');
create view dbapp as
select tablename,group_concat(tablefield) as fields from
dbapp_tablenames as ts
left join dbapp_tablefields as fs on (ts.tablenameID=fs.tablenameID)
group by ts.tablenameID;
create trigger dbapp_insert instead of insert on dbapp begin
select raise(rollback, 'field already in table')
where (
select * from dbapp_tablefields
where tablefield=new.fields and tablenameID=new.tablenameID
) is not null;
insert into dbapp_tablenames (tablename) values (new.tablename);
insert into dbapp_tablefields (tablenameID, tablefield)
select tablenameID,new.fields from dbapp_tablenames
where tablename=new.tablename;
end;
---
SELECTing from dbapp is not the problem, I get the desired output. What
doesn't work is INSERTing into dbapp! I'd like to add fields via
INSERT INTO dbapp (tablename,fields) VALUES ('bureaus','someattr');
which gives me the following logical error message
SQL error: no such column: new.tablenameID
How can I get the correct tablenameID for tablefield to be inserted?
I hope this is was not too difficult to understand. If somethin is unclear,
please ask me for making clear again the above...
Greetings,
Fabiano
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
