Database Schema API

Drupal 6.x. Table "node".

What should the value of the "language" field be if the displayed text is Chinese.

If a value is required, are there any other things that need to be done so that Chinese symbols are correctly displayed.

Any help appreciated.

Steve
http://prime357.org

ps. I've got a thread running at my site which I think this may be a part issue to it. As you can imagine it's becoming very confusing as all I see is either unicode or Chinese symbols (can't read Chinese).

Hope I'm not out of line with this request.

I'm now on the lookout for C++ contributors (and other like stuff in time). I started the site http://prime357.org, which is a separation from my personal blogging (http://superjacent.net - running) site, in early April this year. Too much geeky stuff was creeping into the running site.

I hope this is the right area for this question which refers to the 'thread' field of the table 'comments'. I provide a non php solution to convert Wordpress data to Drupal. I recently became aware that the thread field is not a string of decimal numbers but is in fact a string of base 36 (Vancode) numbers. I've created the necessary functions to convert to and from base 36 format but I'm a little confused re - the leading digit/character.

Here is an excerpt from http://api.drupal.org/api/function/int2vancode/5

<

blockquote>

Join Amazon (Kieran Lal) and other Drupal community members and leaders at the Drupal Birds of a Feather session at the MySQL Conference & Expo on Wednesday, April 16th at 7:30pm. The BoF takes place in Ballroom “E” at the Santa Clara Convention Center. Afterwards, Sun will be giving away a Playstation 3 and Sun Fire X2100 M2 Server ($2,495) at their party next door. Wear your Drupal shirt!

More info about the conference:
http://en.oreilly.com/mysql2008

More info about the Sun party:

I start my Drupal + Oracle research since the end of Drupal 4.7 life cycle (around Oct 2006), based on my client's request. The project is still running, but I am not satisfy about its progress. After keep trace in Drupal database stack implementation for more than years, it is time for focusing on Drupal 7.x development.

So what are the changes since 4.7.x? And what will be happened for 7.x? I would like to share my research progress with you, and so let's brain storming for what's next :-)

Abstract

Drupal 6.0 is revamped with Schema API, so what's next for Drupal 7.x? PDO for sure! With this powerful data-access abstraction layer, workload will much reduced for DB abstraction layer designers and developers, and finally benefit our contribute developers and end users.

By the way, together with the decision of Drupal 7.x + PDO, there is also some voice about moving PostgreSQL (and so other potential databases support, e.g. Oracle, DB2, MSSQL, etc) support away from core, but contribute; on the other hand, add official SQLite support into Drupal core, together with MySQL.

Is this really a good idea? Or even if it is possible? As an existing Drupal + PostgreSQL users, what will this affect your daily work? As a potential customer of Drupal + Oracle/DB2/MSSQL/etc, is this a good new for you, or just an evil? I would like to provide some brief idea for you within this article.

Enhance cross database compatibility is important for our Drupal Core, especially when we are going to ship D7 with PHP5.2.x PDO implementation. BTW, this will require number of individual patches, so your help and review are required - add it to the top list with a brief description and an estimate of the time to review. Once reviewed, move the issues to the bottom "reviewed" list, perhaps with your name attached.

My primary battle plan should be enhance Drupal cross database compatibility. I have involved in this topic for around a year, and I would like to keep it on going. To accomplish this target, I would like to complete the following tasks before D7 code freeze:

• Improve Database API for better abstraction

  Something should be done before any other targets, e.g. resolve reserved words conflict, split drivers into individual files for better management, indeed but simple abstraction for different database syntax, add INSERT/UPDATE/DELETE abstraction and enhance drupal_write_record() abstraction, etc. With a better common base for multiple database development, we will need much less time to study the differences between databases than that of before.

• Add PHP PDO supporting

  Shipping D7 with PHP PDO should be a spotlight topic, since it is now official package for PHP5.2.x, and legacy drivers will soon be removed in PHP6.x. We may need some cleanup in our core query syntax, in order to let PDO get works.

• Introduce more database backend

  Up to this point, I would like to introduce some other database backend for D7, e.g. Oracle, SQLite, IBM DB2 and also MSSQL. If we are able to support SQLite in D7, which also means we are going to standardize our core queries into SQL92 standard, the problem for maintain multiple database backend should no longer a critical problem; if we are able to support Oracle, the most complicated database I guess, this should no longer a problem for us to implement drivers for other databases.

I have summarize most of my personal battle targets in here. Most logic are proved as functioning, and they are all get set for the open of D7 public development. On the other hand, I would like to explore if this may integrate with other interest musings with Data API, so we will able get all stuff better in D7 ;-)

I'm assuming it's too late, but if not here's an easy performance win for D6:

http://drupal.org/node/215080

<

