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 ^)
			{
			}
		}
	}

 

USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.