Examples with Loop io.github.ihongs.db.link.Loop used on opensource projects

Example 1 with Loop

use of io.github.ihongs.db.link.Loop in project HongsCORE by ihongs.

the class DataCmdlet method revert.

public static void revert(String[] args, Inst df) throws HongsException, InterruptedException {
    Map opts = CmdletHelper.getOpts(args, new String[] { "conf=s", "form=s", "user:s", "memo:s", "time:i", "bufs:i", "drop:b", "includes:b", "cascades:b", "!A", "?Usage: revert --conf CONF_NAME --form FORM_NAME [--time TIMESTAMP] ID0 ID1 ..." });
    String conf = (String) opts.get("conf");
    String form = (String) opts.get("form");
    String user = (String) opts.get("user");
    String memo = (String) opts.get("memo");
    long ct = Synt.declare(opts.get("time"), 0L);
    int bn = Synt.declare(opts.get("bufs"), 1000);
    Set<String> ds = Synt.asSet(opts.get(""));
    Data dr = df.getInstance(conf, form);
    dr.setUserId(Synt.defoult(user, Cnst.ADM_UID));
    // user = dr.getUserId( );
    form = dr.getFormId();
    Map sd = new HashMap();
    sd.put("memo", memo);
    Table tb = dr.getTable();
    String tn = tb.tableName;
    // 查询迭代
    Loop lp;
    // 操作总数
    int c = 0;
    // 变更计数
    int i = 0;
    // 事务计数
    int j = 0;
    if (ct == 0) {
        String fa = "`a`.*";
        String fc = "COUNT(*) AS _cnt_";
        String qa = "SELECT " + fa + " FROM `" + tn + "` AS `a` WHERE `a`.`form_id` = ? AND `a`.`etime`  = ?";
        String qc = "SELECT " + fc + " FROM `" + tn + "` AS `a` WHERE `a`.`form_id` = ? AND `a`.`etime`  = ?";
        if (!ds.isEmpty()) {
            c = ds.size();
            qa = qa + " AND a.id IN (?)";
            lp = tb.db.query(qa, 0, 0, form, 0, ds);
        } else {
            lp = tb.db.query(qa, 0, 0, form, 0);
            c = Synt.declare(tb.db.fetchOne(qc, form, 0).get("_cnt_"), 0);
        }
    } else {
        String fx = "`x`.*";
        String fa = "`a`.id, MAX(a.ctime) AS ctime";
        String fc = "COUNT(DISTINCT a.id) AS _cnt_";
        String qa = "SELECT " + fa + " FROM `" + tn + "` AS `a` WHERE `a`.`form_id` = ? AND `a`.`ctime` <= ?";
        String qc = "SELECT " + fc + " FROM `" + tn + "` AS `a` WHERE `a`.`form_id` = ? AND `a`.`ctime` <= ?";
        String qx = " WHERE x.id = b.id AND x.ctime = b.ctime AND x.`form_id` = ? AND x.`ctime` <= ?";
        if (!ds.isEmpty()) {
            c = ds.size();
            qa = qa + " AND a.id IN (?)";
            qx = qx + " AND x.id IN (?)";
            qa = qa + " GROUP BY `a`.id";
            qx = "SELECT " + fx + " FROM `" + tn + "` AS `x`, (" + qa + ") AS `b` " + qx;
            lp = tb.db.query(qx, 0, 0, form, ct, ds, form, ct, ds);
        } else {
            qa = qa + " GROUP BY `a`.id";
            qx = "SELECT " + fx + " FROM `" + tn + "` AS `x`, (" + qa + ") AS `b` " + qx;
            lp = tb.db.query(qx, 0, 0, form, ct, form, ct);
            c = Synt.declare(tb.db.fetchOne(qc, form, ct).get("_cnt_"), 0);
        }
    }
    /**
     * 清空全部数据
     * 以便更新结构
     */
    if (Synt.declare(opts.get("drop"), false)) {
        IndexWriter iw = dr.getWriter();
        /**/
        String pd = dr.getPartId();
        try {
            if (pd != null && !pd.isEmpty()) {
                iw.deleteDocuments(new Term("@" + Data.PART_ID_KEY, pd));
            } else {
                iw.deleteAll();
            }
            iw.commit();
            iw.deleteUnusedFiles();
            iw.maybeMerge();
        } catch (IOException ex) {
            throw new HongsException(ex);
        }
    }
    /**
     * 级联更新操作
     * 默认不作级联
     */
    Casc da = new Casc(dr, Synt.declare(opts.get("includes"), false), Synt.declare(opts.get("cascades"), false));
    boolean pr = (Core.DEBUG == 0);
    long tm = System.currentTimeMillis();
    long tc = tm / 1000;
    if (pr)
        CmdletHelper.progres(tm, c, i);
    dr.begin();
    for (Map od : lp) {
        String id = (String) od.get(Cnst.ID_KEY);
        if (Synt.declare(od.get("etime"), 0L) != 0L) {
            if (Synt.declare(od.get("state"), 1) >= 1) {
                sd.put("rtime", od.get("ctime"));
                dr.rev(id, sd, tc);
            } else {
                dr.del(id, sd, tc);
            }
        } else {
            if (Synt.declare(od.get("state"), 1) >= 1) {
                od = Synt.toMap(od.get("data"));
                od.putAll(sd);
                da.update(id, od);
            } else {
                dr.delDoc(id);
            }
        }
        ds.remove(id);
        i++;
        j++;
        if (j == bn) {
            j = 0;
            da.commit();
            dr.begin();
            if (pr) {
                CmdletHelper.progres(tm, c, i);
            }
        }
    }
    da.commit();
    dr.begin();
    if (pr) {
        CmdletHelper.progres(tm, c, i);
    }
    /**
     * 删掉多余数据
     */
    for (String id : ds) {
        dr.delDoc(id);
    }
    da.commit();
    if (pr) {
        CmdletHelper.progres();
    }
    CmdletHelper.println("Revert " + i + " item(s) for " + form + " to " + dr.getDbName());
}

