E119Using ASE as infrastructure for Content Management Solutions • Anupam Singh • Staff Software Engineer • Database Server Technology • firstname.lastname@example.org • Vadiraja Bhatt • Bharat Rane
Summary What is Content? What are the operations on Content? How can ASE be used for these operations?
Overview of Content Management • What is it? • Russell Nakano, in his book on the subject, uses the term content management “to refer to the principles and practices around developing, managing, maintaining, and deploying content in an organization. …Content Management is a discipline that manages the timely, accurate, collaborative, iterative and reproducible development of a web property.  Russell Nakano, Web Content Management: A Collaborative Approach, Addison-Wesley, 2001. Nakano is a co-founder of Interwoven, Inc. • It is distinct from, although related to, content delivery
Overview of Content Management • What are the main components of a CM system? • Content Creation / Editing / Acquisition Subsystem • Workflow Subsystem • Repository Subsystem • Deployment and Operations Management Subsystem
Repository • “provides storage, access, retrieval, indexing, versioning, and configuration management of content. Content includes files, database assets, and structured assets (e.g., XML).”
Sybase Focus on Repository • Areas ASE has been enhanced to better support Enterprise Content Management applications: • More effective management of external data. • Enhanced XML support • Logins • Rationale: • Enterprise Content often spans multiple database and file systems • Implicit links between database content and file system content are unmanaged, can become ‘broken’ • Content often represented in XML format, search operations need to be XML aware • Database is one of several components in Enterprise Content Management configurations
What is Content? Relational Data. Most, if not all, mission critical data is relational. • Relational data has been unbelievably successful • But, there’s a lot of ‘other’ data that is part of an enteprise’s content.
What is content? (contd) Plain Text Content could be in HTML files, Text files, email messages etc. • Text data cannot be mapped to relational schemas. • Text indexing and searching is distinct from relational indexing. • Text data may not be mission critical but it contains a lot of important information for the enterprise.
What is content? (contd) XML documents Many companies are adopting XML as the interchange standard. • XML is the document of record. • XML indexing, searching is distinct from text indexing and relational indexing. • Many companies are adopting XML but their solutions are not effective.
What is content? (contd) Business Logic Application logic works on data from the database. • Application logic was not regarded as content. • But, for data centric business logic, the programs that work on the data are content!
Repository Operations There are certain standard operations with any type of content • STORE • ACCESS • PROCESS
STORE STORE means … • Persist • Index • Manage
ACCESS ACCESS means … • Security • Integrity • Transactions
PROCESS PROCESS means … • Transform • Query • Search
STORE (XML) • SQLXML • Generating XML from SQL • Via DBMS capabilities • Via client-side transformations • XMLSQL • Selective mapping/decomposing of XML to relations • XML Storage • Storing/searching of XML in its original form. • Content stored in DBMS • Content stored in file system • Content stored in foreign/external DBMS
STORE (XML) • XML Storage • XML Query Engine • Enhanced Full-Text Search engine (xml filter) • High performance indexing
STORE (XML) • Support for Storing, Searching and Querying XML documents • Key functionalities: • XQL to query XML documents • Store parsed XML documents in ASE • Query parsed XML documents • Enhanced Full-Text Search is XML-aware
STORE (XML) • Benefits • Simple: Java classes to extract contents • General: extraction can be generalized • Optimized: store once, query many times • Tight integration with SQL language
STORE (XML) • Methods • Parse • Xql.parse() • Uses SAX 2.0 interface • Any SAX 2.0 parser can be used • Returns SybXmlStream object
STORE (XML) ASE XQL Engine Table Applications Store Engine XML Data XML Parser SybXmlStream Formation File CIS
STORE (XML)XML SQL dues id total <FpML> <receiver party> Goldman Sachs </receiver party> <payer party> Barings </payer party> <payment schedule> <amount> 100000 </amount> <currency> dollars <currency> <date> 09-01-2001 </date> <frequency> monthly <frequency> </payment schedule> … </FpML> trades payer id recei id SQL data generator Running in the database receivable id total
SybXmlStream xmlStream = Xql.parse(“<xml>..</xml>”); String result = Xql.query(“/bookstore”, xmlStream); insert XMLtable(xmlcol) values (Xql.parse(“<xml..</xml>”) SybXmlStream benefits Parsed document can be stored in Image column of a Table Memory File through XFS Integration with SQL Inserts, updates, deletes supported Suitable for “store once, query many times” STORE (XML)
STORE (FILES) Support for external files in ASE will ensure an easy and cost-effective way of managing data that is used in e-commerce and typically stored in files. Key Benefits: • Provides referential integrity, access control, coordinated backup and transaction consistency • Integrated with Verity Text Search and XQL support • Sybase Central support
STORE (FILES) • Business Problem: There are many types of unstructured data that has traditionally resided in flat files and outside the scope of a RDBMS. With exploding information content, it has become extremely difficult to maintain the integrity of data in these files. • Current solutions • Impractical: move unstructured data into RDBMS • Costly: Custom software to maintain data integrity
STORE (FILES) • SQL access to file system directories and files create proxy_table <table_name> external directory at “directory pathname” • The proxy table contains the following cols:
STORE (FILES) • To create a directory proxy table use the create proxy_table command: create proxy_table <table_name> external directory at "directory pathname[;R]" Example: create proxy_table D1 external directory at "/work/project"
File System Directory Access (Cont’d) • The directory pathname must reference a file system directory visible to and searchable by the ASE process • A proxy table is created which maps column names to attributes of files that exist within the directory • Recurse through all sub-directories when the ";R"extension is used
STORE (FILES) Command Shell Client SQL Client Internal FSAPI ASE File System System Traps OS Kernel Files
STORE (BUSINESS LOGIC) • ASE Component • Creates the Shared memory segments • Starts and Stops external engines • Services TDS requests from clients • Performs its own thread management • Performs its own memory management • EJB Component • Services EJB requests from the clients • Low level protocol is iiop • Threads managed by the OS • Memory management done by OS
STORE (BUSINESS LOGIC) • Perform system management activities for the EJB Server such as • Starting the EJB Server • Stopping the EJB Server • Determining the status of the EJB Server • Through system stored procedures. • sp_extengine “SYB_EJB”, “start” • sp_extengine “SYB_EJB”, “stop” • sp_extengine “SYB_EJB”, “status”
ACCESS (BUSINESS LOGIC) • Creating a connection Cache • Using in an EJB component • _con = JCM.getCacheByName(”AppConn").getConnection(JCMCache.JCM_WAIT);. • Facilitates faster data transfer through shared memory. • Improved performance with Entity beans
ACCESS (BUSINESS LOGIC) • J2EE Data source • Reusable business components • No proprietary code
EJB Server EJB Server Logins/ Roles S H M Database Server Logins/ Roles Database Server Logins/ Roles ACCESS Conventional multi tier ASE 12.5 EJB server • One set of Logins/Roles Shared by EJB • Maintained in ASE store • J2EE roles are mapped to ASE Roles • Option to manage it separately
ACCESS • Sybase Central interface to map Roles
Key benefits of EJB in ASE • Super Fast Data Access • No network overhead • OS level resource management • CPUs and Memory allocation • Disparate system entities • SQL Performance unaffected • Independent Operations • EJB crash will not affect SQL engine • Any VM in EJB engine
Key benefits of EJB in ASE (contd.) • Highly Secured • Protection from malicious apps • No need for wire level security • Allow fast deployment • Re-usability of Existing components • Unified Systems Management • Maintains one set of Users/Roles in ASE • Single Server management interface
ACCESS • LDAP • Public Key Infrastructure & Secure Socket Layer • Row level Security ASE 12.5 Directory & Security
ACCESS • Business Need: Cost-effectively manage and authenticate users of ASE applications and have a unified way of storing ASE information. • Current solutions: • ASE information is stored in interfaces file whose format is different for different platforms. Manageability issue for customers with hundreds of servers on different platforms • Application information is stored both in ASE and an LDAP server: issues of synchronization and cost.
ACCESS Integrated directory services with ASE will ensure cost-effective management of client and server information. Key Benefits: • Server information that is spread in multiple interfaces files can potentially be available from a single ASE/LDAP server • Instant synchronization of ASE and LDAP data, obviating the need for explicit transfer and synchronization of data between the two stores
A 3rd party LDAP server will be used A mechanism will be supported to publish selected data from ASE to LDAP server ACCESS JNDI/ TSQL LDAP API LDAP API ASE LDAP
ACCESS • Business need: With explosion of e-business, there is a critical need for security to ensure that only valid and trusted users are able to access data securely in ASE. • Current solution: • No communication level security
ACCESS New security services in ASE will ensure access control and secure transactions over the Internet. Key Benefits: • Communication level security using Secure Socket Layer protocol • Industry standard Public Key Infrastructure based security using a Certificate Authority for authentication, encryption and end-to-end security • Row level security for access control
ACCESS • SSL v 3.0.7 integrated with Ctlib and ASE • Support digital certificates from CA like Entrust and Verisign • Row Level security • uses user defined rules for constraints • powerful constraints can be built using Java • association done at login time
ACCESS • Row level security sp_addtype typeA, int create access rule ruleA as @col = suser_id() sp_bindrule ruleA, typeA create table tableA (c1 typeA, c2 int, …) Running select returns only those rows where value of c1 matches suser_id()
PROCESS (XML) • SQL XML • Sample java classes • Leverage java in the server • XML XML • Run XQL queries within SQL • Run XQL queries in client
PROCESS (XML XML) Articles Table <News> <article> <company> Sybase </company> … <symbol> SY </symbol> <headline> stock goes through the roof </headline> <text> Breaking news … </text> </article> … </News> <News> <article> <company> Sybase </company> … <symbol> IBM </symbol> <headline> announces <symbol> INFX </symbol> takeover </headline> <text> Breaking news … </text> </article> … </News> Personalization For SY
PROCESS (XML) • Key benefits: • Extension of XPath (which is W3C standard) • Path based language • Powerful constructs • Predicate • Grouping • Set operation • Simple • /bookstore/book[author/first-name = ‘Joe’]/title • To query semi-structured/hierarchical data