Programming challenge: Query a database
FedoraForum.org - Fedora Support Forums and Community
Page 1 of 14 1 2 3 11 ... LastLast
Results 1 to 15 of 197
  1. #1
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Programming challenge: Query a database

    Since I've been doing a lot of LTPJ (Linux + Tomcat + PostgreSQL + Java ) programming lately, I thought it would be fun to see how to access different databases using different programming languages. So after reading this thread on which desktops people here are using, I came up with an idea for another programming challenge: query a database and display the results.

    The rules for this challenge are as follows:

    1. Create a database called linux, containing a table called userinfo having three columns - name, gui, distro - that store a user's name, desktop gui, and distro, respectively. You can use any database system you want, as long as it's an actual database system.

      Creating the database, connection info, etc, is up to you, but for convenience I've created some generic SQL statements for creating the table and inserting some data. They should work in pretty much any database system, but you aren't required to use them (you could use your own):

      Code:
      CREATE TABLE userinfo (name TEXT, gui TEXT, distro TEXT);
      INSERT INTO userinfo (name,gui,distro) VALUES('RupertPupkin','Window Maker','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('Dan','Enlightenment','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('GarethJones','GNOME','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('glennzo','GNOME','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('Chilly Willy','GNOME','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('BBQDave','XFCE','Mint');
      INSERT INTO userinfo (name,gui,distro) VALUES('DBelton','XFCE','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('stevea','XFCE','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('Wayne','KDE','Ubuntu');
      INSERT INTO userinfo (name,gui,distro) VALUES('sonoran','XFCE','Arch');
      INSERT INTO userinfo (name,gui,distro) VALUES('smr54','dwm','Arch');
      INSERT INTO userinfo (name,gui,distro) VALUES('nonamedotc','Cinnamon','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('PatMcLJr','Openbox','Fedora');
      INSERT INTO userinfo (name,gui,distro) VALUES('kona0197','GNOME','Windows 7');
    2. Write a command-line program that takes the name of a desktop (e.g. XFCE) as
      a command-line argument and queries the linux database for the names and distros of the users who use that desktop (ordered by name), then display the results in a table format similar to this:

      Code:
      Number of matches: N
      User                | Distro
      -----------------------------------
      user 1              | distro 1
      user 2              | distro 2
      ...                 | ...
      user N              | distro N
      It's OK if the number of matches is 0. The more important part is showing the number of matches before displaying the results. Extra bonus points for making your query invulnerable to SQL injection attacks!

      Error checking is optional. Oh, and don't just write something that simply calls an existing command-line utility to do the work for you, that would be cheating!
    3. Post your program code and some sample output from the program, stating which programming language and database system you used.


    And that's it! This should be fairly simple, so I hope we get a lot of people participating and showing a wide variety of database systems, programming languages and techniques.

    *************************************************

    I'll get the challenge started with an example using Tcl to query a PostgreSQL database. You'll need the tcl-pgtcl package from the Fedora repos for this.

    Save this code in a file called db_pgsql.tcl and make it executable:
    Code:
    #!/usr/bin/tclsh
    package require Pgtcl
    set gui [lindex ${argv} 0]
    set conn [pg_connect -conninfo "host=localhost dbname=linux user=test password=test123"]
    pg_execute ${conn} "PREPARE query1 (TEXT) AS SELECT name,distro FROM userinfo WHERE gui = \$1 order by name"
    set result [pg_exec_prepared ${conn} query1 TEXT TEXT "${gui}"]
    set numRows [pg_result ${result} -numTuples]
    puts "Number of matches: ${numRows}"
    puts [format "%-20s| %s" "User" "Distro"]
    puts [string repeat "-" 35]
    pg_result ${result} -assign data
    for {set i 0} {$i < ${numRows}} {incr i} {
       puts [format "%-20s| %s" $data($i,name) $data($i,distro)]
    }
    pg_disconnect ${conn}
    Sample output:
    Code:
    $  ./db_pgsql.tcl XFCE
    Number of matches: 4
    User                | Distro
    -----------------------------------
    BBQDave             | Mint
    DBelton             | Fedora
    sonoran             | Arch
    stevea              | Fedora
    
    $ ./db_pgsql.tcl GNOME
    Number of matches: 4
    User                | Distro
    -----------------------------------
    Chilly Willy        | Fedora
    GarethJones         | Fedora
    glennzo             | Fedora
    kona0197            | Windows 7
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  2. #2
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Here's another PostgreSQL example, this time using Java:

    Save as db_pgsql.java:
    Code:
    import java.sql.*;
    import static java.lang.System.out;
    public class db_pgsql {
     public static void main(String[] args) {
       try {
          Class.forName("org.postgresql.Driver");
          String url = "jdbc:postgresql://localhost/linux?user=test&password=test123";
          Connection conn = DriverManager.getConnection(url);
          if (conn != null) {
             String query = "SELECT name,distro FROM userinfo WHERE gui = ? ORDER BY name";
             PreparedStatement prep = conn.prepareStatement(query,
                                      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
             prep.setString(1, args[0]);
             ResultSet rs = prep.executeQuery();
             rs.last();
             out.println("Number of matches: " + rs.getRow());
             out.printf("%-20s| %s\n", "User", "Distro");
             out.println(String.format("%035d", 0).replace("0", "-"));
             rs.beforeFirst();
             while (rs.next()) {
                out.printf("%-20s| %s\n", rs.getString("name"), rs.getString("distro"));
             }
             rs.close();
             prep.close();
             conn.close();
          }
       } catch (Exception e) {
          out.println(e.getMessage());
       }
     }
    }
    Compile and run:
    Code:
    $ javac db_pgsql.java
    $ java db_pgsql "Window Maker"
    Number of matches: 1
    User                | Distro
    -----------------------------------
    RupertPupkin        | Fedora
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  3. #3
    Join Date
    May 2010
    Location
    Adelaide, Australia
    Age
    27
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Yay another programming challenge

    Using Perl and a MariaDB server (DBD::mysql):

    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;
    
    my $count = 0;
    my $str = '';
    my $dbo = DBI->connect("DBI:mysql:linux;host=192.168.204.128", "root", "test") or die($DBI::errstr);
    my $q = $dbo->prepare("SELECT * FROM userinfo WHERE gui = ? ORDER BY name");
    $q->execute($ARGV[0]);
    while (my $row = $q->fetchrow_hashref) {
        $str .= sprintf "%-20s| %s\n", $row->{name}, $row->{distro};
        $count++;
    }
    $dbo->disconnect;
    print "Number of matches: $count\n";
    printf "%-20s| %s\n", "User", "Distro";
    print "-" x 35, "\n";
    print $str;
    Output:
    Code:
    [andrew@localhost ~]$ ./testdb.pl GNOME
    Number of matches: 4
    User                | Distro
    -----------------------------------
    Chilly Willy        | Fedora
    GarethJones         | Fedora
    glennzo             | Fedora
    kona0197            | Windows 7
    [andrew@localhost ~]$ ./testdb.pl KDE
    Number of matches: 2
    User                | Distro
    -----------------------------------
    ah7013              | Mageia
    Wayne               | Ubuntu
    [andrew@localhost ~]$ ./testdb.pl 'Window Maker'
    Number of matches: 1
    User                | Distro
    -----------------------------------
    RupertPupkin        | Fedora
    [andrew@localhost ~]$ ./testdb.pl blahblah
    Number of matches: 0
    User                | Distro
    -----------------------------------
    [andrew@localhost ~]$
    Last edited by ah7013; 31st January 2013 at 10:01 AM. Reason: Forgot order by name

  4. #4
    Join Date
    Apr 2010
    Location
    Earth
    Posts
    901
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    a example using Python and MongoDB. MongoDB is a objectoriented DB.

    If anyone wants to try either setup MongoDB server under your Linux or use a MongoDB provider (for example: mongohq.com, there is a free DB option up to 500 MB space). pymongo has to be installed.

    Code:
    #!/usr/bin/python
    
    from pymongo import collection
    from pymongo import Connection
    
    class MONGO:
        def __init__(self): # all this has to be adjusted
            self.host = '******.mongohq.com' # can be 'localhost' too, if server running
            self.port = 10008
            self.db_name = 'linux' # if a MongoDB provider is used create the db on their website
            self.user = '**********'
            self.pw = '***********' 
            self.authenticate = True # with local Mongo server not needed by default
            
        def connect(self):
            """Connects to the database and returns the DB connection,
        if successful"""
            try:
                self.connection = Connection(self.host, self.port)
            except TypeError:
                    print "connection settings seem to be wrong! Correct it."
                    return False
    
            self.connected_db = self.connection[self.db_name]
    
            if self.authenticate:
                if self.connected_db.authenticate(self.user, self.pw):
                    #print("authentication happened")
                    None
                else:
                    print("Authentification failed")
                    return False
            return self.connected_db
    
    class USER:
        def __init__(self):
            self.mongo = MONGO()
            self.collection = 'userinfo' # database table
    
            
        def check_connection(self):
             """return the db if MongoDB connection works, otherwise False"""
             try:
                 return self.db
             except AttributeError:
                self.db = self.mongo.connect()
                return self.db
    
        def insert_data(self):
            data = [{"name": "RupertPupkin", "gui": "Window Maker",
                     "distro": "Fedora",},
                    {"name": "Dan", "gui": "Enlightenment",
                     "distro": "Fedora",},
                    {"name": "GarethJones", "gui": "GNOME",
                     "distro": "Fedora",},
                    {"name": "RupertPupkin", "gui": "Window Maker",
                     "distro": "Fedora",},
                    {"name": "glennzo", "gui": "GNOME",
                     "distro": "Fedora",},
                    {"name": "Chilly Willy", "gui": "GNOME",
                     "distro": "Fedora",},
                    {"name": "BBQDave", "gui": "XFCE",
                     "distro": "Mint",},
                    {"name": "DBelton", "gui": "XFCE",
                     "distro": "Fedora",},
                    {"name": "stevea", "gui": "XFCE",
                     "distro": "Fedora",},
                    {"name": "Wayne", "gui": "KDE",
                     "distro": "Ubuntu",},
                    {"name": "sonoran", "gui": "XFCE",
                     "distro": "Arch",},
                    {"name": "smr54", "gui": "dwm",
                     "distro": "Arch",},
                    {"name": "nonamedotc", "gui": "Cinnamon",
                     "distro": "Fedora",},
                    {"name": "PatMcLJr", "gui": "Openbox",
                     "distro": "Fedora",},
                    {"name": "kona0197", "gui": "GNOME",
                     "distro": "Windows 7",}] 
            self.db = self.check_connection()
            collection = self.db[self.collection]
            collection.create_index("gui") # optional
            collection.insert(data)        
            
    
        def query_gui(self, gui): # improved version of this method is in post 6
            self.db = self.check_connection()
            try:
                collection = self.db[self.collection]
            except TypeError:
                print "mongo server connection not working?!"
            names = collection.find({'gui' : gui }).distinct('name')
            distro_list = []
            for name in names:
                distro = collection.find_one({'name' : name})['distro']
                distro_list.append(distro)                
            count = collection.find({'gui' : gui }).count()
            if names:
                return (names, distro_list, count)
            else:
                self.insert_data()
                return False
    
        def drop_all(self):
            """removes all data of the collection"""
            self.db = self.check_connection()
            collection = self.db[self.collection]
            collection.drop()
            
    def main(): # improved version of main() see in post 6
        query = USER()
        to_query = ['XFCE', 'GNOME']
        
        #query.drop_all()
        #query.insert_data()
        
        for item in to_query:
            queried = query.query_gui(item)
            print '\n' + item
            print "Number of matches: " + str(queried[2])
            print "- - - - - - - - - - - - - - - - - - - - - - -"
            for i, name in enumerate(queried[0]):
                print str(name) + "        |       " + str(queried[1][i])
        
            
    if __name__ == '__main__':
        main()
    my code isn't very good yet, I know. Maybe I will modify it somewhen when I have more time, so that it can be queried via command-line. Instead of the main function doing this. It's probably obvious that I'm no professional Python programmer.

    output:
    XFCE
    Number of matches: 4
    - - - - - - - - - - - - - - - - - - - - - - -
    BBQDave | Mint
    DBelton | Fedora
    stevea | Fedora
    sonoran | Arch

    GNOME
    Number of matches: 4
    - - - - - - - - - - - - - - - - - - - - - - -
    GarethJones | Fedora
    glennzo | Fedora
    Chilly Willy | Fedora
    kona0197 | Windows 7
    Last edited by Fenrin; 1st February 2013 at 01:06 PM. Reason: added a comment

  5. #5
    Join Date
    Dec 2005
    Location
    North Carolina
    Age
    32
    Posts
    1,162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    An example using C and MySQL.

    Save this example as testdb.c:

    Code:
    #include <mysql.h>
    #include <stdio.h>
    #include <string.h>
    #include <stdlib.h>
    
    int main(int argc, char *argv[])
    {
        MYSQL *conn;
        MYSQL_RES *res;
        MYSQL_ROW row;
        char *server = "localhost";
        char *user = "test";
        char *password = "test123";
        char *database = "linux";
        char query[200] = "SELECT name,distro FROM userinfo WHERE gui = \'";
    
        conn = mysql_init(NULL);
    
        if (!mysql_real_connect(conn, server,
                                user, password,
                                database, 0, NULL, 0)) {
            fprintf(stderr, "%s\n", mysql_error(conn));
            exit(1);
        }
    
        strcat(query, argv[1]);
        strcat(query, "\' ORDER BY name");
        if (mysql_query(conn, query)) {
            fprintf(stderr, "%s\n", mysql_error(conn));
            exit(1);
        }
    
        res = mysql_store_result(conn);
        printf("Number of matches: %d\n", mysql_num_rows(res));
        printf("User                | Distro\n");
        printf("-----------------------------------\n");
        while ((row = mysql_fetch_row(res)) != NULL)
          printf("%-20s| %s\n", row[0], row[1]);
    
        mysql_free_result(res);
        mysql_close(conn);
    
        return 0;
    }
    Compile with:

    Code:
    gcc -o testdb testdb.c `mysql_config --libs --cflags`
    And then to run just use ./testdb

    It only takes one argument. I need to play around with some libraries that handle command-line arguments for C. Python's facilities are fairly easy for that though.
    Last edited by Flounder; 1st February 2013 at 08:17 AM.
    Laptop: Lenovo ThinkPad T410, CPU: Intel Core i5 520M, Ram: 8GB DDR3, Hard Drive: 320GB, Graphics: Intel HD, OS: Windows 7 / Arch Linux x86_64
    Desktop: Motherboard: ASRock Fatal1ty AB350 Gaming K4, CPU: AMD Ryzen 3 1200, RAM: 8GB DDR4, Storage: Samsung 850 Pro 256GB, Graphics: Asus Radeon RX 550 4GB, OS: Arch Linux x86_64

  6. #6
    Join Date
    Apr 2010
    Location
    Earth
    Posts
    901
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Quote Originally Posted by Flounder
    [...]Python's facilities are fairly easy for that though.
    ah yes, just modified my above code so that it can be called from command line. I didn't use argparse module yet, so I didn't know it was so easy

    I also improved the query_gui method a bit.

    Code:
        def query_gui(self, gui):
            self.db = self.check_connection()
            try:
                collection = self.db[self.collection]
            except TypeError:
                print "mongo server connection not working?!"
            data = collection.find({'gui' : gui })                
            if data:
                return data
            else:
                self.insert_data()
                return False
    
    def main():
        import argparse
        parser = argparse.ArgumentParser(
            description="query MongoDB collection 'userinfo'")    
        parser.add_argument("gui", help='query a certain gui')
        gui = parser.parse_args().gui
        
        query = USER()
    
        queried = query.query_gui(gui)
        print '\n' + gui
        print "Number of matches: " + str(queried.count())
        print "- - - - - - - - - - - - - - - - - - - - - - -"
        for row in queried.sort("name"):
            print str(row['name']) + "        |       " + str(row['distro'])
    command and output:
    Code:
    $ python ./mongo2.py GNOME
    
    GNOME
    Number of matches: 4
    - - - - - - - - - - - - - - - - - - - - - - -
    Chilly Willy        |       Fedora
    GarethJones        |       Fedora
    glennzo        |       Fedora
    kona0197        |       Windows 7
    Last edited by Fenrin; 1st February 2013 at 10:32 AM.

  7. #7
    Join Date
    Oct 2010
    Location
    Canberra
    Posts
    3,507
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Re: Programming challenge: Query a database

    Thought I might do one in C++ and PostgreSql using the pqxx library:

    Code:
    // Name        : challenge.cpp
    
    #include <iostream>
    #include <iomanip>
    #include <string>
    #include <vector>
    #include <pqxx/pqxx>
    
    class UserInfo;
    class Users
    {
    protected:
    	pqxx::connection *conn;
    	std::string undef;	// value for empty column
    
    public:
    	Users();
    	~Users();
    	void fetchByGui( const std::string &gui, std::vector< UserInfo > &);
    };
    
    class UserInfo
    {
    protected:
    	std::string name;
    	std::string gui;
    	std::string distro;
    public:
    	UserInfo( const std::string &name, const std::string &gui, const std::string &distro);
    	friend std::ostream & ::operator << ( std::ostream &, UserInfo &);
    };
    
    using namespace std;
    
    Users::Users()
    {
    	conn = 0;
    	try {
    		conn = new pqxx::connection("dbname=linux");
    	}
    	catch(const std::exception &e )
    	{
    		cerr << "Error opening connection: " << e.what() << endl;
    		conn = 0;
    		throw;
    	}
    }
    
    Users::~Users()
    {
    	delete conn;
    }
    
    void Users::fetchByGui( const string &gui, vector<UserInfo> &uis )
    {
    	pqxx::work w(*conn);
    	pqxx::result r = w.exec("SELECT name, distro from userinfo where gui = " + w.quote(gui));
    	for ( auto x : r )
    	{
    		UserInfo ui(x[0].as(undef), gui, x[1].as(undef));
    		uis.push_back(ui);
    	}
    	w.commit();
    }
    
    UserInfo::UserInfo( const std::string &n, const std::string &g, const std::string &d)
    	: name(n), gui(g), distro(d)
    {}
    
    ostream & operator << ( std::ostream &f, UserInfo &ui)
    {
    	f << left << setw(20) << ui.name << "| " << ui.distro;
    	return f;
    }
    
    int main(int argc, char * argv[] )
    {
    	string gui;
    	if ( argc < 2 )
    	{
    		cerr << "usage: challenge <gui>" << endl;
    		return 1;
    	}
    	else
    		gui = argv[1];
    
    	try {
    		Users users;
    		vector<UserInfo> uis;
    		users.fetchByGui(gui, uis);
    
    		cout << "Number of matches: " << uis.size() << "\n";
    		cout << left << setw(20) << "User" << "| Distro\n";
    		cout << string(40,'-') << endl;
    		for ( auto x : uis )
    			cout << x << endl;
    	}
    	catch(const std::exception &e )
    	{
    		cerr << e.what() << endl;
    	}
    
    	return 0;
    }
    This can be compiled with
    Code:
    g++  -std=c++0x -o challenge challenge.cpp -lpqxx
    The output is:
    Code:
    $ ./challenge GNOME
    Number of matches: 4
    User                | Distro
    ----------------------------------------
    GarethJones         | Fedora
    glennzo             | Fedora
    Chilly Willy        | Fedora
    kona0197            | Windows 7

    User error. Please replace user and try again

  8. #8
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Here's one in the R language, using a SQLite database called linux.db in my home directory.
    For this you'll need to install the R-RSQLite package from the Fedora repos.

    Save this as an executable script db_sqlite.R:
    Code:
    #!/usr/bin/env Rscript
    suppressPackageStartupMessages(library(RSQLite))
    args <- commandArgs(TRUE)
    conn <- dbConnect(SQLite(), dbname=paste(path.expand("~"), "/linux.db", sep=""))
    query <- "SELECT name,distro FROM userinfo WHERE gui = ? ORDER BY name"
    rs <- dbGetPreparedQuery(conn, query, bind.data=data.frame(c(args[1])))
    x <- dbDisconnect(conn)
    cat(sprintf("Number of matches: %d\n", length(rs$name)))
    cat(sprintf("%-20s| %s\n", "User", "Distro"))
    cat(paste(rep("-",35), collapse=""), sep="\n")
    cat(sprintf("%-20s| %s\n", rs$name, rs$distro), sep="")
    Some sample output:
    Code:
    $ ./db_sqlite.R dwm
    Number of matches: 1
    User                | Distro
    -----------------------------------
    smr54               | Arch
    
    $ ./db_sqlite.R XFCE
    Number of matches: 4
    User                | Distro
    -----------------------------------
    BBQDave             | Mint
    DBelton             | Fedora
    sonoran             | Arch
    stevea              | Fedora
    
    $ ./db_sqlite.R CDE
    Number of matches: 0
    User                | Distro
    -----------------------------------
    Last edited by RupertPupkin; 2nd February 2013 at 05:35 AM.
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  9. #9
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Here's an example using OCaml with a SQLite database (~/linux.db). You'll need the ocaml-sqlite-devel package installed from the Fedora repos.

    Save this as db_sqlite.ml:
    Code:
    let dbh = Sqlite3.db_open (String.concat "/" [Sys.getenv "HOME"; "linux.db"]);;
    let prep = Sqlite3.prepare dbh "SELECT name,distro FROM userinfo WHERE gui = ? ORDER BY name";;
    Sqlite3.bind prep 1 (Sqlite3.Data.TEXT Sys.argv.(1));;
    let count = ref 0;;
    let rs = ref "";;
    while Sqlite3.step prep = Sqlite3.Rc.ROW do
       rs := String.concat "" [!rs; (Printf.sprintf "%-20s| %s\n"
                               (Sqlite3.Data.to_string (Sqlite3.column prep 0))
                               (Sqlite3.Data.to_string (Sqlite3.column prep 1)))];
       count := !count + 1;
    done;;
    Sqlite3.db_close dbh;;
    Printf.printf "Number of matches: %d\n" !count;;
    Printf.printf "%-20s| %s\n%s\n%s" "User" "Distro" (String.make 35 '-') !rs;;
    Compile like this in 64-bit Fedora (replace the "lib64" with "lib" in 32-bit Fedora):
    Code:
    ocamlopt -I /usr/lib64/ocaml/sqlite3 -o db_sqlite sqlite3.cmxa db_sqlite.ml
    Sample output:
    Code:
    $ ./db_sqlite GNOME
    Number of matches: 4
    User                | Distro
    -----------------------------------
    Chilly Willy        | Fedora
    GarethJones         | Fedora
    glennzo             | Fedora
    kona0197            | Windows 7
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  10. #10
    Join Date
    Oct 2010
    Location
    Canberra
    Posts
    3,507
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Re: Programming challenge: Query a database

    My previous C++/postgres version was a bit verbose, including some error detection and a couple of classes. To demonstrate that C++ can be quite minimal if you want, I offer the following example:

    Code:
    #include <iostream>
    #include <iomanip>
    #include <string>
    #include <pqxx/pqxx>
    
    using namespace std;
    using namespace pqxx;
    
    int main( int argc, char * argv[] ) {
            connection conn("dbname=linux");
            work w(conn);
            result r = w.exec("SELECT name, distro from userinfo where gui = " + w.quote(argv[1]));
            cout << "Number of matches: " << r.size() << "\n";
            cout << left << setw(20) << "User" << "| Distro\n" << string(35,'-') << endl;
            string undef;
            for ( auto x : r ) {
                    cout << left << setw(20) << x[0].as(undef) << "| " << x[1] << "\n";
            }
            cout << endl;
            w.commit();
            return 0;
    }
    As before it can be built using
    Code:
    g++  -std=c++0x -o minimal minimal.cpp -lpqxx
    It produces the following output:
    Code:
    $ ./minimal GNOME
    Number of matches: 4
    User                | Distro
    -----------------------------------
    GarethJones         | Fedora
    glennzo             | Fedora
    Chilly Willy        | Fedora
    kona0197            | Windows 7
    
    $ ./minimal 'Window Maker'
    Number of matches: 1
    User                | Distro
    -----------------------------------
    RupertPupkin        | Fedora

    User error. Please replace user and try again

  11. #11
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Here's an example using Lua with SQLite. You'll need the lua-dbi package installed from the Fedora repos for this.

    Code:
    #!/usr/bin/lua
    require('DBI')
    count = 0
    data = ""
    conn = assert(DBI.Connect("SQLite3", os.getenv("HOME").."/linux.db"))
    prep = conn:prepare("SELECT name,distro FROM userinfo WHERE gui = ? ORDER BY name")
    prep:execute(arg[1])
    for rs in prep:rows(true) do
       data = data..string.format("%-20s| %s\n", rs.name, rs.distro)
       count = count + 1
    end
    prep:close()
    conn:close()
    print(string.format("Number of matches: %d", count))
    print(string.format("%-20s| %s\n%s\n%s", "User", "Distro", string.rep("-", 35), data))
    Sample output:
    Code:
    $ ./db_sqlite.lua XFCE
    Number of matches: 4
    User                | Distro
    -----------------------------------
    BBQDave             | Mint
    DBelton             | Fedora
    sonoran             | Arch
    stevea              | Fedora
    
    $ ./db_sqlite.lua fvwm
    Number of matches: 0
    User                | Distro
    -----------------------------------
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  12. #12
    Join Date
    May 2010
    Location
    Adelaide, Australia
    Age
    27
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Perl and SQLite. On Mageia I installed the rpm perl-DBD-SQLite-1.350.0-1.mga2 from the repos for this to work. I'm not sure what it's called in Fedora's repos.
    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;
    
    my $db = 'linux.db';
    my $count = 0;
    my $str = '';
    my $dbo = DBI->connect("DBI:SQLite:dbname=$db") or die($DBI::errstr);
    my $q = $dbo->prepare("SELECT * FROM userinfo WHERE gui = ? ORDER BY name");
    $q->execute($ARGV[0]);
    while (my $row = $q->fetchrow_hashref) {
        $str .= sprintf "%-20s| %s\n", $row->{name}, $row->{distro};
        $count++;
    }
    $dbo->disconnect;
    print "Number of matches: $count\n";
    printf "%-20s| %s\n", "User", "Distro";
    print "-" x 35, "\n";
    print $str;
    Example run:
    Code:
    [andrew@localhost ~]$ ./sqlite.pl GNOME
    Number of matches: 4
    User                | Distro
    -----------------------------------
    Chilly Willy        | Fedora
    GarethJones         | Fedora
    glennzo             | Fedora
    kona0197            | Windows 7
    [andrew@localhost ~]$ ./sqlite.pl i3
    Number of matches: 0
    User                | Distro
    -----------------------------------
    [andrew@localhost ~]$

  13. #13
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Quote Originally Posted by ah7013
    Perl and SQLite. On Mageia I installed the rpm perl-DBD-SQLite-1.350.0-1.mga2 from the repos for this to work. I'm not sure what it's called in Fedora's repos.
    The Fedora package has the same name (perl-DBD-SQLite). I just installed it and tried your Perl script, it worked perfectly.

    Here's Ruby and SQLite, which requires the rubygem-sqlite3 package from the Fedora repos. Ruby has one of the few SQLite drivers I've seen with support for forward/reverse cursors (similar to Java's JDBC). Yay Ruby!

    Code:
    #!/usr/bin/ruby
    require 'sqlite3'
    db = SQLite3::Database.new(Dir.home + "/linux.db")
    db.results_as_hash = true
    count = 0
    rs = db.query("SELECT name,distro FROM userinfo WHERE gui = ? ORDER BY name", ARGF.argv[0])
    while rs.next() != nil
       count += 1
    end
    rs.reset()
    print("Number of matches: ", count, "\n")
    printf("%-20s| %s\n%s\n", "User", "Distro", "-" * 35)
    rs.each() do |row|
       printf("%-20s| %s\n", row["name"], row["distro"])
    end
    rs.close
    db.close
    Sample output:
    Code:
    $ ./db_sqlite.rb KDE
    Number of matches: 2
    User                | Distro
    -----------------------------------
    Wayne               | Ubuntu
    ah7013              | Mageia
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  14. #14
    Join Date
    Nov 2006
    Location
    Detroit
    Posts
    7,361
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Latest update: we now have 11 examples.

    Here's a summary of what we have right now, first organized by programming language with the corresponding database:
    1. Perl: MariaDB, SQLite
    2. Tcl: PostgreSQL
    3. Java: PostgreSQL
    4. Python: MongoDB
    5. C: MySQL
    6. C++: PostgreSQL
    7. R: SQLite
    8. OCaml: SQLite
    9. Lua: SQLite
    10. Ruby: SQLite


    Here's the list organized by database with the corresponding programming language:
    1. SQLite: R, OCaml, Lua, Perl, Ruby
    2. PostgreSQL: Tcl, Java, C++
    3. MariaDB: Perl
    4. MongoDB: Python
    5. MySQL: C
    OS: Fedora 35 x86_64 | Machine: Lenovo ThinkCentre M91P | CPU: Intel Core i5-2500 3.30GHz | RAM: 28GB PC3-12800 DDR3 | Disk: 500GB SATA | Video: Intel HD Graphics 2000 128MB | Sound: Turtle Beach Santa Cruz CS4630 | Ethernet: Intel 82579LM

  15. #15
    Join Date
    May 2010
    Location
    Adelaide, Australia
    Age
    27
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Programming challenge: Query a database

    Quote Originally Posted by RupertPupkin
    The Fedora package has the same name (perl-DBD-SQLite). I just installed it and tried your Perl script, it worked perfectly.
    Ok cool, thanks for testing

    about to set up postgresql and do another perl one for that as well. Was meant to try it yesterday but I got caught up in reading about Mojolicious

    ---------- Post added at 03:50 PM ---------- Previous post was at 03:03 PM ----------

    And here is Perl+Postgresql (perl-DBD-Pg package on Mageia and I assume it's the same on Fedora)
    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;
    
    my $db = 'linux';
    my $server = '192.168.122.3';
    my $count = 0;
    my $str = '';
    my $dbo = DBI->connect("DBI:Pg:dbname=$db;host=$server", 'root', 'test') or die($DBI::errstr);
    my $q = $dbo->prepare("SELECT * FROM userinfo WHERE gui = ? ORDER BY name");
    $q->execute($ARGV[0]);
    while (my $row = $q->fetchrow_hashref) {
        $str .= sprintf "%-20s| %s\n", $row->{name}, $row->{distro};
        $count++;
    }
    $dbo->disconnect;
    print "Number of matches: $count\n";
    printf "%-20s| %s\n", "User", "Distro";
    print "-" x 35, "\n";
    print $str;
    Example runs:
    Code:
    [andrew@localhost ~]$ ./pg.pl XFCE
    Number of matches: 4
    User                | Distro
    -----------------------------------
    BBQDave             | Mint
    DBelton             | Fedora
    sonoran             | Arch
    stevea              | Fedora
    [andrew@localhost ~]$ ./pg.pl 'Window Maker'
    Number of matches: 1
    User                | Distro
    -----------------------------------
    RupertPupkin        | Fedora
    [andrew@localhost ~]$

Page 1 of 14 1 2 3 11 ... LastLast

Similar Threads

  1. Programming challenge: Create a GUI window
    By RupertPupkin in forum Programming & Packaging
    Replies: 143
    Last Post: 18th January 2020, 05:11 PM
  2. Replies: 3
    Last Post: 5th October 2008, 03:25 AM
  3. Programming For differnt OS with Database
    By clifweb in forum Programming & Packaging
    Replies: 16
    Last Post: 4th January 2008, 06:27 PM
  4. linux programming vs. windows programming
    By unlovedwarrior in forum Programming & Packaging
    Replies: 11
    Last Post: 22nd October 2006, 09:14 PM
  5. Automatic daily login script *Programming Challenge*
    By SickFreak in forum Programming & Packaging
    Replies: 4
    Last Post: 23rd November 2005, 11:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •