Table creation/Postal addresses

Table creation/Postal addresses
You are encouraged to solve this task according to the task description, using any language you may know.

In this task, the goal is to create a table to store addresses. You may assume that all the addresses to be stored will be located in the USA. As such, you will need (in addition to a field holding a unique identifier) a field holding the street address, a field holding the city, a field holding the state code, and a field holding the zipcode. Choose appropriate types for each field.

For non-database languages, show how you would open a connection to a database (your choice of which) and create an address table in it. You should follow the existing models here for how you would structure the table.

ALGOL 68

Works with: ALGOL 68 version Standard - no extensions to language used

Works with: ALGOL 68G version Any - tested with release mk15-0.8b.fc9.i386

<lang algol68>MODE ADDRESS = STRUCT( INT page, FLEX[50]CHAR street, FLEX[25]CHAR city, FLEX[2]CHAR state, FLEX[10]CHAR zip ); FORMAT address repr = $"Page: "gl"Street: "gl"City: "gl"State: "gl"Zip: "gll$;

INT errno; FILE sequence; errno := open(sequence, "sequence.txt", stand back channel); SEMA sequence sema := LEVEL 1;

OP NEXTVAL = ([]CHAR table name)INT: (

 INT out;
 # INT table page = 0; # # only one sequence implemented #
 # DOWN sequence sema; # # NO interprocess concurrency protection #
   on open error(sequence, 
     (REF FILE f)BOOL: (
       reset(sequence); #set(table page,1,1);# 
       put(sequence, 0);
       try again;
       FALSE
     )
   );
   try again:
   reset(sequence); #set(table page,1,1);# get(sequence,out);
   out +:=1; 
   reset(sequence); #set(table page,1,1);# put(sequence,out);
 # UP sequence sema; #
 out

);

OP INIT = (REF ADDRESS self)REF ADDRESS: ( page OF self := NEXTVAL "address"; self);

REF ADDRESS john brown = INIT LOC ADDRESS;

john brown := (page OF john brown, "10 Downing Street","London","England","SW1A 2AA");

printf((address repr, john brown));

FILE address table; errno := open(address table,"address.txt",stand back channel);

1. set(address table, page OF john brown,1,1); - standard set page not available in a68g #

put bin(address table, john brown); close(address table)</lang> Output:

Page:          +1
Street: 10 Downing Strreet
City: London
State: England
Zip: SW1A 2AA

C

Library: SQLite

<lang c>#include <stdio.h>

1. include <stdlib.h>
2. include <sqlite3.h>

const char *code = "CREATE TABLE address (\n" " addrID INTEGER PRIMARY KEY AUTOINCREMENT,\n" " addrStreet TEXT NOT NULL,\n" " addrCity TEXT NOT NULL,\n" " addrState TEXT NOT NULL,\n" " addrZIP TEXT NOT NULL)\n" ;

int main() {

 sqlite3 *db = NULL;
 char *errmsg;
 
 if ( sqlite3_open("address.db", &db) == SQLITE_OK ) {
   if ( sqlite3_exec(db, code, NULL, NULL,  &errmsg) != SQLITE_OK ) {
     fprintf(stderr, errmsg);
     sqlite3_free(errmsg);
     sqlite3_close(db);
     exit(EXIT_FAILURE);
   }
   sqlite3_close(db);
 } else {
   fprintf(stderr, "cannot open db...\n");
   sqlite3_close(db);
   exit(EXIT_FAILURE);
 }
 return EXIT_SUCCESS;

}</lang>

DB2 UDB

<lang sql>CREATE TABLE Address ( addrID Integer generated by default as identity, addrStreet Varchar(50) not null, addrCity Varchar(25) not null, addrState Char(2) not null, addrZIP Char(10) not null )</lang>

J

J is a programming language, not a database, but it ships with a database built in the programming language called JDB. Using that, assuming hd is your database, then:

<lang j> Create__hd 'Address';noun define addrID autoid; addrStreet varchar addrCity varchar addrState char addrZip char )</lang>

Of course J can connect external databases too, using e.g. ODBC. See the list of J database topics.

Mathematica

<lang Mathematica>TableCreation="CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL )";

Needs["DatabaseLink`"] conn=OpenSQLConnection[ JDBC[ "mysql","databases:1234/conn_test"], "Username" -> "test"] SQLExecute[ conn, TableCreation]</lang>

MySQL

<lang mysql>CREATE TABLE `Address` (

   `addrID`       int(11)     NOT NULL   auto_increment,
   `addrStreet`   varchar(50) NOT NULL   default ,
   `addrCity`     varchar(25) NOT NULL   default ,
   `addrState`    char(2)     NOT NULL   default ,
   `addrZIP`      char(10)    NOT NULL   default ,
   PRIMARY KEY (`addrID`)

);</lang>

Oracle

<lang sql>CREATE SEQUENCE seq_address_pk START BY 100 INCREMENT BY 1 / CREATE TABLE address (

   addrID   NUMBER DEFAULT seq_address_pk.nextval,
   street   VARCHAR2( 50 ) NOT NULL,
   city     VARCHAR2( 25 ) NOT NULL,
   state    VARCHAR2( 2 ) NOT NULL,
   zip      VARCHAR2( 20 ) NOT NULL,
   CONSTRAINT address_pk1 PRIMARY KEY ( addrID )

) /</lang>

Oz

Translation of: Python

Library: SQLite

Library: Ozsqlite

The SQLite version that comes with Ozsqlite does not understand "AUTOINCREMENT". <lang oz>declare

 [Sqlite] = {Module.link ['x-ozlib:/sqlite/Sqlite.ozf']}

 DB = {Sqlite.open 'test.db'}

in

 try

    {Sqlite.exec DB
     "CREATE TABLE address ("
     #"addrID		INTEGER PRIMARY KEY,"
     #"addrStreet	TEXT NOT NULL,"
     #"addrCity	TEXT NOT NULL,"
     #"addrState	TEXT NOT NULL,"
     #"addrZIP		TEXT NOT NULL"
     #")" _}

 catch E then
    {Inspector.configure widgetShowStrings true}
    {Inspect E}
 finally
    {Sqlite.close DB}
 end</lang>

Perl

<lang perl>use DBI;

my $db = DBI->connect('DBI:mysql:database:server','login','password');

my $statment = <<EOF; CREATE TABLE `Address` (

   `addrID`       int(11)     NOT NULL   auto_increment,
   `addrStreet`   varchar(50) NOT NULL   default ,
   `addrCity`     varchar(25) NOT NULL   default ,
   `addrState`    char(2)     NOT NULL   default ,
   `addrZIP`      char(10)    NOT NULL   default ,
   PRIMARY KEY (`addrID`)

); EOF

my $exec = $db->prepare($statment); $exec->execute;</lang>

This example uses mysql, but DBI supports a extensive list of database drivers. See dbi.perl.org for more info.

PHP+SQLite

Translation of: Python

not tested <lang php><?php $db = new SQLite3(':memory:'); $db->exec("

   CREATE TABLE address (
       addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
       addrStreet TEXT NOT NULL,
       addrCity   TEXT NOT NULL,
       addrState  TEXT NOT NULL,
       addrZIP    TEXT NOT NULL
   )

"); ?></lang>

PicoLisp

PicoLisp has built-in database functionality, in the form of (non-relational) entity/relations, built on top of persistent objects (so-called external symbols)

Define an "address" entity, and create the database: <lang PicoLisp>(class +Adr +Entity) (rel nm (+Sn +Idx +String)) # Name [Soundex index] (rel str (+String)) # Street (rel zip (+Ref +String)) # ZIP [Non-unique index] (rel cit (+Fold +Idx +String)) # City [Folded substring index] (rel st (+String)) # State (rel tel (+Fold +Ref +String)) # Phone [Folded non-unique index] (rel em (+Ref +String)) # EMail [Non-unique index] (rel txt (+Blob)) # Memo (rel jpg (+Blob)) # Photo

(pool "address.db") # Create database</lang> Create a first entry, and show it: <lang PicoLisp>(show

  (new! '(+Adr)  # Create a record
     'nm "FSF Inc."
     'str "51 Franklin St"
     'st "Boston, MA"
     'zip "02110-1301" ) )</lang>

Output:

{2} (+Adr)
   zip "02110-1301"
   st "Boston, MA"
   str "51 Franklin St"
   nm "FSF Inc."

Interactive "select": <lang PicoLisp>(select nm zip +Adr nm "FSF") # Select name, zip from Adr where name = FSF*</lang> Output:

"FSF Inc." "02110-1301" {2}

PostgreSQL

<lang sql>CREATE SEQUENCE address_seq start 100; CREATE TABLE address (

   addrID   int4 PRIMARY KEY DEFAULT nextval('address_seq'),
   street   varchar(50) not null,
   city     varchar(25) not null,
   state    varchar(2) not null,
   zip      varchar(20) not null

);</lang>

PureBasic+SQLite

Easiest approach with sqlite. Further possible: PostgresQL or each other over ODBC. <lang Purebasic> UseSQLiteDatabase() Procedure CheckDatabaseUpdate(Database, Query$)

  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
     Print(DatabaseError())
  EndIf
  ProcedureReturn Result

EndProcedure openconsole() DatabaseFile$ = GetCurrentDirectory()+"/rosettadb.sdb" If CreateFile(0, DatabaseFile$)

  CloseFile(0)
   If OpenDatabase(0, DatabaseFile$, "", "")
     CheckDatabaseUpdate(0,"CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT,	addrStreet TEXT Not NULL, addrCity TEXT Not NULL, addrState TEXT Not NULL, addrZIP TEXT Not NULL)")
     CloseDatabase(0)
  Else
     print("Can't open database !")
  EndIf

Else

  print("Can't create the database file !")

EndIf closeconsole() </lang>

Python+SQLite

Library: SQLite

<lang python>>>> import sqlite3 >>> conn = sqlite3.connect(':memory:') >>> conn.execute(CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL

   ))

<sqlite3.Cursor object at 0x013265C0> >>> </lang>

REXX

A REXX program can call SQL or any other database system, but the version shown here is a RYO (roll your own).
Practically no error checking (for invalid fields, etc.) has been coded
The fields are for the most part, USA specific, but could be expanded for other countries.
In addition to "state", fields such as province, municipality, ward, parish, country, etc) could be added without exclusion. <lang rexx> /*REXX program to create/build/list a table of US postal addresses. */ /*┌────────────────────────────────────────────────────────────────────┐

 │ Format of an entry in the USA address/city/state/zipcode structure.│
 │                                                                    │
 │ The "structure" name can be any legal variable name, but here the  │
 │ name will be shorted to make these comments (and program) easier   │
 │ to read; it's name will be    @USA     (in any case).  In addition,│
 │ the following variables names (stemmed array tails) will need to   │
 │ be kept unitialized  (that is, not used for any variable name).    │
 │ To that end, each of these "hands-off" variable names will have a  │
 │ underscore in the beginning of each name.  Other possibilities are │
 │ to have a trailing underscore (or both leading and trailing), some │
 │ other special eye-catching character such as:  ! @ # $ ?           │
 │                                                                    │
 │ Any field not specified will have a value of "null"  (length 0).   │
 │                                                                    │
 │ Any field can contain any number of characters, this can be limited│
 │ by the restrictions imposed by standards or USA legal definitions. │
 │ Any number of fields could be added (with invalid field testing).  │
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.0             the number of entries in the @USA "array".     │
 │                                                                    │
 │       nnn           is some positive integer (no leading zeros, it │
 │                       can be any length).                          │
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.nnn._name     = name of person, business, or lot description.│
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.nnn._addr     = 1st street address                           │
 │  @USA.nnn._addr2    = 2nd street address                           │
 │  @USA.nnn._addr3    = 3rd street address                           │
 │  @USA.nnn._addrNN   =  ...  (any number, but in sequential order). │
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.nnn._state    = US postal code for the state/terrority/etc.  │
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.nnn._city     = offical city name, may include any char.     │
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.nnn._zip      = US postal zipcode,  5 digit format or        │
 │                                              10 char format.       │
 ├────────────────────────────────────────────────────────────────────┤
 │  @USA.nnn._upHist   = update History (who, date and timestamp).    │
 └────────────────────────────────────────────────────────────────────┘*/

@USA.=; @USA.0=0

@usa.0=@usa.0+1 /*bump the unique number for use.*/ call @USA '_city','Boston' call @USA '_state','MA' call @USA '_addr',"51 Franklin Street" call @USA '_name',"FSF Inc." call @USA '_zip','02110-1301'

@usa.0=@usa.0+1 /*bump the unique number for use.*/ call @USA '_city','Washington' call @USA '_state','DC' call @USA '_addr',"The Oval Office" call @USA '_addr2',"1600 Pennsylvania Avenue NW" call @USA '_name',"The White House" call @USA '_zip',20500

call @USA 'list' exit

/*───────────────────────────────@USA subroutine────────────────────────*/ @USA: procedure expose @USA.; parse arg what,txt; arg ?; nn=@usa.0 if ?\=='LIST' then do

                  call value '@USA.'nn"."what,txt
                  call value '@USA.'nn".upHist",userid() date() time()
                  end
             else do nn=1 for @usa.0
                  call @USA_list
                  end

return

/*───────────────────────────────@USA_tell subroutine-------------------*/ @USA_tell: _=value('@USA.'nn"."arg(1));

          if _\== then say right(translate(arg(1),,'_'),6) "==>" _
          return

/*───────────────────────────────@USA_list subroutine-------------------*/ @USA_list: call @USA_tell '_name' call @USA_tell '_addr'

   do j=2 until _==
   call @USA_tell '_addr'j
   end

call @USA_tell '_city' call @USA_tell '_state' call @USA_tell '_zip' say copies('-',40) return </lang> Output (data used is within the REXX program):

  name ==> FSF Inc.
  addr ==> 51 Franklin Street
  city ==> Boston
 state ==> MA
   zip ==> 02110-1301
----------------------------------------
  name ==> The White House
  addr ==> The Oval Office
 addr2 ==> 1600 Pennsylvania Avenue NW
  city ==> Washington
 state ==> DC
   zip ==> 20500 
----------------------------------------

Ruby

With PStore

PStore implements a persistent key store with transactions. This is a NoSQL database. Each transaction reads the entire database into memory, and then writes it again, so PStore is not good for large databases.

<lang ruby>require 'pstore' require 'set'

Address = Struct.new :id, :street, :city, :state, :zip

db = PStore.new("addresses.pstore") db.transaction do

 db[:next] ||= 0       # Next available Address#id
 db[:ids] ||= Set[]    # Set of all ids in db

end</lang>

To put an Address inside this PStore:

<lang ruby>db.transaction do

 id = (db[:next] += 1)
 db[id] = Address.new(id,
                      "1600 Pennsylvania Avenue NW",
                      "Washington", "DC", 20500)
 db[:ids].add id

end</lang>

With SQLite

Translation of: Python

Library: sqlite3-ruby

<lang ruby>require 'sqlite3'

db = SQLite3::Database.new(':memory:') db.execute("

   CREATE TABLE address (
       addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
       addrStreet TEXT NOT NULL,
       addrCity   TEXT NOT NULL,
       addrState  TEXT NOT NULL,
       addrZIP    TEXT NOT NULL
   )

")</lang>

SAS

<lang sql>DATA address;

  LENGTH addrID 8. street 50$ city 25$ state 2$ zip 20$;
  STOP;

RUN;</lang>

Scheme

Library: SQLite

This example works with Chicken Scheme, using its sql-de-lite library:

<lang scheme> (use sql-de-lite)

(define *db* (open-database "addresses"))

(exec ; create and run the SQL statement

 (sql *db*
      "CREATE TABLE address (
       addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
       addrStreet TEXT NOT NULL,
       addrCity   TEXT NOT NULL,
       addrState  TEXT NOT NULL,
       addrZIP    TEXT NOT NULL
   )"

))

(close-database *db*) ; finally, close database </lang>

Tcl+SQLite

Library: SQLite

<lang tcl>package require sqlite3

sqlite3 db address.db db eval {

   CREATE TABLE address (

addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL

   )

}</lang>

Transact-SQL (MSSQL)

<lang sql>CREATE TABLE #Address (

   addrID       int        NOT NULL   Identity(1,1) PRIMARY KEY,
   addrStreet   varchar(50) NOT NULL ,  
   addrCity     varchar(25) NOT NULL , 
   addrState    char(2)     NOT NULL , 
   addrZIP      char(10)    NOT NULL

) drop table #Address</lang>