sql managing

[Michael Linfield]

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 =
PGconn.connect("localhost",5432,"","","database","god","password")

begin
yield @connection
ensure
@connection.close
end
end

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 );")
end

#

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
ect.

Any guidance would be much appreciated!

Thanks!

- Mac

 

[Michael Linfield]

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}')")

#end
#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
end


@@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}')")
else
puts "Error: Non-Existance Group. Check Spelling."
end
end

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}')")
else
puts "Error: Non-Existance Event. Check Spelling."
end
end

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}')")
else
puts "Error: Non-Existance Project. Check Spelling."
end
end
end
end

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} --
\"#{msg}\""
end

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} --
\"#{msg}\""
end

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} --
\"#{msg}\""
end
end
end