New post
Question
· Feb 15, 2017

Property as Simple list of string values

#Object Data Model #SQL #Caché

I have a property, Emails, that needs to contain a list of comma-delimited values, or "". Is there a benefit to making this property a list of %String, a Collection[list] (or however you do it), an array, or anything other than a simple %String type?

I just  need to store some values, and return these values in SQL -- so it needs to be SQL compatible.  I don't need to index the values or use individual values in the SQL statement (e.g. Select * from table where emails [ "email@co.com"  -- I don't need to do this, although maybe this would work anyway if it's of type %String).

What's the simplest way to store multiple string values, and display them in SQL? I'm just going with %String for now.

Thanks,

Laura

Discussion (3)0
Log in or sign up to continue
Laura Cavanaugh · Feb 15, 2017

Well, that helps.  Thanks.  I can't forsee any reason to make it more complicated, unless there is some huge benefit that I'm overlooking? Ah, I was thinking of changing the emails to a list of users, and getting their emails from the user table, so maybe that's a future benefit.

I extended the %Net.MailMessage for one object, and had such a pain of a time stuffing the To prooperty and retrieving it again (it's a list of %String). This time I was thinking a simple %String would be nice, as long as I can use it with SQL, and stuff.

Thanks,

Laura

0 0
Eduard Lebedyuk · Feb 16, 2017

The benefits of using list of %String  over just %String are:

  • You can easily index individual values
  • You can project and access values as a separate table

I actually had a use case for exactly this data. Requirements were:

  • There are alerts and users
  • Alerts are sent once an hour
  • Each alert is sent to multiple users
  • Each user should receive only one digest email with all relevant alerts

List of %String was extremely useful in this case. First I wrote a temp table:

/// Store alerts (for current process and runtime-only)
Class Util.Alert Extends %Persistent
{

/// Alert topic
Property topic As %String(MAXLEN = 1000) [ Required ];

/// Alert text
Property text As %String(MAXLEN = "");

/// Recipients
Property emails As list Of %String(SQLPROJECTION = "table/column", STORAGEDEFAULT = "array");

/// Index
Index emailsIndex On emails(ELEMENTS);

/// Add one Alert
/// w ##class(Util.Alert).add("Error", $lb("1@1.com","2@2.com"), "text")
ClassMethod add(topic As %String, text As %String, emails As %List) As %Status
{
    set obj = ..%New()
    set obj.topic = topic
    set obj.text = text
    
    if $listvalid(emails) {
        for i=1:1:$ll(emails){
            do obj.emails.Insert($lg(emails,i))
        }
    }
    return obj.%Save()
}

}

Next I wrote a business service which searched for alerts and added them to this table (not relevant for this discussion). And then once all alerts for an hour are in the Util.Alert table, sendEmails method can easily send alerts in digest mode:

/// Generate emails from alerts and send them
ClassMethod sendEmails()
{
    &sql(DECLARE C1 CURSOR FOR
         SELECT DISTINCT emails  
         INTO :email
         FROM Util.Alert
    )
 
    &sql(OPEN C1)
    &sql(FETCH C1)

    While (SQLCODE = 0) {
        set text = ..generateEmailText(email)
        do ..SendEmail(email, text)
        &sql(FETCH C1)
    }
    &sql(CLOSE C1)
}

/// Create email with all alerts for user
ClassMethod generateEmailText(email As %String) As %String
{
    set emailText = ""
    &sql(DECLARE C2 CURSOR FOR
         SELECT topic, text  
         INTO :topic, :text
         FROM Util.Alert
         WHERE FOR SOME %ELEMENT(emails) (%VALUE=:email)
    )

    &sql(OPEN C2)
    &sql(FETCH C2)

    While (SQLCODE = 0) {        
        set emailText = emailText _  topic _ ": " _ text _ $$$NL
         &sql(FETCH C2)
    }
    &sql(CLOSE C2)
    
    return emailText
}

Without list of %String SQL here would be far harder to write or slower.

3 0