Sql managing

So i have postgres setup and the database is working all fine and
dandy, but heres my issues.

The basic framework contains users, groups, events, and projects. I
want to give users the ability to join the groups events projects ect.
and further more be able to invite other users to those projects events
ect. All the meanwhile storing this in an SQL database.

my initial thoughts were of course to set up a table for users groups
projects and events using a seperate script to purely create the tables.
Then in a second script the actual user functions take place.

Basically if i defined a function for example to add a user to a group,
how would i properly query the sql server to let the people know that
user1 has been successfully added to the group, and further more
retrieve that information that user1 is in group2 later down the road.

In simpler words, whats an efficient way to retrieve data via sql.

require ‘rubygems’
require ‘postgres’
require ‘socket’

def database
@connection =

yield @connection

database do |x|
x.exec(“create table users ( name char(12) default 0 );”)
x.exec(“create table groups ( group char(12) default 0 );”)
x.exec(“create table events ( event char(12) default 0 );”)
x.exec(“create table projects ( project char(12) default 0 );”)

and then further on i would need to somehow create addition columns in
those tables to add users to the list in those particular groups events

Any guidance would be much appreciated!


  • Mac

Well, i came up with a solution for myself, but i do have one
question…I’ve always been hazy on this subject and i really need to
improve on it, but how would i generate an STDIN for this… IE: run the
script from command line as the following:
Invite.event(“foo”,“Event_2”) | sqlrun.rb

obviously that would never work but hopefully you get the general idea
of what im going after, full command line control rather than doing a
while true loop for a gets.chomp lol.

Here is the script none-the-less

require ‘rubygems’
require ‘postgres’

#def create_user

puts “####Create a New Account####”

print “Desired Username: \n”

newuser = gets.chomp

print “Desired Password: \n”

newpass = gets.chomp

#base.exec(“insert into USERS(name) values (’#{newuser}’)”)
#base.exec(“insert into USERS(pass) values (’#{newpass}’)”)

#Commenting this user creation out for now, implementing later

def login
puts “##Login Information Please##”
print “Username: \n”
@@client = gets.chomp
print “Password : \n”
clientpass = gets.chomp

@@base = PGconn.connect(“localhost”,5432,"","",“base”,“god”,“letmein”)

class User
class << self

def join_group(newgroup)
@@base = PGconn.connect(“localhost”,5432,"","",“base”,“god”,“letmein”)
if newgroup == “Group_1”
@@base.exec(“insert into GROUP_1(members) values (’#{@@client}’)”)
elsif newgroup == “Group_2”
@@base.exec(“insert into GROUP_2(members) values (’#{@@client}’)”)
elsif newgroup == “Group_3”
@@base.exec(“insert into GROUP_3(members) values (’#{@@client}’)”)
puts “Error: Non-Existance Group. Check Spelling.”

def join_event(newevent)

if newevent == “Event_1”
@@base.exec(“insert into EVENT_1(members) values (’#{@@client}’)”)
elsif newevent == “Event_2”
@@base.exec(“insert into EVENT_2(members) values (’#{@@client}’)”)
elsif newevent == “Event_3”
@@base.exec(“insert into EVENT_3(members) values (’#{@@client}’)”)
puts “Error: Non-Existance Event. Check Spelling.”

def join_project(newproj)

if newproj == “Project_1”
@@base.exec(“insert into PROJECT_1(members) values (’#{@@client}’)”)
elsif newproj == “Project_2”
@@base.exec(“insert into PROJECT_2(members) values (’#{@@client}’)”)
elsif newproj == “Project_3”
@@base.exec(“insert into PROJECT_3(members) values (’#{@@client}’)”)
puts “Error: Non-Existance Project. Check Spelling.”

class Invite
class << self

def project(user,projectid)
@@base = PGconn.connect(“localhost”,5432,"","",“base”,“god”,“letmein”)
@@base.exec(“insert into #{projectid}(members) values (’#{user}’)”)
print "Message: "
msg = gets.chomp
puts “#{user} has been invited to #{projectid} by #{@@client} –

def group(user,groupid)
@@base.exec(“insert into #{groupid}(members) values (’#{user}’)”)
print "Message: "
msg = gets.chomp
puts “#{user} has been invited to #{groupid} by #{@@client} –

def event(user,eventid)
@@base.exec(“insert into #{eventid}(members) values (’#{user}’)”)
print "Message: "
msg = gets.chomp
puts “#{user} has been invited to #{eventid} by #{@@client} –