DZone Snippets: join code

DELETE - SQL

dubby (Dave) — Thu, 06 Sep 2007 10:30:33 GMT

// description of your code here



DELETE ... 

    [ FROM ... ] 

    [ ... JOIN ... ] 

    [ WHERE ... ] 



// FULL Syntax follows:

DELETE [ FROM ] 

    {

       [ database. ] [ owner. ] table_name 

         [

           WITH (  INDEX ( Index_1,   [ index_2,  ...,  n ] ) 

                      | FASTFIRSTROW 

                      | HOLDLOCK 

                      | PAGLOCK 

                      | READCOMMITTED 

                      | REPEATABLEREAD 

                      | ROWLOCK 

                      | SERIALIZABLE 

                      | TABLOCK 

                      | TABLOCKX 

                      [   ...   n  |  ...,  n ] 

                     )

       ]

     |

       OPENQUERY( server, 'query' ) 

     |

       OPENROWSET( 'provider_name', 

                                 { 'datasource';'user_id';'password',  |  'provider_string', }

                                 { [ catalog. ] [ schema. ] object  |  'query' }

                               ) 

     |

       view_name 

    }



    [

      FROM 

      {

          derived_table 

            [ [ AS ] table_alias ]

            [ ( column_alias_1,   [ column_alias_2,   ...,  n ]  )  ]

        |

          CONTAINSTABLE ( table, column  |  *, 'search_conditions' ) 

            [ [ AS ] table_alias ]

        |

          FREETEXTTABLE ( table, column  |  *, 'free_text_string' ) 

            [ [ AS ] table_alias ]

        |

          table_name [ [ AS ] table_alias ]

            [

               WITH (  INDEX ( Index_1,   [ index_2,   ...,  n ]  ) 

                          | FASTFIRSTROW 

                          | HOLDLOCK 

                          | NOLOCK 

                          | PAGLOCK 

                          | READCOMMITTED 

                          | READPAST 

                          | READUNCOMMITTED 

                          | REPEATABLEREAD 

                          | ROWLOCK 

                          | SERIALIZABLE 

                          | TABLOCK 

                          | TABLOCKX 

                          | UPDLOCK 

                          [ ...   n  |  ...,  n ] 

                         )

            ]

        |

          view_name [ [ AS ] table_alias ]

      }











      [

          INNER JOIN  |  LEFT [ OUTER ] JOIN  |  RIGHT [ OUTER ] JOIN 

            {

               derived_table [ ON search_conditions ]

             |

               OPENQUERY( server, 'query' ) [ ON search_conditions ]

             |

               OPENROWSET

                               ( 'provider_name', 

                                 { 'datasource';'user_id';'password',  |  'provider_string', }

                                 { [ catalog. ] [ schema. ] object  |  'query' }

                               ) [ ON search_conditions ]

             | 

               table_name [ ON search_conditions ]

             | 

               view_name [ ON search_conditions ]

            }

            [   ...,  n ]

          |

          CROSS JOIN  |  FULL [ OUTER ] JOIN 

            {

               derived_table 

             | 

               OPENQUERY( server, 'query' ) 

             |

               OPENROWSET

                               ( 'provider_name', 

                                 { 'datasource';'user_id';'password',  |  'provider_string', }

                                 { [ catalog. ] [ schema. ] object  |  'query' }

                               ) 

             | 

               table_name 

             | 

               view_name 

            }

            [   ...,  n ]

       ]

   ] 



   [

      {

         WHERE search_conditions 

       |

         WHERE CURRENT OF [ GLOBAL ] cursor_name 

       |

         WHERE CURRENT OF cursor_variable_name 

      }

         [

           OPTION (  FAST number_rows 

                          | FORCE ORDER 

                          | HASH GROUP 

                          | ORDER GROUP 

                          | HASH JOIN 

                          | LOOP JOIN 

                          | MERGE JOIN 

                          | KEEP PLAN 

                          | MAXDOP 

                          | ROBUST PLAN 

                          | CONCAT UNION 

                          | HASH UNION 

                          | MERGE UNION 

                          [   ...,  n ]

                        )

         ]

   ]

//Full explanation follows:

KEYWORDS Keywords are denoted with upper case letters. Obey the spelling.

