TreeFrogFramework: src/tsqlormapper.h Source File

1#pragma once

3#include <QList>

4#include <QMap>

5#include <QtSql>

6#include <TCriteria>

7#include <TCriteriaConverter>

8#include <TGlobal>

9#include <TSqlJoin>

10#include <TSqlObject>

11#include <TSqlQuery>

21public:

32 virtual int findBy(int column, const QVariant &value) = 0;

33 virtual int findIn(int column, const QVariantList &values) = 0;

36 virtual int findCountBy(int column, const QVariant &value) = 0;

37 virtual int updateAll(const TCriteria &cri, int column, const QVariant &value) = 0;

38 virtual int updateAll(const TCriteria &cri, const QMap<int, QVariant> &values) = 0;

40};

52template <class T>

54public:

63 template <class C>

70 template <class C>

78 int findBy(int column, const QVariant &value);

79 int findIn(int column, const QVariantList &values);

82 T last() const;

83 T value(int i) const;

88 int updateAll(const TCriteria &cri, const QMap<int, QVariant> &values);

99 public:

102

107 {

108 m = o.m;

110 return *this;

111 }

112 inline const T operator*() const { return m->value(it); }

116 {

117 it = std::min(it + 1, m->rowCount());

118 return *this;

119 }

121 {

122 int i = it;

123 it = std::min(it + 1, m->rowCount());

125 }

127 {

128 --it;

129 return *this;

130 }

132 {

133 int i = it++;

135 }

136

137 private:

139 m(mapper), it(i) {}

141 };

142

143protected:

144 void setFilter(const QString &filter);

147 virtual void clear();

149 virtual int rowCount(const QModelIndex &parent) const;

150

151private:

152 QString queryFilter;

153 QList<QPair<QString, Tf::SortOrder>> sortColumns;

154 int queryLimit {0};

155 int queryOffset {0};

156 int joinCount {0};

157 QStringList joinClauses;

158 QStringList joinWhereClauses;

159

162};

163

167template <class T>

170{

171 setTable(T().tableName());

172}

173

177template <class T>

181

186template <class T>

188{

190 TCriteriaConverter<T> conv(cri, database(), QStringLiteral("t0"));

192 } else {

194 }

195

196 QElapsedTimer time;

197 time.start();

198 int oldLimit = queryLimit;

199 queryLimit = 1;

200 bool ret = select();

202 queryLimit = oldLimit;

203

204

206}

207

212template <class T>

217

222template <class T>

224{

225 int idx = T().primaryKeyIndex();

226 if (idx < 0) {

227 tSystemDebug("Primary key not found, table name: {}", qUtf8Printable(T().tableName()));

228 return T();

229 }

230

232}

233

240template <class T>

242{

244 TCriteriaConverter<T> conv(cri, database(), QStringLiteral("t0"));

246 } else {

248 }

249

250 QElapsedTimer time;

251 time.start();

252 bool ret = select();

253 while (canFetchMore()) {

254 fetchMore();

255 }

257

258 return ret ? rowCount() : -1;

259}

260

267template <class T>

272

279template <class T>

284

288template <class T>

290{

291 return QSqlTableModel::rowCount();

292}

293

297template <class T>

299{

300 return QSqlTableModel::rowCount(parent);

301}

302

307template <class T>

312

317template <class T>

322

328template <class T>

330{

331 T rec;

332 if (i >= 0 && i < rowCount()) {

333 rec.setRecord(record(i), QSqlError());

334 } else {

336 }

337 return rec;

338}

339

344template <class T>

346{

347 queryLimit = limit;

348}

349

354template <class T>

356{

357 queryOffset = offset;

358}

359

363template <class T>

365{

366 if (column >= 0) {

367 QString columnName = TCriteriaConverter<T>::getPropertyName(column, QSqlTableModel::database().driver());

368 if (!columnName.isEmpty()) {

369 sortColumns << qMakePair(columnName, order);

370 }

371 }

372}

373

377template <class T>

