Esempio n. 1
0
  @Override
  public void createPeriodStructure() {
    try {
      jdbcTemplate.execute("DROP TABLE IF EXISTS " + TABLE_NAME_PERIOD_STRUCTURE);
    } catch (BadSqlGrammarException ex) {
      // Do nothing, table does not exist
    }

    String quote = statementBuilder.getColumnQuote();

    String sql =
        "CREATE TABLE "
            + TABLE_NAME_PERIOD_STRUCTURE
            + " (periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(15) NOT NULL, daysno INTEGER NOT NULL";

    for (PeriodType periodType : PeriodType.PERIOD_TYPES) {
      sql += ", " + quote + periodType.getName().toLowerCase() + quote + " VARCHAR(15)";
    }

    sql += ")";

    log.info("Create period structure SQL: " + sql);

    jdbcTemplate.execute(sql);

    final String isoInSql = "create unique index in_periodstructure_iso on _periodstructure(iso)";

    jdbcTemplate.execute(isoInSql);
  }
Esempio n. 2
0
  @Override
  public void createDatePeriodStructure() {
    try {
      jdbcTemplate.execute("DROP TABLE IF EXISTS " + TABLE_NAME_DATE_PERIOD_STRUCTURE);
    } catch (BadSqlGrammarException ex) {
      // Do nothing, table does not exist
    }

    String quote = statementBuilder.getColumnQuote();

    String sql =
        "CREATE TABLE "
            + TABLE_NAME_DATE_PERIOD_STRUCTURE
            + " (dateperiod DATE NOT NULL PRIMARY KEY";

    for (PeriodType periodType : PeriodType.PERIOD_TYPES) {
      sql += ", " + quote + periodType.getName().toLowerCase() + quote + " VARCHAR(15)";
    }

    sql += ")";

    log.info("Create date period structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
Esempio n. 3
0
  @Override
  public void populateCategoryStructure(List<DataElementCategory> categories) {
    String sql =
        "insert into "
            + CreateCategoryTableStatement.TABLE_NAME
            + " "
            + "select coc.categoryoptioncomboid as cocid, con.categoryoptioncomboname as cocname, ";

    for (DataElementCategory category : categories) {
      sql +=
          "("
              + "select co.name from categoryoptioncombos_categoryoptions cocco "
              + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid "
              + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid "
              + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid "
              + "and cco.categoryid = "
              + category.getId()
              + " "
              + "limit 1) as "
              + statementBuilder.columnQuote(category.getName())
              + ", ";

      sql +=
          "("
              + "select co.uid from categoryoptioncombos_categoryoptions cocco "
              + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid "
              + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid "
              + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid "
              + "and cco.categoryid = "
              + category.getId()
              + " "
              + "limit 1) as "
              + statementBuilder.columnQuote(category.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql +=
        "from categoryoptioncombo coc "
            + "inner join _categoryoptioncomboname con on coc.categoryoptioncomboid = con.categoryoptioncomboid";

    log.info("Populate category structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
Esempio n. 4
0
  @Override
  public void populateOrganisationUnitGroupSetStructure(List<OrganisationUnitGroupSet> groupSets) {
    String sql =
        "insert into "
            + CreateOrganisationUnitGroupSetTableStatement.TABLE_NAME
            + " "
            + "select ou.organisationunitid as organisationunitid, ou.name as organisationunitname, ";

    for (OrganisationUnitGroupSet groupSet : groupSets) {
      sql +=
          "("
              + "select oug.name from orgunitgroup oug "
              + "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid "
              + "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = "
              + groupSet.getId()
              + " "
              + "where ougm.organisationunitid = ou.organisationunitid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getName())
              + ", ";

      sql +=
          "("
              + "select oug.uid from orgunitgroup oug "
              + "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid "
              + "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = "
              + groupSet.getId()
              + " "
              + "where ougm.organisationunitid = ou.organisationunitid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from organisationunit ou";

    log.info("Populate organisation unit group set structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
Esempio n. 5
0
  @Override
  public void populateIndicatorGroupSetStructure(List<IndicatorGroupSet> groupSets) {
    String sql =
        "insert into "
            + CreateIndicatorGroupSetTableStatement.TABLE_NAME
            + " "
            + "select i.indicatorid as indicatorid, i.name as indicatorname, ";

    for (IndicatorGroupSet groupSet : groupSets) {
      sql +=
          "("
              + "select ig.name from indicatorgroup ig "
              + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid "
              + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = "
              + groupSet.getId()
              + " "
              + "where igm.indicatorid = i.indicatorid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getName())
              + ", ";

      sql +=
          "("
              + "select ig.uid from indicatorgroup ig "
              + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid "
              + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = "
              + groupSet.getId()
              + " "
              + "where igm.indicatorid = i.indicatorid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from indicator i";

    log.info("Populate indicator group set structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
Esempio n. 6
0
  @Override
  public void populateDataElementGroupSetStructure(List<DataElementGroupSet> groupSets) {
    String sql =
        "insert into "
            + CreateDataElementGroupSetTableStatement.TABLE_NAME
            + " "
            + "select d.dataelementid as dataelementid, d.name as dataelementname, ";

    for (DataElementGroupSet groupSet : groupSets) {
      sql +=
          "("
              + "select deg.name from dataelementgroup deg "
              + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid "
              + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = "
              + groupSet.getId()
              + " "
              + "where degm.dataelementid = d.dataelementid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getName())
              + ", ";

      sql +=
          "("
              + "select deg.uid from dataelementgroup deg "
              + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid "
              + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = "
              + groupSet.getId()
              + " "
              + "where degm.dataelementid = d.dataelementid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from dataelement d";

    log.info("Populate data element group set structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
Esempio n. 7
0
  @Override
  public void createCategoryStructure(List<DataElementCategory> categories) {
    try {
      jdbcTemplate.execute("DROP TABLE IF EXISTS " + CreateCategoryTableStatement.TABLE_NAME);
    } catch (BadSqlGrammarException ex) {
      // Do nothing, table does not exist
    }

    final String statement =
        new CreateCategoryTableStatement(categories, statementBuilder.getColumnQuote())
            .getStatement();

    log.info("Create category structure table SQL: " + statement);

    jdbcTemplate.execute(statement);
  }
Esempio n. 8
0
  @Override
  public void createIndicatorGroupSetStructure(List<IndicatorGroupSet> groupSets) {
    try {
      jdbcTemplate.execute(
          "DROP TABLE IF EXISTS " + CreateIndicatorGroupSetTableStatement.TABLE_NAME);
    } catch (BadSqlGrammarException ex) {
      // Do nothing, table does not exist
    }

    final String statement =
        new CreateIndicatorGroupSetTableStatement(groupSets, statementBuilder.getColumnQuote())
            .getStatement();

    log.info("Create indicator group set table SQL: " + statement);

    jdbcTemplate.execute(statement);
  }
Esempio n. 9
0
  @Override
  public void createOrganisationUnitStructure(int maxLevel) {
    try {
      jdbcTemplate.execute("DROP TABLE IF EXISTS " + TABLE_NAME_ORGANISATION_UNIT_STRUCTURE);
    } catch (BadSqlGrammarException ex) {
      // Do nothing, table does not exist
    }

    String quote = statementBuilder.getColumnQuote();

    StringBuilder sql = new StringBuilder();

    sql.append("CREATE TABLE ")
        .append(TABLE_NAME_ORGANISATION_UNIT_STRUCTURE)
        .append(
            " ( organisationunitid INTEGER NOT NULL PRIMARY KEY, organisationunituid CHARACTER(11), level INTEGER");

    for (int k = 1; k <= maxLevel; k++) {
      sql.append(", ")
          .append(quote)
          .append("idlevel" + k)
          .append(quote)
          .append(" INTEGER, ")
          .append(quote)
          .append("uidlevel" + k)
          .append(quote)
          .append(" CHARACTER(11)");
    }

    sql.append(");");

    log.info("Create organisation unit structure table SQL: " + sql);

    jdbcTemplate.execute(sql.toString());

    final String uidInSql =
        "create unique index in_orgunitstructure_organisationunituid on _orgunitstructure(organisationunituid)";

    jdbcTemplate.execute(uidInSql);
  }