EOVSA Wiki - User contributions [en]

2016 December

2016-12-07T00:51:59Z

Mlukicheva: /* Dec 07 */

== Dec 01 ==
'''12:52 UT''' For the past several days we have done normal solar observing, with some interruptions due to testing new fast-correlator designs. The latter is showing improvement, but still some issues to resolve. During the solar observations, there have been a few small events. One M1-class flare occurred, but had almost no radio emission apparently. Activity seems to be subsiding now.

== Dec 02 ==
'''15:00 UT''' We made an attempt last night to do pointing measurements on Ant 14, but there were several problems. The tunable LO was not switching, but mainly Ant 14 was not in the subarray! After this was fixed, the wind was high, so observations were terminated early. None of the observations will be useful. We will proceed with normal solar observing.

== Dec 03 ==
'''12:04 UT''' Another attempt is underway to do pointing measurements. Early in the observations, it was discovered that the GPS clock had lost connection, which caused many subsystems to get confused. By about 01:30 UT the system had been brought back into service, and data-taking had started. Ant 12 is missing, because of a possibly spurious ''Lo Hard Limit'' in Azimuth (the current Azimuth is reading high, not low...). Also, overnight there have been many episodes of high wind, so many of the scans will be useless. Right now I count 9 potentially good scans. I also found the tunable LO saying ''Queue Overrun'', so tuning on band14 may not have been correct for awhile--not sure when this occurred.

== Dec 04 ==
'''12:22 UT''' The wind was bad '''all day''' yesterday without let-up, hence there were virtually '''no''' good scans the entire day--maybe about 9 total. I restarted the pointing schedule last night, for a third night of attempts to get good results, and the wind was good, '''but''' this morning I found three anomalies: Ant 14 Dec drive was stuck with a permit, Ant 14 VATTN was set at 5 dB (maybe not a killer), and Ant 14 DCMATTN was 0 (definitely a killer). Also DCMAUTO was not off, which is very strange. I have no idea how these settings get corrupted. It may be that none of these data up to now are any good. The scan starting 12:39 UT may be the first with any chance.

'''14:30 UT''' The Ant 14 Dec drive spiked again, so it only successfully did one source. I have reset it, but there appears to be some generic problem with the dish that results in Dec motor-current spikes.

'''17:30 UT''' I checked again, and again the Ant 14 drive is stopped. I reset it, but I do not know if we are getting any good data. Perhaps I have to babysit it on each source change.

'''21:51 UT''' For what it is worth, the observations from 17:30 UT to now should be good, with 4 more pointings to go.

== Dec 05 ==
'''23:30 UT''' No observations at all today, because of testing of the fast correlator. The packet headers look better now, so we are getting close, but there are some issues with both number of X packets and the content of the packets, suggesting an issue with the FFT block.

== Dec 06 ==
'''17:20 UT''' Normal solar observing has started. Kjell tells me that the air conditioning stopped again, but he is cooling the room manually.

'''18:34 UT''' It seems that none of the pointing observations we attempted over the weekend are any good, even though at least some were certainly supposed to be tracking, with correct settings. As a test, I am grabbing a quick observation of 3C273--on source by 18:36 UT. Okay, I just checked, and the data on 3C273 is just fine. I cannot imagine what went wrong over the weekend. I guess we have to make another attempt to do pointing, overnight.

'''18:50 UT''' Back to solar observing.

'''23:41 UT''' Start of 24-hour pointing measurements. Ant12 "to stow". Wind 10 mph.

== Dec 07 ==
'''00:00 UT''' Continue 4-hour pointing measurements.

'''00:04 UT''' Strong wind 21 mph.

'''00:44 UT''' Front end temperatures for Ant3 and Ant14 are red.

2016 December

2016-12-07T00:49:13Z

Mlukicheva: /* Dec 07 */

2016 December

2016-12-07T00:04:43Z

Mlukicheva: /* Dec 07 */

2016 December

2016-12-06T23:46:41Z

Mlukicheva: /* Dec 06 */

2016 December

2016-12-06T23:45:42Z

Mlukicheva:

== Dec 01 ==
'''12:52 UT''' For the past several days we have done normal solar observing, with some interruptions due to testing new fast-correlator designs. The latter is showing improvement, but still some issues to resolve. During the solar observations, there have been a few small events. One M1-class flare occurred, but had almost no radio emission apparently. Activity seems to be subsiding now.

== Dec 02 ==
'''15:00 UT''' We made an attempt last night to do pointing measurements on Ant 14, but there were several problems. The tunable LO was not switching, but mainly Ant 14 was not in the subarray! After this was fixed, the wind was high, so observations were terminated early. None of the observations will be useful. We will proceed with normal solar observing.

