def external_point_update(point, user):
    db_point = list(point_by_uuid(point.uuid) or [])
    if db_point:
        # !!! should we update lat, lon too?
        point.id = db_point[0].id
        web.query("""
            UPDATE locations
                SET title=$title
                WHERE id = $id
                ;
            """, vars=point.copy())
        # how to update tags???
        # tags_insert(point, user, point.tags, deny_namespaces=[])
        return "update"
    else:
        web.insert("locations", 
                    lat=point.lat, 
		    lon=point.lon, 
		    title=point.title,
                    uuid=point.uuid,
                    user_id=user.id, 
		    origin=point.origin,
                    added=point.added,  #!!!?
		    url=point.url)
        db_point = list(point_by_uuid(point.uuid))[0]
        tags_insert(db_point, user, point.tags, deny_namespaces=[])
        point.id = db_point.id
        return "insert"

def clean(lifetime=2592000):
    """Delete all sessions older than lifetime
    We could call this on every request, but may as well just do it 
    at some periodic interval. """
    timestamp = int(time.time()) - lifetime
    web.query("DELETE FROM sessions WHERE timestamp < $timestamp", vars=locals())
    return True

def remove_datasource_from_project(ds, project):
    web.query("""
        DELETE FROM locations_datasources
            WHERE locations_datasources.location_id = $project_id
                AND locations_datasources.datasource_id = $ds_id
            ;
    """, vars=dict(ds_id=ds.id, project_id=project.id))

def drupy_cron():
    """clean out old stuff in the watchdog table"""
    time1 = time() - variable_get('watchdog_clear', 604800)
    time2 = time() - 3600
    web.transact()
    web.query('DELETE FROM watchdog WHERE timestamp < $time1', vars=locals())
    web.query('DELETE FROM flood WHERE timestamp < $time2', vars=locals())
    web.commit()

def point_update(point, user):
    web.query("""
        UPDATE locations
            SET title=$title,
                uuid=$uuid
            WHERE id = $id
            ;
        """, vars=point.copy())

def regenerate(uid=0):
    """Called when an anonymous user becomes authenticated or vice-versa."""
    old_session_id = web.cookies()._SID_
    new_session_id = _generate_id()
    web.setcookie("_SID_",new_session_id)
    #uid = int(uid)
    #print web.query("UPDATE sessions SET uid = '$uid', sid = $new_session_id WHERE sid = $old_session_id", vars=locals(),_test=True)
    web.query("UPDATE sessions SET uid = $uid, sid = $new_session_id WHERE sid = $old_session_id", vars=locals())

def remove_point_from_project(point, project):
    web.query("""
        UPDATE projects_points
            SET visible = 0
            WHERE location_id = $point_id
                AND project_id = $project_id
            ;
    """, vars=dict(point_id=point.id, project_id=project.id))

def unset_policies(object, user, roles, adder_user):
    for role in roles:
        web.query("""
        DELETE FROM locations_policy_table
            WHERE user_id = $user_id 
            AND location_id=$location_id
            AND role=$role
        ;""", vars={'user_id': user.id, 'location_id': object.id,
            'role': role})

 def delete(self, id, providerName):
     username = self.usernameProvider.get()
     web.transact() 
     web.delete('places',  where="providerid=%s and provider=%s" % \
         (web.db.sqlquote(id), web.db.sqlquote(providerName)))
     web.query(self.SQL_ACTION % (web.db.sqlquote(username),
                      web.db.sqlquote('elimino propiedad %s-%s' %
                      (providerName, id))));
     web.commit()

