Charmed Quark Systems, Ltd. - Support Forums and Community
Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - Printable Version

+- Charmed Quark Systems, Ltd. - Support Forums and Community (https://www.charmedquark.com/vb_forums)
+-- Forum: General Discussion (https://www.charmedquark.com/vb_forums/forumdisplay.php?fid=3)
+--- Forum: Driver Development (https://www.charmedquark.com/vb_forums/forumdisplay.php?fid=13)
+--- Thread: Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers (/showthread.php?tid=2929)

Pages: 1 2 3 4


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - pseigler - 09-29-2007

If anyone's interested...

List Builder allows you to expose items as a string collection for use in widgets such as the Vertical List Widget. This is helpful if you want a means to dynamically log security alerts, open zones, lighting status, processor power, etc. For example, you could use List Builder along with the Vertical List Widget to save the most recent songs played, then when you click on one of the list items it will take you to the Media page for that track. In addition, the List Builder has a feature to serialize the list out as a comma delimited string. This could be used to push this “most recently played” list out to a media player for playback.

The driver currently supports up to 10 lists, or Banks, with a maximum of 1000 items per Bank. The maximum item count is set when the driver is loaded. Once a Bank reaches its maximum item limit, List Builder will automatically push the oldest item out of the Bank. This first-in-first-out means of Bank management can be changed after the driver is loaded on a per Bank level. Bank 1 could have a limit of 10 items, Bank 2 a limit of 100, etc. List Builder will automatically truncate a Bank if the limit is changed to a value less than the total item count.

Fields:
AutoAdd_XX, R/W
For each configured Bank of items the driver will create a numerically ordered field named AutoAdd_XX. This field controls the Automatic Adding of Data to the XX bank. When this field is set to True, a string of spatially delimited data written to the Data field will immediately be parsed and added to the same numbered DataBankText_XX and DataBank_XX fields.

DataBankText_XX, R
For each configured Bank of items the driver will create a numerically ordered field named DataBankText_XX. This field contains the serialized version of the DataBank_XX items. The items will be wrapped in double quotes and separated with commas. This field is always in sync with same numbered DataBank_XX field.

DataBank_XX, R
For each configured Bank of items the driver will create a numerically ordered field named DataBank_XX. This field contains the stringlist version of the same numbered DataBankText_XX field.

PushOut_XX, R/W
For each configured Bank of items the driver will create a numerically ordered field named PushOut_XX. This field controls the item limit for the corresponding Bank. Items are managed in a first-in-first-out fashion. If the PushOut value is set to a value less than the current item count, List Builder will truncate the oldest items until the new PushOut value is reached.

RevOrder_XX, R/W
For each configured Bank of items the driver will create a numerically ordered field named RevOrder_XX. The field allows you to reverse the items order in the respective Bank. This feature is very usefully when using the Vertical List Browser. Reversing the Bank order will make all new items added to the Bank show up on the top of the list in the Vertical List Browser. This will give a nice dynamic logging affect.

Data, W
The Data field is used to add new Bank items as well as Data values for Commands. If a Command requires a Data value, make sure that AutoAdd is set to False for all Banks, or the Command Data value will be added to any Banks with AutoAdd enabled automatically. List Builder cannot differentiate between Command Data values and actual data.

Command, W
Used to send commands to List Builder. SEE BELOW.

INITIALIZE [Bank #] - This command will initialize the given Bank # to the item(s) located in the Data field.
Example:
> CQC Action sets Data field to: 1 Two “Three is enough” “Four is too much”
> CQC Action sets Command field to: INITIALIZE 0
> DataBankText_0 contains: “1”, “Two”, “Three is enough”, “Four is too much”
> DataBank_0 contains a stringlist object of: 1 | Two | Three is enough | Four is too much

ADD [Bank #] - This command will add the items in the Data field to the given Bank #.
Example:
> CQC Action sets Data field to: 1 Two “Three is enough” “Four is too much”
> CQC Action sets [/font]Command field to: INITIALIZE 0
> CQC Action sets Data field to: 5 6
> CQC Action sets Command field to: ADD 0
> DataBankText_0 contains: “1”, “Two”, “Three is enough”, “Four is too much”, “5”, “6”
> DataBank_0 contains a stringlist object of: 1 | Two | Three is enough | Four is too much | 5 | 6

DELETE [Bank #] - The command will delete the item(s) located in the Data field. If multiple values exist in the Data field, the Delete command will enumerate through each and remove all found. The Delete command does treat ALL COMPARISONS WITH CASE SENSITIVETY. So, values such as “The dog is wet” and The Dog Is Wet” are not the same. Multiple values should be separated with spaces, using double quotes to surround items containing spaces.
Example:
> CQC Action sets Data field to: “a b c” a b c b “d e f”
> CQC Action sets Command field to: INITIALIZE 0
> CQC Action sets Data field to: b “d e f”
> CQC Action sets Command field to: DELETE 0
> DataBankText_0 contains: “a b c”, “a”, “c”
> DataBank_0 contains a stringlist object of: a b c | a | c

PURGE [Bank #] - The command will delete ALL items in the given Bank #. This is a irreversible command.
Example:
> CQC Action sets Data field to: “a b c” a b c b “d e f”
> CQC Action sets [/font]Command field to: INITIALIZE 0
> CQC Action sets Command field to: PURGE 0

DELTOP [Bank #] - The command will delete the TOP N items in the given Bank #. N is a value retrieved from the Data field. Make sure to set AutoAdd to False for all Banks before writing the value to be used with this command. Otherwise, the value will be automatically added to all Banks with AutoAdd set to True.
Example:
> CQC Action sets Data field to: “One Two Three” 4 5 “Six Seven” 8 9
> CQC Action sets Command field to: INITIALIZE 2
> DataBankText_2 contains: “One Two Three”, “4”, “5”, “Six Seven”, “8”, “9”
> DataBank_2 contains a stringlist object of: One Two Three | 4 | 5 | Six Seven | 8 9
> CQC Action sets Data field to: 3
> CQC Action sets Command field to: DELTOP 2
> DataBankText_2 contains: “Six Seven”, “8”, “9”
> DataBank_2 contains a stringlist object of: Six Seven | 8 9

DELBOTTOM [Bank #] - The command will delete the BOTTOM N items in the given Bank #. N is a value retrieved from the Data field. Make sure to set AutoAdd to False for all Banks before writing the value to be used with this command. Otherwise, the value will be automatically added to all Banks with AutoAdd set to True.
Example:
> CQC Action sets Data field to: “One Two Three” 4 5 “Six Seven” 8 9
> CQC Action sets Command field to: INITIALIZE 2
> DataBankText_2 contains: “One Two Three”, “4”, “5”, “Six Seven”, “8”, “9”
> DataBank_2 contains a stringlist object of: One Two Three | 4 | 5 | Six Seven | 8 9
> CQC Action sets Data field to:3
> CQC Action sets Command field to: DELBOTTOM 2
> DataBankText_2 contains: “One Two Three”, “4”, “5”,
> DataBank_2contains a stringlist object of: One Two Three | 4 | 5


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - stefand - 10-02-2007

Hi, I read this with interest. Does this interact with the DataLogDB driver?

If so,
Do you care to give an example of actual use with the UI widgets?
Let's say that I have a DataLogDB with 3 lists in them.
Temperature a
Temperature b
Temperature c.
Would this driver allow me to show the results of those 3 in one template?


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - pseigler - 10-03-2007

that's a good question, since the DataLogDB exposes it's results as a stringlist itself. i'll have to try that.

ideally, ListBuilder should be able to accept stringlist as Data. that way, you could feed ListBuilder query results from the DataLogDB driver.

i havent tried to send a stringlist to ListBuilder at this time, i'll try and let you know. regardless, though, i'll make sure it can do that on the next version i'm currently testing. since the next version will allow DB access, i'm writing it in a way that you can attach an ODBC datasource to individual lists. this way, you could assign the DataLogDB to a list and feed ListBuilder a custom query, such as

SELECT TOP 100 X FROM DataLogDatabase WHERE ([TimeStamp] > '2007-09-01') AND (Descriptor = 'My device') AND (Descriptor = 'Your Device')

etc, etc. you get the picture. this will allow ListBuilder to pull anytype of data from any database with complete custom queries. including pulling things like the 3 items you want to add together.


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - stefand - 10-04-2007

Now that would make it pretty interesting!

One thing that the datalogdb driver lacks is to massage the returned data in some way. It probably does not belong in the datalogdb driver, but could belong into this one.

Here is what I mean: If I query for a temperature field with the datalogdb driver, I get back an entry like
2007-10-04 08:47:48.000 22.30
I would like to change this to
10/04 8:47:48 22.30

Maybe you could associate a format in and out with DataBankText_XX
Like in
"YYYY-MM-DD HH:MM:SS.HHH AA.BB" and out
"MM/DD HH:MM:SS AA.BB"

Unfortunately there seem to be no direct support in CML to do this easily.
It would have been great if we had scanf and printf.
I will ask Dean in a separate thread if this is possible.


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - pseigler - 10-14-2007

List Builder now can save state, persist data via database support, expose it's lists to other applications and access database data using up to 10 custom SQL queries.

i.e. You may have 3 DataBanks, Banks 1 and 2 use custom queries to build data lists. Bank 1 retrieves data from CQC Data Log Driver and bank 2 may retrieve data from your recipe database, while bank 3 is stateless list that remembers what songs or movies have been played in the last week.

The SQL you use is limited by the database you are connecting too. For example, the following SQL statement is a very simple example of pulling memory information out of a CQC Data Log database:

Code:
SELECT 'Server was using ' + CONVERT(nvarchar(4), [Value]) + '% memory at ' + CONVERT(nvarchar(20), [TimeStamp], 108) As [Value] FROM DataLogValues WHERE (Descriptor LIKE 'Memory%') ORDER BY [TimeStamp]

Setting AutoAdd to True on a data bank with a non-empty Query field will force List Builder to refresh your DataBank by requering every 5 seconds. This means anytime new data is inserted or updated in the database you are quering, your DataBank will never be more than 5 seconds behind - of course this depends on the speed of the query and database access.

The AUTOADD, REVORDER and PUSHOUT commands have been removed. They are no longer needed as each value can be dynamically set directly on each respective field.

IF, you have opted to use a database with List Builder, ALL configuration values and DataBanks will be dynamically saved to List Builders tables, SEE BELOW. This means your last configurations and DataBank values will persist across driver reloads, server outages and CQC service bounces.

A new command has been added for the Query support. If AutoAdd is False, you can call the banks query manually by issuing the following command:

RUNQUERY [Bank #] - This command will run the current Query entered for the given Bank #. Queries must be entered to the Banks respective Query field prior to issuing this command. NOTE: There is no need to use a TOP statement within your SQL queries. List Builder will only retrieve the number of records as configured in the respective banks PUSHOUT field value.
Example:
> CQC Action sets Query field to: SELECT 'Traffic report changed at ' + CONVERT(nvarchar(10), [TimeStamp], 108) + '. Click here for details.' As [Value] FROM CQCData WHERE (Descriptor = 'Traffic') ORDER BY [TimeStamp]
> CQC Action sets Command field to: RUNQUERY 1
> DataBankText_1 contains: “<SQL Result Record 1>”, ““<SQL Result Record 2>”, ““<SQL Result Record 3>”, ““<SQL Result Record...>”


List Builder will ONLY use the data from the first column of each record. Keep this in mind when building your SQL queries. You can combine columns in MS SQL by using the '+' operator.

List Builder does not care how the data is retrieved from the database. For example, you could use custom Views and Stored Procedures if you wish. Use good judgement when creating your queries and test thouroughly before throwing them into List Builder for CQC. Some things to remember:

i) List Builder will only let you pull a max of 1000 list items. So theres no need to run a query that returns 50,000 records.

ii) If you have a PUSHOUT value of 10 set for a particular bank, use T-SQL's 'TOP #' command to pull on the top 10 records. Allthough List Builder will only use the first 10 records, keep the data passing to a minimum for performance. There's no need to send 100 records, when you're only using 10.

iii) Deleting data in List Builders banks WILL NOT affect the data from the original source. HOWEVER, list builder will allow you to send a destructive command such as UPDATE, DELETE and INSERT - YES folks, you can use list builder to save, update and delete data on the fly.

iv) List Builder was designed to access 1 database and 1 database only. When the driver is installed, you will be asked for an ODBC connection to use. Typically, you will have all your CQC related tables in 1 database, thus you can easily build queries against any one of these tables. However, if you need to access data in databases outside the scope of your ODBC connector, I recommend creating Views or Stored Procedures.

SQL for Tables required to run ListBuilder. Just run these in the Query Editor of your SQL server:

Code:
[SIZE=2][COLOR=#0000ff]CREATE [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]TABLE[/COLOR][/SIZE][SIZE=2] [dbo][/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2][tblLBConf][/SIZE][SIZE=2][COLOR=#808080](
[/COLOR][/SIZE][SIZE=2][tblLBConf_Moniker] [nvarchar][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]COLLATE[/COLOR][/SIZE][SIZE=2] SQL_Latin1_General_CP1_CI_AS [/SIZE][SIZE=2][COLOR=#808080]NOT [/COLOR][/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBConf_Bank] [int] [/SIZE][SIZE=2][COLOR=#808080]NOT [/COLOR][/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBConf_PushOut] [int] [/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBConf_RevOrder] [bit] [/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBConf_AutoAdd] [bit] [/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBConf_Query] [nvarchar][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]2048[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]COLLATE[/COLOR][/SIZE][SIZE=2] SQL_Latin1_General_CP1_CI_AS [/SIZE][SIZE=2][COLOR=#808080]NULL
)[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][SIZE=2] [PRIMARY]
[/SIZE]
[SIZE=2][COLOR=#0000ff]CREATE [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]TABLE[/COLOR][/SIZE][SIZE=2] [dbo][/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2][tblLBData][/SIZE][SIZE=2][COLOR=#808080](
[/COLOR][/SIZE][SIZE=2][tblLBData_tblLBConf_Moniker] [varchar][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]COLLATE[/COLOR][/SIZE][SIZE=2] SQL_Latin1_General_CP1_CI_AS [/SIZE][SIZE=2][COLOR=#808080]NOT [/COLOR][/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBData_tblLBConf_Bank] [tinyint] [/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBData_TimeStamp] [datetime] [/SIZE][SIZE=2][COLOR=#808080]NULL,
[/COLOR][/SIZE][SIZE=2][tblLBData_Value] [text] [/SIZE][SIZE=2][COLOR=#0000ff]COLLATE[/COLOR][/SIZE][SIZE=2] SQL_Latin1_General_CP1_CI_AS [/SIZE][SIZE=2][COLOR=#808080]NULL
)[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][SIZE=2] [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[/SIZE]

Please give me feedback and bug information. The next version of List Builder will support casting, so DataBank items can be used in any CQC Widget out there.

** Please note, this driver is not intended to replace the CQC DataLog driver. ListBuilders goal was not as a logger, but as a means to massage data for display. ListBuilder will be slow if used as a recorder or database management tool.


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - pseigler - 10-14-2007

Quote:Here is what I mean: If I query for a temperature field with the datalogdb driver, I get back an entry like
2007-10-04 08:47:48.000 22.30
I would like to change this to
10/04 8:47:48 22.30

Maybe you could associate a format in and out with DataBankText_XX
Like in
"YYYY-MM-DD HH:MM:SS.HHH AA.BB" and out
"MM/DD HH:MM:SS AA.BB"

This is now possible with ListBuilder using the Query field. A query such as this:

Code:
SELECT 'The temp in the master bedroom was ' + [Value] + ' degrees on ' + CONVERT(nvarchar(20), [TimeStamp], 100) FROM DataLogValues WHERE (Descriptor = 'MasterBedroomTemp')
ORDER BY [TimeStamp]

This would result in a DataBank full of stringlist that looked like this:

The temp in the master bedroom was 22.30 degrees on Oct 4 2007 8:47AM

Obviously, the dates and times and text can be changed to anything, but it provides a sample to chew on.


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - stefand - 10-15-2007

Wow, that looks like powerful stuff.
It does look like I will have to investigate a bit the query syntax.

I cannot use this in the near future, but I am sure I will be using this.


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - pseigler - 10-18-2007

Some folks are asking what this driver can really do. here are some examples that i've used it for.

1) Building a dynamic play list. For example, a user of a panel can create they're own playlist, which are stored in SQL, and then just push a button to play their list.

2) build a macro of automation control. instead of hardcoding a control sequance, a user can build there own macro in real time.

3) anything you can do with T-SQL, you can do with this driver. since ListBuilder simply passes the T-SQL to your server and returns the results in the respective DataBank.

4) Bring together data from multiple databases for display in CQC.

5) Save CQC Variable state in SQL. Imagine, now CQC Variable information will be remembered between server reboots, driver reloads or service resets.


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - jchurley - 11-26-2007

Is this driver working?

According to the install notes above:

Quote:When the driver is installed, you will be asked for an ODBC connection to use.


I've tried installing this a few times and have never been prompted for an ODBC connection (and I do have several).

FWIW, the driver version # is 1.1.

Thanks

Joe


Driver to Support Dynamic stringlists for Vertical and Horizontal List Browsers - pseigler - 11-26-2007

i may have uploaded the wrong driver...the attached driver is the latest and has been exported from the last beta of CQC.

this package also includes new fields that provide the item counts.