SQL Nuggets

Some SQL queries that may come in handy. The queries below assume winston.prefix=W

Maintenance

Give the winston user database access:

    GRANT ALL ON `W\_%`.* to winstonuser@'localhost' identified by 'winstonpass';

Find channels which are missing their database: ```SQL SELECT code FROM W_ROOT.channels WHERE CONCAT('W_', code) NOT IN (SELECT schema_name FROM information_schema.schemata); ```

Groups

Create a new top-level channel group called "Networks":

    INSERT INTO W_ROOT.groupnodes (parent, name, open) VALUES (0, 'Networks', 1);

Create a new subgroup of Networks called "Augustine": ```SQL INSERT INTO W_ROOT.groupnodes (parent, name, open) SELECT nid, 'Augustine', 0 from groupnodes WHERE name = 'Networks'; ```

Add all broadband seismic channels at AU22 to the Augustine group: ```SQL use W_ROOT; INSERT INTO grouplinks (sid, nid) SELECT channels.sid, groupnodes.nid FROM channels, groupnodes WHERE channels.code like 'AU22$BH%' and groupnodes.name='Augustine'; ```

Remove 3 channels from the Okmok group: ```SQL use W_ROOT; DELETE FROM grouplinks WHERE nid IN (SELECT nid FROM groupnodes WHERE name='Okmok') AND sid IN (SELECT sid FROM channels WHERE code IN ('OKIF$ADC$AV', 'OKIF$BDF$AV', 'OKIF$HDF$AV')); ```