Results 1 to 7 of 7

Thread: Managing data in a TableView

  1. #1
    Join Date
    Jun 2010
    Location
    Cincinnati, Ohio, USA
    Posts
    90
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Windows

    Question Managing data in a TableView

    First off, I am new somewhat new to using Qt. About two years ago I used it for a specific project, but I never had to dive too deep into the Model/View and never touched the SQL plug-ins. In my current project, I have a product table in which the user needs to be able to add, delete, edit, and sort:

    CREATE TABLE Product (
    ProductId INTEGER PRIMARY KEY AUTOINCREMENT,
    PriceListId INTEGER NOT NULL,
    ProductType INTEGER NOT NULL DEFAULT 1,
    SortOrder INTEGER NOT NULL,
    Description CHAR(80) NOT NULL,
    Price NUMERIC(10,4) NOT NULL
    )

    The only two things displayed are the description and price. I have created a custom QSqlTableModel to display the price correctly. I have implemented my own delegate for the TableView to present the correct editor with the correct limitations on both fields. To add something, I have extra widgets to enter the description and price, then I use this SQL to insert it into the database:

    Qt Code:
    1. INSERT INTO Product (PriceListId, SortOrder, Description, Price)
    2. VALUES(:priceListId1, (SELECT MAX(P.SortOrder) + 1 FROM Product P WHERE P.PriceListId = :priceListId2), :description, :price)
    To copy to clipboard, switch view to plain text mode 

    I am trying to figure out how to delete and to sort, but it is turning out doing it manually seems like a lot of work and I hafe a feeling there is a better way to leverage Qt to make life much easier.

    1. How does one go about adding a none trivial item to a ModelView?
    2. Like in the current .Net datagrids, is it possible to get a blank line at the bottom of the TableView for a user to add a new row?
    3. What is the standard way for a user to delete something from a TableView?
    4. Any and all suggestions on how I am implementing sorting is welcome, but at the DB level and how to implement it at the UI level is welcome.


    See the next post for the ui file I generated for this

    Sam

  2. #2
    Join Date
    Jun 2010
    Location
    Cincinnati, Ohio, USA
    Posts
    90
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Managing data in a TableView

    Here is my ui file, it was too big to fit in the last post:

    Qt Code:
    1. <?xml version="1.0" encoding="UTF-8"?>
    2. <ui version="4.0">
    3. <class>ProductMgrFormClass</class>
    4. <widget class="QWidget" name="ProductMgrFormClass">
    5. <property name="geometry">
    6. <rect>
    7. <x>0</x>
    8. <y>0</y>
    9. <width>450</width>
    10. <height>400</height>
    11. </rect>
    12. </property>
    13. <property name="minimumSize">
    14. <size>
    15. <width>450</width>
    16. <height>400</height>
    17. </size>
    18. </property>
    19. <property name="windowTitle">
    20. <string>Product Manager</string>
    21. </property>
    22. <layout class="QVBoxLayout" name="verticalLayout_2">
    23. <item>
    24. <widget class="QLabel" name="priceListLabel">
    25. <property name="text">
    26. <string>Price List:</string>
    27. </property>
    28. </widget>
    29. </item>
    30. <item>
    31. <layout class="QHBoxLayout" name="horizontalLayout">
    32. <item>
    33. <widget class="QComboBox" name="productListComboBox">
    34. <property name="sizePolicy">
    35. <sizepolicy hsizetype="Expanding" vsizetype="Fixed">
    36. <horstretch>0</horstretch>
    37. <verstretch>0</verstretch>
    38. </sizepolicy>
    39. </property>
    40. <property name="minimumSize">
    41. <size>
    42. <width>175</width>
    43. <height>0</height>
    44. </size>
    45. </property>
    46. </widget>
    47. </item>
    48. <item>
    49. <widget class="QPushButton" name="pushButton">
    50. <property name="sizePolicy">
    51. <sizepolicy hsizetype="Minimum" vsizetype="Fixed">
    52. <horstretch>0</horstretch>
    53. <verstretch>0</verstretch>
    54. </sizepolicy>
    55. </property>
    56. <property name="text">
    57. <string>Manage</string>
    58. </property>
    59. </widget>
    60. </item>
    61. </layout>
    62. </item>
    63. <item>
    64. <widget class="QLabel" name="priceListProductsLabel">
    65. <property name="text">
    66. <string>Price List Products:</string>
    67. </property>
    68. </widget>
    69. </item>
    70. <item>
    71. <layout class="QHBoxLayout" name="horizontalLayout_4">
    72. <item>
    73. <widget class="QTableView" name="productTableView">
    74. <property name="editTriggers">
    75. <set>QAbstractItemView::AnyKeyPressed|QAbstractItemView::DoubleClicked|QAbstractItemView::EditKeyPressed|QAbstractItemView::SelectedClicked</set>
    76. </property>
    77. <property name="cornerButtonEnabled">
    78. <bool>false</bool>
    79. </property>
    80. <attribute name="horizontalHeaderVisible">
    81. <bool>true</bool>
    82. </attribute>
    83. <attribute name="horizontalHeaderVisible">
    84. <bool>true</bool>
    85. </attribute>
    86. </widget>
    87. </item>
    88. <item>
    89. <layout class="QVBoxLayout" name="verticalLayout">
    90. <item>
    91. <widget class="QPushButton" name="upButton">
    92. <property name="enabled">
    93. <bool>false</bool>
    94. </property>
    95. <property name="toolTip">
    96. <string>Move selected product up.</string>
    97. </property>
    98. <property name="text">
    99. <string/>
    100. </property>
    101. <property name="icon">
    102. <iconset resource="salescenter.qrc">
    103. <normaloff>:/SalesCenter/Images/sub_blue_plus-16x16.png</normaloff>:/SalesCenter/Images/sub_blue_plus-16x16.png</iconset>
    104. </property>
    105. <property name="iconSize">
    106. <size>
    107. <width>16</width>
    108. <height>16</height>
    109. </size>
    110. </property>
    111. </widget>
    112. </item>
    113. <item>
    114. <widget class="QPushButton" name="downButton">
    115. <property name="enabled">
    116. <bool>false</bool>
    117. </property>
    118. <property name="toolTip">
    119. <string>Move selected product down.</string>
    120. </property>
    121. <property name="text">
    122. <string/>
    123. </property>
    124. <property name="icon">
    125. <iconset resource="salescenter.qrc">
    126. <normaloff>:/SalesCenter/Images/sub_blue_minus-16x16.png</normaloff>:/SalesCenter/Images/sub_blue_minus-16x16.png</iconset>
    127. </property>
    128. </widget>
    129. </item>
    130. <item>
    131. <widget class="QPushButton" name="deleteButton">
    132. <property name="enabled">
    133. <bool>false</bool>
    134. </property>
    135. <property name="toolTip">
    136. <string>Delete the selected product.</string>
    137. </property>
    138. <property name="text">
    139. <string/>
    140. </property>
    141. <property name="icon">
    142. <iconset resource="salescenter.qrc">
    143. <normaloff>:/SalesCenter/Images/sub_blue_delete-16x16.png</normaloff>:/SalesCenter/Images/sub_blue_delete-16x16.png</iconset>
    144. </property>
    145. </widget>
    146. </item>
    147. <item>
    148. <spacer name="verticalSpacer">
    149. <property name="orientation">
    150. <enum>Qt::Vertical</enum>
    151. </property>
    152. <property name="sizeHint" stdset="0">
    153. <size>
    154. <width>20</width>
    155. <height>40</height>
    156. </size>
    157. </property>
    158. </spacer>
    159. </item>
    160. </layout>
    161. </item>
    162. </layout>
    163. </item>
    164. <item>
    165. <widget class="QGroupBox" name="groupBox">
    166. <property name="title">
    167. <string> Add Product </string>
    168. </property>
    169. <layout class="QFormLayout" name="formLayout">
    170. <item row="0" column="0">
    171. <widget class="QLabel" name="newProductLabel">
    172. <property name="text">
    173. <string>Product Description</string>
    174. </property>
    175. </widget>
    176. </item>
    177. <item row="0" column="1">
    178. <widget class="QLineEdit" name="newProductLineEdit">
    179. <property name="minimumSize">
    180. <size>
    181. <width>175</width>
    182. <height>0</height>
    183. </size>
    184. </property>
    185. <property name="maxLength">
    186. <number>80</number>
    187. </property>
    188. </widget>
    189. </item>
    190. <item row="1" column="0">
    191. <widget class="QLabel" name="newPriceLabel">
    192. <property name="text">
    193. <string>Price:</string>
    194. </property>
    195. </widget>
    196. </item>
    197. <item row="1" column="1">
    198. <layout class="QHBoxLayout" name="horizontalLayout_2">
    199. <item>
    200. <widget class="QDoubleSpinBox" name="newPriceDoubleSpinBox">
    201. <property name="minimumSize">
    202. <size>
    203. <width>50</width>
    204. <height>0</height>
    205. </size>
    206. </property>
    207. <property name="alignment">
    208. <set>Qt::AlignRight|Qt::AlignTrailing|Qt::AlignVCenter</set>
    209. </property>
    210. <property name="buttonSymbols">
    211. <enum>QAbstractSpinBox::NoButtons</enum>
    212. </property>
    213. <property name="decimals">
    214. <number>2</number>
    215. </property>
    216. <property name="maximum">
    217. <double>999999999.990000009536743</double>
    218. </property>
    219. </widget>
    220. </item>
    221. <item>
    222. <spacer name="horizontalSpacer_2">
    223. <property name="orientation">
    224. <enum>Qt::Horizontal</enum>
    225. </property>
    226. <property name="sizeHint" stdset="0">
    227. <size>
    228. <width>40</width>
    229. <height>20</height>
    230. </size>
    231. </property>
    232. </spacer>
    233. </item>
    234. </layout>
    235. </item>
    236. <item row="2" column="0">
    237. <widget class="QPushButton" name="addProductButton">
    238. <property name="enabled">
    239. <bool>false</bool>
    240. </property>
    241. <property name="text">
    242. <string>Add Product</string>
    243. </property>
    244. </widget>
    245. </item>
    246. <item row="2" column="1">
    247. <spacer name="horizontalSpacer">
    248. <property name="orientation">
    249. <enum>Qt::Horizontal</enum>
    250. </property>
    251. <property name="sizeHint" stdset="0">
    252. <size>
    253. <width>310</width>
    254. <height>20</height>
    255. </size>
    256. </property>
    257. </spacer>
    258. </item>
    259. </layout>
    260. </widget>
    261. </item>
    262. <item>
    263. <widget class="QDialogButtonBox" name="buttonBox">
    264. <property name="standardButtons">
    265. <set>QDialogButtonBox::Ok</set>
    266. </property>
    267. </widget>
    268. </item>
    269. </layout>
    270. </widget>
    271. <layoutdefault spacing="6" margin="11"/>
    272. <tabstops>
    273. <tabstop>productListComboBox</tabstop>
    274. <tabstop>pushButton</tabstop>
    275. <tabstop>productTableView</tabstop>
    276. <tabstop>upButton</tabstop>
    277. <tabstop>downButton</tabstop>
    278. <tabstop>deleteButton</tabstop>
    279. <tabstop>newProductLineEdit</tabstop>
    280. <tabstop>newPriceDoubleSpinBox</tabstop>
    281. <tabstop>addProductButton</tabstop>
    282. <tabstop>buttonBox</tabstop>
    283. </tabstops>
    284. <resources>
    285. <include location="salescenter.qrc"/>
    286. </resources>
    287. <connections/>
    288. </ui>
    To copy to clipboard, switch view to plain text mode 

  3. #3
    Join Date
    Jul 2009
    Posts
    139
    Thanks
    13
    Thanked 59 Times in 52 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: Managing data in a TableView

    This does everything except delete nicely. Someone else will probably suggest a lot simpler method.
    Header:
    Qt Code:
    1. #include <QtSql>
    2. #include <QtGui>
    3.  
    4. class EditableSqlModel : public QSqlQueryModel
    5. {
    6. Q_OBJECT
    7.  
    8. public:
    9. EditableSqlModel(QObject * parent = 0);
    10. bool setData ( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole );
    11. Qt::ItemFlags flags ( const QModelIndex & index ) const;
    12. QVariant data ( const QModelIndex & item, int role = Qt::DisplayRole ) const;
    13. int rowCount ( const QModelIndex & parent = QModelIndex() ) const;
    14. void sort ( int column, Qt::SortOrder order = Qt::AscendingOrder );
    15. bool removeRows ( int row, int count, const QModelIndex & parent = QModelIndex() );
    16. private:
    17. QHash<QString, QString> hash;
    18. int rows;
    19. QString sortField, ordering;
    20. };
    21.  
    22.  
    23. class MainWindow : public QMainWindow
    24. {
    25. Q_OBJECT
    26. public:
    27. MainWindow(QWidget * parent = 0);
    28. public slots:
    29. void deleteEntry();
    30.  
    31. private:
    32. EditableSqlModel model;
    33. QAction deleteAction;
    34. };
    To copy to clipboard, switch view to plain text mode 
    and source
    Qt Code:
    1. #include <QtGui>
    2. #include "test11.h"
    3.  
    4.  
    5. EditableSqlModel::EditableSqlModel(QObject * parent) :
    6. QSqlQueryModel(parent), rows(1), sortField("First"), ordering("ASC")
    7. {}
    8.  
    9. QVariant EditableSqlModel::data ( const QModelIndex & item, int role ) const
    10. {
    11. if (item.row() == rowCount() - 1)
    12. return QVariant(); /* Our empty row for inserts. */
    13.  
    14. if (role == Qt::DisplayRole)
    15. {
    16. QString where = QString("%1_%2").arg(item.row()).arg(item.column());
    17. QString original;
    18.  
    19. if (hash.contains(where))
    20. original = hash.value(where); /* New data from the hash. */
    21. else
    22. original = QSqlQueryModel::data(item, role).toString(); /* Old data from the original model. */
    23.  
    24. if (item.column() == 1)
    25. {
    26. /* Custom formatting goes here. */
    27. original = original.prepend('$');
    28. }
    29.  
    30. return original;
    31. }
    32.  
    33. return QSqlQueryModel::data(item, role);
    34. }
    35.  
    36.  
    37. bool EditableSqlModel::setData ( const QModelIndex & itemIndex, const QVariant & value, int role )
    38. {
    39. if (itemIndex.column() < 0 || itemIndex.column() > 2)
    40. return false;
    41.  
    42. QSqlQuery query;
    43. bool fInserted;
    44.  
    45. if (itemIndex.row() == rowCount() - 1)
    46. {
    47. QString first = itemIndex.column() == 1 ? value.toString() : "";
    48. QString last = itemIndex.column() == 2 ? value.toString() : "";
    49.  
    50. query.prepare("INSERT INTO T1(First, Last) VALUES(:first, :last)");
    51. query.bindValue(":first", first);
    52. query.bindValue(":last", last);
    53. query.exec();
    54.  
    55. /* Store our primary key. */
    56. QString where = QString("%1_%2").arg(itemIndex.row()).arg(0);
    57. hash.insert(where, query.lastInsertId().toString());
    58.  
    59. beginInsertRows(itemIndex.parent(), itemIndex.row(), itemIndex.row());
    60. rows++;
    61. fInserted = true;
    62. }
    63. else
    64. {
    65. int primaryKey = data(index(itemIndex.row(), 0), Qt::DisplayRole).toInt();
    66.  
    67. if (itemIndex.column() == 1)
    68. {
    69. query.prepare("UPDATE T1 SET First=:first WHERE id = :id");
    70. query.bindValue(":first", value);
    71. query.bindValue(":id", primaryKey);
    72. query.exec();
    73. }
    74. else if (itemIndex.column() == 2)
    75. {
    76. query.prepare("UPDATE T1 SET Last=:last WHERE id = :id");
    77. query.bindValue(":last", value);
    78. query.bindValue(":id", primaryKey);
    79. query.exec();
    80. }
    81. }
    82.  
    83. /* Store the new value in a hash to retrieve later. */
    84. QString where = QString("%1_%2").arg(itemIndex.row()).arg(itemIndex.column());
    85. hash.insert(where, value.toString());
    86.  
    87. if (fInserted)
    88. endInsertRows();
    89.  
    90. return true;
    91. }
    92.  
    93. int EditableSqlModel::rowCount ( const QModelIndex & parent) const
    94. {
    95. if (parent.isValid()) return 0;
    96. return QSqlQueryModel::rowCount(parent) + rows;
    97. }
    98.  
    99.  
    100. Qt::ItemFlags EditableSqlModel::flags ( const QModelIndex & index ) const
    101. {
    102. return QSqlQueryModel::flags(index) | Qt::ItemIsEditable;
    103. }
    104.  
    105. void EditableSqlModel::sort ( int column, Qt::SortOrder order)
    106. {
    107. sortField = column == 1 ? "First" : "Last";
    108. ordering = order == Qt::AscendingOrder ? "ASC" : "DESC";
    109. rows = 1;
    110. hash.clear();
    111. beginResetModel();
    112. setQuery(QString("SELECT id, First, Last FROM T1 ORDER BY %1 %2").arg(sortField, ordering));
    113. endResetModel();
    114. }
    115.  
    116. bool EditableSqlModel::removeRows ( int row, int count, const QModelIndex & parent)
    117. {
    118. beginResetModel();
    119. QSqlQuery query;
    120. query.prepare("DELETE FROM T1 WHERE id=:id");
    121. for (int i = row; i < row + count; i++)
    122. {
    123. query.bindValue(":id", data(index(i, 0), Qt::DisplayRole));
    124. query.exec();
    125. }
    126. rows = 1;
    127. hash.clear();
    128. setQuery(QString("SELECT id, First, Last FROM T1 ORDER BY %1 %2").arg(sortField, ordering));
    129. endResetModel();
    130. }
    131.  
    132. void MainWindow::deleteEntry()
    133. {
    134. QModelIndexList idxs = tv.selectionModel()->selectedRows();
    135.  
    136. for (int i = idxs.length() - 1; i >= 0; i--)
    137. {
    138. tv.model()->removeRow(idxs.at(i).row(), idxs.at(i).parent());
    139. }
    140. }
    141.  
    142. MainWindow::MainWindow(QWidget *parent) :
    143. QMainWindow(parent), deleteAction("Delete", this), tv(this)
    144. {
    145. model.setQuery("SELECT id, First, Last FROM T1 ORDER BY First ASC");
    146.  
    147. tv.setModel(&model);
    148. tv.setColumnHidden(0, true); /* Hide primary key */
    149. tv.setSortingEnabled(true);
    150. tv.addAction(&deleteAction);
    151. tv.setContextMenuPolicy(Qt::ActionsContextMenu);
    152.  
    153. connect(&deleteAction, SIGNAL(triggered()), this, SLOT(deleteEntry()));
    154.  
    155. setCentralWidget(&tv);
    156. tv.show();
    157.  
    158. }
    159.  
    160. void setupDb(QSqlDatabase & db)
    161. {
    162. db = QSqlDatabase::addDatabase("QSQLITE");
    163. db.setDatabaseName(":memory:");
    164. db.open();
    165.  
    166. QString sql = QString("CREATE TABLE T1(id INTEGER PRIMARY KEY AUTOINCREMENT, First, Last)");
    167. QSqlQuery query(db);
    168. query.exec(sql);
    169.  
    170. db.transaction();
    171.  
    172. query.prepare("INSERT INTO T1 (First, Last) "
    173. "VALUES (:2, :3)");
    174.  
    175. for (int i = 0; i < 100; i++)
    176. {
    177. query.bindValue(":2", QString("Bart %1").arg(i));
    178. query.bindValue(":3", "Simpson");
    179. query.exec();
    180. }
    181.  
    182. db.commit();
    183. }
    184.  
    185.  
    186.  
    187. int main(int argc, char *argv[])
    188. {
    189. QApplication a(argc, argv);
    190.  
    191. setupDb(db);
    192.  
    193. MainWindow win(0);
    194. win.show();
    195.  
    196. return a.exec();
    197.  
    198. }
    To copy to clipboard, switch view to plain text mode 

  4. The following user says thank you to numbat for this useful post:

    scarleton (6th June 2010)

  5. #4
    Join Date
    Jun 2010
    Location
    Cincinnati, Ohio, USA
    Posts
    90
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Managing data in a TableView

    This is GREAT, thank you!!! Do you have any thoughts on how to detect when a user clicks on the row number, to the left of the first column? If I can get that piece of info, I can implement a <Delete> button outside of the TableView.

    Sam

  6. #5
    Join Date
    Jun 2010
    Location
    Cincinnati, Ohio, USA
    Posts
    90
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Managing data in a TableView

    Someone posted to another question along the same lines, the solution was to capture the sectionClicked signal on the TableView's verticalHeader.

  7. #6
    Join Date
    Jul 2009
    Posts
    139
    Thanks
    13
    Thanked 59 Times in 52 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: Managing data in a TableView

    Here is version 2. It does delete elegantly (without refreshing the model). It turned out to be very difficult to get working, so test well if you use it.
    Header:
    Qt Code:
    1. #include <QtSql>
    2. #include <QtGui>
    3.  
    4. class EditableSqlModel : public QAbstractItemModel
    5. {
    6. Q_OBJECT
    7.  
    8. public:
    9. EditableSqlModel(QObject * parent = 0);
    10. bool setData ( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole );
    11. Qt::ItemFlags flags ( const QModelIndex & index ) const;
    12. QVariant data ( const QModelIndex & item, int role = Qt::DisplayRole ) const;
    13. int rowCount ( const QModelIndex & parent = QModelIndex() ) const;
    14. void sort ( int column, Qt::SortOrder order = Qt::AscendingOrder );
    15. bool removeRows ( int row, int count, const QModelIndex & parent = QModelIndex() );
    16. QModelIndex index ( int row, int column, const QModelIndex & parent = QModelIndex() ) const;
    17. bool hasIndex ( int row, int column, const QModelIndex & parent = QModelIndex() ) const;
    18. QModelIndex parent(const QModelIndex &child) const;
    19. int columnCount(const QModelIndex &parent = QModelIndex()) const;
    20. void setQuery(QString sql);
    21.  
    22. private:
    23. void refreshOffsets();
    24. void resizeVectors();
    25.  
    26. QString lastSql;
    27. QHash<QString, QString> hash;
    28. int rows;
    29. QString sortField, ordering;
    30. QVector<int> rowOffsets;
    31. QVector<bool> available;
    32. enum { UNAVAILABLE = 1, AVAILABLE = 0 };
    33. };
    34.  
    35.  
    36. class MainWindow : public QMainWindow
    37. {
    38. Q_OBJECT
    39. public:
    40. MainWindow(QWidget * parent = 0);
    41. public slots:
    42. void deleteEntry();
    43.  
    44. private:
    45. EditableSqlModel model;
    46. QAction deleteAction;
    47. };
    To copy to clipboard, switch view to plain text mode 
    Source:
    Qt Code:
    1. #include <QtGui>
    2. #include "test11.h"
    3.  
    4.  
    5. EditableSqlModel::EditableSqlModel(QObject * parent) :
    6. QAbstractItemModel(parent), rows(1), sortField("First"), ordering("ASC")
    7. {}
    8.  
    9.  
    10. QModelIndex EditableSqlModel::parent(const QModelIndex &child) const
    11. {
    12. Q_UNUSED(child);
    13. return QModelIndex();
    14. }
    15.  
    16. int EditableSqlModel::columnCount(const QModelIndex &parent) const
    17. {
    18. if (parent.isValid())
    19. return 0;
    20.  
    21. return 3;
    22. }
    23.  
    24. bool EditableSqlModel::hasIndex ( int row, int column, const QModelIndex & parent ) const
    25. {
    26. if (column < 0 || column > 2 || row < 0 || row >= rowCount() || parent.isValid())
    27. return false;
    28.  
    29. return true;
    30. }
    31.  
    32. QModelIndex EditableSqlModel::index ( int row, int column, const QModelIndex & parent ) const
    33. {
    34. return hasIndex(row, column, parent) ? createIndex(row, column, 0) : QModelIndex();
    35. }
    36.  
    37.  
    38. QVariant EditableSqlModel::data ( const QModelIndex & item, int role ) const
    39. {
    40. if (!item.isValid() || !hasIndex(item.row(), item.column(), item.parent()))
    41. return QVariant();
    42.  
    43. if (item.row() == rowCount() - 1)
    44. return QVariant(); /* Our empty row for inserts. */
    45.  
    46. /* Adjust for deleted rows. */
    47. int row = item.row() + rowOffsets.at(item.row());
    48.  
    49. if (role == Qt::DisplayRole || role == Qt::EditRole)
    50. {
    51. QString where = QString("%1_%2").arg(row).arg(item.column());
    52. QString original;
    53.  
    54. if (hash.contains(where) || hash.contains(QString("%1_0").arg(row)))
    55. original = hash.value(where); /* New data from the hash. */
    56. else
    57. original = mod.data(mod.index(row, item.column()), role).toString(); /* Old data from the original model. */
    58.  
    59. if (item.column() == 1)
    60. {
    61. /* Custom formatting goes here. */
    62. original = original.prepend('$');
    63. }
    64.  
    65. return original;
    66. }
    67.  
    68. return QVariant();
    69. }
    70.  
    71.  
    72. bool EditableSqlModel::setData ( const QModelIndex & itemIndex, const QVariant & value, int role )
    73. {
    74. if (!itemIndex.isValid() || !hasIndex(itemIndex.row(), itemIndex.column(), itemIndex.parent()))
    75. return false;
    76.  
    77. if (role != Qt::DisplayRole && role != Qt::EditRole)
    78. return false;
    79.  
    80. QSqlQuery query;
    81. bool fInserted;
    82.  
    83. /* Adjust for deleted rows. */
    84. int row = itemIndex.row() + rowOffsets.at(itemIndex.row());
    85.  
    86. if (itemIndex.row() == rowCount() - 1 &&
    87. !hash.contains(QString("%1_0").arg(row)))
    88. {
    89. /* A new record, so insert it into the database. */
    90. QString first = itemIndex.column() == 1 ? value.toString() : "";
    91. QString last = itemIndex.column() == 2 ? value.toString() : "";
    92.  
    93. query.prepare("INSERT INTO T1(First, Last) VALUES(:first, :last)");
    94. query.bindValue(":first", first);
    95. query.bindValue(":last", last);
    96. query.exec();
    97.  
    98. /* Store our primary key. */
    99. QString where = QString("%1_0").arg(row);
    100. hash.insert(where, query.lastInsertId().toString());
    101.  
    102. beginInsertRows(itemIndex.parent(), itemIndex.row() + 1, itemIndex.row() + 1);
    103. rows = rowCount() + 1;
    104. resizeVectors();
    105. fInserted = true;
    106. }
    107. else
    108. {
    109. /* An existing record, so update it in the database. */
    110. int primaryKey = data(index(itemIndex.row(), 0), Qt::DisplayRole).toInt();
    111.  
    112. if (itemIndex.column() == 1)
    113. {
    114. query.prepare("UPDATE T1 SET First=:first WHERE id = :id");
    115. query.bindValue(":first", value);
    116. query.bindValue(":id", primaryKey);
    117. query.exec();
    118. }
    119. else if (itemIndex.column() == 2)
    120. {
    121. query.prepare("UPDATE T1 SET Last=:last WHERE id = :id");
    122. query.bindValue(":last", value);
    123. query.bindValue(":id", primaryKey);
    124. query.exec();
    125. }
    126. }
    127.  
    128. /* Store the new value in a hash to retrieve later. */
    129. QString where = QString("%1_%2").arg(row).arg(itemIndex.column());
    130. hash.insert(where, value.toString());
    131.  
    132. if (fInserted)
    133. endInsertRows();
    134.  
    135. return true;
    136. }
    137.  
    138. int EditableSqlModel::rowCount ( const QModelIndex & parent) const
    139. {
    140. return parent.isValid() ? 0 : rows;
    141. }
    142.  
    143.  
    144. Qt::ItemFlags EditableSqlModel::flags ( const QModelIndex & index ) const
    145. {
    146. Q_UNUSED(index);
    147. return Qt::ItemIsSelectable | Qt::ItemIsEnabled | Qt::ItemIsEditable;
    148. }
    149.  
    150. void EditableSqlModel::setQuery(QString sql)
    151. {
    152. if (sql == lastSql) return;
    153. beginResetModel();
    154. beginRemoveRows(QModelIndex(), 0, rowCount() - 1);
    155. endRemoveRows();
    156.  
    157. mod.setQuery(sql);
    158. rows = mod.rowCount() + 1;
    159. resizeVectors();
    160.  
    161. beginInsertRows(QModelIndex(), 0, rows - 1);
    162. hash.clear();
    163. qFill(rowOffsets, 0);
    164. qFill(available, 0);
    165.  
    166. endInsertRows();
    167. endResetModel();
    168. lastSql = sql;
    169. }
    170.  
    171. void EditableSqlModel::sort ( int column, Qt::SortOrder order)
    172. {
    173. sortField = column == 1 ? "First" : "Last";
    174. ordering = order == Qt::AscendingOrder ? "ASC" : "DESC";
    175. setQuery(QString("SELECT id, First, Last FROM T1 ORDER BY %1 %2").arg(sortField, ordering));
    176. }
    177.  
    178. void EditableSqlModel::resizeVectors()
    179. {
    180. rowOffsets.resize(qMax(rowOffsets.count() + 1, rows));
    181. available.resize(qMax(available.count() + 1, rows));
    182.  
    183. refreshOffsets();
    184. }
    185.  
    186. void EditableSqlModel::refreshOffsets()
    187. {
    188. /* Now calculate the offsets. */
    189. for (int i = 0, j = 0; i < available.count(); i++)
    190. {
    191. if (available.at(i) == AVAILABLE)
    192. {
    193. rowOffsets[j] = i - j;
    194. j++;
    195. }
    196. }
    197. }
    198.  
    199. bool EditableSqlModel::removeRows ( int row, int count, const QModelIndex & parent)
    200. {
    201. beginRemoveRows(parent, row, row + count - 1);
    202.  
    203. if (row == rowCount() - 1)
    204. {
    205. /* Don't let the new addition row be deleted. */
    206. endRemoveRows();
    207. beginInsertRows(parent, row, row);
    208. endInsertRows();
    209. return false;
    210. }
    211.  
    212. QSqlQuery query;
    213. query.prepare("DELETE FROM T1 WHERE id=:id");
    214. for (int i = row + count - 1; i >= row; i--)
    215. {
    216. query.bindValue(":id", data(index(i, 0)).toInt());
    217. query.exec();
    218. }
    219.  
    220. /* Get real rows deleted and mark as deleted. */
    221. for (int i = row; i < row + count; i++)
    222. {
    223. int rowreal = i + rowOffsets.at(i);
    224. available[rowreal] = UNAVAILABLE;
    225.  
    226. hash.remove(QString("%1_0").arg(rowreal));
    227. hash.remove(QString("%1_1").arg(rowreal));
    228. hash.remove(QString("%1_2").arg(rowreal));
    229. }
    230.  
    231. refreshOffsets();
    232. rows -= count;
    233. endRemoveRows();
    234. return true;
    235. }
    236.  
    237. void MainWindow::deleteEntry()
    238. {
    239. QModelIndexList idxs = tv.selectionModel()->selectedRows();
    240.  
    241. /* Must do this one at a time or sort and go backwards. */
    242. while (!idxs.isEmpty())
    243. {
    244. tv.model()->removeRow(idxs.at(0).row(), idxs.at(0).parent());
    245. idxs = tv.selectionModel()->selectedRows();
    246. }
    247. }
    248.  
    249. MainWindow::MainWindow(QWidget *parent) :
    250. QMainWindow(parent), deleteAction("Delete", this), tv(this)
    251. {
    252. model.setQuery("SELECT id, First, Last FROM T1");
    253.  
    254. tv.setModel(&model);
    255. tv.setColumnHidden(0, true); /* Hide primary key */
    256. tv.setSortingEnabled(true);
    257. tv.addAction(&deleteAction);
    258. tv.setContextMenuPolicy(Qt::ActionsContextMenu);
    259.  
    260. connect(&deleteAction, SIGNAL(triggered()), this, SLOT(deleteEntry()));
    261. setCentralWidget(&tv);
    262. tv.show();
    263. }
    264.  
    265. void setupDb(QSqlDatabase & db)
    266. {
    267. db = QSqlDatabase::addDatabase("QSQLITE");
    268. db.setDatabaseName(":memory:");
    269. db.open();
    270.  
    271. QString sql = QString("CREATE TABLE T1(id INTEGER PRIMARY KEY AUTOINCREMENT, First, Last)");
    272. QSqlQuery query(db);
    273. query.exec(sql);
    274.  
    275. db.transaction();
    276.  
    277. query.prepare("INSERT INTO T1 (First, Last) "
    278. "VALUES (:2, :3)");
    279.  
    280. for (int i = 0; i < 5; i++)
    281. {
    282. query.bindValue(":2", QString("Bart %1").arg(i));
    283. query.bindValue(":3", "Simpson");
    284. query.exec();
    285. }
    286.  
    287. db.commit();
    288. }
    289.  
    290.  
    291. int main(int argc, char *argv[])
    292. {
    293. QApplication a(argc, argv);
    294.  
    295. setupDb(db);
    296.  
    297. MainWindow win(0);
    298. win.show();
    299.  
    300. return a.exec();
    301. }
    To copy to clipboard, switch view to plain text mode 

  8. #7
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Managing data in a TableView

    Please do not inline such large quantities of code in your posts. Attach files to your posts instead.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


Similar Threads

  1. Replies: 1
    Last Post: 2nd May 2010, 08:50
  2. getting data from tableView
    By mkarakaplan in forum Newbie
    Replies: 1
    Last Post: 7th November 2007, 09:51
  3. managing workerthreads
    By darksaga in forum Qt Programming
    Replies: 4
    Last Post: 20th August 2007, 18:26
  4. My derived TableView - how to get data mouse is over
    By steg90 in forum Qt Programming
    Replies: 3
    Last Post: 17th May 2007, 09:09
  5. Managing Z order without QCanvasItem
    By yellowmat in forum Newbie
    Replies: 1
    Last Post: 4th February 2006, 16:17

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.