|
|
Home » Developing U++ » U++ Developers corner » Database portability: Sqlite3
Database portability: Sqlite3 [message #7042] |
Fri, 08 December 2006 23:02  |
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 #7363 is a reply to message #7357] |
Sat, 23 December 2006 13:14  |
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
|
|
|
Goto Forum:
Current Time: Sun Apr 27 18:45:01 CEST 2025
Total time taken to generate the page: 0.00550 seconds
|
|
|