SourceForge has been redesigned. Learn more.
Close

[c203b8]: / examples / sqlgen / README  Maximize  Restore  History

Download this file

106 lines (85 with data), 4.1 kB

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
NOTE: This example is only a proof-of-concept and is not intended for
      production use!

This example generates SQL statements, given a tuple of column names.  It uses
MySQL, but should be easy to port to other database engines.

The following files can be used to create and populate the database:

    create_database.sql
        Creates movie_db database and users: movie_admin/admin_pw and
        movie_user/user_pw.
    create_tables.sql
        movie
            id                  int auto_increment
            title               varchar(100)
            genre_id            int
            year                year
            length              time
        genre
            id                  int auto_increment
            genre_name          varchar(100)
        director
            id                  int auto_increment
            director_name       varchar(100)
        movie_director_link
            movie_id            int
            director_id         int
            billing             int
        catalog
            movie_id            int
            dvd_number          int
            selection_number    int default 1
    drop_tables.sql

This shell script loads 6 movies:

    load_tables
        Loads the following files in the indicated order:
            genre.txt
            movie.txt
            director.txt
            movie_director_link.txt
            catalog.txt

load_mysql_schema.py
    This provides one function which loads the "schema" fact base from the
    MySQL connection provided:

        load_schema(pyke_engine, db_connection)

    All facts are asserted as universal facts so that they remain after an
    engine.reset() is done.

    The following facts are asserted:

        schema.column(table_name, col_name, type, null, key, default, extra)
        schema.primary_key(table_name, columns)
        schema.many_to_1(table_many, table_1,
                         table_many_columns, table_1_columns)
        schema.links_to(depth, start_table, end_table, joins)

database.krb
    This uses backward-chaining to build SQL statements and cook them into
    plans that execute them.  The top-level goal is:

        get_data($starting_tables, $needed_data) \
          taking(db_cursor, starting_keys)

    $starting_tables is a list of tables that you have id values for.  The
    starting_keys parameter to the plan is a dictionary mapping these table
    names to id values that identify a unique row in that table.

    The $needed_data is a tuple of column_names and/or (multi-row-name,
    (options), column_name...).

    The plan will return a dictionary with keys from $needed_data and values
    from the database.  Where the multi-row-name sub-tuple is used, the key in
    the top-level dictionary is multi-row-name (this can be anything, it
    doesn't match anything in the schema).  Its value is a tuple of
    dictionaries with the indicated column_names as keys and values from the
    database.

test.py
    This has several items of possible broader interest for debugging.

        - A debug "cursor" class to use instead of a real database cursor.
        - A "parse(str)" function that recognizes numbers, string literals and
            symbols (but not True/False/None at this point) and tuples (but
            not requiring a trailing comma for singleton tuples).
        - An "as_pattern(data)" function that converts data into a pattern.
            Strings starting with "$" are converted to pattern variables.
            Tuples ending with a string starting with "*$" are converted into
            tuple patterns with rest variables.

    Test functions:

        init()
            Creates a pyke engine and calls load_mysql_schema.load_schema.
        run()
            Loops on "goal: " prompt.  Type a goal, or trace/untrace rule_name.
            Empty string terminates the loop.  When the plan is run, it first
            runs it with the debug cursor, then enters a loop prompting for the
            starting_keys values.  These should be entered space separated.
            An empty line terminates the plan loop.