Index: trunk/MagicSoft/Mars/mjobs/JobsLinkDef.h
===================================================================
--- trunk/MagicSoft/Mars/mjobs/JobsLinkDef.h	(revision 8990)
+++ trunk/MagicSoft/Mars/mjobs/JobsLinkDef.h	(revision 8996)
@@ -6,4 +6,5 @@
 
 #pragma link C++ class MSequence+;
+#pragma link C++ class MSequenceSQL+;
 #pragma link C++ class MDataSet+;
 
Index: trunk/MagicSoft/Mars/mjobs/MSequence.cc
===================================================================
--- trunk/MagicSoft/Mars/mjobs/MSequence.cc	(revision 8990)
+++ trunk/MagicSoft/Mars/mjobs/MSequence.cc	(revision 8996)
@@ -171,5 +171,5 @@
 //
 /////////////////////////////////////////////////////////////////////////////
-#include "MSequence.h"
+#include "MSequenceSQL.h"
 
 #include <stdlib.h>
@@ -575,9 +575,8 @@
 // --------------------------------------------------------------------------
 //
-// Get LightCondition from resource file and convert it to LIghtCondition_t
-//
-MSequence::LightCondition_t MSequence::ReadLightCondition(TEnv &env, const char *prefix) const
-{
-    TString str = GetEnvValue2(env, prefix, "LightConditions", "n/a");
+// Get LightCondition from str and convert it to LightCondition_t
+//
+MSequence::LightCondition_t MSequence::GetLightCondition(const TString &str) const
+{
     if (!str.CompareTo("n/a", TString::kIgnoreCase))
         return kNA;
@@ -634,9 +633,11 @@
     fPeriod    = GetEnvValue2(env, prefix, "Period",    -1);
 
-    fLightCondition = ReadLightCondition(env, prefix);
-
     TString str;
+    str = GetEnvValue2(env, prefix, "LightConditions", "n/a");
+    fLightCondition = GetLightCondition(str);
+
     str = GetEnvValue2(env, prefix, "Start", "");
     fStart.SetSqlDateTime(str);
+
     str = GetEnvValue2(env, prefix, "Night", "");
     str += " 00:00:00";
@@ -667,4 +668,9 @@
         env.PrintUntouched();
     }
+}
+
+MSequence::MSequence(const char *fname, UInt_t seq)
+{
+    *this = MSequenceSQL(fname, seq);
 }
 
@@ -875,6 +881,8 @@
         return;
     }
-    out << "# Path: " << GetFileName() << endl;
-    out << "# Name: " << GetBaseName() << endl;
+    if (!fFileName.IsNull())
+        out << "# FileName: " << fFileName << endl;
+    if (!fDataPath.IsNull())
+        out << "# DataPath: " << fDataPath << endl;
     out << endl;
     if (pre.IsNull())
@@ -885,5 +893,5 @@
         out << pre << "Period:         " << fPeriod << endl;
     if (fNight!=MTime())
-        out << pre << "Night:          " << fNight << endl;
+        out << pre << "Night:          " << fNight.GetStringFmt("%Y-%m-%d") << endl;
     out << endl;
     out << pre << "LightCondition: ";
@@ -894,9 +902,9 @@
     case kTwilight: out << "Twilight" << endl; break;
     case kMoon:     out << "Moon" << endl;     break;
-    case kDay:      out << "Day" << endl;     break;
+    case kDay:      out << "Day" << endl;      break;
     }
 
     if (fStart!=MTime())
-        out << pre << "Start:          " << fStart << endl;
+        out << pre << "Start:          " << fStart.GetSqlDateTime() << endl;
     if (fLastRun>=0)
         out << pre << "LastRun:        " << fLastRun << endl;
