def process_single_query(self, name, need_list, param):
        config = self.parse_param(name, param)
        count = 0

        query = select(config.columns, config.condition, from_obj=[config.table])
        if need_list:
            if config.order_by:
                query = query.order_by(*config.order_by)
            if config.group_by:
                query = query.group_by(*config.group_by)
            if config.total:
                if DEBUG:
                    log.debug('Query Schema {} Count:'.format(config.name))
                    log.debug(query.count())
                count = do_(query.count()).scalar()
            if config.page > 0:
                query = query.limit(config.limit).offset((config.page-1)*config.limit)
            if DEBUG:
                log.debug('Query Schema {}:'.format(config.name))
                log.debug(query)
            result = {'data': [dict(row) for row in do_(query)]}
            if config.total:
                result['total'] = count
        else:
            query = query.limit(1)
            if DEBUG:
                log.debug('Query Schema {}:'.format(config.name))
            result = list(do_(query))
            if result:
                result = dict(result[0])
            else:
                result = {}
        return result

def scan_step_all_path():
    from uliweb import settings

    ScanPathes = get_model("scanpathes")
    ScanPathes.remove()

    allext = {}
    root_dp = settings.SCAN.DIR
    ScanPathes(path = ".",type = "d",).save()

    count = 0
    Begin()

    IGNORE_DIRS_SET = set(settings.SCAN.DIR_IGNORE)
    for root,dirs,files in os.walk(root_dp):
        root_relp = os.path.relpath(root,root_dp)
        if not isinstance(root_relp,unicode):
            root_relp = root_relp.decode("utf8")
        sys.stdout.write(".")
        rp = ScanPathes.get(ScanPathes.c.path==root_relp)
        if not rp:
            print "\ncan not find in db so do not scan %s"%(root)
            continue
        ignore_dirs = []
        for dn in dirs:
            dp = os.path.join(root,dn)
            if os.path.islink(dp):
                print "\nignore link:%s"%(dp)
                ignore_dirs.append(dn)
            elif dn in IGNORE_DIRS_SET:
                print "\nignore dir: %s"%(dp)
                ignore_dirs.append(dn)
            else:
                relp = os.path.relpath(dp,root_dp)
                do_(ScanPathes.table.insert().values(path = relp.decode("utf8"),type = "d",parent=rp.id))
        for dn in ignore_dirs:
            dirs.remove(dn)
        l = root.split(os.sep)
        for fn in files:
            fp = os.path.join(root,fn)
            if not os.path.islink(fp):
                p,ext = os.path.splitext(fn)
                relp = os.path.relpath(fp,root_dp)
                do_(ScanPathes.table.insert().values(path = relp.decode("utf8"),type = "f",ext=ext,parent=rp.id))
                if allext.has_key(ext):
                    allext[ext] += 1
                else:
                    allext[ext] = 1
            else:
                print "\nignore link:%s"%(fp)
    Commit()

    Exts = get_model("exts")
    Exts.remove()
    for i,k in enumerate(allext):
        Exts(ext = k,num = allext[k]).save()
    print

