inspectortable.js

SQLiteDBMS是SQLite数据库的管理服务器软件

/* create */
var _inspectorTableFieldGrid=null;

function inspectorTableNew() {
	lGo('newTableWin');
	_inspectorData=null;

	var form=document.forms['form_tablenew'];
	if (form) {
		try{
			form['name'].value='new_table';
			form['temp'].checked=form['temp'].defaultChecked=false;
			form['name'].focus();
			form['name'].select();
		}catch(e){}
	}

	if (!_inspectorTableFieldGrid) {
		var parent=document.getElementById('newTableWin');
		var div=oFindChild(parent, 'DIV', 'newViewResult');
		if (!div) return;

		while (div.childNodes.length > 0)
			div.removeChild(div.childNodes[0]);

		_inspectorTableFieldGrid=new boxGrid();
		_inspectorTableFieldGrid.width='100%';
		_inspectorTableFieldGrid.header=['Name', 'Type', 'NotNull', 'Default', 'PK', 'Uniq', 'Auto'];
		_inspectorTableFieldGrid.align=['center','center','center','center','center','center','center','center'];
		_inspectorTableFieldGrid.type=['html','html','html','html','html','html','html','html'];
		div.appendChild(_inspectorTableFieldGrid.draw());
	}
	_inspectorTableFieldGrid.clearData();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
	inspectorTableNewAddField();
}

function inspectorTableNewAddField() {
	_inspectorTableFieldGrid.appendData(
		'<input type=text name="fieldname" style="width:70px;height:19px;font-size:8pt">',
		'<select name="type" style="width:60px;font-size:8pt"><option value=""></option><option value="integer">Integer</option><option value="real">Real</option><option value="text">Text</option><option value="blob">Blob</option></select>',
		'<input type=checkbox name="notnull" onclick="this.defaultChecked=this.checked;">',
		'<input type=text name="default" style="width:60px;height:19px;font-size:8pt">',
		'<input type=checkbox name="pk" onclick="this.defaultChecked=this.checked;">',
		'<input type=checkbox name="unique" onclick="this.defaultChecked=this.checked;">',
		'<input type=checkbox name="auto" onclick="this.defaultChecked=this.checked;">'
	);
}

