################################################################################
# a general example
################################################################################


include Dbside

DB = Postgres::Database.new(
  :database => 'reality',
  :user => 'postgres',
  :password => '12345'
)

class Nodes < DB::Table
  set_table_name :my_nodes
 
  # associations
  one_to_one :parent_node, :class => Nodes, :match => {:id => :parent_id}
  one_to_many :states, :match {:node_id => :id}, :cascade => :all # :class => States is implied
  one_to_many :children, :class => Nodes, :match {:parent_id => :id},
    :cascade => :delete
  one_to_one :producer, :class => Channels, :match {:id => :producer_id}

  # method hooks
  before_delete :remove_from_producer

  def remove_from_producer
  ...
  end

  # another way to do the hook is this:
  before_delete do
    ...
  end

  # custom methods
  def set_state(opts)
    ...
    update_all_subscribers
  end

  def write(value, datatype = nil)
    if producer
      producer.write_request(self, value, datatype)
    else
      set_state(:quality => :good, :value => value, :datatype => datatype)
    end
  end
end

class States < DB::Table
  Qualities = {0 => :unknown, 1 => :good, 2 => :bad, 3 => :invalid}.freeze
  Datatypes = {0 => :text, 1 => :integer, 2 => :float, 3 => :boolean}

  translate :quality => Qualities
  translate :datatype => Datatypes
 
end

class Nodes < DB::Table
  translate :state_datatype => States::Datatypes

  translate :state_stamp, :fetch => proc {|s| Time.at(s)}, :update => proc {|s| s.to_f}

  # or maybe
  translate :state_stamp => Time
end

################################################################################
# database connection
################################################################################

DB = ServerSide::Database::Postgres.new(
  {:database => 'reality', :user => 'postgres', :password => '12345'})

class ServerSide::Database::Base
  def [](tblname)
    query(:source => tblname)
  end
end

################################################################################
# SQL DSL 
################################################################################

# Assign a table dataset to a constant
Posts = DB[:posts]

# A blank query
d = Dataset.new

Dataset.from(:posts)
#=> SELECT * FROM posts

Dataset.select(:id, :title).from(:posts)
#=> SELECT id, title FROM posts

Dataset.from(:posts).filter(:subject => 'music')
#=> SELECT * FROM posts WHERE subject = 'music'

Dataset.from(:posts).order(:stamp.DESC)
#=> SELECT * FROM posts ORDER BY stamp DESC

Dataset.from(:posts).limit(30)
#=> SELECT * FROM posts LIMIT 30

# pagination
Dataset.paginate(20, 1)
#=> SELECT * FROM posts LIMIT 0, 20
Dataset.paginate(20, 6)
#=> SELECT * FROM posts LIMIT 100, 20

################################################################################
# filter_by_XXX, order_by_XXX - auto-generated custom methods
################################################################################

DB.source(:posts).filter(:subject => /music/)
DB[:posts].filter(:subject => /music/)
DB[:posts].filter_by_subject(/music/) # automatically created filter_by_XXX method

Posts.filter_by_subject(/music/).order_by_stamp_DESC  # automatically created order_by_XXX method

# select specific columns
Posts.select_id_and_title.order_by_title # automatically created select_XXX method.

################################################################################
# filter expressions
################################################################################

filter(:stamp => t1..t2)
filter(:id => [1, 76, 23, 45])
filter(:title => /prototype/)
filter(:title.NOT.LIKE => 'john%')
filter(:title.START_WITH => 'john')
filter(:title.END_WITH =>'john')
filter(:id.NOT => [6, 5, 32, 2])

################################################################################
# joins
################################################################################

# join tables
Posts.join(:authors, :author_id => :id)

# default join is LEFT OUTER JOIN, but there are methods for other joins
Posts.inner_join(...)
Posts.left_outer_join(...) # join is an alias for this method
Posts.right_outer_join(...)
Posts.full_outer_join(...)

################################################################################
# selecting single records
################################################################################

n = Nodes.find(:path => 'users'/user)
n = Nodes.by_path('users'/user) # automatically created by_XXX method

class Nodes < Table
  default_find_by :path # redefines the [] method
end

n = Nodes['users'/user]

Nodes.first(:path => 'users'/user)
Nodes.last(:path => 'users'/user)
Nodes.first_by_path('users'/user) # etc.

# find_by_XXX and by_XXX methods raise an exception if record not found
# first_XXX and last_XXX methods return nil if record not found

################################################################################
# accessing record values
################################################################################

Nodes.each {|n| puts n[:title]} # each record is fetched and sent to the block
# the n in the block is a reference to the query itself

Nodes.all #=> returns an array with all records as hashes

Nodes.first #=> fetches the first record returns a reference to self with the record values loaded, or nil if no record
Nodes.last #=> fetches the last record and returns a reference to self with record values loaded, or nil of no record

Nodes['tprs/Bazra'] #=> fetches the first record that has the matching path

################################################################################
# summarizing
################################################################################

Nodes.count #=> returns the number of 
n = Nodes.group_by_quality.summarize(:quality, :id.COUNT, :value.AVG) #=>
  # returns an array of hashes:
  # [{:quality => 0, :count => 34, 'value average' => 43.12}, ...]
Nodes.group_by_quality.count #=> returns an hash:
# {0 => 23, 1 => 65, 3 => 44, 2 => 123}
Nodes.AVG(:value) #=> returns the average value
Nodes.group_by_quality.AVG(:value) #=> returns a hash:
#  {0 => 43.12, 1 => 54, 2 => 5.213}


################################################################################
# Creating records
################################################################################

