使用Genie在Sqlite数据库中创建表？ [英] Creating a table in a Sqlite database with Genie?

问题描述

我尝试使用Genie代码创建数据库。但是，我面临文档的问题，所以我在这里问：

I am trying to create a database using Genie code. However, I am facing problems with documentation, so I am asking here!

这可以被认为是不直观的，因为我可以直接在命令行运行sqlite并创建数据集。

This can be considered unintuitive, because I could run sqlite directly on command line and create the dataset. I am doing this way for didatic reasons.

我想在python中模仿的代码是：

The code I am trying to mimic in python is:

#--------------------------------------
import apsw
#--------------------------------------
# Opening/creating database. Database name is cookbook.db3
connection=apsw.Connection("cookbook.db3")
cursor=connection.cursor()
#--------------------------------------
# Create The Tables
#--------------------------------------
sql = 'CREATE TABLE Recipes (pkiD INTEGER PRIMARY KEY, name TEXT, servings TEXT, source TEXT)'
cursor.execute(sql)
sql = 'CREATE TABLE Instructions (pkID INTEGER PRIMARY KEY, instructions TEXT, recipeID NUMERIC)'
cursor.execute(sql)
sql = 'CREATE TABLE Ingredients (pkID INTEGER PRIMARY KEY, ingredients TEXT, recipeID NUMERIC)'
cursor.execute(sql)
#--------------------------------------
# Insert Data into tables
#--------------------------------------
# Insert data into Recipe table
sql = 'INSERT INTO Recipes (name,servings,source) VALUES ("Spanish Rice",4,"Greg")'
cursor.execute(sql)
# Get the pkid for the inserted record
sql = "SELECT last_insert_rowid()"
cursor.execute(sql)
for x in cursor.execute(sql):
    lastid = x[0]
# Insert data into the instructions table
sql = 'INSERT INTO Instructions (recipeID,instructions) VALUES( %s,"Brown hamburger. Stir in all other ingredients. Bring to a boil. Stir. Lower to simmer. Cover and cook for 20 minutes or until all liquid is absorbed.")' % lastid
cursor.execute(sql)
# Insert data into the ingredients table
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 cup parboiled Rice (uncooked)")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 pound Hamburger")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"2 cups Water")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 8 oz can Tomato Sauce")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 small Onion chopped")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 clove Garlic chopped")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 tablespoon Ground Cumin")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 teaspoon Ground Oregano")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"Salt and Pepper to taste")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"Salsa to taste")' % lastid
cursor.execute(sql)
# Put in one more for good "measure"
sql = 'INSERT INTO Recipes (name,servings,source) VALUES ("Pickled Pepper-Onion Relish","9 half pints","Complete Guide to Home Canning")'
cursor.execute(sql)
# Get the pkid for the inserted record
sql = "SELECT last_insert_rowid()"
cursor.execute(sql)
for x in cursor.execute(sql):
    lastid = x[0]
sql = 'INSERT INTO Instructions (recipeID,instructions) VALUES( %s,"Wash and chop vegetables. Combine all ingredients and boil gently until mixture thickens and volume is reduced by 1/2 (about 30 minutes). Fill sterile jars with hot relish, leaving 1/2 inch head space and seal tightly. Store in refrigerator and use within one month or process in boiling water bath if extended storage is desired. Hot pack process time at 0-1000 feet for 5 minutes, 1,001 to 6000 ft 10 minutes, above 6,000 ft 15 minutes.")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"6 cups finely chopped Onions")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"3 cups finely chopped Red Peppers")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"3 cups finely chopped Green Peppers")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"1 1/2 cups sugar")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"6 cups White Vinegar (5 percent)")' % lastid
cursor.execute(sql)
sql = 'INSERT INTO Ingredients (recipeID,ingredients) VALUES ( %s,"2 tablespoons canning or pickling salt")' % lastid
cursor.execute(sql)
# Tell us we are done
print 'Done'

我学习了如何使用vala.doc ，我发现了操纵sqlite数据库的例程：Sqlite.Database。但是，我在编译时仍然遇到错误。

I have learned how to use vala.doc, and I found the routine to manipulate sqlite databases: Sqlite.Database. However, I keep getting an error on compilation.

这是我复制代码的距离：

This is how far I got on reproducing that code:

/* HOW TO CREATE THE DB WITH GENIE */

// Opening/creating db. 

[indent=4]
init
    Sqlite.Database db
    string errmsg

    int ec = Sqlite.Database.open("cookbook.db", out db)
    if ec != Sqlite.OK
        stderr.printf("Can't open database: %d: Ss\n", db.errcode(), db.errmesg)
        return -1

    // Insert data
    query:string ="""
        CREATE TABLE Recipes (pkiD INTEGER PRIMARY KEY, name TEXT, servings TEXT, source TEXT)
        """
    db.exec (query, null, out errmsg)

b $ b

它被认为是插入表到数据库，但我得到以下错误：

It was suposed to insert tables into a database, but I am getting the following error:

    valac --pkg sqlite3 cookcreate.gs 
cookcreate.gs:9.11-9.11: error: syntax error, expected `:' but got `.' with previous identifier
    Sqlite.Database db
          ^
Compilation failed: 1 error(s), 0 warning(s)

赞赏。

推荐答案

看起来好像你已经使用了Valadoc中的示例，但没有将类型信息从Vala语法转换为Genie句法。因此 Sqlite.Database db 将是 db：Sqlite.Database 。

It looks as though you have used the example in Valadoc, but not converted the type information from Vala syntax to Genie syntax. So Sqlite.Database db would be db:Sqlite.Database.

Genie中的工作示例是：

A working example in Genie would be:

[indent=4]
init
    db:Sqlite.Database   
    errmsg:string

    ec:int = Sqlite.Database.open("cookbook.sqlite", out db)
    if ec != Sqlite.OK
        stderr.printf("Can't open database: %d: %s\n", db.errcode(), db.errmsg())
        Process.exit( -1 )

    query:string ="""CREATE TABLE Recipes (
        pkiD INTEGER PRIMARY KEY, 
        name TEXT,
        servings TEXT, 
        source TEXT 
        )
    """
    db.exec (query, null, out errmsg)

需要注意的几点：

• Genie只能返回成功的结果，所以 return -1 目前不被允许。这可能会在某些时候更改，请参见 https://bugzilla.gnome.org/show_bug.cgi ？id = 707233 要解决此问题，您可以使用GLib的Process.exit（），如上面的示例中所使用。这具有缺点，即程序立即终止而没有对象破坏。因此，如果您在类中使用 final 关闭数据库连接，例如 final 块将不会称为。或者您可以 return ，总是返回0


• 逐字符串， verbatim string，非常适合在Genie中嵌入SQL： - ）

• Genie can only return a successful outcome, so return -1 isn't currently allowed. This may change at some point, see https://bugzilla.gnome.org/show_bug.cgi?id=707233 To work around this you can use GLib's Process.exit(), as used in the example above. This has the disadvantage that the program terminates immediately without object destruction. So if you are using final in your classes to close the database connection, for example, the final block will not be called. Or you can just return, which always returns 0
• Verbatim strings, """I'm a verbatim string""", are great for embedding SQL in Genie :-)

这篇关于使用Genie在Sqlite数据库中创建表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！