Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Developing U++ » U++ Developers corner » Database portability: Sqlite3
Database portability: Sqlite3 [message #7042] Fri, 08 December 2006 23:02 Go to next message
fabio is currently offline  fabio
Messages: 9
Registered: November 2006
Promising Member
I like database portability, yet complete portability is never possible..........

In uppsrc/plugin/sqlite3/Sqlite3upp.cpp ........
Vector<String> Sqlite3Session::EnumDatabases() {
	// In theory, sqlite3 can "ATTACH" multiple databases (up to 10).
	// However, I don't know how to list them.
	Vector<String> out;
	out.Add(current_dbname);
	return out;
}

Vector<String> Sqlite3Session::EnumTables(String database) {
	// Ignores database
	Vector<String> out;
	Sql sql(*this);
	sql*Select(SqlId("tbl_name")).From(SqlId("sqlite_master")).Where(SqlId("type")=="table");
	while (sql.Fetch())
		out.Add(sql[0]);
	return out;
}



The solution (from file uppsrc/plugin/sqlite3/lib/shell.c) is:

for get list of databases:
PRAGMA database_list;

for get the list of all tables and views:
SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

for get list of column:
PRAGMA table_info(table);


and for date emulation problem .......

the functions:
const void *sqlite3_column_decltype16(sqlite3_stmt*,int); //UTF-16
or
const char *sqlite3_column_decltype(sqlite3_stmt *, int i); // UTF-8

return a c-string with type of column or null pointer if computed column.
For declared 'date'-column return the pseudo-type 'date' and not the real type 'text'.

Real type SQLITE_TEXT (3) is returned from function:
int sqlite3_column_type(sqlite3_stmt*, int iCol);
(1,2,3,4,5) or (SQLITE_INTEGER,SQLITE_FLOAT,SQLITE_TEXT,SQLITE_BLOB,SQLITE_ NULL)

one solution is test the two value SQLITE_TEXT && 'date' for detect colum of pseudo-type date


consequently ....

My first little (useless?) contribution is:
Sqlite3Session::EnumDatabases(), modified for database list
Sqlite3Session::EnumTables(), modified for database name support
Sqlite3Session::EnumViews(), created
Sqlite3Session::EnumColumns(), created

Vector<String> Sqlite3Session::EnumDatabases() {
        Vector<String> out;
	Sql sql(*this);	
	sql.Execute("PRAGMA database_list;");
	while (sql.Fetch())
                out.Add(sql[1]);  // sql[1] is database name, sql[2] is filename
	return out;
}

Vector<String> Sqlite3Session::EnumTables(String database) {
	Vector<String> out;
        String dbn=database;
        if(dbn.IsEmpty()) dbn=current_dbname; // for backward compatibility
	Sql sql(*this);
        sql.Execute("SELECT name FROM "+dbn+".sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY 1;");
	while (sql.Fetch())
		out.Add(sql[0]);
	return out;
}

Vector<String> Sqlite3Session::EnumViews(String database) {
	Vector<String> out;
        String dbn=database;
        if(dbn.IsEmpty()) dbn=current_dbname;
	Sql sql(*this);
        sql.Execute("SELECT name FROM "+dbn+".sqlite_master WHERE type='view' AND name NOT LIKE 'sqlite_%' ORDER BY 1;");
	while (sql.Fetch())
		out.Add(sql[0]);
	return out;
}


and a few changes of
void Sqlite3Connection::BindParam(int i, const Value& r)
bool Sqlite3Connection::Execute()
void Sqlite3Connection::GetColumn(int i, Ref f)

for date emulation.....

the result work with EditDate, SqlCtrl, SqlArray.

Remain the problem of aggregate function like max and min, but Sqlite3 permit creation of user function ..... and so ......

In the attached file (zip):
new uppsrc/plugin/sqlite3/sqlite3.h
new uppsrc/plugin/sqlite3/sqlite3upp.cpp
Sqlite3upp_changes.txt



Mirek can you modify the source of distribution from the attached file ? Thanks.

Fabio

Re: Database portability: Sqlite3 [message #7075 is a reply to message #7042] Mon, 11 December 2006 14:37 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13976
Registered: November 2005
Ultimate Member
I am really sorry, but it seems your attachment was lost during forum migration.

Can you resend it please?

Mirek
Re: Database portability: Sqlite3 [message #7081 is a reply to message #7075] Tue, 12 December 2006 14:39 Go to previous messageGo to next message
fabio is currently offline  fabio
Messages: 9
Registered: November 2006
Promising Member
ok

Fabio
Re: Database portability: Sqlite3 [message #7121 is a reply to message #7081] Thu, 14 December 2006 20:57 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13976
Registered: November 2005
Ultimate Member
Well, there seems to be problem with this date emulation - no expressions are supported, only Date columns selected.

My suggestion was to encode dates as some very high doubles.

E.g.

Date d;
//...
double x = (d - Date(1, 1, 1)) * 1.0e300;


Loosing a double range between 1.0e300 - 1.0e307 does not seem a big problem to me. Moreover, at least some expressions work as expected.

Mirek
Re: Database portability: Sqlite3 [message #7357 is a reply to message #7121] Sat, 23 December 2006 12:38 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13976
Registered: November 2005
Ultimate Member
luzr wrote on Thu, 14 December 2006 14:57

Well, there seems to be problem with this date emulation - no expressions are supported, only Date columns selected.

My suggestion was to encode dates as some very high doubles.



After further investigation, I came to conclusion that your solution based on column type is better and the best we can acutally get with Sqlite....

Applied all of yours patches. Welcome to the U++ contributors Smile

Mirek
Re: Database portability: Sqlite3 [message #7363 is a reply to message #7357] Sat, 23 December 2006 13:14 Go to previous message
fabio is currently offline  fabio
Messages: 9
Registered: November 2006
Promising Member
Thanks Mirek.

Sqlite user function can't get declared type, and is not actually possible to solve problem of expressions in columns.

I try to contact sqlite team for implement 'date' column type.
No response from team ....

For date stored as text 'yyyy-mm-dd', is very simple to implement get from table (and put to table) with littles changes in sqlite text function. The only difference is the returned type: SQLITE_DATE and not SQLITE_TEXT.

User function can execute date-conversion and date-expression, but the core of sqlite must support the 'date' type.

I wait..... and .... retry.

Fabio
Previous Topic: Critical feature - namespace
Next Topic: Build UPP modules into DLL with import library
Goto Forum:
  


Current Time: Sat May 11 17:25:16 CEST 2024

Total time taken to generate the page: 0.02753 seconds