== Dec 03 ==
'''12:04 UT''' Another attempt is underway to do pointing measurements. Early in the observations, it was discovered that the GPS clock had lost connection, which caused many subsystems to get confused. By about 01:30 UT the system had been brought back into service, and data-taking had started. Ant 12 is missing, because of a possibly spurious ''Lo Hard Limit'' in Azimuth (the current Azimuth is reading high, not low...). Also, overnight there have been many episodes of high wind, so many of the scans will be useless. Right now I count 9 potentially good scans. I also found the tunable LO saying ''Queue Overrun'', so tuning on band14 may not have been correct for awhile--not sure when this occurred.

== Dec 04 ==
'''12:22 UT''' The wind was bad '''all day''' yesterday without let-up, hence there were virtually '''no''' good scans the entire day--maybe about 9 total. I restarted the pointing schedule last night, for a third night of attempts to get good results, and the wind was good, '''but''' this morning I found three anomalies: Ant 14 Dec drive was stuck with a permit, Ant 14 VATTN was set at 5 dB (maybe not a killer), and Ant 14 DCMATTN was 0 (definitely a killer). Also DCMAUTO was not off, which is very strange. I have no idea how these settings get corrupted. It may be that none of these data up to now are any good. The scan starting 12:39 UT may be the first with any chance.

'''14:30 UT''' The Ant 14 Dec drive spiked again, so it only successfully did one source. I have reset it, but there appears to be some generic problem with the dish that results in Dec motor-current spikes.

'''17:30 UT''' I checked again, and again the Ant 14 drive is stopped. I reset it, but I do not know if we are getting any good data. Perhaps I have to babysit it on each source change.

'''21:51 UT''' For what it is worth, the observations from 17:30 UT to now should be good, with 4 more pointings to go.

== Dec 05 ==
'''23:30 UT''' No observations at all today, because of testing of the fast correlator. The packet headers look better now, so we are getting close, but there are some issues with both number of X packets and the content of the packets, suggesting an issue with the FFT block.

== Dec 06 ==
'''17:20 UT''' Normal solar observing has started. Kjell tells me that the air conditioning stopped again, but he is cooling the room manually.

'''18:34 UT''' It seems that none of the pointing observations we attempted over the weekend are any good, even though at least some were certainly supposed to be tracking, with correct settings. As a test, I am grabbing a quick observation of 3C273--on source by 18:36 UT. Okay, I just checked, and the data on 3C273 is just fine. I cannot imagine what went wrong over the weekend. I guess we have to make another attempt to do pointing, overnight.

'''18:50 UT''' Back to solar observing.

'''23:41 UT''' Start of 24-hour pointing measurements. Ant12 "to stow"

== Dec 07 ==
'''00:00 UT''' Continue 4-hour pointing measurements.

Stateframe Database

2016-09-20T18:49:51Z

Mlukicheva: /* Initial setup of database access software */

== Setup and Initial Test of Python Access to Existing Database ==

=== Introduction ===

