I am in the process of doing a similar legacy integration for a couple of sites that will continue to use the existing databases, neither are live yet (its a background job, so I am plodding away slowly). The way I tackled that part was a single page for all the external content (about 30,000 pages), but using some custom blocks that interrogate the existing databases directly rather than coding into the single page or copying content. That gives me more flexibility to re-arrange it later. I don't expect this to work with the C5 internal search mechanism, but Google should be able to index it as 30,000 or so pages beneath the single page.

Having said that, I also need to move some other legacy page content directly into C5's database and as you are planning, so would be interested in the guts of your solution as it will save me having to dig how to do that out of the documentation and forums for myself.

Yeah... it's hard to say which is better, but both are possible.

I guess you have a spectrum of possibilities.

At one end, you pull the data regularly and create pages, adding blocks, etc, as the OP suggested. That's all very possible. If you look at the install code for old versions of c5 (<= 5.3, i believe), you'll see that that's how the default pages were installed (it was switched to straight DB imports due to the slowness).

At the other, you have a single page which does the SOAP query on demand based on the arguments, which can be made to look like a page in the path. Somewhere along the middle, you have a job that dumps into a DB table that the single page can then call.

I'm still not sold on creating c5 "pages" (non-singles) for this kind of structured, crud-ed content. I would say the decision should be based on c5-searchability, editability (will there be the 1/20 cases where the end-user wants to add something "new" to a particular tour (by using an additional block, content or gallery, etc)?). Speaking of gallery, you haven't said how rich content is handled. Are those in the backend system too? Or will the job create the record and someone comes in and adds the pictures, etc?

The only gotcha I can think of is ensuring that edits are done where applicable and duplicate pages aren't produced, but you seem advanced enough to take that into consideration.

James

Thanks James (and John the Fish)...both given me new ideas here.

James, my original thought was a single page (eg. /tours/) that simply accepts a tour code or id and loads the content dynamically from the back-office API. Problem is, this is obviously not the most efficient process as requires a fair bit of processing and querying an offsite database for each page load. Also is then a bit of a headache trying to figure out how to work in META tags, associated content like excursions, hotels and, indeed, stuff like images and other media (yes, this rich media stuff will probably be required, and will probably not be coming from the back-end system). Sure there would be some way this could be worked, but would make a large part of the C5 functionality redundant.

This is what brought me to the Concrete 'page' approach (ie. themed page types instead of a single page). I figure I could get away with a traditional C5 page for each tour and, John the Fish, as I think you've done, custom blocks that could be added to the tour page as and when that query different areas of the back-end system related to associated bits like hotels, special offers, relevant excursions, etc... This way the fundamental tour pages are searchable (the main itinerary / description put in place when the tour page is first created at least), can be edited independent of the back-end system (to jazz up in terms of marketing speak / more floral copy), and can take image galleries, downloads, videos, SEO stuff via page attribute, etc... In other words, all the good Concrete5-y stuff! Am I wrong? Is there some way I could use a /tours C5 single page to allow my client this functionality?

I think James's assessment is exactly correct -- it would be easier to have a single page that pulls everything from the external system, but if you want to be able to edit content on the page in a way that's different from that source system, you're going to need to create pages.

I think doing it as a batch job like you suggest is the best way to go. The biggest challenge is going to be making sure your client maintains the content (30,000 pages?! -- I have a hard time just updating my home page!)

Good luck.

-Jordan

Thanks for the input, Jordan...i'm a big fan!

Yes, unfortunately I think the page route is going to be the way to go, at least for the main tour pages of the site. I'm going to need it searchable, i'm going to need to give the client the ability to edit the pulled in content, i'm going to need to give client the ability to add to the pages, etc... I'm thinking I might be able to get away with single pages for the other add-on elements they might want on there (like hotels, excursions, up-sell stuff, associated highlights/monuments, etc...), but even on that i'm not sure. They have specifically requested the ability to search the site and whilst i'm hoping I can get away with just the tour page/itinerary searchable, I can't guarantee at this stage. Might depend how the back-end system is configured to see if I can pull associated assets through automatically in any useful way, but too early to worry about that headache. On the plus side, not talking about anything LIKE 30,000 pages (!!!)...more like 50 (at least for tours...hotels, destination info, add-ons, etc... will obviously add more pages if I go ahead and have to create them as pages too).

