Projekt

Allgemein

Profil

Herunterladen als
Herunterladen (31,9 KB) Statistiken
| Zweig: | Revision:
c22391b2 Christian Ehringfeld
/*
* Copyright (C) 2015 Christian Ehringfeld <c.ehringfeld@t-online.de>
*
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
* for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with this program; if not, see <http://www.gnu.org/licenses/>.
*/
14f9beed Christian Ehringfeld
#include "querybuilder.h"
b0bf458e Christian Ehringfeld
#include "database.h"
#include <QMetaObject>
#include <QMetaProperty>
#include "entity.h"
a47954c0 Christian Ehringfeld
#include <QRegularExpression>
d568923d Christian Ehringfeld
#include "entityinstancefactory.h"
a47954c0 Christian Ehringfeld
b0bf458e Christian Ehringfeld
using namespace CuteEntityManager;
14f9beed Christian Ehringfeld
e0e1ead8 Christian Ehringfeld
QueryBuilder::QueryBuilder(QSharedPointer<Schema> schema,
QSharedPointer<Database> database) {
b0bf458e Christian Ehringfeld
this->schema = schema;
this->database = database;
14f9beed Christian Ehringfeld
}

24425325 Christian Ehringfeld
QueryBuilder::~QueryBuilder() {
}

b0bf458e Christian Ehringfeld
bool QueryBuilder::createTable(const QSharedPointer<Entity> &entity) const {
bool rc = false;
ce6994c4 Christian Ehringfeld
if (entity.data()) {
2e43da3f Christian Ehringfeld
auto tableDefinition = this->generateTableDefinition(entity);
QString tableName = entity.data()->getTablename();
ce6994c4 Christian Ehringfeld
this->schema.data()->containsTable(tableName) ? rc = true : rc = false;
if (!rc) {
QSqlQuery q = this->database.data()->getQuery(this->createTable(tableName,
tableDefinition));
if (this->database.data()->transaction(q)) {
this->schema.data()->getTableSchema(tableName);
rc = true;
if (rc) {
rc = this->createIndices(entity);
}
2e43da3f Christian Ehringfeld
}
b0bf458e Christian Ehringfeld
}
}
return rc;
2e43da3f Christian Ehringfeld
}

bool QueryBuilder::createIndices(const QSharedPointer<Entity> &entity) const {
Entity *e = entity.data();
bool ok = true;
QStringList queries = QStringList();
QString superIndex = this->createFkSuperClass(e);
ce6994c4 Christian Ehringfeld
if (!superIndex.isEmpty()) {
2e43da3f Christian Ehringfeld
queries.append(superIndex);
}
47f9301a Christian Ehringfeld
queries.append(this->relationFks(entity));
2e43da3f Christian Ehringfeld
ok = this->database.data()->transaction(queries);
return ok;
}

dd45fcf5 Christian Ehringfeld
47f9301a Christian Ehringfeld
QStringList QueryBuilder::relationFks(const QSharedPointer<Entity> &entity)
const {
dd45fcf5 Christian Ehringfeld
QStringList queries = QStringList();
b0e92bc6 Christian Ehringfeld
if (this->supportsForeignKeys()) {
auto relations = entity.data()->getNonInheritedRelations();
auto props = entity.data()->getMetaProperties();
auto iterator = relations.constBegin();
while (iterator != relations.constEnd()) {
auto relation = iterator.value();
if (relation.getMappedBy().isEmpty() && !relation.getCascadeType().isEmpty()) {
QString update = relation.getCascadeType().contains(MERGE)
|| relation.getCascadeType().contains(ALL) ? this->getForeignKeyCascade(
CASCADE) : this->getForeignKeyCascade(NO_ACTION);
QString remove = relation.getCascadeType().contains(REMOVE)
|| relation.getCascadeType().contains(ALL) ? this->getForeignKeyCascade(
CASCADE) : this->getForeignKeyCascade(SET_NULL);
this->createRelationFK(queries, entity, relation,
props.value(relation.getPropertyName()), update, remove);
}
++iterator;
ce6994c4 Christian Ehringfeld
}
dd45fcf5 Christian Ehringfeld
}
return queries;
}

b0e92bc6 Christian Ehringfeld
bool QueryBuilder::supportsForeignKeys() const {
return true;
}

47f9301a Christian Ehringfeld
void QueryBuilder::createRelationFK(QStringList &queries,
const QSharedPointer<Entity> &entity, const Relation &relation,
const QMetaProperty &metaProperty, const QString &update,
const QString &remove) const {
QSharedPointer<Entity> ptr = QSharedPointer<Entity>
(EntityInstanceFactory::createInstance(metaProperty.type()));
if (ptr.data()) {
if (relation.getType() == ONE_TO_ONE || relation.getType() == MANY_TO_ONE) {
QString indexName = this->generateIndexName(relation.getPropertyName(),
entity.data()->getTablename(),
this->generateColumnNameID(relation.getPropertyName()),
ptr.data()->getTablename(), true);
queries.append(this->addForeignKey(indexName, entity.data()->getTablename(),
QStringList(this->generateColumnNameID(relation.getPropertyName())),
ptr.data()->getTablename(),
QStringList(ptr.data()->getPrimaryKey()), remove, update));

} else if (relation.getType() == MANY_TO_MANY) {
QString tableName = this->generateManyToManyTableName(entity, ptr);
queries.append(this->createForeignKeyManyToMany(tableName, entity, update,
remove));
queries.append(this->createForeignKeyManyToMany(tableName, ptr, update,
remove));
}
}
}

QString QueryBuilder::createForeignKeyManyToMany(const QString &tableName,
const QSharedPointer<Entity> &entity, const QString &update,
const QString &remove) const {
QString fkColumn = this->generateManyToManyColumnName(entity);
QString indexName = this->generateIndexName(fkColumn,
tableName, fkColumn,
entity.data()->getTablename(), true);
return this->addForeignKey(indexName, tableName, QStringList(fkColumn),
entity.data()->getTablename(), QStringList(entity.data()->getPrimaryKey()),
remove, update);
}

ce6994c4 Christian Ehringfeld
QString QueryBuilder::createTable(const QString &tableName,
const QHash<QString, QString> &tableDefinition) const {
2e43da3f Christian Ehringfeld
return this->createTableQuery(tableName,
ce6994c4 Christian Ehringfeld
tableDefinition);
2e43da3f Christian Ehringfeld
}

