Differences between revisions 25 and 27 (spanning 2 versions)
Revision 25 as of 2003-04-25 11:25:41
Size: 5784
Editor: DonPark
Comment:
Revision 27 as of 2003-04-25 11:35:35
Size: 6070
Editor: DonPark
Comment:
Deletions are marked like this. Additions are marked like this.
Line 16: Line 16:
new tables
 communitygroup
 memberstatus
Line 27: Line 30:
A record in the user table refers to an individual person.  A record in the user table refers to an individual person.
Line 29: Line 32:
userid - character varying(20) - Unique ID for the user
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
 * userid - character varying(20) - Unique ID for the user
 * 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
 * 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
Line 34: Line 47:
memberstatus || character varying(10)
phone1 || character varying(15)
phone2 || character varying(15)
url || character varying(200)
comments || character varying(2000)
certificationid || numeric
mailto || character varying(100)
MOIN_ID || character varying(24)
tempkey || character varying(16)
Node table
 A wireless accesspoint at a particular location.
                  Table "public.node"
 *nodeid || numeric
|| name || character varying(1000)
|| providerid || numeric
|| carrierid || numeric ||
|| neighborhoodid || numeric ||
|| bandwidthid || numeric ||
|| statusid || numeric ||
|| certificationid || numeric ||
|| comments || character varying(2000) ||
|| longitude || character varying(200) ||
|| latitude || character varying(200) ||
|| essid || character varying(500) ||
|| channel || character varying(2) ||
|| address || character varying(200) ||
|| city || character varying(100) ||
|| state || character varying(100) ||
|| zip || character varying(20) ||
|| url1 || character varying(300) ||
|| url2 || character varying(300) ||
|| maintainer1 || character varying(20) ||
|| maintainer2 || character varying(20) ||
|| maintainer3 || character varying(20) ||
|| maintainer4 || character varying(20) ||
|| createdate || date ||
|| lastuseddate || date ||
|| node_geom || geometry ||
|| owner || numeric ||
|| height || numeric ||
|| pri_pub_ip_block || character varying(128) ||

Indexes: node_nodeid_key unique btree (nodeid)

Check constraints: "$1" (srid(node_geom) = -1)
                   "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom IS NULL))
Line 125: Line 167:
Node table
                  Table "public.node"
|| Column || Type ||
|| nodeid || numeric ||
|| name || character varying(1000) ||
|| providerid || numeric ||
|| carrierid || numeric ||
|| neighborhoodid || numeric ||
|| bandwidthid || numeric ||
|| statusid || numeric ||
|| certificationid || numeric ||
|| comments || character varying(2000) ||
|| longitude || character varying(200) ||
|| latitude || character varying(200) ||
|| essid || character varying(500) ||
|| channel || character varying(2) ||
|| address || character varying(200) ||
|| city || character varying(100) ||
|| state || character varying(100) ||
|| zip || character varying(20) ||
|| url1 || character varying(300) ||
|| url2 || character varying(300) ||
|| maintainer1 || character varying(20) ||
|| maintainer2 || character varying(20) ||
|| maintainer3 || character varying(20) ||
|| maintainer4 || character varying(20) ||
|| createdate || date ||
|| lastuseddate || date ||
|| node_geom || geometry ||
|| owner || numeric ||
|| height || numeric ||
|| pri_pub_ip_block || character varying(128) ||

Indexes: node_nodeid_key unique btree (nodeid)

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

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.

The roster of tables.

  • bandwidth carrier certification geometry_columns neighborhood node noderelation provider spacial_ref_sys status users

new tables

  • communitygroup memberstatus

User

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

Node

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

User Table

  • A record in the user table refers to an individual person.
  • userid - character varying(20) - Unique ID for the user
  • 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
  • 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

  • A wireless accesspoint at a particular location.
    • Table "public.node"
  • nodeid || numeric

|| name || character varying(1000) || providerid || numeric

carrierid

numeric

neighborhoodid

numeric

bandwidthid

numeric

statusid

numeric

certificationid

numeric

comments

character varying(2000)

longitude

character varying(200)

latitude

character varying(200)

essid

character varying(500)

channel

character varying(2)

address

character varying(200)

city

character varying(100)

state

character varying(100)

zip

character varying(20)

url1

character varying(300)

url2

character varying(300)

maintainer1

character varying(20)

maintainer2

character varying(20)

maintainer3

character varying(20)

maintainer4

character varying(20)

createdate

date

lastuseddate

date

node_geom

geometry

owner

numeric

height

numeric

pri_pub_ip_block

character varying(128)

Indexes: node_nodeid_key unique btree (nodeid)

Check constraints: "$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...


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

I pulled this right out of postgresql since it doesnt appear to be documented anywhere else -- DonPark


[CategoryDocumentation]

CommunityDatabase (last edited 2012-03-11 02:06:36 by RussellSenior)