In a nutshell, with some custom coding, do you think a C5 job to grab unique tour codes from their back-office API and using them to generate pages programmatically (with certain associated elements of content) is achievable? If you reckon that's not an impossible task then at least i'm confident enough to move forward with my current plan.

The spanner in the works here -- and this is going to be a nightmare for both myself and the guys responsible for their back-office system -- is that they want the ability to have certain content (eg. day by day itinerary) edited on the website posted BACK to the back-office system (and, I guess, to subsequently refresh the content on the website to keep it all in sync). Phew. Bit ridiculous, huh? I'm thinking this is because the back-office system is terrifically unpleasant to edit and maintain and they want the flexibility to use the website as a kindof front-end editing interface for it...maybe. Bit unclear really. This is really why I need to pull the back-office content into the C5 database so we've got a separate working version of all content. Versioning this, returning it to the back-office system and subsequently accepting a new version back is going to be FUN. I'll work on talking them out of it, but I reckon I will need as much of the content in Concrete as possible in any case.

Jordan, see you've been intermittently active on a discussion going on in another thread about Attribute categories. You don't seem to be a huge fan of them as the developer has designed and conceived of their use, but you reckon i'd find some mileage in 'virtual tables' to hold hotel, sites, monuments, special offer data, etc... that need to be associated with certain tours but don't necessarily need pages of their own? Would regular attributes take care of this (assuming I create a single page interface to manage them in the Dashboard)?

Thanks Jordan, James and John...really appreciate your input!

Not sure where I got that 30,000 number from (must have been confused with another thread). If you're only talking about 50 pages then yeah this shouldn't be too overwhelming.

In regards to posting C5 changes back to the original system: if this were me I would *VERY STRONGLY* try to avoid this at all costs. Integrating with 3rd-party systems is always problematic -- just pulling data is going to be a challenge enough (certainly doable, but will require some work). 2-way syncing is going to be nothing but a complete nightmare. Unless you're an experienced developer in general AND experienced with C5 specifically AND experienced with the 3rd party system specifically, it's going to be the worst job you've ever had, and honestly you probably won't even be able to finish it. If I were in your situation I would tell the client it will be challenging and costly, and if they still want to do it, give them an astronomical cost. Like whatever you think your estimate is, multiply it by 5 or 10. Not because you want to extract as much money as possible from them, but because that's probably what it will wind up taking you to do. I am NOT kidding about this! (Can you tell I've been in this situation before? :)

In regards to the attribute system, yeah my thinking is that anything requiring more than a few custom attributes should just be built out as its own set of libraries, database tables, etc. -- building a meta-database on top of the C5 attribute system which is itself sitting on top of another database just strikes me as overkill and difficult to maintain. (By the way, if you're interested, this is called the "Entity Attribute Value" Model or Pattern, and it is a common situation to come across in all systems and platforms -- it's basically a tradeoff between flexibility versus performance and code maintainability and data integrity). But that's just my opinion -- obviously others feel differently, and it's not like one way is right and wrong -- it's again just a tradeoff of flexibility versus complex code, so you have to decide what you're comfortable with.

As for how this applies to your situation, well what exactly do you need to store all that metadata in the C5 system for if it's not appearing on pages? In its most simplistic form, it seems that you could just have one attribute to store whatever ID links that page back to the original source record in the 3rd-party system -- everything else would be content on the page. But nothing's ever that simple, eh?

Great question and an intriguing discussion, btw -- thanks.

-Jordan

Haha...yeah, get the impression you're speaking from bitter experience there. Sure you're very right there...more I try and think in terms of real-life usage (and actually building this) the more concerned it makes me. Even the developer of the back-office system was shaking his head over this one...a requirement they only fire at him the other day before we took a brief, and he seems pretty unsure how to proceed on the 'bi-direction API', and WHY they want it.

In an ideal world, you're very right...it would seem that much of what I require could quite simply be achieved by grabbing each tour page and giving it the one attribute containing the tour id or the unique code that identifies it to the back-office system for the purpose of API request. I'm still hoping this *might* do the trick for the add-on stuff, but will depend on the backoffice system, I suppose. Really a little in the dark on the actual functionality offered by it, so I think I need to speak to them again tomorrow and get a little more detail on how I will be able to interrogate it to get at the content.

What i'm tossing over and over in my head right now is how to handle the one-way sync (or, at least, how to handle it on a regular interval/basis). In the first instance, sure...probably not THAT difficult to query a list of tours, pull in the necessary content, programmatically create the page and one or two content blocks to get us started. What happens if they update the tour description in the back-office system? Forgetting the posting-back from Concrete to them, i'm sure they won't want to have to perform the same edit of basis tour data on the website to bring up to date. Even if it's just a departure date, availabilty status or price and not actual itinerary detail. No worries if the content is just pulled in dynamically via SOAP, but what if they DO want to edit stuff on the site direct? Yeah, they wouldn't want to do this for dates and availability i'm sure as this will def. need to reflect their back-office database live, but they've already said they'll want the ability to edit the itinerary content/detail. Damn...what do you think the best solution there would be? Maybe if the back-office system could generate a 'last updated' flag for each tour which a job in C5 could check against a date held for receipt of content on that tour every night? That sound plausible? Or maybe query a record via the API daily that lists tour codes that have been updated, giving the C5 administrator the option to pull that new content in? That a better bet? Thing is, we would lose all C5 formatting if added via an edit of the Content block on the tour page is a load of plain text is pulled in as replacement, right? Don't see any way around that. Guess we just have to hope things aren't changed that often! Dynamically grabbed prices, availability and stuff is fine...just the main description long content that would cause us probs.

My debate over attributes is just how best to handle the extra bits associated with the tour...let's say they have the tour page and they want to associate 4 hotels, 3 tourist destinations and a optional tour excursion with it? Assuming we leave the content in these instances offsite and just make do with a list of hotel/site/add-on options identified by ID, would a page (collection) attribute not be the best solution in this case? In theory, I could then use the items unique ID in the back-office system, and build a 'more info' page via a single-page with the use of a query string and an API request. Damn...guess will also depend on quite how their back-office system stores and relates data, huh? Too many grey areas here.

In terms of more straight-forward technical questions...will the Concrete full page cache cache the results of an API request assuming I have a custom 'hotels on this tour' block on each tour page, which pulls in the list of hotels dynamically? Guessing this wouldn't make it searchable at all, but would minimise requests and keep things speeding along (assuming I set the cache lifetime long enough). Would a custom block with the necessary cache=TRUE flag store the returned block record of a dynamic API request? Also, i'm guessing it's possible to programmatically specify page attributes during automatic creation? Eg. install a 'hotel' attribute when the script is building the page, and drop in any IDs that might be relevant? Again, saving me more manual labour. ;-)

Thanks again...invaluable having the benefit of your insight on this before I dig myself a massive hole and absolutely ruin my summer on this (urgent and time-pressured!) project! Haha...you know, keep an 'intriguing discussion' from progressing into a career-ending tech-disaster!

Wow, great question and discussion!
Thanks for posting this arcanepain, as I too potentially will be in a similar situation very soon. You're a bit brighter than me though, as I would have probably jumped right into doing it before even posting to the forums. Lesson learned, thanks!
The system I may be working with would be a database that's already on a website running ASP. I haven't even looked into it deeply yet, I'm currently solving the problem via a customized version of InCurl from the Marketplace. However, the only thing in the db that's updated nightly is pricing, which is backed up nightly from the home office's server every night. I have no idea what that thing is running, some specialty software purchased ages ago. It too may be running on ASP, I'm not sure.
Thanks Jordan for your two cents, you're always so helpful!
For now, I'll stick w/ what's working, but plan for additional functionality at a future date.
Thanks again for the excellent post, I'll be sure to keep tabs on this one!

