Re: [sqlite] delete on view

[Fabiano Sidler]

Igor Tandetnik schrieb:
> You don't have a single row in dbapp view that has fields='surname'. The 
> only record you have is one where fields='name,surname'. So no row 
> matches condition, and thus no row gets deleted. Your trigger never even 
> runs.

That's weird. So Sqlite first does read from the view before actually
perform a DELETE? And how could I then implement what I wanted my view
to do?

Thanks for your fast reply! ;)
Greetings,
Fabiano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete on view

[Fabiano Sidler]

Ok, I've removed that nested DELETE command. Now I have still the problem
that I can't delete tablenames with multiple field entries. The full sql
I have is as follows:
---
create table dbapp_tablenames (
  tablenameID integer primary key,
  tablename text not null unique on conflict ignore
);
create table dbapp_tablefields (
  tablenameID integer not null,
  tablefield text not null
);

insert into dbapp_tablenames (tablename)
  values ('employees');
insert into dbapp_tablenames (tablename)
  values ('offices');
insert into dbapp_tablefields (tablenameID,tablefield)
  select tablenameID,'administration' from dbapp_tablenames where 
tablename='offices';
insert into dbapp_tablefields (tablenameID,tablefield)
  select tablenameID,'name' from dbapp_tablenames where tablename='employees';
insert into dbapp_tablefields (tablenameID,tablefield)
  select tablenameID,'surname' from dbapp_tablenames where 
tablename='employees';

create view dbapp as
  select tablename,group_concat(tablefield) as fields 
  from dbapp_tablefields as fs
  join dbapp_tablenames as ts 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 tablefield from dbapp_tablefields f
join dbapp_tablenames t on (f.tablenameID=t.tablenameID)
where tablefield=new.fields and t.tablename=new.tablename) 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;

create trigger dbapp_delete instead of delete on dbapp begin
  delete from dbapp_tablefields where tablefield=old.fields; /* HERE */
/*
where tablenameID=(
select tablenameID from dbapp_tablenames
where tablename=old.tablename)
and tablefield=old.fields;
*/
--where tablefield=old.fields;
  delete from dbapp_tablenames
  where tablename=old.tablename and not exists (
select * from dbapp_tablefields f
where f.tablenameID = dbapp_tablenames.tablenameID
and tablefield=old.fields);
end;
---

So consider the following output of this database:
---
sqlite> select * from dbapp;
employees|name,surname
offices|administration
sqlite> delete from dbapp where tablename='offices' and fields='administration';
sqlite> select * from dbapp;
employees|name,surname
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> select * from dbapp;
employees|name,surname
---

I don't get a clue how to find the error? Or is that indeed a Sqlite bug?

Thanks for answering...
Greetings,
Fabiano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] delete on view

[Fabiano Sidler]

Hi folks!

After having UPDATE and INSERT on a view working, I'd also like to DELETE
FROM a view. I have the following tables and a view:

---
create table dbapp_tablenames (
tablenameID integer primary key,
tablename text not null unique on conflict ignore
);
create table dbapp_tablefields (
tablenameID integer not null,
tablefield text not null
);
create view dbapp as
select tablename,group_concat(tablefield) as fields
from dbapp_tablefields as fs join dbapp_tablenames as ts
on (ts.tablenameID=fs.tablenameID) group by ts.tablenameID;
create trigger dbapp_delete instead of delete on dbapp begin
delete from dbapp_tablefields where tablenameID=(
select tablenameID from dbapp_tablenames
where tablename=old.tablename
and tablefield=old.tablefield);
select case when ((
select tablefield from dbapp_tablefields f
join dbapp_tablenames t on (f.tablenameID=t.tablenameID)
where tablefield=old.fields and tablname=old.tablename) is null)
then (delete from dbapp_tablenames where tablename=old.tablename)
end;
end;
---

But the trigger produces a syntax error at "delete" in the first line.
What's wrong?

Greetings,
Fabiano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get attributes not supplied by insert

[Fabiano Sidler]

Jay A. Kreibich schrieb:
>   http://www.sqlite.org/lang_createview.html
> 
> "You cannot COPY, DELETE, INSERT or UPDATE a view. Views are
>  read-only in SQLite.  However, in many cases you can use a
>  TRIGGER on the view to accomplish the same thing."
> 
>   -j

Aha!...and...so what? I have a trigger...

Greeting,
Fabiano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] get attributes not supplied by insert

[Fabiano Sidler]

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