> > When I show the processlist several times in a row it is nearly > > always SELECTing some filename in the Filename table. > > That's expected - it selects to find out if the filename has > to be inserted. By the way - is there a portable way of > having unique file names, and, in case of inserting a > duplicate one, failing in a way that allows Bacula to process > the FilenameId? This might save some time. > Unfortunately, I suspect stored procedures won't work because > of major differences between SQLite, PostgreSQL and MySQL.
If there was a way to do it in a stored proc, that would probably be fine even if the syntax of the proc differed - because the proc would be part of the schema, just like table and index definitions. As long as the call syntax would be the same. However, I don't beleive sqlite does stored procs, and I doubt it's in the plans - doesn't really make sense for an embedded db. I think the standard way of doing it is using a SAVEPOINT. This is supported by postgresql and mysql 4 (with innodb, of course). Sqlite doesn't, it seems. If you're thinking of doing the INSERT->SELECT (try insert, do select if it fails) for filenames, I think you're on a bad track though. That's only good if you will in most cases do INSERT and just in some cases to SELECT. Looking at my own database, it certainly looks different - once a full backup is through, most files never change their names so they wouldn't need INSERTs. The best scheme would probably be SELECT->INSERT->SELECT (second select if the insert failed). That you can do with SAVEPOINTs (around the INSERT). Another thought - does the filename *HAVE* to be unique? You can gain a lot of concurrency if you say "I want to use the existing one in 99.99% of the cases, but I don't care about reaching 100%". Then you can do SELECT->INSERT and the INSERT will *always* succeed. In case a concurrent proces sinserted the same name, you get two ids for the same name. You could either live with that, or clean it up at the end of the job, perhaps? (or is this perhaps already done today? Since there is no unique constraint on it?) (Sorry if I'm ranting about things that are completely out of the scope you were talking about ;-)) //Magnus ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