This project has red flags all over it. The client is asking for things that they don't seem to truly understand, and needs it done ASAP. Your first job is really going to be educating them. This gets tricky in terms of how you bill for it, but you'll have to figure that out for your own situation (I often bill out for a "discovery phase" if I sense that things are getting out of the ordinary for a marketing website).

But, really you need to sit down with them and go through the details here. If they say they don't have time for that or are too busy or "just want to get it done", than I honestly suggest you fire this client as they will be nothing but trouble. But if they are professional and reasonable and appreciate your expertise and guidance, then just go through the details. Don't say "no I won't do that!", but instead say "sure I'd be happy to do that, here is the cost". Be honest about the cost -- and be sure to break everything down into discrete steps as much as possible in your head otherwise you'll under-estimate this big time.

I often come up with alternate suggestions for clients -- what I think is the most cost-effective solution, which involves some compromise -- but if you're talking about losing 10% of functionality for more than half the price, most business will appreciate that and go with it. And sometimes the most cost-effective 90% solution isn't even technological -- for example, I'll bid out the price of hiring a temp for a few days to do some data entry, which can often be less expensive than coding up a data import script.

Good luck!

-Jordan

PS - to actually answer your question, pulling in updated info is easy if they're not modifying it on the C5 side (just include a last-updated timestamp in addition to the ID as a custom attribute), but if they're going to update data on the C5 side then now you're basically back to the 2-way sync situation -- how do you know which data to trust, or where that data even is (like if it's in a content block, how are you going to locate that nugget of info if it was inside a <b> tag but now the user added a new paragraph before it or changed it to <h1> or any of a zillion possibilities)?

Thanks again for your expertise Jordan!
It could certainly get tricky. I'm just planning for this one to come up in the near future and want to be prepped w/ good answers and know how I'm going to handle it.
The person who maintains their main site, which has a catalog that gets it's prices updated nightly via the business's in-house server (the one I don't know what it's operating on) built the website about 10 years ago. It may have been cutting edge then, but now it's showing it's age, particularly in the design and semantic (not) markup. It's a table based layout if that tells you anything.
I created an offshoot site w/ c5 that's using a modified version of InCurl to remove <font> tags (Ugh) and add some classes to some of the elements.
Overall, this is working great. Speed is an issue, but InCurl caches for a day after the first load so it's not too bad.
Apparently the client has a hard time getting the other webmaster to do any updates to his old site w/o charging an arm and a leg. He was blown away by the site I created, and I think I'll be able to use that to my advantage and convince the client to allow me to provide new, up-to-date HTML code w/ CSS stylesheets for his older site, which he can turn over to the other webmaster to put in the necessary dynamic elements to make it all work off of his db. That would keep cost low, relegate the unsupportive webmaster to db caretaker only, and allow me to setup that site as it should be.
After that, I'm thinking InCurl, or some cURL method, could be developed to query the main site db (In ASP) on say a weekly basis just to check for any updated products, but will query the price column every day and update that only on a daily basis.
That way, I can build my own db tables that looks to the main db for updates. I do not think I'll have to sync from me to them, as they do like their in-house system.
Once that's accomplished, I want to modify the shopping cart. On the main site, it's currently just a "Reservation Request" that sends an email and then the company contacts that person about the reservation. I'd like to have the company's in-house server updating product quantities very often to the main website, something it doesn't even do at the moment. From there, I could get that info and create a true shopping cart b/c I'll be able to get product availability.
It shouldn't be too hard, but that's how it always seems doesn't it!
I do not think it's of the caliber of difficulty that arcanepain is dealing with though.
Right now, I'm just looking at what would be the most cost effective way to get something like that done.
One thing I'm not sure of is PHP ability to query an ASP/Windows Database. Can it?
I'm not sure what language the DB is in, just know it's on a Microsoft server and the development company is big Microsoft fans.
If PHP cannot directly access the db, which I doubt the webmaster would give me the keys to anyway, then a cURL method would be appropriate, don't you think?
Cheers, thanks for the advice!

