E119 Using ASE as infrastructure for Content Management Solutions

1. E119Using ASE as infrastructure for Content Management Solutions • Anupam Singh • Staff Software Engineer • Database Server Technology • singha@sybase.com • Vadiraja Bhatt • Bharat Rane

2. Summary What is Content? What are the operations on Content? How can ASE be used for these operations?

3. Overview of Content Management • What is it? • Russell Nakano, in his book on the subject[1], 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. [1] 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

4. 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

5. Repository • “provides storage, access, retrieval, indexing, versioning, and configuration management of content. Content includes files, database assets, and structured assets (e.g., XML).”

6. 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

7. 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.

8. 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.

9. 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.

10. 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!

11. Repository Operations There are certain standard operations with any type of content • STORE • ACCESS • PROCESS

12. STORE STORE means … • Persist • Index • Manage

13. ACCESS ACCESS means … • Security • Integrity • Transactions

14. PROCESS PROCESS means … • Transform • Query • Search

15. STORE (XML) • SQLXML • Generating XML from SQL • Via DBMS capabilities • Via client-side transformations • XMLSQL • 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

16. STORE (XML) • XML Storage • XML Query Engine • Enhanced Full-Text Search engine (xml filter) • High performance indexing

17. 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

18. 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

19. STORE (XML) • Methods • Parse • Xql.parse() • Uses SAX 2.0 interface • Any SAX 2.0 parser can be used • Returns SybXmlStream object

20. STORE (XML) ASE XQL Engine Table Applications Store Engine XML Data XML Parser SybXmlStream Formation File CIS

21. 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

22. 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)

23. 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

24. 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

25. 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:

26. 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"

27. 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

28. STORE (FILES)

29. STORE (FILES) Command Shell Client SQL Client Internal FSAPI ASE File System System Traps OS Kernel Files

30. STORE (BUSINESS LOGIC)

31. 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

32. 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”

33. 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

34. ACCESS (BUSINESS LOGIC) • J2EE Data source • Reusable business components • No proprietary code

35. 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

36. ACCESS • Sybase Central interface to map Roles

37. ACCESS (BUSINESS LOGIC)

38. 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

39. 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

40. ACCESS • LDAP • Public Key Infrastructure & Secure Socket Layer • Row level Security ASE 12.5 Directory & Security

41. 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.

42. 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

43. 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

44. 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

45. 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

46. 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

47. 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()

48. PROCESS (XML) • SQL  XML • Sample java classes • Leverage java in the server • XML  XML • Run XQL queries within SQL • Run XQL queries in client

49. 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

50. 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