This document describes the initial setup and access of the EOVSA Stateframe database from Python. The original version of this document described the case of a database called “eOVSA05” that existed on a machine named “GARY-FY13NB” (laptop). That connection string for connecting from the laptop was:
<pre>
cnxn = pyodbc.connect(“DRIVER={SQL Server}; SERVER=GARY-FY13NB; DATABASE=eOVSA05;Trusted_connection=yes;")
</pre>

However, some adjustments were needed to allow connection to the actual server machine (EOVSASQL), using an IP address and “SQL Authentication.” The trick was to create a user on the server in the SQL Server Management Studio (user name is <code>‘aaa’</code>, password is <code>‘I@bsbn2w’</code>), and then use the server IP address (currently ‘128.235.89.168’) on port 1433. This port had to be opened in the server’s Windows Firewall. The new connection string is:
<pre>
cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06;UID=aaa;PWD=I@bsbn2w;")
</pre>

The other commands have been updated for the current situation, also.
As of 2014-Sep-30, the SQL Server has been set up at OVRO and the connection string from Helios is:
<pre>
cnxn = pyodbc.connect("DRIVER={FreeTDS}; SERVER=192.168.24.106,1433; DATABASE=eOVSA06;UID=aaa;PWD=I@bsbn2w;")
</pre>

=== Initial setup of database access software ===

The Python library to access Microsoft SQL Server is called pyodbc. This describes downloading the library and integrating it with an existing '''python2.76''' installation, making a first connection to the database, and executing a query. The query assumes that database “eOVSA06” has a populated table “fV26_vD15.”

1. First install '''pyodbc''' ([https://code.google.com/p/pyodbc/downloads/list]). For instance, '''pyodbc-3.0.7.win-amd64-py2.7.exe'''.

2. Start '''ipython''' and type
<pre>import pyodbc</pre>
3. Connect to database with
<pre>cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06; UID=aaa; PWD=I@bsbn2w;")</pre>
4. Get a 'cursor' to the database:
<pre>cursor = cnxn.cursor()</pre>
5. Send an SQL query:
<pre>cursor.execute("select top 16 * from fV26_vD15 order by Timestamp")</pre>
6. Fetch the data returned by the query
<pre>rows = cursor.fetchall()</pre>
7. rows now contains a list of 16 rows.

Note that setup on Linux (Ubuntu) is quite a bit more complicated. Here are the steps:

1. Download '''pyodbc-3.0.7.zip''' from the above link (or copy from '''helios.solar.pvt''').

2. Install unixODBC and FreeTDS packages:
<pre>sudo apt-get install unixODBC-dev freetds-dev freetds-bin tdsodbc</pre>
3. Edit '''/etc/freetds/freetds.conf''' (or copy from helios) to change the last 4 lines to
<pre>
[sqlserver]
host = sqlserver.solar.pvt
port = 1433
tds version = 11.0
</pre>
4. Edit file '''/etc/odbc.ini''' (or copy from helios) to contain:
<pre>
[sql]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver.solar.pvt
Database = eOVSA06
</pre>
5. Edit file '''/etc/odbcinst.ini''' (or copy from helios) to contain:
<pre>
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
</pre>
6. Unzip the zip file in step 1, cd to the directory '''pyodbc-3.0.7''', and type:
<pre>
python setup.py build
sudo python setup.py install
</pre>

From there, use the connection string for Linux shown in the introduction section, and proceed from there.

=== Examples of returned queries ===

<pre>
cursor.execute(“select top 2 * from fV26_vD1 order by Timestamp”)
rows = cursor.fetchall()
</pre>

Returns the entire “dimension-1” data for the first two entries in the table, ordered by ''Timestamp''. The contents can be accessed one row at a time (<code>rows[0]</code> and <code>rows[1]</code> in this case), and the entry names can be listed with <code>rows[0].cursor_description</code>. One can access the value of, say, the current temperature via <code>rows[0].Sche_Data_Weat_Temperature</code>:
<pre>
cursor.execute(“””select top 20 * from fV26_vD15 a where (a.[I15] % 15) = 0 order by Timestamp”””)
rows = cursor.fetchall()
</pre>

Returns the first 20 rows for Antenna 1 [via (<code>a.[Index] % 15) = 0</code>] of the dimension-15 table, ordered by ''Timestamp''. One can check that these are all for the same antenna, for example, by checking one of the pointing coefficients using the Python code:
<pre>
for row in rows: print row.Ante_Cont_PointingCoefficient2</pre>

=== Create a new StateFrameDef Table entry ===

Whenever a new version of the stateframe is created due to some change in the content of the stateframe, a new StateFrameDef table entry must be created. This is accomplished via a set of SQL commands, like:
<pre>cursor.execute(“insert into StateFrameDef (Status, Version, Dimension, DataType, FieldBytes, DimOffset, StartByte, FieldNum, FieldName)
values ( 0, 15, 15, 'u16', 2, 28, 759, 1, 'DCM_Slot')”)
</pre>

and so on for the entire new table. Once all of the rows of the table are entered, the command to create the tables for that version is:
<pre>
cursor.execute(“update StateFrameDef set status=1 where Version=’15’”) </pre>

NB: Once a stateframedef entry is entered, it cannot be entered again within causing unrecoverable errors in the table. See below for description of procedure to clear all of the tables and start ove

=== Complete process of inserting a new binary record ===

The structure of binary data for a given stateframe version is encoded in its XML file with the name ‘stateframe_vxx.00.xml’, where xx is the version number, i.e. 26. The binary data has to be rearranged via a routine in '''stateframedef.py''' called <code>transmogrify()</code>. A complete recipe for reading and inserting data from a stateframe log file (‘sf_20140205_v26.0.log’ in this example), is as follows:
<pre>
import stateframedef as sfd
import pyodbc
sf, version = sfd.rxml.xml_ptrs(‘stateframe_v26.00.xml’)
brange, outlist = sfd.sfdef(sf)
f = open(‘sf_20140205_v26.0.log’,’rb’)
buf = f.read(32)
recsize = sfd.struct.unpack_from(‘i’, buf, 16)[0]
f.close()
cnxn = pyodbc.connect("DRIVER={SQL Server};SERVER=128.235.89.168,1433;
DATABASE=eOVSA06;UID=aaa;PWD=I@bsbn2w;")
cursor = cnxn.cursor()
with open(‘sf_20140205_v26.0.log’, ‘rb’) as f:
bufin = f.read(recsize)
bufout = sfd.transmogrify(bufin, brange)
try:
cursor.execute(‘insert into fBin (Bin) values (?)’,
pyodbc.Binary(bufout))
except:
pass
cnxn.commit()
</pre>

The execute line may return an error and fail, of course, especially if the data have previously been inserted, so it is enclosed in a try: except: clause so that everything does not immediately stop. Error checking would go into the except: clause.

=== Procedure to create the information for a new stateframedef table ===

The Python code in '''stateframedef.py''' does all of the manipulation related to creating StateFrameDef (and also ScanHeaderDef) tables as well as converting stateframe (or scanheader) binary data to the database data. For various reasons involving the internals of SQL, it is necessary to reorder certain data (two-dimensional arrays) in the stateframe and scanheader before they can be saved in the SQL database. These special cases are handled in the bowels of '''stateframedef.py'''’s <code>walk_keys()</code> routine.
To create a stateframedef table from a new stateframe xml file, e.g. stateframe_v32.00.xml, do:
<pre>
import stateframedef as sfd
sf, version = sfd.rxml.xml_ptrs(‘stateframe_v32.00.xml’)
brange, outlist = sfd.sfdef(sf)
sfd.startbyte(outlist)
tbl = sfd.outlist2table(outlist,version)
</pre>

This last line prints the table to the screen and also creates the contents of the table as commands for input to SQL. To upload the table in SQL and activate it, the commands are:
<pre>
for line in tbl:
cursor.execute(line)
cursor.execute(“update StateFrameDef set status=1 where Version=’” +
str(int(version)) + ”’”)
</pre>

=== To close a connection ===

Once a connection cxnx has been created, and a cursor has been defined, do the following to release them:
<pre>cursor.close()
del cursor
cnxn.close()
</pre>

=== To clear all tables and start from scratch ===

The SQL tables that describe each version of the stateframe or scanheader are called (case-insensitive) StateFrameDef and ScanHeaderDef. If somehow these get confused (as in entering a line that is already in the table), any previously backed-up tables can be restored using:
<pre>
cursor.execute(“ov_fTEST_DefRestore”) </pre>

However, if no appropriate backup exists, the tables need to be cleared and reloaded. This is a very quick process, luckily. To empty the “definition” tables, execute the following:
<pre>cursor.execute(“ov_fTEST_DefTruncate”)</pre>

To reload them, it is intended to have a single function (not available yet):
<pre>reload_deftables(),</pre>

which will clear the tables and reload them automatically. Right now, there is a routine that does this for one file:
<pre>
flag = load_deftable(xml_file),</pre>

which returns True if successful, or False if an error. If a definition for the specified version already exists in the table, a warning is generated and the table is not redefined, but the routine returns True. This avoids trying to redefine the table and thus messing it up. The <code>reload_deftables()</code> routine will just clear the tables, and then take all xml files in a directory and repeatedly call <code>load_deftable()</code>.

=== Getting data across version boundaries ===

The data for each version of the stateframe appears in unique tables, so that the information for one period of time may be in, for example, fV32_vD15, while for the next adjacent time it is in fV35_vD15 (no data were recorded for versions 33-34). If one wants to get data that spans these two tables, one would use the following query (this example is for Ante_Cont_Elevation1):
<pre>
cursor.execute(“””select Timestamp, Ante_Cont_Elevation1
from fV32_vD15 a where (a.[i15] % 15) = 0
union all
select Timestamp, Ante_Cont_Elevation1
from fV35_vD15 b where (b.[i15] % 15) = 0
order by TimeStamp”””)
</pre>

Note that it is necessary to include in the select list the column (TimeStamp in this case) that is to be used for ordering the data. Otherwise one gets a cryptic error message [42000] ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. Once the data are selected, the following lines will plot them:
<pre>
rows = cursor.fetchall()
elev = zeros(len(rows),'float')
times = zeros(len(rows),'float')
for i,x in enumerate(rows):
times[i], elev[i] = x
plot(times-times[0],elev/10000.,'.')
</pre>

=== New python code for accessing the database ===

There is a module called dbutil that contains (and will further be developed) routines to access the database. The two current routines are:
* <code>cursor = get_cursor()</code> Opens the database and returns a cursor for access to it.
* <code>mydict = get_dbrecs(cursor, version, dimension, timestamp, nrecs)</code> Takes as input an open cursor and version, dimension, timestamp and nrecs, and returns a dictionary from the table indicated by the version and dimension, starting at timestamp, and having nrecs entries. The data in each mydict key has dimensions of dimension x nrecs.

Stateframe Database

2016-09-20T18:45:47Z

Mlukicheva: Created page with " == Setup and Initial Test of Python Access to Existing Database == === Introduction === This document describes the initial setup and access of the EOVSA Stateframe databa..."

== Setup and Initial Test of Python Access to Existing Database ==

=== Introduction ===

This document describes the initial setup and access of the EOVSA Stateframe database from Python. The original version of this document described the case of a database called “eOVSA05” that existed on a machine named “GARY-FY13NB” (laptop). That connection string for connecting from the laptop was:
<pre>
cnxn = pyodbc.connect(“DRIVER={SQL Server}; SERVER=GARY-FY13NB; DATABASE=eOVSA05;Trusted_connection=yes;")
</pre>

However, some adjustments were needed to allow connection to the actual server machine (EOVSASQL), using an IP address and “SQL Authentication.” The trick was to create a user on the server in the SQL Server Management Studio (user name is <code>‘aaa’</code>, password is <code>‘I@bsbn2w’</code>), and then use the server IP address (currently ‘128.235.89.168’) on port 1433. This port had to be opened in the server’s Windows Firewall. The new connection string is:
<pre>
cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06;UID=aaa;PWD=I@bsbn2w;")
</pre>

The other commands have been updated for the current situation, also.
As of 2014-Sep-30, the SQL Server has been set up at OVRO and the connection string from Helios is:
<pre>
cnxn = pyodbc.connect("DRIVER={FreeTDS}; SERVER=192.168.24.106,1433; DATABASE=eOVSA06;UID=aaa;PWD=I@bsbn2w;")
</pre>

=== Initial setup of database access software ===

The Python library to access Microsoft SQL Server is called pyodbc. This describes downloading the library and integrating it with an existing '''python2.76''' installation, making a first connection to the database, and executing a query. The query assumes that database “eOVSA06” has a populated table “fV26_vD15.”

1. First install '''pyodbc''' ([https://code.google.com/p/pyodbc/downloads/list]). I installed '''pyodbc-3.0.7.win-amd64-py2.7.exe'''.

2. Start '''ipython''' and type
<pre>import pyodbc</pre>
3. Connect to database with
<pre>cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06; UID=aaa; PWD=I@bsbn2w;")</pre>
4. Get a 'cursor' to the database:
<pre>cursor = cnxn.cursor()</pre>
5. Send an SQL query:
<pre>cursor.execute("select top 16 * from fV26_vD15 order by Timestamp")</pre>
6. Fetch the data returned by the query
<pre>rows = cursor.fetchall()</pre>
7. rows now contains a list of 16 rows.

Note that setup on Linux (Ubuntu) is quite a bit more complicated. Here are the steps:

1. Download '''pyodbc-3.0.7.zip''' from the above link (or copy from '''helios.solar.pvt''').

2. Install unixODBC and FreeTDS packages:
<pre>sudo apt-get install unixODBC-dev freetds-dev freetds-bin tdsodbc</pre>
3. Edit '''/etc/freetds/freetds.conf''' (or copy from helios) to change the last 4 lines to
<pre>
[sqlserver]
host = sqlserver.solar.pvt
port = 1433
tds version = 11.0
</pre>
4. Edit file '''/etc/odbc.ini''' (or copy from helios) to contain:
<pre>
[sql]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver.solar.pvt
Database = eOVSA06
</pre>
5. Edit file '''/etc/odbcinst.ini''' (or copy from helios) to contain:
<pre>
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
</pre>
6. Unzip the zip file in step 1, cd to the directory '''pyodbc-3.0.7''', and type:
<pre>
python setup.py build
sudo python setup.py install
</pre>

From there, use the connection string for Linux shown in the introduction section, and proceed from there.

=== Examples of returned queries ===

<pre>
cursor.execute(“select top 2 * from fV26_vD1 order by Timestamp”)
rows = cursor.fetchall()
</pre>

Returns the entire “dimension-1” data for the first two entries in the table, ordered by ''Timestamp''. The contents can be accessed one row at a time (<code>rows[0]</code> and <code>rows[1]</code> in this case), and the entry names can be listed with <code>rows[0].cursor_description</code>. One can access the value of, say, the current temperature via <code>rows[0].Sche_Data_Weat_Temperature</code>:
<pre>
cursor.execute(“””select top 20 * from fV26_vD15 a where (a.[I15] % 15) = 0 order by Timestamp”””)
rows = cursor.fetchall()
</pre>

Returns the first 20 rows for Antenna 1 [via (<code>a.[Index] % 15) = 0</code>] of the dimension-15 table, ordered by ''Timestamp''. One can check that these are all for the same antenna, for example, by checking one of the pointing coefficients using the Python code:
<pre>
for row in rows: print row.Ante_Cont_PointingCoefficient2</pre>

=== Create a new StateFrameDef Table entry ===

Whenever a new version of the stateframe is created due to some change in the content of the stateframe, a new StateFrameDef table entry must be created. This is accomplished via a set of SQL commands, like:
<pre>cursor.execute(“insert into StateFrameDef (Status, Version, Dimension, DataType, FieldBytes, DimOffset, StartByte, FieldNum, FieldName)
values ( 0, 15, 15, 'u16', 2, 28, 759, 1, 'DCM_Slot')”)
</pre>

and so on for the entire new table. Once all of the rows of the table are entered, the command to create the tables for that version is:
<pre>
cursor.execute(“update StateFrameDef set status=1 where Version=’15’”) </pre>

NB: Once a stateframedef entry is entered, it cannot be entered again within causing unrecoverable errors in the table. See below for description of procedure to clear all of the tables and start ove

=== Complete process of inserting a new binary record ===

The structure of binary data for a given stateframe version is encoded in its XML file with the name ‘stateframe_vxx.00.xml’, where xx is the version number, i.e. 26. The binary data has to be rearranged via a routine in '''stateframedef.py''' called <code>transmogrify()</code>. A complete recipe for reading and inserting data from a stateframe log file (‘sf_20140205_v26.0.log’ in this example), is as follows:
<pre>
import stateframedef as sfd
import pyodbc
sf, version = sfd.rxml.xml_ptrs(‘stateframe_v26.00.xml’)
brange, outlist = sfd.sfdef(sf)
f = open(‘sf_20140205_v26.0.log’,’rb’)
buf = f.read(32)
recsize = sfd.struct.unpack_from(‘i’, buf, 16)[0]
f.close()
cnxn = pyodbc.connect("DRIVER={SQL Server};SERVER=128.235.89.168,1433;
DATABASE=eOVSA06;UID=aaa;PWD=I@bsbn2w;")
cursor = cnxn.cursor()
with open(‘sf_20140205_v26.0.log’, ‘rb’) as f:
bufin = f.read(recsize)
bufout = sfd.transmogrify(bufin, brange)
try:
cursor.execute(‘insert into fBin (Bin) values (?)’,
pyodbc.Binary(bufout))
except:
pass
cnxn.commit()
</pre>

The execute line may return an error and fail, of course, especially if the data have previously been inserted, so it is enclosed in a try: except: clause so that everything does not immediately stop. Error checking would go into the except: clause.

=== Procedure to create the information for a new stateframedef table ===

The Python code in '''stateframedef.py''' does all of the manipulation related to creating StateFrameDef (and also ScanHeaderDef) tables as well as converting stateframe (or scanheader) binary data to the database data. For various reasons involving the internals of SQL, it is necessary to reorder certain data (two-dimensional arrays) in the stateframe and scanheader before they can be saved in the SQL database. These special cases are handled in the bowels of '''stateframedef.py'''’s <code>walk_keys()</code> routine.
To create a stateframedef table from a new stateframe xml file, e.g. stateframe_v32.00.xml, do:
<pre>
import stateframedef as sfd
sf, version = sfd.rxml.xml_ptrs(‘stateframe_v32.00.xml’)
brange, outlist = sfd.sfdef(sf)
sfd.startbyte(outlist)
tbl = sfd.outlist2table(outlist,version)
</pre>

This last line prints the table to the screen and also creates the contents of the table as commands for input to SQL. To upload the table in SQL and activate it, the commands are:
<pre>
for line in tbl:
cursor.execute(line)
cursor.execute(“update StateFrameDef set status=1 where Version=’” +
str(int(version)) + ”’”)
</pre>

=== To close a connection ===

Once a connection cxnx has been created, and a cursor has been defined, do the following to release them:
<pre>cursor.close()
del cursor
cnxn.close()
</pre>

=== To clear all tables and start from scratch ===

The SQL tables that describe each version of the stateframe or scanheader are called (case-insensitive) StateFrameDef and ScanHeaderDef. If somehow these get confused (as in entering a line that is already in the table), any previously backed-up tables can be restored using:
<pre>
cursor.execute(“ov_fTEST_DefRestore”) </pre>

However, if no appropriate backup exists, the tables need to be cleared and reloaded. This is a very quick process, luckily. To empty the “definition” tables, execute the following:
<pre>cursor.execute(“ov_fTEST_DefTruncate”)</pre>

To reload them, it is intended to have a single function (not available yet):
<pre>reload_deftables(),</pre>

which will clear the tables and reload them automatically. Right now, there is a routine that does this for one file:
<pre>
flag = load_deftable(xml_file),</pre>

which returns True if successful, or False if an error. If a definition for the specified version already exists in the table, a warning is generated and the table is not redefined, but the routine returns True. This avoids trying to redefine the table and thus messing it up. The <code>reload_deftables()</code> routine will just clear the tables, and then take all xml files in a directory and repeatedly call <code>load_deftable()</code>.

=== Getting data across version boundaries ===

The data for each version of the stateframe appears in unique tables, so that the information for one period of time may be in, for example, fV32_vD15, while for the next adjacent time it is in fV35_vD15 (no data were recorded for versions 33-34). If one wants to get data that spans these two tables, one would use the following query (this example is for Ante_Cont_Elevation1):
<pre>
cursor.execute(“””select Timestamp, Ante_Cont_Elevation1
from fV32_vD15 a where (a.[i15] % 15) = 0
union all
select Timestamp, Ante_Cont_Elevation1
from fV35_vD15 b where (b.[i15] % 15) = 0
order by TimeStamp”””)
</pre>

Note that it is necessary to include in the select list the column (TimeStamp in this case) that is to be used for ordering the data. Otherwise one gets a cryptic error message [42000] ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. Once the data are selected, the following lines will plot them:
<pre>
rows = cursor.fetchall()
elev = zeros(len(rows),'float')
times = zeros(len(rows),'float')
for i,x in enumerate(rows):
times[i], elev[i] = x
plot(times-times[0],elev/10000.,'.')
</pre>

=== New python code for accessing the database ===

There is a module called dbutil that contains (and will further be developed) routines to access the database. The two current routines are:
* <code>cursor = get_cursor()</code> Opens the database and returns a cursor for access to it.
* <code>mydict = get_dbrecs(cursor, version, dimension, timestamp, nrecs)</code> Takes as input an open cursor and version, dimension, timestamp and nrecs, and returns a dictionary from the table indicated by the version and dimension, starting at timestamp, and having nrecs entries. The data in each mydict key has dimensions of dimension x nrecs.

Calibration Database

2016-09-20T17:10:11Z

Mlukicheva: Created page with "== Description and Use of the EOVSA Calibration Database == === Background === We have created a general-purpose table in the SQL-Server database ''eOVSA06'', named ''abin'..."

== Description and Use of the EOVSA Calibration Database ==

=== Background ===

We have created a general-purpose table in the SQL-Server database ''eOVSA06'', named ''abin'', which is used to hold binary calibration data in a general format given by an XML format string in the same table. The table is meant to be extendable to any calibration type, although it remains to be seen whether it is general enough to handle all use cases. This document describes the scheme, the format of the ''abin'' entries, and the list of currently defined binary types (this will have to be updated on a regular basis as new definitions are added).

=== Description of the General Scheme ===

The general idea is to create entries into the ''abin'' table that are self-describing and completely general.

The table columns are:

<pre>['Bin';, 'Timestamp', 'Version', 'Id', 'Description']</pre>

The ''Id'' number is auto-incremented to be unique to each record, and is never set by the user. Each type definition will appear in the table with an ''n''.0 ''Version'' number (float), and whenever it is updated, a new n.0 record is written with the current ''Timestamp''. This provides a history, with the corresponding ''Timestamp'' giving the start timerange of applicability (actually regretting that this key is called ''Version'', since its purpose could more accurately be referred to as the calibration ''Type''). To distinguish between this key and the true versions given within the type definition record, the latter is referred as the “internal version.” The ''Bin'' column contains an XML data description that is to be used to decode the data. The ''Version'' (type) number ''n'' will be unique for each calibration type, so that records with ''Version'' = 1.0, for example, will always contain the latest definition for a particular type of data defined as type 1 (the type of calibration data is further described in the ''Description'' column). The type definitions, as well as helper routines for creating, reading, and writing records is found in the Python module '''cal_header.py'''.

The XML data itself, found in the ''Bin'' column of a ''Version n''.0 record, contains an internal version variable that gives a further record of the version of the XML format. As a concrete example, the latest ''Version'' 4.0 (delay centers) calibration will contain an XML string that includes its own internal version variable, say its value is 2.1, that would distinguish it from an earlier type 4.0 version. This internal version number is used by the <code>send_xml2sql()</code> to determine whether a definition defined in '''cal_header.py''' has changed and needs to be written to the ''abin'' table.

After (never before) the defining ''n''.0 record is written, subsequent records of that type can be written containing the binary calibration data, which will be decoded using the defining XML string. Thus, after writing the latest ''Version'' 4.0 format record, subsequent records with Version 4.1 (type 4, with internal version 1.0) can be written that will be decoded using that latest 4.0 XML string. Other versions, e.g. 4.2 (internal version 2.0) etc., could in principle be written, although it is not clear why that would be needed (perhaps an important change to the contents, but without a corresponding change to the format, could be indicated with a new 4.x version number). Thus, the latest <code>delay_centers</code> entry can be read with a query like:

<pre>SELECT TOP 1 * FROM abin WHERE Version > 4.0 AND Version < 5.0 ORDER BY Timestamp DESC</pre>

while the <code>delay_centers</code> entry for a given ''Timestamp tstamp'' can be read with a query like:

<pre>SELECT TOP 1 * FROM abin WHERE Version > 4.0 AND Version < 5.0 AND Timestamp <= tstamp ORDER BY Timestamp DESC</pre>

In testing this, it was discovered that binary records returned by such a query are limited in length to 4096. To get an arbitrarily long record, one must prepend the string “SET TEXTSIZE 2147483647” to the query. Note that such details are already handled by the helper routine <code>read_cal()</code> in '''cal_header.py'''. As new calibration types are created, their definitions will be added to '''cal_header.py''', both by updating the <code>cal_types()</code> routine to add the new type’s ''Version'' number and ''Description'', and by adding a two writing routines—one called <code>type>2xml()</code> routine that returns the XML description of the data (later written into the database by <code>send_xml2sql()</code>), and one called <code><type>2sql()</code> that converts the calibration data to a binary buffer and writes it into the database, where <code><ype></code> is a hopefully rational name for the new type. As new formats for an existing type are created, it should be fine to simply update the <code>cal_types()</code> routine to change the description (if needed) and update the format embodied in the <code><type>2xml()</code> and <code><type>2sql()</code>
routines. It should not be necessary to keep the old format, since the database itself already forms a history. Of course, any previous versions of the '''cal_header.py''' file will also be kept in the '''github''' versioning system.

=== Currently-Defined Types ===

This section will hopefully be updated whenever new types are added, to provide a list of currently-defined calibration data types. However, it is probably wise to consult the '''cal_header.py''' file to verify the current definitions. Here is the verbatim return statement from <code>cal_types()</code>:

<pre>
return {1:['Total power calibration (output of SOLPNTCAL)','proto_tpcal2xml',1.0],

2:['DCM master base attenuation table [units=dB]','dcm_master_table2xml',1.0],

3:['DCM base attenuation table [units=dB]','dcm_table2xml',1.0],

4:['Delay centers [units=ns]','dlacen2xml',1.0]}
</pre>

To add a new type, simply add another entry to this dictionary, with a unique type number, and a three-element list whose first element is the ''Description'' string, second element is the string name of the routine to call to create the XML definition (returns a binary buffer ready for writing to the ''abin'' table), and third element is the version number. Then add the corresponding <code>type>2xml()</code> routine defining the format of the binary data, and the <code><type>2sql()</code> routine that converts the calibration data to a corresponding binary buffer. The '''cal_header.py''' module includes a routine <code>send_xml2sql()</code>, which can be called at any time and checks the latest version of each calibration type in the ''abin'' table, and updates any that have changed (i.e. has a different version number than the latest one in the table). The return statement of each <code><type>2sql()</code> routine should call <code>write_cal()</code> to actually write the binary buffer to the database, so that a single call to the routine does everything. It is anticipated that routines that create the calibration data will call the corresponding <code><type>2sql()</code> routine directly.

To change an existing type, change the description in the cal_types() routine, if desired, and change the corresponding <code><type>2xml()</code> and <code><type>2sql()</code> routines to create the new definition. It should not be strictly necessary to increment the version number that will be written into the XML description, unless two active versions are needed at the same time. It is up to the programmer to decide whether to increment the version’s minor (fractional) or major (integer) part of the version number, since only its uniqueness is required.

=== Reading Back Data for a given Calibration Type ===

If the above scheme is followed, it should be possible to use a single, general routine to find and successfully read the binary calibration data for a given time. The <code>read_cal()</code> routine in the '''cal_header.py''' module does this, returning a Python dictionary and the binary buffer. The dictionary contains key, value pairs defining the variable names (keys) and the types and start location (values) in the binary buffer. To use these returned entities, one employs the <code>extract()</code> routine defined in the '''stateframe.py''' module, e.g. to read the total power (type 1) calibration factors for antenna 5 on April 3, 2016 as of 20:00 UT:

<pre>
import stateframe, util

tp, buf = read_cal(1, t=util.Time(‘2016-04- 03 20:00’))

calfac = stateframe.extract(buf,tp[‘Antenna’][4][‘Calfac’])
</pre>

Here the index for antenna 5 is 4, since it is a zero-based index. Note that to read the values for the current time, the input ''t'' can be omitted.