Working on the 'alternate suggestion' route right now...just waiting to hear back from the developer of their back-office system. If I can suss out exactly what they're hoping to achieve / the problem they're trying to solve with wanting the content to go both ways, then maybe I can suggestion a better (less daunting) solution. Also trying to get some examples of how to query their API and the XML response back so I can get a little more laid out in my head -- can one tour/product code unlock all data, descriptions and associations?

Actually been browsing around these forums alot to see what I can turf up in terms of solutions other people have come up with...the InCurl/cURL route I did debate, but there isn't really any front-end to the back-office system that would be worth my while trying to pull in. Assuming I can get around having to worry about syncing the two databases up and trying to take account of edits and stuff, been thinking about the best way to approach associated content -- eg. hotels, extensions and stuff. First debate is whether I'll need to create Concrete pages for all these bits or not...hopefully these can be dynamic API queries but, assuming the client will want full ability to edit, search, etc these bits on the site, then fair enough, content needs to come over to our site/database. Ok...then does each bit NEED it's own page, or can we get away with a back-end, single-page editing interface and a new 'HOTEL' attribute collection, new 'EXCURSION' attribute collection, etc (along the lines of ijessup's New Object package) which could be edited, searched (I think) and pull into a relevant tour page or rendered on a '/hotel' single page via link and query string? Queried by relevant tour code, assuming set from a multiple select list of all available tour codes/pages on the site.

OR perhaps I could create full pages and set the tour code as an attribute, and use something like ChadStrat's 'related pages' add-on (or, indeed, your related_content package I found buried on another discussion) -- I could then build custom blocks for 'hotels' and 'excursions' based off the page_list block and have it pull in add-ons it identifies by the relevant tour code set in a page attribute on those other pages.

Yeah...think the above should all be do-able, and the different approaches just require different levels of manual labour building the pages or coding stuff to do most of the leg-work/building for me. The New Object stuff scares me a bit (alot), but would probably be worth looking into to provide an editor with a convenient method to manage attributes that can hold an array of sub-attributes without having to add many separate ones to tour pages and/or massively cluttering up the Concrete 'properties' pane. Anyone know is there's any way to create custom attribute types that can hold multiple sub-attributes (like HOTEL as an attribute type, holding Title, Description, Photo, Address) without tackling these new object/category attributes?! That would be ideal.

I'll keep everyone posted, but think you've all have more than enough of my problem-filled, essay-like musings for now! :-)

Sounds like you're getting everything formulated, glad to hear it.
Unfortunately, I can't be of much assistance for your situation. However, I have to admit the posts on this question are the longest I've ever seen for any forum post, and I enjoy the musing, as it helps me to muse even more and get thoughts down on paper.
Thanks again for the great discussion!

Hey...i'm an English and Philosophy major. Really not a chance i'm going to do 'concise' very well! :D

Haha, your posts show it! You're certainly doing some advanced stuff to not be majoring in graphic or web design/development. Best of luck!

By "ASP/Windows Database" I assume you mean SQL Server, which PHP can in fact query. And Concrete5 uses the ADODB library for database access, which should make it even easier (might need to fiddle with connection info, but once it's connected all of the query syntax should be the same, since that's the whole purpose of ADODB):
http://phplens.com/lens/adodb/docs-adodb.htm...

An excellent example of setting page permissions

http://www.concrete5.org/community/forums/customizing_c5/package-in...

I'm guessing you could code this yourself but XML/XSLT would be the way to go.

I built an add-on designed specifically for this sort of scenario (pull). You say they have XML feeds of their data? If you can fathom XSLT you can take that data using this add-on and transform it however you please on your C5 website.

http://www.concrete5.org/marketplace/addons/eantics-xml-transformat...

Can't help with posting data back, makes me shake just thinking about it!

Cheers

Interesting...another thing to ponder there! Can't say I was at all familiar with XLS stuff. I'm still waiting to hear back from the back-office developer to try and get some solid sense of what i'll be working with from his system...will look at this avenue again then I guess. Just hope all the musing in this thread hasn't been in vain! Watch this space, all...