def query(sql_query, vars={}, limit = 10, count_query = None, processed=False, _test=False):
    """Works very similar to web.query(), but it returns a tuple of the query result 
    and pager object (which just holds the page numbers links to display)
    
    Typical use:
    iter_nodes, page_nums = inc.pager.query('SELECT * FROM node WHERE uid=5 ORDER BY nid')
    for node in iter_nodes:
        print node
    print page_nums.render()
    
    DOCTEST
    >>> import pager
    >>> limit=10
    >>> iter_nodes, page_nums = pager.query('''SELECT n.nid, c.cache, c.nid \
    ...     AS cache_nid, c.vid as cache_vid, n.vid, n.type, \
    ...     n.status, n.created, n.changed, n.comment, n.promote, n.sticky, \
    ...     u.uid, u.name, u.picture, u.data FROM node n INNER JOIN \
    ...     users u ON u.uid = n.uid LEFT JOIN cache_node c ON c.nid = n.nid \
    ...     AND c.vid = n.vid WHERE n.promote = 1 AND n.status = 1 \
    ...     ORDER BY n.sticky DESC, n.created DESC''',limit=limit, _test=True)
    count_query: SELECT COUNT(*) FROM node n INNER JOIN     users u ON u.uid = n.uid LEFT JOIN cache_node c ON c.nid = n.nid     AND c.vid = n.vid WHERE n.promote = 1 AND n.status = 1    
    >>> iter_nodes
    <sql: 'SELECT n.nid, c.cache, c.nid     AS cache_nid, c.vid as cache_vid, n.vid, n.type,     n.status, n.created, n.changed, n.comment, n.promote, n.sticky,     u.uid, u.name, u.picture, u.data FROM node n INNER JOIN     users u ON u.uid = n.uid LEFT JOIN cache_node c ON c.nid = n.nid     AND c.vid = n.vid WHERE n.promote = 1 AND n.status = 1     ORDER BY n.sticky DESC, n.created DESC LIMIT 10 OFFSET 40'>
    
    NOTE: right now the regex only works when the sql is all caps.  
        i.e. inc.pager.query('select * From node WHERE uid=5 ORDER BY nid')
        would not work.  It's a good convention, but maybe should fix the regex
        to accept non caps in the future?
    """

    if not processed and not isinstance(sql_query, web.db.SQLQuery):
        sql_query = str(web.db.reparam(sql_query, vars))

    if not count_query:
        p = re.compile(r'SELECT.*?FROM (.*) ORDER BY .*')
        count_query = p.sub(lambda m: "SELECT COUNT(*) FROM %s" % m.group(1), sql_query)

    if _test:
        num_pages=10
        page = 5
        print 'count_query:', count_query
    else:
        count = web.query(count_query)[0].values()[0]
        num_pages = int(float(count) / limit + 1)
        page = _current_page()
    
    #page number validation
    #todo !!! wait a minute, maybe these two lines are no good. 
    # cause then there can be many urls for the first and last pages...
    if page < 1: page=1
    elif page > num_pages: page=num_pages
    
    p = pager(page,num_pages)
    
    offset = (page-1)*limit
    return web.query(''.join((sql_query,' LIMIT $limit OFFSET $offset')),
            vars={'limit':limit,'offset':offset},_test=_test), p

 def __delitem__(self, key):
     if not isinstance(key, tuple):
         key = None, key
     web.query("""DELETE FROM profile 
             WHERE lang=$lang AND prop_key=$prop_key;""", 
             vars=dict(lang=key[0], prop_key=key[1],))
     try:
         del self.__dict__["_data"][key]
     except:
         pass  # no problem if the key is not there

 def __setitem__(self, key, item):
     # this should be transaction, but is ok as
     # profile should not be edited by many people at once
     self.__delitem__(key)
     if not isinstance(key, tuple):
         key = None, key
     web.query("""INSERT INTO profile (lang, prop_key, prop_value) 
                 VALUES ($lang, $prop_key, $prop_value);""", 
               vars=dict(lang=key[0], prop_key=key[1], prop_value=item))
     self.__dict__["_data"][key] = item

    def POST(self, providerName, id, state):
        lid = scrappers[providerName].local(id)
        web.transact() 
        web.query(self.SQL_UPDATE % (web.db.sqlquote(state), lid))
        web.query(self.SQL_ACTION % (web.db.sqlquote(usernameProvider.get()),
          web.db.sqlquote('cambio al estado %s la prop  %s-%s' % 
             (state,providerName,id))));

        web.commit()
        web.seeother('../../')