379{

380 if (!column.isEmpty()) {

381 T obj;

382 if (obj.propertyNames().contains(column, Qt::CaseInsensitive)) {

383 sortColumns << qMakePair(column, order);

384 } else {

385 Tf::warn("Unable to set sort order : '{}' column not found in '{}' table",

386 qUtf8Printable(column), qUtf8Printable(obj.tableName()));

387 }

388 }

389}

390

395template <class T>

397{

399 return *this;

400}

401

406template <class T>

408{

410 return *this;

411}

412

416template <class T>

418{

420 return *this;

421}

422

426template <class T>

428{

430 return *this;

431}

432

439template <class T>

441{

442 queryFilter = filter;

443}

444

449template <class T>

451{

452 QString query;

453 bool joinFlag = !joinClauses.isEmpty();

454 bool valid = false;

455 auto rec = record();

456

457 query.reserve(1024);

458 query += QLatin1String("SELECT ");

459

460 for (int i = 0; i < rec.count(); ++i) {

461 if (rec.isGenerated(i)) {

462 if (joinFlag) {

463 query += QLatin1String("t0.");

464 }

466 query += QLatin1Char(',');

467 valid = true;

468 }

469 }

470

471 if (Q_UNLIKELY(!valid)) {

472 return QString();

473 }

474

475 query.chop(1);

476 query += QLatin1String(" FROM ");

478 query += QLatin1String(" t0");

479

480 if (joinFlag) {

481 for (auto &join : (const QStringList &)joinClauses) {

482 query += join;

483 }

484 }

485

486 QString filter = queryFilter;

487 if (!joinWhereClauses.isEmpty()) {

488 for (auto &wh : (const QStringList &)joinWhereClauses) {

489 if (!filter.isEmpty()) {

490 filter += QLatin1String(" AND ");

491 }

492 filter += wh;

493 }

494 }

495

496 if (Q_LIKELY(!filter.isEmpty())) {

497 query.append(QLatin1String(" WHERE ")).append(filter);

498 }

499

500 QString orderby = orderBy();

501 if (!orderby.isEmpty()) {

502 query.append(orderby);

503 }

504

505 if (queryLimit > 0) {

506 query.append(QLatin1String(" LIMIT ")).append(QString::number(queryLimit));

507 }

508

509 if (queryOffset > 0) {

510 query.append(QLatin1String(" OFFSET ")).append(QString::number(queryOffset));

511 }

512

513 return query;

514}

515

520template <class T>

522{

524 TCriteriaConverter<T> conv(cri, database(), "t0");

526 } else {

528 }

529

530 QString query;

531 query.reserve(1024);

532 query += QLatin1String("SELECT COUNT(*) FROM (");

534 query += QLatin1String(") t");

535

536 int cnt = -1;

538 bool res = q.exec(query);

539 if (res) {

541 cnt = q.value(0).toInt();

542 }

543 return cnt;

544}

545

550template <class T>

555

561template <class T>

563{

564 static const QByteArray UpdatedAt("updated_at");

565 static const QByteArray ModifiedAt("modified_at");

566

567 QString upd;

568 upd.reserve(256);

569 upd.append(QLatin1String("UPDATE ")).append(tableName()).append(QLatin1String(" SET "));

570

571 QSqlDatabase db = database();

572 TCriteriaConverter<T> conv(cri, db);

573 QString where = conv.toString();

574

575 if (values.isEmpty()) {

577 return -1;

578 }

579

580 const QMetaType datetimeMetaType(QMetaType::QDateTime);

581 T obj;

582

583 for (int i = obj.metaObject()->propertyOffset(); i < obj.metaObject()->propertyCount(); ++i) {

584 const char *propName = obj.metaObject()->property(i).name();

585 QByteArray prop = QByteArray(propName).toLower();

588 upd += QLatin1Char('=');

590 upd += QLatin1Char(',');

591 break;

592 }

593 }

594

595 auto it = values.begin();

596 while (true) {

597 upd += conv.propertyName(it.key(), db.driver());

598 upd += QLatin1Char('=');

600

601 if (++it == values.end()) {

602 break;

603 }

604 upd += QLatin1Char(',');

605 }

606

607 if (!where.isEmpty()) {

608 upd.append(QLatin1String(" WHERE ")).append(where);

609 }

610

612 bool res = sqlQuery.exec(upd);

614}

615

620template <class T>

622{

623 QMap<int, QVariant> map;

624 map.insert(column, value);

626}

627

632template <class T>

634{

635 QSqlDatabase db = database();

636 QString del = db.driver()->sqlStatement(QSqlDriver::DeleteStatement,

637 T().tableName(), QSqlRecord(), false);

638 TCriteriaConverter<T> conv(cri, db);

639 QString where = conv.toString();

640

641 if (del.isEmpty()) {

643 return -1;

644 }

645 if (!where.isEmpty()) {

646 del.append(QLatin1String(" WHERE ")).append(where);

647 }

648

650 bool res = sqlQuery.exec(del);

652}

653

657template <class T>

658template <class C>

660{

661 if (column < 0 || join.joinColumn() < 0) {

662 return;

663 }

664

665 QString clause;

666

667 switch (join.joinMode()) {

669 clause = QLatin1String(" INNER JOIN ");

670 break;

671

673 clause = QLatin1String(" LEFT OUTER JOIN ");

674 break;

675

677 clause = QLatin1String(" RIGHT OUTER JOIN ");

678 break;

679

680 default:

681 break;

682 }

683

684 int joinCount = joinClauses.count();

685 QString alias = QLatin1Char('t') + QString::number(joinCount + 1);

686 QSqlDatabase db = database();

687

688 clause += C().tableName();

689 clause += QLatin1Char(' ');

690 clause += alias;

691 clause += QLatin1String(" ON ");

692 clause += TCriteriaConverter<T>::getPropertyName(column, db.driver(), "t0");

693 clause += QLatin1Char('=');

694 clause += TCriteriaConverter<C>::getPropertyName(join.joinColumn(), db.driver(), alias);

695 joinClauses << clause;

696

697 if (!join.criteria().isEmpty()) {

698 TCriteriaConverter<C> conv(join.criteria(), db, alias);

699 joinWhereClauses << conv.toString();

700 }

701}

702

703template <class T>

704template <class C>

706{

708 return *this;

709}

710

714template <class T>

716{

717 setTable(tableName());

718}

719

723template <class T>

725{

726 QSqlTableModel::clear();

727 queryFilter.clear();

728 sortColumns.clear();

729 queryLimit = 0;

730 queryOffset = 0;

731 joinCount = 0;

732 joinClauses.clear();

733 joinWhereClauses.clear();

734

735

736

737}

738

742template <class T>

744{

745 QString str;

746

747 if (!sortColumns.isEmpty()) {

748 str.reserve(64);

749 str += QLatin1String(" ORDER BY ");

750 for (auto &p : sortColumns) {

751 str += QLatin1String("t0.");

753 str += (p.second == Tf::AscendingOrder) ? QLatin1String(" ASC,") : QLatin1String(" DESC,");

754 }

755 str.chop(1);

756 }

757 return str;

758}

759

The TAbstractSqlORMapper class is the abstract base class of functionality to object-relational mappi...

Definition tsqlormapper.h:20

virtual int findCount(const TCriteria &cri=TCriteria())=0

virtual void setOffset(int offset)=0

virtual int findCountBy(int column, const QVariant &value)=0

virtual int updateAll(const TCriteria &cri, const QMap< int, QVariant > &values)=0

virtual int updateAll(const TCriteria &cri, int column, const QVariant &value)=0

virtual int find(const TCriteria &cri=TCriteria())=0

virtual void setLimit(int limit)=0

virtual ~TAbstractSqlORMapper()

Definition tsqlormapper.h:23

TAbstractSqlORMapper(const QSqlDatabase &db)

Definition tsqlormapper.h:22

virtual void setSortOrder(const QString &column, Tf::SortOrder order=Tf::AscendingOrder)=0

virtual int removeAll(const TCriteria &cri=TCriteria())=0

virtual void setSortOrder(int column, Tf::SortOrder order=Tf::AscendingOrder)=0

virtual int findIn(int column, const QVariantList &values)=0

virtual int findBy(int column, const QVariant &value)=0

The TCriteria class represents a WHERE clause without SQL for the sake of database abstraction.

Definition tcriteria.h:6

bool isEmpty() const

Returns true if the criteria has no data; otherwise returns false.

Definition tcriteria.cpp:345

The TSqlJoin class represents JOIN clause for combination to a record of other table.

Definition tsqljoin.h:14

Const iterator.

Definition tsqlormapper.h:98

bool operator!=(const ConstIterator &o) const

Definition tsqlormapper.h:114

ConstIterator operator--(int)

Definition tsqlormapper.h:131

bool operator==(const ConstIterator &o) const

Definition tsqlormapper.h:113

ConstIterator & operator--()

Definition tsqlormapper.h:126

ConstIterator operator++(int)

Definition tsqlormapper.h:120

const T operator*() const

Definition tsqlormapper.h:112

ConstIterator & operator=(const ConstIterator &o)

Definition tsqlormapper.h:106

ConstIterator(const ConstIterator &o)

Definition tsqlormapper.h:104

ConstIterator & operator++()

Definition tsqlormapper.h:115

const TSqlORMapper< T > * m

Definition tsqlormapper.h:100

int it

Definition tsqlormapper.h:101

ConstIterator()

Definition tsqlormapper.h:103

The TSqlORMapper class is a template class that provides concise functionality to object-relational m...

Definition tsqlormapper.h:53

int findIn(int column, const QVariantList &values)

Retrieves with the criteria that the column is within the list of values values returns the number of...

Definition tsqlormapper.h:280

virtual void clear()

Clears and releases any acquired resource.

Definition tsqlormapper.h:724

int find(const TCriteria &cri=TCriteria())

Retrieves with the criteria cri from the table and returns the number of the ORM objects.

Definition tsqlormapper.h:241

TSqlORMapper()

Constructor.

Definition tsqlormapper.h:168

virtual QString selectStatement() const

Returns a SELECT statement generated from the specified parameters.

Definition tsqlormapper.h:450

T value(int i) const

Returns the ORM object in the results retrieved by find() function.

Definition tsqlormapper.h:329

T findFirst(const TCriteria &cri=TCriteria())

Returns the first ORM object retrieved with the criteria cri from the table.

Definition tsqlormapper.h:187

void setJoin(int column, const TSqlJoin< C > &join)

Sets a JOIN clause for column to join.

Definition tsqlormapper.h:659

void setSortOrder(int column, Tf::SortOrder order=Tf::AscendingOrder)

Sets the sort order for column to order.

Definition tsqlormapper.h:364

T last() const

Returns the last ORM object in the results retrieved by find() function.

Definition tsqlormapper.h:318

int findBy(int column, const QVariant &value)

Retrieves with the criteria for the column as the value in the table and returns the number of the OR...

Definition tsqlormapper.h:268

int findCount(const TCriteria &cri=TCriteria())

Returns the number of records retrieved with the criteria cri from the table.

Definition tsqlormapper.h:521

T findFirstBy(int column, const QVariant &value)

Returns the first ORM object retrieved with the criteria for the column as the value in the table.

Definition tsqlormapper.h:213

int rowCount() const

Returns the number of rows of the current query.

Definition tsqlormapper.h:289

void setOffset(int offset)

Sets the offset to offset, which is the number of rows to skip for execution of SELECT statement.

Definition tsqlormapper.h:355

TSqlORMapper< T > & join(int column, const TSqlJoin< C > &join)

Definition tsqlormapper.h:705

int findCountBy(int column, const QVariant &value)

Returns the number of records retrieved with the criteria for the column as the value from the table.

Definition tsqlormapper.h:551

void setLimit(int limit)

Sets the limit to limit, which is the limited number of rows for execution of SELECT statement.

Definition tsqlormapper.h:345

virtual ~TSqlORMapper()

Destructor.

Definition tsqlormapper.h:178

int updateAll(const TCriteria &cri, int column, const QVariant &value)

Updates the value of the specified column in all rows that satisfy the criteria cri and returns the n...

Definition tsqlormapper.h:621

ConstIterator end() const

Definition tsqlormapper.h:93

void reset()

Reset the internal state of the mapper object.

Definition tsqlormapper.h:715

int removeAll(const TCriteria &cri=TCriteria())

Removes all rows based on the criteria cri from the table and returns the number of the rows affected...

Definition tsqlormapper.h:633

T first() const

Returns the first ORM object in the results retrieved by find() function.

Definition tsqlormapper.h:308

void setFilter(const QString &filter)

Sets the current filter to filter.

Definition tsqlormapper.h:440

virtual QString orderByClause() const

Definition tsqlormapper.h:146

QString orderBy() const

Returns a SQL WHERE clause generated from a criteria.

Definition tsqlormapper.h:743

T findByPrimaryKey(const QVariant &pk)

Returns the ORM object retrieved with the primary key pk from the table.

Definition tsqlormapper.h:223

ConstIterator begin() const

Definition tsqlormapper.h:92

TSqlORMapper< T > & offset(int offset)

Sets the offset to offset, which is the number of rows to skip for execution of SELECT statement.

Definition tsqlormapper.h:407

TSqlORMapper< T > & limit(int limit)

Sets the limit to limit, which is the limited number of rows for execution of SELECT statement.

Definition tsqlormapper.h:396

The TSqlQuery class provides a means of executing and manipulating SQL statements.

Definition tsqlquery.h:6

QVariant value(int index) const

Returns the value of field index in the current record.

Definition tsqlquery.h:68

bool exec(const QString &query)

Executes the SQL in query.

Definition tsqlquery.cpp:200

int numRowsAffected() const

Returns the number of rows affected by the result's SQL statement, or -1 if it cannot be determined.

Definition tsqlquery.h:50

static QString formatValue(const QVariant &val, const QMetaType &type, int databaseId=0)

Returns a string representation of the value val for the database databaseId.

Definition tsqlquery.cpp:125

static QString escapeIdentifier(const QString &identifier, QSqlDriver::IdentifierType type=QSqlDriver::FieldName, int databaseId=0)

Returns the identifier escaped according to the rules of the database databaseId.

Definition tsqlquery.cpp:102

bool next()

Retrieves the next record in the result, if available, and positions the query on the retrieved recor...

Definition tsqlquery.h:62

@ In

Definition tfnamespace.h:289

@ InnerJoin

Definition tfnamespace.h:298

@ RightJoin

Definition tfnamespace.h:300

@ LeftJoin

Definition tfnamespace.h:299

The Tf namespace contains miscellaneous identifiers used throughout the library of TreeFrog Framework...

Definition tdebug.h:10

SortOrder

Definition tfnamespace.h:133

@ AscendingOrder

Definition tfnamespace.h:134

bool strcmp(const QByteArray &str1, const QByteArray &str2)

Definition tglobal.h:223

T_CORE_EXPORT void writeQueryLog(const QString &query, bool success, const QSqlError &error, int duration)

Definition tsystemglobal.cpp:144

void warn(const std::format_string< Args... > &fmt, Args &&... args)

Outputs the warning message to the log file, formatting args according to the format string fmt.

Definition tglobal.h:259

QSqlError lastError()

Returns the VariantMap object of the error status of the last operation.

Definition tcachesqlitestore.cpp:26

#define T_DISABLE_COPY(Class)

Definition tdeclexport.h:37

#define T_DISABLE_MOVE(Class)

Definition tdeclexport.h:41

const QByteArray ModifiedAt("modifiedAt")

const QByteArray UpdatedAt("updatedAt")

void tSystemDebug(const std::format_string< Args... > &fmt, Args &&... args)

Definition tsystemglobal.h:108

void tSystemError(const std::format_string< Args... > &fmt, Args &&... args)

Definition tsystemglobal.h:59