I don't know if I can be of any help, but I have had to extract content from external sites and programatically create new C5 pages and content blocks a few times now.

I would give my left you-know-what for an Area "addDefaultContent" method, but I can understand why the dev's didn't waste time with this given their preexisting way to add this via the dashboard - still, something on my wish list.

Anyways, here's a snippet of my script that may shed some light:


- $objPage is the returned new Page object
- $objParent is the targeted Page object new pages will go under

- $sqlDate is just a date I extracted from the source

- AREA_HANDLE is just a constant defined as 'Main content'

- ToolKit::isValidPage is just a custom singleton that ensures we have a proper Page object

- This version I was taking static HTML pages with gawd-awful deprecated HTML/CSS code so I used their .html file name to base my cName and cHandle off of

- $strHtml is the processed content from the source pages



I haven't run this by any C5 experts so there could be mistakes I'm not seeing. I have used it a couple times and I haven't had any problems.

This sounds like a cool and scary problem at the same time and like others have said it's fun to read about.

Best of luck!

I love you, Beebs93! Yeah...I reckon I can get my head around that and, though i'm still waiting for clarification on the back-end system i'm going to have to be working with, I think i'll be able to use that chunk of script of yours to form the crux of my solution! I'm guessing there are perhaps ways of achieving this by looping through more Concrete helpers & more elements of the API, but I would imagine the direct database submissions you've specified will perform a little better and do the job just as well. I miss somewhere you specify the page type? Eg. 'fullwidth'? Sure that's easy enough to add into the DB queries here...guess you've left that bit out from the snippet you've shared here to help keep things generic and re-usable (or maybe the default was fine for you, of course!). Either way, many thanks!

To everyone else following the discussion, i'm still waiting to be enlightened on certain points...lots of crazy ideas for solutions to the various problems bouncing around in my head though (actually, doing a pretty great job of ruining my weekend as I chew ideas over with a view to responding to the client Monday/Tuesday!).

Oops, you're right. I inserted the missing snippet and reformatted the post to make a bit easier to read.

And just so I can say I told you so, run this on a dev/test C5 site (or backup your current site) if you decide to use it. I mangled a couple databases trying to work this one out.

Generally it's better to stick to the APIs rather than inserting SQL, in case the tables change.

Below is a function I use in my package controller to insert default content onto the single pages I create (there are dozens in the package, so this is the most extensible way I've found). It might be of some help.



In my package directory, I have a /package_name/config/install/text directory with a bunch of files like amplify.elements.detail.Introduction.html. When I pass a page object for a page with the path /amplify/elements/detail, it looks for amplify.elements.detail.* and, for each file it finds, loads the content into area named after the *.

James

Yeah, James' version is probably the safer route. A combination of Page's add() and addBlock() methods should be the best bet.

Thanks alot, both! Any way that works, i'll take it! First approach should definitely be the full-bore Concrete 5 API process I suppose, but i'd be really surprised if, in bringing this project to life (assuming we can nail down exactly what the client needs and its achievability!) I don't have to resort to a fair bit of direct database wrangling and, Beebs93, you've definitely given me a leg up with that!

Quick update for you all...proposal finally off to the client now. Flagged up potential integration stumbling blocks and (likely) difficulties in the build, caveats to warn of possible delays, incurred costs, etc... Let's see what they come back with! Tentatively included one-way integration in the core proposal, but pulled out the two-way integration as separate development. If we get the thumbs up then build will have to begin very soon...don't worry, sure i'll be reviving this thread then!!

James that's a really interesting idea for populating a site from default content. Any chance you will can share the package?

No, I can't share the package. It's a very large and expensive web-application-in-a-box, with a ton of single pages. I wanted the intro text of each page to be editable after install, so my solution was as described. The controller creates about 40 singles, and adds default content as it goes.

But if you had in mind some other packagey functionality (something based on the above), then I can probably separate it out pretty easily and add the package around it.

James