[sqlite] SQLite and OOo
Hi to all. I'm new to this list and I've read here very interesting discussions about SQLite as a standard for Free Office suites. I'm an OpenOffice.org user, and I hope SQLite will be choosen as an embeded database for OOo. For now, we are able to use SQLite databases with OOo thanks to the ODBC drivers for Christian Werner (www.ch-werner.de/sqliteodbc/). This driver works for windows and Linux, and has been much improved by the developper to work perfectly with OOo. I've written a howto in french (http://fr.openoffice.org/Documentation/How-to/Bdd/08SQLite_2.pdf) which explains how to use the SQLite ODBC driver with OOo. This howto has been translated into english here : documentation.openoffice.org/HOW_TO/data_source/SQLite.pdf I've also tried the JDBC driver (www.ch-werner.de/javasqlite/) but the actual version is too experimental and doesn't work with OOo. Perhaps you know there are discussions on OOo lists about the choice of a file based database which could be embeded in OOo in the future. You can read here an interesting article (dba.openoffice.org/miscellaneous/developer_projects.html#embed_sqlite). It seems that HSQLDB could be the first choice, but many users think SQLite would be a better choice because it is already a standard and because of its good performances. The main drawback of SQLite is that it doesn't support ALTER TABLE. It's a big limitation for simple users when building a database. If this fonctionnality (and a few others) could be add in a near future, SQLite would probably become the first choice for OOo. So I hope that SQLite developpers will be able to work together with OOo developpers to write an embeded OOo driver for SQLite (not ODBC), and then to store SQLite database in the future OOo base file format. Best regards. Yves
Re: [sqlite] Improved Interpreter in latest SQLite version?
On 19 Jul 2004, at 23:56, gohaku wrote: Does sqlite v3.0.2 include such an interpreter? No. I use dbish which is from the perl DBI::Shell package (on CPAN). If you install the right modules it has scrollback, EDITOR support, etc. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] Kexi 0.1 beta4 released
Hello, I am posting the announce here because Kexi uses SQLite as its main backend, for file-based databases. 16 July 2004 The Kexi Team announced the immediate availability of Kexi 0.1 beta 4, codenamed FireDuck, newest preview release of the integrated environment for managing data aimed at developers and experienced users. Kexi and its libraries are available under flexible Open Source license (LGPL). It can be obtained in Linux source code http://download.kde.org/download.php?url=unstable/apps/KDE3.x/office/kexi-0.1beta4.tar.bz2 and 32-bit MS Windows binary http://download.kde.org/download.php?url=unstable/apps/KDE3.x/office/kexi-2005beta4-win32.exe from download.kde.org. Note: FTP servers' mirroring may take even one day. Changes since beta 3 version * Much improved Form Designer (widget editing, additional tools) * Much improved Query Designer (both Visual and SQL mode, parser) * Fixes in Table Designer * Many fixes in Data Table View (universal data editing widget) * Overall application improvements (error/problem messages, loading/saving, actions) See also http://www.kexi-project.org/announce-0.1-beta4.html Support Kexi users are invited to report bugs and wishes. This can be done directly to the Kexi Bug Database http://bugs.kexi-project.org/. Please login as user guest, without a password. A list of unsupported features and known problems is also available. Kexi Sponsorship Kexi is developed by Kexi Team http://www.kexi-project.org/people.html - an international group of independent developers, with additional assinstance and support from the OpenOffice Polska http://www.openoffice.com.pl company. About Kexi Kexi is a integrated environment for managing data. It helps in creating database schema, inserting, querying and processing data. As Kexi is a real member of the KDE http://www.kde.org and KOffice http://www.koffice.org projects, it integrates fluently into both. It is designed to be fully usable also without running KDE Desktop on Unix, MS Windows and Mac OS X platforms. -- regards / pozdrawiam, Jaroslaw Staniek / OpenOffice Polska Kexi Project: http://www.kexi-project.org, http://koffice.kde.org/kexi QT-KDE Wrapper Project: http://iidea.pl/~js/qkw
[sqlite] PB in sqlite 3.0.2 when define SQLITE_OMIT_DATETIME_FUNCS
PB in sqlite 3.0.2 when define SQLITE_OMIT_DATETIME_FUNCS
I think sqlite3RegisterDateTimeFunctions should be in date.c
void sqlite3RegisterDateTimeFunctions(sqlite *db){
#ifndef SQLITE_OMIT_DATETIME_FUNCS
static struct {
char *zName;
int nArg;
void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
} aFuncs[] = {
{ julianday, -1, juliandayFunc },
{ date, -1, dateFunc},
{ time, -1, timeFunc},
{ datetime, -1, datetimeFunc},
{ strftime, -1, strftimeFunc},
};
int i;
for(i=0; isizeof(aFuncs)/sizeof(aFuncs[0]); i++){
sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0);
}
#endif
}
Re: [sqlite] Improved Interpreter in latest SQLite version?
On Mon, 19 Jul 2004, gohaku wrote: Hello everyone, I am currently using SQLite v2.8.11. I have not had much experience with SQLite other than executing simple Queries and Updates. I don't have any problems with SQLite v2.8.11 and as such, have not upgraded to v3.0.2 I would like to know if v3.0.2 has an improved interpreter. Below is an example of a sqlite interpreter session: sqlite create table test ( tehfield string ); sqlite drop table test; sqlite ^[[A Ctrl+Z ...Corrections: sqlitecreate table test ( thefield string ); Basically, I would like to scrollback through complete SQL statements I have typed/executed. Does sqlite v3.0.2 include such an interpreter? even 2.8.11 does - if you compile with readline support. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] Another way of corrupting the database.
Is there any way to determine the sqlite version ... ? Look at the first 47 bytes of the file -- or the first 48 bytes if you want a null-terminated string.
[sqlite] RE: Data Types
Hello again, Can anyone answer these simple questions for me? Why is the SHOW_DATATYPES pragma not mentioned on the SQL syntax page under the PRAGMA keyword section? Do I have to execute the pragma per transaction? If not, why I am intermittently not getting the data types? Thanks, Steve From: Drew, Stephen Sent: Monday, July 19, 2004 7:09 PMTo: '[EMAIL PROTECTED]'Subject: Data Types Hi there, I am using the PRAGMA command "SHOW_DATATYPES=on". However, not all of my queries return the datatypes.Has anyone else experienced this problem? Regards,Steve
[sqlite] updates, triggers and out of order
Hi sqlite-users :) I have found sqlite as the best solution to all my data storage problems - a great piece of software! Im currently working on a specific algorithm to fill in a table of numbers. Within the range of an index (idx), I have to count the numbers of rows where (in1==in2) and place this number in the result column for each subsequent row, if (in1 != in2) I have to fill in a '0' and restart counting, e.g. idx | in1 | in2 | result 1 | 1 | 1 | 2 2 | 1 | 1 | 2 3 | 1 | 2 | 0 4 | 1 | 2 | 0 5 | 2 | 2 | 4 6 | 2 | 2 | 4 7 | 2 | 2 | 4 8 | 2 | 2 | 4 The algorithm works as follows: CREATE TABLE work (idx integer, in1 integer, in2 integer, result integer); // insert data, order by idx CREATE TRIGGER update_work UPDATE OF result ON work WHEN new.result == 0 BEGIN UPDATE work SET result = (SELECT count(*) FROM work WHERE result==-1) WHERE result == -1; END; UPDATE work SET result = CASE WHEN in1 == 0 THEN 0 WHEN in2 == 0 THEN 0 WHEN in1 != in2 THEN 0 ELSE -1 END; There is one difficulty: I have to rely on the order of updates, say idx==1 has to be updated first, then idx==2 and so on. In principle the algorithm works - I wouldn't write this mail if there wasn't a BUT: In a dataset of max(idx) == 220 I found by inserting a line with the current idx into a triggerlog-table, that updating started at the 125th row, went to last last (220), got on with the first (1) und stopped at the 124th row - ough! [The actual work table had some more columns, the index column is 3 columns wide and and there are 4 but 1 result columns - but the principle is the same ;)]. My question(s): is there a way to urge sqlite to start at idx==1 and increment as expected? If not is there a better way to achieve the goal of my task? As an afterthought: Testing the code above on a newly created table (still 220 rows but less columns) gives the expected results - maybe an issue with sqlite's memory management?! Thanks a lot for any help! With kind regards Daniel -- Dipl.-Math. (FH) Daniel Franke Institut fuer Medizinische Biometrie und Statistik Medizinische Universität zu Luebeck Ratzeburger Allee 160, Haus 4 23538 Luebeck Telefon: 0451-500-2786 Telefax: 0451-500-2999 [EMAIL PROTECTED]
[sqlite] missing documentation: END TRANSACTION
The documentation lists END [TRANSACTION] as a legal command, but does not describe it. I assume that END TRANSACTION is a synonym for ROLLBACK but its action should probably be described explicitly in the documentation (especially if this assumption is incorrect). Cheers, Derrell
Re: [sqlite] missing documentation: END TRANSACTION
[EMAIL PROTECTED] wrote: The documentation lists END [TRANSACTION] as a legal command, but does not describe it. I assume that END TRANSACTION is a synonym for ROLLBACK but its action should probably be described explicitly in the documentation (especially if this assumption is incorrect). END [TRANSACTION] is the same as COMMIT. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] How to handle slashes (\) in text columns
I'm using the TCL binding of SQLITE and was trying to figure out how to
enter various characters into a text column. To enter a slash, all I
normally would do is to append another slash to it. However, this only
works fine if the slash is not the last character in the field. Consider
the following:
% db eval insert into t1 values('\')
%-- no character
% db eval insert into t1 values('\\')
% \\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST')
% \TEST -- returns as expected
% db eval insert into t1 values('TEST\\')
% TEST\\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST\\')
% \\TEST\\ -- now not even the first slash was escaped!!
In short, what I'm asking is how can I insert text with one slash at the
end?
-Shamil D.
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
Re: [sqlite] Another way of corrupting the database.
On Tue, 20 Jul 2004 07:21:43 -0500, you wrote: Is there any way to determine the sqlite version ... ? Look at the first 47 bytes of the file -- or the first 48 bytes if you want a null-terminated string. That works.. Thank you.. Randall Fox
Re: [sqlite] How to handle slashes (\) in text columns
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, July 20, 2004 10:02 PM
Subject: [sqlite] How to handle slashes (\) in text columns
I'm using the TCL binding of SQLITE and was trying to figure out how to
enter various characters into a text column. To enter a slash, all I
normally would do is to append another slash to it. However, this only
works fine if the slash is not the last character in the field. Consider
the following:
% db eval insert into t1 values('\')
%-- no character
% db eval insert into t1 values('\\')
% \\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST')
% \TEST -- returns as expected
% db eval insert into t1 values('TEST\\')
% TEST\\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST\\')
% \\TEST\\ -- now not even the first slash was escaped!!
In short, what I'm asking is how can I insert text with one slash at the
end?
I'm rather rusty on Tcl, but I think that this is what tcl'ers refer to as
quoting hell.
The short answer is that you need 4 slashes, e.g.
db eval insert into t1 values('')
The reason goes something like this - the first pass of the quoted stuff
expands variables and does the slash escaping which leaves us with 2
slashes. The eval then causes the slash escaping to happen again.
There may be better ways to handle this ?. I did a quick search but didn't
come up with any conclusive. You could ask this question on comp.lang.tcl
and see if they have a cleaner suggestion if no-one else here replies.
Lawrence
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16/07/04
Re: [sqlite] How to handle slashes (\) in text columns
I'm afraid I still get //
% db eval insert into t1 values('')
% //
Quoting Hell it still is...
-Shamil D.
Lawrence Chitty
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
lworld.comcc:
Subject: Re: [sqlite] How to handle
slashes (\) in text columns
07/20/2004 05:30 PM
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, July 20, 2004 10:02 PM
Subject: [sqlite] How to handle slashes (\) in text columns
I'm using the TCL binding of SQLITE and was trying to figure out how to
enter various characters into a text column. To enter a slash, all I
normally would do is to append another slash to it. However, this only
works fine if the slash is not the last character in the field. Consider
the following:
% db eval insert into t1 values('\')
%-- no character
% db eval insert into t1 values('\\')
% \\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST')
% \TEST -- returns as expected
% db eval insert into t1 values('TEST\\')
% TEST\\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST\\')
% \\TEST\\ -- now not even the first slash was escaped!!
In short, what I'm asking is how can I insert text with one slash at the
end?
I'm rather rusty on Tcl, but I think that this is what tcl'ers refer to as
quoting hell.
The short answer is that you need 4 slashes, e.g.
db eval insert into t1 values('')
The reason goes something like this - the first pass of the quoted stuff
expands variables and does the slash escaping which leaves us with 2
slashes. The eval then causes the slash escaping to happen again.
There may be better ways to handle this ?. I did a quick search but didn't
come up with any conclusive. You could ask this question on comp.lang.tcl
and see if they have a cleaner suggestion if no-one else here replies.
Lawrence
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16/07/04
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
[sqlite] ANN: SQLite Database Browser 1.1
SQLite Database Browser is a freeware, public domain, open source visual tool used to create, design and edit database files compatible with SQLite 2.x. It is hosted at sourceforge, and the home page for the project lives at http://sqlitebrowser.sourceforge.net/ Source is available of course, as well as pre-compiled binaries for Linux, Windows and Mac OSX. The following features were added in the 1.1 update: - SQL query window: enter SQL commands and see the output and error codes directly in a browser view. - SQL log: lists all SQL commands issued by the browser application and/or by the user. Useful as a tool to analyze and learn SQL. - Abitility to open database files specified with a command line parameter (sqlitebrowser mydb) and/or drag and drop. - Added experimental modify table wizard, since SQLite does not support the ALTER TABLE SQL command. Use it with care. Works correctly for all tables originally created in SQLiteBrowser. - The browser uses a transaction in the background to undo changes made to a database before they are commited to disk. All changes are now temporary until the user decides to SAVE the file. - Added CSV import/export capabilities: a wizard makes it possible to configure table importing from a CSV file (delimiters, etc.) Supports most CSV format variations, including files generated by MS Excel and Access. - Added SQL dump import/export capabities. Useful to store an SQL format backup of your database, or to interchange data with MySQL or Oracle, for example. - Added text import/export capabilities directly to from a text file to db records. - Modified record browsing interface. The spreadsheet interface now redraws faster (and clips longer records accordingly) Double click a cell to edit the record on a separate window. This allows better support for longer records including line breaks, for example. and many other small enhancements and bug fixes. Enjoy. Regards, Mauricio Piacentini Tabuleiro