ce6994c4 Christian Ehringfeld
QString QueryBuilder::createFkSuperClass(const Entity *e) const {
2e43da3f Christian Ehringfeld
QString r = "";
auto superMetaObject = e->metaObject()->superClass();
if (e->getInheritanceStrategy() == JOINED_TABLE
&& QString(superMetaObject->className()) != QString("Entity")) {
ce6994c4 Christian Ehringfeld
Entity *superClass = EntityInstanceFactory::createInstance(
superMetaObject->className());
if (superClass) {
QString refColumn = superClass->getPrimaryKey();
QString refTable = superClass->getTablename();
r = this->addForeignKey(this->generateIndexName(e->getPrimaryKey(),
e->getTablename(), refColumn, refTable, true), e->getTablename(),
f62afb13 Christian Ehringfeld
QStringList(e->getPrimaryKey()), refTable, QStringList(refColumn),
this->getForeignKeyCascade(CASCADE),
ce6994c4 Christian Ehringfeld
this->getForeignKeyCascade(CASCADE));
delete superClass;
2e43da3f Christian Ehringfeld
}
}
return r;
b0bf458e Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::createTableQuery(const QString &tableName,
const QHash<QString, QString> &tableDefinition) const {
b0bf458e Christian Ehringfeld
bool first = true;
QString s = "CREATE TABLE ";
s.append(this->schema.data()->quoteTableName(tableName).append(" (\n"));
f4e3904b Christian Ehringfeld
auto i = tableDefinition.constBegin();
b0bf458e Christian Ehringfeld
while (i != tableDefinition.constEnd()) {
if (first) {
first = false;
} else {
9c2f773f Christian Ehringfeld
s.append(", ");
b0bf458e Christian Ehringfeld
}
e0e1ead8 Christian Ehringfeld
s.append(this->schema.data()->quoteColumnName(i.key())).append(" " +
this->getColumnType(
i.value()));
b0bf458e Christian Ehringfeld
++i;
}
s.append("\n);");
return s;
}

d99101ae Christian Ehringfeld
QString QueryBuilder::renameTable(QString tableName, QString newName) const {
e0e1ead8 Christian Ehringfeld
return "RENAME TABLE " + this->schema.data()->quoteTableName(
tableName) + " TO " +
this->schema.data()->quoteTableName(
d99101ae Christian Ehringfeld
newName);
24425325 Christian Ehringfeld
}

d99101ae Christian Ehringfeld
QString QueryBuilder::dropTable(QString tableName) const {
return "DROP TABLE " + this->schema.data()->quoteTableName(tableName);
24425325 Christian Ehringfeld
}

d99101ae Christian Ehringfeld
QString QueryBuilder::truncateTable(QString tableName) const {
return "TRUNCATE TABLE " + this->schema.data()->quoteTableName(tableName);
24425325 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::addColumn(QString tableName, QString columnName,
QString columnType) const {
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " ADD " +
this->schema.data()->quoteColumnName(
d99101ae Christian Ehringfeld
columnName) + " " + this->getColumnType(columnType);
24425325 Christian Ehringfeld
}

QString QueryBuilder::dropColumn(QString tableName, QString columName) const {
e0e1ead8 Christian Ehringfeld
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " DROP COLUMN " +
d99101ae Christian Ehringfeld
this->schema.data()->quoteColumnName(columName);
24425325 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::renameColumn(QString tableName, QString oldName,
QString newName) const {
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " RENAME COLUMN " +
this->schema.data()->quoteColumnName(oldName) + " TO " +
this->schema.data()->quoteColumnName(
newName);
24425325 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::alterColumn(QString tableName, QString columnName,
QString newType) const {
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " CHANGE " +
d99101ae Christian Ehringfeld
this->schema.data()->quoteColumnName(columnName) + " " +
this->schema.data()->quoteColumnName(columnName) + this->getColumnType(newType);
24425325 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::addPrimaryKey(QString name, QString tableName,
QStringList columns) const {
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " ADD CONSTRAINT " +
this->schema.data()->quoteColumnName(name) + "PRIMARY KEY (" +
this->buildColumns(columns) + " )";
24425325 Christian Ehringfeld
}

QString QueryBuilder::dropPrimaryKey(QString name, QString tableName) const {
e0e1ead8 Christian Ehringfeld
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " DROP CONSTRAINT " +
d99101ae Christian Ehringfeld
this->schema.data()->quoteColumnName(name);
24425325 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::addForeignKey(QString name, QString tableName,
QStringList columns,
QString refTableName,
QStringList refColumns, QString deleteConstraint,
QString updateConstraint) const {
QString r = "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + "ADD CONSTRAINT " +
d99101ae Christian Ehringfeld
this->schema.data()->quoteColumnName(name)
e0e1ead8 Christian Ehringfeld
+ " FOREIGN KEY (" + this->buildColumns(columns) + ")" + " REFERENCES " +
this->schema.data()->quoteTableName(
d99101ae Christian Ehringfeld
refTableName) +
" (" + this->buildColumns(refColumns) + ")";
if (!deleteConstraint.isEmpty()) {
r.append(" ON DELETE " + deleteConstraint);
}
if (!updateConstraint.isEmpty()) {
r.append(" ON UPDATE " + updateConstraint);
}
return r;
24425325 Christian Ehringfeld
}

2e43da3f Christian Ehringfeld
QString QueryBuilder::generateIndexName(const QString &name,
const QString &table, const QString &refColumn, const QString &refTable,
const bool fk) const {
return QString(fk ? "fk" : "idx").append("_").append(name).append(table).append(
47f9301a Christian Ehringfeld
refColumn).append(refTable);
f62afb13 Christian Ehringfeld
}

47f9301a Christian Ehringfeld
QString QueryBuilder::generateColumnNameID(QString name) const {
f62afb13 Christian Ehringfeld
return name.append("_id");
2e43da3f Christian Ehringfeld
}

ce6994c4 Christian Ehringfeld
QString QueryBuilder::getForeignKeyCascade(DbForeignKeyCascade cascade) const {
47f9301a Christian Ehringfeld
QString r = "";
ce6994c4 Christian Ehringfeld
switch (cascade) {
case RESTRICT:
47f9301a Christian Ehringfeld
r = "RESTRICT";
ce6994c4 Christian Ehringfeld
break;
case CASCADE:
47f9301a Christian Ehringfeld
r = "CASCADE";
ce6994c4 Christian Ehringfeld
break;
case NO_ACTION:
47f9301a Christian Ehringfeld
r = "NO ACTION";
ce6994c4 Christian Ehringfeld
break;
case SET_DEFAULT:
47f9301a Christian Ehringfeld
r = "SET DEFAULT";
ce6994c4 Christian Ehringfeld
break;
case SET_NULL:
47f9301a Christian Ehringfeld
r = "SET NULL";
ce6994c4 Christian Ehringfeld
break;
}
47f9301a Christian Ehringfeld
return r;
ce6994c4 Christian Ehringfeld
}

24425325 Christian Ehringfeld
QString QueryBuilder::dropForeignKey(QString name, QString tableName) const {
e0e1ead8 Christian Ehringfeld
return "ALTER TABLE " + this->schema.data()->quoteTableName(
tableName) + " DROP CONSTRAINT " +
d99101ae Christian Ehringfeld
this->schema.data()->quoteColumnName(name);
24425325 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::createIndex(QString name, QString tableName,
QStringList columns,
bool unique) const {
QString s = (unique ? "CREATE UNIQUE INDEX " : "CREATE INDEX ") +
this->schema.data()->quoteTableName(
d99101ae Christian Ehringfeld
name) + " ON " + this->schema.data()->quoteTableName(tableName) + " (";
s.append(this->buildColumns(columns));
s.append(");");
return s;
24425325 Christian Ehringfeld
}

QString QueryBuilder::dropIndex(QString name, QString tableName) const {
e0e1ead8 Christian Ehringfeld
return "DROP INDEX " + this->schema.data()->quoteTableName(name) + " ON " +
this->schema.data()->quoteTableName(
ce6994c4 Christian Ehringfeld
tableName);
24425325 Christian Ehringfeld
}

b0bf458e Christian Ehringfeld
QSharedPointer<Database> QueryBuilder::getDatabase() const {
return database;
}
14f9beed Christian Ehringfeld
b0bf458e Christian Ehringfeld
void QueryBuilder::setDatabase(const QSharedPointer<Database> &value) {
database = value;
14f9beed Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::buildCreateQuery(QHash<QString, QVariant>::const_iterator
i,
dc6b13b4 Christian Ehringfeld
QHash<QString, QVariant>::const_iterator end,
QString &p1, QString &p2) const {
bool first = true;
while (i != end) {
if (!first) {
p1 += ",";
p2 += ",";
} else {
first = false;
}
p1 += this->schema.data()->quoteColumnName(i.key());
p2 += ":" + i.key();
++i;
}
p1 += ")";
p2 += ");";
return p1 + p2;
}

e0e1ead8 Christian Ehringfeld
QHash<QString, QString> QueryBuilder::generateTableDefinition(
const QSharedPointer<Entity> &entity)
const {
b0bf458e Christian Ehringfeld
auto map = QHash<QString, QString>();
auto o = entity.data()->metaObject();
1a3b37ba Christian Ehringfeld
auto superMetaObject = entity.data()->metaObject()->superClass();
47f9301a Christian Ehringfeld
auto superMetaObjectPropertyMap = entity.data()->getSuperMetaProperties();
d99101ae Christian Ehringfeld
QHash<QString, Relation> relations = entity.data()->getRelations();
b0bf458e Christian Ehringfeld
for (int var = 0; var < o->propertyCount(); ++var) {
auto m = o->property(var);
1a3b37ba Christian Ehringfeld
if (!superMetaObjectPropertyMap.contains(QString(m.name()))
&& m.name() != QString("objectName") && m.isReadable()
9c2f773f Christian Ehringfeld
&& !entity.data()->getTransientAttributes().contains(m.name())) {
6dc7d533 Christian Ehringfeld
if (m.isEnumType()) {
e0e1ead8 Christian Ehringfeld
map.insert(m.name(), this->schema.data()->getTypeMap().data()->value(
this->schema.data()->TYPE_INTEGER));
d99101ae Christian Ehringfeld
} else if (relations.contains(m.name())) {
Relation r = relations.value(m.name());
e0e1ead8 Christian Ehringfeld
if (r.getType() == RelationType::MANY_TO_ONE
|| (r.getType() == RelationType::ONE_TO_ONE
&& r.getMappedBy().isEmpty())) {
47f9301a Christian Ehringfeld
map.insert(this->generateColumnNameID(QString(m.name())),
this->schema.data()->TYPE_BIGINT);
d99101ae Christian Ehringfeld
}
6dc7d533 Christian Ehringfeld
} else if (entity.data()->getBLOBColumns().contains(m.name())) {
e0e1ead8 Christian Ehringfeld
map.insert(m.name(), this->schema.data()->getTypeMap().data()->value(
this->schema.data()->TYPE_BINARY));
6dc7d533 Christian Ehringfeld
} else {
e0e1ead8 Christian Ehringfeld
map.insert(m.name(), this->transformAbstractTypeToRealDbType(
this->transformTypeToAbstractDbType(
m.typeName())));
6dc7d533 Christian Ehringfeld
}
b0bf458e Christian Ehringfeld
}
}
2e43da3f Christian Ehringfeld
if (QString(superMetaObject->className()) != QString("Entity")
&& entity.data()->getInheritanceStrategy() != JOINED_TABLE) {
map.insert(entity.data()->getPrimaryKey(), this->schema.data()->TYPE_BIGPK);
6dc7d533 Christian Ehringfeld
}
b0bf458e Christian Ehringfeld
return map;
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::generateManyToManyTableName(const QSharedPointer<Entity>
&firstEntity,
ba800d6d Christian Ehringfeld
const QSharedPointer<Entity> &secondEntity) const {
2075db87 Christian Ehringfeld
QString first = QString(firstEntity.data()->getClassname());
QString second = QString(secondEntity.data()->getClassname());
if (QString::compare(first, second, Qt::CaseSensitive) <= 0) {
return firstEntity.data()->getTablename() + "_" +
secondEntity.data()->getTablename();
98b5b08d Christian Ehringfeld
} else {
2075db87 Christian Ehringfeld
return secondEntity.data()->getTablename() + "_" +
firstEntity.data()->getTablename();
98b5b08d Christian Ehringfeld
}
}

e0e1ead8 Christian Ehringfeld
QHash<QString, QHash<QString, QString>> QueryBuilder::generateRelationTables(
const QSharedPointer<Entity> &entity)
ba800d6d Christian Ehringfeld
const {
auto relations = QHash<QString, QHash<QString, QString>>();
813205af Christian Ehringfeld
QHash<QString, Relation> m = entity.data()->getRelations();
f4e3904b Christian Ehringfeld
auto props = entity.data()->getMetaProperties();
ba800d6d Christian Ehringfeld
for (auto i = m.begin(); i != m.end(); ++i) {
813205af Christian Ehringfeld
Relation r = i.value();
ba800d6d Christian Ehringfeld
if (r.getType() == MANY_TO_MANY && r.getMappedBy().isEmpty()) {
813205af Christian Ehringfeld
QHash<QString, QString> h = QHash<QString, QString>();
ba800d6d Christian Ehringfeld
h.insert("id", this->schema.data()->TYPE_BIGPK);
e0e1ead8 Christian Ehringfeld
h.insert(this->generateManyToManyColumnName(entity),
this->schema.data()->TYPE_BIGINT);
03fe3afa Christian Ehringfeld
auto m = props.value(r.getPropertyName());
d568923d Christian Ehringfeld
Entity *e = EntityInstanceFactory::createInstance(m.type());
03fe3afa Christian Ehringfeld
QSharedPointer<Entity> ptr = QSharedPointer<Entity>(e);
706de2e8 Christian Ehringfeld
h.insert(this->generateManyToManyColumnName(ptr),
03fe3afa Christian Ehringfeld
this->schema.data()->TYPE_BIGINT);
2075db87 Christian Ehringfeld
relations.insert(this->generateManyToManyTableName(entity, ptr), h);
813205af Christian Ehringfeld
}
}
97f9a843 Christian Ehringfeld
return relations;
813205af Christian Ehringfeld
}


6dc7d533 Christian Ehringfeld
QString QueryBuilder::transformTypeToAbstractDbType(QString typeName) const {
24425325 Christian Ehringfeld
QHash<QString, QString> *m = this->schema.data()->getAbstractTypeMap().data();
if (m->contains(typeName)) {
return m->value(typeName);
}
813205af Christian Ehringfeld
for (auto i = m->begin(); i != m->end(); ++i) {
24425325 Christian Ehringfeld
if (i.key().contains(typeName)) {
return i.value();
}
}
return this->schema.data()->TYPE_TEXT;
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::transformAbstractTypeToRealDbType(
QString typeName) const {
6dc7d533 Christian Ehringfeld
return this->schema.data()->getTypeMap().data()->value(typeName);
}

a47954c0 Christian Ehringfeld
QString QueryBuilder::getColumnType(const QString &type) const {
d99101ae Christian Ehringfeld
/**
a47954c0 Christian Ehringfeld
* @WARNING
d99101ae Christian Ehringfeld
*/
a47954c0 Christian Ehringfeld
QHash<QString, QString> *tMap = this->schema.data()->getTypeMap().data();
if (tMap->contains(type)) {
return this->transformAbstractTypeToRealDbType(type);
}
//cant believe that this could work in Qt
//https://github.com/yiisoft/yii2/blob/master/framework/db/QueryBuilder.php
e0e1ead8 Christian Ehringfeld
QRegularExpression reg = QRegularExpression(
QRegularExpression::escape("/^(\\w+)\((.+?)\\)(.*)$/"));
a47954c0 Christian Ehringfeld
reg.optimize();
e0e1ead8 Christian Ehringfeld
QRegularExpressionMatchIterator i = reg.globalMatch(type, 0,
QRegularExpression::PartialPreferFirstMatch);
a47954c0 Christian Ehringfeld
short s = 0;
bool ok = false;
QString before = "";
while (i.hasNext() && s < 2) {
before = i.next().captured();
if (tMap->contains(before)) {
ok = true;
}
if (ok) {
e0e1ead8 Christian Ehringfeld
return before.replace(QRegularExpression::escape("/\\(.+\\)/"),
"(" + i.next().captured() + ")");
a47954c0 Christian Ehringfeld
}
s++;
}
9c2f773f Christian Ehringfeld
reg = QRegularExpression(QRegularExpression::escape("/^(\\w+)\\s+/"));
i = reg.globalMatch(type, 0, QRegularExpression::PartialPreferFirstMatch);
a47954c0 Christian Ehringfeld
if (i.hasNext()) {
return before.replace(QRegularExpression::escape("/^w+/"), i.next().captured());
}
return type;
d99101ae Christian Ehringfeld
}

dc6b13b4 Christian Ehringfeld
/**
* @brief QueryBuilder::find
* @param id
* @param tableName
* @return
*/
QSqlQuery QueryBuilder::find(const qint64 &id, const QString &tableName) const {
e0e1ead8 Christian Ehringfeld
QSqlQuery q = this->database.data()->getQuery("SELECT * FROM " +
this->schema.data()->quoteTableName(
dc6b13b4 Christian Ehringfeld
tableName) + " WHERE id= :id LIMIT 1;");
q.bindValue(":id", id);
return q;
}

/**
* @brief QueryBuilder::findByAttributes
* @param m
* @param tableName
* @param ignoreID
* @return
*/
e0e1ead8 Christian Ehringfeld
QSqlQuery QueryBuilder::findByAttributes(const QHash<QString, QVariant> &m,
const QString &tableName,
c599658a Christian Ehringfeld
const bool &ignoreID, const qint64 limit, const qint64 offset) const {
e0e1ead8 Christian Ehringfeld
QSqlQuery q = this->database.data()->getQuery("SELECT * FROM " +
this->schema.data()->quoteTableName(
c599658a Christian Ehringfeld
tableName) + this->where(m, "AND", ignoreID) + this->limit(limit, offset));
dc6b13b4 Christian Ehringfeld
this->bindValues(m, q, ignoreID);
return q;
}

e0e1ead8 Christian Ehringfeld
QSqlQuery QueryBuilder::findByAttributes(const QSharedPointer<Entity> &e,
bool ignoreID,
const qint64 limit,
c599658a Christian Ehringfeld
const qint64 offset) {
e0e1ead8 Christian Ehringfeld
QHash<QString, QVariant> values = this->getEntityAttributes(
e.data()->getMetaProperties(), e);
return this->findByAttributes(values, e.data()->getTablename(), ignoreID, limit,
offset);
dc6b13b4 Christian Ehringfeld
}

QSqlQuery QueryBuilder::findAll(const QString &tableName) const {
e0e1ead8 Christian Ehringfeld
return this->database->getQuery("SELECT * FROM " +
this->schema.data()->quoteTableName(
dc6b13b4 Christian Ehringfeld
tableName) + ";");
}

QSqlQuery QueryBuilder::remove(const QSharedPointer<Entity> &entity) const {
e0e1ead8 Christian Ehringfeld
QSqlQuery q = this->database->getQuery("DELETE FROM " +
this->schema.data()->quoteTableName(
entity.data()->getTablename()) + " WHERE " +
this->schema.data()->quoteColumnName("id") + "=:id;");
dc6b13b4 Christian Ehringfeld
q.bindValue(":id", entity.data()->getId());
return q;
}

QSqlQuery QueryBuilder::findId(const QSharedPointer<Entity> &entity) const {
e0e1ead8 Christian Ehringfeld
QHash<QString, QVariant> values = this->getEntityAttributes(
entity.data()->getMetaProperties(),
entity);
QSqlQuery q = this->database.data()->getQuery("SELECT " +
this->schema.data()->quoteColumnName("id")
+ " FROM " +
dc6b13b4 Christian Ehringfeld
this->schema.data()->quoteTableName(
entity.data()->getTablename()) + this->where(values,
"AND", true) + " LIMIT 1");
this->bindValues(values, q);
return q;
}

e0e1ead8 Christian Ehringfeld
QSqlQuery QueryBuilder::count(const QSharedPointer<Entity> &entity,
bool ignoreID) const {
QHash<QString, QVariant> values = this->getEntityAttributes(
entity.data()->getMetaProperties(),
entity);
QSqlQuery q = this->database.data()->getQuery("SELECT COUNT(*) FROM " +
this->schema.data()->quoteTableName(
dc6b13b4 Christian Ehringfeld
entity.data()->getTablename()) + this->where(
values, "AND", ignoreID));
this->bindValues(values, q, ignoreID);
return q;
}

QSqlQuery QueryBuilder::count(const QString &tableName) const {
e0e1ead8 Christian Ehringfeld
QSqlQuery q = this->database.data()->getQuery("SELECT COUNT(*) FROM " +
this->schema.data()->quoteTableName(
dc6b13b4 Christian Ehringfeld
tableName) + ";");
return q;
}

QSqlQuery QueryBuilder::merge(const QSharedPointer<Entity> &entity) const {
QHash<QString, QVariant> values = this->saveAttributes(entity);
e0e1ead8 Christian Ehringfeld
QSqlQuery q = this->database.data()->getQuery("UPDATE " +
this->schema.data()->quoteTableName(
dc6b13b4 Christian Ehringfeld
entity->getTablename()) + " SET " + this->attributes(
values) + " WHERE " + this->schema.data()->quoteColumnName("id") + "=:id;");
this->bindValues(values, q);
return q;
}

QSqlQuery QueryBuilder::create(const QSharedPointer<Entity> &entity) const {
QHash<QString, QVariant> values = this->saveAttributes(entity);
values.remove("id");
QSqlQuery q = this->database.data()->getQuery();
e0e1ead8 Christian Ehringfeld
QString p1 = "INSERT INTO " + this->schema.data()->quoteTableName(
entity->getTablename()) + "(";
dc6b13b4 Christian Ehringfeld
QString p2 = "VALUES(";
if (!values.isEmpty()) {
q.prepare(this->buildCreateQuery(values.constBegin(), values.constEnd(),
p1, p2));
}
this->bindValues(values, q);
return q;
}

e0e1ead8 Christian Ehringfeld
QSqlQuery QueryBuilder::oneToMany(const QString &tableName,
const QString &attribute,
const qint64 &id,
c599658a Christian Ehringfeld
const qint64 &limit) {
706de2e8 Christian Ehringfeld
QHash<QString, QVariant> values = QHash<QString, QVariant>();
values.insert(attribute, id);
c599658a Christian Ehringfeld
return this->findByAttributes(values, tableName, false, limit);
706de2e8 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QSqlQuery QueryBuilder::manyToMany(const QString &tableName,
const QString &attribute,
const qint64 &id,
706de2e8 Christian Ehringfeld
const QString &foreignKey, const QString &foreignTable) {
QSqlQuery q = this->database.data()->getQuery();
98b5b08d Christian Ehringfeld
QString sql = "SELECT " +
this->schema.data()->quoteTableName(
e0e1ead8 Christian Ehringfeld
foreignTable) + ".* FROM " +
this->schema.data()->quoteTableName(tableName) + " " + this->leftJoin(
foreignTable, tableName,
foreignKey) + " WHERE " + this->schema.data()->quoteColumnName(
attribute) + "=:id;";
706de2e8 Christian Ehringfeld
q.prepare(sql);
q.bindValue(":id", id);
return q;
}

98b5b08d Christian Ehringfeld
QSqlQuery QueryBuilder::manyToManyDelete(const QString &tableName,
const QString &attribute, const qint64 &id) {
QSqlQuery q = this->database.data()->getQuery();
QString sql = "DELETE FROM " + this->schema.data()->quoteTableName(
tableName) + " WHERE " + this->schema.data()->quoteColumnName(
attribute) + "=:id";
q.prepare(sql);
q.bindValue(":id", id);
return q;
}

QSqlQuery QueryBuilder::manyToManyInsert(const QString &tableName,
const QString &col1, const QString &col2) const {
QSqlQuery q = this->database.data()->getQuery();
QString sql = "INSERT INTO " + this->schema.data()->quoteTableName(
tableName) + "(" + col1 + "," + col2 + ")"
+ "VALUES(?, ?);";
q.prepare(sql);
return q;
}
706de2e8 Christian Ehringfeld
e0e1ead8 Christian Ehringfeld
QString QueryBuilder::leftJoin(const QString &foreignTable,
const QString &tableName,
const QString &foreignKey) {
return "LEFT JOIN " + this->schema.data()->quoteTableName(
foreignTable) + " ON " +
this->schema.data()->quoteColumnName(foreignTable + ".id") + "=" +
this->schema.data()->quoteColumnName(
706de2e8 Christian Ehringfeld
tableName + "." + foreignKey);
}

1a3b37ba Christian Ehringfeld
QString QueryBuilder::superClassColumnName(const QMetaObject *&superMeta)
const {
return QString(superMeta->className()).toLower();
}

c599658a Christian Ehringfeld
QString QueryBuilder::limit(const qint64 &limit, const qint64 &offset) const {
QString s = "";
if (limit > 0) {
s.append(" LIMIT ").append(QString::number(limit));
}
if (offset > 0) {
s.append(" OFFSET ").append(QString::number(offset));
}
return s;
1e213c09 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::generateManyToManyColumnName(const QSharedPointer<Entity>
&entity) const {
47f9301a Christian Ehringfeld
return this->generateColumnNameID(QString(
entity.data()->metaObject()->className()));
706de2e8 Christian Ehringfeld
}

11bbe9a6 Christian Ehringfeld
QSqlQuery QueryBuilder::getQuery() const {
return this->database.data()->getQuery();
}

e0e1ead8 Christian Ehringfeld
QHash<QString, QVariant> QueryBuilder::saveAttributes(const
QSharedPointer<Entity> &entity) const {
f4e3904b Christian Ehringfeld
auto props = entity.data()->getMetaProperties();
dc6b13b4 Christian Ehringfeld
auto values = this->getEntityAttributes(props, entity);
auto relValues = this->getManyToOneAttributes(props, entity);
auto iterator = relValues.constBegin();
while (iterator != relValues.constEnd()) {
values.insert(iterator.key(), iterator.value());
++iterator;
}
return values;
}

e0e1ead8 Christian Ehringfeld
QHash<QString, QVariant> QueryBuilder::getEntityAttributes(
const QHash<QString, QMetaProperty>
&props,
const QSharedPointer<Entity> &entity) const {
b0bf458e Christian Ehringfeld
Entity *e = entity.data();
auto map = QHash<QString, QVariant>();
auto transientAttrs = e->getTransientAttributes();
ba800d6d Christian Ehringfeld
auto relations = e->getRelations();
auto i = props.constBegin();
while (i != props.constEnd()) {
if (!transientAttrs.contains(i.key()) && !relations.contains(i.key())) {
map.insert(i.key(), i.value().read(e));
}
++i;
}
return map;
}

e0e1ead8 Christian Ehringfeld
QHash<QString, QVariant> QueryBuilder::getManyToOneAttributes(
const QHash<QString, QMetaProperty>
&props,
const QSharedPointer<Entity> &entity) const {
ba800d6d Christian Ehringfeld
Entity *e = entity.data();
auto map = QHash<QString, QVariant>();
auto relations = e->getRelations();
auto i = relations.constBegin();
while (i != relations.constEnd()) {
Relation r = i.value();
1a3b37ba Christian Ehringfeld
if ((r.getType() == MANY_TO_ONE && props.contains(i.key()))
4f3b13f3 Christian Ehringfeld
|| (r.getType() == ONE_TO_ONE && r.getMappedBy().isEmpty())) {
ba800d6d Christian Ehringfeld
auto v = props.value(i.key()).read(e);
b0bf458e Christian Ehringfeld
if (v.canConvert<Entity *>()) {
ba800d6d Christian Ehringfeld
this->insertRelationId(qvariant_cast<Entity *>(v), map, i.key());
b0bf458e Christian Ehringfeld
} else if (v.canConvert<QSharedPointer<Entity>>()) {
e0e1ead8 Christian Ehringfeld
this->insertRelationId(qvariant_cast<QSharedPointer<Entity>>(v).data(), map,
i.key());
a47954c0 Christian Ehringfeld
} else if (v.canConvert<QPointer<Entity>>()) {
ba800d6d Christian Ehringfeld
this->insertRelationId(qvariant_cast<QPointer<Entity>>(v).data(), map, i.key());
b0bf458e Christian Ehringfeld
}
ba800d6d Christian Ehringfeld
++i;
b0bf458e Christian Ehringfeld
}
}
return map;
}

e0e1ead8 Christian Ehringfeld
void QueryBuilder::insertRelationId(const Entity *e,
QHash<QString, QVariant> &map,
QString relName) const {
b0bf458e Christian Ehringfeld
if (e && e->getId() > -1) {
f62afb13 Christian Ehringfeld
map.insert(this->generateColumnNameID(relName), e->getId());
b0bf458e Christian Ehringfeld
}
}

d99101ae Christian Ehringfeld
QString QueryBuilder::buildColumns(const QStringList &columns) const {
QString r = "";
bool first = true;
for (int var = 0; var < columns.size(); ++var) {
if (!first) {
r.append(",");
} else {
first = false;
}
r.append(columns.at(var));
}
return r;
}

e0e1ead8 Christian Ehringfeld
void QueryBuilder::bindValues(const QHash<QString, QVariant> &h, QSqlQuery &q,
bool ignoreID) const {
dc6b13b4 Christian Ehringfeld
QHash<QString, QVariant>::const_iterator i = h.constBegin();
while (i != h.constEnd()) {
if (!ignoreID || (ignoreID && !(i.key() == "id"))) {
q.bindValue(":" + i.key(), i.value());
}
++i;
}
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::where(const QSharedPointer<Entity> &entity,
QString conjunction,
bool ignoreID) const {
return this->where(this->getEntityAttributes(entity.data()->getMetaProperties(),
entity),
conjunction, ignoreID);
dc6b13b4 Christian Ehringfeld
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::where(const QHash<QString, QVariant> &m,
const QString &conjunction,
bool ignoreID) const {
dc6b13b4 Christian Ehringfeld
if (m.size() == 0 || (ignoreID && m.contains("id") && m.size() == 1)) {
return "";
}
return " WHERE " + this->attributes(m, conjunction, ignoreID);
}

e0e1ead8 Christian Ehringfeld
QString QueryBuilder::attributes(const QHash<QString, QVariant> &m,
const QString &conjunction,
bool ignoreID) const {
dc6b13b4 Christian Ehringfeld
QString rc = "";
if (!m.isEmpty()) {
QHash<QString, QVariant>::const_iterator i = m.constBegin();
while (i != m.constEnd()) {
if (!ignoreID || (ignoreID && !(i.key() == "id"))) {
if (!(rc == "")) {
rc += " " + conjunction + " ";
}
03fe3afa Christian Ehringfeld
rc += this->schema.data()->quoteColumnName(i.key()) + "=:" + i.key();
dc6b13b4 Christian Ehringfeld
}
++i;
}
}
return rc;
}
ba800d6d Christian Ehringfeld
b0bf458e Christian Ehringfeld
QSharedPointer<Schema> QueryBuilder::getSchema() const {
return schema;
}
14f9beed Christian Ehringfeld
b0bf458e Christian Ehringfeld
void QueryBuilder::setSchema(const QSharedPointer<Schema> &value) {
schema = value;
14f9beed Christian Ehringfeld
}