Comments (13)
I recall hearing "TEXT" is not in ISO SQL. Is NAME a std data type? Also is session_user in the std? Queries currently use session_user sometimes, current_user other times. Also TEXT and NAME are sometimes upper case, sometimes lower case
Sean Murthy (smurthys)
from classdb.
TEXT is Postgres' implementation of the standard CLOB type (Essentially VARCHAR without a max length). NAME is an internal type used by the pg_catalog, I will rewrite the queries to remove it.
Steven Rollo (srrollo)
from classdb.
Need to carefully review all data types (especially TEXT
) and functions used: not saying there are errors, but it is important in the education context that every effort be made to use the correct types and queries. For example, should Student.lastDDLObject
be TEXT
?
Also, every non-standard use should be clearly documented in code.
from classdb.
pg_event_trigger_ddl_commands()
returns TEXT
for both object_identity
(user for Student.lastDDLObject
) and command_tag
(used for Student.lastDDLOperation
). source.
However, since object_identity
returns a string made up of two identifiers, (schema.objname), and Postgres objects have a maximum length of 63 characters source, we can calculate that the maximum length of a string in Student.lastDDLObject
will be 131 (63x2 for names + 2x2 for quotes + 1 for .)
Likewise, the longest statement tag that can trigger is 32 characters long source, so we can assume that the maximum length for Student.lastDDLOperation
will be 32 characters.
I think we can replace TEXT
in both of these fields with VARCHAR
of appropriate length.
from classdb.
Here is a list of non-standard types used and some thoughts on replacing them:
prepareClassDB.sql
-Student.lastDDLObject TEXT
: Can be replaced with VARCHAR(131)
-Student.lastDDLOperation TEXT
: Can be replaced with VARCHAR(32)
-listUserConnectionsReturn.applicationName TEXT
: This lists a string from the client identifying itself (ie. dBeaver sends DBeaver 4.0.7 - Main). I think this could safely be replaced with VARCHAR(63)
, since application_name
uses NAME
internally.
-killConnection INT4
: INT4
is the input type for pg_terminate_backend
, but it is probably safe to just use INT
.
prepareUserLogging.sql
-classdb.postgresLog
: uses mostly non-standard types, but I think this is OK since it is used for a completely Postgres specific feature, and the schema is recommended in the documentation. I also have a link to the documentation as a comment above the table.
-logPath TEXT
: This will hold the full path for the log file. We can probably make this VARCHAR(4096)
, which seems to be the longest max path length for most Linux systems. Windows' max path length is only 260 characters.
-objID TEXT
: This is the same as Student.lastDDLObject
, so VARCHAR(63)
metaFunctions.sql
-All uses of TEXT
in this script refer to fields that are stored as NAME
internally, so they can be replaced with VARCHAR(63)
from classdb.
With Student.lastDDL*
let us safely add more space to take into account quoted identifiers, future changes, etc. I will make the changes to Student
table since I am editing that file. I am thinking 256 and 63 for the two fields respectively.
I agree we leave the schema of classdb.postgres
as is.
objID
: I assume you mean VARCHAR(131)
which is what you show for Student.lastDDLObject
.
from classdb.
Yes, VARCHAR(131)
for objID
is what I meant
from classdb.
In metaFunctions.sql
, replace use of SETOF
with TABLE
for return type because "TABLE is more portable".
Using TABLE
for return type also eliminates the need to define user-defined types.
from classdb.
One downside to TABLE
notation is that the return type columns have to be explicitly enumerated for each function instead of referring to a user defined type. This results in some repeated code since each of the metaFunctions has an overload with the same return type. One way to refactor this would be to create one version of each function with appropriate default parameters, so the column definition list is not repeated.
Another place where SETOF
can be replaced is classdb.listUserConnections
in prepareClassDB.sql
from classdb.
I decided to go with the single function solution in 8479bc8. So far, I have only updated metaFunctions.sql. I will edit classdb.listUserConnections
after the current PR.
from classdb.
To keep the data types consistent, we should update the userName parameters in prepareClassDB and prepareClassServer to VARCHAR(63) instead of VARCHAR(50), in order to match the new changed types in PR #55.
from classdb.
Some more instances are located here
classdb.listUserConnectionsReturn
should be merged intoclassdb.listUserConnections
using aTABLE
return typeclassdb.killUserConnections
should returnTABLE(success BOOLEAN)
from classdb.
These instances were addressed in adf6656.
from classdb.
Related Issues (20)
- Add session id to table DDLActivity (N) HOT 2
- Test scripts are not organized as components (M) HOT 1
- SET SESSION AUTHORIZATION breaks ClassDB.getSessionID() (W) HOT 1
- Not all object types are listed by listOwnedObjects (M)
- Automate removal of superuser-owned objects (N)
- Permit users to create schemas (N)
- Server-version comparison functions are too long to be part of another script (E)
- Unit tests for shared ownership of team objects are insufficient (M)
- Guard uses of ADD COLUMN IF NOT EXISTS; changed in pg9.6 (N)
- The documentation for function ClassDB.importConnectionLog is incomplete (M)
- Server version comparison uses incorrect preset option (W) HOT 1
- DDL activity logging inserts a duplicate entry for some ALTER TABLE statments (WE)
- Server-version comparers are not marked immutable (M) HOT 1
- Guard uses of DROP OWNED: changed in pg9.5 (N) HOT 1
- Incorrect script filename used for server initialization (W)
- Dropping a user as student from a DB removes the user from student role in all DBs on the server HOT 6
- Users known in one ClassDB database are able to log in to all ClassDB databases on the same server (W) HOT 1
- Parameter initialPwd in function createRole is probably unnecessary (E)
- Roles with login are not granted login privilege HOT 2
- Connection log import fails on Postgres 13 (W) HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from classdb.