ouhscbbmc / dbmonitor Goto Github PK
View Code? Open in Web Editor NEWMonitor characteristics of databases involving the BBMC
License: GNU General Public License v2.0
Monitor characteristics of databases involving the BBMC
License: GNU General Public License v2.0
@thomasnwilson, have you done anything in PHP or REDCap to send an email with code? Did you ever get is working R? If so, I'd like to know the settings that made it work.
I'm thinking that's a critical feature we'll need for this. I'm not seeing anything about email from a REDCap plugin.
A paragraph near the front of ch 14 in our PHP makes it sound easy. But doesn't enough details for me (who was just skimming).
Implement this: OuhscBbmc/RedcapGovernanceDocs#12 (comment)
(also see https://github.com/OuhscCcanMiechvEvaluation/MReporting/issues/277)
I just wrote this email to the first users. I'm posting it here because I think it's a good start to describe the purpose of this tool to people who's databases we might use it for. @bard1536 & @thomasnwilson, feel free to make comments/questions.
I’m adding the four of you to a REDCap project, so you’ll probably get a funny email in the next few minutes.
I’m not moving any of your database from SQL Server to REDCap. The four things REDCap will be doing are:
Storing row counts (not the actual data) of each table in your database. The database will be automatically & periodically polled, and the row counts will be deleted. This longitudinal record will help detect if (a) a large batch of records was deleted, or (b) not as many records were added as you expected.
Storing the location of where to save the backups. As I’ve discussed with some of you, I want a backup system that’s independent of Peds IS. These backups will be stored as (compressed) CSV files. Anytime you like, you can open these CSVs (with something like Excel) and verify the backups exist. This strategy won’t work for huge databases, but I don’t expect that your specific one will cause any strains.
Providing a GUI where you can initiate either step above. The new software will still monitor the record counts and backup to CSV daily. But if there are times when you want run an explicit backup, you should be able to log into REDCap and press a button to initiate it. This feature won’t be complete for another few weeks.
Providing a place to see the longitudinal reports of the row counts and the success/failure of the backups.
@thomasnwilson, I'm trying to get plugins working for rcapdev. Did you do anything to enable plugins on miechvprojects?
I don't see anything in the settings, or in this link: https://iwg.devguard.com/trac/redcap/wiki/Plugins
I'm wondering if it has something to do with the URL configuration warnings we're getting from that server (https://github.com/OuhscCcanMiechvEvaluation/MReporting/issues/252). I'm emailing Cliff now to see if there's any progress.
The heart of the automation will
record_id
and add that number to the records about to be added@thomasnwilson, thanks for agreeing to do this. As of right now, I think this project needs just four columns.
record_id
: autonumber field (recommended by @thomasnwilson )database
: a text fieldtable
: a text fieldprobe_date
: a date and time fieldrow_count
: an integer fieldPlease call my house if you have questions.
Spun-off from #1
The second/parent project is more like demographic information, where there's just one record per database. Currently, I'm thinking:
roster_id
a primary keydatabase
a text field for the nameserver
a text field for the name of the serverdsn
a text field for the name of the ODBC DSN on the tool's local machinename_pi
the principle investigator'semail_pi
their emailname_manager
the person who actually knows what's going onemail_manager
their nameserver_type
right now, it we'll support only SQL Server. Maybe Access or MySQL later. The backup tool (which is probably written in R) will need to know, because they'll be handled differentlymonitor_row_count
a boolean field for whether the database's tables' rows should be counted.should_backup
a boolean field for whether the tool should still be backing up the database@bard1536, If you're not using RODBC::odbcDriverConnect
, consider doing it in the future. I've been using RODBC::odbcConnect
, which requires that you manually set up a DSN on each machine that calls it. Like the DSNs we set up, it uses the Window's credentials (that's what the 'trusted_connection' part means).
This will save a little work the way we typically use it. It could save a lot of work if there are a lot of machines using it.
There are some benefits of the manual DSN approach. I like that a GUI is used to point to the database, and then test the connection --independent of any R code.
Close the issue when you've read it.
http://stackoverflow.com/questions/5720508/sql-server-rodbc-connection
Here's the core line:
dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')
Create a basic script that backs up the tables to csv, in a location designated in the roster.
In the beginning of the project, the SQL code was double/triple counting some rows. Export from REDCap into R, correct the counts, and import them back into R.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.