variables All user-supplied variables are denoted with lower case letters.

..., n Signifies that there can be more than one value in a comma delimited list. Note that the dots and n are not part of the code and must not appear in the SQL query.

... n Signifies that there can be more than one value in a blank space delimited list. Note that the dots and n are not part of the code and must not appear in the SQL query.

{ } Signifies that all, or some portion, of the code elements between the braces are required elements and must appear in the SQL query. Note that these braces are not part of the code and must not appear in the SQL query.

[ ] Signifies that the code elements between the square brackets can optionally appear in the SQL query, but are not required. Note that these brackets are not part of the code and must not appear in the SQL query.

| The or symbol signifies that you may use only one of the code elements or values from the possible choices. Note that the or symbol is not part of the code and must not appear in the SQL query.

fractionfiles.py

mcandre (Andrew Pennebaker) — Mon, 19 Feb 2007 01:01:02 GMT

// Splits a file into smaller ones, and joins them together.



#!/usr/bin/env python



"""Splits and joins files. Helpful when media can't fit a file.

Be prepared for a lot of output files!"""



__author__="Andrew Pennebaker (andrew.pennebaker@gmail.com)"

__date__="6 Jan 3006 - 12 Feb 2006"

__copyright__="Copyright 2006 Andrew Pennebaker"

__license__="GPL"

__version__="0.3"

__URL__="http://snippets.dzone.com/posts/show/3541"



import sys, os

from getopt import getopt



SPLIT_MODE="SPLIT"

JOIN_MODE="JOIN"



def splitFile(name, length, number):

	if length==None:

		infile=open(name, "rb")

		size=0

		while infile.read(1)!="":

			size+=1



		infile.close()



		maxlength=size/number

		if number*maxlength			maxlength+=1



	else:

		if length<1:

			raise Exception



	infile=None

	try:

		infile=open(name, "rb")

	except Exception, e:

		raise e



	i=0

	j=0

	c=infile.read(1)

	while c!="":

		outfile=None

		try:

			outfile=open("%s.%d" % (name, j), "wb")

		except Exception, e:

			raise e



		while i			outfile.write(c)

			c=infile.read(1)

			i+=1



		outfile.close()

		i=0

		j+=1



	infile.close()



def joinFiles(filenames):

	if len(filenames)<1:

		raise Exception



	filenames.sort() # ...0 must be first



	origFilename=filenames[0][0:-2] # take ".0" off the first file name

	origFile=None



	try:

		origFile=open(origFilename, "wb")

	except Exception, e:

		raise e



	c="&" # dummy



	for filename in filenames:

		smallFile=None

		try:

			smallFile=open(filename, "rb")

		except Exception, e:

			raise e



		c=smallFile.read(1)

		while c!="":

			origFile.write(c)

			c=smallFile.read(1)



		smallFile.close()



	origFile.close()



def usage():

	print "Usage: %s [options] [files]" % (sys.argv[0])

	print "\n--split "

	print "--join "

	print "--maxlength "

	print "--maxfiles "

	print "--help (usage)"



	sys.exit()



def main():

	global SPLIT_MODE

	global JOIN_MODE



	mode=SPLIT_MODE

	filenames=[]

	maxlength=1024

	maxfiles=None



	systemArgs=sys.argv[1:] # ignore program name



	optlist=[]

	args=[]



	try:

		optlist, args=getopt(systemArgs, None, ["split", "join", "maxlength=", "maxfiles=", "help"])

	except Exception, e:

		usage()



	if len(optlist)<1 or len(args)<1:

		usage()



	for option, value in optlist:

		if option=="--help":

			usage()



		elif option=="--split":

			mode=SPLIT_MODE

		elif option=="--join":

			mode=JOIN_MODE

		elif option=="--maxlength":

			try:

				maxlength=int(value)

				if maxlength<1:

					raise Exception

				maxfiles=None

			except Exception, e:

				raise "Length must be at least one"

		elif option=="--maxfiles":

			try:

				maxfiles=int(value)

				if maxfiles<1:

					raise Exception

				maxlength=None

			except Exception, e:

				raise "Number must be at least one"



	filenames=args



	if mode==SPLIT_MODE:

		for filename in filenames:

			try:

				splitFile(filename, maxlength, maxfiles)

			except Exception, e:

				raise e



	elif mode==JOIN_MODE:

		for directory in filenames:

			files=["%s%s%s" % (directory, os.sep, file) for file in os.listdir(directory)]



			try:

				joinFiles(files)

			except Exception, e:

				raise e



