Jan
26

Fibonacci Sequence in SQL

Categories:

by

Here is a little SQL snippet for generating a the Fibonacci Sequence using:

  

  

SELECT x, ((1 / SQRT (5)) * POWER (((1 + SQRT (5)) / 2), x))
        - ((1 / SQRT (5)) * POWER (((1 - SQRT (5)) / 2), x)) fibonacci
          FROM (    SELECT LEVEL x
                      FROM DUAL
                CONNECT BY LEVEL < 100);

This post has no tag

Jan
24

Euclid’s Algorithm

Categories:

by

I have finally got around to reading The Art of Computer Programming Vol. 1 by Donald Knuth, and in the effort to better understand the algorithms defined within I plan on implementing them here in Clojure. I know that abs can be found in the contrib library, but so can gcd.

Here is Algorithm 1.1E

(ns euclid
  (:use clojure.test))
 
(defn
  ^{:test #(are [x y] (= x y) 9 (abs -9) 9 (abs 9) 0 (abs 0) 'a (abs 'a))
    :doc "Returns the absolute value for an integer, else it will just return
  what was passing in to the function."}
  abs [x]
    (if (number? x)
      (if (not (neg? x))
       x 
       (- x))
      x))
 
(defn
  ^{:test #(are [x y] (= x y) 17 (gcd 119 544) 17 (gcd 544 119) 119 (gcd 119 0))
    :doc "Returns the GCD for two given integers."}
  gcd [m n]
  (let [m (abs m) n (abs n)]
    (if (zero? n)
      m
      (if (< m n)
        (recur n m)
        (let [r (rem m n)]
          (if (zero? r)
            n
            (recur n r)))))))
euclid=> (run-tests)
 
Testing euclid
 
Ran 2 tests containing 7 assertions.
0 failures, 0 errors.
{:type :summary, :test 2, :pass 7, :fail 0, :error 0}

This post has no tag

Nov
30

Creating a PDF from PL/SQL

Categories:

by

This is just a simple test to see the feasibility of generating a PDF from PL/SQL. This will create a very simple single page PDF with a red border and “Hello World” in grey. It also assumes that one knows what to pass in to various functions. This knowledge would be derived from the PDF Reference documentation found on Adobe’s site.

NOTE – This is by no way consided to be even remotely production ready. It was just created to see what it would take, and I thought I would share my notes.

DECLARE
   TYPE pdf_objects IS TABLE OF VARCHAR2 (32767);
 
   objects   pdf_objects := pdf_objects ();
 
   crlf      VARCHAR2 (2) := CHR (13) || CHR (10);
 
   text      VARCHAR2 (120)
      :=    'BT'
         || crlf
         || '/F1 24 Tf'
         || crlf
         || '100 100 Td'
         || crlf
         || '0 Tr'
         || crlf
         || '0.5 g'
         || crlf
         || '( Hello World ) Tj'
         || crlf
         || 'ET';
 
   frame     VARCHAR2 (1000)
       :=    '1.0 0.0 0.0 RG'
          || crlf
          || '1.0 1.0 1.0 rg'
          || crlf
          || '30 30 552 732 re'
          || crlf
          || 'B';
 
   -----------------------------------------------------------------------------
   -- Takes in a PDF'ed string and creates an string that will represent a
   -- PDF object, that will need to have an object index (<<XX>>) assigned.
   -----------------------------------------------------------------------------
   FUNCTION create_object (content IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN '<<XX>> 0 obj' || crlf || content || crlf || 'endobj';
   END create_object;
 
   -----------------------------------------------------------------------------
   -- Takes a string that represents a PDF object and a nested tables of objects
   -- and creates a new nested table that will have the new object added with
   -- the object index assigned.
   -----------------------------------------------------------------------------
   FUNCTION add_object (object IN VARCHAR2, objects IN pdf_objects)
      RETURN pdf_objects
   AS
      l_objects   pdf_objects;
   BEGIN
      l_objects := objects;
      l_objects.EXTEND (1);
      l_objects (l_objects.LAST)
                            := REPLACE (object, '<<XX>>', (l_objects.LAST - 1));
      RETURN l_objects;
   END add_object;
 
   -----------------------------------------------------------------------------
   -- Wraps a string as a PDF Dictionary.
   -----------------------------------------------------------------------------
   FUNCTION create_dictionary (content IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN '<< ' || content || ' >>';
   END create_dictionary;
 
   -----------------------------------------------------------------------------
   -- Wraps a string as a PDF stream.
   -----------------------------------------------------------------------------
   FUNCTION create_stream (content IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN    create_dictionary ('/Length ' || LENGTH (content))
             || crlf
             || 'stream '
             || crlf
             || content
             || crlf
             || 'endstream';
   END create_stream;
 
   -----------------------------------------------------------------------------
   -- Adds some static text to the objects table to be later added to the PDF.
   -- This does not wrap the text as a PDF object.
   -----------------------------------------------------------------------------
   FUNCTION add_meta (object IN VARCHAR2, objects IN pdf_objects)
      RETURN pdf_objects
   AS
      l_objects   pdf_objects;
   BEGIN
      l_objects := objects;
      l_objects.EXTEND (1);
      l_objects (l_objects.LAST) := object;
      RETURN l_objects;
   END add_meta;
 
   -----------------------------------------------------------------------------
   -- Creates a simple PDF header that takes in the PDF version, for example
   -- 1.4, 1.7, ...
   -----------------------------------------------------------------------------
   FUNCTION create_header (version IN FLOAT)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN '%PDF-' || version;
   END create_header;
 
   -----------------------------------------------------------------------------
   -- Creates the PDF trailer that gives the number of objects in the PDF as
   -- well as a pointer to the catalog object.
   -----------------------------------------------------------------------------
   FUNCTION create_trailer (objects IN pdf_objects)
      RETURN VARCHAR2
   AS
      l_content   VARCHAR2 (32767) := 'trailer' || crlf;
      l_size      NUMBER := 0;
   BEGIN
      l_content
              :=  l_content
               || create_dictionary (     '/Size '
                                       || (objects.COUNT () - 1)
                                       || ' /Root 1 0 R' )
               || crlf;
 
      FOR i IN objects.FIRST .. objects.LAST
      LOOP
         EXIT WHEN i = objects.COUNT ();
         l_size := l_size + LENGTH (objects (i));
      END LOOP;
 
      l_content := l_content || 'startxref' || crlf || l_size || crlf || '%%EOF';
      RETURN l_content;
   END create_trailer;
 
   -----------------------------------------------------------------------------
   -- Creates a xref table for the PDF that has the byte offset to the PDF
   -- objects in the file.
   -----------------------------------------------------------------------------
   FUNCTION create_xref (objects IN pdf_objects)
      RETURN VARCHAR2
   AS
      offset   NUMBER := 0;
      xref     VARCHAR2 (32767) := '';
   BEGIN
      xref := 'xref' || crlf;
      xref := xref || '0 ' || objects.COUNT () || crlf;
      xref := xref || LPAD ('0', 10, '0') || ' 65535 f' || crlf;
 
      FOR i IN objects.FIRST .. objects.LAST
      LOOP
         IF i > 1
         THEN
            xref
              :=     xref
                  || LPAD (offset, 10, '0')
                  || ' '
                  || LPAD ('0', 5, '0')
                  || ' n';
 
            IF i <> objects.LAST
            THEN
               xref := xref || crlf;
            END IF;
         END IF;
 
         offset := offset + LENGTH (objects (i));
      END LOOP;
 
      RETURN xref;
   END create_xref;
 
   -----------------------------------------------------------------------------
   -- Spit out a string that represents the PDF document.
   -----------------------------------------------------------------------------
   FUNCTION dump_document (objects IN pdf_objects)
      RETURN VARCHAR2
   AS
      output   VARCHAR2 (32767) := '';
   BEGIN
      FOR i IN objects.FIRST .. objects.LAST
      LOOP
         output := output || objects (i) || crlf;
      END LOOP;
 
      RETURN output;
   END dump_document;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
BEGIN
   -- Create the header of the PDF passing in the PDF version to the used.
   objects := add_meta (create_header (1.4), objects);
 
   -- Create the Catalog PDF object that points to the Outlines and Pages object.
   objects := add_object (
                 create_object (
                    create_dictionary (
                       '/Type /Catalog /Outlines 2 0 R /Pages 3 0 R')), objects);
 
   -- Create the Outlines object.
   objects := add_object (
                 create_object (
                    create_dictionary ('/Type Outlines /Count 0')), objects);
 
   -- Create the Pages object that points to the Page objects.
   -- In this case, it only has one page.
   objects := 
      add_object (
         create_object (
            create_dictionary ('/Type /Pages /Kids [ 4 0 R ] /Count 1')), objects);
 
   -- Create the Page object.  This will include the page size (i.e. the MediaBox),
   -- as well as pointing to the Contents of the page. 
   objects :=
      add_object (
         create_object (
            create_dictionary (    '/Type /Page'
                                || '/Parent 3 0 RPage'
                                || '/MediaBox [ 0 0 612 792 ]Page'
                                || '/Contents 5 0 RPage'
                                || '/Resources '
                                || create_dictionary (    '/ProcSet 6 0 R'
                                                       || ' /Font '
                                                       || create_dictionary (
                                                            '/F1 7 0 R')))),
         objects);
 
   -- Create a PDF Stream object that draws a frame on the page along
   -- with some text.
   objects := add_object (
                 create_object (
                    create_stream (frame || crlf || text)), objects);
 
   -- Still need to look to see what exactly this line does.
   objects := add_object (
                 create_object (
                    create_dictionary ('[ /PDF /Text ]')), objects);
 
   -- Set up the Font object for the text stream.
   objects :=
      add_object (
         create_object (
            create_dictionary (    '/Type /FontPage'
                                || '/Subtype /Type1Page'
                                || '/Name /F1Page'
                                || '/BaseFont /HelveticaPage'
                                || '/Encoding /MacRomanEncoding')),
         objects);
 
   -- Create the xref table for the document.
   objects := add_meta (create_xref (objects), objects);
 
   -- Create the trailer.
   objects := add_meta (create_trailer (objects), objects);
 
   -- Print out the PDF for testing.
   DBMS_OUTPUT.put_line (dump_document (objects));
END;

This post has no tag

Nov
19

Using Incanter with Oracle

Categories:

by

The following just just an example of pulling data out of an Oracle database and graphing the output in Incanter.

Environment
C:\Windows\System32\javaw.exe -cp C:\lib\ojdbc6.jar;C:\lib\orai18n.jar;C:\lib\incanter.jar incanter.main

Code

(use 'clojure.contrib.sql 'incanter.core)
(def db {:classname "oracle.jdbc.OracleDriver"
         :subprotocol "oracle:thin"
         :subname "@<hostname>:<port>:<instance>"
         :user "<user>"
         :password "<password>" })
 
(view
   (dataset ["Physical GB" "Updated"]
      (with-connection db 
         (with-query-results
           rs
           ["SELECT SUM (physical_gb) physical_gb, updated
               FROM some.table
           GROUP BY updated
           ORDER BY updated"] 
            (vec
               (map #(vector (:physical_gb %) (:updated %)) rs))))))

This post has no tag

Oct
25

Euler Problem #1 (Groovy)

Categories:

by 

// Site: http://projecteuler.net/problem=1
// Answer: 233168
(1..(1000 - 1)).inject(0) { a, v ->
  (v % 3 == 0 || v % 5 == 0) ? a + v : a
}

This post has no tag

Oct
25

Euler Problem #1 (PowerShell)

Categories:

by 

# Site: http://projecteuler.net/problem=1
# Answer 233168
(1..(1000 - 1) |
 where-object {$_ % 3 -eq 0 -or $_ % 5 -eq 0} |
 measure-object -sum).sum

This post has no tag

Oct
25

Euler Problem #1 (SQL)

Categories:

by 

-- Site: http://projecteuler.net/problem=1
-- Answer 233168
SELECT SUM (CASE WHEN MOD (x, 3) = 0 OR MOD (x, 5) = 0 THEN x ELSE 0 END)
  FROM (    SELECT LEVEL x
              FROM DUAL
        CONNECT BY LEVEL < 1000);

This post has no tag

Oct
25

Euler Problem #1 (PL/SQL)

Categories:

by 

-- Site: http://projecteuler.net/problem=1
-- Answer: 233168
DECLARE
   ceiling      BINARY_INTEGER := 1000;
   answer       BINARY_INTEGER := 0;
   TYPE collection_type IS TABLE OF BINARY_INTEGER;
   collection   collection_type := collection_type ();
BEGIN
   FOR i IN 1 .. (ceiling - 1)
   LOOP
      IF MOD (i, 3) = 0 OR MOD (i, 5) = 0
      THEN
         collection.EXTEND;
         collection (collection.COUNT) := i;
      END IF;
   END LOOP;
 
   FOR i IN collection.FIRST .. collection.LAST
   LOOP
      answer := answer + collection (i);
   END LOOP;
 
   DBMS_OUTPUT.put_line (answer);
END;

This post has no tag

Oct
25

Euler Problem #1 (Perl)

Categories:

by 

# Site - http://projecteuler.net/problem=1
# Answer: 233168
use List::Util qw(reduce);
print reduce {$a + $b} grep {$_ % 3 == 0 or $_ % 5 == 0} (1..(1000 - 1));

This post has no tag

Oct
25

Euler Problem #1 (Clojure)

Categories:

by

Just one thing to note, the metadata “:test” syntax has the same functionality as the “(with-test …)” syntax.

; Site - http://projecteuler.net/problem=1
(ns euler
  (:use clojure.test clojure.contrib.math))
 
(defn
  ^{:test #(is (= 23 (problem-1 10)))}
  problem-1
  [max]
  (reduce
    +
    (filter
      #(or
        (= 0 (mod % 3))
        (= 0 (mod % 5)))
      (range 1 max))))

First, test the function against the known answer of 23 for an input of 10.

(run-tests)
 
Testing euler
 
Ran 1 tests containing 1 assertions.
0 failures, 0 errors.
{:type :summary, :test 1, :pass 1, :fail 0, :error 0}

We have lift off! Now, for the answer to the an input of 1000.

(problem-1 1000)
233168

This post has no tag

Older posts «