Firefox/Projects/IndexedDB/SQL Schema

From MozillaWiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

The currently proposed schema can be viewed by going here, clicking "Save/Load", pasting the xml file below into the text area, and clicking load. The only tables I'm presently certain about are "database", "object_store", "object_store_data", and "ai_object_store_data". Indexes are currently in flux in the spec, and they may change (there is talk of compound indexes). 

<?xml version="1.0" encoding="utf-8" ?>
<sql>
<datatypes db="sqlite">

	<group label="Numeric" color="rgb(238,238,170)">

		<type label="Integer" length="0" sql="INTEGER" re="INT" quote=""/>

		<type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote=""/>

		<type label="Single precision" length="0" sql="FLOAT" quote=""/>

		<type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote=""/>

	</group>



	<group label="Character" color="rgb(255,200,200)">

		<type label="Char" length="1" sql="CHAR" quote="'"/>

		<type label="Varchar" length="1" sql="VARCHAR" quote="'"/>

		<type label="Text" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>

		<type label="Binary" length="1" sql="BINARY" quote="'"/>

		<type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>

		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>

	</group>



	<group label="Date &amp; Time" color="rgb(200,255,200)">

		<type label="Date" length="0" sql="DATE" quote="'"/>

		<type label="Time" length="0" sql="TIME" quote="'"/>

		<type label="Datetime" length="0" sql="DATETIME" quote="'"/>

		<type label="Year" length="0" sql="YEAR" quote=""/>

		<type label="Timestamp" length="0" sql="TIMESTAMP" quote="'"/>

	</group>

	

	<group label="Miscellaneous" color="rgb(200,200,255)">

		<type label="ENUM" length="1" sql="ENUM" quote=""/>

		<type label="SET" length="1" sql="SET" quote=""/>

	</group>

</datatypes><table x="63" y="52" name="database">
<row name="name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="description" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="version" null="1" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
<default>NULL</default></row>
<key type="UNIQUE" name="unique_name">
<part>name</part>
</key>
<comment>List of databases stored.</comment>
</table>
<table x="248" y="125" name="object_store">
<row name="id" null="1" autoincrement="0">
<datatype>INTEGER</datatype>
<default>NULL</default></row>
<row name="name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="key_path" null="1" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
<default>NULL</default></row>
<row name="autoincrement" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<default>0</default></row>
<row name="readers" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<default>0</default></row>
<row name="isWriting" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<default>0</default></row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<comment>Stores object stores.</comment>
</table>
<table x="488" y="42" name="index">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="object_store_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="object_store" row="id" />
</row>
<row name="name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="key_path" null="1" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
<default>NULL</default></row>
<row name="unqiue" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<default>0</default></row>
<row name="object_store_autoincrement" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<comment>Mirrors object_store.autoincrement</comment>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="UNIQUE" name="unique_index_name">
<part>name</part>
</key>
<comment>Stores the indexes for object stores</comment>
</table>
<table x="515" y="316" name="object_data">
<row name="id" null="0" autoincrement="1">
<datatype>INTEGER</datatype>
</row>
<row name="object_store_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="object_store" row="id" />
</row>
<row name="data" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="key_value" null="1" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
<default>NULL</default><comment>Used for in-line keys.</comment>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="INDEX" name="key_index">
<part>key_value</part>
<part>object_store_id</part>
</key>
<comment>Stores objects for an object store.</comment>
</table>
<table x="748" y="201" name="index_data">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="index_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="index" row="id" />
</row>
<row name="object_data_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="object_data" row="id" />
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
<comment>This duplicates data in object_data.data.</comment>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="INDEX" name="value_index">
<part>index_id</part>
<part>value</part>
</key>
<comment>Stores data for indexes</comment>
</table>
<table x="749" y="360" name="unique_index_data">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="index_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="index" row="id" />
</row>
<row name="object_data_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="object_data" row="id" />
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
<comment>This duplicates data in object_data.data.</comment>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="UNIQUE" name="unique_constraint">
<part>index_id</part>
<part>value</part>
</key>
<comment>Used for unique indexes to store data.</comment>
</table>
<table x="21" y="339" name="ai_object_data">
<row name="id" null="1" autoincrement="1">
<datatype>INTEGER</datatype>
<default>NULL</default></row>
<row name="object_store_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="object_store" row="id" />
</row>
<row name="data" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="INDEX" name="key_index">
<part>id</part>
<part>object_store_id</part>
</key>
<comment>Stores data for autoincrement keys in object stores</comment>
</table>
<table x="250" y="345" name="ai_index_data">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="index_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="index" row="id" />
</row>
<row name="ai_object_data_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="ai_object_data" row="id" />
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="INDEX" name="key_index">
<part>index_id</part>
<part>value</part>
</key>
<comment>Stores the index data for autoincrement object stores</comment>
</table>
<table x="249" y="477" name="unique_ai_index_data">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="index_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="index" row="id" />
</row>
<row name="ai_object_data_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="ai_object_data" row="id" />
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<key type="PRIMARY" name="">
<part>id</part>
</key>
<key type="UNIQUE" name="unique_constraint">
<part>index_id</part>
<part>value</part>
</key>
<comment>Stores the data for a unique index for an autoincrement object store</comment>
</table>
</sql>
Retrieved from "https://wiki.mozilla.org/index.php?title=Firefox/Projects/IndexedDB/SQL_Schema&oldid=219594"