if __name__=="__main__":

	main()

Files Joiner/Unjoiner //Pascal class

jonasraoni (Jonas Raoni Soares Silva) — Thu, 15 Jun 2006 19:09:02 GMT

This is just a snippet since I didn't added some units that can be replaced without major efforts:
- PathParser.pas: parses paths shortcuts (it's on my bigbold snippets, search on my tags)
- Stack.pas: implements a simple stack (it's on my bigbold snippets, search on my tags)
- ZlibEx.pas: used to compress/decompress the file contents (My ZlibEx is a modified version of this file: http://www.dellapasqua.com/delphizlib)
- MD5.pas: used to calculate the file hash and check consistency when unjoining files
- SysUtils2: Some idiot functions =b

I used this in a personalized installer that I've made in my first job =b



unit FileJoiner;



interface



uses

  SysUtils, SysUtils2, Classes, MD5, ZlibEx, PathParser, Stack;



type

  TOverwriteMode = ( omNo, omAskUser, omIfNewer, omIfOlder, omIfDiff );

  TOverwriteAction = ( oaOverwriteAll, oaNoOverwriteAll, oaYes, oaNo );



  TFileHeader = record

    MD5Hash: TDigestStr;

    ModificationDate: TDateTime;

    Attributes: LongWord;

    Overwrite: TOverwriteMode;

    Size: Int64;

    MustKeep: Boolean;

  end;



  TFileJoinerItem = class

  public

    Source, Destiny: string;

    MustKeep, Recurse: Boolean;

    Overwrite: TOverwriteMode;



    constructor Create( const FromPath, ToPath: string; const OverwriteMode: TOverwriteMode = omIfNewer; const Recursive: Boolean = True; const MustKeepFile: Boolean = False ); overload;

    function Assign( Item: TFileJoinerItem ): TFileJoinerItem;



    procedure Save( const Stream: TStream );

    procedure Load( const Stream: TStream );

  end;



  TCustomFileJoiner = class;



  TFileJoinerFilesCallback = procedure( Sender: TCustomFileJoiner; Item: TFileJoinerItem ) of object;

  TFileJoinerNotifyEvent = procedure( Sender: TCustomFileJoiner ) of object;

  TFileJoinerFileExists = procedure( Sender: TCustomFileJoiner; var CanOverwrite: TOverwriteAction ) of object;



  TJoinerStatus = ( jsIdle, jsJoining, jsUnjoining );



  TCustomFileJoiner = class

  private

    FStream: TStream;

    FCurFile, FTotalFiles: LongWord;

    FCurSize, FTotalSize, FCurPosition, FCurWrittenBytes: Int64;

    FCurFilename: string;

    FCurFileInfo: TFileHeader;

    FOnFileExists: TFileJoinerFileExists;

    FOnWorkEnd, FOnWorkBegin, FOnWork, FOnProcessFile: TFileJoinerNotifyEvent;



    procedure ProgressNotifier( Sender: TObject );



  public

    //properties

    property CurFilename: string read FCurFilename;

    property CurFileInfo: TFileHeader read FCurFileInfo;

    property CurFilePosition: Int64 read FCurPosition;

    property CurWrittenBytes: Int64 read FCurWrittenBytes;

    property CurSize: Int64 read FCurSize;

    property CurFile: LongWord read FCurFile;



    property TotalSize: Int64 read FTotalSize;

    property TotalFiles: LongWord read FTotalFiles;



    //events

    property OnWorkBegin: TFileJoinerNotifyEvent read FOnWorkBegin write FOnWorkBegin;

    property OnWork: TFileJoinerNotifyEvent read FOnWork write FOnWork;

    property OnWorkEnd: TFileJoinerNotifyEvent read FOnWorkEnd write FOnWorkEnd;

    property OnProcessFile: TFileJoinerNotifyEvent read FOnProcessFile write FOnProcessFile;

    property OnFileExists: TFileJoinerFileExists read FOnFileExists write FOnFileExists;

  end;



  TFileJoiner = class( TCustomFileJoiner )

  private

    FPaths: TList;



    function GetItem(const Index: Integer): TFileJoinerItem;

    function GetCount: Integer;

    procedure StreamFile( Sender: TCustomFileJoiner; Item: TFileJoinerItem );

    procedure Compress( Input: TStream );



  public

    constructor Create;

    destructor Destroy; override;



    procedure Join( const Filename: string ); overload;

    procedure Join( const Stream: TStream ); overload;



    procedure SaveList( const Filename: string ); overload;

    procedure SaveList( Stream: TStream ); overload;

    procedure LoadList( const Filename: string ); overload;

    procedure LoadList( Stream: TStream ); overload;



    procedure CountFiles;



    function Add( const FromPath, ToPath: string; const OverwriteMode: TOverwriteMode = omIfNewer; const Recursive: Boolean = False; const MustKeep: Boolean = False ): Integer;

    procedure Clear;

    procedure Remove( const Index: Integer );

    procedure ListFiles( const Callback: TFileJoinerFilesCallback );



    property Count: Integer read GetCount;

    property Items[ const Index: Integer ]: TFileJoinerItem read GetItem; default;

  end;



  TFileUnjoiner = class( TCustomFileJoiner )

  private

    FDataBegin: Int64;

    procedure Decompress( Output: TStream );



  public

    procedure Assign( const Filename: string ); overload;

    procedure Assign( Stream: TStream ); overload;

    procedure UnJoin;

  end;



implementation



{ TCustomFileJoiner }



procedure TCustomFileJoiner.ProgressNotifier(Sender: TObject);

begin

  if Assigned( FOnWork ) then

    with TStream( Sender ) do

    begin

      FCurWrittenBytes := Position - FCurPosition;

      FCurPosition := Position;

      FOnWork( Self );

    end;

end;



{ TFileJoiner }



procedure TFileJoiner.Join( const Filename: string );

begin

  FStream := TFileStream.Create( Filename, fmCreate );

  try

    Join( FStream );

  finally

    FStream.Free;

  end;

end;



procedure TFileJoiner.Join( const Stream: TStream );

var

  Pos: array[0..1] of Int64;

begin

  FStream := Stream;

  if Assigned( FOnWorkBegin ) then

    FOnWorkBegin( Self );



  FCurFile := 0;

  FCurSize := 0;



  //record position to get back later and reserve space on the file to record the "totals"

  Pos[0] := FStream.Position;

  FStream.Seek( SizeOf( FCurFile ) + SizeOf( FCurSize ), soCurrent );



  //write files

  ListFiles( StreamFile );



  //write the totals and get back

  Pos[1] := Stream.Position;

  FStream.Position := Pos[0];

  FStream.Write( FCurFile, SizeOf( FCurFile ) );

  FStream.Write( FCurSize, SizeOf( FCurSize ) );

  FStream.Position := Pos[1];



  //job done

  if Assigned( FOnWorkEnd ) then

    FOnWorkEnd( Self );

end;



procedure TFileJoiner.StreamFile( Sender: TCustomFileJoiner; Item: TFileJoinerItem );

var

  InputFile: TFileStream;

  Pos: array[0..1] of Int64;

begin

  try

    Inc( FCurFile );

    FCurPosition := 0;

    FCurFilename := Item.Source;



    FCurFileInfo.MD5Hash := FileMD5Digest( Item.Source );

    FCurFileInfo.ModificationDate := FileDateToDateTime( FileAge( Item.Source ) );

    FCurFileInfo.Attributes := FileGetAttr( Item.Source );

    FCurFileInfo.Overwrite := Item.Overwrite;



    InputFile := TFileStream.Create( Item.Source, fmOpenRead or fmShareDenyWrite );

    try

      FCurFileInfo.Size := InputFile.Size;

      if Assigned( FOnProcessFile ) then

        FOnProcessFile( Self );



      Pos[0] := FStream.Position;

      //reserve space for the file header and EOF position

      FStream.Seek( SizeOf( FCurFileInfo ) + SizeOf( Pos[0] ), soCurrent );

      StringWrite( FStream, Item.Destiny );



      Compress( InputFile );



      //update the header and get back

      Pos[1] := FStream.Position;

      FStream.Position := Pos[0];

      FStream.Write( FCurFileInfo, SizeOf( FCurFileInfo ) );

      FStream.Write( Pos[1], SizeOf( Pos[1] ) );

      FStream.Position := Pos[1];



      //update summary

      Inc( FCurSize, FCurFileInfo.Size );

    finally

      InputFile.Free;

    end;

  except

    on E: Exception do

      raise EWriteError.CreateFmt( '%s.StreamFile: Error on joining: "%s" - %s',  [ ClassName, FCurFilename, E.Message ] );

  end;

end;



function TFileJoiner.Add(const FromPath, ToPath: string;

  const OverwriteMode: TOverwriteMode; const Recursive: Boolean; const MustKeep: Boolean ): Integer;

begin

  Result := FPaths.Add( TFileJoinerItem.Create( FromPath, ToPath, OverwriteMode, Recursive, MustKeep ) );

end;



procedure TFileJoiner.Clear;

var

  I: Integer;

begin

  for I := FPaths.Count - 1 downto 0 do

  begin

    TFileJoinerItem( FPaths[I] ).Free;

    FPaths.Delete( I );

  end;

end;



constructor TFileJoiner.Create;

begin

  FPaths := TList.Create;

end;



destructor TFileJoiner.Destroy;

begin

  Clear;

  FPaths.Free;

  inherited;

end;



procedure TFileJoiner.CountFiles;

type

  PStackItem = ^TStackItem;

  TStackItem = record

    Data: PChar;

    Searcher: TSearchRec;

  end;



var

  I: Integer;

  Path, Filter: string;

  Stack: TStack;

  CurStack, X: PStackItem;

begin

  FTotalFiles := 0;

  FTotalSize := 0;



  Stack := TStack.Create;

  try

    for I := 0 to FPaths.Count - 1 do

    begin

      Path := Self[I].Source;

      if LastDelimiter( '*?', ExtractFileName( Path ) ) <> 0 then

      begin

        Filter := ExtractFileName( Path );

        Path := ExtractFilePath( Path );

      end

      else if FileExists( Path ) then

      else if DirectoryExists( Path ) then

      begin

        Filter := '*';

        Path := AddSlash( Path );

      end

      else

        raise Exception.CreateFmt( '%s.GetFilesSumary: "%s" não encontrado', [ ClassName, Path ] );



      New( CurStack );

      CurStack^.Data := CopyString( Path );

      repeat

        with CurStack^ do

        begin

          if FindFirst( Data + Filter, faDirectory, Searcher ) = 0 then

          begin

            repeat

              Inc( FTotalFiles );

              Inc( FTotalSize, Searcher.Size );

            until FindNext( Searcher ) <> 0;

            FindClose( Searcher );

          end;



          if Self[I].Recurse and ( FindFirst( Data + Filter, faArchive, Searcher ) = 0 ) then

          begin

            repeat

              if Searcher.Name[1] <> '.' then

              begin

                New( X );

                X^.Data := CopyString( AddSlash( Data + Searcher.Name ) );

                Stack.Push( X );

              end;

            until FindNext( Searcher ) <> 0;

            FindClose( Searcher );

          end;

          FreeMem( Data );

          Dispose( CurStack );

          CurStack := Stack.Pop;

        end;

      until CurStack = nil;

    end;

  finally

    Stack.Free;

  end;

end;



function TFileJoiner.GetItem(const Index: Integer): TFileJoinerItem;

begin

  Result := FPaths.Items[ Index ];

end;



function TFileJoiner.GetCount: Integer;

begin

  Result := FPaths.Count;

end;



procedure TFileJoiner.ListFiles( const Callback: TFileJoinerFilesCallback);

type

  PStackItem = ^TStackItem;

  TStackItem = record

    Source, Destiny: PChar;

    Searcher: TSearchRec;

  end;



var

  Stack: TStack;

  Filter: string;

  Current, X: PStackItem;

  Data: TFileJoinerItem;

  I: Integer;

begin

  Stack := TStack.Create;

  try

    Data := TFileJoinerItem.Create;

    try

      for I := 0 to FPaths.Count - 1 do

      begin

        Data.Assign( Self[I] );

        with Data do

        begin

          Destiny := AddSlash( Destiny );

          if LastDelimiter( '*?', ExtractFileName( Source ) ) <> 0 then

          begin

            Filter := ExtractFileName( Source );

            Source := ExtractFilePath( Source );

          end

          else if FileExists( Source ) then

          begin

            Destiny := Destiny + ExtractFileName( Data.Source );

            Callback( Self, Data );

            Continue;

          end

          else if DirectoryExists( Source ) then

          begin

            Filter := '*';

            Destiny := AddSlash( Destiny + ExtractFileName( RemoveSlash( Source ) ) );

            Source := AddSlash( Source );

          end

          else

            raise Exception.CreateFmt( '%s.ListFiles: "%s" não encontrado', [ ClassName, Source ] );

        end;



        New( Current );

        with Current^ do

        begin

          Source := CopyString( Data.Source );

          Destiny := CopyString( Data.Destiny );

        end;



        repeat

          with Current^ do

          begin

            if FindFirst( Source + Filter, faDirectory, Searcher ) = 0 then

            begin

              repeat

                Data.Source := Source + Searcher.Name;

                Data.Destiny := Destiny + Searcher.Name;

                Callback( Self, Data )

              until FindNext( Searcher ) <> 0;

              FindClose( Searcher );

            end;



            if Data.Recurse and ( FindFirst( Source + '*', faArchive, Searcher ) = 0 ) then

            begin

              repeat

                if Searcher.Name[1] <> '.' then

                begin

                  New( X );

                  X^.Source := CopyString( AddSlash( Source + Searcher.Name ) );

                  X^.Destiny := CopyString( AddSlash( Destiny + Searcher.Name ) );

                  Stack.Push( X );

                end;

              un



Rails: Inner joins by association names
MattScilipoti (Matt Scilipoti) — Tue, 30 May 2006 08:58:02 GMT
From http://habtm.com/articles/2006/05/10/inner-joins-by-association-names:



We can enjoy eager loading with association names.



Member.find(:all, :include=>[:group, :profile])



But :joins option forces us to write raw SQL like this.



Member.count(:joins=>"INNER JOIN groups ON groups.id = members.group_id ...")



Why should we do it by hand although those AR classes know their reflections? It’s too boring and painful, and far from DRY.

expand join query



So, why don’t you put this code to ‘vendor/plugins/expandjoinquery/init.rb’?



class ActiveRecord::Base

  class << self

    private

    def add_joins!(sql, options, scope = :auto)

      scope = scope(:find) if :auto == scope

      join = (scope && scope[:joins]) || options[:joins]

      sql << " #{expand_join_query(join)} " if join

    end



    def expand_join_query(*joins)

      joins.flatten.map{|join|

        case join

        when Symbol

          ref = reflections[join] or

            raise ActiveRecord::ActiveRecordError, "Could not find the source association :#{join} in model #{self}"

          case ref.macro

          when :belongs_to

            "INNER JOIN %s ON %s.%s = %s.%s" % [ref.table_name, ref.table_name, primary_key, table_name, ref.primary_key_name]

          else

            "INNER JOIN %s ON %s.%s = %s.%s" % [ref.table_name, ref.table_name, ref.primary_key_name, table_name, primary_key]

          end

        else

          join.to_s

        end

      }.join(" ")

    end

  end

end



Now, we can enjoy :joins option as same as :include one!



# simply inner join

Member.find(:first, :joins=>:group)

=> SELECT * FROM members INNER JOIN groups ON groups.id = members.group_id LIMIT 1



# two inner joins

Member.count(:joins=>[:group, :profile])

=> SELECT count(*) AS count_all FROM members

   INNER JOIN groups   ON groups.id = members.group_id

   INNER JOIN profiles ON profiles.member_id = members.id 



# symbol and raw SQL

Member.find(:all, :joins=>[:group, "INNER JOIN addresses USING(address_id)"])

=> SELECT * FROM members

   INNER JOIN groups ON groups.id = members.group_id 

   INNER JOIN addresses USING(address_id) 



restrictions



This is just an idea and not deeply considered yet. So, there are following restrictions.



    * cascading is not supported

    * not work with :include option (i think)



I hope someone will polish this up :)