def load_table(table, filename, con, delimiter=',', format=None, encoding='utf-8', delete=True):
    import csv
    from uliweb.utils.date import to_date, to_datetime
    
    table = reflect_table(con, table.name)
    
    if delete:
        do_(table.delete())
    
    if not os.path.exists(filename):
        log.info("The table [%s] data is not existed." % table.name)
        return 
    
    f = fin = open(filename, 'rb')
    try:
        first_line = f.readline()
        fields = first_line[1:].strip().split()
        n = 1
        if format:
            fin = csv.reader(f, delimiter=delimiter)
            
        for line in fin:
            try:
                n += 1
                if not format:
                    line = eval(line.strip())
                record = dict(zip(fields, line))
                params = {}
                for c in table.c:
                    if c.name in record:
                        if not format:
                            params[c.name] = record[c.name]
                        else:
                            if record[c.name] == 'NULL':
                                params[c.name] = None
                            else:
                                if isinstance(c.type, String):
                                    params[c.name] = unicode(record[c.name], encoding)
                                elif isinstance(c.type, Date):
                                    params[c.name] = to_date(to_datetime(record[c.name]))
                                elif isinstance(c.type, DateTime):
                                    params[c.name] = to_datetime(record[c.name])
                                else:
                                    params[c.name] = record[c.name]
                ins = table.insert().values(**params)
                do_(ins)
            except:
                log.error('Error: Line %d' % n)
                raise
    finally:
        f.close()

    def api_schema_perm_update(self):
        value = request.POST.get('value')
        schema = request.POST.get('scheme')
        permid = request.POST.get('permid')
        selectedRoleList = _json.loads(value)
        #----delete the origin role_perm_rel
        RPRModel = get_model('Role_Perm_Rel')
        do_(RPRModel.table.delete().where(and_(RPRModel.c.scheme == schema, RPRModel.c.permission == permid)))
        #----batch add the role_perm_rel
        for s in selectedRoleList:
            roleid = s.split('_')[0]
            RPRModel(role=roleid, permission=permid, scheme=schema).save()

        return json({"status":"OK", "value":value})

    def _sync_parent(self, parent):
        from sqlalchemy import select, func
        from uliweb.orm import do_

        M = functions.get_model('async_tasks')
        sql = select([M.c.status, func.count('*')], M.c.parent_task==parent.task_id,
                     from_obj=[M.table]).group_by(M.c.status)
        status = {}
        for row in do_(sql):
            status[row[0]] = row[1]

        queued = status.get('0', 0)
        success = status.get('1', 0)
        started = status.get('2', 0)
        error = status.get('E', 0)
        failed = status.get('F', 0)
        cancel = status.get('C', 0)

        if started:
            parent.status = '2'
        elif failed:
            parent.status = 'F'
        elif error:
            parent.status = 'E'
        elif (queued and not filter(None, [success, started, error, failed, cancel])):
            parent.status = '0'
        elif (success and not filter(None, [queued, started, error, failed])):
            parent.status = '1'
        elif (cancel and not filter(None, [queued, success, started, error, failed])):
            parent.status = 'C'

    def handle(self, options, global_options, *args):
        
        engine = get_engine(options, global_options)

        if args:
            apps = args
        else:
            apps = self.get_apps(global_options)
        
        tables = get_tables(global_options.apps_dir, apps, engine=options.engine, 
            settings_file=global_options.settings, 
            local_settings_file=global_options.local_settings)
        
        for name, t in tables.items():
            exist = engine.dialect.has_table(engine.connect(), name)
            if not exist:
                flag = 'NOT EXISTED'
            else:
                try:
                    result = list(do_(t.select().limit(1)))
                    flag = 'OK'
                except Exception as e:
                    if options.traceback:
                        import traceback
                        traceback.print_exc()
                    flag = 'FAILED'
                
            if global_options.verbose or flag!='OK':
                print 'Validating [%s] %s...%s' % (options.engine, name, flag)

    def api_org_roles_load(self, org_id):
        page = int(request.GET.get('iDisplayStart') or 0)
        psize = int(request.GET.get('iDisplayLength') or 10)
        RoleModel = get_model('role')
        UserModel = get_model('user')
        OrgRoleModel = get_model('orgrole')
        UserGroupModel = get_model('usergroup')

        totalRecords = OrgRoleModel.filter(OrgRoleModel.c.organization == org_id).count()
        query = select([RoleModel.c.id.label('role_id'), RoleModel.c.name.label('role_name'), OrgRoleModel.c.organization, OrgRoleModel.c.id.label('orgrole_id')]).select_from(join(RoleModel.table, OrgRoleModel.table, RoleModel.c.id == OrgRoleModel.c.role)).where(OrgRoleModel.c.organization == org_id).offset(page).limit(psize)
        result = do_(query)
        ret = {}
        def fetch_users(orgrole_id):
            ret = []
            userList = UserModel.filter(OrgRoleModel.users.join_filter(OrgRoleModel.c.id == orgrole_id))
            for s in userList:
                ret.append({s.id:s.username})
            return ret

        def fetch_usergroups(orgrole_id):
            ret = []
            userList = UserGroupModel.filter(OrgRoleModel.usergroups.join_filter(OrgRoleModel.c.id == orgrole_id))
            for s in userList:
                ret.append({s.id:s.name})
            return ret

        ret['aaData'] = [{'role_id':s['role_id'], 'role_name':s['role_name'], 'users':fetch_users(s.orgrole_id), 'usergroups':fetch_usergroups(s.orgrole_id), 'orgrole_id':s['orgrole_id']} for s in result]
        ret['iTotalRecords'] = totalRecords
        ret['iTotalDisplayRecords'] = totalRecords
        return json(ret)

