Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » Using Created_Date, Revised_Date
Using Created_Date, Revised_Date [message #1281] Fri, 07 March 2008 03:45 Go to next message
melvinsdb is currently offline  melvinsdb
Messages: 16
Registered: November 2006
Location: Philippines
Junior Member

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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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.


Re: Using Created_Date, Revised_Date [message #1283 is a reply to message #1281] Tue, 11 March 2008 21:55 Go to previous messageGo to next message
melvinsdb is currently offline  melvinsdb
Messages: 16
Registered: November 2006
Location: Philippines
Junior Member

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 #1284 is a reply to message #1283] Wed, 12 March 2008 05:38 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
It is possible to use AUTO_INSERT and AUTO_UPDATE with any column name, but as I have already stated the only values that the framework can insert are either date/time or user_id. It is not possible to specify other values. It is therefore illogical to use these on a numeric field, for example, as there is no numeric value which the framework can use.

Re: Using Created_Date, Revised_Date [message #4953 is a reply to message #1284] Sat, 29 August 2015 23:36 Go to previous messageGo to next message
rafs is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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.


Re: Using Created_Date, Revised_Date [message #4957 is a reply to message #4954] Sun, 30 August 2015 08:16 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Regarding (1) So, yes this is exactly what I was thinking--to use '123' (strings) for my user_ids in RDC instead of proper names, and hope that RDC might try to CAST or convert automagically. But, you think the answer is ...

No, it should not work, as is. But how many changes to RDC do you think would be required to add code to catch UPDATES/INSERTS to these specific fields, by hacking the DAO?
Re: Using Created_Date, Revised_Date [message #4958 is a reply to message #4957] Mon, 31 August 2015 04:53 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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.


Re: Using Created_Date, Revised_Date [message #4959 is a reply to message #4958] Mon, 31 August 2015 11:45 Go to previous message
rafs is currently offline  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.
Previous Topic: Replace detail screen label
Next Topic: How do I display the "Parent Field" (s) of the linked entities instead of their Primary Keys in a LI
Goto Forum:
  


Current Time: Sat Jun 12 07:49:43 EDT 2021

Total time taken to generate the page: 0.01473 seconds