예제 #1
0
    def delete_range(env, start, end):
        """
        Deletes range of records and attributes of them from DB.
        """
        if start == '' or end == '':
            return False
        try:
            start = int(start)
            end = int(end)
        except:
            return False

        db = env.get_db_cnx()
        cursor = db.cursor()

        cursor.execute("SELECT id FROM downloader_downloaded "\
                       "WHERE timestamp>=%s AND timestamp<=%s", (start, end))

        ids = []
        for (id, ) in cursor:
            ids.append(id)

        for id in ids:
            cursor.execute(
                "DELETE FROM downloader_downloaded_attributes "
                "WHERE downloaded=%s", (id, ))

        cursor.execute("DELETE FROM downloader_downloaded WHERE " \
                       " timestamp>=%s AND timestamp<=%s", (start, end))

        return db.commit()
예제 #2
0
파일: model.py 프로젝트: nyuhuhuu/trachacks
 def delete_range(env, start, end):
     """
     Deletes range of records and attributes of them from DB.
     """
     if start == '' or end == '':
         return False
     try:
         start = int(start)
         end = int(end)
     except:
         return False
     
     
     db = env.get_db_cnx()
     cursor = db.cursor()
     
     cursor.execute("SELECT id FROM downloader_downloaded "\
                    "WHERE timestamp>=%s AND timestamp<=%s", (start, end))
     
     ids = []
     for (id,) in cursor:
         ids.append(id)
     
     for id in ids:
         cursor.execute("DELETE FROM downloader_downloaded_attributes "
                        "WHERE downloaded=%s", (id,))
     
     cursor.execute("DELETE FROM downloader_downloaded WHERE " \
                    " timestamp>=%s AND timestamp<=%s", (start, end))
     
     
     return db.commit()
예제 #3
0
    def get_files(env):
        db = env.get_db_cnx()
        cursor = db.cursor()

        cursor.execute("SELECT id FROM downloader_file "
                       "WHERE deleted IS NULL "
                       "ORDER BY name, timestamp")
        files = []
        for (id, ) in cursor:
            files.append(File(env, id, db=db))

        return files
예제 #4
0
파일: model.py 프로젝트: nyuhuhuu/trachacks
 def get_files(env):
     db = env.get_db_cnx()
     cursor = db.cursor()
     
     cursor.execute("SELECT id FROM downloader_file "
                    "WHERE deleted IS NULL "
                    "ORDER BY name, timestamp")
     files = []
     for (id,) in cursor:
         files.append(File(env, id, db=db))
     
     return files
예제 #5
0
    def fetch_downloads_list(env, req=None, sort='timestamp', desc=False, \
                             per_page=None, page=1, get_range=False, \
                             count=False, \
                             filter=None, filter_id=None, range=None):
        """
        Fetch list of downloads in needed sort and with needed offset and limit.
        """

        # Count offset
        if per_page == None:
            offset = 0
            q_limit = ' '
        else:
            offset = (page - 1) * per_page
            q_limit = ' LIMIT %d OFFSET %d ' % (per_page, offset)

        if desc:
            desc = ' DESC'
        else:
            desc = ''

        db = env.get_db_cnx()
        cursor = db.cursor()

        # Get count of all records
        cursor.execute("SELECT count(id) FROM downloader_downloaded")
        row = cursor.fetchone()
        rec_count = row[0]

        # Filter
        if filter_id:
            filter_id = int(filter_id)
        else:
            filter_id = 0
        if filter == 'category':
            q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
                         " downloader_file.id=c.file " +\
                         " LEFT OUTER JOIN downloader_release ON " + \
                         " downloader_release.id=downloader_file.release "
            q_fil_where = ' AND category=%d ' % (filter_id, )
        elif filter == 'release':
            q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
                         " downloader_file.id=c.file "
            q_fil_where = ' AND downloader_file.release=%d ' % (filter_id, )
        elif filter == 'file':
            q_fil_join = ' '
            q_fil_where = ' AND c.file=%d ' % (filter_id, )
        else:
            q_fil_join = ' '
            q_fil_where = ' '

        # Range
        if isinstance(range, tuple) and len(range) == 2:
            min_val, max_val = range
            q_rng_where = (" AND c.timestamp>= %d " +
                          " AND c.timestamp<= %d ") % \
                          (min_val, max_val)
        else:
            q_rng_where = ' '

        # Fetech sorted piece of data
        q_attr_from = ''
        q_attr_on = ''
        q_attr_where = ' c.id > %s '
        q_attr_sort = 0
        if sort == 'timestamp':
            sort = 'c.timestamp'
        elif sort == 'file_name':
            sort = 'c.name'
        else:
            q_attr_from = " LEFT OUTER JOIN " \
                " downloader_downloaded_attributes AS a "
            q_attr_on = " ON a.downloaded=c.id "
            q_attr_where = " a.name=%s " \
                "OR a.name='' OR a.name IS NULL "
            q_attr_sort = sort
            sort = 'a.value'

        # What to fetch
        if get_range:
            q_get_rng_select = ' min(c.timestamp), max(c.timestamp) '
        elif count:
            q_get_rng_select = ' count(c.id) '
        else:
            q_get_rng_select = ' c.id '
        """
        env.log.info(
            "SELECT %s "
            "FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
            " f.id=d.file) AS c %s %s %s "
            "WHERE %s %s %s "
            " ORDER BY %s %s" % 
            (q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
             q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit) % \
            (q_attr_sort,)
            )
        """

        cursor.execute(
            "SELECT %s "
            "FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
            " f.id=d.file) AS c %s %s %s "
            "WHERE %s AND c.deleted IS NULL %s %s "
            " ORDER BY %s %s" %
            (q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
             q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit),
            (q_attr_sort,)
            )

        if get_range:
            row = cursor.fetchone()
            return (row[0], row[1])
        elif count:
            row = cursor.fetchone()
            return row[0]
        else:
            dwn_list = []
            for row in cursor:
                dwn_list.append(DownloadData(env, req, id=row[0]))
            return (rec_count, dwn_list)
예제 #6
0
파일: model.py 프로젝트: nyuhuhuu/trachacks
 def fetch_downloads_list(env, req=None, sort='timestamp', desc=False, \
                          per_page=None, page=1, get_range=False, \
                          count=False, \
                          filter=None, filter_id=None, range=None):
     """
     Fetch list of downloads in needed sort and with needed offset and limit.
     """
     
     # Count offset
     if per_page == None:
         offset = 0
         q_limit = ' '
     else:
         offset = (page - 1) * per_page
         q_limit = ' LIMIT %d OFFSET %d ' % (per_page, offset)
     
     if desc:
         desc = ' DESC'
     else:
         desc = ''
     
     db = env.get_db_cnx()
     cursor = db.cursor()
     
     # Get count of all records
     cursor.execute("SELECT count(id) FROM downloader_downloaded")
     row = cursor.fetchone()
     rec_count = row[0]
     
     # Filter
     if filter_id:
         filter_id = int(filter_id)
     else:
         filter_id = 0
     if filter == 'category':
         q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
                      " downloader_file.id=c.file " +\
                      " LEFT OUTER JOIN downloader_release ON " + \
                      " downloader_release.id=downloader_file.release "
         q_fil_where = ' AND category=%d ' % (filter_id,)
     elif filter == 'release':
         q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
                      " downloader_file.id=c.file "
         q_fil_where = ' AND downloader_file.release=%d ' % (filter_id,)
     elif filter == 'file':
         q_fil_join = ' '
         q_fil_where = ' AND c.file=%d ' % (filter_id,)
     else:
         q_fil_join = ' '
         q_fil_where = ' '
         
     # Range
     if isinstance(range, tuple) and len(range) == 2:
         min_val, max_val = range
         q_rng_where = (" AND c.timestamp>= %d " + 
                       " AND c.timestamp<= %d ") % \
                       (min_val, max_val)
     else:
         q_rng_where = ' '
     
     # Fetech sorted piece of data
     q_attr_from = ''
     q_attr_on = ''
     q_attr_where = ' c.id > %s '
     q_attr_sort = 0    
     if sort == 'timestamp':
         sort = 'c.timestamp'
     elif sort == 'file_name':
         sort = 'c.name'
     else:
         q_attr_from = " LEFT OUTER JOIN " \
             " downloader_downloaded_attributes AS a "
         q_attr_on = " ON a.downloaded=c.id "
         q_attr_where = " a.name=%s " \
             "OR a.name='' OR a.name IS NULL "
         q_attr_sort = sort
         sort = 'a.value'
         
     # What to fetch
     if get_range:
         q_get_rng_select = ' min(c.timestamp), max(c.timestamp) '
     elif count:
         q_get_rng_select = ' count(c.id) '
     else:
         q_get_rng_select = ' c.id '
         
     """
     env.log.info(
         "SELECT %s "
         "FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
         " f.id=d.file) AS c %s %s %s "
         "WHERE %s %s %s "
         " ORDER BY %s %s" % 
         (q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
          q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit) % \
         (q_attr_sort,)
         )
     """
         
     cursor.execute(
         "SELECT %s "
         "FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
         " f.id=d.file) AS c %s %s %s "
         "WHERE %s AND c.deleted IS NULL %s %s "
         " ORDER BY %s %s" % 
         (q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
          q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit), 
         (q_attr_sort,)
         )
     
     if get_range:
         row = cursor.fetchone()
         return (row[0], row[1])
     elif count:
         row = cursor.fetchone()
         return row[0]
     else:
         dwn_list = []
         for row in cursor:
             dwn_list.append(DownloadData(env, req, id=row[0]))
         return (rec_count, dwn_list)