SQLite

[LoneAngelIbesu]

Originally Posted by Damix2

Just in general a 'sub-table' in a ERD is just 2 tables in a parent-child relationship. The child will always hold the foreign key to point towards the parent, not entirely sure why you are getting an error.

Sorry, my help was fairly useless.

Does the following link give any valuable information?

http://sqlite.org/omitted.html

More specifically, this bit on foreign-key constraints:

FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers. The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.

[Damix2]

Originally Posted by LoneAngelIbesu

Does the following link give any valuable information?

http://sqlite.org/omitted.html

More specifically, this bit on foreign-key constraints:

As for your error? No, not really, at least not that I can see. The fact that SQLite doesn't support referential integrity is surprising I'd say. Also no outer joins (granted, that can be accomplished in other ways.)

[LoneAngelIbesu]

Originally Posted by Damix2

As for your error? No, not really, at least not that I can see. The fact that SQLite doesn't support referential integrity is surprising I'd say. Also no outer joins (granted, that can be accomplished in other ways.)

Using requestsql2() pointing to the "sqlite_master" database results in no error. However, when trying to access the data, nothing is outputted.

PHP Code:

  sql.execute(
    "CREATE TABLE IF NOT EXISTS shops ("
    @ "title TEXT NOT NULL DEFAULT 'Shop', "
    @ "stype TEXT NOT NULL DEFAULT 'Server', "
    @ "owner TEXT NOT NULL DEFAULT 'Unknown', "
    @ "kerou INT NOT NULL DEFAULT 5000, "
    @ "desc TEXT NOT NULL DEFAULT 'Description text.', "
    @ "createtime INT NOT NULL DEFAULT CURRENT_TIMESTAMP)", false);
  requestsql2("sqlite_master",
    "CREATE TABLE IF NOT EXISTS shops.items ("
    @ "arch TEXT NOT NULL DEFAULT 'Unknown', "
    @ "price INT DEFAULT 0, "
    @ "stock INT DEFAULT 0)", false);
  sql.execute(format(
    "INSERT INTO shops VALUES('%s','%s','%s',%s,'%s',%s)",
    "Test Shop", "Server", "NPCServer", 5000, "Description text.", "CURRENT_TIMESTAMP"), false);
  requestsql2("sqlite_master",
    "INSERT INTO shops.items VALUES('arch1',1000,10)", false);
  temp.sqltest = sql.execute("SELECT * FROM shops WHERE title='Test Shop'", true);
  for(temp.row: temp.sqltest.rows) {
    echo("Title: " @ temp.row.title);
    for(temp.irow: temp.row.items) { //I've also tried temp.row.items.rows
      echo(temp.irow.arch @ ": " @ temp.irow.stock);
    }
  } 


The execute() command is a simple error checker:

PHP Code:

public function execute(query, isreq) {
  temp.req = requestsql(temp.query, temp.isreq);
  if (temp.req.error != "") {
    echo("SQL Error: " @ temp.req.error);
    echo("      Query: " @ temp.query);
  }
  if (temp.isreq && !temp.req.completed &&
      !waitfor(temp.req, "onReceiveData", 5))
    return null;
  return temp.req;
} 


[Inverness]

Originally Posted by LoneAngelIbesu

Using requestsql2() pointing to the "sqlite_master" database results in no error.

sqlite_master is a table not a database. Stefan said in an earlier post that requestsql2() is for choosing between mysql and sqlite databases, so at the moment it is useless to you and you shouldn't use it.

select tbl_name from sqlite_master where type='table' order by tbl_name

That would list all tables in the SQLite database.

[LoneAngelIbesu]

Originally Posted by Inverness

Stefan said in an earlier post that requestsql2() is for choosing between mysql and sqlite databases, so at the moment it is useless to you and you shouldn't use it.

The /scripthelp entry should be updated as such, then.

As good as all of this information is, none of it helps with my original question of how to create 'sub-tables'. Just a light reminder...

[Inverness]

Originally Posted by LoneAngelIbesu

As good as all of this information is, none of it helps with my original question of how to create 'sub-tables'. Just a light reminder...

No such thing as sub-tables in this case. The syntax you're thinking of is for creating a table in a database other than the default, which is useless in this case since there is only one database. Just create a normal table.

[LoneAngelIbesu]

Originally Posted by Inverness

No such thing as sub-tables in this case. The syntax you're thinking of is for creating a table in a database other than the default, which is useless in this case since there is only one database. Just create a normal table.

Simply because the syntax is incorrect doesn't mean that what I'm trying to do is impossible, mind you. I've considered using a separate table, but would favor my original plan to avoid overly-complicated referencing.

I've looked in to creating VIEWS, but I'm not sure if I'm doing it correctly.

PHP Code:

  sql.execute(
    "CREATE TABLE IF NOT EXISTS shops (
      title TEXT NOT NULL DEFAULT 'Shop', 
      stype TEXT NOT NULL DEFAULT 'Server', 
      owner TEXT NOT NULL DEFAULT 'Unknown', 
      arch TEXT NOT NULL DEFAULT 'Unknown', 
      price INT DEFAULT 0, 
      stock INT DEFAULT 0, 
      kerou INT NOT NULL DEFAULT 5000, 
      desc TEXT NOT NULL DEFAULT 'Description text.', 
      createtime INT NOT NULL DEFAULT CURRENT_TIMESTAMP)", false);
  sql.execute(
    "CREATE VIEW shopitems AS SELECT arch, price, stock FROM shops", false);
  sql.execute(
    "CREATE TRIGGER insert_item INSTEAD OF INSERT ON shopitems
      BEGIN
        INSERT INTO shops (arch, price, stock) VALUES (arch, price, stock);
      END;
     CREATE TRIGGER update_item INSTEAD OF UPDATE ON shopitems
      BEGIN
        UPDATE shops SET arch = new.arch;
        UPDATE shops SET price = new.price;
        UPDATE shops SET stock = new.stock;
      END;", false);