On 6 Dec 2010, at 10:45pm, Oliver Peters wrote: > Am Montag, den 06.12.2010, 22:13 +0000 schrieb Simon Slavin: >> On 6 Dec 2010, at 10:00pm, Oliver Peters wrote: >> >>>> In general, views, triggers, and foreign key constraints can only >>>> use objects within the same schema (=database). [snip] >>> >>> what is the problem to give the programmer/user the chance to decide on >>> his own if he wants to use such a feature with all its included risks? >> >> If you allow this, there's no way to do an integrity check on the >> resulting database files because users might rename or move the files >> that act as the second database. > > I think that depends on the context you're using sqlite. In this case I > have multiple users working on one sqlitefile in a MS network and I want > them to store their individual ID in there own temporary table so that a > trigger can use these IDs.
Normally you would use a programming language for this. Just put the current user's ID into a variable in the programming language. No need to store it in a SQL file since it has no meaning after the user session is finished. > I don't see the danger here and if you see it > I don't see the necessity to be as careful as you ;-). (Please be > patient with me because I'm just a heavy user). How do you know all users attach the same two files ? Let's suppose that a TRIGGER is defined from a parent database file to a child database file. Each person on your network might choose to use a different parent file with the same child file, or vice versa. Perhaps one cannot access the network temporarily and chooses to work with files on their hard disk. As soon as one person does this you have a very messy collection of files and have to pick through them manually to try to make one file with the 'correct' data in it. Having all the data in the same database file on disk prevents any mismatch that would cause this problem. SQLite is not unusual in this. There are equivalent ways to connect two pieces of data in other SQL engines but in the cases I've seen you specify the location of the other database when you create the relationship. There's no way to switch one database without having someone with programmer access to redefine the match-up between the two. >> Also, you have to invent another >> error condition for every command that might change the database, so >> it can report that the command itself is fine but the required >> database is not attached. > > I interpret that sqlite doesn't already have such an error condition. > That let's me ask 2 questions: > > 1. what is the point against such a feature? Suppose someone uses the wrong second file. There's no way for software to recover from it. All your software can do it spit out an error code then quit: A) there is no such attached file B) the attached file has no such tables defined in it C) the tables in the attached file have no such columns in D) there is already data in the files which violate the TRIGGER requirements Without a slow startup procedure that checks everything, there's no way to check for these error conditions before the error occurs, so your user could be deep inside some complicated process for adding or editing data before the software can tell that there's a problem with the data structure. Then the user gets dumped out of the software after having already put in a bunch of details that cannot be used. Users hate this so programmers try not to allow it. > 2. why can't I live without it (in my case I have a NOT NULL and a > FOREIGN KEY constraint)? That's your choice in how you choose to set up your TRIGGERs. Unfortunately there's nothing in the SQL language to stop another programmer not understanding the problems and failing to set up the constraints correctly. >> If you want this done, you can do it in your own code. > > That is impossible because I'm not a programmer (only a little awk and > basic) I'm not sure how to respond to this. SQL is not a programming language, it's just a way to store data. It provides some small elements of intelligence, like TRIGGERs and FOREIGN KEYs, but it does these things in a fast simple lightweight way. Without a programming language in front of it, it's like blank paper and a pencil. You even have to define a TRIGGER to stop users putting strings into number fields ! If you want more flexible intelligence you're going to have to learn a programming language. You're obviously capable of it since TRIGGERs are a programming thing: if you can use those correctly you obviously understand the principles. >> Or you can >> rethink the mismatch between what you mean by 'database' and what >> SQLite means by 'database'. > > The handling of data in dbs means to be very precise if I understood the > lessons I've learned until now. So please give me enlightenment cause I > really don't understand your last sentence. A database is something like 'your data in one place'. What you are asking for is to split your data up into different files on disk. So you don't seem to want 'your data in one place'. You seem to feel that this data is all related (otherwise the TRIGGER would be impossible) but the data is not related (because you don't want to put it all in the same file on disk) ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
