Snippets
Every now and again, I put something together that I think "I should remember that" or "Oh god, that's truely awful". I thought I'd have a place here where I can put all of those bits for future reference, probably by myself. These are public domain, feel free to use as you wish.
- SQLite Schema Updater
- Close /* I needed a way to look at an existing SQLite schema, compare it to a text file containing some table definitions and then work out if the two were the same. Here's how I did it. The hashtable 'givenschema' contains each table create statement keyed by the name of the table. */ public static void CheckSchema() { lock(myconnection) { Hashtable p = new Hashtable(); foreach (string key in givenschema.Keys) { p.Clear(); p.Add("@tblname",key); string sql = GetSingle("select sql from sqlite_master where tbl_name=@tblname",p).ToString(); string newsql = givenschema[key].ToString().Replace("%NAME%",key); newsql = newsql.TrimEnd(";".ToCharArray()); if (sql==null) { ExecuteNonQuery(newsql,null); } else if (newsql!=sql) { ArrayList currenttable = new ArrayList(); ArrayList newtable = new ArrayList(); ArrayList commonitems = new ArrayList(); string[] columns = sql.Remove(0,sql.IndexOf("(")+1).TrimEnd(")".ToCharArray()).Split(",".ToCharArray()); foreach (string column in columns) { string trimmedcolumn = column.Trim(); currenttable.Add(trimmedcolumn); } columns = newsql.Remove(0,newsql.IndexOf("(")+1).TrimEnd(")".ToCharArray()).Split(",".ToCharArray()); foreach (string column in columns) { string trimmedcolumn = column.Trim(); newtable.Add(trimmedcolumn); } foreach (string item in currenttable) { if (newtable.Contains(item)) { commonitems.Add(item); } } try { myconnection.Open(); SQLiteTransaction t = myconnection.BeginTransaction(); SQLiteCommand mycmd = myconnection.CreateCommand(); mycmd.CommandText = newsql.Replace(key,key+"_temp").Replace("CREATE TABLE","CREATE TEMPORARY TABLE"); mycmd.ExecuteNonQuery(); System.Text.StringBuilder sb = new System.Text.StringBuilder(); foreach (string item in newtable) { if (commonitems.Contains(item)) { sb.Append(item+","); } else { sb.Append("null,"); } } mycmd.CommandText="INSERT INTO "+key+"_temp SELECT "+sb.ToString().TrimEnd(",".ToCharArray())+" FROM "+key; mycmd.ExecuteNonQuery(); mycmd.CommandText="DROP TABLE "+key; mycmd.ExecuteNonQuery(); mycmd.CommandText=newsql; mycmd.ExecuteNonQuery(); mycmd.CommandText="INSERT INTO "+key+" SELECT * FROM "+key+"_temp"; mycmd.ExecuteNonQuery(); mycmd.CommandText="DROP TABLE "+key+"_temp"; mycmd.ExecuteNonQuery(); t.Commit(); myconnection.Close(); mycmd.Dispose(); t.Dispose(); } catch (Exception) { throw; } finally { myconnection.Close(); } } } } }
- LIMIT statement equivalent in MSSQL 2000
- Close --This query allows you to do in MSSQL what many other RDBs allow you to do very simply: Select a --certain number of rows starting at row x. Normally you'd just do 'SELECT * FROM foo LIMIT x,y'. --Joyously, MSSQL 2000 doesn't support this. So here's how you do it. select * from ( select top @totalnum columns from ( select top @startnum columns from entries p order by @sort ASC ) as newtbl order by @sort DESC ) as newtbl2 order by @sort ASC ;
- Code to detect whether a file is a Lightwave preset.
- Close private static bool IsFilePSTFile(string filename) { bool ret=false; try { FileStream fs = File.OpenRead(filename); BinaryReader br = new BinaryReader(fs); byte[] firstfour = br.ReadBytes(4); br.ReadBytes(4); byte[] label = br.ReadBytes(4); if (fileencoding.GetString(firstfour)=="FORM" && fileencoding.GetString(label)=="PST_") { ret = true; } br.Close(); fs.Close(); } catch (EndOfStreamException) { } catch (System.IO.IOException) { } return ret; }
- Deletes duplicate rows in a table in MSSQL 2005.
- Close --Deletes duplicate entries on a table in MSSQL 2005. Might even work in MSSQL 2000. --Before SELECT * FROM deleteduplicates; --Show the results of attaching row numbers partitioned by id WITH [numberedduplicates](row_number, id, name) AS ( SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id), id, name FROM deleteduplicates ) SELECT * FROM numberedduplicates; --Delete the rows that aren't the first row for a particular id WITH [numberedduplicates](row_number, id, name) AS ( SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id), id, name FROM deleteduplicates ) DELETE FROM numberedduplicates WHERE row_number > 1; --After SELECT * FROM deleteduplicates;
- Thumbnailer code from Picsie - resizes thumbnails according to dimensions pulled from a db
- Close #!/usr/bin/perl ## This was quite clever, it read a bunch of information from the database, ## looked in the directory for what was meant to be going on, and created ## a bunch of thumbnails from this, whilst at the same time pulling EXIF ## information from that to whack in the db. A keeper I think, even though picise is deaded now. use Image::Magick; use DBD::mysql; use DBI; use Image::ExifTool; if (-f "thumbnailer.lock") { print "lock found. Exiting\n"; exit(); } `touch thumbnailer.lock`; $sql_dsn = "{DSN}"; $sql_username = "picsie"; $sql_pwd = "password"; $dbh = DBI->connect($sql_dsn,$sql_username,$sql_pwd); $sth=$dbh->prepare("select name,value from settings"); $sth->execute(); while (my @rows = $sth->fetchrow_array()) { ${$rows[0]} = $rows[1]; } #Delete thumbnails which don't have a valid type or valid image $dbh->do("delete from thumbnails where type not in (select id from tn_resolutions) or image not in(select id from images)"); #Do some tidying up $sth=$dbh->prepare("select id from thumbnails"); $sth->execute(); #Remove dead entries from the db while(my @ids = $sth->fetchrow_array()) { if (!-f $thumbnailsdir.$ids[0].'.jpg') { $dbh->do("delete from thumbnails where id=?",{},($ids[0])); } } opendir(DIR,$thumbnailsdir); @files = grep {-f $thumbnailsdir.$_ } readdir(DIR); closedir (DIR); #remove orphaned thumbnails for(@files) { ($interested,$notinterested)=split(/\./,$_); @num = $dbh->selectrow_array("select count(*) from thumbnails where id=?",{},($interested)); if ($num[0] eq 0) { unlink $thumbnailsdir.$_; } } $sth = $dbh->prepare("select exact,width,height,quality,id from tn_resolutions"); $sth->execute(); while (my @rows2 = $sth->fetchrow_array()) { push @resolutions,[@rows2]; } $sth=$dbh->prepare("select images.id,filename,name from images inner join categories on categories.id=images.category"); $sth->execute(); while(my @rows=$sth->fetchrow_array()) { for(@resolutions) { print "doing $rows[1]\n"; @exists = $dbh->selectrow_array("select count(*) from thumbnails where type=? and image=?",{},($$_[4],$rows[0])); if ($exists[0] eq 0) { $filename=""; while (-f $thumbnailsdir.$filename.'.jpg' || $filename eq "") { $filename=&random_filename_generator; } $image = new Image::Magick; $image->Read($originalsdir.$rows[2]."/".$rows[1]); $width = $image->Get('width'); $height = $image->Get('height'); if ($width > 0 && $height > 0) { $diff = abs($width-$height); print $originalsdir.$rows[2]."/".$rows[1]." $width $height\n"; $ratio = $width/$height; $resratio = $$_[1] / $$_[2]; if ($$_[0] eq 0) { if ($ratio>$resratio) { #image is wider than the thumbnail size $newwidth = $$_[1]; $newheight = $newwidth / $ratio; } else { $newheight = $$_[2]; $newwidth = $newheight * $ratio } $image->Resize(width=>$newwidth,height=>$newheight,filter=>'Lanczos'); } else { if ($ratio>$resratio) { $widthatnewres = $$_[2] * $ratio; $cropx = ($widthatnewres - $$_[1])/2; $image->Resize(width=>$widthatnewres,height=>$$_[2],filter=>'Lanczos'); $image->Crop($$_[1].'x'.$$_[2].'+'.$cropx.'+0'); } else { $heightatnewres = $$_[1] / $ratio; $cropy = ($heightatnewres - $$_[2])/2; $image->Resize(width=>$$_[1],height=>$heightatnewres,filter=>'Lanczos'); $image->Crop($$_[1].'x'.$$_[2].'+0+'.$cropy); } } $image->Set(quality=>$$_[3]); $image->Strip(); $image->Write($thumbnailsdir.$filename.'.jpg'); &do_that_exif_thang($originalsdir.$rows[2]."/".$rows[1],$thumbnailsdir.$filename.'.jpg'); $dbh->do("insert into thumbnails (id,type,image) values (?,?,?)",{},($filename,$$_[4],$rows[0])); } } else { print "exists\n"; } } } $dbh->disconnect(); unlink("thumbnailer.lock"); sub random_filename_generator() { my @chars=('a'..'z','A'..'Z','0'..'9','_'); my $random_string; foreach (1..20) { $random_string.=$chars[rand @chars]; } return $random_string; } sub do_that_exif_thang() { $exif = new Image::ExifTool; $exif->SetNewValuesFromFile($_[0]); print $exif->WriteInfo($_[1]); }