blockquote>The type column of the {system} table is currently a varchar(255) field. Yet in 99% of all Drupal installations it stores either the text 'module' or the text 'theme' and nothing else. This is already a strong case for making it an int column and defining constants for module and theme. Then, on every page, there is this query:
SELECT filename FROM system WHERE name = 'user' AND type = 'module';

I have been pondering the question of a data API for a while, as have a lot of people. Much of the recent discussion has focused on an Active Record approach to a data API. Now, Active Record is a very powerful architectural pattern. It maps nicely from storage to interface, it can be fairly self-documenting, and it is conceptually simple and approachable.

It is also, I believe, insufficient.

I think it is safe to say that many developers already using or about to start using MySQL table type InnoDB. Some developers even changing default MySQL storage engine to InnoDB.
In many cases it make sense to convert table type if not for all tables, then at least for some tables.
In this situation will be nice if modules developers will state what table type is necessary for those modules and why.
Otherwise, its has to be investigated and tested for all modules in the project installation. May be it is fun but it is also a lot of time.
My suggestions for Drupal developers are

• include into modules .install file the indication of MySQL table type in the end of each table creation query like "CREATE TABLE `tablename` (...) ENGINE=InnoDB DEFAULT CHARSET=utf8" or "... ENGINE=MyISAM ...";
• include into 'INSTALL.TXT' file description of why these tables need to have those types.

That will give another developers enough information for making right decision on MySQL optimization of project's Drupal installations.

So, I've had some free time latley, and I've started back on the database API layers (DB2 and SQL Server currently).
However, I have found 2 disturbing issues.

Overview
First, I'm currently exploring the following 3 ways to connect to sql server:
1. FreeTDS
2. php_mssql driver
3. MSFT SQL Driver ( See pcorbetts posting for a link to the driver )

1. PHP 5.2.5 breaks support for SQL Server

As mentioned on the other paper A Data API for Drupal. Here is a paper that shows how this could be accomplished with some OOP bits implementing the Active Records Pattern. While keeping the interface procedural.

We all know that Dupal will not go full OOP. But some OOP code could help us do things not possible with procedural code. Such as lazy loading of nested objects and 'on demand' database fields processing.

There's growing interest in the Drupal community in the prospect of renewing our core data handling APIs. Doing so will increase consistency and efficiency and ease barriers. It will also be a key step in enabling transactional web services.

In Drupal 6 we took some impressive first steps. What should we tackle for Drupal 7?

The attached paper, written by Nedjo Rogers and Henrique Recidive and sponsored by CivicSpace, aims to carry this discussion forward and map out both some conceptual space and concrete development tasks.

Once foreign keys are specified during table creation, we will have to make sure to create tables in the correct order. e.g. You can't create system.schema, whose files table has a uid column, before you create user.schema which creates the users table which hold the master uid column.

If this proves too difficult we can use ALTER TABLE to put the foreign key contraints in afterward, but that seems much less desireable.

Now that we have four more weeks before D6 code freeze, maybe it is possible to add join relationships/foreign key support into Schema API 1 after all. Otherwise, it will be another year before this info is in core. Note that this info does not really have to be in core---it can be added by a contrib module hook_schema_alter. But a lot of people would be pretty psyched to have referential integrity for pgsql built in to D6.

Now that the schema has been committed to core, I wanted to open up a thread where people could discuss additions an enhancements to the API.

I posted a patch to add a db_rename_table over on #147285. I've love to see it get in before the feature freeze.

Now that Schema almost has a driver for PostgreSQL I needed to resolve the data type issue. I like Frando's approach (I seem to say that a lot) of storing 'type' and 'size' separately because it makes a variety of things easier. For example:

So, I've had a few inquiries over the last few months about random node generation as I have implemented it successfully for a client. I have posted a full write up of it on my blog. Feel free to comment on it here or on my blog; I will post a patch when I have a chance and if there is interest.

Article link: http://earnestberry.com/node/13

One thing that becomes possible when Drupal can access its own database schema is that queries which currently are performed separately can instead be performed together. In particularly, I think it might be possible to load an entire node with a single SELECT query or, at least, in far fewer than Drupal currently uses. Working proof of concept code is already in the Schema module; it loads all CCK fields, comments, and author info in one query. Loading more things only requires adding a join entry to the schema data structure (see below).

One of the first things we to do is define the data structure that modules will use for declaring their tables. Schema module, Frando's patch, dopry's patch, and CCK fields all use different structures though their underlying approaches are basically the same. We need to decide what kind of information the data structures must contain and can contain. This will obviously include at least columns (name, type, length, nullable, default), indices (name, columns, primary vs unique vs multi), and join/relationship information.

Welcome to the Database Schema API group. A number of different Drupal developers have independently suggested and/or coded various approaches to abstracting table creation/alteration. The idea finally seems to have reached critical mass and I suggest that we will be best served by combining everyone's efforts on the topic. Hence, this group.

By way of background, here is some recommended reading on the topic: