You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
optional: find the csproj, check the additional file exists; if not, add it
Where dbschema.txt could be something like (is there prior art here? plain text?):
(note on order: objects are invariant alphabetical; columns/parameters/etc are positional)
default schema dbo
server version 16.0.1000.6
table dbo.Foo
column Id int notnull identity readonly
column Name string
column Label string computed readonly
table someSchema.Bar
column Id int notnull identity readonly
view someView
column Id notnull null
...
we would then load the dbschema.txt and use it to validate commands at build time, without needing constant SQL access. Schema should be implied via "default schema" when omitted.
Possible checks:
object exists
column exists (including views and functions)
capitalization
appropriate data types
treat views as immutable
don't allow update/insert to readonly columns
correct parameters on functions
correct function kind (scalar vs tabular UDF)
system functions vs server version
stored procedure parameters (columns are probably a stretch, unless we get the sp_helptext-etc source and parse that too?)
server version syntax rules (currently we always assume "latest")
Rewriting the db schema would be a case of re-running the tool, with the changes visible in source control.
Note: advanced SQL analysis is currently limited to SQL Server; we should probably at least not assume that when connecting (use the provider model), but... whatever works.
The text was updated successfully, but these errors were encountered:
Sort the table names, and the column names before writing file so diff/merge more likely to work in a senible way. The file should always be 100% same if database is logically the same.
Also think of how a build server could run tool to check that file is the same when database creation script is run then checked in file. But leave details of implementation to the users build management team.
context: https://twitter.com/IanStockport/status/1702313925810024716
imagine a "dotnet tool":
which:
conn-key
dbschema.txt
Where
dbschema.txt
could be something like (is there prior art here? plain text?):(note on order: objects are invariant alphabetical; columns/parameters/etc are positional)
we would then load the
dbschema.txt
and use it to validate commands at build time, without needing constant SQL access. Schema should be implied via "default schema" when omitted.Possible checks:
sp_helptext
-etc source and parse that too?)Rewriting the db schema would be a case of re-running the tool, with the changes visible in source control.
Note: advanced SQL analysis is currently limited to SQL Server; we should probably at least not assume that when connecting (use the provider model), but... whatever works.
The text was updated successfully, but these errors were encountered: