[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.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks