Firefox/Projects/IndexedDB/SQL Schema: Difference between revisions

From MozillaWiki
Jump to navigation Jump to search
(Tweak object store bits of the schema)
(Account for autoincrement object stores)
Line 1: Line 1:
The currently proposed schema can be viewed by [https://services.forerunnerdesigns.com/dbs/ 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", and "object_store_data".  Indexes are currently in flux in the spec, and they may change (there is talk of compound indexes).
The currently proposed schema can be viewed by [https://services.forerunnerdesigns.com/dbs/ 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).


<pre>
<pre>
Line 63: Line 63:


</datatypes><table x="64" y="52" name="database">
</datatypes><table x="64" y="52" name="database">
<row name="id" null="0" autoincrement="1">
<row name="id" null="1" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
</row>
</row>
Line 88: Line 88:
</table>
</table>
<table x="248" y="125" name="object_store">
<table x="248" y="125" name="object_store">
<row name="id" null="0" autoincrement="1">
<row name="id" null="1" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
</row>
</row>
Line 101: Line 101:
<datatype>MEDIUMTEXT</datatype>
<datatype>MEDIUMTEXT</datatype>
<default>NULL</default></row>
<default>NULL</default></row>
<row name="autoincrement" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<default>0</default></row>
<key type="PRIMARY" name="">
<key type="PRIMARY" name="">
<part>id</part>
<part>id</part>
<part>database_id</part>
</key>
</key>
<comment>Stores object stores.</comment>
<comment>Stores object stores.</comment>
</table>
</table>
<table x="436" y="52" name="index">
<table x="488" y="42" name="index">
<row name="id" null="1" autoincrement="1">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
<default>NULL</default></row>
</row>
<row name="database_id" null="0" autoincrement="0">
<row name="database_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
Line 128: Line 130:
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
<default>0</default></row>
<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="">
<key type="PRIMARY" name="">
<part>id</part>
<part>id</part>
Line 137: Line 143:
<comment>Stores the indexes for object stores</comment>
<comment>Stores the indexes for object stores</comment>
</table>
</table>
<table x="282" y="280" name="object_data">
<table x="515" y="316" name="object_data">
<row name="id" null="0" autoincrement="1">
<row name="id" null="0" autoincrement="1">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
Line 154: Line 160:
<key type="PRIMARY" name="">
<key type="PRIMARY" name="">
<part>id</part>
<part>id</part>
<part>object_store_id</part>
</key>
</key>
<key type="INDEX" name="key_index">
<key type="INDEX" name="key_index">
Line 162: Line 167:
<comment>Stores objects for an object store.</comment>
<comment>Stores objects for an object store.</comment>
</table>
</table>
<table x="580" y="210" name="index_data">
<table x="748" y="201" name="index_data">
<row name="id" null="1" autoincrement="1">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
<default>NULL</default></row>
</row>
<row name="index_id" null="0" autoincrement="0">
<row name="index_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
Line 187: Line 192:
<comment>Stores data for indexes</comment>
<comment>Stores data for indexes</comment>
</table>
</table>
<table x="589" y="398" name="unique_index_data">
<table x="749" y="360" name="unique_index_data">
<row name="id" null="1" autoincrement="1">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
<default>NULL</default></row>
</row>
<row name="index_id" null="0" autoincrement="0">
<row name="index_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<datatype>INTEGER</datatype>
Line 211: Line 216:
</key>
</key>
<comment>Used for unique indexes to store data.</comment>
<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>
</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>
</table>
</sql>
</sql>
</pre>
</pre>

Revision as of 21:39, 5 April 2010

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="64" y="52" name="database">
<row name="id" null="1" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="origin" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<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="PRIMARY" name="">
<part>id</part>
</key>
<key type="UNIQUE" name="origin_name">
<part>origin</part>
<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>
</row>
<row name="database_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="database" 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="autoincrement" 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="database_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="database" row="id" />
</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>database_id</part>
<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>
</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.allizom.org/index.php?title=Firefox/Projects/IndexedDB/SQL_Schema&oldid=213464"