function inspectorTableNewDropField() {
	var parent=document.getElementById('newTableWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!div) return;

	var input=oFindChildByTagNames(div, 'INPUT');
	var field=[];
	for (var i=input.length; i--;) {
		if (input[i].type == 'text' && input[i].name == 'fieldname' && !input[i].value)
			field.push(input[i]);
	}
	if (field && field.length > 0) {
		for (i=field.length; i--;) {
			var row=oFindParentByTagName(field[i], 'TR');
			row.offsetParent.deleteRow(row.rowIndex);
		}
	}
}

function inspectorTableNewSubmit(showSql) {
	var form=document.forms['form_tablenew'];
	if (!form) return false;
	if (!trim(form['name'].value)) {
		alert('Insert new table\'s name.');
		form['name'].focus();
		return false;
	}
	if (_inspectorTableFieldGrid.table.rows.length <= 1) {
		alert('No fields.');
		return false;
	}
	if (!showSql && !confirm('Create new table?')) return false;

	var fieldDDL=[];
	for (var i=1; i < _inspectorTableFieldGrid.table.rows.length; i++) {
		var input=oFindChildByTagNames(_inspectorTableFieldGrid.table.rows[i], 'INPUT');
		var select=oFindChildByTagName(_inspectorTableFieldGrid.table.rows[i], 'SELECT');
		var fields={};

		for (var p=input.length; p--;)
			fields[input[p].name]=input[p];

		if (!fields['fieldname'] || !fields['notnull'] || !fields['default'] || !fields['pk'] || !fields['unique'] || !fields['auto'] || !select) {
			alert('Error.');
			return false;
		}
		if (!trim(fields['fieldname'].value)) {
			var row=oFindParentByTagName(fields['fieldname'], 'TR');
			row.offsetParent.deleteRow(row.rowIndex);
			i--;
			continue;
		}

		var ddl=fields['fieldname'].value;
		if (select.value)
			ddl+=' '+select.value;
		if (fields['notnull'].checked == true) {
			ddl+=' NOT NULL';
			if (!fields['default'].value)
				ddl+=' DEFAULT \'\'';
		}
		if (fields['default'].value)
			ddl+=' DEFAULT \''+fields['default'].value+'\'';
		if (fields['pk'].checked == true)
			ddl+=' PRIMARY KEY';
		if (fields['unique'].checked == true)
			ddl+=' UNIQUE';
		if (fields['auto'].checked == true)
			ddl+=' AUTOINCREMENT';
		/*
		if (fields['check'].value)
			ddl+=' CHECK ('+fields['check'].value+')';
		*/

		fieldDDL.push(ddl);
	}

	if (fieldDDL.length <= 0) {
		alert('No fields.');
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		inspectorTableNewAddField();
		return;
	}

	var newName=form['name'].value;
	var temp=(form['temp'].checked == true)?'TEMP ':'';

	var sql='CREATE '+temp+'TABLE "'+trim(newName)+'" (\n'+fieldDDL.join(',\n')+'\n)';

	if (showSql) return dbShowSQL(sql);

	dbExecCommand(sql, inspectorTableNewResponse);

	return false;
}

function inspectorTableNewResponse(dbRes) {
	if (dbRes) {
		inspectorDdlClear();
		dbExecCommand('show table', inspectorTableListDraw);
		lHide();
	}
}

/* create as */
function inspectorTableNewAs() {
	lGo('newTableAsWin');
	_inspectorData=null;

	var parent=document.getElementById('newTableAsWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!div) return;

	while (div.childNodes.length > 0)
		div.removeChild(div.childNodes[0]);

	var form=document.forms['form_tablenewas'];
	if (form) {
		try{
			form['name'].value='new_table';
			form['temp'].checked=form['temp'].defaultChecked=false;
			form['query'].value='SELECT ..';
			form['name'].focus();
			form['name'].select();
		}catch(e){}
	}
}

function inspectorTableNewAsSubmit(showSql) {
	var form=document.forms['form_tablenewas'];
	if (!form) return false;
	if (!trim(form['name'].value)) {
		alert('Insert new table\'s name.');
		form['name'].focus();
		return false;
	}
	if (!showSql && !confirm('Create new table?')) return false;

	var newName=form['name'].value;
	var temp=(form['temp'].checked == true)?'TEMP ':'';

	var sql='CREATE '+temp+'TABLE "'+trim(newName)+'" AS '+(form['query'].value.replace(/;\s*$/, ''));

	if (showSql) return dbShowSQL(sql);

	dbExecCommand(sql, inspectorTableNewResponse);

	return false;
}

function inspectorTableNewAsFetch() {
	var form=document.forms['form_tablenewas'];
	if (!form) return false;

	dbExecCommand('SELECT * FROM ('+(form['query'].value.replace(/;\s*$/, ''))+') LIMIT 50', inspectorTableNewAsFetchResponse);
}

function inspectorTableNewAsFetchResponse(dbRes) {
	var parent=document.getElementById('newTableAsWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!parent || !div) return;

	while (div.childNodes.length > 0)
		div.removeChild(div.childNodes[0]);

	var grid=new boxGrid();
	if (dbRes.columns && dbRes.columns[0] && dbRes.columns[0].columnname)
		grid.header=dbRes.columns[0].columnname;

	div.appendChild(grid.draw());

	if (dbRes.row) {
		for (var i=0; i < dbRes.row.length; i++)
			grid.appendData(dbRes.row[i].data);
	}
}

/* add column */
function inspectorTableAddColumn(data) {
	if (_libVersion < 2.0) {
		alert('"ALTER TABLE RENAME TO" >= 3.2.0');
		return;
	}
	if (data.type != 'table') {
		alert('ALTER TABLE command allows the user to rename or add a new column to an existing table.');
		return;
	}
	lGo('addColumnWin');
	_inspectorData=data;

	var form=document.forms['form_addcolumn'];
	if (form) {
		try{
			form['name'].value='newcolumn';
			form['type'].value='integer';
			form['default'].value='';
			form['notnull'].checked=false;
			form['name'].focus();
			form['name'].select();
		}catch(e){}
	}
}

function inspectorTableAddColumnSubmit(showSql) {
	var form=document.forms['form_addcolumn'];
	if (!form) return false;
	if (!trim(form['name'].value)) {
		alert('Insert new column\'s name.');
		form['name'].focus();
		return false;
	}
	if (!showSql && !confirm('Add?')) return false;

	var newName=form['name'].value;
	var type=form['type'].value;
	var ext='';
	if (form['notnull'].checked == true) {
		ext+=' NOT NULL ';
		if (!form['default'].value)
			ext+=' DEFAULT \'\' ';
	}
	if (form['default'].value)
		ext+=' DEFAULT \''+form['default'].value+'\' ';

	var sql='ALTER TABLE "'+trim(_inspectorData.name)+'" ADD COLUMN "'+newName+'" '+type+ext;

	if (showSql) return dbShowSQL(sql);

	dbExecCommand(sql, inspectorTableAddColumnResponse);

	return false;
}

function inspectorTableAddColumnResponse(dbRes) {
	if (dbRes) {
		inspectorDdlClear();
		dbExecCommand('show table', inspectorTableListDraw);
		lHide();
	}
}

/* insert new data */
function inspectorTableNewData(data) {
	lGo('newDataWin');
	_inspectorData=data;

	var form=document.forms['form_tablenewdata'];
	if (form) {
		try{
			form['name'].value=data.name;
		}catch(e){}
	}

	var parent=document.getElementById('newDataWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!parent || !div) return;

	while (div.childNodes.length > 0)
		div.removeChild(div.childNodes[0]);

	inspectorTableNewDataGetField(data.name);
}

function inspectorTableNewDataGetField(table) {
	dbExecCommand('DESC '+table, inspectorTableNewDataGetFieldResponse);
}

function inspectorTableNewDataGetFieldResponse(dbRes) {
	var parent=document.getElementById('newDataWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!parent || !div) return;

	while (div.childNodes.length > 0)
		div.removeChild(div.childNodes[0]);

	var grid=new boxGrid();
	var field=['Del'];
	grid.type.push('html');
	if (dbRes.row) {
		for (var i=0; i < dbRes.row.length; i++) {
			field.push(dbRes.row[i].data[0]);
			grid.type.push('html');
		}
	}else{
		alert('No fields.');
		return;
	}

	grid.header=field;

	div.appendChild(grid.draw());
	inspectorTableNewDataAdd();
}

function inspectorTableNewDataAdd() {
	var parent=document.getElementById('newDataWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!parent || !div) return;
	var table=oFindChildByTagName(div, 'TABLE');
	if (!table || !table.grid) return;
	var grid=table.grid;
	var fields=[];
	for (var i=grid.header.length; i--;) {
		if (fields.length == 0)
			fields.push('<a href="javascript:;" onclick="inspectorTableNewDataDrop(this)">Del</a>');
		else
			fields.push('null? <input type=checkbox name="null" checked onclick="this.defaultChecked=this.checked;this.nextSibling.nextSibling.readOnly=this.checked;this.nextSibling.nextSibling.className=(this.checked?\'disabled\':\'\');"><br><textarea readonly class="disabled" style="width:100px"></textarea>');
	}
	grid.appendData(fields);
}

function inspectorTableNewDataDrop(link) {
	if (!link) return;
	var row=oFindParentByTagName(link, 'TR');
	var table=oFindParentByTagName(link, 'TABLE');
	if (!row || !table) return;
	if (!confirm('Drop this row?')) return;
	table.deleteRow(row.rowIndex);
	if (table.rows.length <= 1)
		inspectorTableNewDataAdd();
}

function inspectorTableNewDataSubmit(showSql) {
	var form=document.forms['form_tablenewdata'];
	if (!form) return false;

	var parent=document.getElementById('newDataWin');
	var div=oFindChild(parent, 'DIV', 'newViewResult');
	if (!parent || !div) return false;
	var table=oFindChildByTagName(div, 'TABLE');
	if (!table || !table.grid) return false;
	var grid=table.grid;

	var tableName=trim(form['name'].value);

	if (grid.table.rows.length <= 1) {
		alert('No data.');
		return false;
	}
	if (!showSql && !confirm('Execute insert statement?')) return false;

	var insert=[];
	for (var i=1; i < grid.table.rows.length; i++) {
		var tmpField=[];
		var tmpData=[];
		for (var p=1; p < grid.table.rows[i].cells.length; p++) {
			var input=oFindChildByTagName(grid.table.rows[i].cells[p], 'INPUT');
			var textarea=oFindChildByTagName(grid.table.rows[i].cells[p], 'TEXTAREA');
			if (input && textarea && input.checked == false) {
				tmpField.push(grid.header[p]);
				tmpData.push(textarea.value.replace(/\'/g, '\'\''));
			}
		}
		if (tmpField.length <= 0)
			insert.push('INSERT INTO '+tableName+' ('+grid.header[1]+') VALUES(NULL);');
		else
			insert.push('INSERT INTO '+tableName+' ('+tmpField.join(',')+') VALUES(\''+tmpData.join('\', \'')+'\');');
	}

	sql=insert.join('\n');

	if (showSql) return dbShowSQL(sql);

	dbExecCommand('BEGIN;\n'+sql+'COMMIT;', inspectorTableNewDataResponse);

	return false;
}

function inspectorTableNewDataResponse(dbRes) {
	if (dbRes)
		lHide();
}