DAQ = Namespace("http://purl.org/eis/vocab/daq#")
DCAT = Namespace("http://www.w3.org/ns/dcat#")
DQV = Namespace("http://www.w3.org/ns/dqv#")
DUV = Namespace("http://www.w3.org/ns/duv#")
OA = Namespace("http://www.w3.org/ns/oa#")
PROV = Namespace("http://www.w3.org/ns/prov#")
SDMX = Namespace("http://purl.org/linked-data/sdmx/2009/attribute#")
PWQ = Namespace("http://data.wu.ac.at/portalwatch/quality#")
PW_AGENT = URIRef("http://data.wu.ac.at/portalwatch")
def general_prov(graph):
def fetch_prov(snapshot, portalid, start_time, end_time, graph):
def quality_prov(entity, dataset, gen_time, fetch_activity, graph):
def add_dimensions_and_metrics(g):
def get_measures_and_dataset(portal, dataset, datasetquality, graph=None, portal_uri=None, fetch_activity=None):
def _get_measures_for_dataset(portal, dataset, datasetquality):
def get_measures_for_dataset(portal, dataset, datasetquality):
def dataset_quality_to_dqv(graph, ds_id, datasetquality, snapshot, fetch_activity=None): def dataset_quality_to_dqv(graph, ds_id, datasetquality, snapshot, fetch_activity=None):
    sn_time = utils_snapshot.tofirstdayinisoweek(snapshot)

    # BNodes: ds_id + snapshot + metric + value
    # add quality metrics to graph
    # TODO should we use portalwatch URI?
    for metric, value in [(PWQ.Date, datasetquality.exda), (PWQ.Rights, datasetquality.exri), (PWQ.Preservation, datasetquality.expr),
                          (PWQ.Access, datasetquality.exac), (PWQ.Discovery, datasetquality.exdi), (PWQ.Contact, datasetquality.exco),
                          (PWQ.ContactURL, datasetquality.cocu), (PWQ.DateFormat, datasetquality.coda), (PWQ.FileFormat, datasetquality.cofo),
                          (PWQ.ContactEmail, datasetquality.coce), (PWQ.License, datasetquality.coli), (PWQ.AccessURL, datasetquality.coac),
                          (PWQ.OpenFormat, datasetquality.opfo), (PWQ.MachineRead, datasetquality.opma), (PWQ.OpenLicense, datasetquality.opli)]:
        # add unique BNodes
        bnode_hash = hashlib.sha1(ds_id.n3() + str(snapshot) + metric.n3() + str(value))
        m = BNode(bnode_hash.hexdigest())

        graph.add((m, DQV.isMeasurementOf, metric))
        graph.add((m, DQV.value, Literal(value)))

        # add additional triples
        graph.add((ds_id, DQV.hasQualityMeasurement, m))
        graph.add((m, RDF.type, DQV.QualityMeasurement))
        graph.add((m, DQV.computedOn, ds_id))
        if fetch_activity:
            # add prov to each measure
            quality_prov(m, ds_id, sn_time, fetch_activity, graph) def dynPortal(db, portal, cursn, prevsn): def dcat_analyser():
    dcat_analyser = {}
    ##################### EXISTS ######################################
    # ACCESS

    dcat_analyser['ExAc'] = AnyMetric([AccessUrlDCAT(), DownloadUrlDCAT()], id='ExAc')
    # CONTACT
    dcat_analyser['ExCo'] = AnyMetric([DatasetContactDCAT(), DatasetPublisherDCAT()], id='ExCo')


    # DISCOVERY
    dcat_analyser['ExDi'] = AverageMetric([DatasetTitleDCAT(), DatasetDescriptionDCAT(), DatasetKeywordsDCAT(), DistributionTitleDCAT(), DistributionDescriptionDCAT()], id='ExDi')
    # PRESERVATION
    dcat_analyser['ExPr'] = AverageMetric([DatasetAccrualPeriodicityDCAT(), DistributionFormatsDCAT(), DistributionMediaTypesDCAT(), DistributionByteSizeDCAT()], id='ExPr')
    # DATE
    dcat_analyser['ExDa'] = AverageMetric([DatasetCreationDCAT(), DatasetModificationDCAT(), DistributionIssuedDCAT(), DistributionModifiedDCAT()], id='ExDa')


    # LICENSE
    dcat_analyser['ExRi'] = ProvLicenseDCAT()
    # TEMPORAL
    dcat_analyser['ExTe'] = DatasetTemporalDCAT()
    # SPATIAL
    dcat_analyser['ExSp'] = DatasetSpatialDCAT()

    ####################### CONFORMANCE ###########################
    # ACCESS
    dcat_analyser['CoAc'] = AnyConformMetric([ConformAccessUrlDCAT(), ConformDownloadUrlDCAT()], id='CoAc')
    # CONTACT
    dcat_analyser['CoCE'] = AnyConformMetric([EmailConformContactPoint(), EmailConformPublisher()], id='CoCE')
    dcat_analyser['CoCU'] = AnyConformMetric([UrlConformContactPoint(), UrlConformPublisher()], id='CoCU')

    # DATE
    dcat_analyser['CoDa'] = AverageConformMetric([DateConform(dcat_access.getCreationDate),
                                             DateConform(dcat_access.getModificationDate),
                                             DateConform(dcat_access.getDistributionCreationDates),
                                             DateConform(dcat_access.getDistributionModificationDates)], id='CoDa')
    # LICENSE
    dcat_analyser['CoLi'] = LicenseConform()
    # FORMAT
    dcat_analyser['CoFo'] = IANAFormatDCATAnalyser()

    ####################### OPENNESS ###########################
    dcat_analyser['OpFo'] = FormatOpennessDCATAnalyser()
    dcat_analyser['OpMa'] = FormatMachineReadableDCATAnalyser()
    dcat_analyser['OpLi'] = LicenseOpennessDCATAnalyser()

    ####################### RETRIEVABILITY ###########################
    #status_code = DatasetStatusCode()
    #dcat_analyser['ReDa'] = status_code
    #dcat_analyser['ReRe'] = DSRetrieveMetric(status_code)

    return dcat_analyser def getCurrentSnapshot():
    now = datetime.now()
    y=now.isocalendar()[0]
    w=now.isocalendar()[1]
    sn=str(y)[2:]+'{:02}'.format(w)

    return sn def aggregatePortalQuality(db, portalid, snapshot):
    log.info("Computing Aggregated Statistics", pid=portalid, snapshot=snapshot)

    with Timer(key=portalid+'-agg', verbose=True):
        df= aggregateByPortal3(db, portalid, snapshot)

    data={}
    with Timer(key=portalid+'-mean', verbose=True):
        if df.shape[0] != 0:

            for i in boolTypeCol:
                if df[i].dtype.name == 'bool':
                    df[i]=df[i].astype(int)
                else:
                    df[i]=df[i].replace(True,1)
                    df[i]=df[i].replace(False,0)
                #df[c]=df[c].apply(bool).astype(int)

            data={ k:float(str(v[['mean']]['mean'].round(decimals=2))) for k,v  in dict(df.describe()).items()}
            data.update({ k+'N':int(v[['count']]['count']) for k,v  in dict(df.describe()).items()})

    data['datasets']=df.shape[0]
    PSQ= PortalSnapshotQuality(portalid=portalid, snapshot=snapshot, **data)
    db.add(PSQ)
    return PSQ def readDBConfFromFile(config):
    dbConf={ 'host':None, 'port':None, 'password':None, 'user':None, 'db':None}
    if config:
        with open(config) as f_conf:
            config = yaml.load(f_conf)
            if config is not None and 'db' in config:
                dbConf.update(config['db'])
            if config is None:
                log.error("Cannot load config file")
    #log.info("DBConfig", conf=dbConf)
    return dbConf def md5(data):
    return hashlib.md5(json.dumps(data, sort_keys=True)).hexdigest() class ErrorHandler():

    exceptions=defaultdict(long)

    DEBUG=False

    @classmethod
    def handleError(cls, log, msg=None, exception=None, debug=False, **kwargs):
        name=type(exception).__name__
        cls.exceptions[name] +=1

        if debug:
            print(traceback.format_exc())

        log.error(msg, exctype=type(exception), excmsg=exception.message, **kwargs)

    @classmethod
    def printStats(cls):
        print '>>>','--*'*10,'EXCEPTIONS','*--'*10
        if len(cls.exceptions)==0:
            print "No exceptions handled"
        else:
            print "  Numbers of Exceptions:"
            for exc, count in cls.exceptions.iteritems():
                print " ",exc, count
        print '<<<','--*'*25 def getExceptionCode(e):
    #connection erorrs
    try:

        if isinstance(e,requests.exceptions.ConnectionError):
            return 702
        if isinstance(e,requests.exceptions.ConnectTimeout):
            return 703
        if isinstance(e,requests.exceptions.ReadTimeout):
            return 704
        if isinstance(e,requests.exceptions.HTTPError):
            return 705
        if isinstance(e,requests.exceptions.TooManyRedirects):
            return 706
        if isinstance(e,requests.exceptions.Timeout):
            return 707
        if isinstance(e,ckanapi.errors.CKANAPIError):
            try:
                err = literal_eval(e.extra_msg)
                return err[1]
            except Exception:
                return 708

        #if isinstance(e,requests.exceptions.RetryError):
        #    return 708

        #parser errors
        if isinstance(e, exceptions.ValueError):
            return 801
        if isinstance(e , TimeoutError):
            return 802
        #format errors
        if isinstance(e,urlnorm.InvalidUrl):
            return 901
        if isinstance(e,requests.exceptions.InvalidSchema):
            return 902
        if isinstance(e,requests.exceptions.MissingSchema):
            return 903
        else:
            return 600
    except Exception as e:
        log.error("MISSING Exception CODE", exctype=type(e), excmsg=e.message,exc_info=True)
        return 601 def getExceptionString(e):
    try:
        if isinstance(e,ckanapi.errors.CKANAPIError):
            try:
                err = literal_eval(e.extra_msg)
                return str(type(e))+":"+str(err[2])
            except Exception:
                return str(type(e))+":"+str(e.extra_msg)
        else:
            if e.message:
                return str(type(e))+":"+str(e.message)
            if e.message:
                return str(type(e))+":"
    except Exception as e:
        log.error("Get Exception string", exctype=type(e), excmsg=e.message,exc_info=True)
        return 601 class Timer(object):

    measures={}

    def __init__(self, verbose=False, key=None):
        self.verbose = verbose
        self.key=key

    def __enter__(self):
        self.start = time.time()
        return self

    def __exit__(self, *args):
        end = time.time()
        secs = end - self.start
        msecs = secs * 1000  # millisecs
        if self.verbose:
            print '(%s) elapsed time: %f ms' % (self.key,msecs)
        if self.key:
            if self.key not in self.__class__.measures:
                self.__class__.measures[self.key]=faststat.Stats()
            if msecs>=0:
                self.__class__.measures[self.key].add(msecs)


    @classmethod
    def printStats(cls):
        print '>>>','--*'*10,'TIMER','*--'*10
        s=[" Timing stats:","\n"]
        for m,st in Timer.measures.items():
            p = st.percentiles
            if st.n < len(p):
                quartiles = "(n too small)"
            else:
                quartiles = (_sigfigs(p.get(0.25, -1)),
                    _sigfigs(p.get(0.5, -1)), _sigfigs(p.get(0.75, -1)))
            d = ["  ["+m+'] -', str(st.mean), 'avg ms for',m,str(st.n),'calls)'
                    ,"\n       (min:",str(st.min),"-",str(st.max),":max, quantils:",quartiles,")\n"]
            s=s+d
        s=s+['-'*50]
        print " ".join(s)
        print '<<<','--*'*10,'TIMER','*--'*10

    @classmethod
    def getStats(cls):

        stats={}
        for m,st in cls.measures.items():
            p = st.percentiles
            stats[m]={
                'avg':st.mean
                , 'calls':st.n
                , 'min':st.min
                , 'max':st.max
                , 'q25':None, 'q5':None, 'q75':None
            }
            if st.n < len(p):
                quartiles = "(n too small)"
            else:
                stats[m]['q25']=_sigfigs(p.get(0.25, -1))
                stats[m]['q5']=_sigfigs(p.get(0.5, -1))
                stats[m]['q75']=_sigfigs(p.get(0.75, -1))
        return stats class Dataset(Base):
    __tablename__ = tab_datasets

    id           = Column( String, primary_key=True)
    snapshot     = Column( SmallInteger, primary_key=True, index=True)
    portalid     = Column( String, primary_key=True, index=True)
    organisation = Column(String, index=True)
    title        = Column(String, index=True)
    md5          = Column(String, ForeignKey(tab_datasetsdata+'.md5'), index=True)


    __table_args__ = (ForeignKeyConstraint([portalid, snapshot],
                                           [tab_portalsnapshot+'.portalid',tab_portalsnapshot+'.snapshot']),
                      {})

    portalsnapshot  = relationship("PortalSnapshot", back_populates="datasets")
    data  = relationship("DatasetData", back_populates="dataset")

    def __repr__(self):
        return "<Dataset(id=%s, portalid='%s', snapshot=%s, md5=%s)>" % (
            self.id, self.portalid, self.snapshot, self.md5) class DatasetData(Base):
    __tablename__ = tab_datasetsdata

    md5 = Column(String, primary_key=True, index=True, nullable=False)
    raw = Column(JSONB)
    dataset  = relationship("Dataset", back_populates="data")
    resources  = relationship("MetaResource", back_populates="dataset")

    modified = Column(TIMESTAMP)
    created = Column(TIMESTAMP)
    organisation = Column(String, index=True)
    license = Column(String, index=True)

    def __repr__(self):
        return "<DatasetData(md5=%s, data=%s)>" % (
            self.md5, self.raw is not None) class PortalSnapshot(Base):
    __tablename__ = tab_portalsnapshot

    portalid      = Column(String, ForeignKey(tab_portals+'.id'), primary_key=True, index=True,nullable=False)
    snapshot= Column( SmallInteger, primary_key=True)
    portal  = relationship("Portal", back_populates="snapshots")

    start       = Column(TIMESTAMP)
    end         = Column(TIMESTAMP)
    status      = Column(SmallInteger)
    exc         = Column(String)
    datasetcount    = Column(Integer)
    datasetsfetched    = Column(Integer)
    resourcecount   = Column(Integer)

    @hybrid_property
    def fetchtime(self):
        return self.end-self.start

    datasets = relationship("Dataset", back_populates="portalsnapshot")

    def __repr__(self):
        return "<PortalSnapshot(id=%s, snapshot=%s, start=%s, end=%s, status=%s,ds=%s,res=%s)>" % (
            self.portalid, self.snapshot, self.start, self.end, self.status,self.datasetcount,self.resourcecount) class MetaResource(Base):
    __tablename__ = tab_resources

    uri = Column(String, primary_key=True, index=True)
    md5 = Column(String,ForeignKey(DatasetData.md5), primary_key=True,index=True )
    valid = Column(Boolean)
    format = Column(String)
    media = Column(String)
    size = Column(BigInteger)
    created = Column(TIMESTAMP)
    modified = Column(TIMESTAMP)


    dataset  = relationship("DatasetData", back_populates="resources")


    def info(cls):
        return select([ResourceInfo]).where(cls.uri == ResourceInfo.uri).where(cls.snapshot== ResourceInfo.snapshot)

    def __repr__(self):
        return "<Resource(uri=%s, dataset=%s)>" % (
            self.uri, self.md5) class DatasetQuality(Base):
    __tablename__ = tab_datasetsquality

    md5     = Column(String, ForeignKey(DatasetData.md5), primary_key=True, index=True)
    cocu = Column(Boolean)
    coce = Column(Boolean)
    coda = Column(Float)
    cofo = Column(Float)
    coli = Column(Boolean)
    coac = Column(Boolean)

    exda = Column(Float)
    exri = Column(Float)
    expr = Column(Float)
    exac = Column(Boolean)
    exdi = Column(Float)
    exte = Column(Float)
    exsp = Column(Float)
    exco = Column(Boolean)

    opfo = Column(Float)
    opma = Column(Float)
    opli = Column(Boolean)


    data = relationship("DatasetData", backref=backref("quality", uselist=False))

    def __repr__(self):
        return "<DatasetQuality(md5=%s, assessment=%s)>" % (
            self.md5, any([self.exda,self.coac,self.coce,self.cocu])) class Portal(Base):
    __tablename__ = tab_portals

    id      = Column(String, primary_key=True, index=True,nullable=False)
    uri     = Column(String, nullable=False)
    apiuri  = Column(String)
    software = Column(String(12), nullable=False) # OpenDataSoft, CKAN, Socrata <13
    iso     = Column(String(2), nullable=False)
    active  = Column(Boolean, default=True,nullable=False)

    snapshots = relationship("PortalSnapshot", back_populates="portal")
    snapshotsquality = relationship("PortalSnapshotQuality", back_populates="portal")

    @hybrid_property
    def snapshot_count(self):
        print len(self.snapshots)
        return len(self.snapshots)
    @snapshot_count.expression

    def snapshot_count(cls):
        return select([func.count(PortalSnapshot.snapshot)])\
            .where(PortalSnapshot.portalid == cls.id).label("snapshot_count")

    @hybrid_property
    def first_snapshot(self):
        print [s for s in self.snapshots]
        return min([s.snapshot for s in self.snapshots])

    @first_snapshot.expression
    def first_snapshot(cls):
        return select([func.min(PortalSnapshot.snapshot)])\
            .where(PortalSnapshot.portalid == cls.id).label("first_snapshot")

    @hybrid_property
    def last_snapshot(self):
        return max([s.snapshot for s in self.snapshots])

    @last_snapshot.expression
    def last_snapshot(cls):
        return select([func.max(PortalSnapshot.snapshot)])\
            .where(PortalSnapshot.portalid == cls.id).label("last_snapshot")

    @hybrid_property
    def datasetcount(self):
        return self.snapshots.order_by(PortalSnapshot.snapshot.desc()).one().datasetcount

    @datasetcount.expression
    def datasetcount(cls):
        q=select([PortalSnapshot.datasetcount])\
            .where(PortalSnapshot.portalid == cls.id).order_by(PortalSnapshot.snapshot.desc()).limit(1).label("datasetcount")
        return q

    @hybrid_property
    def resourcecount(self):
        return self.snapshots.order_by(PortalSnapshot.snapshot.desc()).one().resourcecount

    @resourcecount.expression
    def resourcecount(cls):
        q=select([PortalSnapshot.resourcecount])\
            .where(PortalSnapshot.portalid == cls.id).order_by(PortalSnapshot.snapshot.desc()).limit(1).label("resourcecount")
        return q

    def __repr__(self):
        return "<Portal(id=%s, uri='%s', apiuri='%s', software='%s', iso=%s)>" % (
            self.id, self.uri, self.apiuri, self.software, self.iso) class PortalSnapshotQuality(Base):
    __tablename__ = tab_portalsnapshotquality

    portalid      = Column(String, ForeignKey(tab_portals+'.id'), primary_key=True, index=True,nullable=False)
    snapshot= Column( SmallInteger, primary_key=True)
    portal  = relationship("Portal", back_populates="snapshotsquality")

    cocu = Column(Float)
    cocuN = Column(Integer)
    coce = Column(Float)
    coceN = Column(Integer)
    coda = Column(Float)
    codaN = Column(Integer)
    cofo = Column(Float)
    cofoN = Column(Integer)
    coli = Column(Float)
    coliN = Column(Integer)
    coac = Column(Float)
    coacN = Column(Integer)
    exda = Column(Float)
    exdaN = Column(Integer)
    exri = Column(Float)
    exriN = Column(Integer)
    expr = Column(Float)
    exprN = Column(Integer)
    exac = Column(Float)
    exacN = Column(Integer)
    exdi = Column(Float)
    exdiN = Column(Integer)
    exte = Column(Float)
    exteN = Column(Integer)
    exsp = Column(Float)
    exspN = Column(Integer)
    exco = Column(Float)
    excoN = Column(Integer)
    opfo = Column(Float)
    opfoN = Column(Integer)
    opma = Column(Float)
    opmaN = Column(Integer)
    opli = Column(Float)
    opliN = Column(Integer)
    datasets=Column(Integer)

    def __repr__(self):
        return "<PortalSnapshotQuality(id=%s, snapshot=%s, agg=%s)>" % (
            self.portalid, self.snapshot,  any([self.exda,self.coac,self.coce,self.cocu])) def dict_to_dcat(dataset_dict, portal, graph=None, format='json-ld', portal_uri=None):
    if graph == None:
        graph = rdflib.Graph()
    dataset_ref = None

    if portal.software == 'CKAN':
        converter = CKANConverter(graph, portal.apiuri)
        dataset_ref = converter.graph_from_ckan(dataset_dict)
    elif portal.software == 'Socrata':
        if 'dcat' in dataset_dict and dataset_dict['dcat']:
            graph.parse(data=dataset_dict['dcat'], format='xml')
            dataset_ref = fix_socrata_graph(graph, dataset_dict, portal.apiuri)
            # TODO redesign distribution, format, contact (publisher, organization)
    elif portal.software == 'OpenDataSoft':
        dataset_ref = graph_from_opendatasoft(graph, dataset_dict, portal.apiuri)
        # TODO contact, publisher, organization
    elif portal.software == 'XMLDCAT':
        # dataset_dict is already json-ld
        str_ds = json.dumps(dataset_dict)
        graph.parse(data=str_ds, format='json-ld')
        dataset_ref = graph.value(predicate=RDF.type, object=DCAT.Dataset)
    elif portal.software == 'DataGouvFr':
        dataset_ref = graph_from_data_gouv_fr(graph, dataset_dict, portal.apiuri)

    # add portal ref to graph
    if portal_uri and dataset_ref:
        graph.add((portal_uri, DCAT.dataset, dataset_ref))

    if portal.software == 'XMLDCAT':
        return dataset_dict
    else:
        return json.loads(graph.serialize(format=format)) def getDistributionAccessURLs(dataset):
    return accessDistribution(dataset, DCAT.accessURL) def getDistributionDownloadURLs(dataset):
    return accessDistribution(dataset, DCAT.downloadURL) def getDistributionFormatWithURL(dataset, url):
    f = accessDistributionWithURL(dataset, url, DCT['format'])
    if f.startswith('_:'):
        # blank node
        v = accessById(dataset, f, RDFS.label)
        if not v:
            v = accessById(dataset, f, RDF.value)
        if v:
            f = v
    if f.startswith('_:'):
        f = None
    return f def getDistributionMediaTypeWithURL(dataset, url):
    return accessDistributionWithURL(dataset, url, DCAT.mediaType) def getDistributionSizeWithURL(dataset, url):
    return accessDistributionWithURL(dataset, url, DCAT.byteSize) def getDistributionCreationDateWithURL(dataset, url):
    return accessDistributionWithURL(dataset, url, DCT.issued) def getDistributionModificationDateWithURL(dataset, url):
    return accessDistributionWithURL(dataset, url,DCT.modified) def getOrganization(dataset):

    for dcat_el in getattr(dataset,'dcat',[]):
        #TODO there is also a FOAF.Ogranisation
        if str(FOAF.Organization) in dcat_el.get('@type',[]):
            for tag in dcat_el.get(str(FOAF.name),[]):
                orga=tag['@value']
                if orga is not None and len(orga)>0:
                    return orga
    for dcat_el in getattr(dataset,'dcat',[]):
        #TODO there is also a FOAF.Ogranisation
        if str(VCARD.Organization) in dcat_el.get('@type',[]):
            for tag in dcat_el.get(str(VCARD.fn),[]):
                orga=tag['@value']
                if orga is not None and len(orga)>0:
                    return orga
    return None def getCreationDate(dataset):
    return accessDataset(dataset, DCT.issued) def getModificationDate(dataset):
    return accessDataset(dataset, DCT.modified) def getLicense(dataset):
    values = getDistributionLicenseTriples(dataset)
    for id, label, url in values:
        id, appr = license_mapping.map_license(label, id, url)
        return id
    return None def getTitle(dataset):
    return accessDataset(dataset, DCT.title) def toDatetime(value):
    date=None
    if value:
        for pattern in [ "%Y-%m-%dT%H:%M:%S", "%Y-%m-%d"]:
            try:
                date= datetime.datetime.strptime(value.split(".")[0][:19], pattern)
                break
            except Exception as e:
                pass
    return date def normaliseFormat(v):
    if v is None:
        return None
    v = v.encode('utf-8').strip()
    v = v.lower()
    if v.startswith('.'):
        v = v[1:]
    return v def getPortalProcessor(P):
    if P.software == 'CKAN':
        return CKAN()
    elif P.software == 'Socrata':
        return Socrata()
    elif P.software == 'OpenDataSoft':
        return OpenDataSoft()
    elif P.software == 'XMLDCAT':
        return XMLDCAT()
    elif P.software == 'DataGouvFr':
        return DataGouvFr()
    else:
        raise NotImplementedError(P.software + ' is not implemented') class DBManager(object):
    def __init__(self, db='portalwatch', host="localhost", port=5432, password=None, user='opwu', debug=False):

        # Define our connection string
        self.log = log.new()

        conn_string = "postgresql://"
        if user:
            conn_string += user
        if password:
            conn_string += ":" + password
        if host:
            conn_string += "@" + host
        if port:
            conn_string += ":" + str(port)
        conn_string += "/" + db
        log.info("Connecting DB")

        self.engine = create_engine(conn_string, pool_size=20, client_encoding='utf8', echo=debug)
        add_engine_pidguard(self.engine)
        #add_query_time_logging(self.engine)
        # register_after_fork(self.engine, self.engine.dispose)
        log.info("Connected DB")
        # self.engine.connect()


        self.session_factory = sessionmaker(bind=self.engine)  # , expire_on_commit=False

        # self.session = self.Session()



        self.dataset_insert_function = DDL(
            """
            CREATE OR REPLACE FUNCTION dataset_insert_function()
            RETURNS TRIGGER AS $$

            DECLARE
                _snapshot smallint;
                _table_name text;

            BEGIN
                _snapshot := NEW.snapshot;
                _table_name := '""" + tab_datasets + """_' || _snapshot;

                    PERFORM 1 FROM pg_tables WHERE tablename = _table_name;

                      IF NOT FOUND THEN
                        EXECUTE
                          'CREATE TABLE '
                          || quote_ident(_table_name)
                          || ' (CHECK ("snapshot" = '
                          || _snapshot::smallint
                          || ')) INHERITS (""" + tab_datasets + """)';

                        -- Indexes are defined per child, so we assign a default index that uses the partition columns
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_org') || ' ON '||quote_ident(_table_name) || ' (organisation)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_sn')  || ' ON '||quote_ident(_table_name) || ' (snapshot)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_pid') || ' ON '||quote_ident(_table_name) || ' (portalid)';
                        EXECUTE 'ALTER TABLE '  || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_pkey') || ' PRIMARY KEY (id, snapshot, portalid)';
                        EXECUTE 'ALTER TABLE '  || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_md5_fkey') || ' FOREIGN KEY (md5) REFERENCES datasetsdata (md5) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION';
                        EXECUTE 'ALTER TABLE '  || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_portalid_fkey') || ' FOREIGN KEY (portalid, snapshot) REFERENCES portalsnapshot (portalid, snapshot) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION';


                      END IF;

                      EXECUTE
                        'INSERT INTO '
                        || quote_ident(_table_name)
                        || ' VALUES ($1.*)'
                      USING NEW;

                      RETURN NULL;
                END;

                $$ LANGUAGE plpgsql;
                """)
        self.dataset_insert_trigger = DDL(
            """
            CREATE TRIGGER dataset_insert_trigger
            BEFORE INSERT ON """ + tab_datasets + """
                FOR EACH ROW EXECUTE PROCEDURE dataset_insert_function();
                """
        )
        self.resourcesinfo_insert_function = DDL(
            """
            CREATE OR REPLACE FUNCTION resourcesinfo_insert_function()
            RETURNS TRIGGER AS $$

            DECLARE
                _snapshot smallint;
                _table_name text;

            BEGIN
                _snapshot := NEW.snapshot;
                _table_name := '""" + tab_resourcesinfo + """_' || _snapshot;

                    PERFORM 1 FROM pg_tables WHERE tablename = _table_name;

                      IF NOT FOUND THEN
                        EXECUTE
                          'CREATE TABLE '
                          || quote_ident(_table_name)
                          || ' (CHECK ("snapshot" = '
                          || _snapshot::smallint
                          || ')) INHERITS (""" + tab_resourcesinfo + """)';

                        -- Indexes are defined per child, so we assign a default index that uses the partition columns
                        EXECUTE 'ALTER TABLE '  || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_pkey') || ' PRIMARY KEY (uri, snapshot)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_status') || ' ON '||quote_ident(_table_name) || ' (status)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_uri') || ' ON '||quote_ident(_table_name) || ' (uri)';
                      END IF;

                      EXECUTE
                        'INSERT INTO '
                        || quote_ident(_table_name)
                        || ' VALUES ($1.*)'
                      USING NEW;



                      RETURN NULL;
                END;

                $$ LANGUAGE plpgsql;
                """)
        self.resourcesinfo_insert_trigger = DDL(
            """
            CREATE TRIGGER resourcesinfo_insert_trigger
            BEFORE INSERT ON """ + tab_resourcesinfo + """
                FOR EACH ROW EXECUTE PROCEDURE resourcesinfo_insert_function();
                """
        )

        self.resourcescrawllog_insert_function = DDL(
            """
            CREATE OR REPLACE FUNCTION resourcescrawllog_insert_function()
            RETURNS TRIGGER AS $$

            DECLARE
                _snapshot smallint;
                _table_name text;

            BEGIN
                _snapshot := NEW.snapshot;
                _table_name := '""" + tab_resourcescrawllog + """_' || _snapshot;

                    PERFORM 1 FROM pg_tables WHERE tablename = _table_name;

                      IF NOT FOUND THEN
                        EXECUTE
                          'CREATE TABLE '
                          || quote_ident(_table_name)
                          || ' (CHECK ("snapshot" = '
                          || _snapshot::smallint
                          || ')) INHERITS (""" + tab_resourcescrawllog + """)';

                        -- Indexes are defined per child, so we assign a default index that uses the partition columns
                        EXECUTE 'ALTER TABLE '  || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_pkey') || ' PRIMARY KEY (uri, snapshot, timestamp)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_status') || ' ON '||quote_ident(_table_name) || ' (status)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_uri') || ' ON '||quote_ident(_table_name) || ' (uri)';
                        EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_domain') || ' ON '||quote_ident(_table_name) || ' (domain)';
                      END IF;

                      EXECUTE
                        'INSERT INTO '
                        || quote_ident(_table_name)
                        || ' VALUES ($1.*)'
                      USING NEW;



                      RETURN NULL;
                END;

                $$ LANGUAGE plpgsql;
                """)
        self.resourcescrawllog_insert_trigger = DDL(
            """
            CREATE TRIGGER resourcescrawllog_insert_trigger
            BEFORE INSERT ON """ + tab_resourcescrawllog + """
                FOR EACH ROW EXECUTE PROCEDURE resourcescrawllog_insert_function();
                """
        )

    def db_DropEverything(self):
        print "Sroping everything"
        conn = self.engine.connect()

        # the transaction only applies if the DB supports
        # transactional DDL, i.e. Postgresql, MS SQL Server
        trans = conn.begin()

        inspector = reflection.Inspector.from_engine(self.engine)

        # gather all data first before dropping anything.
        # some DBs lock after things have been dropped in
        # a transaction.

        metadata = MetaData()

        tbs = []
        all_fks = []

        for table_name in inspector.get_table_names():
            fks = []
            for fk in inspector.get_foreign_keys(table_name):
                if not fk['name']:
                    continue
                fks.append(
                    ForeignKeyConstraint((), (), name=fk['name'])
                )
            t = Table(table_name, metadata, *fks)
            tbs.append(t)
            all_fks.extend(fks)

        for fkc in all_fks:
            conn.execute(DropConstraint(fkc, cascade=True))

        for table in tbs:
            if table.name != tab_datasets and table.name != tab_resourcesinfo:
                conn.execute(DropTable(table))

        trans.commit()

    def tableExists(self,Variable_tableName):
        return self.engine.has_table(self.engine, Variable_tableName)
    def viewExists(self,Variable_tableName):
        print 'check if table {} exists'.format(Variable_tableName)
        return self.engine.connect().execute()

    def init(self, Base):

        event.listen(Dataset.__table__, 'after_create', self.dataset_insert_function)
        event.listen(Dataset.__table__, 'after_create', self.dataset_insert_trigger)
        event.listen(ResourceInfo.__table__, 'after_create', self.resourcesinfo_insert_function)
        event.listen(ResourceInfo.__table__, 'after_create', self.resourcesinfo_insert_trigger)

        Base.metadata.create_all(self.engine) class DBClient(object):

    def __init__(self, dbm=None, Session=None):
        if dbm is not None:
            self.dbm=dbm
            self.Session = scoped_session(dbm.session_factory)
        elif Session:
            self.Session
        Base.query = self.Session.query_property()


    from contextlib import contextmanager

    @contextmanager
    def session_scope(self):
        """Provide a transactional scope around a series of operations."""
        #session = self.Session()
        try:
            yield self.Session
            self.Session.flush()
            self.Session.commit()
        except:
            self.Session.rollback()
            raise
        #finally:
        #   self.Session.remove()

    def remove(self):
        self.Session.remove()

    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#
    ### ADD & COMMIT
    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#

    def delete(self, obj):
        with self.session_scope() as session:
            session.delete(obj)

    def add(self, obj):
        with self.session_scope() as session:
            session.add(obj)

    def bulkadd(self, obj):
        with self.session_scope() as session:
            session.bulk_save_objects(obj)

    def commit(self):
        self.Session.commit()

    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#
    ### EXISTS FUNCTIONS
    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#

    def exist_datasetdata(self, md5):
        return DatasetData.query.filter_by(md5=md5).first()

    def exist_resourceinfo(self, uri, snapshot):
        return ResourceInfo.query.filter_by(uri=uri, snapshot=snapshot).first()

    def exist_metaresource(self, uri):
        return MetaResource.query.filter_by(uri=uri).first()

    def datasetqualityExists(self, md5):
        with self.session_scope() as session:
            return session.query(DatasetQuality).filter_by(md5=md5).first()

    def getContentLocation(self, uri=None, digest=None):
        with self.session_scope() as session:
            q= session.query(ResourceCrawlLog.disklocation).filter(ResourceCrawlLog.uri==uri).filter(ResourceCrawlLog.digest==digest).first()
            return q
    def getLastDigest(self, uri):
        with self.session_scope() as session:
            q= session.query(ResourceCrawlLog.digest).filter(ResourceCrawlLog.uri==uri).filter(ResourceCrawlLog.status==200).order_by(ResourceCrawlLog.timestamp.desc()).first()
            return q

    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#
    ### PORTALS
    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#
    def portals(self):
        with self.session_scope() as session:
            return session.query(Portal)

    def portalAll(self):
        with self.session_scope() as session:
            return session.query(Portal, Portal.snapshot_count,Portal.first_snapshot, Portal.last_snapshot, Portal.datasetCount, Portal.resourceCount)

    def portalsSnapshots(self, snapshot):
        with self.session_scope() as session:
            return session.query(PortalSnapshot)\
            .filter(PortalSnapshot.snapshot==snapshot)


    def portalsQuality(self,snapshot):
        with self.session_scope() as session:
            return session.query(PortalSnapshotQuality)\
            .filter(PortalSnapshotQuality.snapshot==snapshot)

    def portalsAll(self,snapshot):
        with self.session_scope() as session:
            return session.query( PortalSnapshot)\
                .filter(PortalSnapshot.snapshot==snapshot)\
                .outerjoin( PortalSnapshotQuality, PortalSnapshot.portalid==PortalSnapshotQuality.portalid )\
                .join(Portal)\
                .add_entity(PortalSnapshotQuality)\
                .add_entity(Portal)\


    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#
    ### PORTAL
    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#

    def portal(self, portalid):
        with self.session_scope() as session:
            return session.query(Portal).filter(Portal.id==portalid)

    def portalSnapshot(self,snapshot, portalid):
        with self.session_scope() as session:
            return session.query(PortalSnapshot)\
                .filter(PortalSnapshot.snapshot==snapshot)\
                .filter(PortalSnapshot.portalid==portalid)

    def portalSnapshotQuality(self, portalid, snapshot):
        with self.session_scope() as session:
            return session.query(PortalSnapshotQuality)\
                .filter(PortalSnapshotQuality.portalid==portalid)\
                .filter(PortalSnapshotQuality.snapshot==snapshot)


    def statusCodeDist(self, snapshot,portalid=None):
        with self.session_scope() as session:
            q= session.query(ResourceInfo.status,func.count())\
                .join(MetaResource, ResourceInfo.uri==MetaResource.uri)\
                .join(Dataset,Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)\
                .filter(ResourceInfo.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)

            q=q.group_by(ResourceInfo.status)
            q=q.order_by(func.count().desc())

            return q

    def organisationDist(self, snapshot, portalid=None):
        with self.session_scope() as session:
            q= session.query(Dataset.organisation,func.count().label('count'))\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            q=q.group_by(Dataset.organisation)
            q=q.order_by(func.count().desc())
            return q



    def formatDist(self, snapshot, portalid=None, iso=None, software=None):
        with self.session_scope() as session:
            q= session.query(MetaResource.format, func.count().label('count'))\
                .join(Dataset,Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            if iso:
                q= q.join(Portal, Portal.id == Dataset.portalid)\
                .filter(Portal.iso==iso)
            if software:
                q= q.join(Portal, Portal.id == Dataset.portalid)\
                .filter(Portal.software==software)


            q=q.group_by(MetaResource.format)
            q=q.order_by(func.count().desc())
            return q


    def distinctFormats(self, snapshot, portalid=None):
        with self.session_scope() as session:
            q= session.query(MetaResource).distinct(MetaResource.format)\
                .join(Dataset,Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            return q

    def licenseDist(self, snapshot, portalid=None):
        with self.session_scope() as session:
            q= session.query(DatasetData.license,func.count().label('count')).join(Dataset)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            q=q.group_by(DatasetData.license)
            q=q.order_by(func.count().desc())
            return q

    def distinctLicenses(self, snapshot, portalid=None):
        with self.session_scope() as session:
            q= session.query(DatasetData).distinct(DatasetData.license)\
                .join(Dataset)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            return q

    def validURLDist(self, snapshot,portalid=None):
        with self.session_scope() as session:
            q= session.query(MetaResource.valid, func.count().label('count'))\
                .join(Dataset,Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)

            q=q.group_by(MetaResource.valid)
            q=q.order_by(func.count(MetaResource.valid).desc())

            return q

    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#
    ### DATASETS
    #--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--#

    def getDatasets(self, portalid=None, snapshot=None):
        with self.session_scope() as session:
            q= session.query(Dataset)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            if snapshot:
                q=q.filter(Dataset.snapshot==snapshot)
            return q

    def getDatasetData(self,md5=None):
        with self.session_scope() as session:
            q= session.query(DatasetData).filter(DatasetData.md5==md5).first()
            return q


    def getUnfetchedResources(self,snapshot, portalid=None, batch=None, iso=None, exclude_iso=None):
        with self.session_scope() as session:
            q=session.query(MetaResource.uri)\
                .join(Dataset, Dataset.md5==MetaResource.md5)\
                .join(Portal, Portal.id==Dataset.portalid)\
                .filter(Dataset.snapshot==snapshot)\
                .filter(MetaResource.valid==True)\
                .filter(
                #~exists().where(ResourceInfo.uri == MetaResource.uri).where(ResourceInfo.snapshot == snapshot)
                    ~exists().where(
                        and_( ResourceInfo.uri==MetaResource.uri, ResourceInfo.snapshot==snapshot))
                )
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            if iso:
                q=q.filter(Portal.iso==iso)
            if exclude_iso:
                q=q.filter(Portal.iso!=iso)
            if batch:
                q=q.limit(batch)
            return q

    def getDataUnfetchedResources(self,snapshot, portalid=None, batch=None, format=None):
        with self.session_scope() as session:
            q=session.query(MetaResource.uri, Dataset.id, Dataset.md5)\
                .join(Dataset, Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)\
                .filter(MetaResource.valid==True)\
                .filter(
                    ~exists().where(and_(MetaResource.uri== ResourceCrawlLog.uri, ResourceCrawlLog.snapshot==snapshot))
                )
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            if batch:
                q=q.limit(batch)
            if format:
                q=q.filter(MetaResource.format==format)
            return q

    def getDataResources(self, snapshot, portalid=None, format=None):
        with self.session_scope() as session:
            q=session.query(MetaResource.uri, Dataset.id, Dataset.md5)\
                .join(Dataset, Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)\
                .filter(MetaResource.valid==True)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            if format:
                q=q.filter(MetaResource.format==format)
            return q

    def getResourceInfos(self, snapshot, portalid=None):
        with self.session_scope() as session:
            q= session.query(ResourceInfo)\
                .join(MetaResource, ResourceInfo.uri==MetaResource.uri)\
                .join(Dataset,Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            return q


    def getMetaResource(self, snapshot, portalid=None, size=None):
        with self.session_scope() as session:
            q= session.query(MetaResource)\
                .join(Dataset, Dataset.md5==MetaResource.md5)\
                .filter(Dataset.snapshot==snapshot)
            if portalid:
                q=q.filter(Dataset.portalid==portalid)
            if size:
                q=q.filter((MetaResource.size<=size) | (MetaResource.size == None))
            return q

    def getResourceInfoByURI(self, uri, snapshot):
        with self.session_scope() as session:
            q= session.query(ResourceInfo)\
                .filter(ResourceInfo.uri==uri)\
                .filter(ResourceInfo.snapshot==snapshot)
            return q

    def portalSnapshotQualityDF(self, portalid, snapshot):

        q= self.portalSnapshotQuality(portalid,snapshot)
        data=None
        for r in q:
            data=row2dict(r)
            break
        d=[]

        datasets= int(data['datasets'])
        for inD in qa:
            for k , v in inD['metrics'].items():
                k=k.lower()
                # TODO what to do if metric has no value?
                if data[k] != None and data[k] != 'None':
                    value=float(data[k])
                    perc=int(data[k+'N'])/(datasets*1.0) if datasets>0 else 0
                    c= { 'Metric':k, 'Dimension':inD['dimension'],
                         'dim_color':inD['color'], 'value':value, 'perc':perc}
                    c.update(v)
                    d.append(c)
        return pd.DataFrame(d)

    def getResourcesHistory(self, uri, md5, source=None):
        with self.session_scope() as session:
            q= session.query(ResourceHistory)\
                .filter(ResourceHistory.uri==uri)\
                .filter(ResourceHistory.md5==md5)
            if source:
                q=q.filter(ResourceHistory.source==source)
            q=q.order_by(ResourceHistory.snapshot.asc())
            return q

    def getPortalSnapshotDynamics(self, snapshot, portalid):
        with self.session_scope() as session:
            q = session.query(PortalSnapshotDynamicity)\
                .filter(PortalSnapshotDynamicity.snapshot==snapshot)\
                .filter(PortalSnapshotDynamicity.portalid==portalid)
            return q
import datetime
import json
import os
import rdflib
import structlog
import yaml
import urlnorm
from multiprocessing import Pool
from scrapy.utils.url import escape_ajax
from sqlalchemy import not_
from w3lib.url import safe_url_string
from odpw.quality import dqv_export
from odpw.quality.dqv_export import dataset_quality_to_dqv
from odpw.utils import dynamicity
from odpw.quality.dcat_analysers import dcat_analyser
from odpw.utils.utils_snapshot import getCurrentSnapshot
from odpw.services.aggregates import aggregatePortalQuality
from odpw.utils.helper_functions import readDBConfFromFile, md5
from odpw.utils.error_handling import ErrorHandler, getExceptionCode, getExceptionString
from odpw.utils.timing import Timer
from odpw.core.model import Dataset, DatasetData, PortalSnapshot, MetaResource, \
    DatasetQuality, Portal, PortalSnapshotQuality
from odpw.core.dataset_converter import dict_to_dcat
from odpw.core.dcat_access import getDistributionAccessURLs, getDistributionDownloadURLs, getDistributionFormatWithURL, \
    getDistributionMediaTypeWithURL, getDistributionSizeWithURL, getDistributionCreationDateWithURL, \
    getDistributionModificationDateWithURL, getOrganization, getCreationDate, getModificationDate, getLicense, getTitle
from odpw.core.parsing import toDatetime, normaliseFormat
from odpw.core.portal_fetch_processors import getPortalProcessor
from odpw.core.db import  DBManager
from odpw.core.api import DBClient
from rdflib.namespace import Namespace, RDF
from random import randint
from time import sleep
               ,'dq':[]}

    c=0
    for i, d in enumerate(iter):
        c+=1
        with Timer(key='ProcessDataset'):
            #CREATE DATASET AND ADD


            with Timer(key='md5'):
                md5v=None if d.data is None else md5(d.data)


            if md5v:
                with Timer(key='dict_to_dcat'):
                    #analys quality
                    d.dcat=dict_to_dcat(d.data, P)

                DD=None
                DQ=None
                with Timer(key='db.datasetdataExists(md5v)'):
                    process = not db.exist_datasetdata(md5v)
                if process:
                    #DATATSET DATA
                    DD=createDatasetData(md5v,d)
                    try:
                        db.add(DD) #primary key, needs to be inserted first
                        #DATATSET QUALTIY
                        #print "adding",md5v
                        DQ = createDatasetQuality(P, md5v, d)
                        bulk_obj['dq'].append(DQ)

                        #META RESOURCES
                        MQs= createMetaResources(md5v , d)
                        for MR in MQs:
                            bulk_obj['mr'].append(MR)
                    except Exception as e:
                        pass
                        #print "AND AGAIN",md5v, db.datasetdataExists(md5v)
                #DATATSET
                title=getTitle(d)
                title = title[0] if len(title)>0 else None

                D= Dataset(id=d.id,
                       snapshot=d.snapshot,
                       portalid=d.portal_id,
                       md5=md5v,
                       organisation=DD.organisation if DD else getOrganization(d),
                       title=title
                        )

                bulk_obj['d'].append(D)


                # store metadata in local git directory
                try:
                    if store_local != None:
                        with Timer(key='store_to_local_git'):
                            if 'name' in d.data:
                                dir_name = d.data['name']
                            else:
                                dir_name = d.id
                            filename = os.path.join(store_local, P.id, dir_name)
                            if not os.path.exists(filename):
                                os.makedirs(filename)

                            with open(os.path.join(filename, 'original.json'), 'w') as f:
                                json.dump(d.data, f, indent=4)

                            g = rdflib.Graph()
                            g.parse(data=json.dumps(d.dcat), format='json-ld')
                            dqv_export.general_prov(g)
                            ds_id = g.value(predicate=RDF.type, object=DCAT.Dataset)
                            if not DQ:
                                DQ = db.datasetqualityExists(md5=md5v)
                            if DQ:
                                dqv_export.add_dimensions_and_metrics(g)
                                dataset_quality_to_dqv(g, ds_id, DQ, snapshot)
                            with open(os.path.join(filename, 'metadata.jsonld'), 'w') as f:
                                g.serialize(f, format='json-ld')

                except Exception as exc:
                    ErrorHandler.handleError(log, "StoreToLocalGitException", exception=exc, pid=P.id, dataset=d.id, snapshot=snapshot,
                                             exc_info=True)
            else:
                D= Dataset(id=d.id,
                       snapshot=d.snapshot,
                       portalid=d.portal_id,
                       md5=md5v,
                       organisation=None)
                bulk_obj['d'].append(D)

        if i%batch==0:
            bulkInsert(bulk_obj,db )
            for k in bulk_obj:
                bulk_obj[k]=[]
        c=i

    #cleanup, commit all left inserts
    bulkInsert(bulk_obj,db)
    for k in bulk_obj:
        bulk_obj[k]=[]
    log.info("InsertedDatasets", parsed=c, portalid=P.id ,snapshot=snapshot)


#--*--*--*--*
def help():
    return "perform head lookups"
def name():
    return 'Fetch'

def setupCLI(pa):
    pa.add_argument("-c","--cores", type=int, help='Number of processors to use', dest='processors', default=4)
    pa.add_argument('--pid', dest='portalid' , help="Specific portal id ")
    pa.add_argument('--repair', help="Re-fetch portals with status != 200", action='store_true')

def cli(args,dbm):
    sn = getCurrentSnapshot()

    dbConf= readDBConfFromFile(args.config)

    db= DBClient(dbm)