コード例 #1
0
ファイル: DatabaseServer.cs プロジェクト: eoehen/nHydrate
        internal static void CreateDatabase(InstallSetup setup)
        {
            try
            {
                using (var conn = new NpgsqlConnection(setup.MasterConnectionString))
                {
                    conn.Open();
                    var cmdCreateDb = new NpgsqlCommand();
                    var fileInfo    = string.Empty;
                    if (!string.IsNullOrEmpty(setup.DiskPath))
                    {
                        fileInfo = " WITH LOCATION '" + Path.Combine(setup.DiskPath, setup.NewDatabaseName) + "')";
                    }
                    cmdCreateDb.CommandText = $"CREATE DATABASE \"{setup.NewDatabaseName}\"" + fileInfo;
                    cmdCreateDb.CommandType = System.Data.CommandType.Text;
                    cmdCreateDb.Connection  = conn;
                    DatabaseServer.ExecuteCommand(cmdCreateDb);
                }

                using (var conn = new NpgsqlConnection(setup.ConnectionString))
                {
                    conn.Open();
                    //Add UUID generator
                    var command = new NpgsqlCommand();
                    command.CommandText = "CREATE EXTENSION \"uuid-ossp\";";
                    command.CommandType = System.Data.CommandType.Text;
                    command.Connection  = conn;
                    DatabaseServer.ExecuteCommand(command);
                }
            }
            catch { throw; }
            finally
            {
                System.Threading.Thread.Sleep(1000);
            }
        }
コード例 #2
0
        internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts)
        {
            if (sql.StartsWith("--##METHODCALL"))
            {
                CallMethod(sql, connection, transaction, setup);
                return;
            }

            //Test for empty statements
            var originalSQL = sql.Trim();

            sql = originalSQL;
            if (string.IsNullOrEmpty(sql))
            {
                return;
            }

            //Test for noop statements (all comments/empty strings)
            var lines = sql.BreakLines().TrimAll();

            lines.RemoveAll(x => x.StartsWith("--"));
            lines.RemoveAll(x => x == "");
            if ([email protected]())
            {
                return;
            }
            lines = sql.BreakLines().TrimAll(); //Reset

            #region Get Script Key
            var isBody = false;
            var key    = Guid.NewGuid();
            var l      = lines.FirstOrDefault(x => x.StartsWith("--MODELID: "));
            if (l != null)
            {
                lines.Remove(l);
                l   = l.Replace("--MODELID:", string.Empty).Trim();
                sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run
                                                          //if (!Guid.TryParse(l, out key)) key = Guid.NewGuid();
            }
            else
            {
                l = lines.FirstOrDefault(x => x.StartsWith("--MODELID,BODY: "));
                if (l != null)
                {
                    lines.Remove(l);
                    l   = l.Replace("--MODELID,BODY:", string.Empty).Trim();
                    sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run
                    if (!Guid.TryParse(l, out key))
                    {
                        key = Guid.NewGuid();
                    }
                    else
                    {
                        isBody = true;
                    }
                }
            }
            #endregion
            if (string.IsNullOrEmpty(sql))
            {
                return;
            }

            #region Try to remove objects before creation
            var dropObjectName = string.Empty;
            var dropSQL        = GetSQLDropScript(sql);

            //Add a bit of convenience for dropping DB objects before creation
            if (!string.IsNullOrEmpty(dropSQL))
            {
                try
                {
                    if (!setup.CheckOnly)
                    {
                        var dropCommand = new NpgsqlCommand(dropSQL, connection);
                        dropCommand.Transaction    = transaction;
                        dropCommand.CommandTimeout = 0;
                        DatabaseServer.ExecuteCommand(dropCommand);
                    }
                }
                catch (Exception ex)
                {
                    //Ignore. The scripts should not need this. It has been added for convenience
                }
            }
            #endregion

            var command = new NpgsqlCommand(sql, connection);
            command.Transaction    = transaction;
            command.CommandTimeout = 0;
            try
            {
                if (!setup.CheckOnly)
                {
                    var       debugText = "[" + DateTime.Now.ToString() + "]\r\n";
                    const int MAX_SQL   = 500;
                    var       sqlLength = Math.Min(sql.Length, MAX_SQL);
                    debugText += sql.Substring(0, sqlLength);
                    if (sqlLength == MAX_SQL)
                    {
                        debugText += "...";
                    }
                    debugText += "\r\n\r\n";
                    Log.Verbose(debugText);

                    _timer.Restart();
                    DatabaseServer.ExecuteCommand(command);
                    _timer.Stop();

                    Log.Debug <long, string>("Time:{Elapsed:000} Sql:{sql}", _timer.ElapsedMilliseconds, sql);

                    if (successOrderScripts != null && isBody)
                    {
                        successOrderScripts.Add(key);
                    }
                }
            }
            catch (NpgsqlException sqlexp)
            {
                if (failedScripts != null)
                {
                    //Ignore this error, we will re-process it
                    failedScripts.Add(new KeyValuePair <Guid, string>(key, originalSQL));
                    return;
                }
                else
                {
                    throw new InvalidSQLException(sqlexp.Message, sqlexp)
                          {
                              SQL = sql, FileName = setup.DebugScriptName
                          };
                }
            }
            catch (Exception ex) { throw; }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
            }
        }