Example 2 with Loop

use of io.github.ihongs.db.link.Loop in project HongsCORE by ihongs.

the class UserCmdlet method uproot.

/**
 * 归并账号
 * @param uid  目标账号
 * @param uids 被并账号
 * @throws HongsException
 */
public static void uproot(String uid, Set<String> uids) throws HongsException {
    DB db;
    Table tb;
    Loop lo;
    db = DB.getInstance("master");
    // ** 关联登录 **/
    tb = db.getTable("user_sign");
    tb.update(Synt.mapOf("user_id", uid), "`user_id` IN (?)", uids);
    // ** 用户权限 **/
    tb = db.getTable("user_role");
    lo = tb.fetchCase().filter("`user_id` = ?", uid).select("`role`").select();
    Set rids = new HashSet();
    for (Map ro : lo) {
        rids.add(ro.get("role"));
    }
    lo = tb.fetchCase().filter("`user_id` IN (?) AND `role` NOT IN (?)", uids, rids).select("`role`").select();
    rids.clear();
    for (Map ro : lo) {
        rids.add(ro.get("role"));
    }
    for (Object rid : rids) {
        tb.insert(Synt.mapOf("role", rid, "user_id", uid));
    }
    // ** 用户分组 **/
    tb = db.getTable("dept_user");
    lo = tb.fetchCase().filter("`user_id` = ?", uid).select("`dept_id`").select();
    Set dids = new HashSet();
    for (Map ro : lo) {
        dids.add(ro.get("dept_id"));
    }
    lo = tb.fetchCase().filter("`user_id` IN (?) AND `dept_id` NOT IN (?)", uids, dids).select("`dept_id`").select();
    dids.clear();
    for (Map ro : lo) {
        dids.add(ro.get("dept_id"));
    }
    for (Object did : dids) {
        tb.insert(Synt.mapOf("dept_id", did, "user_id", uid));
    }
    // ** 用户资料 **/
    tb = db.getTable("user");
    lo = tb.fetchCase().filter("`id` = ?", uid).select("`phone`,`phone_checked`,`email`,`email_checked`,`username`").select();
    Map info = new HashMap();
    boolean phoneChecked = false;
    boolean emailChecked = false;
    boolean loginChecked = false;
    for (Map ro : lo) {
        info.putAll(ro);
        Object phone = info.get("phone");
        if (Synt.declare(ro.get("phone_checked"), false) && phone != null && !phone.equals("")) {
            phoneChecked = true;
        }
        Object email = info.get("email");
        if (Synt.declare(ro.get("email_checked"), false) && email != null && !email.equals("")) {
            emailChecked = true;
        }
        Object login = info.get("username");
        if (login != null && !login.equals("")) {
            loginChecked = true;
        }
    }
    lo = tb.fetchCase().filter("`id` IN (?)", uids).assort("`ctime` DESC, `mtime` DESC").select("`phone`,`phone_checked`,`email`,`email_checked`,`username`,`password`,`passcode`").select();
    for (Map ro : lo) {
        if (!phoneChecked) {
            Object phone = ro.get("phone");
            if (Synt.declare(ro.get("phone_checked"), false) && phone != null && !phone.equals("")) {
                phoneChecked = true;
                info.put("phone_checked", 1);
                info.put("phone", phone);
            }
        }
        if (!emailChecked) {
            Object email = ro.get("email");
            if (Synt.declare(ro.get("email_checked"), false) && email != null && !email.equals("")) {
                emailChecked = true;
                info.put("email_checked", 1);
                info.put("email", email);
            }
        }
        if (!loginChecked) {
            Object login = ro.get("username");
            if (login != null && !login.equals("")) {
                loginChecked = true;
                info.put("username", login);
                info.put("password", info.get("password"));
                info.put("passcode", info.get("passcode"));
            }
        }
    }
    // 更新资料和权限时间
    long now = System.currentTimeMillis() / 1000;
    info.put("rtime", now);
    info.put("mtime", now);
    tb.update(info, "`id`  =  ? ", uid);
    // 其他用户标记为删除
    info.clear();
    info.put("state", 0);
    info.put("rtime", now);
    info.put("mtime", now);
    tb.update(info, "`id` IN (?)", uids);
    // ** 其他关联 **/
    /**
     * 仅能更新普通的关联到用户
     * 对那些有额外唯一约束的表
     * 请自行处理
     */
    db = DB.getInstance();
    String u = CoreConfig.getInstance("master").getProperty("core.master.uproot");
    if (null != u && !u.isEmpty())
        for (String n : u.split(",")) {
            int p = n.indexOf(":");
            if (p < 0) {
                throw new HongsException("Config item 'core.master.uproot' must be '[DB.]TABLE:FIELD'");
            }
            String t = n.substring(0, p).trim();
            String f = n.substring(1 + p).trim();
            tb = db.getTable(t);
            tb.db.execute("UPDATE `" + tb.tableName + "` SET `" + f + "` = ? WHERE `" + f + "` IN (?)", uid, uids);
        }
}