n = Nodes.create(:name => 'sharon', :age => 32) #=> Dataset.new(:id => 1342876)
n[:id] #=> 1342876
n[:name] #=> 'sharon'

# Dataset is a descendant of Hash.
# When a record is fetched, the values are put into the Dataset.

class Dataset
  def create(values)
    ... # perform query
    where(:id => last_created_id).fetch
  end
  
  def fetch # fetches the next record in the query result
    ... # copy values to self
    self
  end
  
  def []= (field, value)
    update(field => value)
  end
end

################################################################################
# Updating records
################################################################################

Nodes.by_path('/tprs').update(:active => false)
Nodes.filter_by_path(/^\/tprs/).update(:active => false)
Nodes.filter_by_path(/^\/tprs/)[:active] = false

Nodes.filter_by_path(/^\/cex/).each {|n| n[:active] = !n[:active]}

################################################################################
# Deleting records
################################################################################

Nodes.find_by_path('/tprs').delete

################################################################################
# Individual records are really datasets
################################################################################

node = Nodes['/tprs']
node.is_a?(Dataset) #=> true

node[:active] = false
node.update(:active => false)

node[:active] #=> true/false

node.delete

################################################################################
# Defining a table class
################################################################################

class Nodes < Dbside::Table
end

################################################################################
# Relations
################################################################################

class Posts < Dbside::Table
  has_one :category, :table => :categories, :match => {:category_id => :id}
  
end

################################################################################
# Custom methods
################################################################################

Nodes.by_path('/tprs').children

class Nodes
  def children  
    Nodes.filter_by_parent_id(@record[:id])
  end
end

################################################################################
# relationships
################################################################################

class Nodes
  one_to_many :states, :match => {:id => :node_id}
  #synonimous to...
  def states
    States.filter(:node_id => @record[:id])
  end
  
  one_to_one :producer, :table => :channels, :match => {:producer_id => :id}
  #synonimous to...
  def producer
    Channels.first(:id => @record[:producer_id])
  end
end

# Let's take the code from
# http://blog.hasmanythrough.com/articles/2006/09/09/finding-unassociated-objects
# and see how it's done using our DSL

class Managers < Table
  # We use this to change the basic query for the table.
  # This is how we can create subsets.
  basic_dataset source(:personnel).filter(:type => 'manager')

  one_to_many :employees, :table => :personnel, :match {:id => :manager_id}
  # equivalent to
  def employees
    db[:personnel].filter(:manager_id => @record[:id])
  end
  
  def not_employees
    db[:personnel].filter(:manager_id.NOT => @record[:id])
  end
  
  def self.without_employees
    @@without_employees ||=
    left_outer_join('employees e', :id => 'e.manager_id').filter('e.id IS NULL')
  end
end

class Employees < Table
  basic_dataset source(:personnel).filter(:type => 'employee')
  
  one_to_one :manager, :table => :personnel, :match {:manager_id => :id}
  # equivalent to
  def manager
    db[:personnel].filter(:id => @record[:manager_id])
  end
  
  query :managed, filter(:manager_id.IS_NOT_NULL)
  query :orphans, filter(:manager_id.IS_NULL)
end





################################################################################
# Some code
################################################################################
class Dataset < Hash
  def self.query(name, dataset = nil, &block)
    puts "query"
    @queries ||= {}
    meta_def(name) {|*args| dataset || block.call(args)}
  end
  
  def self.method_missing(name, *args)
    case name.to_s
    when /^filter_by_(.+)$/:
      expressions = $1.split('_and_')
      query(name) do |a|
        filter(expressions.inject({}) {|m, i| m[i]=a.shift; m})
      end
    when /^(?:first_by_|by_)(.+)$/:
      expressions = $1.split('_and_')
      meta_def(name) {|*a| filter(expressions.inject({}) {|m, i| m[i]=a.shift; m}).first}
    when /^last_by_(.+)$/:
      expressions = $1.split('_and_')
      meta_def(name) {|*a| filter(expressions.inject({}) {|m, i| m[i]=a.shift; m}).last}
    when /^order_by_(.+)$/:
      expressions = $1.split('_and_')
      meta_def(name) {order(expressions)}
    else # create stub class.methods that create new queries and then call the corresponding
      # instance method.
      if instance_methods.include?(name.to_s)
        meta_def(name) do |*a|
          new.set_source(name.downcase).send(name, *a)
          # Create a new dataset, set its source to the table (class name in 
          # lower case) and invoke the modifier
        end
      end
    end
    send(name, *args) if respond_to?(name)
  end
  
  def self.[]=(name, value)
    make_query(name) {value}
  end
  
  def self.[](name)
    send(name)
  end
  
  def filter(*args)
    puts "filter(#{args.inspect})"
    dup.add_filter(*args) # create a copy of self and add the filter
  end
  
  def order(*args)
    puts "order(#{args.inspect})"
    dup.set_order(*args) # create a copy of self and set the order
  end
end

class Symbol
  def DESC
    "#{to_s} DESC"
  end
  
  def SUM
    "SUM(#{self})"
  end
end

class Table < Dataset
  def self.inherited(c)
    name = c.name.split('::').last
    c = eval(name)
    c.module_def(:table_dataset) {Dataset.new.from(name)}
  end
end

class Nodes < Table
  default_find_by :path
  query :music_posts, filter_by_subject('music').order(:stamp.DESC)
  query :posts_by_subject {|s| filter_by_subject(s).order_by_stamp_DESC}
  
  def print_music_posts
    music_posts.each {|p| puts p[:title]}
  end
end