def dump_table(table, filename, con, std=None, delimiter=',', format=None, encoding='utf-8', inspector=None):
    from uliweb.utils.common import str_value
    from StringIO import StringIO
    import csv
    
    if not std:
        if isinstance(filename, (str, unicode)):
            std = open(filename, 'w')
        else:
            std = filename
    else:
        std = sys.stdout
    #add inspector table columns process, will not use model fields but database fields
    if inspector:
        meta = MetaData()
        table = Table(table.name, meta)
        inspector.reflecttable(table, None)
        
    result = do_(table.select())
    fields = [x.name for x in table.c]
    if not format:
        print >>std, '#' + ' '.join(fields)
    elif format == 'txt':
        print >>std, '#' + ','.join(fields)
    for r in result:
        if not format:
            print >>std, r
        elif format == 'txt':
            buf = StringIO()
            fw = csv.writer(buf, delimiter=delimiter)
            fw.writerow([str_value(x, encoding=encoding) for x in r])
            print >>std, buf.getvalue().rstrip()
        else:
            raise Exception, "Can't support the text format %s" % format

    def run(self):
        import datetime
        from decimal import Decimal

        row = self.queue.get()
        if row == None:
            self.is_exit = 'quit'
            return
        data = eval(row)
        if self.bulk:
            self.buf.append(data)
            if len(self.buf) >= self.bulk:
                do_(self.sql, args=self.buf)
                self.buf = []
        else:
            do_(self.sql, args=[data])
        self.total += 1

    def detail(self, schema_id):
        OrgModel = get_model('rbacorg')
        RoleModel = get_model('role')
        PermModel = get_model('Permission')
        RPRModel = get_model('Role_Perm_Rel')

        query = select([self.model.c.id, self.model.c.name, self.model.c.gorg, self.model.c.description, OrgModel.c.name.label('org_name')]).select_from(join(self.model.table, OrgModel.table, self.model.c.gorg == OrgModel.c.id)).where(self.model.c.id == schema_id)
        schemaList = do_(query)
        return {'schemaid':schema_id, 'schema_obj':schemaList.fetchone()}

 def api_list(self):
     page = int(request.GET.get('iDisplayStart', 0))
     psize = int(request.GET.get('iDisplayLength', 10))
     totalRecords = self.model.count()
     query = select([self.model.c.id, self.model.c.name, self.model.c.gorg, self.model.c.description]).select_from(self.model.table).order_by(self.model.c.id.desc()).offset((page)).limit(psize)
     schemaList = do_(query)
     ret = {}
     ret['aaData'] = [{'id':s['id'], 'name':s['name'], 'description':s['description'], 'gorg':s['gorg']} for s in schemaList]
     ret['iTotalRecords'] = totalRecords
     ret['iTotalDisplayRecords'] = totalRecords
     return json(ret)

 def decide_path(id):
     path = get_path(id)
     if path.type == 'f':
         return path.crbits or 0,path.crindex_bits or 0
     else:
         #print "dir %d crtype ?"%(id)
         crbits = 0x00
         crindex_bits = 0x00
         for p in get_children_pathes(id):
             crbits_child,crindexbits_child = decide_path(p.id)
             #print "\t",p.path
             #print "\t%d|%d="%(crindex_bits,crindexbits_child),
             crbits |= crbits_child
             crindex_bits |= crindexbits_child
             #print crindex_bits
         crtype = crbits2crtype(crbits)
         if (path.crtype!=crtype) or (path.crbits!=crbits) or (path.crindex_bits!=crindex_bits):
             do_(ScanPathes.table.update().where(ScanPathes.c.id==path.id).values(crtype=crtype,crbits=crbits,crindex_bits=crindex_bits))
         print "%s\t0x%04x\t0x%08x\t%s"%(crtype2csstag(crtype)[3:8],crbits,crindex_bits,path.path)
     return crbits,crindex_bits

 def org_roles(self, org_id):
     SchemaModel = get_model('rbacscheme')
     #----check if the organization is a global organization
     Org = self.model.get(org_id)
     RoleModel = get_model('role')
     RPRModel = get_model('Role_Perm_Rel')
     if Org.rbacscheme:
         query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.rbacscheme == SchemaModel.c.id)).where(self.model.c.id == org_id)
         OrgObj = do_(query).fetchone()
         query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.rbacscheme)
     else:
         #----global organization
         query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.id == SchemaModel.c.gorg)).where(self.model.c.id == org_id)
         OrgObj = do_(query).fetchone()
         query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.schema_id)
     #----need to filter the rols which belone to this schema
     roleList = do_(query)
     roleDict = {}
     for s in roleList:
         roleDict[s.id] = s.name
     return {'orgid':org_id, 'orgname':OrgObj.name, 'schemaname':OrgObj.schema_name, 'schema':OrgObj.rbacscheme , 'schema_id':OrgObj.schema_id, 'roleDict':roleDict}

 def get_paragraph_comments_count(self, cid):
     """
     获得某个章节每个paragraph评论的数目
     返回结果为：
     
     {id1:count1, id2:count2,...}
     """
     from uliweb.orm import do_
     from sqlalchemy.sql import select, func, and_
     
     query = select([func.count(1), self.model_comments.c.anchor], 
         and_(self.model_comments.c.chapter==int(cid),
             self.model_comments.c.deleted==False)).group_by(self.model_comments.c.anchor)
     d = {}
     for row in do_(query):
         d[row[1]] = row[0]
     return json(d)

def dump_table(table, filename, con, std=None, delimiter=',', format=None, 
    encoding='utf-8', inspector=None, engine_name=None):
    from uliweb.utils.common import str_value
    from StringIO import StringIO
    import csv
    
    b = time()
    if not std:
        if isinstance(filename, (str, unicode)):
            std = open(filename, 'w')
        else:
            std = filename
    else:
        std = sys.stdout
    #add inspector table columns process, will not use model fields but database fields
    if inspector:
        meta = MetaData()
        table = Table(table.name, meta)
        inspector.reflecttable(table, None)
        
    result = do_(table.select(), engine_name)
    fields = [x.name for x in table.c]
    if not format:
        print >>std, ' '.join(fields)
    elif format == 'txt':
        print >>std, ','.join(fields)
    n = 0
    if format == 'txt':
        fw = csv.writer(std, delimiter=delimiter)
    for r in result:
        n += 1
        if not format:
            print >>std, r
        elif format == 'txt':
            fw.writerow([str_value(x, encoding=encoding, newline_escape=True) for x in r])
        else:
            raise Exception, "Can't support the text format %s" % format
  
    return 'OK (%d/%lfs)' % (n, time()-b)

    def handle(self, options, global_options, *args):

        engine = get_engine(options, global_options)

        if args:
            apps = args
        else:
            apps = self.get_apps(global_options)

        tables = get_sorted_tables(
            get_tables(
                global_options.apps_dir,
                apps,
                engine_name=options.engine,
                settings_file=global_options.settings,
                local_settings_file=global_options.local_settings,
            )
        )
        _len = len(tables)

        for i, (name, t) in enumerate(tables):
            exist = engine.dialect.has_table(engine.connect(), name)
            if not exist:
                flag = "NOT EXISTED"
            else:
                try:
                    result = list(do_(t.select().limit(1)))
                    flag = "OK"
                except Exception as e:
                    if options.traceback:
                        import traceback

                        traceback.print_exc()
                    flag = "FAILED"

            if global_options.verbose or flag != "OK":
                print "Validating [%s] %s...%s" % (options.engine, show_table(name, t, i, _len), flag)

    def api_schema_perm_load(self, schema_id):
        page = int(request.GET.get('iDisplayStart') or 0)
        psize = int(request.GET.get('iDisplayLength') or 10)
        RoleModel = get_model('role')
        PermModel = get_model('Permission')
        RPRModel = get_model('Role_Perm_Rel')

        perm_rpr = select([PermModel.c.id.label('perm_id'), PermModel.c.name.label('perm_name'), RPRModel.c.scheme, RPRModel.c.role ]).select_from(join(PermModel.table, RPRModel.table, PermModel.c.id == RPRModel.c.permission)).alias()
        query = select([perm_rpr.c.perm_id, perm_rpr.c.perm_name, perm_rpr.c.scheme, RoleModel.c.id.label('role_id'), RoleModel.c.name.label('role_name') ]).select_from(join(perm_rpr, RoleModel.table, RoleModel.c.id == perm_rpr.c.role)).where(perm_rpr.c.scheme == schema_id)  # .offset((page)).limit(psize)
        result = do_(query)
        ret = {}
        #----prepare dataset and do the pagnization in memery
        dataset = {}
        permDict = {}
        for s in result:
            if s['perm_name'] in dataset.keys():
                t = dataset.get(s['perm_name'])
                t.append({s['role_id']:s['role_name']})
            else:
                dataset[s['perm_name']] = [{s['role_id']:s['role_name']}]
                permDict[s['perm_name']] = s['perm_id']

        def fetch_role_id(rols):
            ret = []
            for s in rols:
                for k in s:
                    ret.append(k)
            return ret
        #----pagination in memery
        totalRecords = len(dataset)
        dataset = OrderedDict(sorted(dataset.items(), key=lambda t: t[0]))
        from itertools import islice
        dataset = OrderedDict(islice(dataset.items(), page, psize))
        ret['aaData'] = [{'perm':s, 'roles':dataset[s], 'role_id_list':fetch_role_id(dataset[s]), 'perm_id':permDict[s]} for s in dataset]
        ret['iTotalRecords'] = totalRecords
        ret['iTotalDisplayRecords'] = totalRecords
        return json(ret)

     def pre_save(data):
         from sqlalchemy.sql import select, func
 
         data['topic'] = int(topic_id)
         data['parent'] = int(parent_id)
         data['floor'] = (do_(select([func.max(Post.c.floor)], Post.c.parent==post.id)).scalar() or 0) + 1

    def handle(self, options, global_options, *args):
        import os,re
        from uliweb import settings

        self.get_application(global_options)

        exts_ignore_dict = {}
        for ext in settings.SCAN.FILE_EXTS_IGNORE:
            exts_ignore_dict[ext]=True

        ScanPathes = get_model("scanpathes")
        CopyrightInfo = get_model('copyrightinfo')
        CopyrightInfo.remove()
        files = ScanPathes.filter(ScanPathes.c.type=='f')
        restring = get_restring_from_relist(settings.SCAN.RE_LIST)
        cobj_copyright = re.compile(restring,re.M)
        root_dp = settings.SCAN.DIR
        count = 0
        nback = 0
        tnum = ScanPathes.filter(ScanPathes.c.type=='f').count()
        Begin()
        for path in files:
            if not exts_ignore_dict.has_key(path.ext):
                fp = os.path.join(root_dp,path.path)
                f = open(fp)
                c = f.read()
                f.close()
                crbits = 0
                isbin = False

                crindex_bits = 0
                cribegin = -1
                criend = -1

                if c[:4]=='\x7fELF':
                    isbin = True
                elif c[:8]=='!<arch>\n':
                    isbin = True
                elif c[:6]=='CATI\x01\x00':
                    isbin = True
                if not isbin:
                    for m in cobj_copyright.finditer(c):
                        d = m.groupdict()
                        for i,k in enumerate(d):
                            if d[k]!=None:
                                index = int(k[1:])
                                crindex_bits |= (0x01<<index)
                                crbits |= index2crbits(k,settings.SCAN.RE_LIST)
                                ibegin = m.start(0)
                                iend = m.end(0)
                                do_(CopyrightInfo.table.insert()
                                    .values(path = path.id,
                                        crindex = index,
                                        ibegin = ibegin,
                                        iend = iend,
                                    )
                                )
                                if cribegin<0 or ibegin<cribegin:
                                    cribegin = ibegin
                                if criend<0 or iend>criend:
                                    criend = iend
                crtype = crbits2crtype(crbits)
                do_(ScanPathes.table.update()
                    .where(ScanPathes.c.id==path.id)
                    .values(copyright=((crbits&CRBITS_COPYRIGHT)!=0),
                            copyright_inhouse=((crbits&CRBITS_COPYRIGHT_INHOUSE)!=0),
                            copyright_gpl=((crbits&CRBITS_COPYRIGHT_GPL)!=0),
                            copyright_oos=((crbits&CRBITS_COPYRIGHT_OOS)!=0),
                            crbits = crbits,
                            crtype = crtype,
                            crindex_bits = crindex_bits,
                            cribegin = cribegin,
                            criend = criend
                            )
                    )
            count+=1
            s = "%d/%d"%(count,tnum)
            out = "%s%s"%(nback*"\b",s)
            print out,
            sys.stdout.flush()
            nback = len(s)+1
        Commit()

 def get_children_pathes(id):
     r = do_(select(ScanPathes.c, ScanPathes.c.parent==id))
     return r.fetchall()