The user_version pragma gets or sets the user-defined version value that is stored in the database header. Following is the simple syntax:

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

This is a 32-bit signed integer value which can be set by the developer for version tracking purpose.  Default value is 0.

Using user_version for database updating example


	bool DatabaseSqlite::CheckForUpdateDatabase(void)
	{
		System::Data::SQLite::SQLiteConnection ^Connection1;
		String ^sTemp;
		int user_version;
		bool do_loop;

		try
		{

			//----- OPEN THE DATABASE CONNECTION -----
			Connection1 = gcnew System::Data::SQLite::SQLiteConnection("data source=" + Environment::GetFolderPath(Environment::SpecialFolder::ApplicationData) + "\\" + Application::CompanyName + "\\" + Application::ProductName + "\\mydatabasefile.db");
			Connection1->Open();                             // Open the connection to the database

			System::Data::SQLite::SQLiteCommand ^Command1 = gcnew System::Data::SQLite::SQLiteCommand(Connection1);

			//-------------------------------------
			//-------------------------------------
			//----- CHECK FOR UPDATE DATABASE -----
			//-------------------------------------
			//-------------------------------------

			#define OUR_CURRENT_SQLITE_DATABASE_VERSION		1			//<<<<<<<<<<<<<INCREMENT WHEN NEW UPDATE STEP ADDED TO SWITCH BELOW

			do_loop = true;
			while (do_loop)
			{
				//----- GET OUR CURRENT DATABASE VERSION -----
				//(We use the PRAGMA user_version field from the database header to store our version number)
				user_version = 99999999;
				Command1->CommandText = "PRAGMA user_version;";      // Select all rows from our database table
				System::Data::SQLite::SQLiteDataReader ^Reader1 = Command1->ExecuteReader();
				{
					if (Reader1->Read())
						user_version = Convert::ToInt32(Reader1["user_version"]);
				}
				Reader1->Close();

				//Exit if no updates needed
				if (user_version >= OUR_CURRENT_SQLITE_DATABASE_VERSION)
					break;

				//--------------------------
				//----- DO NEXT UPDATE -----
				//--------------------------
				switch (user_version)
				{
					case 0:
					//----- CURRENTLY VERSION 0 - ADD NEW #### FIELD TO #### ------
					sTemp = "ALTER TABLE tblLogEvents ADD COLUMN my_new_field_name TEXT;";
					Command1->CommandText = sTemp;
					Command1->ExecuteNonQuery();
					break;

				default:
					//----- ERROR -----
					do_loop = false;
					break;
				}


				//----- UPDATE OUR DATABASE VERSION NUMBER -----
				if (do_loop)
				{
					user_version++;
					Command1->CommandText = "PRAGMA user_version=" + user_version + ";";
					Command1->ExecuteNonQuery();
				}

			}

			return(true);
		}
		catch (Exception ^e)
		{
			MessageBox::Show(L"Error:\n" + e, L"Error", MessageBoxButtons::OK, MessageBoxIcon::Error);
			return(false);
		}
		finally
		{
			//----- CLOSE THE DATABASE CONNECTION -----
			try
			{
				if (Connection1 != nullptr)
					Connection1->Close();
			}
			catch (Exception ^)
			{
			}
		}
	}

 

Feel free to comment if you can add help to this page or point out issues and solutions you have found. I do not provide support on this site, if you need help with a problem head over to stack overflow.

Comments

Your email address will not be published. Required fields are marked *