SQLite for PSP

[kolaaaa]

Hello all,

I port "SQLite" to the PSP by writing the OS dependent part.
This C library allow your homebrew to create database and query it with SQL langage.

If anyone interrested, ask me.
I also can upload it somewhere.

kolaaaa

[Gh0sT]

Nice man, I'm interested.

Googled the docs and would love to try this out.

Thanks in advance,
Gh0sT

[kolaaaa]

You can download it here : SQLite 3.3.17 for PSP rev. 33

README.PSP is include for building instructions.

Thanks for reporting problems (if any)

[jimparis]

This could be useful in the pspdev repository. I could add it if you'd like, or you can talk to someone like Oobles to get access.

[kolaaaa]

Hi,

I will commit it as soon as I have write access to the pspdev repository.

For now, I made a patch of the same release, for those how prefers, SQLite 3.3.17 for PSP rev. 33 patch.

Use it like that :
1 - Unpack original SQLite 3.3.17 source from SQLite Site
2 - Copy the patch to the root of source
3 - in your shell : $patch -p 2 sqlite-3.3.17-psp33.patch
4 - Now you have SQLite for PSP rev. 33, see README.PSP to build the library

kolaaaa

[kolaaaa]

Now it is in the pspdev repository in "/sqlite"

kolaaaa

[sakya]

Hi! :)

I'm trying to use SQLite but I'm having problem just to open the file.

Code: Select all

retValue = sqlite3_open(dbFile, &db);

Always returns "unable to open file" error both if the file doesen't exists or if it's a SQLite db (created on PC).

What should I check?
The app is in user mode, I just included sqlite3.h, and declared sqlite3 like this:

Code: Select all

sqlite3 *db;

Any idea?
Many thanks
Sakya

[kolaaaa]

Hi,

This code works for me :

Code: Select all

char fileName [256];
sqlite3 *db;

sprintf(fileName, "./test.db");
int rc = sqlite3_open(fileName, &db);

if( rc )
{
/* error */
}

/* success */

I hope that will helps you.

kolaaaa

[sakya]

Hi! :)

Many thanks, it works. :)

I was trying to open ms0:/test.db
Is there a reason why I cannot create this file while I can create ./test.db?
Also setting fileName to 'ms0:/PSP/GAME/test/test.db' (the same directory where the eboot is) fails.

Many thanks
Ciaooo
Sakya

[kolaaaa]

Maybe there is a problem with absolute path, I will check that when I got time.

Thanks for telling me this bug.

kolaaaa

EDIT : this bug has been resolved

[mypspdev]

Hi, please just an help to set up PSP SQLite, thanks in advance.
I've pspdev well set-up and running, I've donwloaded and unzipped the
- sqlite-3.3.17-psp
In the README.PSP is:
Building
--------
Make sure PSPSDK is correctly setup and PSPDEV is set.
$ LDFLAGS="-L$(psp-config --pspsdk-path)/lib -lc -lpspuser" \
./configure --host=psp --disable-readline --disable-tcl \
--prefix=$(psp-config --psp-prefix)
$ make
$ make install

Question: should I prepare a makefile with the previous instruction to building up the libsqlite3.a?

Could anyone please show me or attach a makefile ready to work with pspdev?

Then I should prepare a test database: with SQLite for Windows, from DOS promt, I run the command "sqlite3.exe test.db" and with SQL commands I could populate the test database with some structures and records. Isn't it?

Then a PSP application (ebbot.pbp) which is linking -lsqlite3 library could access the database with something like or from:

________________________
#include <stdio.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName, argv ? argv : "NULL");
}
printf("\n");
return 0;
}

int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0;
int rc;

