In Zittme, you don't write SQL directly; you declare tables and queries in XML. The same code runs on different database types, and values are bound automatically, so you don't have to worry about SQL injection.

Declaring tables: schemas/*.xml

The file name is the table name. The core creates the table when the module is installed.

<table name="mymodule_item">
	<column name="item_srl" type="bigint" notnull="notnull" primarykey="primarykey" />
	<column name="member_srl" type="bigint" notnull="notnull" index="idx_member_srl" />
	<column name="title" type="varchar" size="250" notnull="notnull" />
	<column name="content" type="bigtext" />
	<column name="status" type="varchar" size="10" notnull="notnull" default="open" index="idx_status" />
	<column name="regdate" type="char" size="14" index="idx_regdate" />
</table>

• Main types: number / bigint / varchar (size required) / char / text / bigtext / date / float
• By convention, dates are stored in char(14) as a YYYYMMDDHHIISS string (date('YmdHis')).
• Unique IDs are issued with getNextSequence() and stored in a *_srl column.

Adding columns on a live site: the core does not automatically add new columns to tables that already exist. You need to do two things together: edit the schema XML, and call addColumn() in the Install class's moduleUpdate.

Declaring queries: queries/*.xml

SELECT

<query id="getItems" action="select">
	<tables>
		<table name="mymodule_item" />
	</tables>
	<columns>
		<column name="*" />
	</columns>
	<conditions>
		<condition operation="equal" column="status" var="status" default="open" />
		<condition operation="like" column="title" var="s_title" pipe="and" />
		<condition operation="more" column="regdate" var="start_regdate" pipe="and" />
	</conditions>
	<navigation>
		<index var="sort_index" default="item_srl" order="desc" />
		<list_count var="list_count" default="20" />
		<page var="page" default="1" />
	</navigation>
</query>

• var is the name of the value passed from PHP. If you don't pass a value, that condition is dropped entirely. Add notnull="notnull" to required conditions.
• default is the default value used when no value is given.
• pipe is the connector (and/or) to the previous condition. Don't use it on the first condition.

All available operations:

• If you put list_count and page in <navigation>, the executeQueryArray result also includes page information (page_navigation).

INSERT / UPDATE / DELETE

<query id="insertItem" action="insert">
	<tables>
		<table name="mymodule_item" />
	</tables>
	<columns>
		<column name="item_srl" var="item_srl" notnull="notnull" filter="number" />
		<column name="title" var="title" notnull="notnull" />
		<column name="content" var="content" />
		<column name="status" var="status" default="open" />
		<column name="regdate" var="regdate" />
	</columns>
</query>

<query id="updateItemStatus" action="update">
	<tables>
		<table name="mymodule_item" />
	</tables>
	<columns>
		<column name="status" var="status" notnull="notnull" />
	</columns>
	<conditions>
		<condition operation="equal" column="item_srl" var="item_srl" filter="number" notnull="notnull" />
	</conditions>
</query>

• filter="number" is a validation that lets only numbers through.
• For delete, use action="delete" with only conditions. Always put notnull on the key conditions so you never end up with a delete/update without conditions.

Running from PHP

$args = new \stdClass;
$args->status = 'open';
$args->page = (int)\Context::get('page') ?: 1;

$output = executeQueryArray('mymodule.getItems', $args);
if (!$output->toBool())
{
	return $output;  // DB 오류
}
$items = $output->data;              // 항상 배열
$paging = $output->page_navigation;  // navigation 선언 시

• executeQuery is meant for single results, while executeQueryArray always returns results as an array. Use the Array version for lists.
• If you need a join, put both tables in <tables> and connect them in <conditions>, or use <table type="left join"> with nested <conditions> declarations. For complex joins, the fastest way is to look at the query XML of core modules (board, document).

The most common pitfall

Columns not listed in <columns> are silently discarded. Even if you pass $args->new_column = 'value' from PHP, it is ignored without an error if that column is not declared in <columns> of the insert/update query XML. If you're sure you saved it but it isn't in the DB, this is almost always the reason.

When you add a column, change these three places together.

1. Add the column to schemas/table.xml
2. addColumn in the Install class's moduleUpdate (for existing installations)
3. Add it to <columns> in the related insert/update query XML