Hi, @Simon,
> > CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 > > CASE takes a value. You can't put a SQLite command in there. > actually, it's working if you put the command between (). I guess it's like the `` on linux (eg), mplayer `ls *.mp3` but maybe,even if it's working, you want to point that's not correct to use it. > More generally, you come from a programming background and are > expecting SQL to work like a programming language. It doesn't. that's clearly a difficulty for me... > Constructions like this > > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > > SELECT 'evil little sister' > > should be rewritten so that you are not trying to do a SELECT in the > middle of your INSERT. > > This is why we quoted the form > > INSERT OR IGNORE ...; > UPDATE ...; > > earlier. Two separate commands, not one command trying to run > another. I have to think about that... ----------------------------------------------------------------- @Ryan, > To get you started on this road, may I ask you take a leap of faith > and explain to us from A to Z what you would like to achieve with the > tables and data you are asking about above - i.e don't show us the > programming you think will solve the problem and ask us if that is > good SQL, rather explain us the problem to be solved and allow us to > show you the good SQL that will do it. > > Every next task should be clearer and easier. Actualy, it's a personal open source project. So the code could be see by anyone. The only thing is, the most recent part isn't yet on the repo (last push was before the summer). But, if for some reason you want to see the code as it is today on my computer (almost all files were heavily modified during the summer), I can push it. this project is about helping me to manage all files I have on my computer. I'm a metalhead and science nerd at the same time. So, I have tons of music and tons of ebooks and publications... So my project as currently two goals: 1) store the data on disk, I should not access the files directly. 2) using a DB to retrieve any document trough a search engine. (actually I want to add a third goal: I want to create something I don't know how I will call it, but it's something like a binder. For example if you open a binder about 'modelling demons', you can link data of differents files even if they don't share tags or whatever (can be in that example some texture, some .blend or pdf about modelling humanoid...).) So, I'm writing some custom GTKmm widgets wich interact with the DB (but they don't directly access any sqlite function, I'm working with engines communication (gtkmm_engine, sqlite_engine, etc.)). I'm currently thinking to make that program modular. For example, the user (usualy me), set what kind of data (trough some config file (in my head I call these files 'collection')) should be store in the DB. For each data will correspond a widget. Let's took an example, for ebooks: I want to save the filename (filename is ruled by the program, the user has no direct way to modify it), title, some tags and a description for each ebook (for the last three the program shows some widgets, wich allow the user to modify the values). At this point, when the program read the ebook.collection, it will create a first table EBOOK with four columns (ID,FILENAME,TITLE,DESCRIPTION) for the tags, things are more complicated. I need two tables: - TAGS with three columns (ID,NAME,COUNT) - TAGSMAP with two columns (ID_EBOOK,ID_TAGS) (I explain the use below) so if I take my old example : (id|NAME|COUNT) 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 if I'm seeking to get all files with the tags 'black metal' the program check all entries in TAGSMAP with ID_TAGS = 53, SELECT * FROM TAGSMAP WHERE ID_TAGS = 53 would returns something like, (ID_EBOOK|ID_TAGS) 3|53 5|53 9|53 then the ID_EBOOKs refer to the IDs of the data (title and description in this case) for some files in EBOOK. now my problems come when you want to add/delete a tags. If for a file you want (eg) to delete the tag 'black metal' you click on the button I setup to delete that tag, the program at this time has two informations: the current EBOOK.FILENAME and the TAGS.NAME of the tag. So I have to query the DB to get their respective IDs (ID of that FILENAME in EBOOK and ID of that NAME in TAGS). Then, the program has to delete the entry in the TAGSMAP where ID_EBOOK is the same than ID for the current file in EBOOK and where ID_TAGS equal the ID in TAGS. For TAGS, the program has first to check COUNT. Before the check I have to get the value from the DB. If COUNT = 1, the program has to delete the tag with NAME 'black metal'. If COUNT > 1, the program has to decrement count. My first manner to do that, was to just store/read the data to/from the DB. All conditions (like what I try to do for some days now...) were executed by the program, not by queries. But the problem (wich is not really a big issue), I do a lot of queries because of the IFs in my program then I get one/two seconds of lags (wich may also be here because I wrote all operation I do in the console for debug purpose...)... So, as I wrote in an earlier email, since I know I can use transaction, I try to replace all queries in one giant query send to the DB when I want to update the data for a file (title, tags and description). Maybe, I should ask if what I want to do is really possible by SQL or if I should better let the IFs in the program better than struggling like I did in my last emails... I really hope I was clear. If you need further explanation about something just tell it. Anyway thank you for the time you spend to explain your magic to some "muggle" like me... regards, Nicolas J.
