You can expand option 1 to have a 3rd column, giving a data-type. Your application can than use this data-type column to cast the value. But yeah, I would go with option 1, if config files are not an option. Another advantage of option 1 is you can read it into a Dictionary object (or … Read more
All database statements are executed within the context of a physical transaction, even when we don’t explicitly declare transaction boundaries (BEGIN/COMMIT/ROLLBACK). If you don’t declare the transaction boundaries, then each statement will have to be executed in a separate transaction. This may even lead to opening and closing one connection per statement. Declaring a service … Read more
No, I don’t think an HTTP 502 Bad Gateway error is appropriate when a database error occurs. HTTP errors say something about the HTTP protocol. This specific error indicates a server is trying to relay the HTTP request, but the upstream server did not respond correctly. Your web application communicating with a database server is … Read more
The exact same page explains what a dirty read is: Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid … Read more
Probably a nicer version. OrderStateIdentifier field is used for both JSON serialization and database field, while OrderState is only used in the code for convenience. public string OrderStateIdentifier { get { return OrderState.ToString(); } set { OrderState = value.ToEnum<OrderState>(); } } [NotMapped] [JsonIgnore] public OrderState OrderState { get; set; } public static class EnumHelper { … Read more
I’m a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.
I had the same question and the answer, which I found, is: if not engine.dialect.has_schema(engine, schema_name): engine.execute(sqlalchemy.schema.CreateSchema(schema_name)) We can also check schema without engine instance, but using connection conn = engine.connect() if conn.dialect.has_schema(conn, schema_name): …
There is only one way to know: Time it. In general, I think a single join enables the database to do a lot of optimizations, as it can see all the tables it needs to scan, overhead is reduced, and it can build up the result set locally. Recently, I had about 100 select-statements which … Read more
Assuming you installed PostgreSQL on Windows with the PostgreSQL “One-click” installer packaged by EnterpriseDB, psql is not added to the PATH automatically. That’s partly because adding it to the path could otherwise cause confusion when people have multiple versions of PostgreSQL installed. You need to specify the full explicit path to psql, eg: “%PROGRAMFILES%\Postgresql\9.2\bin\psql.exe” or … Read more
The “phantom read” in MySQL on RR isolation level is hidden deep, but still can reproduce it. Here are the steps: create table ab(a int primary key, b int); Tx1: begin; select * from ab; // empty set Tx2: begin; insert into ab values(1,1); commit; Tx1: select * from ab; // empty set, expected phantom … Read more