| Using Created_Date, Revised_Date [message #1281] |
Fri, 07 March 2008 03:45  |
|
|
Sir Tony,
I am trying to look for literature and reading on how to use the fields created_date, created_user, revised_date and revised_user. So far, I encountered your past suggestion of setting the auto_update field in the data dictionary for revised_date.
The four fields seem to be automatically added to tables that are defined in the framework. How do I choose not to have them? Like in your class_lesson and student_lesson tables (Classroom Sample System).
Is my understanding correct that if I have them marked as auto_updated (all four fields), then when the framework creates or inserts a new record, created_date and created_user will acquire the corresponding value?
I tried searching the fields across the framework but only found them existing in *.dict.inc.
Just asking for a little enlightenment on the matter. Thanks
Melvin
|
|
|
|
| Re: Using Created_Date, Revised_Date [message #1282 is a reply to message #1281] |
Fri, 07 March 2008 04:54  |
AJM
Messages: 2368
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
Firstly, no database fields are automatically added by the framework. The data dictionary 'import' facility will only ever import the details of fields (columns) which have been defined in the database schema.
Most of the tables that I create always have the following columns at the end:
`created_date` datetime NOT NULL default '2000-01-01 00:00:00',
`created_user` varchar(16) NOT NULL default 'UNKNOWN',
`revised_date` datetime default NULL,
`revised_user` varchar(16) default NULL,
I have been using these for several decades as a first level auditing facility, and the habit has stuck. It is also a useful way of being able to isolate recent inserts or updates with simple SQL queries.
After these columns have been imported into the data dictionary their details need to be updated as follows:
- for all four columns set NOEDIT and NOSEARCH.
- for 'created_date/user' set AUTO_INSERT.
- for 'revised_date/user' set AUTO_UPDATE.
You can either do this manually using the online screen, or you can run script 'radicore\dict\sql\update_created_date.sql'.
This means that the AUTO_INSERT columns will only be set by the framework on an INSERT, and the AUTO_UPDATE columns will only be set on an UPDATE. The values inserted will be as follows:
- string fields = $_SESSION['logon_user_id']
- date fields = current date
- time fields = current time
- datetime fields = current date+time
In addition none of these fields will be visible on INSERT or SEARCH screens, and will be display-only in all others.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Using Created_Date, Revised_Date [message #1283 is a reply to message #1281] |
Tue, 11 March 2008 21:55 |
|
|
Thanks Sir Tony, It was a big help again as usual.
But can I add an inquiry about AUTO-UPDATE and AUTO-INSERT?
Are they useful for fields other than the Created/Revised group? Can they be used for non-date fields as well?
Thanks again
|
|
|
|
|
|
| Re: Using Created_Date, Revised_Date [message #4953 is a reply to message #1284] |
Sat, 29 August 2015 23:36 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
Tony,
Tacking on here regarding created_user, and revised_user...
I have an existing data set where all the tables have:
created_at timestamp without time zone
created_by integer
updated_at timestamp without time zone
updated_by integer
I am attempting to design the DB such that it will work with two web app frameworks simultaneously--RADICORE and another framework (whose name shall not be spoken, but rhymes with "Ache" PHP). This other framework uses integers for the 'users' table. If I were only using RADICORE, then I could change the field names and change the datatypes to varchar(16). And I would do a SQL query to CAST all of the existing users records from integer into strings (once as a batch). But to work with both in an ongoing way...
(1) Is it possible to make this work? Will the RADICORE detect that the users field in my DB is an integer and attempt to CAST from the varchar value that is stored in MNU_users (or whichever table contains the RADICORE users)?
(2) Do the field names need to be named exactly the same as you have them--'created_user', 'created_date', 'revised_user', 'revised_date'--in order to work with RADICORE's auto_insert, and auto_update?
[Updated on: Sat, 29 August 2015 23:38] Report message to a moderator |
|
|
|
| Re: Using Created_Date, Revised_Date [message #4954 is a reply to message #4953] |
Sun, 30 August 2015 05:27 |
AJM
Messages: 2368
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
(1) On all the tables in the MNU_USER database the user_id column is expected to be VARCHAR(16), so if you wish to populate it with something which looks like a number it will be treated as a string and not a number. This means that in any SQL query the value for user_id must be enclosed in single quotes.
(2) You do NOT have to create those fields with the exact names. I deliberately chose to implement the AUTO_INSERT and AUTO_UPDATE features by using settings in the dictionary and not by the field names.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: Using Created_Date, Revised_Date [message #4958 is a reply to message #4957] |
Mon, 31 August 2015 04:53 |
AJM
Messages: 2368
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
When you supply a user_id to RADICORE it treats it as a string so will always enclose any value in quotes, thus allowing any combination of numeric and non-numeric characters. Are you trying to access RADICORE's tables through this rival framework, or does this rival framework have its own set of tables where the user_id is an integer and you are trying to link the two tables?
If you wish to use a pure integer value to link a RADICORE user to an external system there is a field called PARTY_ID which I added to the MNU_USER table years ago. I did this to link a user to my version of Len Silverston's PARTY database where party_id is an auto incrementing integer.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Using Created_Date, Revised_Date [message #4959 is a reply to message #4958] |
Mon, 31 August 2015 11:45 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
The rival framework has its own set of tables where the users' PK is an integer.
A work-around solution to meet my needs, though an ugly one, could be to have two sets of fields in each table for each framework.
I will consider the PARTY_ID option.
Thanks again.
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
