CODEWITHSUNDEEP
c++sortingsqlite
Birdbuster
Birdbuster

Reputation: 1549

Best way to actually sort a database table with sqlite and in c++?

I need to sort a table permanently. I have no control of how the database is created or its tables. I have a program written in c++ that has several methods that do different things with the database and its tables. For instance print, recall row, update row, download row, update row and the list goes on.

I am currently on sort database. It needs to actually sort the database in respect to a specified column and can be sub sorted by another column if the user specifies one. So that after the sort is done if it was viewed with a third party application for instance all the data would show up sorted.

I know SELECT * FROM table1 ORDER BY Col1, Col2 would return the table sorted but from an actually memory stand point all the data is the table is still the same, so if col 1 was all out of order after the ORDER BY the database viewer would still show col 1 all out of order. After the sort the data in the database should be sorted and when viewed in the database viewer it all be in order.

This is a complete Edit of my first post. fyi

Upvotes: 0

Views: 1652

Answers (1)

Birdbuster
Birdbuster

Reputation: 1549

Through the help of @RogerRowland I was able to solve this issue but for those looking to do the same thing as me. Why you would actually ever want to do this I dont know, but if you need to actually sort the database table's data rather than just return sorted data and are using sqlite3 and c++ this is what you wanna do.

Step 1)

rc = sqlite3_prepare(this->dbPointer, "CREATE [TEMP] VIEW tempViewOfSortedTable AS SELECT * FROM tableName ORDER BY columnName", -1, &ppStmt, NULL);

Step 2) Grab all of your table info (column name and types and what not) and store it in a string with.

rc = sqlite3_prepare_v2(this->dbPointer, "pragma table_info tableName", -1, &ppStmt, NULL);

Step 3) Create a new table with the same columns and properties as your original.

sprintf(strQuery, "CREATE TABLE tempUserTable (%s)", strListOfFieldsAndTypes);
if (rc == SQLITE_OK)rc = sqlite3_exec (this->dbPointer, strQuery, NULL, NULL, &errMsg);

Step 4) Copy all of your view's sorted data into the new table.

sprintf(strQuery, "INSERT INTO tempUserTable SELECT %s FROM tempViewOfSortedTable", strListOfFields);
if (rc == SQLITE_OK)rc = sqlite3_exec (this->dbPointer, strQuery, NULL, NULL, &errMsg);

Step 5) Drop the old table

if (rc == SQLITE_OK)rc = sqlite3_exec (this->dbPointer, "DROP TABLE tableName", NULL, NULL, &errMsg);

Step 6) Rename your new table to the old name if desired.

if (rc == SQLITE_OK)rc = sqlite3_exec (this->dbPointer, "ALTER TABLE tempUserTable RENAME TO 'tableName'", NULL, NULL, &errMsg);

You may also want to drop the view if you didn't create a temp one or even if you are going to be repeating this before you close the db because if you dont the temp view lasts till you close the db.

Also by all means not claiming this is the fast route. The only reason I was using sqlite3_exec is because I recycled someone else's old code I will be replacing it with sqlite3_prepare_v2 myself but wanted to get this up for any others with the same question.

Sources.

http://www.sqlite.org/lang_createview.html

http://www.sqlite.org/capi3ref.html

Upvotes: 1

Related Questions