· 7 years ago · Jan 20, 2019, 05:48 AM
1#region license
2/*
3This file is public domain.
4You may freely do anything with it.
5
6Copyright (c) VPKSoft 2019
7*/
8#endregion
9
10using System;
11using System.Collections.Generic;
12using System.Data.SQLite;
13using System.IO;
14
15namespace ScriptNotepad
16{
17 /// <summary>
18 /// A class to run updates on a program's database if updated.
19 /// </summary>
20 public class ScriptRunner
21 {
22 /// <summary>
23 /// A class representing a "block" in the script file. A such block should start with SQL comment '--VER n' and end with '--ENDVER n', where n is the version number.
24 /// </summary>
25 public class DBScriptBlock
26 {
27 /// <summary>
28 /// A list of lines in a SQL script file which should start with SQL comment '--VER n' and end with '--ENDVER n', where n is the version number of the database.
29 /// </summary>
30 public List<string> SQLBlock = new List<string>();
31
32 /// <summary>
33 /// The database version number.
34 /// </summary>
35 public int DBVer = 0;
36 }
37
38 /// <summary>
39 /// Checks the SQL script for version blocks and if the there are version blocks not already run they are executed against the SQLite database.
40 /// </summary>
41 /// <param name="sqliteDatasource">A file name for a SQLite database.</param>
42 /// <param name="scriptFile">A database script file location (optional).</param>
43 /// <returns>True if the script was run successfully, otherwise false.
44 /// <note type="note">This has no indication to an issue whether an commands were actually executed.</note>
45 /// </returns>
46 public static bool RunScript(string sqliteDatasource, string scriptFile = "")
47 {
48 try // we try..
49 {
50 int dbVersion = 0; // assume that the database it at version 0..
51
52 // construct a list of database version blocks..
53 List<DBScriptBlock> sqlBlocks = new List<DBScriptBlock>();
54
55 // indicates if any of the database version block executions failed..
56 bool noBlockExecError = true;
57
58 // construct a SQLite database connection..
59 using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + sqliteDatasource + ";Pooling=true;FailIfMissing=false"))
60 {
61 conn.Open(); // open the SQLite database connection..
62
63 int DBVer = 0; // assume that a version block in the SQL script file is at version 0..
64 string line; // a line in the SQL script file..
65
66 try // again it is required to try as there might be syntax errors in the script file with the
67 // '--VER n' and '--ENDVER n' blocks..
68 {
69 // start reading the script file.. it is assumed to be in the application's
70 // executable directory by the name of script.sql_script..
71
72 // if the script file location has been set then use that; otherwise use the default location..
73 scriptFile = scriptFile == string.Empty ?
74 Path.Combine(AppDomain.CurrentDomain.BaseDirectory + "script.sql_script") :
75 scriptFile;
76
77 using (StreamReader sr = new StreamReader(scriptFile))
78 {
79 while (!sr.EndOfStream) // read until all lines are read..
80 {
81 // keep reading until a first '--VER n' line is found..
82 while (!(line = sr.ReadLine()).StartsWith("--VER " + DBVer)) { }
83
84 // start building an instance of DBScriptBlock class from the "block"..
85 DBScriptBlock scriptBlock = new DBScriptBlock
86 {
87 DBVer = Convert.ToInt32(line.Split(' ')[1])
88 };
89
90 // and lines to the block, until a line like '--ENDVER n' is found..
91 while (!(line = sr.ReadLine()).StartsWith("--ENDVER " + DBVer))
92 {
93 // add the lines to the DBScriptBlock class instance..
94 scriptBlock.SQLBlock.Add(line);
95 }
96 DBVer++; // increase the database version by one..
97 sqlBlocks.Add(scriptBlock); // add the DBScriptBlock class instance to the list..
98 }
99 }
100 }
101 catch
102 {
103 // possible to many lines at the end of the script (last line must end with '--ENDVER n')
104 }
105
106 // as the SQL script file should always start with script block such as:
107 // --VER 0
108 // CREATE TABLE IF NOT EXISTS DBVERSION(DBVERSION INTEGER NOT NULL);
109 // --ENDVER 0
110
111 // an assumption is made that the version of the SQLite database can now be checked..
112 using (SQLiteCommand command = new SQLiteCommand(conn))
113 {
114 try
115 {
116 // check the current SQLite database version..
117 command.CommandText = "SELECT IFNULL(MAX(DBVERSION), 0) AS VER FROM DBVERSION; ";
118 using (SQLiteDataReader dr = command.ExecuteReader())
119 {
120 // if anything was returned..
121 if (dr.Read())
122 {
123 // get the current SQLite database version..
124 dbVersion = dr.GetInt32(0);
125 }
126 else
127 {
128 // nothing was returned, so assume the version as 0..
129 dbVersion = 0;
130 }
131 }
132 }
133 catch // an exception occurred..
134 {
135 dbVersion = 0; // ..so assume the version as 0..
136 }
137 }
138
139 // avoid to run the last block multiple times..
140 if (dbVersion > 0)
141 {
142 // ..if the database version is larger than 0..
143 sqlBlocks.RemoveAt(0);
144 }
145
146 // loop through the list of DBScriptBlock class instances starting from the next SQL script version..
147 for (int i = dbVersion; i < sqlBlocks.Count; i++)
148 {
149 string exec = string.Empty; // build an SQLite "transaction" block of lines in the block
150 foreach (string sqLine in sqlBlocks[i].SQLBlock)
151 {
152 exec += sqLine + Environment.NewLine;
153 }
154 try // keep trying..
155 {
156 // execute the SQLite "transaction" script block..
157 using (SQLiteCommand command = new SQLiteCommand(conn))
158 {
159 command.CommandText = exec;
160 command.ExecuteNonQuery();
161 }
162 }
163 catch
164 {
165 // indicate that a block execution failed..
166 noBlockExecError = false;
167 break; // do nothing as the database wouldn't get fully updated..
168 }
169
170 // construct a SQL sentence to update the SQLite database version..
171 exec = "INSERT INTO DBVERSION(DBVERSION) " + Environment.NewLine +
172 "SELECT " + sqlBlocks[i].DBVer + " " + Environment.NewLine +
173 "WHERE NOT EXISTS(SELECT 1 FROM DBVERSION WHERE DBVERSION = " + sqlBlocks[i].DBVer + "); " + Environment.NewLine;
174 // update the SQLite database version (DBVERSION table)..
175 using (SQLiteCommand command = new SQLiteCommand(conn))
176 {
177 command.CommandText = exec;
178 command.ExecuteNonQuery();
179 }
180 }
181 }
182
183 // return the value indicating if the SQLite database was updated successfully..
184 return true & noBlockExecError;
185 }
186 catch
187 {
188 // return false indicating that the SQLite database wasn't updated successfully..
189 return false;
190 }
191 }
192 }
193}