Comments (21)
Hi jgebal,
we already thought about default values for the API. As you suggested, it would be very usefull for CREATE operations. For other operations like UPDATE or CREATE_OR_UPDATE would be "risky" to use default values. Instead of a cursor, we have the idea to take the default value from the table column directly, I think it's the simplest way ;-)
So in our next version, default values will be added for the CREATE operations.
Best regards
André
from table-api-generator.
Hi Jacek,
I like your idea and as André mentioned we already thought about it - but not so deep. We wanted only to provide the table defaults - but additional custom defaults would be also great.
I was thinking a little bit about your idea and I have the following comments/ideas:
To be backward compatible I don't want to change the default create_row methods (I use it already in some projects and breaking backward compatibility would be generate much work - in my projects and maybe also in some unknown projects of other users).
How about two new functions per generated API? (@aborngra what do you think?)
- create_row_table_defaults (with the defaults defined for the table, thats what we thought about defaults)
- create_row_custom_defaults (the one you want to have and I also :-)
There is one problem: We save the API creation parameters in the API spec to be able to recreate the API without any given parameter, that is also the reason to have the parameter c_reuse_existing_api_params
with the default value true
. We use XML coded parameters in the spec to be able to read the parameters even the package is invalid because of table changes or other reasons. So the question is: What do you think - should the parameter for the custom defaults a ref cursor and we convert it to XML for the spec or it is better that the user defines already a XML for the default values and we provide may be a convenient function to convert a ref cursor into XML?
One comment about our method names: We were inspired by the CRUD acronym for Create, Read, Update and Delete - that is the reason to use create_row instead of insert_xxx or ins_xxx.
And yes, there is room for improvements - thanks for all your suggestions and ideas :-)
Best regards
Ottmar
from table-api-generator.
Hi Guys,
Thanks for all the hard work you've put into the project.
I love the CRUD API approach and the way you parametrised the generator.
Adding default values should not be breaking change I think as, currently whenever you call the insert API, you need to specify all attributes.
The only way it would be considered a breaking change is when you modify table. The code using API with defaults would still work (After regeneration) while the code using API without defaults would not.
Maybe the defautls/no defaults should be parameter of generation?
It seems like two different use-cases.
In general more parameters give more control, just the default behavior should be well defined :)
We're thinking of a very specific use-case for table API's (as helpers for unit tests).
I might do a demo project at some pint that would show how it is beneficial for unit testing.
Thanks
Jacek
from table-api-generator.
Hi Jacek,
are you constantly working? ;-)
You are right: The defaults are NOT invalidating the API on table changes - and this is a nice thing - to be remembered to recreate the APIs when table changes occur. For that I (hopefully also André) don't want to change the existing create_row logic - not to have default values was a design decision at our project start.
I like the idea to have two additional create_row methods like mentioned before. With this it is also clear, which method does what regarding the defaults. And yes, we need parameters for that - thats the reason for my question before: which should be the parameter type for the creation of the create_row_custom_defaults
- a ref cursor or xml? What do you think is better to use (please reread my previous comment)
Suggestion:
- function:
create_row_table_defaults
(parameter:p_enable_table_defaults
boolean default false) - function:
create_row_custom_defaults
(parameter:p_custom_table_defaults
xmltype or sys_refcursor? default null, we need to save the defaults in the package spec for reuse/recreating)
Best regards
Ottmar
from table-api-generator.
Oh, I think it should be:
- function:
create_row_column_defaults
(parameter:p_enable_column_defaults
boolean default false) - function:
create_row_custom_defaults
(parameter:p_custom_column_defaults
xmltype or sys_refcursor? default null
Regards
Ottmar
from table-api-generator.
I'm wondering it having 3 different API entry pints to create a row isn't over-compilcating stuff too much.
Ideally, one API should be enough. I wonder if there would be a scenario where you would like API to have all 3 and the consumers would use all three. Wouldn't it be enough to have just one (or two) and it's behavior would differ depending on how it's generated?
Also.
calling api: a_long_table_name_goes_here_api.create_row_column_defaults
is a lot of typing.
Could we just say that:
create_row
creates row with given columnscreate_a_row
creates a row with default values (unless overriden)
I like short, easy to remember yet meaningful names.
I think that cursor could be better than XML as not all developers find XML easy enough.
Actually I was thinking of deriving the values from table itself (first row?) as a default behavior if generating API with defaults. The cursor could be an optional parameter.
from table-api-generator.
Hi Jacek,
the parameter based entry point was to be able to direct assign APEX page items. The row type based for cases you want to change only some columns because it saves you much code and the procedure is useful if you don't care about a return value.
May be the procedure could be omitted and the column based could be parameterized? I have to change much APEX calls without the column based version. At the end the code is generated and you pick what you need. But I am open for new ideas... I can remember that André was also thinking about to have only row based functions. I have to discuss this with him.
Regarding the names - do you have an editor with autocompletion? ;-)
I find a clear long name better then a short one - but this is may be a personal preference. For users who only review the code the long name is definitely better. For a developer who works the whole day with the code a short name is better.
Is the longer name really an issue? Also I miss the distinction between the defaults defined for the table columns and the custom defaults. Do you only want to have the custom defaults?
What about create_row_defaults
and create_row_custom_defaults
? It's a bit shorter for the column defaults and clear enough regarding the distinction.
Best regards
Ottmar
from table-api-generator.
Hmmm...
Seems like it would be great to have a chat typing takes so much time.
Do you guys are familiar with https://appear.in
We could schedule a meeting and chave a wideo conf call in a random meeting room.
I'd like to know a bit more about the problems you're solving with the generator and get to know if we actually aim towards something similar or are those two separate classes of problems that would require two different solutions.
It would be bad if you would try to make your library do too much it the "too much" is not simple generalization of specific problem.
Would you be willing to have a chat about it?
from table-api-generator.
From my side no problem, best time for me would be something around 20:00 CET in the evening the next days. @aborngra: what do you think?
But you mentioned that you are currently traveling. So enjoy your time first and then we can have a chat.
from table-api-generator.
Hi Jacek,
I discussed the open issues with André - as far as you agree with the following sum up wee don't need a chat.
We will see how long this will take - it is a free time project. We will keep you updated and create a new branch for the changes, so that you will be able to test before the official release.
Best regards (also from André)
Ottmar
Table API Generator - Next Steps
After some rethinking: All listed improvements should be backward compatible since we have more than 20 known and some unknown applications using the current functionality. For special use cases a developer can create a wrapper to set his personal defaults.
New Functionality
- New parameter
p_api_name varchar2 default null
to control API name- Extended substitutions for the SQL Developer multi API generation like
#TABLE_NAME_17#
or better#TABLE_NAME_4_20#
also for the parameterp_sequence_name
- Extended substitutions for the SQL Developer multi API generation like
- New method
read_a_row
without any parameter to read the first record from the table (one cursor fetch) - New method
create_a_row
with default parameters- If new parameter
p_column_defaults xmltype default null
is null then the table column defaults are used, otherwise the provided custom defaults - XMLTYPE because we need to save this parameter in the API spec for parameterless recreation and also this gives us the possibility to define some dynamic content like in the example below (the XML is very simple and since JSON is commonly used these days this xml should not be a problem for a developer - a helper function to convert a ref_curser into this xml would be nice)
- Additional reason is the SQL Developer integration - there we have only a reduced set of input types
- If new parameter
<defaults
employee_id="employees_seq.nextval"
first_name="'Chuck'"
last_name="'Norris'"
email="substr(sys_guid(),1,25)"
phone_number="'123'"
hire_date="to_date('2017-05-27','YYYY-MM-DD')"
job_id="'FI_MGR'"
salary="123"
commission_pct="0.01"
manager_id="null"
department_id="null"
/>
- New parameter
enable_getter_and_setter boolean default true
- New parameter
return_row_instead_of_pk boolean default false
- New parameter
enable_parameter_prefixes boolean default true
Improvements
- When using
p_col_prefix_in_method_names => false
then do NOT throw an exception when no unique column prefix is found for a table? To be discussed - Create tests with Travis and SonarQube
- Something else?
- ...
from table-api-generator.
Hi @ogobrecht,
I didn't fully read through your code and I don't fully understand how it works (just know what the outcomes are ;) )
Keeping backward compatibility with existing solution seems like a good plan.
If you will create a separate branch for new features I might be able to do some skeleton for CI/CD on Travis and prepare basic unit testing for generator.
Having full suite of unit tests will definitely make it easier for you to work safely with the code,
Regards
Jacek
from table-api-generator.
Hi Jacek, hi André,
here the current status from today:
Done
- New Branch
0.5
- New parameter
p_api_name varchar2 default null
to control API name- Extended substitutions for the SQL Developer multi API generation like
#TABLE_NAME_17#
or better#TABLE_NAME_4_20#
also for the parameterp_sequence_name
- Additional Info:
#TABLE_NAME_17#
is treated as substr(1, 17)#TABLE_NAME_4_20#
is treated as substr(4, 20)- Negative positions also possible like
#TABLE_NAME_-20_20#
(length can NOT be ommitted because of use case with one number and backward compatibility, we had only#TABLE_NAME_26#
,#TABLE_NAME_28#
...)
- Extended substitutions for the SQL Developer multi API generation like
- New method
read_a_row
without any parameter to read the first record from the table (one cursor fetch) - New parameter
enable_getter_and_setter boolean default true
- New parameter
enable_parameter_prefixes boolean default true
Work In Progress
- New parameter
return_row_instead_of_pk boolean default false
- Parameter is already existing, but not working
To Do
- New method
create_a_row
with default parameters- If new parameter
p_column_defaults xmltype default null
is null then the table column defaults are used, otherwise the provided custom defaults - XMLTYPE because we need to save this parameter in the API spec for parameterless recreation and also this gives us the possibility to define some dynamic content like in the example below (the XML is very simple and since JSON is commonly used these days this xml should not be a problem for a developer - a helper function to convert a ref_curser into this xml would be nice)
- Additional reason is the SQL Developer integration - there we have only a reduced set of input types
- If new parameter
- When using
p_col_prefix_in_method_names => false
then do NOT throw an exception when no unique column prefix is found for a table? To be discussed - Align oddgen wrapper package for SQL Developer integration
- Create tests with Travis and SonarQube
- Something else?
- ...
from table-api-generator.
Hi Jacek,
the new parameter return_row_instead_of_pk boolean default false
is now working.
The last missing feature is now "new method create_a_row with default parameters".
Best regards
Ottmar
from table-api-generator.
Hi Jacek, hi André
now we are roughly feature complete - it was a short weekend ;-)
@jgebal: Please be so kind and do your first tests and let us know if you can live with current implementation of the column defaults. The two new helper functions to retrieve automatically the column_defaults are completely independent of the package and can be used as a base for own implementations and specific business needs.
The next steps from our side would be tests and documentation. Thank you again for your first test implementation. Could be, that we have to ask again for a little help when we start to implement more specific tests.
Best regards
Ottmar
Done - see also README.md in branch 0.5
- New parameter
enable_proc_with_out_params boolean default true
: these procedures are created mainly for APEX to directly bind page items to the API without any other code - since not anyone is using APEX this can now be disabled - New method
create_a_row
with default parameters- If new parameter
p_column_defaults xmltype default null
is null then the table column defaults are used, otherwise the provided custom defaults - XMLTYPE because we need to save this parameter in the API spec for parameterless recreation and also this gives us the possibility to define some dynamic content like in the example below (the XML is very simple and since JSON is commonly used these days this xml should not be a problem for a developer
- Additional reason is the SQL Developer integration - there we have only a reduced set of input types
- Two new helper functions to retrieve automatically useful defaults:
- om_tapigen.util_get_custom_col_defaults
- om_tapigen.util_table_row_to_xml
- The second one is used in the first one and the first one is a incomplete first implementation which could be used as a template for own business needs - please see also the spec and body for implementatione details; For the table EMPLOYEES the result works fine - see usage example below;
- If new parameter
<defaults>
<item><col>LAST_NAME</col><val>'Atkinson'</val></item>
...
</defaults>
-- usage of the helper om_tapigen.util_get_custom_col_defaults:
BEGIN
om_tapigen.compile_api (
p_table_name => 'EMPLOYEES',
p_reuse_existing_api_params => FALSE,
p_col_prefix_in_method_names => TRUE,
p_enable_insertion_of_rows => TRUE,
p_enable_update_of_rows => TRUE,
p_enable_deletion_of_rows => FALSE,
p_enable_generic_change_log => TRUE,
p_enable_dml_view => TRUE,
p_sequence_name => 'EMPLOYEES_SEQ',
p_api_name => 'EMPLOYEES_API',
p_enable_getter_and_setter => FALSE,
p_enable_proc_with_out_params => FALSE,
P_enable_parameter_prefixes => FALSE,
p_return_row_instead_of_pk => TRUE,
p_column_defaults => om_tapigen.util_get_custom_col_defaults ('EMPLOYEES'));
END;
/
-- inspect the result of the helper
SELECT XMLSERIALIZE (
DOCUMENT OM_TAPIGEN.UTIL_GET_CUSTOM_COL_DEFAULTS (
P_TABLE_NAME => 'CAT_FEE_TYPES')
INDENT)
FROM DUAL;
Remaining ToDo's
- When using
p_col_prefix_in_method_names => false
then do NOT throw an exception when no unique column prefix is found for a table? To be discussed - Align oddgen wrapper package for SQL Developer integration
- Update documentation
- Create tests with Travis and SonarQube
- Something else?
- ...
from table-api-generator.
Hi @ogobrecht. Awesome progress in a short period of time. I really appreciate.
I'll have a look at the generator and see how it would fit as a helper functionality for utPLSQL Unit Testing.
It looks like I should start blogging about it once I put all the pieces of the puzzle together :)
I can help with implementation of some unit tests.
The best way to start is to come up with requirements in form of small examples and turn those individual examples into tests.
I can have a look at one of simple functionalities and try to reverse-engineer the requirements and examples out of it.
I cannot commit on any time to deliver as I have my everyday job and utPLSQL project (still in quite active development).
from table-api-generator.
Hi Jacek,
welcome to the club - the API generator is also a free time project of André and me :-)
It takes as long as it takes - no expectations from our side. We have also much to do with the documentation, the SQL Developer integration, the tests with utPLSQL and so on...
I had to fix today the pk column handling in the new method create_a_row - I was too concentrated to solve the issue with the parameter handling for the column defaults that I totally forgot the pk column handling: The pk column parameter defaults now to NULL since the sequence is handled by the create_row method or a trigger or in 12c automatically with the identity column and also configurable with the sequence name parameter.
now you should be able to create a row on the employees table with this simple call: employees_api.create_a_row;
Best regards
Ottmar
from table-api-generator.
Hi @ogobrecht
Few issues found:
- Data is not generated for TIMESTAMP datatype - this is a very common datatype and should be supported
- Data is only generated in the get_a_row function for NOT NULL columns. I have a check constraint on two columns (column_a IS NOT NULL OR column_b IS NOT NULL). The default row is therefore unusable.
- API cannot be generated for other user than the owner of the table, as USER_xxx data dictionary tables are used.
I would suggest:
- support for TIMESTAMP datatype
- generate row with defaults for all columns populated (not only NOT NULL)
- add owner as a parameter for the generator so the API's can be generated for tables in other schemes. The API should be generated into schema owning the table (by default).
Thanks
Jacek
from table-api-generator.
Hi Jacek,
here the changes from the weekend:
- New Parameter
p_owner all_users.username%type default user
to be able to create APIs in other schemas - Rework pipelined table function
view_existing_apis
to be able to find also APIs with names other then<TABLE_NAME>_API
since the API name is now changeable with the parameterp_api_name
- Additional supported data types by the helper functions
util_get_custom_col_defaults
andutil_table_row_to_xml
- now the following data types should working:NUMBER
,INTEGER
,FLOAT
,%CHAR%
,DATE
,TIMESTAMP
and with dummy dataCLOB
,BLOB
,XMLTYPE
- Some small fixes and maintainability changes
I hope it is now working for your use case. The thing with the missing data types was clear. The weekend before I had not enough time and I wanted first to hear from you if the solution with the helper functions is generally ok for you, before I spend more time to improve it.
If someone needs special handling for the own business he/she can grab the functions and implement the special business logic in an additional helper package. I recommend also creating a wrapper function for the standard APIs in a company to be able to create APIs with a short one-liner.
Best regards
Ottmar
from table-api-generator.
Thanks for the update. I will give it a try and let you know.
Best regards
Jacek
from table-api-generator.
Hi Jacek,
the new version 0.5.0 is out - it took a long time...
The relevant parameter for your testing functionality is named p_enable_custom_defaults
- please have a look in the docs for this parameter.
Hope you are able now to use it as expected for your tests. And sorry for the long time it took to the version 0.5.0, the changelog for this version is huge...
We have currently only simple, SQL script based tests implemented. The next steps are utPLSQL tests, set operations and logger integration.
Please let us know any comments or questions and please close this issue, if you think your requirements are met.
Best regards
Ottmar
from table-api-generator.
Hi Jacek,
the new version 0.6.0 is out. I implemented some improvements for the dummy data generation when you set the parameter p_enable_custom_defaults to true (generated methods create_a_row, read_a_row, get_a_row). Please have a look in the parameter docs.
Since we have now bulk processing this might also interesting for you: bulk processing performance (we use here also your requested function get_a_row...)
Please let us know, if we can now close this issue or close it by yourself.
Best regards
Ottmar
from table-api-generator.
Related Issues (20)
- Add returning into all non-blob-columns HOT 2
- Toggle not to use NotNull-Constraints HOT 4
- new Parameters p_enable_col_camel and p_enable_prefic_fnc_prc HOT 1
- Use SYS_GUID instead of sequence HOT 2
- Primary key not returned on create_row when XMLTYPE column is present HOT 4
- Primary key missing from create_row when identity column is used as PK HOT 4
- Add support for multitenant applications HOT 12
- Generate a function named get_default_row when parameter p_enable_column_defaults is set to true HOT 1
- Development branch: When parameter p_enable_column_defaults is set to true the compiled TAPI is sometimes invalid HOT 1
- compile error HOT 6
- Exception Handling HOT 2
- Oracle 23c Free - Install-Script returns ORA-01422 HOT 3
- Tenant column filter is missing from generated views HOT 1
- Specifying shorter table name substitution variables in p_one_to_one_view_name generates corrupted view name HOT 1
- P%COLUMN_MAPPING parameters are case sensitive HOT 1
- Error installing on oracle 23c free HOT 2
- Generating default values for virtual columns HOT 1
- Enhancement: QuickSQL-like validation and/or transformation
- Read row by unique keys lists tenant column as a parameter
- Boolean support Oracle 23c HOT 1
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 table-api-generator.