Example 3 with Loop

use of io.github.ihongs.db.link.Loop in project HongsCORE by ihongs.

the class DBFields method imports.

@Override
protected final void imports() throws HongsException {
    fields = new LinkedHashMap();
    try (Loop rs = db.query("SELECT * FROM `" + tn + "`", 0, 1)) {
        ResultSetMetaData md = rs.getMetaData();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            Map field = new HashMap();
            field.put("type", md.getColumnType(i));
            field.put("size", md.getPrecision(i));
            field.put("scale", md.getScale(i));
            field.put("unsigned", md.isSigned(i) != true);
            field.put("required", md.isNullable(i) != 1);
            // 用处不大的的属性:
            /*
        field.put("autoIncrement",  md.isAutoIncrement(i));
        field.put("caseSensitive",  md.isCaseSensitive(i));
        field.put("currency",       md.isCurrency(i));
        field.put("readOnly",       md.isReadOnly(i));
        field.put("writable",       md.isWritable(i));
        field.put("searchable",     md.isSearchable(i));
        field.put("tableName",      md.getTableName(i));
        field.put("schemaName",     md.getSchemaName(i));
        field.put("catalogName",    md.getCatalogName(i));
        field.put("label",          md.getColumnLable(i));
        field.put("typeName",       md.getColumnTypeName(i));
        field.put("className",      md.getColumnClassName(i));
        field.put("displaySize",    md.getColumnDisplaySize(i));
        */
            this.fields.put(md.getColumnName(i), field);
        }
    } catch (SQLException ex) {
        throw new HongsException(1068, ex);
    }
    // 由于 ResultSetMetaData 缺少默认值, 通过 DatabaseMetaData 作补充检测
    try (ResultSet rs = db.open().getMetaData().getColumns(null, "%", tn, "%")) {
        while (rs.next()) {
            String fn = rs.getString("COLUMN_NAME");
            int dt = rs.getInt("DATA_TYPE");
            Map fd = fields.get(fn);
            if (null != fd)
                continue;
            fd.put("required", (null == rs.getString("COLUMN_DEF")) && !"YES".equals(rs.getString("IS_NULLABLE")) && !"YES".equals(rs.getString("IS_AUTOINCREMENT")) && !"YES".equals(rs.getString("IS_GENERATEDCOLUMN")) && dt != Types.TIMESTAMP && dt != Types.TIMESTAMP_WITH_TIMEZONE);
        }
    } catch (SQLException ex) {
        throw new HongsException(1068, ex);
    }
}

Example 4 with Loop

use of io.github.ihongs.db.link.Loop in project HongsCORE by ihongs.

the class FetchMore method join.

/**
 * 获取关联数据
 * @param table 关联表
 * @param caze  附加查询
 * @param map   映射关系
 * @param col   关联字段
 * @throws io.github.ihongs.HongsException
 */
public void join(Table table, FetchCase caze, Map<String, List> map, String col) throws HongsException {
    if (map.isEmpty()) {
        return;
    }
    DB db = table.db;
    String name = table.name;
    String tableName = table.tableName;
    boolean multi = caze.getOption("ASSOC_MULTI", false);
    boolean merge = caze.getOption("ASSOC_MERGE", false);
    boolean fills = caze.getOption("ASSOC_FILLS", false);
    if (null != caze.name && 0 != caze.name.length()) {
        name = caze.name;
    }
    // 获取id及行号
    Set ids = map.keySet();
    if (ids.isEmpty()) {
        // throw new HongsException(1170, "Ids map is empty");
        return;
    }
    // 识别字段别名
    String rel = col;
    if (table.getFields().containsKey(col)) {
        col = "`" + name + "`.`" + col + "`";
    } else {
        Pattern pattern;
        Matcher matcher;
        do {
            pattern = Pattern.compile("^(.+?)(?:\\s+AS)?\\s+`?(.+?)`?$", Pattern.CASE_INSENSITIVE);
            matcher = pattern.matcher(col);
            if (matcher.find()) {
                col = matcher.group(1);
                rel = matcher.group(2);
                break;
            }
            pattern = Pattern.compile("^(.+?)\\.\\s*`?(.+?)`?$");
            matcher = pattern.matcher(col);
            if (matcher.find()) {
                col = matcher.group(0);
                rel = matcher.group(2);
                break;
            }
        } while (false);
    }
    // 构建查询结构
    caze.filter(col + " IN (?)", ids).from(tableName, name);
    // 获取关联数据
    Loop rs = db.queryMore(caze);
    /**
     * 根据之前的 ID=>行 关系以表名为键放入列表中
     */
    String sid;
    List lst;
    Map row, sub;
    // 登记已关联上的ID
    Set idz = new HashSet();
    // 暂存字段类型字典
    Map tdz = rs.getTypeDict();
    if (!multi) {
        while ((sub = rs.next()) != null) {
            sid = Synt.asString(sub.get(rel));
            lst = map.get(sid);
            idz.add(sid);
            if (lst == null) {
                // throw new HongsException(1170, "Line nums is null");
                continue;
            }
            Iterator it = lst.iterator();
            while (it.hasNext()) {
                row = (Map) it.next();
                if (!merge) {
                    row.put(name, sub);
                } else {
                    sub.putAll(row);
                    row.putAll(sub);
                }
            }
        }
    } else {
        while ((sub = rs.next()) != null) {
            sid = Synt.asString(sub.get(rel));
            lst = map.get(sid);
            idz.add(sid);
            if (lst == null) {
                // throw new HongsException(1170, "Line nums is null");
                continue;
            }
            Iterator it = lst.iterator();
            while (it.hasNext()) {
                row = (Map) it.next();
                if (row.containsKey(name)) {
                    ((List) row.get(name)).add(sub);
                } else {
                    List lzt = new ArrayList();
                    row.put(name, lzt);
                    lzt.add(sub);
                }
            }
        }
    }
    if (!fills) {
        return;
    }
    if (!multi && merge) {
        Set<String> padSet = tdz.keySet();
        for (Map.Entry<String, List> et : map.entrySet()) {
            String colKey = et.getKey();
            if (idz.contains(colKey)) {
                continue;
            }
            List<Map> mapLst = et.getValue();
            for (Map mapRow : mapLst) {
                for (String k : padSet) {
                    if (!mapRow.containsKey(k)) {
                        mapRow.put(k, null);
                    }
                }
            }
        }
    } else {
        Object padDat = !multi ? new HashMap() : new ArrayList();
        for (Map.Entry<String, List> et : map.entrySet()) {
            String colKey = et.getKey();
            if (idz.contains(colKey)) {
                continue;
            }
            List<Map> mapLst = et.getValue();
            for (Map mapRow : mapLst) {
                if (!mapRow.containsKey(name)) {
                    mapRow.put(name, padDat);
                }
            }
        }
    }
}

Example 5 with Loop

use of io.github.ihongs.db.link.Loop in project HongsCORE by ihongs.

the class FetchCase method getAll.

/**
 * 查询并获取全部结果
 * @return
 * @throws HongsException
 */
public List getAll() throws HongsException {
    List<Map> ra = new ArrayList();
    Map ro;
    try (Loop rs = select()) {
        while ((ro = rs.next()) != null) {
            ra.add(ro);
        }
    }
    return ra;
}