Index: /branches/pap_mops/ippTools/share/pubtool_advance.sql
===================================================================
--- /branches/pap_mops/ippTools/share/pubtool_advance.sql	(revision 24392)
+++ /branches/pap_mops/ippTools/share/pubtool_advance.sql	(revision 24392)
@@ -0,0 +1,35 @@
+SELECT
+    pub_id
+FROM ((
+    -- Get number of files to do
+    SELECT
+        pub_id,
+        COUNT(*) AS num_todo
+    FROM publishRun
+    JOIN publishClient USING(client_id)
+    JOIN diffRun
+        ON diffRun.diff_id = publishRun.stage_id
+    JOIN diffSkyfile USING(diff_id)
+    WHERE publishClient.stage = 'diff'
+    GROUP BY pub_id
+    ) AS publishDiffs UNION (
+    SELECT
+        pub_id,
+        1 AS num_todo
+    FROM publishRun
+    JOIN publishClient USING(client_id)
+    WHERE publishClient.stage = 'camera'
+    ) AS publishDiffs
+) AS publishToDo
+JOIN (
+    -- Get number of files done
+    SELECT
+        pub_id,
+        COUNT(*) AS num_done
+    FROM publishRun
+    JOIN publishFile USING(pub_id)
+    WHERE publishFile.fault = 0
+    GROUP BY pub_id
+    ) AS publishDone USING(pub_id)
+WHERE num_todo = num_done
+
Index: /branches/pap_mops/ippTools/share/pubtool_definerun.sql
===================================================================
--- /branches/pap_mops/ippTools/share/pubtool_definerun.sql	(revision 24392)
+++ /branches/pap_mops/ippTools/share/pubtool_definerun.sql	(revision 24392)
@@ -0,0 +1,28 @@
+-- Get runs to publish
+SELECT
+    client_id,
+    stage_id
+FROM (
+    -- Get diffs to publish
+    SELECT
+        client_id,
+        diff_id AS stage_id
+    FROM publishClient
+    JOIN diffRun
+    WHERE publishClient.stage = 'diff'
+        AND diffRun.state = 'full'
+    -- WHERE hook %s
+    ) AS publishDiffs UNION (
+    -- Get cameras to publish
+    SELECT
+        client_id,
+        cam_id AS stage_id
+    FROM publishClient
+    JOIN camRun
+    WHERE publishClient.stage = 'camera'
+        AND camRun.state = 'full'
+    -- WHERE hook %s
+    ) AS publishCams
+-- Only get stuff that hasn't been published
+LEFT JOIN publishRun USING(client_id, stage_id)
+WHERE publishRun.client_id IS NOT NULL
Index: /branches/pap_mops/ippTools/share/pubtool_pending.sql
===================================================================
--- /branches/pap_mops/ippTools/share/pubtool_pending.sql	(revision 24392)
+++ /branches/pap_mops/ippTools/share/pubtool_pending.sql	(revision 24392)
@@ -0,0 +1,35 @@
+SELECT
+    publishRun.pub_id,
+    publishRun.stage_id,
+    publishClient.destination,
+    publishClient.stage,
+    publishClient.format,
+    publishToDo.component
+FROM publishRun
+JOIN publishClient USING(client_id)
+JOIN ((
+    SELECT
+        pub_id,
+        diff_id AS stage_id,
+        skycell_id AS component
+    FROM publishRun
+    JOIN publishClient USING(client_id)
+    JOIN diffRun
+        ON diffRun.diff_id = publishRun.stage_id
+    JOIN diffSkyfile USING(diff_id)
+    WHERE publishClient.stage = 'diff'
+    ) AS publishDiffs UNION (
+    SELECT
+        pub_id,
+        cam_id AS stage_id,
+        NULL AS component
+    FROM publishRun
+    JOIN publishClient USING(client_id)
+    JOIN camRun
+        ON camRun.cam_id = publishRun.stage_id
+    WHERE publishClient.stage = 'camera'
+    ) AS publishCams
+) AS publishToDo USING(pub_id, stage_id)
+LEFT JOIN publishFile USING(pub_id, component)
+WHERE publishRun.state = 'new'
+    AND publishFile.pub_id IS NULL