01:45 pm
[Link] |
Geek Rants and code sharing....
Did you know there is no "GRANT ALL ON ALL TO user" possible in the SQL language standard? So, if you have a new person you want to help you start managing a database you can't just add him to a database and have him move forward. Sure, I get it, there is a risk that it's just too powerful of a command, but hell, warn me if I try to execute it rather then making me do it manually for every damned table, you'd think the standards were written by socialists "You aren't smart enough to make your own choices so we will just take them away". For those of you who find this via google and are faced (like we were) with a database with hundreds of tables that a new user needs access to, here is a quickie perl script to help you, it's been tested with PostgreSQL, if you use it anywhere else you take your own risks.... for that matter... I accept no risks at all for anything you do I'm just trying to be a good guy by sharing what I wrote don't go getting mad at me if it does something you don't want....
|
#!/usr/local/bin/perl
print "\n\nThis program will grant all on all for a given database if you don't want to do this hit Ctrl-C now!\n\nKeep in mind also that the user must already exist in the database,\nto do that login to the database and use 'CREATE USER username;'\n\n";
$::DBNAME = &getIn("We assume you are currently logged into the database server, What is the name of your database?");
$::GRANTUSER = &getIn("What is the username of the person you are granting all on all to?");
&getIn("Will grant all privs to $::GRANTUSER on the local database $::DBNAME (hit enter to continue)");
use DBI;
### Connect to the database
my $dbh = DBI->connect ( "DBI:Pg:dbname=$::DBNAME", "", "");
### Create a new statement handle to fetch table information
my $tabsth = $dbh->table_info();
### Iterate through all the tables...
while ( my ( $qual, $owner, $name, $type ) = $tabsth->fetchrow_array() ) {
next if $owner ne 'public';
next if $name eq 'grelib_tables';
### The table to fetch data for
my $table = $name;
### The SQL statement to fetch the table metadata
my $statement = "GRANT ALL ON $table TO $::GRANTUSER";
print $statement,"\n";
### Prepare and execute the SQL statement
my $sth = $dbh->prepare( $statement );
$sth->execute();
}
sub getIn {
print $_[0],": ";
my $x = ;
chomp $x;
return $x;
}
Now, for the next little annoying thing.... Did you know that Microsoft Access allows Spaces in their column names? WTF?!?!?!?! What crackhead thought that was a good idea? I've never seen a database anywhere before with columns that had spaces in the names... And this idiot contractor did exactly that. What was the phone number for that truck driving school again????
Anyways, I wrote this cute little windows application for Eccolabs that takes an excel spreadsheet and imports it into a Microsoft Access DB, I was neither the designer of the XLS or the DB but had to deal with them both. Oh, and another stupid thing M$ does with Excel, they store their dates as integers which is number of days since Jan 1, 1900 counting for leap years but assuming 2 leap years that did not exist because they were trying to stay consistant with a bug in Lotus 1,2,3.... I finally found a page to tell me how the dates thing worked.... Anyways.... Real quick, just to share my creation because I will probably never use it again I will give you the code for this little C# .Net windows application that will import an excel spreadsheet (xls) into a Microsoft Access Database (mdb)....
Here is the setup: you have an M$ access DB with tables that you want to import data into, each one has a unique key called "Lead_ID" (if you are adapting my code you will have to deal with lead_id being hardcoded throughout it. Then you have an xls with several sheets, for each sheet you have a separate sheet (probably hidden) that is called Import_tablename and just to be cool you've named all your tables with the convention of _Form at the end of their names. Your import forms have key value pairs across where the first row is all keys that match the database and the second row is all values for those keys.
You run this program (browse to the mdb, then browse to the xls) and it will iterate through the sheets until it finds sheets named Import_x where it assumes x is the table name, then if x ends in _f it assumes you meant _form and ran out of space (because thats how the other consultant made his xls and that had already been released so I had no control of it). Then it goes through and gathers the data turning it into an Insert Query and an Update Query deciding which query to use based on if there is already LEEAD_ID in the table. It then either Inserts or Updates and goes on to the next one.
This should of been pretty simple but it was a pain in the butt.... it also translates "Needs Booster" to "Needs_Booster" (stupid space), and Connection to myConnection (reserved word) again because the xls was already released into the wild but the database was in our control. I don't claim this code to be pretty but it's a real good lesson in convoluted it can be to work with M$ products where you have to interpret their data.....
The Design form has two buttons ("browsebutton" and "button1"), two text fields ("xlsfiledisplay" and "databasedisplay"), a check box ("verboselogging"), and a multi line text field ("userfeedback")....
Lastly, I don't claim this to be pretty code, I claim it to be 2am code.... here is my mess....
|
/*
* Created by SharpDevelop.
* User: Gypsy Rogers
* Date: 10/29/2007
* Time: 6:30 PM
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Data.OleDb;
using System.Data;
using System.Collections.Specialized;
using System.Collections;
using System.Collections.ObjectModel;
namespace PuritanImporter
{
///
/// Description of MainForm.
///
public partial class MainForm
{
public OleDbConnection aConnection;
public bool verboseloggingon = false;
[STAThread]
public static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new MainForm());
}
public MainForm()
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
if ((databasedisplay.Text != null) && (databasedisplay.Text != "")){
aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasedisplay.Text);
}
//
// TODO: Add constructor code after the InitializeComponent() call.
//
}
void BrowsebuttonClick(object sender, System.EventArgs e)
{
if ((databasedisplay.Text != null) && (databasedisplay.Text != "")){
OpenFileDialog datadlg = new OpenFileDialog();
datadlg.Filter = "Excel Files (*.xls)|*.xls";
DialogResult res = datadlg.ShowDialog();
if(res == DialogResult.OK && datadlg.FileName.EndsWith(".xls")){
this.Cursor = Cursors.WaitCursor;
xlsfiledisplay.Text = datadlg.FileName;
logme("Opening XLS file: " + datadlg.FileName,1);
Excel.Application ExcelObj = new Excel.Application();
ExcelObj.Workbooks.Open(datadlg.FileName, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets = ExcelObj.Worksheets;
try { aConnection.Open(); }
catch(OleDbException err){
MessageBox.Show("Error: " + err.Errors[0].Message);
}
foreach(Excel.Worksheet mysheet in ExcelObj.Worksheets){
logme("\r\n\r\n");
if ( Regex.IsMatch(mysheet.Name,"Import_")){
logme("found an import worksheet named " + mysheet.Name, true);
String tablename = mysheet.Name.Substring(7); //substr takes off the import_
//tablename = Regex.Replace(tablename,"Import_",""); //removes Imort_ from the name of the table
if (Regex.IsMatch(tablename,"_F$")){ tablename = tablename + "orm";} //fixes truncated names
logme("\tTable for Import is " + tablename);
// lets get field types
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * from " + tablename, aConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, "Members");
DataTable item = ds.Tables[0];
//logme("Table name: " + item.TableName);
//logme("Its columns are:");
System.Collections.Hashtable myTableDeff = new System.Collections.Hashtable();
foreach (DataColumn tcol in item.Columns)
{
//logme("\t" + tcol.ColumnName + ":" + tcol.DataType);
myTableDeff.Add(tcol.ColumnName,tcol.DataType);
//logme("\t" + tcol.ColumnName + ":" + myTableDeff[tcol.ColumnName].ToString());
}
Excel.Range range = mysheet.get_Range("A1", "DZ2");
System.Array myvalues = (System.Array)range.Value2;
String mykey = "";
String myvalue = "";
//Here we need to iterate through the colums and build our query strings as well as verify
//that the key doesn't already exist
int col = 1;
int numkeys = 1;
String strupdate = "update " + tablename + " set ";
String strinsert = "insert into " + tablename + " (";
String strinsert2 = ") values (";
String checkkey = "";
String coma = "";
OleDbCommand myUpdateCommand = new OleDbCommand(strupdate, aConnection);
OleDbCommand myInsertCommand = new OleDbCommand(strinsert, aConnection);
do
{
try {
mykey = (string)myvalues.GetValue(1,col).ToString();
if (myTableDeff[mykey].ToString() == "System.DateTime"){
//myTableDeff[mykey] = "System.String";
//DateTime.ParseExact(value, , CultureInfo.InvarianCulture)
//System.DateTime startdate = new System.DateTime(1900,1,1);
string strplusdays = (string)myvalues.GetValue(2,col).ToString();
//double plusdays = (double)Convert.ToDouble(strplusdays);
//startdate = startdate.AddDays(plusdays);
//myvalue = startdate.ToString();
myvalue = ExcelSerialDateToDMY(Convert.ToInt32(strplusdays));
//logme("+++DateTime: key: " + mykey + " strplusdays: " + strplusdays + " plusdays: " + plusdays + " myvalue: " + myvalue);
} else {
myvalue = (string)myvalues.GetValue(2,col).ToString();
}
//map bad keys from distributed forms
switch (mykey){
case "Connection":
mykey = "myConnection";
break;
case "Need Booster":
mykey = "Need_Booster";
break;
}
//logme("\t\t\t" + mykey + " : " + myvalue);
col++;
} catch { mykey = "";}
if (mykey == "Lead_ID"){
string sqlcmd = "select LEAD_ID from " + tablename + " where LEAD_ID='" + myvalue + "'";
logme("sqlcmd: " + sqlcmd);
try {
OleDbCommand aCommand = new OleDbCommand(sqlcmd, aConnection);
OleDbDataReader aReader = aCommand.ExecuteReader();
while (aReader.Read()){
//checkkey = aReader.GetInt32(0).ToString();
checkkey = aReader.GetValue(0).ToString();
}
aReader.Close();
logme("checkkey: " + checkkey);
}
catch(OleDbException dbe)
{
logme("checkkey Error: {0}" + dbe.Errors[0].Message, true);
}
}
if ((numkeys<31) && (mykey != "") && (mykey != null) && myTableDeff.ContainsKey(mykey) && isKnownType(myTableDeff[mykey].ToString())){
strupdate = strupdate + coma + mykey + " = @" + mykey;
//strupdate = strupdate + coma + mykey + " = '" + myvalue + "'";
strinsert = strinsert + coma + mykey;
strinsert2 = strinsert2 + coma + "@" + mykey + " ";
//strinsert2 = strinsert2 + coma + "'" + myvalue + "'";
coma = ",";
//OleDbType.Char
//myUpdateCommand.Parameters.Add(mykey, System.Data.OleDb.OleDbType( getMyType(myTableDeff[mykey])), myvalue.Length, myvalue);
//myInsertCommand.Parameters.Add(mykey, System.Data.OleDb.OleDbType( getMyType(myTableDeff[mykey])), myvalue.Length, myvalue);
myUpdateCommand = myAddParam(myUpdateCommand,myTableDeff,mykey,myvalue);
myInsertCommand = myAddParam(myInsertCommand,myTableDeff,mykey,myvalue);
}
numkeys++;
} while ((mykey != "") && (mykey != null));
myUpdateCommand.CommandText = strupdate + " where LEAD_ID='" + checkkey + "'";
myInsertCommand.CommandText = strinsert + strinsert2 + ")";
// if (tablename == "tblLead_Form"){
// myUpdateCommand.CommandText = "update tblLead_Form set Customer_Name = @Customer_Name where LEAD_ID='20071029182048'";
// myUpdateCommand.Parameters.Add("@Customer_Name", "TryThisNow");
// }
if (checkkey != "") {
logme("using Update for import",true);
try { myUpdateCommand.Prepare();} catch (OleDbException preperr) {logme("preperr: " + preperr.Errors[0].Message,true);}
try { myUpdateCommand.ExecuteNonQuery();} catch (OleDbException enqerr) {logme("enqerr: " + enqerr.Errors[0].Message,true);logme("update command: " + myUpdateCommand.CommandText,true);}
} else {
logme("using Insert for import",true);
try { myInsertCommand.Prepare();} catch (OleDbException preperr) {logme("preperr: " + preperr.Errors[0].Message,true);}
try { myInsertCommand.ExecuteNonQuery();} catch (OleDbException enqerr) {logme("enqerr: " + enqerr.Errors[0].Message,true);logme("insert command: " + myInsertCommand.CommandText,true);}
}
} else { logme("found worksheet named " + mysheet.Name + " -- Not an Import Worksheet... ignoring");}
}
ExcelObj.Workbooks.Close();
logme("Import Complete\r\n\r\n",1);
this.Cursor = Cursors.Default;
try { aConnection.Close(); }
catch(OleDbException clerr){
MessageBox.Show("Error: {0}", clerr.Errors[0].Message);
}
}
else {MessageBox.Show("File Browse Canceled or a non xls file was selected, no import completed");}
} else {MessageBox.Show("Please select a database before you import an xls");}
}
object getMyType(object systype){
return (getMyType(systype.ToString()));
}
object getMyType(string systype){
object returnobj = null;
switch (systype) {
case ("System.String"):
returnobj = OleDbType.Char;
break;
default:
//logme("WARNING:" + systype + " is not a known type",true);
break;
}
return(returnobj);
}
Boolean isKnownType(string kkey){
switch (kkey){
case "System.String":
return true;
break;
case "System.DateTime":
return true;
break;
case "System.Int32":
return true;
break;
case "System.Boolean":
return true;
break;
case "System.Decimal":
return true;
break;
default:
//MessageBox.Show("An unknown Table Type has been hit!");
return false;
break;
}
}
OleDbCommand myAddParam (OleDbCommand cmdobj, System.Collections.Hashtable tabledeffobj, string key, string val){
string shkey = "@" + key;
switch (tabledeffobj[key].ToString()){
case "System.String":
cmdobj.Parameters.Add(shkey, OleDbType.Char, val.Length, val);
cmdobj.Parameters[shkey].Value = val;
break;
case "System.DateTime":
logme("adding DateTime Key: " + key + " has a value of " + val);
cmdobj.Parameters.Add(shkey, OleDbType.Char, val.Length, val);
cmdobj.Parameters[shkey].Value = val;
break;
case "System.Int32":
cmdobj.Parameters.Add(shkey, OleDbType.Integer, val.Length, val);
cmdobj.Parameters[shkey].Value = val;
break;
case "System.Boolean":
switch (val){
case "0":
cmdobj.Parameters.Add(shkey, OleDbType.Boolean, 5, "0");
cmdobj.Parameters[shkey].Value = false;
break;
case "1":
cmdobj.Parameters.Add(shkey, OleDbType.Boolean, 5, "1");
cmdobj.Parameters[shkey].Value = true;
break;
case "y":
cmdobj.Parameters.Add(shkey, OleDbType.Boolean, 5, "y");
cmdobj.Parameters[shkey].Value = true;
break;
case "n":
cmdobj.Parameters.Add(shkey, OleDbType.Boolean, 5, "0");
cmdobj.Parameters[shkey].Value = false;
break;
case "no":
cmdobj.Parameters.Add(shkey, OleDbType.Boolean, 5, "0");
cmdobj.Parameters[shkey].Value = false;
break;
case "yes":
cmdobj.Parameters.Add(shkey, OleDbType.Boolean, 5, "1");
cmdobj.Parameters[shkey].Value = true;
break;
}
break;
case "System.Decimal":
cmdobj.Parameters.Add(shkey, OleDbType.Double, val.Length, val);
cmdobj.Parameters[shkey].Value = val;
break;
default:
logme("WARNING:" + tabledeffobj[key].ToString() + " is not a known type for colum " + key,true);
break;
}
return(cmdobj);
}
void logme(string datatxt, bool alwaysshow){
//alwaysshow = true; //take this out later
if (alwaysshow == true || verboseloggingon == true){
userfeedback.AppendText(datatxt + "\r\n");
}
}
void logme(string datatxt){
logme(datatxt,false);
}
void logme (string datatxt, int showme){
if (showme >0){
logme(datatxt,true);
}else { logme(datatxt,false);}
}
string ExcelSerialDateToDMY(int nSerialDate)
{
int nDay = 0;
int nMonth = 0;
int nYear = 0;
// // Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
// // leap year, but Excel/Lotus 123 think it is...
// if (nSerialDate == 60)
// {
// nDay = 29;
// nMonth = 2;
// nYear = 1900;
//
// return;
// }
// else if (nSerialDate < 60)
// {
// // Because of the 29-02-1900 bug, any serial date
// // under 60 is one off... Compensate.
// nSerialDate++;
// }
// Modified Julian to DMY calculation with an addition of 2415019
int l = nSerialDate + 68569 + 2415019;
int n = ( 4 * l ) / 146097;
l = l - (( 146097 * n + 3 ) / 4);
int i = (( 4000 * ( l + 1 ) ) / 1461001);
l = l - (( 1461 * i ) / 4) + 31;
int j = (( 80 * l ) / 2447);
nDay = l - (( 2447 * j ) / 80);
l = (j / 11);
nMonth = j + 2 - ( 12 * l );
nYear = 100 * ( n - 49 ) + i + l;
string newdate = nMonth + "/" + nDay + "/" + nYear;
logme("translated date:" + newdate);
return newdate;
}
void VerboseloggingCheckedChanged(object sender, System.EventArgs e)
{
verboseloggingon = true;
userfeedback.AppendText("Verbose Logging Turned on\r\n");
verboselogging.Enabled = false;
}
void UserfeedbackTextChanged(object sender, System.EventArgs e)
{
}
void Button1Click(object sender, System.EventArgs e)
{
//this browses to the database
OpenFileDialog datadlg = new OpenFileDialog();
datadlg.Filter = "Microsoft Access Files (*.mdb)|*.mdb";
DialogResult res = datadlg.ShowDialog();
if(res == DialogResult.OK && datadlg.FileName.EndsWith(".mdb")){
databasedisplay.Text = datadlg.FileName;
if ((databasedisplay.Text != null) && (databasedisplay.Text != "")){
aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasedisplay.Text);
}
}
}
}
}
|
![[info]](http://l-stat.livejournal.com/img/userinfo.gif){kind=small}
hightekvagabond's journal