コード例 #3
0
        public static void Save(string connectionString, string modelKey, IEnumerable <nHydrateDbObject> list, NpgsqlTransaction transaction)
        {
            NpgsqlConnection conn = null;

            if (transaction == null)
            {
                conn = new NpgsqlConnection(connectionString);
                conn.Open();
            }
            else
            {
                conn = transaction.Connection;
            }
            try
            {
                //Create the table if need be
                using (var command3 = new NpgsqlCommand("CREATE TABLE IF NOT EXISTS \"__nhydrateobjects\"" +
                                                        "(\"rowid\" bigint GENERATED BY DEFAULT AS IDENTITY," +
                                                        "\"id\" UUID NULL," +
                                                        "\"name\" varchar (450) NOT NULL," +
                                                        "\"type\" varchar (10) NOT NULL," +
                                                        "\"schema\" varchar (450) NULL," +
                                                        "\"CreatedDate\" timestamp NOT NULL," +
                                                        "\"ModifiedDate\" timestamp NOT NULL," +
                                                        "\"Hash\" varchar (32) NULL," +
                                                        "\"Status\" varchar (500) NULL," +
                                                        "\"ModelKey\" UUID NOT NULL)", conn))
                {
                    command3.Transaction = transaction;
                    DatabaseServer.ExecuteCommand(command3);
                }

                var sql = new StringBuilder();
                sql.AppendLine($"delete from \"__nhydrateobjects\" where \"id\" IS NULL and \"ModelKey\" = '{UpgradeInstaller.MODELKEY}'");
                using (var command3 = new NpgsqlCommand(sql.ToString(), conn))
                {
                    command3.Transaction = transaction;
                    DatabaseServer.ExecuteCommand(command3);
                }

                //Save items to the table
                foreach (var item in list.Where(x => x.Changed))
                {
                    using (var command = new NpgsqlCommand("insert into __nhydrateobjects (\"id\", \"name\", \"type\", \"schema\", \"CreatedDate\", \"ModifiedDate\", \"Hash\", \"ModelKey\", \"Status\") values (@id, @name, @type, @schema, @CreatedDate, @ModifiedDate, @Hash, @ModelKey, @Status)", conn))
                    {
                        command.Transaction = transaction;
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.Guid, Value = (item.id == Guid.Empty ? System.DBNull.Value : (object)item.id), ParameterName = "@id", IsNullable = true
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.Int64, Value = item.rowid, ParameterName = "@rowid", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.String, Value = item.name, ParameterName = "@name", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.String, Value = item.type, ParameterName = "@type", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.String, Value = (item.schema == null ? System.DBNull.Value : (object)item.schema), ParameterName = "@schema", IsNullable = true
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.DateTime, Value = item.CreatedDate, ParameterName = "@CreatedDate", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.DateTime, Value = DateTime.Now, ParameterName = "@ModifiedDate", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.String, Value = item.Hash, ParameterName = "@Hash", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.Guid, Value = item.ModelKey, ParameterName = "@ModelKey", IsNullable = false
                        });
                        command.Parameters.Add(new NpgsqlParameter {
                            DbType = DbType.String, Value = item.Status, ParameterName = "@Status", IsNullable = true
                        });
                        DatabaseServer.ExecuteCommand(command);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (transaction == null && conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }