TAPI for TE_TEMPLATE

[MikeKutz]

Business Requirements

A template developer may wish to share her/his work with the world. (eg GitHub project)

If the templates are stored within a Database Object, then the developer only needs to provide the CREATE script(s) for that Database Object in order to share their work with the world.

But, what happens if the set of templates are stored in the TE_TEMPLATES table?

XML format is a standard generic file format. XML Documents can support large (>32k) templates. And. within Oracle, XML Documents are easy to create (XML SQL) and easy to parse (XMLTable()) since 9i.

For these reasons, I suggest that an XML Document should be used as the file format to transfer templates from one system to another.

A set of TAPI functions/procedures should provide the methods necessary to create, parse, and validate the XML Document into/from rows in the TE_TEMPLATES table.

Technical Requirements

For future proofing development of TE_TEMPLATES, one should be able to build this TAPI using tePLSQL template(s).

Funtionality needed

• import XML of templates (p_xml in XMLType)
• export a series of templates based on a search parameter (eg p_name_like) return XMLType.
• simplified "get template"
  • TEPLSQL should call this function for templates expected to be in TE_TEMPLATES
• validate XML
• exceptions
  • invalid_XML
  • template_not_found
• XSD for validating the XML
  • XSD should be available as a file
  • XSD should also be a constant of the package.
  • XSD should be generated by the template(s) that create the package.

The developer should provide

• code for package (spec and body).
• XSD
• XML of template(s) used for building the TAPI
• modification of TEPLSQL package that calls this TAPI.
• optionally, an oddgen generator for building this TAPI.

others features should be discussed.

EDIT Notes added Business Requirements section and flagged the original as Technical Requirements

[osalvador]

Hi Mike,

Would be nice to send me examples, I cant't understand everything you want to get to do.

Thanks!

[MikeKutz]

High level overview: A method to transport a collection of templates between independent systems is needed. Example sharing of collections of templates

• between various users
• a shared repository on GitHub
• between "template and fragment editor" and TE_TEMPLATES table,

I'm thinking that this should be a single XML file. (XMLType should be used within the code.)

Since this would be XML, a method to "validate the input" is needed. This is why an XSD is needed.

Code for creating the XSD from a Table spec using PL/SQL can easily be found. A person should be able to turn that code into a template.

I'll post more information later. I hope this helps.

Thanks

MK

[MikeKutz]

A cleaner way to write the requirements for the Package (TAPI)

suggested interface name	Purpose
get_template_by_table	Since this is a TAPI, the code from TEPLSQL line (140-143) could be moved to this package (but keep the RECORD type in TEPLSQL). TEPLSQL could then call this function.
export_templates	Creates an XML containing a collection of templates that meet a specific criteria (eg NAME LIKE '/mytapi/%' )
import_templates	Loads an XML that contains a colletion of templates into the table TE_TEMPLATES. (possible feature) This should throw an error if a matching template name already exists. (can be overridden)
validate_xml	validates the XML against the XSD
the XSD	(most likely) a CONSTANT that represents the XSD for TE_TEMPLATES. All XML must conform to this. This is just one method to store the XSD in a package.
other stuff	support features (constants, functions, exceptions, etc.) as needed

PL/SQL code for creating an XSD from a Table can be found on the internet. That code should be written as a tePLSQL template.

MK

[MikeKutz]

I updated my original post to include a Business Requirements section. Hopefully, that helps explain the reason for this TAPI/Enhancement Request.

As I work on this, I can't decide if these should be a separate Package or more procedures/function within the main tePLSQL Package.

FYI - I've got all the import methods working.

[osalvador]

Hi @MikeKutz

I think that the improvements that you pose are outside the scope of a template engine.

They are great ideas, but I think it is better to do few things, but to do them very well, with great performance, than to try to cover many features and become a big package that complicates the distribution.

If you finally make a separate package, it could be added as an extension to the engine itself, which is optionally installable.

Thanks for your help and dedication.

[MikeKutz]

The coding for a separate package just needs a few tweaks (mostly formatting)... if only #28 was completed.

What I really need is an appropriate name

1. TE_TEMPLATES_API

2. teTEMPLATE

3. something else.

Right now, the package's name is (1) but I can easily change that.

[osalvador]

Hi @MikeKutz,

I think the most important thing is that the name of the package says that it is an API of the table TE_TEMPLATES, so as long as you have these two references it will be correct.

te_templates_api is correct, teTemplateApi would also be correct.

[MikeKutz]

I just added the pks/pkb for TE_TEMPLATES_API. Since this is optional, I should probably create a TE_TEMPLATS_API.md "readme" file.

Don't pull yet. I haven't fully tested the EXPORT features. (and I haven't figured out how to recall a Pull request).

But, feel free to look over the code and give pointers.

[peter-genesys]

Hi @MikeKutz and @osalvador

I use tePLSQL with ODDGEN. I am attaching a few examples of how i use tePLSQL incase it may be useful to you. My code is modified from code by Philipp Salvisberg. My examples include my TAPI, View generator, Instead of Trigger generator, Table Id Trigger generator I've tried to modularise the templates by including reusable cursors package spec teplsql_lib.

Cheers Peter Burgess.

tapis.zip

[MikeKutz]

@peter-genesys

Please post URL to the GitHub project where this code exists. One version of WinZip says your file is corrupted. Also, I have a habit of not trusting posted file. And... I can't download them while at work.

Additionally, a discussion of "template coding techniques" would be better off in a different thread. (I'm just trying to keep this one limited to the TE_TEMPLATES_API package.)

If you want to try something out, my TE_TEMPLATES_API branch in my fork of tePLSQL contains the Package (spec and body) for the TE_TEMPLATES_API package. ( https://github.com/MikeKutz/tePLSQL/tree/te_templates_api )

You can use that package to export out an XML file (of your templates) that I can import (and review).

Make sure you include the resulting XML in your GitHub Repository. If you run into bugs, please tell me. (in this thread would be OK with me.)

Cheers!

MK