New answers tagged

1

In SQL, triggers can be fired "per row" or "per statement". SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing). Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which ...


1

I very much doubt it can do anything with an index starting with prices.pricegroup_id because the where relevant clause covers multiple distinct values. An index on currency_id is liable to be useful for filtering, currency_id, pricegroup_id might perform better because it can read that value for the other WHERE clause to filter out the rows before needing ...


0

Following up on the comments, one option to simplify this requirement without resorting to developing a user interface to link sound files and a database, and considering your skills in shell programming, is using command line tools to edit/retrieve tags on MP3 files (Here's a QA with plenty of resources and alternatives for reading and manipulating mp3 tags ...


1

(Moved here from an edit on the question: Reddit helped me out with this already. Eventually I used this code) fiveminaverage <- dbGetQuery(db," SELECT DATE, AVG(PRICE), MIN(TIME) AS firstSample, MAX(TIME) AS lastSample FROM currentdata WHERE DATE=19990603 GROUP BY DATE, strftime('%s', (CASE WHEN TIME LIKE '_:__:__' THEN '0' || TIME ELSE TIME END)) / (5 ...


1

You'll need to redefine your database to store that information. An example is the following: CREATE TABLE routes ( route_id int PRIMARY KEY, route_name varchar(200) ); CREATE TABLE stops ( stop_id int PRIMARY KEY, stop_name varchar(200) ); CREATE TABLE route_details ( route_id int, stop_number int, -- starting from 1 stop_id int, ...


0

I've had a look at this - am going to go back to it tomorrow, but I think you're going to have to think of every route as a number of sub-routes. Route A - F is also routes A - E, A - D.... D - E and E - F. BTW, I'm using PostgreSQL - D. Richard Hipp (the author of SQLite) has said that he uses PostgreSQL as a template for his own flavour of SQL, so it's ...


0

Finding all nodes in a subtree to update them requires a recursive common table expression: -- update the root of all nodes in 'a's subtree to 'x' WITH RECURSIVE subtree(id) AS ( VALUES('a') UNION ALL SELECT Node.id FROM Node JOIN subtree ON Node.parent = subtree.id ) UPDATE Node SET root = 'x' WHERE id IN subtree; In SQLite, CTEs are ...



Top 50 recent answers are included