Index: trunk/MagicSoft/Mars/mjobs/MSequence.h
===================================================================
--- trunk/MagicSoft/Mars/mjobs/MSequence.h	(revision 8990)
+++ trunk/MagicSoft/Mars/mjobs/MSequence.h	(revision 8996)
@@ -14,4 +14,5 @@
 class MSequence : public MParContainer
 {
+    friend class MSequenceSQL;
 public:
     enum LightCondition_t { kNA, kNoMoon, kTwilight, kMoon, kDay };
@@ -67,5 +68,5 @@
     void    Split(const TEnv &env, const TString &prefix, const char *name, TArrayI &data, TArrayI &sub) const;
 
-    LightCondition_t ReadLightCondition(TEnv &env, const char *prefix) const;
+    LightCondition_t GetLightCondition(const TString &str) const;
 
     // Helper for file setup
@@ -92,5 +93,5 @@
 public:
     MSequence() : fSequence((UInt_t)-1), fLastRun((UInt_t)-1),
-        fNumEvents((UInt_t)-1), fPeriod((UInt_t)-1), fMonteCarlo(kFALSE)
+        fNumEvents((UInt_t)-1), fPeriod((UInt_t)-1), fLightCondition(kNA), fMonteCarlo(kFALSE)
     {
         fName  = "MSequence";
@@ -98,12 +99,17 @@
     }
     MSequence(const char *fname, const char *path="", UInt_t id=(UInt_t)-1);
+    MSequence(const char *fname, UInt_t seq);
     MSequence(const MSequence &s) : MParContainer(s),
         fFileName(s.fFileName), fDataPath(s.fDataPath),
         fSequence(s.fSequence), fStart(s.fStart),
         fLastRun(s.fLastRun), fNumEvents(s.fNumEvents), fPeriod(s.fPeriod),
-        fNight(s.fNight), fProject(s.fProject), fSource(s.fSource),
+        fNight(s.fNight), fLightCondition(s.fLightCondition),
+        fProject(s.fProject), fSource(s.fSource),
         fTriggerTable(s.fTriggerTable), fHvSettings(s.fHvSettings),
-        fRuns(s.fRuns), fCalRuns(s.fCalRuns), fPedRuns(s.fPedRuns),
-        fDatRuns(s.fDatRuns), fMonteCarlo(s.fMonteCarlo) { }
+        fRuns(s.fRuns), fRunsSub(s.fRunsSub), fCalRuns(s.fCalRuns),
+        fCalRunsSub(s.fCalRunsSub), fPedRuns(s.fPedRuns),
+        fPedRunsSub(s.fPedRunsSub), fDatRuns(s.fDatRuns),
+        fDatRunsSub(s.fDatRunsSub), fExclRuns(s.fExclRuns),
+        fExclRunsSub(s.fExclRunsSub), fMonteCarlo(s.fMonteCarlo) { }
 
     // I/O
Index: trunk/MagicSoft/Mars/mjobs/MSequenceSQL.cc
===================================================================
--- trunk/MagicSoft/Mars/mjobs/MSequenceSQL.cc	(revision 8996)
+++ trunk/MagicSoft/Mars/mjobs/MSequenceSQL.cc	(revision 8996)
@@ -0,0 +1,336 @@
+/* ======================================================================== *\
+!
+! *
+! * This file is part of MARS, the MAGIC Analysis and Reconstruction
+! * Software. It is distributed to you in the hope that it can be a useful
+! * and timesaving tool in analysing Data of imaging Cerenkov telescopes.
+! * It is distributed WITHOUT ANY WARRANTY.
+! *
+! * Permission to use, copy, modify and distribute this software and its
+! * documentation for any purpose is hereby granted without fee,
+! * provided that the above copyright notice appear in all copies and
+! * that both that copyright notice and this permission notice appear
+! * in supporting documentation. It is provided "as is" without express
+! * or implied warranty.
+! *
+!
+!
+!   Author(s): Thomas Bretz, 7/2008 <mailto:tbretz@astro.uni-wuerzburg.de>
+!
+!   Copyright: MAGIC Software Development, 2004-2008
+!
+!
+\* ======================================================================== */
+
+/////////////////////////////////////////////////////////////////////////////
+//
+//  MSequenceSQL
+//
+// This is an extension to retrieve a sequence from a database instead
+// of readin it from a file
+//
+// Example 1:
+//    MSQLMagic serv("sql.rc");
+//    MSequenceSQL s(serv, 100002))
+//    if (s.IsValid())
+//        s.Print();
+//
+// Example 2:
+//    MSequenceSQL s("sql.rc", 100002);
+//    if (s.IsValid())
+//        s.Print();
+//
+// Example 2:
+//    MSequence s = MSequenceSQL("sql.rc", 100002);
+//    if (s.IsValid())
+//        s.Print();
+//
+// Instead of initializing the constructor directly you can
+// also use the function GetFromDatabase.
+//
+// Instead of a resource file sql.rc you can also give the qualified
+// path to the database:
+//
+//    mysql://user:password@url/database
+//
+//
+// This tool will work from Period017 (2004_05_17) on...
+//
+/////////////////////////////////////////////////////////////////////////////
+#include "MSequenceSQL.h"
+
+#include <TSQLRow.h>
+#include <TSQLResult.h>
+
+#include "MLogManip.h"
+
+#include "MSQLMagic.h"
+
+ClassImp(MSequenceSQL);
+
+using namespace std;
+
+// --------------------------------------------------------------------------
+//
+// get the list of runs according to the query. Add the runs to run-list
+// according to the type. Stop adding runs if the given number of events
+// is exceeded.
+//
+// return kFALSE in case of error or if no events are in the files.
+//
+Bool_t MSequenceSQL::GetRuns(MSQLMagic &serv, TString query, char type, UInt_t nevts)
+{
+    TSQLResult *res = serv.Query(query);
+    if (!res)
+        return kFALSE;
+
+    UInt_t cnt=0;
+    UInt_t evts=0;
+
+    TSQLRow *row=0;
+    while ((row = res->Next()) && evts<nevts)
+    {
+        AddFile(atoi((*row)[0]), 0, type);
+        evts += atoi((*row)[1]);
+        cnt++;
+    }
+
+    delete res;
+
+    if (cnt==0)
+    {
+        *fLog << err << "ERROR - No " << type << " belonging to this sequence found." << endl;
+        return kFALSE;
+    }
+
+    return kTRUE;
+}
+
+// --------------------------------------------------------------------------
+//
+// Get start and stop time as requested and return stop time if start
+// time is invalid. Return a null string in case of an error or no valid time.
+//
+TString MSequenceSQL::GetTimeFromDatabase(MSQLMagic &serv, const TString &query)
+{
+    TSQLResult *res = serv.Query(query);
+    if (!res)
+        return "";
+
+    // FIXME: Check for the right number of results?
+
+    TSQLRow *row=res->Next();
+
+    const TString start = (*row)[0] ? (*row)[0] : "";
+    const TString stop  = (*row)[1] ? (*row)[1] : "";
+
+    delete res;
+
+    if (!start.IsNull())
+        return start;
+
+    *fLog << warn << "WARNING - Requesting start time of first calibration run failed." << endl;
+
+    if (!stop.IsNull())
+        return stop;
+
+    *fLog << err << "ERROR - Neither start nor stop time of first calibration could be requested." << endl;
+    return "";
+}
+
+// --------------------------------------------------------------------------
+//
+// Convert the result into the MSequence data members
+//
+Bool_t MSequenceSQL::GetFromDatabase(MSQLMagic &serv, TSQLRow &data)
+{
+    // Convert the keys from the sequence table to strings
+    TString str[6];
+    str[0] = serv.QueryNameOfKey("Project",         data[1]);
+    str[1] = serv.QueryNameOfKey("Source",          data[2]);
+    str[2] = serv.QueryNameOfKey("L1TriggerTable",  data[3]);
+    str[3] = serv.QueryNameOfKey("L2TriggerTable",  data[4]);
+    str[4] = serv.QueryNameOfKey("HvSettings",      data[5]);
+    str[5] = serv.QueryNameOfKey("LightConditions", data[6]);
+
+    // check that all strings are valid
+    if (str[0].IsNull() || str[1].IsNull() || str[2].IsNull() || str[3].IsNull() || str[4].IsNull() || str[5].IsNull())
+        return kFALSE;
+
+    // Setup the time and night
+    MTime time;
+    time.SetSqlDateTime(data[7]);
+    MTime night = time.GetDateOfSunrise();
+
+    SetNight(night.GetStringFmt("%Y-%m-%d"));
+
+    // set the other values
+    fStart         = time;
+    fLastRun       = atoi(data[0]);
+    fNumEvents     = atoi(data[8]);
+    fProject       = str[0];
+    fSource        = str[1];
+    fTriggerTable  = str[2]+":"+str[3];
+    fHvSettings    = str[4];
+    fLightCondition = GetLightCondition(str[5]);
+
+    // MISSING: ---> ZdMin, ZdMax, fComment, fMonteCarlo <---
+
+    // Now prepare queries to request the runs from the database
+    TString where(Form(" FROM RunData WHERE fSequenceFirst=%d AND fExcludedFDAKEY=1"
+                       " AND fRunTypeKEY%%s", fSequence));
+
+    const TString query1(Form("SELECT fRunNumber, fNumEvents %s", where.Data()));
+    const TString query2(Form("SELECT fRunStart, fRunStop %s", where.Data()));
+
+    const TString queryA(Form(query1.Data(), " BETWEEN 2 AND 4 ORDER BY fRunNumber"));
+    const TString queryC(Form(query1.Data(), "=4 ORDER BY fRunNumber"));
+    const TString queryD(Form(query1.Data(), "=2 ORDER BY fRunNumber"));
+    const TString queryT(Form(query2.Data(), "=4 ORDER BY fRunNumber LIMIT 1"));
+
+    // Try to get a valid time for the first calibration run
+    const TString timec = GetTimeFromDatabase(serv, queryT);
+    if (timec.IsNull())
+        return kFALSE;
+
+    // Query to get pedestal runs sorted in the order by distance to the cal run
+    const TString query4(Form("=3 ORDER BY ABS(TIME_TO_SEC(TIMEDIFF(fRunStop,'%s'))) ASC", timec.Data()));
+    const TString queryP(Form(query1.Data(), query4.Data()));
+
+    // get and setup runs
+    if (!GetRuns(serv, queryA))
+        return kFALSE;
+    if (!GetRuns(serv, queryC, 'C'))
+        return kFALSE;
+    if (!GetRuns(serv, queryP, 'P', 1000))
+        return kFALSE;
+    if (!GetRuns(serv, queryD, 'D'))
+        return kFALSE;
+
+    return kTRUE;
+}
+
+// --------------------------------------------------------------------------
+//
+// This is a wrapper to get rid of the deletion of res in case of error.
+// It also checks that exactly one sequence has been found and all
+// requested column were returned.
+//
+Bool_t MSequenceSQL::GetFromDatabase(MSQLMagic &serv, TSQLResult &res)
+{
+    if (res.GetRowCount()==0)
+    {
+        *fLog << err << "ERROR - Requested Sequence not found in database." << endl;
+        return kFALSE;
+    }
+
+    if (res.GetRowCount()>1)
+    {
+        *fLog << err << "ERROR - Database request returned unexpected number of rows." << endl;
+        return kFALSE;
+    }
+
+    if (res.GetFieldCount()!=11)
+    {
+        *fLog << err << "ERROR - Database request returned unexpected number of rows." << endl;
+        return kFALSE;
+    }
+
+    TSQLRow *row = res.Next();
+
+    return row ? GetFromDatabase(serv, *row) : kFALSE;
+}
+
+// --------------------------------------------------------------------------
+//
+// Set the contents of a sequence from the database.
+// The sequence number is given as argument.
+//
+// Example 1:
+//    // Define the database url
+//    MSQLMagic serv("mysql://user:password@url/Database");
+//    MSequenceSQL s;
+//    s.GetFromDatabase(serv, 100002);
+//    if (s.IsValid())
+//        s.Print();
+//
+// Example 2:
+//    // Define the database in the resource file
+//    MSQLMagic serv("sql.rc");
+//    MSequenceSQL s;
+//    s.GetFromDatabase(serv, 100002);
+//    if (s.IsValid())
+//        s.Print();
+//
+//  In case of error the Sequence is set invalid and kFALSE is returned,
+//  kTrue in case of success.
+//
+Bool_t MSequenceSQL::GetFromDatabase(MSQLMagic &serv, Int_t sequno)
+{
+    // Check if we are connected to the sql server
+    if (!serv.IsConnected())
+    {
+        *fLog << err << "ERROR - No connection to database in GetSeqFromDatabase." << endl;
+        return kFALSE;
+    }
+
+    // check if any sequence number os avlid
+    if (sequno<0 && fSequence<0)
+    {
+        *fLog << err << "ERROR - No sequence number given in GetSeqFromDatabase." << endl;
+        return kFALSE;
+    }
+
+    // set "filename" and sequence number
+    fFileName = serv.GetName();
+    if (sequno>=0)
+        fSequence = sequno;
+
+    // get sequence information from database
+    TString query("SELECT fSequenceLast, fProjectKEY, fSourceKEY,"
+                  " fL1TriggerTableKEY, fL2TriggerTableKEY, fHvSettingsKEY, "
+                  " fLightConditionsKEY, fRunStart, fNumEvents, "
+                  " fZenithDistanceMin, fZenithDistanceMax "
+                  " FROM Sequences WHERE fSequenceFirst=");
+    query += fSequence;
+
+    // Request information from database
+    TSQLResult *res = serv.Query(query);
+    const Bool_t rc = GetFromDatabase(serv, *res);
+    delete res;
+
+    // invalidate sequence if retrieval failed
+    if (!rc)
+        fSequence = (UInt_t)-1;
+
+    // return result
+    return rc;
+}
+
+// --------------------------------------------------------------------------
+//
+// Set the contents of a sequence from the database.
+// The sequence number is given as argument.
+//
+// Example 1:
+//    // Define the database url
+//    MSequenceSQL s;
+//    s.GetFromDatabase("mysql://user:password@url/Database", 100002);
+//    if (s.IsValid())
+//        s.Print();
+//
+// Example 2:
+//    // Define the database in the resource file
+//    MSequenceSQL s;
+//    s.GetFromDatabase("sql.rc", 100002);
+//    if (s.IsValid())
+//        s.Print();
+//
+//  In case of error the Sequence is set invalid and kFALSE is returned,
+//  kTrue in case of success.
+//
+Bool_t MSequenceSQL::GetFromDatabase(const char *rc, Int_t sequno)
+{
+    MSQLMagic serv(rc);
+    return GetFromDatabase(serv, sequno);
+}
Index: trunk/MagicSoft/Mars/mjobs/MSequenceSQL.h
===================================================================
--- trunk/MagicSoft/Mars/mjobs/MSequenceSQL.h	(revision 8996)
+++ trunk/MagicSoft/Mars/mjobs/MSequenceSQL.h	(revision 8996)
@@ -0,0 +1,33 @@
+#ifndef MARS_MSequenceSQL
+#define MARS_MSequenceSQL
+
+#ifndef MARS_MSequence
+#include "MSequence.h"
+#endif
+
+class TSQLRow;
+class TSQLResult;
+
+class MSQLMagic;
+
+class MSequenceSQL : public MSequence
+{
+private:
+    Bool_t  GetRuns(MSQLMagic &serv, TString query, char type='*', UInt_t nevts=(UInt_t)-1);
+    TString GetTimeFromDatabase(MSQLMagic &serv, const TString &query);
+    Bool_t  GetFromDatabase(MSQLMagic &serv, TSQLResult &res);
+    Bool_t  GetFromDatabase(MSQLMagic &serv, TSQLRow &data);
+
+public:
+    MSequenceSQL(MSQLMagic &serv, Int_t seqno=-1)  { GetFromDatabase(serv, seqno); }
+    MSequenceSQL(const char *rc, Int_t seqno=-1)   { GetFromDatabase(rc, seqno); }
+    MSequenceSQL(Int_t seqno=-1)                   { GetFromDatabase(seqno); }
+
+    Bool_t GetFromDatabase(MSQLMagic &serv, Int_t seqno=-1);
+    Bool_t GetFromDatabase(const char *rc, Int_t seqno=-1);
+    Bool_t GetFromDatabase(Int_t seqno=-1) { return GetFromDatabase("sql.rc", seqno); }
+
+    ClassDef(MSequenceSQL, 0) // Extension of MSequence to get a sequence from a database
+};
+
+#endif
Index: trunk/MagicSoft/Mars/mjobs/Makefile
===================================================================
--- trunk/MagicSoft/Mars/mjobs/Makefile	(revision 8990)
+++ trunk/MagicSoft/Mars/mjobs/Makefile	(revision 8996)
@@ -24,7 +24,8 @@
            -I../mtools -I../mimage -I../mpointing -I../mastro -I../mfbase \
            -I../mhvstime -I../mtrigger -I../mmuon -I../mmc -I../mmovie \
-           -I../mextralgo -I../mhflux
+           -I../mextralgo -I../mhflux -I../msql
 
 SRCFILES = MSequence.cc \
+           MSequenceSQL.cc \
            MDataSet.cc \
            MJob.cc \