if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
exit(1);
}
rc = sqlite3_open(argv[1], &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
return 0;

Isn't it ?

Thanks a lot for your help in clarifing step by step for not-enough-expert like (I'm sorry) myself.
c

[kolaaaa]

Hi,

If you want a ready to use script, see ooPo's scripts on /pspdev/psplibraries.
The library is on pspdev's repository, so you no longer need this old zip file.

For the library usage, it's that.

[mypspdev]

Hi,

If you want a ready to use script, see ooPo's scripts on /pspdev/psplibraries.
The library is on pspdev's repository, so you no longer need this old zip file.

For the library usage, it's that.

Thanks for your reply, kolaaaa,
But, I'm sorry for that, what do you mean with "ooPo's scripts on pspdev/libraries? pspdev's repository?

somewhere on the ps3dev.org website?

something like http://svn.ps2dev.org/listing.php?repna ... rev=0&sc=0 ?

I have already downloaded all these files.


Thanks

[ooPo]

To grab the latest version of psplibraries and install only sqlite:

Code: Select all

svn checkout svn://svn.ps2dev.org/psp/trunk/psplibraries
cd psplibraries
./libraries.sh 16

Sqlite is the 16th file in the psplibraries/scripts directory. They're numbered.

[mypspdev]

To grab the latest version of psplibraries and install only sqlite:

Code: Select all

svn checkout svn://svn.ps2dev.org/psp/trunk/psplibraries
cd psplibraries
./libraries.sh 16

Sqlite is the 16th file in the psplibraries/scripts directory. They're numbered.

Thanks a lot, I'm downloading.

[mypspdev]

Hi to all,
thanks for help and thanks to Sakya too.

I've been able to open an existing database on PSP and select rows from tables.

I've seen open on PSP a 0-byte database file on ms0 when open a non existing database.

The PSP crashes when attempting to
CREATE TABLE (on empty database) as follows

Code: Select all

rc = sqlite3_exec(db,"create table tbl1(one varchar(10),two smallint)",SQLite3_callback,0,&errmsg);
if (rc!=SQLITE_OK)
{
	printf("Error CREATE TABLE\n");
	sqlite3_free(errmsg);
}
else
{
ì	printf("CREATE TABLE Exec OK \n");
}

or INSERT INTO table VALUES as follows:

Code: Select all

rc = sqlite3_exec(db,"insert into tbl1 values('hello',10)",SQLite3_callback,0,&errmsg);
if (rc!=SQLITE_OK)
{
	printf("INSERT 10 Error \n");
	sqlite3_free(errmsg);
}
else
{
	printf("INSERT 10 Exec OK \n");
}

Calback function is:

Code: Select all

static SQLite3_callback(void *NotUsed, int argc, char **argv, char **azColName)
{
                int i;
	for&#40;i=0; i<argc;i++&#41;
	&#123;
		printf&#40;"%s = %s\n", azColName&#91;i&#93;, argv&#91;i&#93; ? argv&#91;i&#93; &#58; "NULL"&#41;;
	&#125;
	printf&#40;"\n"&#41;;
	return 0;
&#125;

Thanks in advance for any help.

Is anything wrong? May be the callback?
Or PSP / SQLite3 could not work properly on Database/File IO?

my best to all

[kolaaaa]

Hi,

The 0-byte file is normal. You can check that on your computer without creating tables :

Code: Select all

prompt$ sqlite3 test.db
sqlite>quit;

and a empty file is created.

But I can't find out your problem. I tried your query and it works on my PSP. Do you have print the errmsg string to know what SQLite is complaining about ?

[mypspdev]

The 0-byte file is normal. You can check that on your computer without creating tables :

ok, it is what I was expecting: as a first step, a 0bytes file is created.
PSP shows:
"Exec OK" after database/file open.

But I can't find out your problem. I tried your query and it works on my PSP. Do you have print the errmsg string to know what SQLite is complaining about ?

Then after sqlite3_open(...) I have in main main.c the sqlite3_exec statement for creating a table.

Here the PSP doesn't show any error as expected from the test

if (rc!=SQLITE_OK)

after few seconds the PSP switch off.

If I try sqlite3_exec for insert a new record into an existing 2-records database, the same behaviour of the PSP.
No messages, no test results from

if (rc!=SQLITE_OK)

switching off.

[mypspdev]

Thanks very much kolaaaa for your test and help.
Immediately I've to set up a new cygwin and update the psp-toolchain for correctly compiling the SQLite3 library.

Yours is well working, thanks.

[JustChris]

Quick Q...is this an api that lets you run a database on a computer or can it work internally for the PSP so you can do admin work without the need for a computer?

[Gary13579]

VERY nice work, I plan on using this in my projects from now on (beats INI files by a long shot).

Quick Q...is this an api that lets you run a database on a computer or can it work internally for the PSP so you can do admin work without the need for a computer?

From what I can tell, the database is stored on the PSP. This essentially allows you to store and access data from raw files on the PSP so much easier, instead of creating a custom API or using slow/annoying INI files.

[JustChris]

So there would be no need for a server to connect to the db file? I'm not very familiar with how SQLite works but with MySQL, its API requires that you connect to a MySQL server to access a database.

I am interested in this because I was working on a program to deal with MySQL databases, but indirectly. It had a query parser for reading flat files exported by MySQL. If there is a way that I can directly access the database files instead I would gladly take that route.

[Gary13579]

So there would be no need for a server to connect to the db file? I'm not very familiar with how SQLite works but with MySQL, its API requires that you connect to a MySQL server to access a database.

I am interested in this because I was working on a program to deal with MySQL databases, but indirectly. It had a query parser for reading flat files exported by MySQL. If there is a way that I can directly access the database files instead I would gladly take that route.

That's basically how it works, I believe. It stores all the data into a .db file with no need for a server to connect to and query. Using the SQLite API, you open/query the file directly.

[sakya]

Hi! :)

I think I found a bug in sqlite for PSP.
If you open a database, and then change the current directory with sceIoChdir you won't be able to insert records ("out of memory" error is reported). :)

Many thanks for your work. ;)

Ciaooo
Sakya

[Art]

To grab the latest version of psplibraries and install only sqlite:

Code: Select all

svn checkout svn://svn.ps2dev.org/psp/trunk/psplibraries
cd psplibraries
./libraries.sh 16

Sqlite is the 16th file in the psplibraries/scripts directory. They're numbered.

I get a URL "this one" doesn't exist.
Is there any direct link to retrieve sqlite3.h?

[J.F.]

To grab the latest version of psplibraries and install only sqlite:

Code: Select all

svn checkout svn://svn.ps2dev.org/psp/trunk/psplibraries
cd psplibraries
./libraries.sh 16

Sqlite is the 16th file in the psplibraries/scripts directory. They're numbered.

I get a URL "this one" doesn't exist.
Is there any direct link to retrieve sqlite3.h?

That's a subversion repo link. You have to use SUBVERSION to CHECKOUT the directory as the code shows ("svn" is the subversion client, "checkout" is the client command, and the "svn://..." url is the repo module to operate on).

Once you checkout the psplibraries module and run the script as shown, you will have sqlite installed and ready to use.

[Art]

So there is more than just SQlite3.c, SQLite3.h ?

I have never been able to run any scripts witht his install.
I only got Mikmod and Freetype from a precompiled toolchain.
At tle last line of that command cygwin tells be to install "autoconf"
before continuing, but all three versions of autoconf are already installed.

[J.F.]

Uh... yes, there's a LOT more than just those two files. If all you wish to check out of the repo is sqlite, do this:

Code: Select all

svn checkout svn://svn.ps2dev.org/psp/trunk/sqlite

If you aren't comfortable with a command line client, there are graphical clients for Windows (I assume you're using Windows since you mention cygwin). TortoiseSVN being one.

http://tortoisesvn.tigris.org/

[Art]

That seems to have worked.
It downloaded a whole sqlite folder, although I don't see the two include files in there.
How do I run the script?

[J.F.]

One of the files in the sqlite directory is "README.PSP". Do you think maybe that might possibly have some info relevant to your question? ;)