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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users