# Sql managing

**URL:** <https://www.ruby-forum.com/t/sql-managing/120360>\
**Category:** Ruby\
**Created:** [November 16, 2007, 7:29am UTC](https://www.ruby-forum.com/t/sql-managing/120360 "2007-11-16T07:29:58Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [November 16, 2007, 7:29am UTC](https://www.ruby-forum.com/t/sql-managing/120360/1 "2007-11-16T07:29:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [November 16, 2007, 10:14am UTC](https://www.ruby-forum.com/t/sql-managing/120360/2 "2007-11-16T10:14:28Z")

</div>

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