The community database is the foundation for a set of applications that will serve the wireless community. The primary focus is a The database is currently implemented in SQL tables on postgresql.

community database take two. March 2003 AdamShand, JerrittCollord, AaronBaer, (insert your name here)

A one to one mapping between table A and table B - create a field in table A that contains a unique ID in table B. BR A one to many mapping between table A and table B - no field is required in table A, create a field in table B that contains the unique ID type of table A. Then add records in table B as needed with the same unique ID BR

User Table BR Description

• A record in the user table refers to an individual person.

Table Relationships

• 1...member of.............N Communitygroup

Fields

• userid - character varying(20) - Unique ID for the user
• createdate - ? - record creation date
• modificationdate - ? - last modification date
• firstname - character varying(50) - The user's first name
• lastname - character varying(100) - The user's last name
• password - character varying(8) - The user's password, in plaintext
• memberstatus - character varying(10) - An index into the memberstatus table
• communitygroupid - An index into the communitygroup table
• phone_home - character varying(15) - Home phone number
• phone_cell - character varying(15) - Cell phone number
• url_homepage - character varying(200) - URL for a home page/info page
• url_faceimage - character varying(200) - URL for a profile image
• comments - character varying(2000) - miscellaneous comments
• certificationid - numeric - unknown
• mailto - character varying(100) - User's email address
• MOIN_ID - character varying(24) - login to the moinmoin system
• tempkey - character varying(16) - unknown

Node table BR Description

• A wireless accesspoint at a particular location.

Table relationships

• 1...owned by..............N User
• 1...managed by............N User
• 1...upstream comes from...N Provider

Fields

• nodeid - numeric - unique ID
• createdate - ? - record creation date
• modificationdate - ? - last modification date
• name - character varying(1000) - Given name of the phone
• providerid - numeric - index into the provider table
• carrierid - numeric - index into the carrier table
• neighborhoodid - numeric - index into the neighborhood table
• bandwidthid - numeric - index into the bandwidth table
• statusid - numeric - index into the status table
• certificationid - numeric - index into the certification table
• comments - character varying(2000) - notes on the node
• longitude - character varying(200) - long
• latitude - character varying(200) - latitude
• altitude - ? - meters above sealevel
• essid - character varying(500) - essid in use (this should index into a table for multiple radios)
• channel - character varying(2) (parrt of the radio channel)
• address - character varying(200) - (this should be an index into an address table)
• city - character varying(100)
• state - character varying(100)
• zip - character varying(20)
• url_sitephoto - character varying(300) - profile shot to include in a profile page
• url_information - character varying(300) - Information about the site
• maintainer1 - character varying(20) - index into user table (change into a new table to support N photos)
• maintainer2 - character varying(20) -
• owner - character varying(20) - index into the user table
• node_geom - geometry - ?
• pri_pub_ip_block - character varying(128) - IP block provided by DHCP. this would be part of the radio/interface specification.

Indexes: node_nodeid_key unique btree (nodeid)

Check constraints: BR

• "$1" (srid(node_geom) = -1) "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom IS NULL))

DonPark and AdamShand are thinking about ways to create a SQL (or maybe LDAP or XML or something) schema which a community wireless group can use as a universal data collection point. We're trying to catalog everything we can think of that a community wireless group would want to keep track of (yes we know we're doomed to failure :-).

• Don, it occurred to me that it might be worth checking out the NoCatAuth SQL schema (etc/nocat.schema in the tarball), they had originally planned for it to be more then just the auth server. -- Adam. I looked it over. added bandwidth and date to Node. Created Network. added URLs and password to User. (8Jun) -- DonPark

  How would the local *Cat box regulate bandwidth to the user? Would the local *Cat box have a table with users and their respective classes (owner, member, guest, etc..), or would there be another table for that? Somehow we have to account for the bandwidth shaping aspect of things. We could add a field of Type STATUS, memberstatus (nodeOwner, fullMember, guest), and have the local node shape bandwidth for those values. Make sense? -- DanRichardson

Databases

• User
  • varchar GUID (32,Key)
  • date created
  • date modified
  • varchar username (64)
  • varchar first_name (64)
  • varchar last_name (64)
  • varchar password (64)
  • Type ICQ, icq_id
  • Type Jabber, jabber_id
  • Type URL, homepage
  • Type URL, faceimage
  • Type Address, mailing
  • Type Login, last_login
  • varchar notes (5k)
• Node
  • varchar GUID (32,Key)
  • date created
  • date modified
  • varchar name (128)
  • Type Antenna, antenna_type
  • Type Direction antenna_pointing_direction
  • int power_output
  • int kbits_second
  • Type Address, antenna_address
  • Type GPS, antenna_gps
  • Type Affiliated group [n]
  • Type User, Owner [n]
  • Type User, Administrator [n]
  • varchar notes (5k)
  • Type URL, description
  • Type URL, login_help
• GPS Loc
  • float lat
  • float long
  • float altitude
• Network
  • GUID varchar(32,Key)
  • date created
  • date modified
  • name varchar(128)

System for getting at the data

• XML-RPC interface
• xmlrpc.personaltelco.net:3322
• inserts
• updates
• queries

  • Q: <nodequery sql="select * where name=\"PCHS\" " />

  • A: <node> <name>PCHS</name> </node> ...

Implementation

• PHP page talking to a MySQL server

[http://www.nodedb.com/ndbcom.dtd] NodeDB.com xml schema...

[CategoryDocumentation]