Integrating Tickets with x ...

[A. Shore]

Folks, the successes we've had (Asterisk, etc.) in hooking up have
been based on both ends of the connection having access to a common
database, the latter being MySQL of course.

So, if you've been playing with any thoughts re a connection - e.g.,
Dstar has been mentioned in the past - and there's an available
database component, we shd talk.

AS

[Shawn Stoddard]

Ever thought of opening the door a little wider? One of the ideas on my
radar is exposing the database using REST-based web service calls. PHP
looks to be able to deliver that type of functionality keeping in line
with your goal of platform independence. Providing that type of access
opens to the door to all sorts of extensions ranging from a native
mobile phone application to some other types of interconnects like APRS
(two way this time), D-STAR (linking multiple locations without the
Internet) or occasionally connected stations (it can and will happen for
some of us). Architecturally, it provides a demarcation between Tickets
and the extension products, making the constant improvements less likely
to break other software.

I would love to hear the thoughts of the group on this idea.

[Arnie Shore]

Shaun, no, I hadn't really given thought to widening the doorway in a RESTful manner, although early on something like that had been suggested  by Bob Austin.  Yr thought is a major/good one, IMO, now that Tickets has achieved some level of maturity.  (Arguments re the latter statement?)

And, fortunately, there's some PHP available online as introductory material, so we'd not be starting from a blank sheet.

The question I see right now is re where's the interest in a client implementation.  An obvious (to me,anyway) initial shot cd be a chat client, or a 'here's an incident' source.

All?

A

[Shawn S]

I know I could guess my way through this, but is there any
documentation regarding the database schema for Tickets? I am
interested in exposing information about the status and location of
incidents via a REST-based web service.

> >> AS- Hide quoted text -
>
> - Show quoted text -

[Arnie Shore]

Shaun, yr guesses prbly wd be right, but no, there's nothing documented re yr suggestion.  See the schema for table 'ticket', and incs/functions.inc (near lines 136-139) for applicable status values, etc.)

It's been a while since I looked around for interchange standards and nothing useful popped out then.  But times change and it may be worthwhile to search again.

Any thoughts yet re what do you see at the consumer end?  AS

[Shawn Stoddard]

I will start there and with any schema information from MYSQL.

No firm decisions yet. I am currently at a developers conference and attended a presentation on ODATA. It was already on the "short list" of possible choices. Working with something REST-based just seemed friendlier for both the server and client in regards to amount of work involved and not restricting the types of client. ODATA is based upon ATOM, which a lot of tools already support. I will probably sleep on this another night or two and then mock something up.

--
You received this message because you are subscribed to the Google Groups "Open Source CAD - Dev" group.
To post to this group, send email to open-sourc...@googlegroups.com.
To unsubscribe from this group, send email to open-source-cad-...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/open-source-cad---dev?hl=en.

[Arnie Shore]

Noted - have fun there.

Of course the ticket schema won't be sufficient to get you all the stuff you'll prbly want - we use a good bit of linked tables to flesh out various values.

For that you'll want the sql, so do contact me off-list to get that exchange going.  (Everyone's welcome, but some details can just clutter up yr inboxes.)

AS

[Shawn S]

Yeah, I spotted that during my initial looking. What SQL are you
offering?

> > and then mock something up.- Hide quoted text -

[Arnie Shore]

Something like the fol SQL will get the data for a given incident id:  (lots of ash and trash here, but the LEFT JOINS fill in the useful linked values.)

You'll need to watch out for possible ambiguity in field names:  Like id, description, etc.that are common to several tables, with id common to all.

It really depends on just what information schema you want to broadcast.

Come back,  A

    $query = "SELECT *,
        `problemstart` AS `my_start`,
        FROM_UNIXTIME(UNIX_TIMESTAMP(problemstart)) AS `test`,
        UNIX_TIMESTAMP(problemstart) AS problemstart,
        UNIX_TIMESTAMP(problemend) AS problemend,
        UNIX_TIMESTAMP(date) AS date,
        UNIX_TIMESTAMP(booked_date) AS booked_date,       
        UNIX_TIMESTAMP(`$GLOBALS[mysql_prefix]ticket`.`updated`) AS updated,       
        `$GLOBALS[mysql_prefix]ticket`.`description` AS `tick_descr`,
        `$GLOBALS[mysql_prefix]ticket`.`lat` AS `lat`,       
        `$GLOBALS[mysql_prefix]ticket`.`lng` AS `lng`,
        `$GLOBALS[mysql_prefix]ticket`.`_by` AS `call_taker`,
        `$GLOBALS[mysql_prefix]facilities`.`name` AS `fac_name`,       
        `rf`.`name` AS `rec_fac_name`,
        `$GLOBALS[mysql_prefix]facilities`.`lat` AS `fac_lat`,       
        `$GLOBALS[mysql_prefix]facilities`.`lng` AS `fac_lng`,         
        `$GLOBALS[mysql_prefix]ticket`.`id` AS `tick_id`
        FROM `$GLOBALS[mysql_prefix]ticket`
        LEFT JOIN `$GLOBALS[mysql_prefix]in_types` `ty` ON (`$GLOBALS[mysql_prefix]ticket`.`in_types_id` = `ty`.`id`)   
        LEFT JOIN `$GLOBALS[mysql_prefix]facilities` ON `$GLOBALS[mysql_prefix]facilities`.id = `$GLOBALS[mysql_prefix]ticket`.facility
        LEFT JOIN `$GLOBALS[mysql_prefix]facilities` rf ON `rf`.id = `$GLOBALS[mysql_prefix]ticket`.rec_facility
        WHERE `$GLOBALS[mysql_prefix]ticket`.`ID`= $id "

--