Q. There is no Collation on columns. Why?
A. I was involved in developing many different DBs and always had a situation that Collation in a column different from the Database Default was considered as an error. One can avoid it via dbProjector.
A brief list of dbProjector benefits:
- SSDT does not support working with data tables. Using only a DB schema without reference and/or initialization data (e.g., reports supported by the application, etc.) is not enough in most cases. When deploying a new DB, it is always necessary to develop something extra to fill various directories, etc. dbProjector has developed distribution means not only of the DB schema itself, but also of data that may be contained therein. In addition, flexible tools to add the data in the update are offered.
- While synchronizing the DB schema to the project in SSDT, one can only select the entire object, for example, one can select the entire table and cannot update only a few columns of the entire table. In dbProjector, one can freely choose to synchronize any parts of the whole object.
- To work with SSDT, one needs to have a full set of monstrous instruments: MSVS, MSSQLSMS, IIS, etc. on the place where you want to deploy the DB. dbProjector is a small distribution easily to install everywhere .NET. can run. If one needs only to install or update the DB, dbProjector offers a separate tool with no installation required, where one can just click Next as in any normal installer for conventional applications.
- SSDT – it is Windows only, and only MS SQL Server. dbProjector easily works on Linux and supports both MS SQL Server and PostgreSQL.
- MS – it is a big bureaucratized machine. If one has something unacceptable in SSDT or wants to add something, you will unlikely influence on it. dbProjector is open to criticism and suggestions
Being formal, let us list some options dbProjector has not:
- Editing objects from IDE. In IDE dbProjector, one can only change a function or a stored field text. It is assumed that all changes of the objects will be made with a detailed DB copy and then selectively imported to the project.
- In SSDT, it is possible, for example, to rename a column that will be remembered in some files with the extension “.refactorlog” in XML format. It is a very controversial tool because later on it will be almost impossible to understand what happens in this XML sheet. More easily is, in such cases, to write Pre-Deployment and Post-Deployment scripts by oneself as it is done in dbProjector.
You can write more advantages and disadvantages on dbProjector forum.
Q. When I deploy the PostgreSQL project, user’s grants are rolled up before creating the user itself and deploy transaction is canceled, WTF?
A. A short answer: One should create roles; move all grants to the roles. The users must be included in the roles only.
Yes, PG users are simply alias from the role (here one could make a few critical remarks about the PG grant system but let us drop it). However, if used carelessly, one likely rubs the wrong way eventually. It seems to me that it is just a box of dynamite to find in a year of development that I cannot delete some irrelevant users because they have some disjointed grants, or worse, some objects in the DB. It seems to me that a good tool should warn against similar actions.
Q. How to deploy without deleting the objects created by users?
A. One should add such objects to the exclusion: «Project → Project Properties → Ignore». One can add a mask.