def multiple_insert(table, values, seqname=None):
    """Inserts multiple rows into a table using sql copy."""
    def escape(value):
        if value is None:
            return "\N"
        elif isinstance(value, basestring): 
            value = value.replace('\\', r'\\') # this must be the first one
            value = value.replace('\t', r'\t')
            value = value.replace('\r', r'\r')
            value = value.replace('\n', r'\n')
            return value
        elif isinstance(value, bool):
            return value and 't' or 'f'
        else:
            return str(value)
            
    def increment_sequence(seqname, n):
        """Increments a sequence by the given amount."""
        d = web.query(
            "SELECT setval('%s', $n + (SELECT last_value FROM %s), true) + 1 - $n AS START" % (seqname, seqname), 
            locals())
        return d[0].start
        
    def write(path, data):
        f = open(path, 'w')
        f.write(web.utf8(data))
        f.close()
        
    if not values:
        return []
        
    if seqname is None:
        seqname = table + "_id_seq"
        
    #print "inserting %d rows into %s" % (len(values), table)
        
    columns = get_table_columns(table)
    if seqname:
        n = len(values)
        start = increment_sequence(seqname, n)
        ids = range(start, start+n)
        for v, id in zip(values, ids):
            v['id'] = id
    else:
        ids = None
        
    data = []
    for v in values:
        assert set(v.keys()) == set(columns)
        data.append("\t".join([escape(v[c]) for c in columns]))
        
    filename = tempfile.mktemp(suffix='.copy', prefix=table)
    write(filename, "\n".join(data))
    web.query("COPY %s FROM '%s'" % (table, filename))
    return ids

def get_default_values(sel_ik, index):
  """will print all the possible keys an index could use in order to still
  show at least one file with the current selection of i/k in mind"""
  value_per_index = []


  #get all the possible values from the index
  if sel_ik:

    #building the ignore clause
    for (sel_index, sel_value) in sel_ik:
      if sel_index == index:
        if value_per_index:
          value_per_index.append(sel_value)
        else:
          value_per_index = [sel_value, ]
    if value_per_index:
      ignore_clause = "and %s not in ("+", ".join(['"'+ivalue+'"' for ivalue in value_per_index])+")"
    else:
      ignore_clause = ""

    if index in config.have_many_values:
      additional_clauses = build_clauses(sel_ik, 'and ', 'images.')
      if ignore_clause:
        temp_ignore_clause = ignore_clause % "value"
      else:
        temp_ignore_clause = ""
      #web.debug('GET VALUE : select value, count(images.id) as quantity from images_%(index)ss , %(index)ss, images where %(index)s_id = %(index)ss.id and images_%(index)ss.image_id = images.id %(temp_ignore_clause)s %(additional_clauses)s group by %(index)s_id' % (vars()))
      return web.query('select value, count(images.id) as quantity from images_%ss , %ss, images where %s_id = %ss.id and images_%ss.image_id = images.id %s %s group by %s_id order by %s' % (index, index, index, index, index, temp_ignore_clause , additional_clauses, index, get_order(index)))
    else:
      additional_clauses = build_clauses(sel_ik, 'and ')
      if index in config.compound_indexes.keys():
        #need to get database specific query to match value
        db_value =  config.compound_indexes[index]['reverse_query']()
      else:
        db_value = index
      if ignore_clause:
        temp_ignore_clause = ignore_clause % db_value
      else:
        temp_ignore_clause = ""
      #web.debug('GET VALUE: select %s as value, count(id) as quantity from images where 1=1 %s %s group by value' % (db_value, temp_ignore_clause , additional_clauses))
      return web.query('select %s as value, count(id) as quantity from images where 1=1 %s %s group by value order by %s' % (db_value, temp_ignore_clause , additional_clauses, get_order(index)))
  else:
    #simpler case, left here for the sake of simplicity
    if index in config.have_many_values:
      #web.debug('select value, count(image_id) as quantity from images_%ss , %ss where %s_id = id  group by %s_id' % (index, index, index, index))
      return web.query('select value, count(image_id) as quantity from images_%ss , %ss where %s_id = id  group by %s_id order by %s' % (index, index, index, index , get_order(index)))
    else :
      if index in config.compound_indexes.keys():
        #need to get database specific query to match value
        db_value =  config.compound_indexes[index]['reverse_query']()
      else:
        db_value = index
      return web.query('select %s as value, count(id) as quantity from images group by value order by %s' % (db_value, get_order(index)))

def group_update(user, group):
    #!!! delete?
    web.query("""
        DELETE FROM group_users
            WHERE user_id = $user_id 
            AND group_id = $group_id;
    """, vars=dict(user_id=user.id, group_id=group.id))
    return web.query("""
        INSERT INTO group_users (user_id, group_id) 
            VALUES ($user_id, $group_id);
    """, vars=dict(user_id=user.id, group_id=group.id))

def get_all_values(index):
  if index in config.have_many_values:
    db_values = web.query('select value from %ss' % index)
    return [row.value for row in db_values]
  elif index in config.compound_indexes.keys():
    compound_info = config.compound_indexes[index]
    db_values = web.query('select distinct(%s) as value from images order by %s' % (compound_info['reverse_query'](), get_order(index)))
    return [str(row.value) for row in db_values]
  else:
    db_values = web.query('select distinct(%s) as value from images order by %s' % (index, get_order(index)))
    return [row.value for row in db_values]

def select(query, chunk_size=50000):
    """Selects large number of rows efficiently using cursors."""
    web.transact()
    web.query('DECLARE select_cursor CURSOR FOR ' + query)
    while True:
        result = web.query('FETCH FORWARD $chunk_size FROM select_cursor', vars=locals())
        if not result:
            break
        for r in result:
            yield r
    web.rollback()

def point_full_update(point, user):
    web.query("""
        UPDATE locations
            SET title=$title,
                uuid=$uuid,
                lat=$lat,
                lon=$lon,
                visible=$visible,
                url=$url
            WHERE id = $id
            ;
        """, vars=point.copy())

def store_infos(infos, extra_db_entries):
  print " %s" % (infos)
  #web.debug(" %s" % (infos))
  simple_infos = infos.copy()
  multiple_infos = {}
  for imv in config.have_many_values:
    try:
      del simple_infos[imv]
      multiple_infos[imv] = infos[imv]
    except KeyError:
      pass
  #checking for file renaming with sha
  possiblePrevFiles = web.query("select id, filename, batch from images where sha ='"+infos['sha']+"'")
  updatingFile = False
  if len(possiblePrevFiles) == 1:
    #file found in db
    print "INFO duplicate found : "+infos['filename']
    prevFile = possiblePrevFiles[0]
    file_id = prevFile.id
    simple_infos['batch'] = prevFile.batch
    try:
      extra_db_entries.remove(prevFile.filename)
      web.update('images', 'id = %s' % file_id, None, **simple_infos)
      updatingFile = True
    except ValueError:
      #raise with .remove when the filename do not match
      print "WARNING duplicate sha accross fileset, creating new entry"
  else:
    if len(possiblePrevFiles) > 1:
      #more than one file with this sha... 
      print "INFO sha present multiple time for file : "+infos["filename"]
    file_id = web.insert('images', True, **simple_infos)

  for index in multiple_infos.keys():
    #store the value in its table
    for value in multiple_infos[index]:
      try:
        value_id = web.insert(index+'s', True, **{"value" : value})
        #debuginsert(index+'s', False, **{"value" : value})
      except: 
        #TODO should be IntegrityError for mysql but not sure how best integrate that without breaking the DB abstraction...
        #but if the error wasn't an IntegrityError then the next line should fail
        value_id = web.query('select id from %ss where value = "%s"' % (index, value))[0].id
      #store the relationship between the value and the file
      try:
        web.insert("images_"+index+'s', False, **{index+"_id": value_id, "image_id" : file_id})
      except Exception, inst:
        #if we are update a file we might raise some integrity error here
        if updatingFile:
          pass
        else:
          raise inst